We can make backup for some databases by periodically running a kind of dump query, like
mysqldumpin 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).
my.cnfin 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
mysqldumpfor some databases you want to replicate)
6) Import those dumped databases to slave database
my.cnfin 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;