Common MySQL problems and solutions

As programmers, MySQL is definitely something we will use, and it is very important. However, sometimes some problems will inevitably occur in the MySQL database at work, so how do we deal with them? Let's talk about some common MySQL problems and solutions that we encounter in our daily work.

1. Forgot the root password of MySQL

1. Log in to the server where the database is located and manually kill mysql process.

(1) Log in to the server where the database is located, and manually kill the MySQL process:

root@bogon:/data/mysql# kill `cat ./mysql.pid`

Among them, mysql.pid refers to the pid file in the MySQL data directory, which records the process number of the MySQL service.

(2) Use the --skip-grant-tables option to restart the MySQL service:

zj@bogon:/data/mysql$ sudo /usr/local/mysql/bin/mysqld -- The skip-grant-tables --user=root &

--skip-grant-tables option means to skip the permission table authentication when starting the MySQL service. Once started, root will not require a password to connect to MySQL.

(3) Connect to mysql with the root user with an empty password, and change the root password:

zj@bogon:/usr/local/mysql/bin$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3Server version: 5.7.18-log Source distribution
Copyright (c) 2000, 2017, 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 respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> set password = password('123456');
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statementMySQL [(none)]> use mysql
Database changed
MySQL [mysql]> update user set authentication_string=password('123456') where user="root" and host="localhost";
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1MySQL [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MySQL [mysql]> exit;
Bye
****************************************************************
zj@bogon:/usr/local/mysql/bin$ mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7Server version: 5.7.18-log Source distribution
Copyright (c) 2000, 2017, 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 respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>

Since the --skip-grant-tables option is used to start, use the "set password" command If the password change fails, the password change is successful after directly updating the authentication_string field of the user table (the test version is 5.7.18, the password field of some versions is 'password'). Refresh the permission table to re-validate the permission authentication. When you log in as root again, you can use the password you just changed.

2. How to deal with table damage in the myisam storage engine

Sometimes you may encounter table damage in myisam. Symptoms of a corrupted table are usually that queries are interrupted unexpectedly and the following error is seen:

'table_name.frm' is locked for changes

Cannot find file 'tbl_name.MYYI' (errcode: nnn)

Unexpected end of file

Log file destroyed

Got error nnn from table processor.

There are usually two solutions:

1. Use the myisamchk tool

Use the myisamchk tool that comes with MySQL to repair:

shell> myisamchk -r tablename

The -r parameter means recover. The above method can solve almost all problems. If not, use the command:

shell> mysiamchk -o tablename

The -o parameter means --safe-recover, which allows for safer repair.

2. Use sql command

Use MySQL's check table and repair table commands to repair together. check table is used to check whether the table is damaged; repair table is used to repair the bad table.

3. The problem of insufficient disk space in the data directory

After the system goes online, as the amount of data continues to increase, you will find that the available space in the data directory is getting smaller and larger. Small, thus causing security risks to the application.

1. For the tables of the myisam storage engine

For the tables of the myisam storage engine, you can use the following options when creating the table to separately specify the data directory and index directory to be stored in different disk spaces, and By default, it will be placed in the data directory at the same time:

data directory = 'absolute path to directory'index directory = 'absolute path to directory'

If the table has been created, you can only Stop or lock the table to prevent table changes, then mv the table's data files and index files to a sufficient partition on the disk, and then create a symbolic link in the original file.

2. For tables of innodb storage engine

Because the data files and index files are stored together, they cannot be separated. When disk space is insufficient, a new data file can be added and placed on a disk with sufficient space.
The specific implementation method is to add this file in the parameter innodb_data_file_path, and the path is written as the absolute path of the new disk.
For example, if there is insufficient space under /home and you want to add a new file under /home1 that can automatically expand data, then the parameters can be written like this:

innodb_data_file_path = /home/ibdata1:2000M;/home1 /ibdata2:2000M:autoextend

After the parameters are modified, the database must be restarted to take effect.

4. Problems with DNS reverse resolution (versions after 5.0 skip domain name reverse resolution by default)

Execute the show processlist command on the client, and sometimes many will appear Process, similar to:

unauthenticated user | 192.168.10.10:55644 | null | connect | null | login | null

These processes will accumulate more and more, and will not will disappear and the application cannot respond normally, resulting in system paralysis.

By default, MySQL will perform reverse resolution of the domain name for the IP address of the remote connection. If there is no corresponding domain name in the system's hosts file, MySQL will consider the connection as an invalid user, so An unauthenticated user appears in the next process and causes the process to block.

The solution is very simple. Add the --skip-name-resolve option when starting, then MySQL can skip the domain name resolution process and avoid the above problems.

5. How to connect to the database after mysql.sock is lost

When connecting to the database on the MySQL server itself, it often appears that mysql.sock does not exist, resulting in the inability to connect. The problem. This is because if you specify localhost as a hostname, mysqladmin defaults to using a Unix socket file connection instead of tcp/ip. This socket file (usually named mysql.sock) is often deleted for various reasons. Through the --protocol=TCP|SOCKET|PIPE|MEMORY option, users can explicitly specify the connection protocol. The following demonstrates a successful connection using the tcp protocol after a Unix socket failure.

1. Unix socket connection:

zj@bogon:~$ mysqlERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

2. tcp connection

zj@bogon:~$ mysql --protocol=TCP

this This article shares five problems and solutions that MySQL may encounter. I hope it can help everyone. If you find it useful, please collect it.

Related recommendations:

How to set up the most secure MySQL database?

View the index method of MySQL data table

Questions about MySQL triggers

The above is the detailed content of Common MySQL problems and solutions. For more information, please follow other related articles on the PHP Chinese website!