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

Jephe Wu - http://linuxtechres.blogspot.com

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


Concept:
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

Steps:
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

Note:
[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/mysqld.pid 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 http://dev.mysql.com/doc/refman/5.0/en/replication-howto-mysqldump.html

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='192.168.0.1', 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 http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html

8. References
a. database partitioning:http://www.slideshare.net/datacharmer/partitions-performance-with-mysql-51-and-55
b. mysql 5.5 online manual for replication at http://dev.mysql.com/doc/refman/5.5/en/replication.html