客户端远程连接mysql数据库服务器_客户端（远程连接）MySQL数据库服务器

一、生产环境：

服务器端操作系统：CentOS Linux 6.6

客户端操作系统: CentOS Linux 6.7

数据库系统：mysql 5.5.41

二、生产任务：远程连接MySQL数据库

三、操作过程(包括一些异常情况)：

1.在客户端连服务端的情况

[root@jxatei ~]# mysql -h 117.40.239.9  -u root  -p

Enter password:

ERROR 1130(HY000): Host '115.151.218.186' is not allowed to connect to this MySQL server

说明：服务器端不充许IP:“115.151.218.186”连服务端。

2.在服务器端加入客户端IP“115.151.218.186”

mysql> GRANTALL PRIVILEGES ON *.* TO 'root'@'115.151.218.186' IDENTIFIED BY  'root' WITH GRANT OPTION;

Query OK, 0 rows affected (0.01 sec)

3.客户端再次连接

[root@jxatei ~]# mysql -h 117.40.239.9  -u root  -p

Enter password:

ERROR 1045 (28000): Access denied for user'root'@'115.151.218.186' (using password: YES)

4.服务端修改

[root@sky9896etc]# vi  my.cnf

[mysqld]

skip-grant-tables    #在[mysqld]中加该条命令

#在启动mysql时不启动grant-tables，授权表。有什么用呢？当然是忘记管理员密码后有用或是其它用途，此次操作是想解决远程连不上服务端。

[root@sky9896etc]# service mysqld stop

停止mysqld：[确定]

[root@sky9896etc]# service mysqld start

*** glibc detected*** /usr/libexec/mysqld: double free or corruption (fasttop):0x00007f7ab4004a40 ***

正在启动mysqld：[确定]

[root@sky9896etc]# ======= Backtrace: =========

/lib64/libc.so.6(+0x75e66)[0x7f7ad3b5ce66]

/usr/libexec/mysqld(_ZN16Security_context7destroyEv+0xb4)[0x5623a4]

/usr/libexec/mysqld(_ZN3THDD1Ev+0x13d)[0x565f1d]

/usr/libexec/mysqld(_ZN3THDD0Ev+0x9)[0x5662f9]

/usr/libexec/mysqld(_Z10unlink_thdP3THD+0xfa)[0x5184ea]

/usr/libexec/mysqld(_Z29one_thread_per_connection_endP3THDb+0x16)[0x518556]

/usr/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0x139)[0x6208f9]

/usr/libexec/mysqld(handle_one_connection+0x5b)[0x6209db]

/lib64/libpthread.so.0(+0x79d1)[0x7f7ad56cb9d1]

/lib64/libc.so.6(clone+0x6d)[0x7f7ad3bcf9dd]

======= Memorymap: ========

00400000-00c71000r-xp 00000000 08:02 530781                            /usr/libexec/mysqld

00e70000-00f88000rw-p 00870000 08:02 530781                            /usr/libexec/mysqld

00f88000-00fb1000rw-p 00000000 00:00 0

01443000-01df1000rw-p 00000000 00:00 0                                  [heap]

7f7ab4000000-7f7ab4021000rw-p 00000000 00:00 0

7f7ab4021000-7f7ab8000000---p 00000000 00:00 0

7f7abb447000-7f7abb448000---p 00000000 00:00 0

7f7abb448000-7f7abbe48000rw-p 00000000 00:00 0

7f7abbe48000-7f7abbe49000---p 00000000 00:00 0

7f7abbe49000-7f7abc849000rw-p 00000000 00:00 0

7f7abc849000-7f7abc84a000---p 00000000 00:00 0

7f7abc84a000-7f7abd24a000rw-p 00000000 00:00 0

7f7abd24a000-7f7abd24b000---p 00000000 00:00 0

7f7abd24b000-7f7abded9000rw-p 00000000 00:00 0

7f7abe2f7000-7f7abe2f8000---p 00000000 00:00 0

7f7abe2f8000-7f7abecf8000rw-p 00000000 00:00 0

7f7abecf8000-7f7abecf9000---p 00000000 00:00 0

7f7abecf9000-7f7abf6f9000rw-p 00000000 00:00 0

…………………………………………………….

#MySQL启动过程出现以上情况

[root@sky9896etc]# mysql -u root mysql

ERROR 1044(42000): Access denied for user ''@'localhost' to database 'mysql'

[root@sky9896etc]# mysql -u root

Welcome to theMySQL monitor.  Commands end with ; or\g.

Your MySQLconnection id is 3

Server version:5.5.41-cll-lve MySQL Community Server (GPL) by Atomicorp

Copyright (c)2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is aregistered trademark of Oracle Corporation and/or its

affiliates. Othernames may be trademarks of their respective

owners.

Type 'help;' or'\h' for help. Type '\c' to clear the current input statement.

mysql> UPDATEuser  SET Password=PASSWORD(’sky9890’) where USER=’root’;

ERROR 1046(3D000): No database selected

mysql> usemysql

ERROR 1044(42000): Access denied for user ''@'localhost' to database 'mysql'

mysql> showdatabases;

+--------------------+

| Database           |

+--------------------+

|information_schema |

+--------------------+

1 row in set (0.00sec)

mysql> showdatabases;

+--------------------+

| Database           |

+--------------------+

|information_schema |

+--------------------+

1 row in set (0.00sec)

mysql> FLUSHPRIVILEGES;

ERROR 1227(42000): Access denied; you need (at least one of) the RELOAD privilege(s) forthis operation

mysql> quit

Bye

[root@sky9896etc]# mysql -u root

Welcome to theMySQL monitor.  Commands end with ; or\g.

Your MySQLconnection id is 7

Server version:5.5.41-cll-lve MySQL Community Server (GPL) by Atomicorp

Copyright (c)2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is aregistered trademark of Oracle Corporation and/or its

affiliates. Othernames may be trademarks of their respective

owners.

Type 'help;' or'\h' for help. Type '\c' to clear the current input statement.

mysql> showdatabases;

+--------------------+

| Database           |

+--------------------+

|information_schema |

+--------------------+

1 row in set (0.00sec)

#因为增加了一条“skip-grant-tables”语句，登录mysql数据库不需要密码，最终导致mysql服务器中没有数据库了。当然客户端还是连不上，因目前服务端本身找不到数据库了。

[root@jxatei~]# mysql -u root -h 117.40.239.9 -p 3306

Enterpassword:

ERROR1049 (42000): Unknown database '3306'

[sky@sky9896~]$ mysql -u root -p

Enterpassword:

ERROR2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'(2)

[sky@sky9896~]$ service mysqld start

touch:无法创建"/var/log/mysqld.log":权限不够

chown:正在更改"/var/log/mysqld.log"的所有者: 不允许的操作

chmod:更改"/var/log/mysqld.log"的权限: 不允许的操作

chown:正在更改"/var/lib/mysql"的所有者: 不允许的操作

chmod:更改"/var/lib/mysql"的权限: 不允许的操作

MySQLDaemon failed to start.

正在启动 mysqld：                                          [失败]

[sky@sky9896~]$ su - root

密码：

[root@sky9896~]# service mysqld status

mysqld已停

[root@sky9896~]# service mysqld start

正在启动 mysqld：                                          [确定]

[root@sky9896~]# service mysqld restart

停止 mysqld：                                              [确定]

正在启动 mysqld：                                          [确定]

------------------空密码登录----------------------------------

[root@sky9896~]# mysql -u root

Welcometo the MySQL monitor.  Commands end with; or \g.

YourMySQL connection id is 2

Serverversion: 5.5.41-cll-lve MySQL Community Server (GPL) by Atomicorp

Copyright(c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracleis 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本地数据库---------

mysql>use mysql

ERROR1044 (42000): Access denied for user ''@'localhost' to database 'mysql'

mysql>use mysql;

ERROR1044 (42000): Access denied for user ''@'localhost' to database 'mysql'

mysql>exit

Bye

[root@sky9896~]# service mysqld stop

停止 mysqld：                                              [确定]

------以命令行参数启动mysql，不启动grant-tables，授权表，以重新设置密码---

[root@sky9896~]# mysqld_safe --skip-grant-tables

15081110:53:31 mysqld_safe Logging to '/var/log/mysqld.log'.

15081110:53:31 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

---------------登录，重新设置密码————————————————

[root@sky9896~]# mysql

Welcometo the MySQL monitor.  Commands end with; or \g.

YourMySQL connection id is 1

Serverversion: 5.5.41-cll-lve MySQL Community Server (GPL) by Atomicorp

Copyright(c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracleis 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

Readingtable information for completion of table and column names

Youcan turn off this feature to get a quicker startup with -A

Databasechanged

mysql>update user set password=password("sky9890") whereuser="root";

QueryOK, 1 row affected (0.00 sec)

Rowsmatched: 5  Changed: 1  Warnings: 0

mysql>flush privileges;

QueryOK, 0 rows affected (0.00 sec)

mysql>show databases;

+--------------------+

|Database           |

+--------------------+

|information_schema |

|back20150625ultrax |

|feifeicms          |

|mysql              |

|performance_schema |

|ultrax             |

+--------------------+

6rows in set (0.00 sec)

mysql>

[root@sky9896~]# service mysqld start

MySQLDaemon failed to start.

正在启动 mysqld：                                          [失败]

[root@sky9896~]# shutdown  - r  now   #重启服务器

[root@sky9896~]# service mysqld status   #运行正常了

mysqld(pid 1239)正在运行...

#但论坛无法正常运行，显示情况如下：Discuz! Database Error

The database has encountered a problem. Need Help?

Error messages:

[Type]无法连接到数据库服务器

[1045] Access denied for user 'root'@'localhost'       (using password: YES)

Program messages:

[Line: 0050]forum.php(discuz_core->init)

[Line: 0065]source/class/class_core.php(discuz_core->_init_db)

[Line:       0327]source/class/class_core.php(db_mysql->connect)

[Line:       0621]source/class/class_core.php(db_mysql->_dbconnect)

已经将此出错信息详细记录,由此给您带来的访问不便我们深感歉意

mysql>use mysql

ERROR1044 (42000): Access denied for user ''@'localhost' to database 'mysql'

mysql>show databases;

+--------------------+

|Database           |

+--------------------+

|information_schema |

+--------------------+

1row in set (0.00 sec)

#原因是匿名登录，没有真正登录，导致客户端无法连上数据库服务器中的数据库。

mysql>show databases;

ERROR2006 (HY000): MySQL server has gone away

Noconnection. Trying to reconnect...

Connectionid:    1

Currentdatabase: *** NONE ***

+--------------------+

|Database           |

+--------------------+

|information_schema |

|back20150625ultrax |

|feifeicms          |

|mysql              |

|performance_schema |

|test               |

|ultrax             |

+--------------------+

7rows in set (0.00 sec)

mysql>update user set password=password("sky9890") whereuser="root";

ERROR2006 (HY000): MySQL server has gone away

Noconnection. Trying to reconnect...

ERROR2002 (HY000): Can't connect to local MySQL server through socket'/var/lib/mysql/mysql.sock' (2)

ERROR:

Can'tconnect to the server

mysql>use mysql

No connection. Trying to reconnect...

Connection id:    1

Current database: *** NONE ***

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Databasechanged

mysql>update user set password=password("sky9890") whereuser="root";

QueryOK, 0 rows affected (0.00 sec)

Rowsmatched: 6  Changed: 0  Warnings: 0

mysql>flush privileges;

QueryOK, 0 rows affected (0.00 sec)

#以上代码执行后，发现还是存在问题，无法更新空密码。

[sky@sky9896 ~]$ mysql –A    #加-A参数解决问题

Welcometo the MySQL monitor.  Commands end with; or \g.

YourMySQL connection id is 1

Serverversion: 5.5.41-cll-lve MySQL Community Server (GPL) by Atomicorp

Copyright(c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracleis a registered trademark of Oracle Corporation and/or its

affiliates.Other names may be trademarks of their respectiveowners.

Type'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>show databases;

+--------------------+

|Database           |

+--------------------+

|information_schema |

|back20150625ultrax |

|feifeicms          |

|mysql              |

|performance_schema |

|test               |

|ultrax             |

+--------------------+

7rows in set (0.00 sec)

mysql>use mysql;

Databasechanged

mysql>update user set password=password("sky9890") whereuser="root";

QueryOK, 0 rows affected (0.00 sec)

Rowsmatched: 6  Changed: 0  Warnings: 0

mysql>flush privileges;

QueryOK, 0 rows affected (0.00 sec)

mysql>exit

Bye

[sky@sky9896~]$ service mysqld restart

cat:/var/run/mysqld/mysqld.pid:权限不够

停止 mysqld：                                              [失败]

正在启动 mysqld：                                          [确定]

[sky@sky9896~]$ su - root

密码：

[root@sky9896~]# service mysqld restart

停止 mysqld：                                              [确定]

正在启动 mysqld：                                          [确定]

[root@sky9896~]# mysql

ERROR1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@sky9896~]# mysql -u root -p

Enterpassword:

Welcometo the MySQL monitor.  Commands end with; or \g.

YourMySQL connection id is 4

Serverversion: 5.5.41-cll-lve MySQL Community Server (GPL) by Atomicorp

Copyright(c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracleis 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>

-------无法更新密码，后面是通过mysql –A 启动方式解决-------------

[root@jxatei/]# mysql -h 117.40.239.9 -u root -p

Enterpassword:

Welcometo the MySQL monitor.  Commands end with; or \g.

YourMySQL connection id is 194

Serverversion: 5.5.41-cll-lve MySQL Community Server (GPL) by Atomicorp

Copyright(c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracleis 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 |

|back20150625ultrax |

|feifeicms          |

|mysql              |

|performance_schema |

|test               |

|tt                 |

|ultrax             |

+--------------------+

8rows in set (0.01 sec)

小结：远程连接MySQL数据库，要做好几件事情，1，做好grant相关授权；2.不能完全按网络上的抄，例如：服务器端配置的不同，远程连接的方式也稍有不同，例如，我就没有用3306端口，用了3306端口反而连不上；3.使用网络上的资料需要谨慎，一不心会生产更多的问题。