Silicon Technix

MySQL replication is quite straight forward. Here are the setup steps:

1. Create a user for replication at master.

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

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| bin-log.000074 | 915 | | |
+----------------+----------+--------------+------------------+
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

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

mysql> unlock tables;
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.

log-bin=mysql-bin
relay-log = relay-log
relay-log-index = relay-log.index

2) Set master

mysql> change master to
-> master_host='blogs.silicontechnix.com',
-> master_user='repluser',
-> master_password='',
-> master_port = 3306,
-> master_log_file='bin-log.000074',
-> master_log_pos=915;
Query OK, 0 rows affected (0.00 sec)
--use the log file name and postion we got previously

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: blogs.silicontechnix.com
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin-log.000074
Read_Master_Log_Pos: 1199
Relay_Log_File: relay-log.000003
Relay_Log_Pos: 533
Relay_Master_Log_File: bin-log.000074
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1199
Relay_Log_Space: 682
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
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 :

mysql> show master status;
+----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| bin-log.000077 | 106 | | |
+----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

At master:

mysql> change master to
-> master_host='blogs.silicontechnix.com',
-> master_user='repluser',
-> master_password='',
-> master_port = 3306,
-> master_log_file='bin-log.000077',
-> master_log_pos=106;
Query OK, 0 rows affected (0.03 sec)

mysql> start slave;
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:

$ 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

(cd $MYSQL_BASE; ./bin/mysqld_safe &)
start slave;

–Do NOT need to run “change master to …” statement as it already in master.info and relay-log.info