Mysql解压版安装常见错误

Mysql解压版安装常见错误

1. 在执行安装命令时，一直提示安装不成功。

$scripts/mysql_install

原因可能有两个：

a. 在安装前存在旧的数据，运行

$rm -r data

b. 没有指定参数

$scripts/mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp

之后，再重新运行安装命令，发现成功了。

2. 运行mysqladmin修改密码命令，一直显示错误

   Can’t connect to local MySQL server through socket (2)

这个问题在于没有启动Mysql服务，启动之后问题就得到了解决

$support_files/mysql.server start

3. 注意Mysql文件夹Owner

mysql文件的所有者权限与当前登录的用户不同，也有可有导致安装错误。在安装前，确保mysql的所有者为当前用户。

4. Mysql在新建用户时易出现的问题

运行以下命令新建用户

mysql> create user canal identified by 'canal';

刷新

mysql> flush privileges;

但在另一个命令行使用 用户名：'canal'，密码：'canal'并不能登录。。。

$mysql -u canal -p canal

显示如下：

$mysql -ucanal -p
Enter password:
ERROR 1045 (28000): Access denied for user 'canal'@'localhost' (using password: YES)

试了一下不输密码，直接回车，竟然登录成功了。。。

$mysql -ucanal -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.6.39-log 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>

再仔细查看create user canal identified by 'canl';的命令返回结果，竟然是：

Query OK, 0 rows affected (0.00 sec)

什么情况，果断google了一把，在stackoverflow上找到了一个解释：

You have an anonymose user for localhost and that anonymous user don't have any password.
​
It is necessary to have both (% and localhost) accounts for your myuser to be able to connect from anywhere as myuser. Without the localhost account, the anonymous user account for localhost that is created by  mysql_install_db would take precedence when myuser connects from the  localhost. As a result, myuser would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the myuser'@'%' account and thus comes earlier in the user table sort order.
​
About sort order:
​
The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific. (The specificity of a literal IP address is not affected by whether it has a netmask, so for example 192.168.1.13 and 192.168.1.0/255.255.255.0 are considered equally specific.) The pattern '%' means "any host" and is least specific. The empty string  '' also means "any host" but sorts after '%'. Rows with the same Host value are ordered with the most-specific User values first (a blank User value means "any user" and is least specific).
​
So mysql is considering your myuser as an anonymous user and since the anonymous user for  localhost don't have any password, it is able to login without password.
​
To resolve your issue, you just need to create a localhost user for your myuser with password

链接：https://serverfault.com/questions/465873/creating-new-mysql-user-password-and-privileges-not-sticking/465902#465902?s=106c4800a7d74fe58a454482728f8178

大致是说：mysql_install_db在mysql数据库的user表下创建了匿名用户，在使用’canal'进行登录时，由于'canal'没有指定主机(Host)，依照匹配规则，是使用的匿名用户登录。

具体参见：

1. http://www.cnblogs.com/linuxnote/p/3780244.html?utm_source=tuicool&utm_medium=referral

2. http://blog.csdn.net/chrisjingu/article/details/50457486

解决方法1：删除user表中的匿名用户

mysql> delete from user where user='';

刷新，刷新！！解决方法2：为‘canal'创建一个指定主机的用户

mysql> create user 'canal'@'localhost' identified by 'canal';