Shulou(Shulou.com)05/31 Report--

This article shows you how to achieve synchronous backup in mysql, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

How to realize synchronous backup by mysql on different servers

1. Server status:

Server AVR 192.168.1.1

Server BRV 192.168.1.2

two。 Create a synchronization user:

Host domain Avatar 192.168.1.2 username A:sync_a password A:aaa

Host domain bvv 192.168.1.1 username B:sync_b password B:bbb

Assign at least the following permissions grantreplicationslave.

3. Execute flushprivileges.

4. Stop MySQL.

5. Configure my.cnf (my.ini).

Server A Server B

User=mysqllog-bin=mysql-binserver-id=1binlog-do-db=testbinlog-ignore-db=mysqlreplicate-do-db=testreplicate-ignore-db=mysqllog-slave-updatesslave-skip-errors=allsync_binlog=1user=mysqllog-bin=mysql-binserver-id=2binlog-do-db=testbinlog-ignore-db=mysqlreplicate-do-db=testreplicate-ignore-db=mysqllog-slave-updatesslave-skip-errors=allsync_binlog=1

Note:

Server-id must be unique.

Binlog-do-db and replicate-do-db represent databases that need to be synchronized.

Binlog-ignore-db and replicate-ignore-db represent databases that do not require synchronization.

Please do not add the following command, which does not solve the problem of uid hopping, on the contrary, the following two lines of commands are the main culprit for causing uid hopping:

Auto_increment_increment=2auto_increment_offset=1

How to realize synchronous backup by mysql on different servers

6. Restart MySQL.

7. Go to the MySQL console:

Server A:

ShowmasterstatusGflushtableswithreadlock

Server B:

ShowmasterstatusGflushtableswithreadlock

Record both the File and Position of the two servers, assuming:

A:File:mysql-bin.000001

Position:001

B:File:mysql-bin.000002

Position:002

Server A:

Changemasterto- > 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:

Changemasterto- > 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 filled in here is quite the opposite.

8. Execute showprocesslistG to see if the synchronization is successful.

The above is how to achieve synchronous backup in mysql. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.