Asynchronous backup method of two MySQL database synchronous implementation

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 username 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 grant replication slave

3. Implementation of flush privileges

4. Stop MySQL

5. Configure MY.CNF (My.ini)

Server A Server B
user = MySQL Tutorial
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

Server-id must be unique
Binlog-do-db and Replicate-do-db represent database tutorials that need to be synchronized
Binlog-ignore-db and replicate-ignore-db represent databases that do not need to be synchronized
Please do not add the following command, which does not resolve the issue of the UID hop number, but the following two lines are the culprit for the UID jump.
Auto_increment_increment = 2
Auto_increment_offset = 1

6. Restart MySQL

7. Access to MySQL console

Server A:
Show Master Status G
Flush tables with read lock;
Server B:
Show Master Status G
Flush tables with read lock;

Record both the file and position of the two servers here, assuming:

a:file:mysql-bin.000001
position:001
b:file:mysql-bin.000002
position:002

Server A:
Change Master to
-> 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 to
-> master_host= ' 192.168.1.1 ',
-> master_user= ' sync_a ',
-> master_password= ' AAA ',
-> master_log_file= ' mysql-bin.000001 ',
-> master_log_pos=001;

The data you fill out here is exactly the opposite.

8. Perform show processlist g to see if sync is successful

Method Two

Two servers
192.168.1.1 (A)
192.168.1.2 (B)
First make sure that the MySQL version is consistent, reference http://dev.mysql.com/doc/refman/5.1/zh/replication.html#replication-implementation-details, Otherwise, the abnormal situation in replication is very frustrating.

1. Create user on two MySQL, set permissions
Add on a:

#grant replication Slave,replication client,reload,super on *.* to ' sync_user ' @ ' 192.168.1.2 ' identified by ' 123456 ' with g Rant option;//for B access

B on:

#grant replication Slave,replication client,reload,super on *.* to ' sync_user ' @ ' 192.168.1.1 ' identified by ' 123456 ' with g Rant option;//for a access

implementation of #flush privileges; Update the database for the user to take effect.

2. Related configuration on the/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

Attention

The 1.server_id must be unique.

2. If you want to synchronize multiple libraries at the same time, add multiple lines of replicate-do-db and specify one database per row. Cannot use the form of REPLICATE-DO-DB=DB1,DB2

3.REPLICATE-IGNORE-DB: Specifies a database that is not synchronized.

After saving, restart MySQL

#mysqladmin-U root-p shutdown

#mysqld_safe--user=mysql

3. Copy the databases that need to be synchronized on both servers to ensure the initial state of the two databases is consistent.

4. For bidirectional synchronization

Bidirectional synchronization is a one-way synchronization in turn to do it again, but must pay attention to the order of operations, which is the key to success

Step1. Execute in MySQL shell on a

#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 the 35

Step2. Execute on B:

#stop slave;//Stop Synchronizing

#

Change MASTER to master_host= ' 192.168.1.1 ', master_port=3306, master_user= ' Sync_user ', master_password= ' 123456 ', Master_log_file= ' mysql-bin.000054 ', master_log_pos=35;

Start Sync #start slave;//

Step3, perform show slave statusg, such as the following, indicating that the synchronization settings were successful.

Slave_io_state:waiting for Master to send event

Slave_io_running:yes

Slave_sql_running:yes

STEP4: No problem in the previous step. Continue 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)

STEP5: Executing on a

#stop slave;//Stop Synchronizing

#

Change MASTER to master_host= ' 192.168.1.2 ', master_port=3306, master_user= ' Sync_user ', master_password= ' 123456 ', Master_log_file= ' mysql-bin.000005 ', master_log_pos=6854;

Start Sync #start slave;//

STEP6: Perform show slave statusg, such as the following, indicating that the synchronization settings were successful.

Slave_io_state:waiting for Master to send event

Slave_io_running:yes

Slave_sql_running:yes

No problem like the above. To this bidirectional synchronization setup completes.

Method Three

First, prepare the server
Because the Binlog format of the (binary log) between different versions of MySQL may vary, the best combination is the same or lower version of Master's MySQL version and Slave, and master's version must not be higher than the slave version.
More.. | Less.. | In this article, we assume that the primary server (hereafter, master) and the version from the server (hereinafter referred to as slave) are 5.0.27, and the operating system is Redhat Linux 9.
Suppose the host name for Sync Master is: Master (ip:192.168.1.123), slave host name is: Slave (ip:192.168.1.124), and 2 MySQL basedir directories are/usr/local/mysql , DataDir are:/var/lib/mysql.
Second, set up synchronization server
1. Set Sync Master
Modify the My.cnf file to
# Replication Master Server (default)
# Binary logging is required for replication
Add the following:
#log-bin=/var/log/mysql/updatelog
Server-id = 1
Binlog-do-db=discuz
Binlog-ignore-db=mysql
Restart MySQL, create a MySQL account for synchronization dedicated
#/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 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 up sync slave
Modify MY.CNF file, 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 Sync
Under Main server Master MySQL command:
#/usr/local/mysql/bin/mysql-u Root-p
Mysql> Show master status;
Show (of course this is the case of my machine, you can't be like me ha, just an example):
+------------------+----------+-------------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+-------------------+------------------+
| mysql-bin.000009 | 98 | Discuz | MySQL |
+------------------+----------+-------------------+------------------+

From the 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; take a look at the sync from the server
Slave_io_running:yes
Slave_sql_running:yes
If it's all yes, that means it's already in sync.
Write some data to the table test to see if the synchronization is successful, if not successful, is definitely not your RP problem, and then check the operation steps!

4, set up bidirectional synchronization

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

Restart MySQL, create a MySQL account for synchronization dedicated
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 the master server's my.cnf and add
Master-host = 192.168.1.124
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 |
+------------------+----------+-------------------+------------------+

Under Server a MySQL command:
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, that is, a->b one-way synchronous reverse operation! Bidirectional synchronization, it's so simple!

Tip: If you modify the configuration of the primary server, remember to remove the Master.info file from the server. Otherwise, using the old configuration from the server may cause errors.
-----------------------------------------------------------------------------------
Note: When you are copying multiple databases, Binlog-do-db and replicate-do-db option settings, many people on the internet are said to be separated by a half-width comma, tested, such a statement is wrong, the MySQL official document also clearly pointed out that if you want to back up multiple databases, as long as the appropriate option to repeatedly set it.
For example:
binlog-do-db=a
binlog-do-db=b
replicate-do-db=a
replicate-do-db=b