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

In this issue, the editor will bring you about how to configure each other as masters and followers in MySQL. The article is rich in content and analyzes and narrates for you from a professional point of view. I hope you can get something after reading this article.

1) An and B two mysql servers

1. Server parameters, edit / etc/my.cnf

[a server]

Server-id = 1

Binlog-do-db = test

Binlog-ignore-db = mysql

Replicate-do-db = test

Replicate-ignore-db = mysql

Master-host = 192.168.255.195

Master-user = repl

Master-password = repl

Master-port = 3306

Master-connect-retry = 10

Sync-binlog = 1

Log-bin=mysql-bin

[B server]

Server-id = 2

Binlog-do-db = test

Binlog-ignore-db = mysql

Replicate-do-db = test

Replicate-ignore-db = mysql

Master-host = 192.168.255.194

Master-user = repl

Master-password = repl

Master-port = 3306

Master-connect-retry = 10

Sync-binlog = 1

Log-bin=mysql-bin

2) perform database operations on An and B

A/B: Slave stop

A/B: Reset master

A: grant replication slave on *. * to identified by 'repl'

B: grant replication slave on *. * to identified by 'repl'

A/B: show master status

A: change master to master_host='192.168.255.194',master_user='repl',master_password='repl',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=98; (log according to master status)

B:

Change master to master_host='192.168.255.195',master_user='repl',master_password='repl',master_port=3306,master_log_file='mysql-bin.000006',master_log_pos=98; (log according to master status)

A/B: slave start

A show slave status B: GG; (view synchronization status)

The following status indicates that the synchronization is successful

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

3) Test database synchronization, add tables in A's test library and delete tables in B's test library

Use test

A:Create table username (id int (15))

Check to see if there is a username table in B

B: drop table username

Check to see if the usernam table has been deleted in A

A _ hand B: show slave status\ G; (check the synchronization status again, and if there is no problem, it means success)

4) configure instances synchronously with each other

1. A / B Mutual Master / Slave synchronous test, Asynchronous mysql:

Set in both database configurations: binlog-do-db=test, binlog-ignore-db=mysql, replicate-do-db=test, replicate-ignore-db=mysql

2. A / B only synchronizes test with each other's master and slave, and does not synchronize with other databases, and the newly created ones will not synchronize either.

Set in both database configurations: binlog-do-db=test, replicate-do-db=test

3. A / B is a master-slave mysql that synchronizes with other databases, such as new databases created.

Set in both database configurations: binlog-ignore-db=mysql, replicate-ignore-db=mysql

4. A B synchronizes all databases with each other, including the newly built databases

The above four items are not set in both database configurations.

5) shell script to realize automatic synchronization

1. Increase super and replication client on permissions

Grant super on *. * to identified by 'repl'

Grant replication client on *. * to identified by 'repl'

two。 Automatic synchronization script autosync.sh

#! / bin/bash

While true

Do

Status= `/ usr/bin/-uroot-e "show slave status\ G;" | grep Slave_SQL_Running | cut-f2-d ":" | sed's /'`

If [$status! = "Yes"]

Then

A = `/ usr/bin/mysql-urepl-prepl-h292.168.255.194-e "show master status" | grep mysql-bin | awk'{print $2}'`

/ usr/bin/mysql-uroot-e "slave stop"

/ usr/bin/mysql-uroot-e "change master to master_host='192.168.255.194',master_user='repl',master_password='repl',master_port=3306

Master_log_file='mysql-bin.000001',master_log_pos=$A "

/ usr/bin/mysql-uroot-e "slave start"

Fi

Sleep 10

Done

3. Background execution

Nohup. / autosync.sh > / dev/null 2 > & 1 &

The above is how to configure each other in the MySQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, 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.