Friday, January 2, 2015

MySQL: Restore Master from Slave

I had a power-outage in one of my Linux clusters. The master MySQL server was corrupted and wouldn't start properly. After trying a bunch of bin-log/relay fixes and other failed attempts, I finally decided to uninstall/re-install and that was so quick and simple and everything worked perfectly. Here are the steps for CentOS/RHEL:
 service mysqld stop
 mv /etc/my.cnf /root/
 mv /var/lib/mysql /var/lib/mysql.orig/
 rpm -e mysql-server
 yum -y install mysql-server
 service mysqld start
# set pw and take defaults
 mysqldump -h working-mysql-server -u root --all-databases --quick --lock-all-tables | mysql -u root -p
 mv /root/my.cnf /etc/
 service mysqld restart
Now, check /var/log/mysqld.log for errors. If all is well, run:
 mysql -e 'show slave status\G'
To get the current status.