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

This article is about how to achieve asynchronous backup in mysql database. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it.

1. Server statu

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

3. Execute 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 require synchronization

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

Please do not add the following command, which will 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 = 2

Auto_increment_offset = 1

6. Restart MySQL

7. Enter the 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, 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 filled in here is exactly the opposite.

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

Method two

Two servers

192.168.1.1 (A)

192.168.1.2 (B)

First make sure that the version of the mysql is consistent, reference, otherwise the abnormal situation in the replication is very troublesome.

1. Create users and set permissions on two mysql

Add on A:

# grant replication slave,replication client,reload,super on *. * to identified by '123456' with grant option;// for B access

On B:

# grant replication slave,replication client,reload,super on *. * to identified by '123456' with grant option;// for An access

Perform # flush privileges; to update the database to make the user effective.

two。 Make relevant configuration on / 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

Be careful

1.server_id must be unique.

two。 If you want to synchronize multiple libraries at the same time, add multiple rows of replicate-do-db, each specifying a database. Cannot use the form of replicate-do-db=db1,db2

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 that need to be synchronized on the two servers to ensure that the initial states of the two databases are the same.

4. Perform two-way synchronization

Two-way synchronization is to do one-way synchronization in reverse, but we must pay attention to the order of operation, 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 mysql-bin.000054, and 35

Step2. Execute on B:

# stop slave;// stops synchronization

#

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 slave;// starts synchronization

Step3, if the following content is displayed in the execution show slave statusG;, the synchronization setting is successful.

Slave_IO_State: Waiting for master to send event

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Step4: there is no problem in the previous step. Then continue to 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)

Step5: execute on A

# stop slave;// stops synchronization

#

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 slave;// starts synchronization

Step6: if the following content is displayed in the execution show slave statusG;, the synchronization setting is successful.

Slave_IO_State: Waiting for master to send event

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

As mentioned above, there is no problem. At this point, the two-way synchronization setting is complete.

Method three

First, prepare the server

Since the (binary log) binlog format may be different between different versions of MySQL, the best combination is that the MySQL version of Master is the same as the Slave version or lower, and the Master version must not be higher than the Slave version.

More.. | | less.. | | in this article, we assume that both the master server (hereinafter referred to as Master) and the slave server (hereinafter referred to as Slave) are version 5.0.27, and the operating system is RedHat 9.

Suppose the hostname of the synchronous Master is: master (IP:192.168.1.123), the hostname of the Slave is: slave (IP:192.168.1.124), and the basedir directory of both MySQL is / usr/local/mysql,datadir: / var/lib/mysql.

Second, set up synchronization server

1. Set synchronization Master

Modify the my.cnf file in the

# 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 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 permission to execute "LOAD TABLE FROM MASTER" or "LOAD DATA FROM MASTER" statements on Slave, you must grant 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 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 synchronization

Under the primary 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, yours can't be the same as mine, ha, 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 take a look at the synchronization from the server

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

If it's all yes, it means it's already in sync.

Write some data to the table to test whether the synchronization is successful. If it is not successful, it is definitely not your RP problem. Check the operation steps again!

4. Set up two-way synchronization

Modify the 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 the my.cnf of the master server 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 primary server slave MySQL command:

Show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000013 | 98 | discuz | mysql | |

+-+

Under the 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, it is the reverse operation of A-> B one-way synchronization! Two-way synchronization, as simple as that!

Tip: if you modify the configuration of the master server, remember to delete the master.info file on the slave server. Otherwise, the slave server uses the same old configuration, which may lead to errors.

Note: when you want to copy multiple binlog-do-db and replicate-do-db options, many people on the Internet say that they are separated by half-width commas. After testing, this statement is wrong. MySQL official documents also clearly point out that if you want to back up multiple databases, just set the corresponding options repeatedly.

For example:

Binlog-do-db=a

Binlog-do-db=b

Replicate-do-db=a

Replicate-do-db=b

The above is how to achieve asynchronous backup in the mysql database. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please 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.