MySQL replication is quite straight forward. Here are the setup steps:
1. Create a user for replication at master.
1 | mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' identified by ''; |
2. Copy master data directory to slave.
For Innodb, use mysqldump with option –single-transaction to get a consistent dump (you can get the log file and position in the dumpfile), or shutdown the source DB to get a cold consistent copy.
For all other DB engine, following these steps:
1) Lock the tables at master
01 | mysql> FLUSH TABLES WITH READ LOCK; |
02 | Query OK, 0 rows affected (0.00 sec) |
04 | mysql> show master status; |
05 | +----------------+----------+--------------+------------------+ |
06 | | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |
07 | +----------------+----------+--------------+------------------+ |
08 | | bin-log.000074 | 915 | | | |
09 | +----------------+----------+--------------+------------------+ |
10 | 1 row in set (0.00 sec) |
–Copy the log file name and position number, it will be used later
2) Copy the data over
1 | $ rsync -avz /mysql/data :/mysql/data |
Remove file master.info and relay-log.info if you build the slave from Master.
3) After the copy is done, unlock tables at Master:
2 | Query OK, 0 rows affected (0.00 sec) |
or you can exist the session which will release the lock as well.
3. Setup slave
If slave binary is not setup, you can also copy them from master.
1) Startup mysql
a. Remember change the server_id to a different value in my.cnf
b. Binary logging is required for replication. And relay-log is recommended.
3 | relay-log-index = relay-log.index |
2) Set master
2 | -> master_host='blogs.silicontechnix.com', |
3 | -> master_user='repluser', |
6 | -> master_log_file='bin-log.000074', |
8 | Query OK, 0 rows affected (0.00 sec) |
9 | --use the log file name and postion we got previously |
2 | Query OK, 0 rows affected (0.00 sec) |
01 | mysql> show slave status\G; |
02 | *************************** 1. row *************************** |
03 | Slave_IO_State: Waiting for master to send event |
04 | Master_Host: blogs.silicontechnix.com |
08 | Master_Log_File: bin-log.000074 |
09 | Read_Master_Log_Pos: 1199 |
10 | Relay_Log_File: relay-log.000003 |
12 | Relay_Master_Log_File: bin-log.000074 |
18 | Replicate_Ignore_Table: |
19 | Replicate_Wild_Do_Table: |
20 | Replicate_Wild_Ignore_Table: |
24 | Exec_Master_Log_Pos: 1199 |
35 | Seconds_Behind_Master: 0 |
36 | Master_SSL_Verify_Server_Cert: No |
41 | 1 row in set (0.00 sec) |
Now the Master-Slave replication is setup, is it easy 
In addition,
A. If you want to setup Master-Master replication, continue the following steps:
At slave :
1 | mysql> show master status; |
2 | +----------------+----------+--------------+------------------+ |
3 | | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |
4 | +----------------+----------+--------------+------------------+ |
5 | | bin-log.000077 | 106 | | | |
6 | +----------------+----------+--------------+------------------+ |
At master:
2 | -> master_host='blogs.silicontechnix.com', |
3 | -> master_user='repluser', |
6 | -> master_log_file='bin-log.000077', |
8 | Query OK, 0 rows affected (0.03 sec) |
2 | Query OK, 0 rows affected (0.00 sec) |
B. If you want to setup a slave from an existing slave, using the following steps:
* Shutdown the existing slave db
* Copy the data to new slave:
1 | $ rsync -avz /mysql/data <target_host>:/mysql/data |
–Keep file master.info and relay-log.info if you build the slave from another slave.
* Startup mysql at new slave.
make sure it uses a different server_id in my.cnf
1 | (cd $MYSQL_BASE; ./bin/mysqld_safe &) |
–Do NOT need to run “change master to …” statement as it already in master.info and relay-log.info
