Mysql recovery

Check MySQL error log on /var/lib/mysql/{hostname.com}.err and first try to fix the error which is related to the issue. Sometimes the issue will be simply fixed by /scripts/restartsrv_mysql or repairing the databases. Check if the engines are showing in MySQL prompt. by show engines; command. If you are unable to start the MySQL and InnoDB engine. First, stop MySQL service. Make sure if the tailwatchd is stopped and the MySQL is not automatically restarted again. # /scripts/restartsrv_tailwatchd stop # service mysql stop Edit /etc/my.cnf and add "innodb_force_recovery = 1" (without quotes) # restart the MySQL "service mysql restart" If the MySQL is not started. you can increase the innodb_force_recovery up to 6. Do not use 6 until really it's needed (Recommended is 3). Check error log and repair if there is any database crash and try to restart without "innodb_force_recovery" in /etc/my.cnf If the mysql is not started without force recovery, then take complete backup of /var/lib/mysql (/var/lib/mysql-current_date). We have to re-add the force recovery to start MySQL "innodb_force_recovery = 1" to /etc/my.cnf and restart the mysql. Take MySQL dump using mysqldump mysqldump --all-databases > all_databases.sql Also, take individual database backups using below script. Use below command to list all database and save to another file. mysql -e 'show databases;' > dblist Check if there is any extra lines added to dblist. This will dump databases individually and report the success and failed database dumps in success.txt and failed.txt. Create file called dump.sh though vi editor and add below scipt to file, --------------------------- #!/bin/bash for i in `cat dblist` do mysqldump --lock-tables=false $i > $i.sql if [ $? -ne 0 ] then echo $i >> failed.txt rm -rf $i.sql else echo $i >> success.txt fi done ---------------------------- Keep the dump.sh and dblist file on seperate directory, because it will create db backups on the same location of the script. change the permission of dump.sh file, chmod 755 dump.sh Run the script file in screen, sh dump.sh After taking a complete backup of all databases, stop the MySQL service. Move and create new MySQL directory. Make sure it's not mounted on any drive. If the /var/lib/mysql is mounted. unmount it. mv /var/lib/mysql /var/lib/mysql-old mkdir /var/lib/mysql chown mysql.mysql /var/lib/mysql -R Copy the MySQL directory from "/var/lib/mysql-old/" there will be another MySQL directory inside old "/var/lib/mysql" which contains database user privileges. cp -rpf /var/lib/mysql-old/mysql /var/lib/mysql/mysql Restore all the database from the backup. If you are unable to restore all database from all-database.sql dump in one go. You have to restore it from the individual backup. ------------------------------------------------ NOTE: You can use below method at your own risk. ------------------------------------------------ Add the individual db list into a file. Then use below script to restore one by one automatically. Create restore.sh using vi editor and change the permission to executable. --------------------------------- #!/bin/bash for i in `cat RestoreDbList` do mysql $i < $i.sql if [ $? -ne 0 ] then echo $i >> restore_failed1.txt rm -rf $i.sql else echo $i >> restore_success.txt fi done --------------------------------- Run the script to restore the individual backup. sh restore.sh This will restore the databases, from RestoreDbList and stores success and failed restorations in separate file. NOTE:- If you are using CloudLinux. You may face SQL or Connection error to the database. you have to remount the Cagefs to make the sites up. /usr/sbin/cagefsctl --remount-all --reinit ================================================================