MySQL master-slave replication configuration MySQL Data Automatic Backup configuration

There are many ways to implement automatic mysql backup. The most common method is mysql master-slave synchronization for data backup. below I will introduce to you a detailed explanation of mysql master-slave configuration for database synchronization.

Benefits of using MySQL master-slave replication include:

1. The architecture of master-slave servers improves stability. If the master server fails, we can use the slave server to provide services;
2. Separate processing of user requests on Master/Slave servers to improve data processing efficiency;
3. copy the data on the master server to the slave server to protect the data from unexpected losses;

The master-slave configuration of the mysql server is a very simple task. Instead of starting from scratch, it is always on the mysql server that has been installed by others. This will involve the mysql version, startup File and other issues.

But it doesn't matter. Let's clarify two points first.

1. mysql configuration file my. cnf location

2. How to start and stop mysql and find the Startup File

Suppose there are two machines and mysql has been installed (try to use the same version, and the two machines have the same network, you can ping)

A friend said, "The slave server cannot be lower than the master server version." However, I am lower than the master server version and there is no problem.

Host A: 192.168.1.100

Slave B: 192.168.1.101

Multiple slave servers

1. log on to host A first.

The Code is as follows:	Copy code
Mysql> grant replication slave on *. * TO 'backup '@ '192. 168.1.101 'identified BY '123 ';

Grant permissions to the slave machine. If multiple slave machines exist, the slave machine is executed multiple times.

2. Open my. cnf of host A and enter

The Code is as follows:	Copy code
Server-id = 1 # host id, integer Log_bin =/var/log/mysql/mysql-bin.log # Make sure this file is writable Read-only = 0 # host, both read and write are allowed Binlog-do-db = test # You need to back up data and write multiple rows. Binlog-ignore-db = mysql # databases that do not need to be backed up, multiple write lines

3. Open my. cnf of slave machine B and enter

The Code is as follows:	Copy code
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 # Time Difference (in seconds) when the master server is disconnected from the server) Replicate-do-db = test # only copy a database Replicate-ignore-db = mysql # do not copy a database

4. Synchronize Databases

There are multiple ways to export the data test of host A to test. SQL first.

Then, create the database test from machine B. mysql imports test. SQL to the database test.

5. Restart mysql of host A and mysql of slave B.

6. Verification

In host A, mysql> show master status/G;

In slave machine B, mysql> show slave status/G;

You can see the following content:

The Code is as follows:	Copy code
File: mysql-bin.000001 Position: 1374 Binlog_Do_DB: test Binlog_Ignore_DB: mysql

You can perform INSERT, UPDATE, and DELETE operations in host A to check whether the operation has been modified in host B.