Detailed explanation of the master-slave database synchronization configuration method of mysql server

This article brings you a detailed explanation of the master-slave database synchronization configuration method of mysql server. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you. help.

First of all, you need to install the mysql service on two machines in the same LAN (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. First log in to host A and execute the following command to grant permissions to the slave. If there are multiple cluster machines, execute multiple times:

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

2. Open my.cnf of host A and enter the following configuration parameters:

server-id = 1 #Host identification, Integer
log_bin = /var/log/mysql/mysql-bin.log #Make sure this file is writable
read-only =0 #Host, both reading and writing are possible
binlog-do-db =test # If you need to back up data, write more than one line and write multiple lines
binlog-ignore-db =mysql #For databases that do not need to be backed up, write multiple lines with multiple lines

3. Open 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 disconnected, the reconnection time difference (seconds)
replicate-do-db =test #Only copy a certain library
replicate-ignore-db=mysql #Do not copy a certain library

4. Synchronize the database

After entering the above configuration, restart host A respectively and slave machine B, synchronization can be automatically realized.

5. Verification

In host A, mysql>show master statusG;

In slave B, mysql>show slave statusG;

You can see roughly these contents

File: mysql-bin.000001
Position: 1374
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql

In addition, it can be in host A, Do some INSERT, UPDATE, and DELETE operations to see if host B has been modified.

The above is the detailed content of Detailed explanation of the master-slave database synchronization configuration method of mysql server. For more information, please follow other related articles on the PHP Chinese website!