mysql unix_socket文件丢失处理 mysql.sock被删除

lshboo

　　为了数据库安全，限制了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
[iyunv@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
[iyunv@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
[iyunv@localhost data]# cd /tmp/
[iyunv@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
[iyunv@localhost tmp]# rm -f mysql3306.sock
[iyunv@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
[iyunv@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)
[iyunv@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
[iyunv@localhost tmp]# mysql -uroot -P -P3306            
Unknown suffix '-' used for variable 'port' (value '-P3306')
mysql: Error while setting value '-P3306' to 'port'
[iyunv@localhost tmp]# mysql -uroot -p -P3306
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql3306.sock' (2)
[iyunv@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
[iyunv@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
[iyunv@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)