How to configure master-slave database synchronization on mysql server

Editor to share with you how to configure mysql server master-slave database synchronization. I hope you will learn a lot after reading this article. Let's discuss it together.

First of all, you need to install mysql services on two machines in the same local area network (of course, you can also use one machine to virtualize two machines). (recommended course: MySQL tutorial)

Host A: 192.168.1.100

Slave B: 192.168.1.101

There can be multiple slaves.

1. Log in to CVM A first, execute the following command to grant slave permission, and execute multiple times if there are multiple cluster machines:

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'backup'@'192.168.1.101' IDENTIFIED BY' 123456'

2. Open the my.cnf of CVM An and enter the following configuration parameters:

Server-id = 1 # host label, integer

Log_bin = / var/log/mysql/mysql-bin.log # make sure this file is writable

Read-only = 0 # host, both read and write

Binlog-do-db = test # data needs to be backed up, multiple writes and multiple lines

Binlog-ignore-db = mysql # databases that do not need to be backed up, multiple writes and multiple lines

3. Open the my.cnf of Slave B and enter the following configuration parameters:

Server-id = 2

Log_bin = / var/log/mysql/mysql-bin.log

Master-host = 192.168.1.100

Master-user = backup

Master-pass = 123456

Master-port = 3306

Master-connect-retry=60 # if the slave server finds that the master server is down, the time difference to reconnect (seconds)

Replicate-do-db = test # only copy a library

Replicate-ignore-db=mysql # does not copy a library

4. Synchronize the database

After entering the above configuration, restart host An and slave B respectively, you can automatically achieve synchronization.

5. Verification

In host A, mysql > show master statusG

In slave B, mysql > show slave statusG

You can see something like this.

File: mysql-bin.000001

Position: 1374

Binlog_Do_DB: test

Binlog_Ignore_DB: mysql

In addition, you can do some INSERT, UPDATE, DELETE operations in host A to see if host B has been modified.

After reading this article, I believe you have a certain understanding of how to configure the master-slave database synchronization of the mysql server. You want to know more about it. Welcome to follow the industry information channel. Thank you for your reading!