How to Implement synchronous data backup for mysql two-Server databases

Mysql tutorial two Server database tutorial synchronous data backup method

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, ensure that the mysql version is consistent. For more information, see.

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 |

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

| Mysql-bin.000005 | 6854 |

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

1 row in set (0.00 sec)

Step 5: Execute on

# Stop slave; // stop Synchronization

#

Change master to MASTER_HOST = '2017. 168.1.2 ', MASTER_PORT = 3306, MASTER_USER = 'sync _ user', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'mysql-bin.000005', MASTER_LOG_POS = 123456;

# Start slave; // start Synchronization

Step 6: 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

If there is no problem. The bidirectional synchronization settings are complete.

Method 3

I. Prepare servers
Because the binlog formats of different MySQL versions (Binary logs) may be different, the best combination is that the MySQL version of the Master is the same or lower than the Slave version, the Master version must not be higher than the Slave version.
More... | less... | In this article, we assume that the Master server and Slave server (Slave) versions are 5.0.27, and the operating system is RedHat Linux 9.
Assume that the Host Name of the synchronization Master is: master (IP: 192.168.1.123), Slave host name is: slave (IP: 192.168.1.124), and the two MySQL basedir directories are/usr/local/mysql, datadir is both:/var/lib/mysql.
Ii. Set synchronization server
1. Set synchronization Master
Modify the my. cnf file in
# Replication Master Server (default)
# Binary logging is required for replication
Add the following content:
# Log-bin =/var/log/mysql/updatelog
Server-id = 1
Binlog-do-db = discuz
Binlog-ignore-db = mysql
Restart MySQL and create a MySQL account for synchronization
#/Usr/local/mysql/bin/mysql-u root-p
Mysql> grant replication slave on *. * TO [email =] 'back' @ '%' [/email] identified by 'back ';
If you want to have the permission to execute the "load table from master" or "load data from master" Statement on Slave, you must grant the Global FILE and SELECT permissions:
Mysql> grant file, SELECT, replication slave on *. * TO [email =] 'back' @ '%' [/email] identified by 'back ';
Mysql> flush privileges;

2. Set synchronization Slave
Modify the my. cnf file and add
Server-id = 2
Master-host = 192.168.1.123
Master-user = back
Master-password = back
Master-port = 3306
Replicate-ignore-db = mysql
Replicate-do-db = discuz

Restart MySQL

3. Start Synchronization
Under the master MySQL command:
#/Usr/local/mysql/bin/mysql-u root-p
Mysql> show master status;
Display (of course, this is my machine, you cannot be the same as me, just an example ):
+ ------------------ + ---------- + ------------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + ------------------- + ------------------ +
| Mysql-bin.000009 | 98 | discuz | mysql |
+ ------------------ + ---------- + ------------------- + ------------------ +

Under the slave server master MySQL command:
#/Usr/local/mysql/bin/mysql-u root-p
Mysql> slave stop;
Mysql> change master to master_host = '192. 168.1.123 ', master_user = 'back', master_password = 'back', master_log_file = 'mysql-bin.000009', master_log_pos = 98;
Mysql> slave start;

Use show slave statusG; to check synchronization from the server
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
If all are yes, it indicates that the synchronization is in progress.
Write Data to the table and test whether the synchronization is successful. If the synchronization fails, it is definitely not your RP problem. Then check the operation steps!

4. Set bidirectional Synchronization

Modify my. cnf of the slave server and add
Log-bin =/var/log/mysql/updatelog
Binlog-do-db = discuz
Binlog-ignore-db = mysql

Restart MySQL and create a MySQL account for synchronization
Mysql> grant replication slave on *. * TO [email =] 'back' @ '%' [/email] identified by 'back ';
Mysql> grant file, SELECT, replication slave on *. * TO [email =] 'back' @ '%' [/email] identified by 'back ';
Mysql> flush privileges;

Modify my. cnf of the master server and add
Master-host = www.3ppt.com
Master-user = back
Master-password = back
Master-port = 3306
Replicate-ignore-db = mysql
Replicate-do-db = discuz

Restart MySQL

Under the master server slave MySQL command:
Show master status;
+ ------------------ + ---------- + ------------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + ------------------- + ------------------ +
| Mysql-bin.000013 | 98 | discuz | mysql |
+ ------------------ + ---------- + ------------------- + ------------------ +

Run the MySQL Command on server:
Mysql> slave stop;
Mysql> change master to master_host = '192. 168.1.124 ', master_user = 'back', master_password = 'back', master_log_file = 'mysql-bin.000013', master_log_pos = 98;
Mysql> slave start;

In fact, this is the reverse operation of A-> B one-way synchronization! Bidirectional synchronization is that simple!

Tip: If you have modified the configuration of the master server, remember to delete the master.info file on the slave server. Otherwise, the old configurations used by the slave server may cause errors.
Bytes -----------------------------------------------------------------------------------
Note: When you want to copy multiple databases, the binlog-do-db and replicate-do-db options are set. Many people on the Internet say that they are separated by commas (,). After testing, this statement is incorrect. The official MySQL documentation also clearly states that if you want to back up multiple databases, you only need to set the corresponding options again.
For example:
Binlog-do-db =
Binlog-do-db = B
Replicate-do-db =
Replicate-do-db = B