Brief note on the MySQL Master-Slave replication

What to do when you want to analyze data contained in database without overloading the server? The answer is simple as you can use MySQL Master-Slave replication to offload this task. I will describe it briefly here.

Default my.cnf configuration used in this example:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Step 1 - Master server

You need to define unique server id (server-id parameter) in my.cnf configuration file as it will identify this server.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0

server-id=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Step 2 - Master server

Define binary log (log_bin parameter) as it provides a record of the data changes to be sent to slave server. Set database to replicate (binlog-do-db parameter).

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0

server-id=1
log_bin = /var/lib/mysql/mysql-log-bin
binlog_do_db=my_database

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Restart MySQL server:

$ sudo service mysqld restart

Step 3 - Master server

Create MySQL user for replication purpose:

mysql> create user 'replication'@'%' identified by 'replication_password';
mysql> grant replication slave on *.* to 'replication'@'%';

Grant permission to read binary log events from the master:

mysql> show grants for 'replication'@'%';
+--------------------------------------------------------------------------------+
| Grants for replication@%                                                       |
+--------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY PASSWORD '*' |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Reload the privileges from the grant tables:

mysql> flush privileges;

Step 4 - Slave Server

Define unique server id and relay log file (relay-log parameter) in configuration file:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0

server-id=2
relay-log=/var/lib/mysql/relay.log

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Step 5 - Master server

Close all open tables and lock them in desired database:

mysql> use my_database;
mysql> flush tables with read lock;

Dump desired database so you can move it to the slave server:

$ mysqldump -u root -p  my_database > my_database.sql

Step 6 - Slave server

Import database exported in the earlier step:

$ mysql -u root -p my_database < my_database.sql

Step 7 - Master server

Check master status and note File and Position values:

mysql> show master status;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| mysql_bin_log.000001 |      106 |  my_database |                  |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Step 8 - Slave server

Enable replication on the slave server:

mysql> change master to master_host='master_hostname',master_user='replication',master_password='replication_password',master_log_file='mysql_bin_log.000001',master_log_pos=106;

Notice that master_log_file and master_log_pos parameters use values from previous step.

Enable replication:
mysql> start slave;

Step 9 - Master server

Unlock tables:

mysql> use my_database;
mysql> unlock tables;

MySQL Master-Slave configuration is done.

Documentation

I recommend to read MySQL documentation:

MySQL - Replication

MySQL - Replication options

MySQL - Replication options slave

MySQL - Replication options binary log

MySQL - Binary log

Milosz Galazka's Picture

About Milosz Galazka

Milosz is a Linux Foundation Certified Engineer working for a successful Polish company as a system administrator and a long time supporter of Free Software Foundation and Debian operating system.

Gdansk, Poland https://sleeplessbeastie.eu