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

Preface

In the daily development or maintenance of MySQL, some problems or failures are often unavoidable, such as lost password and table corruption. Here is a summary of common problems for future needs.

Forget the root password of MySQL

1. Log in to the server where the database is located and manually kill the 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`

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:

The table_name.frm' is locked cannot be changed and the file cannot be found. The tbl_name.MYYI' (errcode:nnn) file ended unexpectedly. The record file was destroyed to 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:

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 locally on the MySQL server, there is often a problem that mysql.sock does not exist, resulting in the inability 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

6. The MyISAM table is too large to be accessed.

First of all, we can view the MyISAM table through the myisamchk command. As shown in the following figure, I look at the admin table

Datefile length represents current file size keyfile length represents index file size max datefile length maximum file size max keyfile length maximum index size

You can expand the data file size with the following command

Alter table table_name MAX_ROWS=88888888 AVG_ROW_LE=66666

The problem of insufficient disk space in the data directory

For MyISAM storage engine

You can store data directories and index directories in different disk spaces.

For InnoDB storage engine

For InnoDB storage engine tables, because data files and index files are stored together. So they can't 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 is to add this file to InnoDB_data_file_path.

Innodb_data_file_path=/home/mysql/data:10000M;/user/mysql/data:10000M:autoextend

After the parameters are modified, the server needs to be restarted before it can take effect.

8. Install multiple Mysql on the same host

In addition to the fact that each Mysql installation directory cannot be the same, it is also required that port and socket cannot be the same.

Mysql.sock is used for communication between client connection and mysql. Socket file, can only be used locally, remote connection through the tcp/ip.

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

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.