mysql unix_socket文件丢失处理 mysql.sock被删除
为了数据库安全，限制了mysql中root用户只能本地登录，其他用户不得拥有授权权限。结果悲剧了，登录到一台服务器上发现unix_socket文件不见了。root用户无法登陆。
怎办？情急之下请出DBA用户伪造一个root@127.0.0.1，原地复活。
mysql> select user,host,password from user;+----------+-----------+-------------------------------------------+| user | host | password |+----------+-----------+-------------------------------------------+| root | localhost | *548E9BC80642113DD04156ED194459C8EAC2A08E || xm_dba | 192.168.% | *548E9BC80642113DD04156ED194459C8EAC2A08E || rep_user | 192.168.% | *548E9BC80642113DD04156ED194459C8EAC2A08E |+----------+-----------+-------------------------------------------+3 rows in set (0.00 sec)mysql> exitBye[root@localhost data]# mysql -uroot -p -hlocalhost -P3306 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.5.18-log Source distributionCopyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> \s--------------mysql Ver 14.14 Distrib 5.5.18, for Linux (x86_64) using readline 5.1Connection id: 4Current database:Current user: root@localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ''Using delimiter: ;Server version: 5.5.18-log Source distributionProtocol version: 10Connection: Localhost via UNIX socketServer characterset: utf8Db characterset: utf8Client characterset: utf8Conn. characterset: utf8UNIX socket: /tmp/mysql3306.sockUptime: 30 min 18 secThreads: 3 Questions: 64 Slow queries: 2 Opens: 48 Flush tables: 1 Open tables: 41 Queries per second avg: 0.035--------------mysql> exitBye[root@localhost data]# lltotal 8drwxr-xr-x. 3 root root 4096 Jan 22 20:40 logsdrwxr-xr-x. 3 mysql mysql 4096 Jan 22 17:31 mysql[root@localhost data]# cd /tmp/[root@localhost tmp]# lltotal 20srwxrwxrwx. 1 mysql mysql 0 Jan 22 20:45 mysql3306.sockdrwx------. 2 root root 4096 Jan 22 16:30 ssh-kLGZrU1461drwxr-xr-x. 2 root root 4096 Jan 23 2013 vmware-config0drwxrwxrwt. 2 root root 4096 Jan 23 2013 VMwareDnDdrwxr-xr-x. 2 root root 4096 Jan 23 2013 vmware-fonts0drwx------. 2 root root 4096 Jan 22 16:28 vmware-root-rw-------. 1 root root 0 Jan 22 2013 yum.log[root@localhost tmp]# rm -f mysql3306.sock [root@localhost tmp]# lltotal 20drwx------. 2 root root 4096 Jan 22 16:30 ssh-kLGZrU1461drwxr-xr-x. 2 root root 4096 Jan 23 2013 vmware-config0drwxrwxrwt. 2 root root 4096 Jan 23 2013 VMwareDnDdrwxr-xr-x. 2 root root 4096 Jan 23 2013 vmware-fonts0drwx------. 2 root root 4096 Jan 22 16:28 vmware-root-rw-------. 1 root root 0 Jan 22 2013 yum.log[root@localhost tmp]# mysql -uroot -p -hlocalhost -P3306Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql3306.sock' (2)[root@localhost tmp]# mysql -uroot -p -h127.0.0.1 -P3306Enter password: ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server[root@localhost tmp]# mysql -uroot -P -P3306 Unknown suffix '-' used for variable 'port' (value '-P3306')mysql: Error while setting value '-P3306' to 'port'[root@localhost tmp]# mysql -uroot -p -P3306Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql3306.sock' (2)[root@localhost tmp]# lltotal 20drwx------. 2 root root 4096 Jan 22 16:30 ssh-kLGZrU1461drwxr-xr-x. 2 root root 4096 Jan 23 2013 vmware-config0drwxrwxrwt. 2 root root 4096 Jan 23 2013 VMwareDnDdrwxr-xr-x. 2 root root 4096 Jan 23 2013 vmware-fonts0drwx------. 2 root root 4096 Jan 22 16:28 vmware-root-rw-------. 1 root root 0 Jan 22 2013 yum.log[root@localhost tmp]# mysql -uxm_dba -p -h192.168.161.82 -P3306Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 6Server version: 5.5.18-log Source distributionCopyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select user,host,password from user;+----------+-----------+-------------------------------------------+| user | host | password |+----------+-----------+-------------------------------------------+| root | localhost | *548E9BC80642113DD04156ED194459C8EAC2A08E || xm_dba | 192.168.% | *548E9BC80642113DD04156ED194459C8EAC2A08E || rep_user | 192.168.% | *548E9BC80642113DD04156ED194459C8EAC2A08E |+----------+-----------+-------------------------------------------+3 rows in set (0.00 sec)mysql> FLUSH PRIVILEGES;Query OK, 0 rows affected (0.00 sec)mysql> create table test.user(select * from mysql.user where user='root');Query OK, 1 row affected (0.02 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> select * from test.user\G*************************** 1. row *************************** Host: localhost User: root Password: *548E9BC80642113DD04156ED194459C8EAC2A08E Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: YCreate_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: authentication_string: 1 row in set (0.00 sec)mysql> update test.user set host='127.0.0.1';Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> insert into mysql.user select * from test.user;Query OK, 1 row affected (0.00 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> select user,host,password from user;+----------+-----------+-------------------------------------------+| user | host | password |+----------+-----------+-------------------------------------------+| root | localhost | *548E9BC80642113DD04156ED194459C8EAC2A08E || root | 127.0.0.1 | *548E9BC80642113DD04156ED194459C8EAC2A08E || xm_dba | 192.168.% | *548E9BC80642113DD04156ED194459C8EAC2A08E || rep_user | 192.168.% | *548E9BC80642113DD04156ED194459C8EAC2A08E |+----------+-----------+-------------------------------------------+4 rows in set (0.00 sec)mysql> FLUSH PRIVILEGES;Query OK, 0 rows affected (0.00 sec)mysql> exitBye[root@localhost tmp]# mysql -uroot -p -h127.0.0.1 -P3306 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 7Server version: 5.5.18-log Source distributionCopyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> \s--------------mysql Ver 14.14 Distrib 5.5.18, for Linux (x86_64) using readline 5.1Connection id: 7Current database:Current user: root@127.0.0.1SSL: Not in useCurrent pager: stdoutUsing outfile: ''Using delimiter: ;Server version: 5.5.18-log Source distributionProtocol version: 10Connection: 127.0.0.1 via TCP/IPServer characterset: utf8Db characterset: utf8Client characterset: utf8Conn. characterset: utf8TCP port: 3306Uptime: 41 min 30 secThreads: 3 Questions: 106 Slow queries: 8 Opens: 50 Flush tables: 1 Open tables: 42 Queries per second avg: 0.042--------------mysql> show grants for 'root'@'127.0.0.1';+----------------------------------------------------------------+| Grants for root@127.0.0.1 |+----------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*548E9BC80642113DD04156ED194459C8EAC2A08E' WITH GRANT OPTION |+----------------------------------------------------------------+1 row in set (0.00 sec)>mysql> show grants for 'xm_dba'@'192.168.%';+------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'xm_dba'@'192.168.%' IDENTIFIED BY PASSWORD '*548E9BC80642113DD04156ED194459C8EAC2A08E' |+------------------------------------------------+1 row in set (0.00 sec)
