最新消息:20210816 当前crifan.com域名已被污染,为防止失联,请关注(页面右下角的)公众号

【已解决】Mac中mysql中创建数据库出错:ERROR 1006 HY000 Can’t create database errno 230174752

Mac crifan 5730浏览 0评论

折腾:

【基本解决】如何恢复Mac中mysql中的mysql表

后,去创建需要的数据库,结果出错:

<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&gt; show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql&gt; 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在使用过程中遇到的错误 – 三平行者的程序人生

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&gt; 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&gt; show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql&gt;
</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.

然后新开个终端去登录试试,结果出错:

【已解决】Mac中dmg安装mysql后登录出错:ERROR 2002 HY000 Can’t connect to local MySQL server through socket /tmp/mysql.sock 61

然后接着登录出错:

<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&gt; UPDATE mysql.user SET Password=PASSWORD('crifan_mysql') WHERE User='root';
ERROR 1054 (42S22): Unknown column 'Password' in 'field list'
mysql&gt; UPDATE mysql.user SET password=PASSWORD('crifan_mysql') WHERE User='root';
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql&gt; UPDATE mysql.user SET password=PASSWORD("crifan_mysql") WHERE User='root';
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql&gt; show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql&gt; 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&gt; 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&gt; 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&gt; update user set password=PASSWORD('crifan_mysql') WHERE user='root';
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql&gt;
</code>

ERROR 1054 (42S22): Unknown column ‘password’ in ‘field list’

MySQL5.7更改密码时出现ERROR 1054 (42S22): Unknown column ‘password’ in ‘field list’ – CSDN博客

<code>mysql&gt; 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&gt; flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; 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&gt; show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql&gt; show
</code>

参考自己之前的:

【已解决】Mac中Sequel连接本地Mysql出错:Unable to connect to host 127.0.0.1, or the request timed out

去:

<code>mysql&gt; show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql&gt; update user set password_expired='N' where user='root';
ERROR 1046 (3D000): No database selected
mysql&gt; 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&gt; use mysql
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql&gt; use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql&gt; update user set password_expired='N' where user='root';
ERROR 1046 (3D000): No database selected
mysql&gt; use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql&gt;
</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&gt; 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&gt; set authentication_string=PASSWORD("crifan_mysql");
ERROR 1193 (HY000): Unknown system variable 'authentication_string'
mysql&gt; 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&gt; set password=PASSWORD('crifan_mysql');
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql&gt; flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; 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&gt; 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&gt; show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql&gt; create database yourDB;
Query OK, 1 row affected (0.00 sec)

mysql&gt; show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| yourDB          |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql&gt;
</code>

【总结】

此处也是费了好半天,才最终经历:

【已解决】Mac中dmg安装mysql后登录出错:ERROR 2002 HY000 Can’t connect to local MySQL server through socket /tmp/mysql.sock 61

【已解决】Mac中启动mysql出错:ERROR The server quit without updating PID file

才解决了mysql不能启动的问题。

再去重置密码,才得以正常使用。

转载请注明:在路上 » 【已解决】Mac中mysql中创建数据库出错:ERROR 1006 HY000 Can’t create database errno 230174752

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
83 queries in 0.179 seconds, using 22.10MB memory