Summary of common problems and application skills in MySQL

Preface

In the daily development or maintenance of MySQL, some problems or failures are often unavoidable, such as lost passwords and damaged tables. Here is a summary of common problems in case you need them in the future.

1. Forgot MySQL root password

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`

Among them, mysql.pid refers to the pid file in the MySQL data directory, which records the process ID 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 to skip the authorization table authentication when starting the MySQL service. After startup, connecting to MySQL as root will not require a password.

(3) Connect to mysql using 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 3
Server 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 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 [(none)]> set password = password('123456');
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
MySQL [(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: 1

MySQL [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 7
Server 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 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 [(none)]>

Because the --skip-grant-tables option was used to start the system, the password could not be changed using the "set password" command. The password was changed successfully after directly updating the authentication_string field of the user table (the tested version is 5.7.18, and the password field in some versions is 'password'). Refresh the permission table to make the permission authentication effective again. When you log in again as root, you can use the password you just changed.

2. How to deal with table corruption in the MyISAM storage engine

Sometimes you may encounter a situation where the myisam table is damaged. Symptoms of a corrupt table are usually queries that terminate unexpectedly and you see errors like:

• 'table_name.frm' is locked and cannot be modified
• Cannot find file 'tbl_name.MYYI' (errcode:nnn)
• Unexpected end of file
• The log file is corrupted
• 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 it doesn't work, use the command:

shell> mysiamchk -o tablename

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

2. Use sql command

Use MySQL's check table and repair table commands together to perform repairs. Check table is used to check whether the table is damaged; repair table is used to repair damaged tables.

3. Insufficient disk space in the data directory

After the system is launched, as the amount of data continues to increase, it will be found that the available space in the data directory is getting smaller and smaller, which poses a security risk to the application.

1. For tables using the MyISAM storage engine

For tables using the MyISAM storage engine, you can use the following options to specify that the data directory and index directory are stored in different disk spaces when creating the table. By default, they are both stored in the data directory:

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

If the table has been created, you can only shut down or lock the table to prevent changes to the table, then mv the table's data files and index files to a partition with sufficient disk space, and then create symbolic links to the original files.

2. For tables using the innodb storage engine

Because data files and index files are stored together, they cannot be separated. When the disk space is insufficient, you can add a new data file, which will be placed on a disk with sufficient space.

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

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

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

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

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

When you execute the show processlist command on the client, sometimes many processes will appear, similar to the following:

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

These processes will accumulate more and more and will not disappear, and the application will not be able to respond normally, causing the system to crash.

By default, MySQL will perform reverse domain name resolution for remotely connected IP addresses. 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 will appear in the next process and cause the process to be blocked.

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

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

When connecting to a database on the local MySQL server, the problem of mysql.sock not existing often occurs, resulting in a failure to connect. This is because if you specify localhost as a host name, 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. The user can explicitly specify the connection protocol through the --protocol=TCP|SOCKET|PIPE|MEMORY option. The following example shows how to successfully connect using the TCP protocol after a failed Unix socket connection.

1. Unix socket connection:

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

2. TCP connection

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

6. MyISAM table is too large and cannot be accessed

First, we can use the myisamchk command to view the status of the MyISAM table. As shown below, I check the admin table

• datefile length represents the current file size
• keyfile length represents the index file size
• max datefile length Maximum file size
• max keyfile length Maximum index size

You can use the following command to expand the data file size

alter table table_name MAX_ROWS=88888888 AVG_ROW_LE=66666

7. Insufficient disk space in the data directory

For MyISAM storage engine

The data directory and index directory can be stored in different disk spaces.

For InnoDB storage engine

For tables using the InnoDB storage engine, data files and index files are stored together. So it is impossible to separate them. When the disk space is insufficient, you can add a new data file and place it on a disk with sufficient space. The specific implementation is to add this file in InnoDB_data_file_path.

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

After the parameters are modified, you need to restart the server for them to take effect.

8. Install multiple Mysql on the same host

In addition to the fact that each MySQL installation directory must be different, the port and socket must also be different.

mysql.sock is used by the client to communicate with mysql. The socket file can only be used locally, and remote connections must be made via TCP/IP.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.