How to Implement synchronous asynchronous backup for mysql Databases on different servers

1. server Status server A: 192.168.1.1 server B: 192.168.1.22. create A synchronous user host Domain A: 192.168.1.2 User Name A: sync_a Password A: aaa host Domain B: 192.168.1.1 user name B: sync_ B password B: bbb at least assign the following permissions grantreplicationslave3. execute flushprivileges4. stop MySQ

1. server Status server A: 192.168.1.1 server B: 192.168.1.2 2. create A synchronization user host Domain A: 192.168.1.2 User Name A: sync_a Password A: aaa host Domain B: 192.168.1.1 user name B: sync_ B password B: bbb at least assign the following permissions grant replication slave 3. execute flush privileges 4. stop MySQ

1. Server Status
Server A: 192.168.1.1
Server B: 192.168.1.2

2. Create a synchronization user
Host Domain A: 192.168.1.2 User Name A: sync_a Password A: aaa
Host Domain B: 192.168.1.1 username B: sync_ B password B: bbb
Assign at least the following permissions to grant replication slave

3. Execute flush privileges

4. Stop MySQL

5. Configure my. cnf (my. ini)

Server A server B
User = mysql
Log-bin = mysql-bin
Server-id = 1
Binlog-do-db = test
Binlog-ignore-db = mysql
Replicate-do-db = test
Replicate-ignore-db = mysql
Log-slave-updates
Slave-skip-errors = all
Sync_binlog = 1 user = mysql
Log-bin = mysql-bin
Server-id = 2
Binlog-do-db = test
Binlog-ignore-db = mysql
Replicate-do-db = test
Replicate-ignore-db = mysql
Log-slave-updates
Slave-skip-errors = all
Sync_binlog = 1

The server-id must be unique.
Binlog-do-db and replicate-do-db indicate the databases to be synchronized.
Binlog-ignore-db and replicate-ignore-db indicate databases that do not need to be synchronized.
Do not add the following command. This command will not solve the problem of uid Skip. On the contrary, the following two commands are the culprit of uid Skip.
Auto_increment_increment = 2
Auto_increment_offset = 1

6. Restart MySQL

7. log on to the MySQL console.

Server:
Show master status G
Flush tables with read lock;
Server B:
Show master status G
Flush tables with read lock;

Record the File and Position of the two servers at the same time. Assume that:

A: File: mysql-bin.000001
Position: 001
B: File: mysql-bin.000002
Position: 002

Server:
Change master
-> Master_host = '192. 168.1.2 ',
-> Master_user = 'sync _ B ',
-> Master_password = 'bbb ',
-> Master_log_file = 'mysql-bin.000002 ',
-> Master_log_pos = 002;
Server B:
Change master
-> Master_host = '2017. 168.1.1 ',
-> Master_user = 'sync _ ',
-> Master_password = 'aaa ',
-> Master_log_file = 'mysql-bin.000001 ',
-> Master_log_pos = 001;

The data entered here is completely the opposite

8. Run show processlist G to check whether the synchronization is successful.

Method 2

Two servers
192.168.1.1 ()
192.168.1.2 (B)
First, make sure that the mysql version is consistent. For details, refer to # replication-implementation-details. Otherwise, the exception in the replication process is quite confusing.

1. Create a user on two mysql instances and Set permissions
Add on:

# Grant replication slave, replication client, reload, super on *. * to 'sync _ user' @ '192. 168.1.2 'identified by '000000' with grant option; // used for B access

B:

# Grant replication slave, replication client, reload, super on *. * to 'sync _ user' @ '192. 168.1.1 'identified by '20140901' with grant option; // used for A access

Run # flush privileges; update the database for the user to take effect.

2. Configure/etc/my. cnf

A B
Server-id = 1

Master-host = 192.168.1.2

Master-user = sync_user

Master-pass = 123456

Master-port = 3306

Master-connect-retry = 60

Replicate-do-db = db1

Replicate-do-db = db2

Replicate-ignore-db = mysql server-id = 2

Master-host = 192.168.1.1

Master-user = sync_user

Master-pass = 123456

Master-port = 3306

Master-connect-retry = 60

Replicate-do-db = db1

Replicate-do-db = db2

Replicate-ignore-db = mysql

Note:

1. server_id must be unique.

2. If you want to synchronize multiple databases at the same time, add multiple rows of replicate-do-db and specify one database per row. Replicate-do-db = db1 cannot be used.

3. replicate-ignore-db: Specifies the database that is not synchronized.

After saving, restart mysql

# Mysqladmin-u root-p shutdown

# Mysqld_safe -- user = mysql

3. Copy the databases to be synchronized on the two servers to ensure the initial status of the two databases is consistent.

4. Bidirectional Synchronization

Two-way synchronization is to re-execute one-way synchronization, but you must pay attention to the operation sequence, which is the key to success.

Step 1: Execute

# Show master status;

+ ------------------ + ---------- + -------------- + ------------------ +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------ + ---------- + -------------- + ------------------ +

| Mysql-bin.000054 | 35 |

+ ------------------ + ---------- + -------------- + ------------------ +

1 row in set (0.00 sec)

Record the mysql-bin.000054, and 35

Step 2: Execute on B:

# Stop slave; // stop Synchronization

#

Change master to MASTER_HOST = '2017. 168.1.1 ', MASTER_PORT = 3306, MASTER_USER = 'sync _ user', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'mysql-bin.000054', MASTER_LOG_POS = 35;

# Start slave; // start Synchronization

Step 3: Execute show slave statusG. If the following content is displayed, the synchronization setting is successful.

Slave_IO_State: Waiting for master to send event

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

Step 4: No problem in the previous step. Execute show master status on B;

# Show master status;

+ ------------------ + ---------- + -------------- + ------------------ +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------ + ---------- + -------------- + ------------------ +