How to create mysql slave servers

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

Environment:  CentOS 6, Mysql 5.1 64bit
Objective: create one or more mysql slaves from master server.

Steps:

1.  setup master server

Some important configuration in /etc/my.cnf as follows:


server_id=1
log_bin             = /srv/mysql/log/mysql-bin.log 
log_bin_index       = /srv/mysql/log/mysql-bin.log.index 
relay_log           = /srv/mysql/log/mysql-relay-bin 
relay_log_index     = /srv/mysql/log/mysql-relay-bin.index 

log_slave_updates   = 1

expire_logs_days    = 2 

max_binlog_size     = 100M 

binlog-format=MIXED

log-queries-not-using-indexes

2. setup slaves

Method 1:  mysqldump

Refer to http://mysql-mmm.org/mmm2:guide for how to setup master-slave replication.

• since master db is running and it's necessary to flash all tables and lock database to prevent any changes before checking the binlog position.

mysql -uroot -ppassword
mysql> flush tables with read lock;
mysql>show master status\G
Note: DON'T CLOSE this mysql-shell. If you close it, the database lock will be removed. Open a second console and type:

• export data

 $ mysqldump -uroot -ppassword --host=master_host -all-databases [

--master-data=1]  > dumpfile.sql

The  --master-data=1  will record the 'change master to ' statement and write the master database information and position into dump file, so you don't have to use 'change master to' again after import.

• unlock tables from first point

• mysql> UNLOCK TABLES;

• restore dumped file to slave 

$ mysql -uroot -ppassword --host=slave_host < dumpfile.sql

Method 2:  mylvmbackup - http://www.lenzg.net/mylvmbackup/

According to ,mylvmbackup website, 

To perform a backup, mylvmbackup obtains a read lock on all tables and flushes all server caches to disk, creates a snapshot of the volume containing the MySQL data directory, and unlocks the tables again. The snapshot process takes only a small amount of time. When it is done, the server can continue normal operations, while the actual file backup proceeds.

This way, you can use LVM snapshot to do backup, master server can assume operation as quickly as possible.  we can just transfer those snapshoted data and log files to slave site, modify server-id configuration in /etc/my.cnf for slave then start mysqld.

Method 3: shutdown master, copy data

Then change /etc/my.cnf to use different server-id, then start up slave db.
then use 'change master to =' to specify the next master bin log without position, the default position is 4 , mysql knows.

Everytime, you stop then start mysql primary server, it will start from a new binary log file and slave should start to replicate from this new binary log and default position 4.

Method 4: create slave from another slave

If you have the current slave running, you might want to create another slave, you can make clone from the current slave database.

Method 4-1:  flush tables and read lock 

•    firstly, stop slave first, mysql -uroot -ppassword, 

mysql> stop slave;

mysql> flush tables with read lock;

mysql> show slave status;

keep this one, do not close it.

• backup data by using mysqldump
• imported the dumped data into the second slave.
• flush privileges for the second slave after import.
• exit the flush table with read lock session

Method 4-2: mylvmbackup, make LVM snapshot for mysql partition.

Just leave server-id different in my.cnf, everything else should be same.

Method 4-3: stop slave process, shutdown mysqld on first slave, use mylvmbackup to make snapshot then bring server up again(optional), copy snapshot data/log to the second slave;

Method 4-4: stop slave db completely, copy data folder only to the third slave , changed /etc/my.cnf server-id, then startup third slave , that's it

3. master-master 
After you have done first slave and master, you might want to make master as slave for the current slave. You can do so.

run 'show master status\G' on current slave to record down position, then use 'change master to ' on master server to make sync with