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> exit
Bye
[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 4
Server version: 5.5.18-log Source distribution

Copyright (c) 2000, 2011, 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> \s
--------------
mysql  Ver 14.14 Distrib 5.5.18, for Linux (x86_64) using readline 5.1

Connection id:          4
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.18-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql3306.sock
Uptime:                 30 min 18 sec

Threads: 3  Questions: 64  Slow queries: 2  Opens: 48  Flush tables: 1  Open tables: 41  Queries per second avg: 0.035
--------------

mysql> exit
Bye
[root@localhost data]# ll
total 8
drwxr-xr-x. 3 root  root  4096 Jan 22 20:40 logs
drwxr-xr-x. 3 mysql mysql 4096 Jan 22 17:31 mysql
[root@localhost data]# cd /tmp/
[root@localhost tmp]# ll
total 20
srwxrwxrwx. 1 mysql mysql    0 Jan 22 20:45 mysql3306.sock
drwx------. 2 root  root  4096 Jan 22 16:30 ssh-kLGZrU1461
drwxr-xr-x. 2 root  root  4096 Jan 23  2013 vmware-config0
drwxrwxrwt. 2 root  root  4096 Jan 23  2013 VMwareDnD
drwxr-xr-x. 2 root  root  4096 Jan 23  2013 vmware-fonts0
drwx------. 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]# ll
total 20
drwx------. 2 root root 4096 Jan 22 16:30 ssh-kLGZrU1461
drwxr-xr-x. 2 root root 4096 Jan 23  2013 vmware-config0
drwxrwxrwt. 2 root root 4096 Jan 23  2013 VMwareDnD
drwxr-xr-x. 2 root root 4096 Jan 23  2013 vmware-fonts0
drwx------. 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 -P3306
Enter 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 -P3306
Enter 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 -P3306
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql3306.sock' (2)
[root@localhost tmp]# ll
total 20
drwx------. 2 root root 4096 Jan 22 16:30 ssh-kLGZrU1461
drwxr-xr-x. 2 root root 4096 Jan 23  2013 vmware-config0
drwxrwxrwt. 2 root root 4096 Jan 23  2013 VMwareDnD
drwxr-xr-x. 2 root root 4096 Jan 23  2013 vmware-fonts0
drwx------. 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 -P3306
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.18-log Source distribution

Copyright (c) 2000, 2011, 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> 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> 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: 0

mysql> 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: Y
Create_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: 0

mysql> insert into mysql.user select * from test.user;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> 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> exit
Bye
[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 7
Server version: 5.5.18-log Source distribution

Copyright (c) 2000, 2011, 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> \s
--------------
mysql  Ver 14.14 Distrib 5.5.18, for Linux (x86_64) using readline 5.1

Connection id:          7
Current database:
Current user:           root@127.0.0.1
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.18-log Source distribution
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 41 min 30 sec

Threads: 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)