Set up the third mysql server to be slave of the running master-master mysql database

Jephe Wu -

Environment: master(db1)-master(db2) mysql 5.5 database (replicate each other) running Oracle Linux 5.6 64bit
Objective: setup a third slave only database(db3, mysql 5.1 on CentOS 6) to sync with one of the master(db2) which the zabbix application is connecting to


a. stop slave on db1 and db2 (mysql -uroot -ppass; sql> stop salve)
b. use mysqldump to make snapshot on db1
c. import data into db3
d. make db3 to sync with db2


1. stop slave on both db1 and db2
mysql -uroot -ppass
sql> stop slave;
sql> show slave status\G

3. setup db3 on CentOS 6 64bit, use /srv/mysql/data as data folder

sh /usr/bin/mysql_install_db --user=mysql --datadir=/srv/mysql/data

copy back /etc/my.cnf mysqld part from db2 or db1

/etc/init.d/mysqld restart
chkconfig mysqld on
chkconfig --list mysqld

[root@db03 mysql]# tail -f /srv/mysql/data/db03.domainnameofserver.err
/usr/libexec/mysqld: Can't create/write to file '/tmpfs/ibJw77ig' (Errcode: 2)
111207 14:19:01  InnoDB: Error: unable to create temporary file; errno: 2
111207 14:19:01 [ERROR] Plugin 'InnoDB' init function returned error.
111207 14:19:01 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
111207 14:19:01 [ERROR] Unknown/unsupported table type: innodb
111207 14:19:01 [ERROR] Aborting

111207 14:19:01 [Note] /usr/libexec/mysqld: Shutdown complete

111207 14:19:01 mysqld_safe mysqld from pid file /var/run/mysqld/ ended

solution=> change /etc/my.cnf /tmpfs to /dev/shm (according to df -h)

4. use mysqldump to make snapshot

on db01,do:
stop slave;
show slave status\G

note: record down about output for master_log_file and position so that db3 will use it to sync with db2 again after importing data

mysqldump -u root -ppass zabbix [--lock-all-tables] | gzip -c | ssh db03 'cat > /srv/mysql/mysqldump_on_db01.gz'

please refer to online doc at

5. import data into db3
zcat mysqldump_on_db01.gz | mysql -uroot -ppass zabbix

6. configuring db3 as slave for db2
/etc/init.d/mysqld start
sql> show slave status\G
empty record
sql> change master to master_host='', master_user='replication', master_password='password',master_log_file='mysql-bin.001080',master_log_pos=50862743;
sql> show slave status\G

7. FAQ
After downgrading to 5.1 and make mysql 5.1 as master database,  if you try to create partition, you might encounter the following error:
ERROR 1548 (HY000) at line 1: Cannot load from mysql.proc. The table is probably corrupted

Solution: on mysql 5.1, run "mysql_upgrade -uroot -ppassword", please refer to

8. References

a. database partitioning:
b. mysql 5.5 online manual for replication at