Shulou(Shulou.com)06/01 Report--

Operating environment:

Operating system: CentOS release 6.8 (Final)

Database: MySQL 5.5.52-cll-lve

Web application software: Apache/2.2.15 (Unix)

Problem: manipulate the user table of the mysql database, plan to delete a duplicate record, accidentally delete the entire user table, the command at that time is: delete from mysql.user; forgot to add the where condition. Finally, the service of the examination system, forum and monitoring system on the Linux system is suspended.

Strong construction: backing up data regularly can avoid many risks and losses.

First review the entire operation on the server:

Mysql > select * from mysql.user

22rows in set (0.01 sec) # this user table stores 22 records

Mysql > delete from mysql.user; # forgot to add the where condition

QueryOK, 22 rows affected (0.00 sec) # deleted 22 records directly

Mysql > exit

Bye

[root@sky9896data] # mysql-u root-p

Enterpassword:

ERROR1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) # cannot log in to the database system

Problem solving process:

[root@sky9896lib] # find /-name mysql # find the database installation path

/ var/webmin/modules/mysql

/ var/lib/mysql

/ var/lib/mysql/mysql

/ var/lib/mysql20150811/mysql

/ usr/bin/mysql

/ usr/include/mysql

/ usr/include/mysql/mysql

/ usr/lib64/mysql

/ usr/share/mysql

[root@sky9896mysql] # cd / etc

[root@sky9896etc] # vi my.cnf

[mysqld]

Add this statement to skip-grant-table # to skip the authorization table restrictions without requiring a password to log in to the database

[root@sky9896data] # mysql-uroot-p

Enterpassword:

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | a |

| | back20150625ultrax |

| | cacti |

| | cacti20151220 |

| | cacti20160104 |

| | edusoho |

| | feifeicms |

| | mysql |

| | performance_schema |

| | phpcom |

| | qibosoft |

| | qibosofts |

| | skyboy |

| | study |

| | syslog |

| | test |

| | test1 |

| | tt |

| | ultrax |

| | zabbix |

+-+

21rows in set (0.02 sec)

Mysql > usemsyql

Mysql > UPDATE user SET Password=PASSWORD ('* *') WHERE user='root'

Mysql > flushprivileges

Mysql > exit

# mysql-uroot-p

The new problem of mysql > show databases; # is that after updating the password, you can't see most of the databases. The problem is that the mysql.user table does not grant good authority to the database.

+-+

| | Database |

+-+

| | information_schema |

| | cacti |

+-+

2rows in set (0.00 sec)

Tested in a variety of ways, and finally by rebuilding the user table

# service mysqld stop

# mysqld_safe-skip-grant-tables

We also need to reopen a ssh connection:

# mysql

# delete mysql

# INSERT INTO `user` (`Host`, `User`, `Password`, `Select_ priv`, `Password`, `Update_ priv`, `Delete_ priv`, `Shutdown_ priv`, `Drop_ priv`, `Reload_ priv`, `Shutdown_ priv`, `Process_ priv`, `File_ priv`, `Grant_ priv`, `References_ priv`, `Index_ priv`, `Alter_ priv`, `Show_db_ priv`, `Super_ priv`, `Create_tmp_table_ priv`, `Lock_tables_ priv`, `Execute_ priv`, `Repl_slave_ priv`, `Repl_client_ priv`, `Create_view_ priv`, `Show_view_ priv`, `Create_routine_ priv`, `Alter_routine_ priv` `Create_user_ priv`, `Event_ priv`, `Trigger_ priv`, `Create_tablespace_ priv`, `ssl_ type`, `ssl_ cipher`, `x509 _ issuer`, `x509 _ Secrett`, `max_ connecs`, `max_ updates`, `max_ connections`, `max_user_ connections`, `plugin`, `authentication_ string`) VALUES ('%', 'root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y') 'Yaoyun, Yaoyun, Yaoyui, Yaoyun, Yaoyui, Yaoyun, Yaoyuzhuai, Yaoyumi, Yaoyu

('127.0.0.1ZZZHANGZHANGZHANG, YYOTHANGZHANG, YYOTHING, YYOTHY, YOOTHY, YOOTHY, YYOYONEI, YYOYONEI, YYOYOBENTS, YYOYOYOYOYONEHANG, YYOYOYOYOYOYOONEHAND, YAYOYOYOOYOYOYOYOYOYOYOYONEHAND YAYOYONEHANG, YAYOYOYOYOYYOOONY, YOOYOYOYOOOOOOOOOYOOOOYOYOYOYOYOYOYOYOYOYOYOYOYOYOYOYONE 'Yhere 's a lot of money.')

(':: 1). (':: 1) I know that I know that I know how to do things like this. (': 1.) '', 0pr 0pr 0pr 0pl 0pr 0pl 0pr 0pl

('localhost','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','' '', 0pr 0pr 0pr 0pl 0pr 0pl 0pr 0pl

# flush privileges

# mysql restart mysql

# mysql-uroot-p

Mysql > use mysql

Mysql > update user setpassword=password ("* *") where host= "localhost" and user= "root"

Mysql > updateuser set password=password ("* *") where host= "%" and user= "root"

Mysql > flushprivileges

Through the above operations, you must log in to the database through a password, and the corresponding permissions are granted to the database, and all databases can be seen in the database system. In the business system configuration file, it should be noted that the user name and password must be consistent with the database system, otherwise the business application system will not be connected to the database system.

In the process of solving the whole problem, we should pay attention to several things:

1. The data is very important, be sure to make a good backup

two。 It is necessary to master the security theory of the database and be proficient in the operation of actual combat authorization.

3. Leave enough disk space, generally less than a few GB bytes, so as to ensure that the operating system, web services, database services run stably, files and logs are writable and other operations.

In the process of solving the whole problem, we should pay attention to several problems:

1. RROR 2002 (HY000): Can't connect to localMySQL server through socket'/ var/lib/mysql/mysql.sock'

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

3. ERROR 2002 (HY000): Can't connect to localMySQL server through socket'/ var/lib/mysql/mysql.sock' (2)

The reason for the above three problems is that the mysql service cannot be started, which is delayed during the startup process. In this case, Apache also causes it to fail to run automatically. In some places, it is said that the disk space is full. It is true that deleting the log files in the / var/log/httpd/ directory can indeed start the mysql service. But in fact, the root directory is full of disk space that causes the service to fail to start.

[root@sky9896] # df-h

Filesystem Size Used Avail Use% Mounted on

/ dev/sda2 49G 46G 0 100% /

Tmpfs 939M 904K 938M1% / dev/shm

/ dev/sda1 93M 54M 35M 61% / boot

/ dev/sda5 179G 2.2G 168G 2% / home

At that time, the root directory was indeed full, and the httpd service could not be started automatically, so it had to be started manually. Later, you can delete the relevant information in the "/" directory and use more than 300 megabytes, so both mysql and Apache service lines can run normally.

[root@sky9896 www] # df-h

Filesystem Size Used Avail Use% Mounted on

/ dev/sda2 49G 46G 381m 100% /

Tmpfs 939M 100K 939M 1% / dev/shm

/ dev/sda1 93M 54M 35M 61% / boot

/ dev/sda5 179G 2.2G 168G 2% / home

So far, the mysql.user table has been completed, the database has updated the password and authorized the user, vacated the "/" disk space, ensured the normal operation of the mysql and apache services, and configured each business application system at the same time, successfully connected to the database and restored the business application system.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.