Configuring MariaDB binary hot backup for master-slave replication under centos7

Time:2019-11-20

Configuring MariaDB binary hot backup for master-slave replication under centos7


First, dump data from the primary database to complete data backup from the secondary database

Create a hot standby account in the main database, and authorize the replication slave permission:

grant replication slave on *.* to "slave"@"192.168.3.206" identified by "admin"


flush privileges;

Modify the configuration file of the main library and add the following configuration.

sudo vim /etc/my.cnf
Server id = 2 // the server ID here is unique. The master and slave databases cannot be the same
Log bin = bin log // the filename of the binary log
Binlog do DB = databasename // the database you want to back up
Binlog ignore DB = MySQL // databases that do not need to be backed up

Restart MariaDB

systemctl restart mariadb

View master-slave status in master library

show slave status\G;
File: bin-log.00000 3 // Note

    Position: 10646 // here, the data of file and position will be used for configuration of slave database

    Binlog_Do_DB:databasename

    Binlog_Ignore_DB:mysql

    1 row in set (0.00 sec)

To change the configuration of the slave library, add the following

Server id = 3 // the server ID here is unique. The master and slave databases cannot be the same
Log bin = bin log // the filename of the binary log
Replicate do DB = databasename // the database you want to back up
replicate-ignore-db=mysql,information_schema,performance_schema

Restart MariaDB

systemctl restart mariadb

Log in from the library maridb, stop the slave first

stop slave;

Change hot standby configuration

Change master to master_host="192.168.3.205",master_port=3306, master_user="backups",master_password="admin",master_log_file="bin-log.000003",master_log_pos=19879;

Master_host = “IP address of master database”
Master? Port = database port
Master Ou user = “the account you created for hot standby”
Master “password =” password of hot standby account “
“Master log file” and “master log POS” should be consistent with the data displayed in the main database show master statusg.


Then turn on slave.

start slave;

Check the status

show slave status\G;
    Slave_IO_Running: Yes

    Slave_SQL_Running: Yes

If yes is displayed for the two items here, it is basically successful. Establish a table in the main database, and then check whether the slave database is also established synchronously to test whether it is successful.


When P.S. stores SQL, it must include data. Only the storage structure will cause data inconsistency between the two databases and errors.