Fix MySQL databases (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 to fix the MySQL database. When a simple restart does not work for the database or a table crashes. Simple MySQL restart: /usr/local/mysql/bin/mysqladmin-uUSERNAME-pPASSWORD shutdown/usr/local/mysql/bin/mysqld_safe & 1. MyISAM Table crash MySQL database allows different tables to use different storage engine. 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. Execute: repair TABLENAME to 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 doesn't work, try other methods. 2. Multi-instance MySQL when you restart MySQL, the process will immediately die, which is very 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 and only one instance is running. 3. Change the InnoDB log settings. Once the MySQL database is running the InnoDB engine, you must not modify/etc/my. the cnf file contains the following lines: 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. the MySQL host table has been lost several times. It may be a number of MyISAM bugs that cannot be imagined. It can be easily fixed as follows:/usr/local/bin/mysql_install_db 5. Abnormal auto-increment MyISAM (auto_increment) if the auto-increment count of MyISAM tables becomes disordered, you cannot insert a new record. 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. Solution-find the valid value of the last auto-increment record (execute the following command) SELECT max (id) from tablename and then update the auto-increment counter of this table, as shown below: alter table tablename AUTO_INCREMENT = id + 1 6. Too many connections to the Database become quite busy, because the number of connections is more than it can process. 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, you can try "killall mysql" and "killall mysqld". When the database is stopped, edit/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 to restart 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 table is 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/etc/my. in the cnf file: [mysqld] innodb_force_recovery = 4 2: 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) 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 database should be properly started now) 6. Restore all data from the backup file. Continued: I recently 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. In/etc/my. add this line to the 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 you set the parameter value to 8 (innodb_force_recovery =) and save all the 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 closes the database again: mysqladmin-uUSERNAME-pPASSWORD shutdown to 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 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 from/etc/my. delete innodb_force_renf In the 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. at the end of the SQL statement, refresh the MySQL permissions (because we have also updated the MySQL table)/usr/local/bin/mysqladmin-uroot flush-privileges. Note: To get the best result If port = 8819 (or any other random port) is added to the/etc/my. 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. Author mushme