Implementation scheme of mysql dual-master synchronous configuration
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
