Mysql Database Repair Method (MYISAM/INNODB) _mysql

On the internet to find a piece of MySQL technical article, feel good, translate it to share under.

Original Author: Mike Peters

I've sorted out 7 ways to fix the MySQL database, when a simple reboot doesn't work for the database, or when a table crashes.

Simple MySQL reboot:

/usr/local/mysql/bin/mysqladmin-uusername-ppassword shutdown
/usr/local/mysql/bin/mysqld_safe &

1, MyISAM table collapse

The MySQL database allows 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 collapse. This is an indisputable fact.

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

Repair a single table and connect your database execution:

Repair TableName

Fix all the tables and execute:

/usr/local/mysql/bin/mysqlcheck--all-databases-uusername-ppassword-r

In most cases, only when you browse the log file do you know that the MyISAM table crashes.
I strongly recommend that you add this line to your/ETC/MY.CNF configuration file. Once the table crashes it will be automatically repaired.

[Mysqld]
Myisam-recover=backup,force

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

2, multi-instance MySQL

This is common when you restart MySQL and the process dies immediately.
View the log file and it will tell you that another MySQL instance may be running.

Stop all MySQL instances:

/usr/local/mysql/bin/mysqladmin-uusername-ppassword shutdown
Killall MySQL
Killall mysqld

Now that the database is restarted, only one instance is running.

3, change the InnoDB log settings
Once the MySQL database is running the InnoDB engine, you must not be able to 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

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

4, the MySQL host table is missing

I've seen this a couple of times. May be some unexpected myisam bugs.

Easily fix it as follows:

/usr/local/bin/mysql_install_db

5, abnormal MyISAM automatic growth (auto_increment)

If the MyISAM count becomes disordered, you cannot insert a new record.
Usually you can tell the self added counter that it's not working properly, by setting the self-add field of the last record to-1.

Resolve the issue-find the valid value for the last self-added record (execute the following command)

SELECT Max (ID) from TableName

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

ALTER TABLE tablename auto_increment = id+1

6, too many connection number

The database becomes quite busy because the number of connections is more than it can handle. And now you can't 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 is stopped, edit the/etc/my.cnf file and increase the number of connections. Don't go crazy with that number, or you'll blow up your whole machine.

On a dedicated database machine, we usually use:

Max_connections = 200
Wait_timeout = 100

Try to focus on the database and see if it helps.
If you are caught unprepared by the query, you need to connect the database for table modification operations, then set a different port number in the/etc/my.cnf file, open the database, modify the operation. Then change the port back (Master-port = 3306) and reboot.

7, InnoDB table collapse

The InnoDB watch is my favorite. Things are cached, reliable, unlike MYISAM,INNODB, which supports concurrent writes to the same table.

InnoDB's internal recovery mechanism is also quite good. If the database crashes, InnoDB will attempt to fix it by running the log file starting with the last timestamp. Most of the cases will succeed and the whole process is transparent.

However, if InnoDB fails to repair itself, the entire database will not start. MySQL will send out 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 it is necessary to run Master/master when using InnoDB-when a master is down, there is also a redundant master backing up.

Before proceeding, browse the MySQL log file to determine if the database crashed because the InnoDB table crashed.

One way to do this is to update the InnoDB log file counter to skip the query that caused the crash, but experience tells us this is not a good way. In this case, the data inconsistency is caused and the master-slave replication is often interrupted.

Once the database fails to start due to a innodb crash, you should deal with the problem in five steps as follows:

First: Add this row to the/etc/my.cnf file:

[Mysqld]
Innodb_force_recovery = 4

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

Third: All tables are exported (Dump all tables)

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

V: Remove the Innodb_force_recovery parameter from the/etc/my.cnf file and restart the database. (Library should now start properly)

Sixth: Recover all data from the backup file.

Continued:
Recently encountered a difficult task--to repair a failed InnoDB database. The database failed to start because of a crash.

The first step is to open the InnoDB in force-recovery mode, when InnoDB is turned on but all updates (UPDATEs) and insert (inserts) operations are ignored.

To add this row to the/etc/my.cnf file:

Innodb_force_recovery = 2

Now restart the database:

/usr/local/bin/mysqld_safe &

(Note: If MySQL does not start, continue to increase the number of Innodb_force_recovery until the parameter value is set to 8 (Innodb_force_recovery =)

Save all data to a temporary file Alldb.sql (the next command takes a certain amount of time):

Mysqldump--force--compress--triggers--routines--create-options-uusername-ppassword--all-databases >/usr/ Alldb.sql

Close the database again:

Mysqladmin-uusername-ppassword shutdown

Deletes the database directory. (Note: My Data directory is under/usr/local/var.) Your settings may be different, make sure that the correct folder is deleted. )

Rm-fdr/usr/local/var


Rebuild database folder, install 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


To remove the innodb_force_recovery from the/etc/my.cnf file, restart the database:

/usr/local/bin/mysqld_safe &


Import all backup files (the next command takes a while):

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


Finally, refresh MySQL's permissions (since we also updated the MySQL table)

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

Note: To get the 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 avoids the MySQL database being too busy when the repair process is in progress.