MySQL database restoration method (MyISAM/InnoDB)

I found a MySQL technical article on the Internet. It feels good. I have translated it and shared it.
 
Author: Mike Peters
 
I have sorted out seven methods for restoring the MySQL database. When a simple restart does not work for the database or the table crashes.
 
Simple MySQL restart:
 
/Usr/local/mysql/bin/mysqladmin-uUSERNAME-pPASSWORD shutdown
/Usr/local/mysql/bin/mysqld_safe &
 
1. MyISAM Table crash
 
MySQL Databases allow different tables to use different storage engines. It is used to store and retrieve data. The popular storage engines are MyISAM and InnoDB.
 
The MyISAM table eventually crashes. This is an indisputable fact.
 
Fortunately, in most cases, MyISAM Table crashes and is easily repaired.
 
Fix a single table and connect to your database for execution:
 
Repair TABLENAME
 
Fix 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 that you add this line in your/etc/my. cnf configuration file. Once the table crashes, it will be automatically repaired.
 
[Mysqld]
Myisam-recover = backup, force
 
If this does not work, you can try other methods.
 
2. Multi-instance MySQL
 
After you restart MySQL, the process will immediately die, which is common.
View the log file, which tells you that another MySQL instance may be running.
 
Stop all MySQL instances:
 
/Usr/local/mysql/bin/mysqladmin-uUSERNAME-pPASSWORD shutdown
Killall mysql
Killall mysqld
 
Restart the database. Only one instance is running.
 
3. Change 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: 10 M: 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 it changes, the database cannot be started.
 
4. MySQL host table loss
 
I have seen such a situation several times. It may be a number of MyISAM bugs that cannot be imagined.
 
You can easily fix it as follows:
 
/Usr/local/bin/mysql_install_db
 
5. Abnormal automatic MyISAM growth (auto_increment)
 
If the auto-increment count of the MyISAM table becomes disordered, you cannot insert new records.
Generally, you can tell the auto-increment counter that it is not working properly. You can set the auto-increment field of the last record to-1.
 
Solve the problem-find the valid value of the last auto-increment record (execute the following command)
 
SELECT max (id) from tablename
 
Then update the auto-increment counter of the table as follows:
 
Alter table tablename AUTO_INCREMENT = id + 1
 
6. Too many connections
 
The database gets quite busy because the number of connections is more than it can handle. And now you cannot connect to your database.
First, stop the database:
 
/Usr/local/mysql/bin/mysqladmin-uUSERNAME-pPASSWORD shutdown
 
If the previous command does not work, try "killall mysql" and "killall mysqld"
When the database is stopped, edit the/etc/my. cnf file to increase the number of connections. Do not add this number to your head, or you will crash your entire machine.
 
On a dedicated database machine, we usually use:
 
Max_connections = 200
Wait_timeout = 100
 
Try restarting the database to see if it is helpful.
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, enable the database, and modify the database. Then modify the port Back (master-port = 3306) and restart.
 
7. InnoDB table crash
 
InnoDB tables are my favorite. Transaction caching is reliable. Unlike MyISAM, InnoDB supports concurrent writes to the same table.
 
The internal recovery mechanism of InnoDB is also quite good. If the database crashes, InnoDB will try to fix it and run the log file from the last timestamp. In most cases, the operation is successful and the entire process is transparent.
 
However, if InnoDB fails to be repaired by itself, the entire database cannot be started. MySQL will issue an error message and exit. Your entire database will be offline. You can try to restart the database constantly, but if the repair process fails, the database will refuse to start.
 
This is why we need to run the master/master when InnoDB is used-when a master is down, there is also a redundant master for backup.
 
Before proceeding, check the MySQL Log File to make sure that the database does not crash because of the InnoDB table crash.
 
One way is to update the log file counter of InnoDB to skip the query that caused the crash, but experience tells us that this is not a good method. In this case, the data is inconsistent and the master-slave replication is often interrupted.
 
Once the database cannot be started because of InnoDB crash, you should follow the following five steps to solve the problem:
 
First, add this row to the/etc/my. cnf file:
 
[Mysqld]
Innodb_force_recovery = 4
 
Second, restart MySQL. Your database will be started now, but with the innodb_force_recovery parameter, all insert and update operations will be ignored.
 
Third: export all tables (Dump all tables)
 
4. 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 database should be started properly now)
 
6. Restore all data from the backup file.
 
Continued:
Recently, I encountered a tricky task-fixing a failed InnoDB database. The database cannot be started due to a crash.
 
Step 1: Enable InnoDB in force-recovery mode. When InnoDB is enabled, all UPDATEs and INSERTs are ignored.
 
Add this line to the/etc/my. cnf file:
 
Innodb_force_recovery = 2
 
Restart the database now:
 
/Usr/local/bin/mysqld_safe &
 
(Note: If MySQL is not started, add the innodb_force_recovery value until the parameter value is set to 8 (innodb_force_recovery =)
 
Save all data to the temporary file alldb. SQL (the next command takes some time ):
 
Mysqldump -- force -- compress -- triggers -- routines -- create-options-uUSERNAME-pPASSWORD -- all-databases>/usr/alldb. SQL
 
Close 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 and you must delete the correct folder .)
 
Rm-fdr/usr/local/var
 
 
Recreate the database folder and install the MySQL basic 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
 
 
Delete innodb_force_recovery from the/etc/my. cnf file and restart the database:
 
/Usr/local/bin/mysqld_safe &
 
 
Import all backup files (the next command takes some time ):
 
Mysql-uroot -- compress </usr/alldb. SQL
 
 
Finally, refresh the MySQL permissions (because we have also updated the MySQL table)
 
/Usr/local/bin/mysqladmin-uroot flush-privileges
 
Note: add port = 8819 (or any other random port) to/etc/my to get the best result. in the cnf file, add -- port = 8819 to the mysqldump command before restarting MySQL. This method avoids MySQL databases from being too busy when the repair process is in progress.