MySQL远程访问设置终极方法
数据库对于服务端开发人员来说，几乎就是左膀右臂。几乎每一个服务端开发人员都要和数据库打交道。而mysql又以其开源免费，小巧玲珑，简单易用，功能强大占据中国数据库使用份额的半壁*。从去年接触到vagrant以来，vagrant以其方便易用深深吸引了d瓜哥。为了尽可能地和生产环境保持尽可能的一致性，减少不必要的问题的发生，vagrant+ubuntu+mysql几乎是绝配。
mysql允许远程访问的设置
1.注释bind-address = 127.0.0.1。
>cd /etc/mysql
>sudo vim my.cnf
将bind-address = 127.0.0.1注释掉（即在行首加#），如下：
# instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address = 127.0.0.1
除了注视掉这句话之外，还可以把后面的ip地址修改成允许连接的ip地址。但是，如果只是开发用的数据库，为了方便起见，还是推荐直接注释掉。
从上面的注释中，可以看出，旧版本的mysql（从一些资料上显示是5.0及其以前的版本）上使用的是skip-networking。所以，善意提醒一下，使用旧版本的小伙伴请注意一下。
2.删除匿名用户
登录进数据库：
>mysql -uroot -p123456
然后，切换到数据库mysql。sql如下：
use mysql;
然后，删除匿名用户。sql如下：
delete from user where user='';
3.增加允许远程访问的用户或者允许现有用户的远程访问。
接着上面，删除匿名用户后，给root授予在任意主机（%）访问任意数据库的所有权限。sql语句如下：
mysql> grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
如果需要指定访问主机，可以把%替换为主机的ip或者主机名。另外，这种方法会在数据库mysql的表user中，增加一条记录。如果不想增加记录，只是想把某个已存在的用户（例如root）修改成允许远程主机访问，则可以使用如下sql来完成：
update user set host='%' where user='root' and host='localhost';
4.退出数据库
mysql> exit
在mysql shell执行完sql后，需要退出到bash执行系统命令，需要执行exit。因为这个太常用也太简单。以下内容就提示“退出”，不再重复列出这个命令。
5.重启数据库
完成上述所有步骤后，需要重启一下数据库，才能让修改的配置生效。执行如下命令重启数据库：
>sudo service mysql restart
到此为止，应该就可以远程访问数据库了。
当然，“雄关漫道真如铁”，世界并不是想象的那么图样图森破，可能还会遇到一些问题，影响到正常使用。下面，d瓜哥把自己遇到的一些问题整理处理，方便各位朋友参考解决。（退一步讲，没有这些问题来撑门面，d瓜哥该叫“终极解决办法”吗？！哈哈）
常见问题解答
华仔说的好啊，“出来混的都是要还的”。对于我们从事挨踢行业的小伙伴来说，“出来混的都是从问题堆里走的”。冯巩也说的好，“作为北京人，兜里没揣两千块钱都不好意思给别人打招呼”。d瓜哥想，对于干挨踢的小伙伴们来说，没遇到过问题，都不好意思说自己是干挨踢的。所以，对于一些常见的问题，或者说是常用操作可能引发的问题来说，有必要整理一下，方便以后随时查阅使用。
error 1045 (28000)错误的原因以及解决办法
>mysql -uroot -p123456
warning: using a password on the command line interface can be insecure.
error 1045 (28000): access denied for user 'root'@'localhost' (using password: yes)
如果没有按照上面流程，删除匿名用户，再或者你是按照网上查的资料来进行设置（大多都没有“删除匿名用户”这步），都可能导致这个问题。至于这个问题的原因说起来，d瓜哥感觉“怪怪”的。要说清楚这个原因，还得从mysql数据库的用户认证机制说起。
mysql中，用户相关的信息存在数据库mysql的user。然我们看一下该表的结构，命令以及返回值如下：
mysql> desc user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| field | type | null | key | default | extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| host | char(60) | no | pri | | |
| user | char(16) | no | pri | | |
| password | char(41) | no | | | |
| select_priv | enum('n','y') | no | | n | |
| insert_priv | enum('n','y') | no | | n | |
| update_priv | enum('n','y') | no | | n | |
| delete_priv | enum('n','y') | no | | n | |
| create_priv | enum('n','y') | no | | n | |
| drop_priv | enum('n','y') | no | | n | |
| reload_priv | enum('n','y') | no | | n | |
| shutdown_priv | enum('n','y') | no | | n | |
| process_priv | enum('n','y') | no | | n | |
| file_priv | enum('n','y') | no | | n | |
| grant_priv | enum('n','y') | no | | n | |
| references_priv | enum('n','y') | no | | n | |
| index_priv | enum('n','y') | no | | n | |
| alter_priv | enum('n','y') | no | | n | |
| show_db_priv | enum('n','y') | no | | n | |
| super_priv | enum('n','y') | no | | n | |
| create_tmp_table_priv | enum('n','y') | no | | n | |
| lock_tables_priv | enum('n','y') | no | | n | |
| execute_priv | enum('n','y') | no | | n | |
| repl_slave_priv | enum('n','y') | no | | n | |
| repl_client_priv | enum('n','y') | no | | n | |
| create_view_priv | enum('n','y') | no | | n | |
| show_view_priv | enum('n','y') | no | | n | |
| create_routine_priv | enum('n','y') | no | | n | |
| alter_routine_priv | enum('n','y') | no | | n | |
| create_user_priv | enum('n','y') | no | | n | |
| event_priv | enum('n','y') | no | | n | |
| trigger_priv | enum('n','y') | no | | n | |
| create_tablespace_priv | enum('n','y') | no | | n | |
| ssl_type | enum('','any','x509','specified') | no | | | |
| ssl_cipher | blob | no | | null | |
| x509_issuer | blob | no | | null | |
| x509_subject | blob | no | | null | |
| max_questions | int(11) unsigned | no | | 0 | |
| max_updates | int(11) unsigned | no | | 0 | |
| max_connections | int(11) unsigned | no | | 0 | |
| max_user_connections | int(11) unsigned | no | | 0 | |
| plugin | char(64) | yes | | | |
| authentication_string | text | yes | | null | |
| password_expired | enum('n','y') | no | | n | |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.00 sec)
或者查看一下该表的建表语句。命令如下：
mysql> show create table user;
由于返回值中掺杂了一些不必要的无用信息，为了方便大家查看，d瓜哥对返回值做了简单的处理，只把最主要的建表语句部分提取出来。如下：
create table `user` (
`host` char(60) collate utf8_bin not null default '',
`user` char(16) collate utf8_bin not null default '',
`password` char(41) character set latin1 collate latin1_bin not null default '',
`select_priv` enum('n','y') character set utf8 not null default 'n',
`insert_priv` enum('n','y') character set utf8 not null default 'n',
`update_priv` enum('n','y') character set utf8 not null default 'n',
`delete_priv` enum('n','y') character set utf8 not null default 'n',
`create_priv` enum('n','y') character set utf8 not null default 'n',
`drop_priv` enum('n','y') character set utf8 not null default 'n',
`reload_priv` enum('n','y') character set utf8 not null default 'n',
`shutdown_priv` enum('n','y') character set utf8 not null default 'n',
`process_priv` enum('n','y') character set utf8 not null default 'n',
`file_priv` enum('n','y') character set utf8 not null default 'n',
`grant_priv` enum('n','y') character set utf8 not null default 'n',
`references_priv` enum('n','y') character set utf8 not null default 'n',
`index_priv` enum('n','y') character set utf8 not null default 'n',
`alter_priv` enum('n','y') character set utf8 not null default 'n',
`show_db_priv` enum('n','y') character set utf8 not null default 'n',
`super_priv` enum('n','y') character set utf8 not null default 'n',
`create_tmp_table_priv` enum('n','y') character set utf8 not null default 'n',
`lock_tables_priv` enum('n','y') character set utf8 not null default 'n',
`execute_priv` enum('n','y') character set utf8 not null default 'n',
`repl_slave_priv` enum('n','y') character set utf8 not null default 'n',
`repl_client_priv` enum('n','y') character set utf8 not null default 'n',
`create_view_priv` enum('n','y') character set utf8 not null default 'n',
`show_view_priv` enum('n','y') character set utf8 not null default 'n',
`create_routine_priv` enum('n','y') character set utf8 not null default 'n',
`alter_routine_priv` enum('n','y') character set utf8 not null default 'n',
`create_user_priv` enum('n','y') character set utf8 not null default 'n',
`event_priv` enum('n','y') character set utf8 not null default 'n',
`trigger_priv` enum('n','y') character set utf8 not null default 'n',
`create_tablespace_priv` enum('n','y') character set utf8 not null default 'n',
`ssl_type` enum('','any','x509','specified') character set utf8 not null default '',
`ssl_cipher` blob not null,
`x509_issuer` blob not null,
`x509_subject` blob not null,
`max_questions` int(11) unsigned not null default '0',
`max_updates` int(11) unsigned not null default '0',
`max_connections` int(11) unsigned not null default '0',
`max_user_connections` int(11) unsigned not null default '0',
`plugin` char(64) collate utf8_bin default '',
`authentication_string` text collate utf8_bin,
`password_expired` enum('n','y') character set utf8 not null default 'n',
primary key (`host`,`user`)
) engine=myisam default charset=utf8 collate=utf8_bin comment='users and global privileges'
从上面两个方法中的任何一个都可以看出，mysql中，使用(host, user)这样的组合键作为主键，也就是标识唯一一个用户。换句话说，即使对于同一个用户名，例如root，随着host的不同，也表示不同的用户，就可以由不同的密码和权限。这是，我们可以查看一下该表内的数据。sql以及返回值如下：
mysql> select user, host, password from user;
+------------------+--------------------------+-------------------------------------------+
| user | host | password |
+------------------+--------------------------+-------------------------------------------+
| root | % | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |
| root | vagrant-ubuntu-trusty-64 | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |
| root | 127.0.0.1 | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |
| root | ::1 | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |
| | localhost | |
| | vagrant-ubuntu-trusty-64 | |
| debian-sys-maint | localhost | *9b8e84ca988e1ca66ceb919a0d9d3bf1532942b6 |
+------------------+--------------------------+-------------------------------------------+
7 rows in set (0.00 sec)
可以看到数据库中有如上这些用户。那么，如果有'root'@'localhost'登录时，怎么匹配呢？
根据mysql官方文档mysql :: mysql 5.6 reference manual :: 6.2.4 access control, stage 1: connection verification里是如下面这样说的：
when multiple matches are possible, the server must determine which of them to use. it resolves this issue as follows:
whenever the server reads the user table into memory, it sorts the rows.
when a client attempts to connect, the server looks through the rows in sorted order.
the server uses the first row that matches the client host name and user name.
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 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).
请注意这句话：“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.”
那么，当从本地连接到数据库时，匿名用户将覆盖其他如'[any_username]'@'%'之类的用户。具体匹配过程如下：
在'root'@'localhost'可以匹配'root'@'%'，''也可以匹配root，那么'root'@'localhost'也可以匹配''@'localhost'。根据上面的文档显示，主机（host）有比用户名称user更高的匹配优先级，则localhost比%有比更高的匹配优先级。所以，''@'localhost'比'root'@'%'匹配优先级更高，'root'@'localhost'就匹配到了''@'localhost'。根据我们上面的查询结果来看，''@'localhost'的密码为空，与登录时提供的密码123456不匹配。所以，就登录失败了。
说到这里，想必大家已经想到了解决方案：很简单，直接把匿名用户删掉就可以了。
但是，现在的问题时，在本地根本登录不上数据库，更别扯删除数据了。
怎么办？d瓜哥想到了三个解决办法，方法如下：
①、比较扯淡的解决方法，在另外一台电脑或者虚拟机上，远程连接这个数据库
这是我想到的一个比较扯淡的解决方法。既然我们已经允许了远程连接，那么我们就可以在另外一台电脑或者另外一个虚拟机上，远程连接到这个数据库，然后把删除匿名用户。步骤如下：
1.在远程电脑上连接到该数据库。命令如下：
mysql -h192.168.1.119 -uroot -p123456
2.然后，切换到mysql数据库。命令如下：
use mysql
3.删除匿名用户。sql如下：
delete from user where user='';
4.退出，然后就可以在本地直接登录数据库了。
②、投机取巧的解决办法，在本地使用跳过权限表认证的登录数据库
刚刚在上面的原因解释中已经说过，虽然登录使用的root帐号，但是被数据库处理成了匿名用户。而登录用的帐号和密码与匿名帐号信息不匹配，才导致权限认证没有通过。那干脆就“不走寻常路”，跳过权限认证这一步，直接登录不就ok了。具体做法类似“忘记密码”的处理方式类似，所以，可以参考下面的 忘记数据库密码 。需要注意的时，这里和“忘记数据库密码”的处理不一样的是，这里不需要这里只需要登录进去，登录进去之后需要删除匿名帐号而不是重置密码。至于删除匿名帐号的方法，请参考上面的介绍，这里就不再赘述。
③、最诡异的解决办法，在本地伪装成远程主机来登录数据库
既然如果不知道ip地址，mysql会默认认为从本地主机登录。那么，我们就指定一下主机，把自己伪装成远程主机登录。d瓜哥认为这个解决办法从mysql的认证机制上是说得通的。
首先，使用ifconfig查出本地主机的ip（注意，不是127开头的回环地址）。
然后，使用如下命令登录：
mysql -h192.168.1.119 -uroot -p123456
再然后的工作就是删除匿名用户。上面已经介绍过了，这里不再赘述。
对于这个方法，d瓜哥需要特别说明一下。这个方法是d瓜哥推测出来的，还经过实际测试。所以，所以，欢迎板砖和鸡蛋！哈哈
忘记mysql用户密码的解决办法
因为需要用一个好久不怎么使用的数据库，密码忘记了，所以遇到了这个问题。这次本来没打算写这个内容，但是计划赶不上变化，因为遇到了error 1045 (28000)。后来，发现使用这个方法也可以解决掉这个问题。为了以后自己查资料方便，干脆把这个主题提出来，作为“常见问题”之一，也记录一下。
1.停止mysql数据库。命令如下：
sudo service mysql stop
暴力点的做法是，使用ps命令查出来mysql数据库的pid，然后在使用kill -9 pid直接杀掉数据库进程。这个方法，太过血腥，就不提供命令了。呵呵
2.启动数据库。命令如下：
sudo mysqld_safe --skip-grant-tables &
说明：参数--skip-grant-tables的意思是启动mysql服务的时候跳过权限表认证。启动后，连接到mysql的root不需要口令。
另外，某些计算机上没有安装mysqld_safe命令，则可以使用如下命令来启动数据库：
sudo mysqld --skip-grant-tables &
除此之外，还可以修改mysql的配置文件/etc/mysql/my.cnf，在[mysqld]的部分中加上一句：skip-grant-tables。但是，使用这种方式在重置完密码后，还要再重新删除这句话。d瓜哥个人感觉比较麻烦。仅做了解，不推荐使用这种方式。
3.登录数据库。命令如下：
mysql -uroot
4.切换到mysql数据库
mysql> use mysql
5.修改root帐号密码
mysql> update user set password=password('123456') where user='root';
6.退出，然后重启数据库
>sudo service mysql restart
现在，应该就可以使用新密码正常访问数据库了。
error 2002 (hy000)错误的原因以及解决办法
在登录数据库的过程中，可能遇到如下错误：
mysql -uroot
error 2002 (hy000): can't connect to local mysql server through socket '/var/run/mysqld/mysqld.sock' (2)
刚开始，d瓜哥以为是数据库没有正确创建mysqld.sock这个文件，到/var/run/mysqld/下查看了一下，确实没有这个文件。
这是怎么回事？怎么会没有这个文件呢？难道数据库没有启动起来。使用如下命令查看一下：
>ps aux | grep mysql
shit，竟然什么都没有输出。看来确实是数据库没有启动起来。使用如下命令启动数据库：
>sudo service mysql start
如果是需要启动跳过认证的方式启动，则使用如下命令之一：
sudo mysqld_safe --skip-grant-tables &
如果提示没有mysqld_safe，则使用如下命令：
>sudo mysqld --skip-grant-tables &
然后就可以正常登录了。
关于这个问题，d瓜哥还想再多说两句：这个错误的根本原因是没有找到mysqld.sock文件，造成这个问题的原因有两种：一根本没有创建这个文件，也就是没有启动数据库，这也是d瓜哥在这里介绍的原因；另外一个原因也有可能是创建的目录不在默认目录，或者连接数据库时指定的这个文件目录和实际产生的目录不符。d瓜哥在网上查资料时，也印着了这个猜测。部分网友的问题就是d瓜哥所说的第二个原因造成的。所以，网上对于这个问题的解决方案还存在另外一个方法。因为d瓜哥没有遇到，而且情况又比较多，d瓜哥这里就不再过多介绍了。请遇到这个问题的朋友自行查资料解决。
error 2003 (hy000)错误的原因以及解决办法。
$ mysql -h192.168.1.113 -uroot -p123456
warning: using a password on the command line interface can be insecure.
error 2003 (hy000): can't connect to mysql server on '192.168.1.113' (111)
是说话，出现这个问题，d瓜哥也很蛋疼。不知这又是怎么搞的！不过，好在有错误代码（error 2003 (hy000)），有了这个就有了打开解决方法之门的钥匙。解决方法垂手可得。
用google百度一下，之后在*上查到了原因：mysql数据库的配置文件/etc/mysql/my.cnf中的bind-address = 127.0.0.1没有注释掉，导致mysql只能接收本地的访问。
知道了原因，解决方法也就随之而来，注释掉这行，然后重启服务器。具体做法，参考本文开头部分。
error 1130错误的原因以及解决办法
d瓜哥在查资料的过程中，还遇到别人提到的一个错误。日志如下：
error 1130: host '192.168.1.3' is not allowed to connect to this mysql server
给的解答是“所用主机的ip地址不在被允许登录的ip之内”。
d瓜哥也测试了一下这个错误。刚刚开始测试的时候，这个错误没有重现，还以为我测试有问题。后来，重启了一下数据库，再次测试发现成功了。确实有这个错误。另外，根据d瓜哥个人测试经验，善意提醒一句，对mysql.user表进行修改后，记得执行一下flush privileges或者重启一下数据库。
关于mysqld_safe的简介
在这次解决这些问题的过程中，d瓜哥查看了一下mysql的进程。命令以及返回值如下：
>ps aux | grep mysql
root 1231 0.0 0.0 4444 740 ? s 15:09 0:00 /bin/sh /usr/bin/mysqld_safe
mysql 1604 0.9 23.0 1134700 471332 ? sl 15:09 0:23 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
root 1605 0.0 0.0 4340 644 ? s 15:09 0:00 logger -t mysqld -p daemon.error
vagrant 3942 0.0 0.0 12140 928 pts/0 s+ 15:51 0:00 grep --color mysql
嗯？！有木有发现一个刚刚说明的熟悉的东东：mysqld_safe。这个东东怎么启动起来呢？刚开始d瓜哥以为这个东西不应该启动，甚至还可能导致远程登录数据库不成功。（因为当时确实有这个问题。而且，在上面跳过认证校验的启动时，用到这个命令了。所以，d瓜哥认为它不应该被启动。）经过和敌人（你知道，这肯定是这里提到的一些问题）“艰苦卓绝”斗争后，发现问题不是这个东东造成的。幸好多思考了一点，否则d瓜哥晚节不保啊！哈哈
因为刚刚以为这是个问题。所以，就把这个加入到了问题列表里。经过d瓜哥的了解，发现这不是个问题，但是对于d瓜哥来说，这确实解决了d瓜哥的一个疑问，也暂且算是问题吧。为了方便有同样问题的小伙伴，d瓜哥觉得有必要把mysqld_safe给详细说明一下。
关于mysqld_safe的介绍，最权威的资料自然是官方文档，由于d瓜哥使用的是mysql 5.6版。所以，这里参考mysql :: mysql 5.6 reference manual :: 4.3.2 mysqld_safe — mysql server startup script来进行说明。以下内容翻译自官方文档的说明。
官方文档中，关于mysqld_safe部分的标题，就可以很好地说明mysqld_safe的定位：mysql服务器的启动脚本。正文内容还说到，在unix下，mysqld_safe是推荐的mysql服务器的启动方式。mysqld_safe可以增加一些安全特性，比如当服务器发生故障时，重启数据库服务器，并且将运行信息记录到错误日志文件中。
更多信息，还请各位朋友自行查阅mysql :: mysql 5.6 reference manual :: 4.3.2 mysqld_safe — mysql server startup script。不过，这里没有讲到我们刚刚使用的参数--skip-grant-tables，关于这个参数，在mysql :: mysql 5.6 reference manual :: 5.1.3 server command options中做了介绍。
flush privileges的解释说明
好吧，d瓜哥承认这是一个不是问题的问题。但是，对于d瓜哥来说，这还是一个疑惑，或者说，d瓜哥想知道这句话的确切含义。
从字面意思来说，flush privileges可以翻译为“刷新权限”。这个也确实是flush privileges的一个功能：从数据库msql中的授权表中，重新加载权限结果。
mysql新设置用户或更改密码后需用flush privileges刷新mysql的系统权限相关表，否则会出现拒绝访问；还有一种方法，就是重新启动mysql数据库，来使新设置生效。
除此之外，flush privileges还可以释放内存的功能。mysql会缓存grant，create user，create server和install plugin语句的结果。这些内存直到执行revoke，drop user，drop server和uninstall plugin才会释放。所以，如果数据库执行了很多前面的语句，则会增加内存开销。执行flush privileges就会释放这部分的内存。
上一篇： Android中FTP上传、下载的功能实现（含进度）
下一篇： DIV_圆边圆角的实现