折腾：
后，去创建需要的数据库，结果出错：
<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