Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to repair the MySQL database, the article is very detailed, has a certain reference value, interested friends must read it!

I've sorted out seven ways to fix MySQL when a simple restart doesn't work on the database, or when a table crashes.

Simple MySQL restart:

/ usr/local//bin/mysqladmin-uUSERNAME-pPASSWORD shutdown

/ usr/local/mysql/bin/mysqld_safe &

1. MyISAM table crashes

MySQL databases allow different tables to use different storage engines. It is used to store and retrieve data. The more popular storage engines are MyISAM and InnoDB.

The MyISAM table will eventually crash. This is an indisputable fact.

Fortunately, in most cases, MyISAM table crashes are easy to fix.

Repair a single table and connect to your database to execute:

Repair TABLENAME

Repair all tables and execute:

/ usr/local/mysql/bin/mysqlcheck-all-databases-uUSERNAME-pPASSWORD-r

In most cases, the MyISAM table crashes only when you browse the log file.

I strongly recommend adding this line to your / etc/my.cnf configuration file. Once the table crashes, it will be repaired automatically.

[mysqld]

Myisam-recover=backup,force

If this doesn't work, there are other ways to try.

2. Multi-instance MySQL

It is common that when you restart MySQL, the process dies immediately.

Look at the log file and it will tell you that another instance of MySQL may be running.

Stop all MySQL instances:

/ usr/local/mysql/bin/mysqladmin-uUSERNAME-pPASSWORD shutdown

Killall mysql

Killall mysqld

Now restart the database and there will be only one instance running.

3. Change the InnoDB log settings

Once the MySQL database is running the InnoDB engine, you must not modify the following lines in the / etc/my.cnf file:

Datadir = / usr/local/mysql/data

Innodb_data_home_dir = / usr/local/mysql/data

Innodb_data_file_path = ibdata1:10M:autoextend

Innodb_log_group_home_dir = / usr/local/mysql/data

Innodb_log_files_in_group = 2

Innodb_log_file_size = 5242880

Once the InnoDB log file size is determined, it cannot be modified. If changed, the database will not start.

4. MySQL host table is missing

I've seen this a few times. It could be some unexpected MyISAM bug.

It can be easily fixed as follows:

/ usr/local/bin/mysql_install_db

5. Abnormal MyISAM automatic growth (auto_increment)

If the self-increment count of the MyISAM table becomes disordered, you can no longer insert new records.

Usually you can tell the self-increment counter that it is not working properly by setting the self-increment field of the last record to-1.

Resolve the problem-find the valid value of the last self-increasing record (execute the following command)

SELECT max (id) from tablename

Then update the self-increment counter for this table as follows:

ALTER TABLE tablename AUTO_INCREMENT = id+1

6. Too many connections

The database becomes quite busy because there are more connections than it can handle. And now you can't even connect to your database.

First, stop the database:

/ usr/local/mysql/bin/mysqladmin-uUSERNAME-pPASSWORD shutdown

If the previous command doesn't work, try "killall mysql" and "killall mysqld"

When the database stops, edit the / etc/my.cnf file to increase the number of connections. Don't crazily increase this number, or you'll ruin your whole machine.

On a dedicated database machine, we usually use:

Max_connections = 200

Wait_timeout = 100

Try to restart the database and see if it helps.

If you are caught off guard by the query and need to connect to the database for table modification, set a different port number in the / etc/my.cnf file, open the database, and modify. Then modify the port back (master-port = 3306) and restart it.

7. InnoDB table crashes

The InnoDB watch is my favorite. Transaction caching is reliable, unlike MyISAM,InnoDB, which supports concurrent writes to the same table.

InnoDB's internal recovery mechanism is also pretty good. If the database crashes, InnoDB will try to fix it by running the log file from the last timestamp. In most cases, it will succeed, and the whole process is transparent.

However, if InnoDB fails to repair itself, the "whole" database will not start. MySQL will send an error message and exit, and your entire library will be offline. You can keep trying to restart the database, but if the repair process fails, the database will refuse to start.

This is why you need to run master/master when using InnoDB-- when a master goes down, there is a redundant master as a backup.

Before continuing, browse through the MySQL log file to make sure that the database is not crashed because of the crash of the InnoDB table.

One way is to update InnoDB's log file counters to skip queries that cause crashes, but experience tells us that this is not a good approach. In this case, it will cause data inconsistency and often interrupt master-slave replication.

Once the database cannot be started due to an InnoDB crash, you should follow these five steps:

First: add this line to the / etc/my.cnf file:

[mysqld]

Innodb_force_recovery = 4

Second: restart MySQL. Your database will start now, but all inserts and updates will be ignored under the innodb_force_recovery parameter.

Third: export all tables (Dump all tables)

Fourth: close the database and delete all data files. Run mysql_install_db to create the default MySQL table.

Fifth: remove the innodb_force_recovery parameter from the / etc/my.cnf file and restart the database. (the library should now start normally)

Sixth: restore all data from the backup file.

Continue:

Recently I was faced with the tricky task of repairing a failed InnoDB database. The database could not be started because of a crash.

The first step is to open InnoDB in force-recovery mode, where InnoDB is enabled but all UPDATEs and INSERTs operations are ignored.

Add this line to the / etc/my.cnf file:

Innodb_force_recovery = 2

Now restart the database:

/ usr/local/bin/mysqld_safe &

(note: if MySQL is not started, continue to increase the value of innodb_force_recovery until the parameter value is set to 8 (innodb_force_recovery =)

Save all data to the temporary file alldb.sql (it will take some time for the next command):

Mysqldump-force-compress-triggers-routines-create-options-uUSERNAME-pPASSWORD-all-databases > / usr/alldb.sql

Shut down the database again:

Mysqladmin-uUSERNAME-pPASSWORD shutdown

Delete the database directory. (note: my data directory is under / usr/local/var. Your settings may be different. Make sure you delete the correct folder.)

Rm-fdr / usr/local/var

Rebuild the database folder and install the MySQL base table

Mkdir / usr/local/var

Chown-R mysql:mysql / usr/local/var

/ usr/local/bin/mysql_install_db

Chown-R mysql:mysql / usr/local/var

Remove innodb_force_recovery from the / etc/my.cnf file and restart the database:

/ usr/local/bin/mysqld_safe &

Import all backup files (it will take some time for the next command):

Mysql-uroot-- compress < / usr/alldb.sql

Finally, refresh the permissions of MySQL (because we also updated the table of MySQL)

/ usr/local/bin/mysqladmin-uroot flush-privileges

Note: for best results, add port=8819 (or any other random port) to the / etc/my.cnf file before restarting MySQL, and then add-- port=8819 to the mysqldump command. This approach prevents the MySQL database from being too busy when the repair process is in progress.

The above is all the contents of the article "how to repair MySQL Database". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.