折腾:
后,去创建需要的数据库,结果出错:
<code>➜ mysql git:(master) ✗ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 39 Server version: 5.7.22 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) mysql> create database yourDB; ERROR 1006 (HY000): Can't create database 'yourDB' (errno: 230174752) </code>
很明显,估计是此处之前
恢复,重置mysql数据库就没弄好。所以此处用起来有问题。
ERROR 1006 HY000 Can’t create database errno 230174752
mac ERROR 1006 HY000 Can’t create database errno 230174752
ERROR 1006 (HY000): Can’t create database | DigitalOcean
另外,此处:
/var/log
下面并没有mysql的log:
sql – MySQL: ERROR 1006 (HY000) Can’t create database (errno: 26469527) – Stack Overflow
mysql> create database db; ERROR 1006 (HY000): Can’t create database ‘db’ (errno: 2) – CSDN博客
ERROR1006 (HY000):Can’t create database(errno: 28) – 叶落山城秋
算了,感觉是重新安装,估计比较靠谱。
所以去重新安装吧:
去用之前的dmg去安装
mysql-5.7.22-macos10.13-x86_64.dmg
<code>➜ ~ ll /usr/local/mysql/ total 48 -rw-r--r-- 1 root wheel 18K 3 4 21:40 COPYING -rw-r--r-- 1 root wheel 2.4K 3 4 21:40 README drwxr-xr-x 40 crifan wheel 1.3K 4 27 11:16 bin drwxrwxrwx 10 _mysql _mysql 320B 4 27 10:59 data drwxr-xr-x 5 crifan wheel 160B 4 27 11:16 docs drwxr-xr-x 50 crifan wheel 1.6K 4 27 11:16 include drwxr-x--- 2 _mysql _mysql 64B 4 26 11:28 keyring drwxr-xr-x 11 crifan wheel 352B 4 27 11:16 lib drwxr-xr-x 4 crifan wheel 128B 3 4 23:24 man drwxr-xr-x 39 crifan wheel 1.2K 4 27 11:16 share drwxr-xr-x 6 crifan wheel 192B 4 27 11:16 support-files ➜ ~ which mysql /usr/local/mysql/bin/mysql ➜ ~ mysql --version mysql Ver 14.14 Distrib 5.7.22, for macos10.13 (x86_64) using EditLine wrapper </code>
结果还是之前的旧的mysql:
<code>➜ ~ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 43 Server version: 5.7.22 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> shwo dbs; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'shwo dbs' at line 1 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) mysql> </code>
所以要去搞清楚,如何彻底卸载掉之前dmg安装的mysql:
【已解决】Mac中如何彻底卸载之前通过dmg安装的mysql
然后再去重新用dmg安装:
2018-04-27T03:52:55.484872Z 1 [Note] A temporary password is generated for root@localhost: 6q1zMflP6S#B
If you lose this password, please consult the section How to Reset the Root Password in the MySQL reference manual.
然后新开个终端去登录试试,结果出错:
然后接着登录出错:
<code>➜ AliyunRdsMysql mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) ➜ AliyunRdsMysql mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) ➜ AliyunRdsMysql mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) ➜ AliyunRdsMysql </code>
不论是用之前的临时密码,还是不用密码,都不行。
感觉就是:
没了root用户,也没了密码
所以要去重置用户和密码了。
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
解决 ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES) 问题 – CSDN博客
其中一个终端:
<code>➜ ~ ➜ ~ mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) ➜ ~ mysqladmin -u root 6q1zMflP6S#B crifan_mysql mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' ➜ ~ /usr/local/mysql/support-files/mysql.server status SUCCESS! MySQL running (74285) ➜ ~ /usr/local/mysql/support-files/mysql.server stop Shutting down MySQL .. SUCCESS! ➜ ~ mysqld_safe --skip-grant-tables --skip-networking 2018-04-27T06:35:06.6NZ mysqld_safe Logging to '/usr/local/mysql/data/promote.cache-dns.local.err'. 2018-04-27T06:35:06.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data </code>
去另外终端去:
<code>➜ ~ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.22 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> UPDATE mysql.user SET Password=PASSWORD('crifan_mysql') WHERE User='root'; ERROR 1054 (42S22): Unknown column 'Password' in 'field list' mysql> UPDATE mysql.user SET password=PASSWORD('crifan_mysql') WHERE User='root'; ERROR 1054 (42S22): Unknown column 'password' in 'field list' mysql> UPDATE mysql.user SET password=PASSWORD("crifan_mysql") WHERE User='root'; ERROR 1054 (42S22): Unknown column 'password' in 'field list' mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec) mysql> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> update set password=PASSWORD('crifan_mysql') WHERE user='root'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set password=PASSWORD('crifan_mysql') WHERE user='root'' at line 1 mysql> update set password=PASSWORD('crifan_mysql') WHERE User='root'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set password=PASSWORD('crifan_mysql') WHERE User='root'' at line 1 mysql> update user set password=PASSWORD('crifan_mysql') WHERE user='root'; ERROR 1054 (42S22): Unknown column 'password' in 'field list' mysql> </code>
ERROR 1054 (42S22): Unknown column ‘password’ in ‘field list’
MySQL5.7更改密码时出现ERROR 1054 (42S22): Unknown column ‘password’ in ‘field list’ – CSDN博客
<code>mysql> update user set authentication_string=PASSWORD('crifan_mysql') WHERE user='root'; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> quit Bye </code>
和之前一样,登录后,又提示密码:
<code>➜ ~ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.22 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> show </code>
参考自己之前的:
【已解决】Mac中Sequel连接本地Mysql出错:Unable to connect to host 127.0.0.1, or the request timed out
去:
<code>mysql> show databases; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> update user set password_expired='N' where user='root'; ERROR 1046 (3D000): No database selected mysql> update mysql.user set password_expired='N' where user='root'; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> use mysql ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> use mysql; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> update user set password_expired='N' where user='root'; ERROR 1046 (3D000): No database selected mysql> use mysql; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> </code>
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
MySQL 报错 ERROR 1820 (HY000): You must reset you… – 简书
<code>mysql> set authentication_string=PASSWORD("crifan_mysql"); ERROR 1193 (HY000): Unknown system variable 'authentication_string' </code>
macos – Reset MySQL root password using ALTER USER statement after install on Mac – Stack Overflow
<code>mysql> set authentication_string=PASSWORD("crifan_mysql"); ERROR 1193 (HY000): Unknown system variable 'authentication_string' mysql> UPDATE mysql.user SET authentication_string=PASSWORD('crifan_mysql') WHERE User='root'; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> set password=PASSWORD('crifan_mysql'); Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> quit; Bye </code>
最后终于可以正常登录并操作新建数据库了。
<code>➜ ~ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.22 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> create database yourDB; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | yourDB | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> </code>
【总结】
此处也是费了好半天,才最终经历:
【已解决】Mac中启动mysql出错:ERROR The server quit without updating PID file
才解决了mysql不能启动的问题。
再去重置密码,才得以正常使用。
转载请注明:在路上 » 【已解决】Mac中mysql中创建数据库出错:ERROR 1006 HY000 Can’t create database errno 230174752