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

The following brings you about the common problems and corresponding solutions of MySQL. If you are interested, let's take a look at this article. I believe it will be of some help to you after reading the common problems and corresponding solutions of MySQL.

Forget the root password of MySQL

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

(1) Log in to the CVM where the database resides, and manually kill the MySQL process:

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

Where mysql.pid refers to the pid file under the MySQL data directory, which records the process number of the MySQL service.

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

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

The skip-grant-tables option means that authorization table authentication is skipped when starting the MySQL service. After startup, the root connected to the MySQL will not need a password.

(3) A root user with an empty password connects to mysql and changes the root password:

Zj@bogon:/usr/local/mysql/bin$ mysql-urootWelcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 3Server version: 5.7.18-log Source distributionCopyright (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 respectiveowners.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 mysqlDatabase changedMySQL [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-p123456mysql: [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 distributionCopyright (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 respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MySQL [(none)] >

Due to the use of the-- skip-grant-tables option to start, the password change failed using the "set password" command. After directly updating the authentication_string field of the user table (test version 5.7.18, some version password field is' password'), the password change was successful. Refresh the permissions table to make the authorization effective again. When you log in with root again, you can use the password you just changed.

Second, how to deal with the table corruption of myisam storage engine

Sometimes you may encounter corrupted myisam tables. The symptom of a corrupted table is usually an unexpected interruption of the query, and you can see the following error:

'table_name.frm' is locked and cannot be changed

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

File ends unexpectedly

The record file was destroyed.

Get the error nnn from the table processor.

There are usually two solutions:

1. Use the myisamchk tool

Use the myisamchk tool that comes with MySQL to fix it:

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 can be repaired more securely.

two。 Use the sql command

Use MySQL's check table and repair table commands to repair, check table is used to check tables for corruption, and repair table is used to repair bad tables.

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

After the system is online, with the continuous increase of the amount of data, it will be found that the available space under the data directory is getting smaller and smaller, thus causing security risks to the application.

1. For tables of the myisam storage engine

For tables of myisam storage engine, when creating tables, you can use the following options to set data directories and index directories to store them in different disk spaces, and by default, they will be placed under the data directories 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 changes to the table, then mv the data file and index file of the table to a sufficient partition on disk, and then create a symbolic link at the original file.

two。 For tables of the innodb storage engine

Because the data file and the index file are stored together, they cannot be separated. When there is a shortage of disk space, you can add a new data file, which is placed on a disk with enough space.

The specific implementation method is to add this file to the parameter innodb_data_file_path, and the path is written as the absolute path of the new disk.

For example, if there is not enough space under / home and you want to add a new file under / home1 that automatically expands the data, the parameters can be written as follows:

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

After the parameter is modified, the database must be restarted before it can take effect.

4. Reverse resolution of DNS (domain name reverse resolution is skipped by default in versions later than 5.0)

When the show processlist command is executed on the client, there are sometimes a lot of processes, similar to:

Unauthenticated user | 192.168.10.10 connect 55644 | null | connect | null | login | null

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

By default, MySQL performs reverse domain name resolution for remotely connected IP addresses. If there is no corresponding domain name in the hosts file of the system, MySQL will consider the connection as an invalid user, so unauthenticated user appears in the next process and causes process blocking.

The solution is simple. By adding the-- skip-name-resolve option at startup, 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 local MySQL CVM, there is often a problem that mysql.sock does not exist, resulting in failure to connect. This is because if you specify localhost as a hostname, mysqladmin defaults to using Unix socket file connections instead of tcp/ip. This socket file (commonly named mysql.sock) is often deleted for a variety of reasons. With the-- protocol=TCP | SOCKET | PIPE | MEMORY option, the user can explicitly specify the connection protocol. The following shows an example of a successful connection using the tcp protocol after a failure using the Unix socket.

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

Read the above about MySQL has any common problems and the corresponding solution details, whether there is anything to gain. If you want to know more about it, you can continue to follow our industry information section.

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.