Master Slave Replication to Automatically Backup Your MySQL Database

We can make backup for some databases by periodically running a kind of dump query, like mysqldump in MySQL. That's the simplest method but it can drain our server resources and it's not suitable for large databases. MySQL comes up with master-slave features that allow you to replicate your database to another location (slave). This mechanism enables MySQL to generate a log file which records any action performed to the database. Then, that action will be run in slave database too.

For example, we have two database servers with IP address 192.168.0.1 (Master) and 192.168.0.2 (Slave).

1) Configure my.cnf in master server

# Master Settings

# locate where the changes record will be stored
log-bin = /var/log/mysql/mysql-bin.log

# set unique ID for master database in master-slave network (up to you)
server-id = 111

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

# select database which will be replicated
# by default system will log all databases
binlog-do-db = my_blog
binlog-do-db = my_store
binlog-do-db = my_company

# or you can select database wich will be ignored
binlog-ignore-db = testing

binlog_format = mixed

2) Create replication user in master database server

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

3) Restart MySQL server

4) Show master status

SHOW MASTER STATUS;

5) Make initial master databases dump that will be used by the slave (run mysqldump for some databases you want to replicate)

6) Import those dumped databases to slave database

7) Configure my.cnf in slave server

log_bin = /var/log/mysql/mysql-bin.log
relay-log = /var/log/mysql/mysql-relay-bin.log
server-id = 222
binlog-do-db = my_blog
binlog-do-db = my_store
binlog-do-db = my_company
binlog_format = mixed

8) Set master user which is used for replication

CHANGE MASTER TO MASTER_HOST='192.168.0.1', 
MASTER_USER='slave_user', MASTER_PASSWORD='password';

9) Start and monitor the slave

START SLAVE;
SHOW SLAVE STATUS;

10) Replication errors can be occured. You can show slave status and looks for Last_Error or Last_SQL_Error. You can skip the error by setting up SQL_SLAVE_SKIP_COUNTER.

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 
START SLAVE;

11) Change it back to 0 if you don't want to skip any error.

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 0; 
START SLAVE;