Shulou(Shulou.com)06/01 Report--

The following brings you about the implementation of mysql dual-master synchronization configuration, if you are interested, let's take a look at this article. I believe it will be of some help to you after reading the implementation scheme of mysql dual-master synchronization configuration.

one。 Implementation scheme of dual-master replication

1. Let the ID of the table increase itself, then master 1 writes 1, 3, 5, and master 2 writes 2, 4, 6

two。 Do not let the ID of the table increase by itself, and then go to the seq server to get the ID through the web program and write it to the double host.

The dual-master work scenario is a highly concurrent writing scenario, so use it with caution.

two。 Dual master configuration, taking multi-instance 3306 and 3308 as examples

Create a user on 3306 dedicated to synchronizing the database

Mysql > grant replicationslave on *. * to rep@'10.0.0.%' identified by '123456'

# *. * represents all libraries and all tables

Mysql > flush privileges

Mysql > select user,hostmysql.user

Mysql > show grants forrep@'10.0.0.%'

two。 The mysqld module in the my.cnf file of the two databases is added as follows.

3306 configuration

[root@mysql /] # sed-n'36 camera 43pact Universe dataUnip 3306Universe my.cnf

# log-slow-queries = / data/3306/slow.log

Pid-file = / data/3306/mysql.pid

Log-bin = / data/3306/mysql-bin

Auto_increment_increment = 2 # interval of self-incrementing ID, such as 1 / 3 / 5 / 2

Auto_increment_offset = initial position of 1 # ID

Log-slave-updates

Relay-log = / data/3306/relay-bin

Relay-log-info-file = / data/3306/relay-log.info

3308 configuration

[root@mysql /] # sed-n'36 camera 43pact Universe dataUnix 3308Universe my.cnf

# log-slow-queries = / data/3308/slow.log

Pid-file = / data/3308/mysql.pid

Log-bin = / data/3308/mysql-bin

Auto_increment_increment = 2 # interval of self-incrementing ID, such as 1 / 3 / 5 / 2

Auto_increment_offset = initial position of 2 # ID

Log-slave-updates

Relay-log = / data/3308/relay-bin

Relay-log-info-file = / data/3308/relay-log.info

3. Restart two database instances

[root@mysql /] # / data/3306/mysql restart

[root@mysql /] # / data/3308/mysql restart

4. Log in to the database to confirm whether the added content is valid.

3306

[root@mysql /] # mysql-uroot-p123456-S / data/3306/mysql.sock

Mysql > show variables like "log_%"

+-+-

| | Variable_name | Value |

+-+-

| | log_bin | ON |

| | log_bin_trust_function_creators | OFF |

| | log_error | / data/3306/mysql_oldboy3306.err |

| | log_output | FILE |

| | log_queries_not_using_indexes | OFF |

| | log_slave_updates | ON |

| | log_slow_queries | OFF |

| | log_warnings | 1 | |

+-+-

8 rows in set (0.00 sec)

Mysql > show variables like "auto_%"

+-+ +

| | Variable_name | Value |

+-+ +

| | auto_increment_increment | 2 | |

| | auto_increment_offset | 1 | |

| | autocommit | ON |

| | automatic_sp_privileges | ON |

+-+ +

4 rows in set (0.00 sec)

3308

Mysql > show variables like "log_%"

+-+

| | Variable_name | Value |

+-+

| | log_bin | ON |

| | log_bin_trust_function_creators | OFF |

| | log_error | / data/3308/mysql_oldboy3308.err |

| | log_output | FILE |

| | log_queries_not_using_indexes | OFF |

| | log_slave_updates | ON |

| | log_slow_queries | OFF |

| | log_warnings | 1 | |

+-+

8 rows in set (0.00 sec)

Mysql > show variables like "auto_%"

+-+ +

| | Variable_name | Value |

+-+ +

| | auto_increment_increment | 2 | |

| | auto_increment_offset | 2 | |

| | autocommit | ON |

| | automatic_sp_privileges | ON |

+-+ +

4 rows in set (0.00 sec)

two。 Backup data from main library 3306, import data from main library 3308

1. Back up data on the main library 3306

[root@mysql] # mysqldump-uroot-p123456-S / data/3306/mysql.sock-A-- events-B-x-- master-data=1 | gzip > / opt/3306_$ (date +% F) .sql.gz

-A: all libraries-x: global locking table

two。 Import data into the main library 3308

[root@mysql ~] # ls / opt

3306 July 2017-07-03.sql.gz

[root@mysql ~] # cd / opt

[root@mysql opt] # gzip-d 3306 / 2017-07-03.sql.gz

[root@mysql opt] # ls

3306 July 2017-07-03.sql

[root@mysql opt] # mysql-uroot-p123456-S / data/3308/mysql.sock

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.