IT Tips and Tricks

On mysql01 (MASTER):
——————–
mysqldump -u root -p –single-transaction –flush-logs –master-data=2 –all-databases –delete-master-logs
| gzip | ssh mysql02 ‘cat | gunzip >/var/lib/mysql_temp.sql’

echo “SHOW MASTER STATUS;” | mysql -p -u root

Note the filename eg. mysql-bin.000299
we will use it later on mysql02

On mysql02 (SLAVE):
——————-
– Changed the following config is in /etc/mysql/my.cnf

FROM:
slave-skip-errors=1045
TO:
——————————
slave-skip-errors=1045,126,1062
——————————
126 = the ambiguous ‘unknown error on master’ typically caused by tables in dis-repair on master.
1062 = duplicate entry (happens all the time on high capacity db in master-master setup)

– Using phpmyadmin, delete all databases except ‘mysql’ and ‘Information schema’

– echo “STOP SLAVE; RESET SLAVE;” | mysql -p -u root

– mysql -p -u root < /var/lib/mysql_temp.sql <-----SQL file produced by mysqldump in mysql01 - mysql -p -u root > CHANGE MASTER TO master_log_file=’mysql-bin.000299′,master_log_pos=4;
Note: ‘mysql-bin.000299’ is the current bin log filename on master above

> START SLAVE;
> SHOW SLAVE STATUS \G;

Value of Read_Master_Log_Pos: should = Exec_Master_Log_Pos: value.
Repeat the command a few times again and each time check if they follow each other.