一.双主复制实现方案
1.让表的ID自增，然后主1写1、3、5，主2写2、4、6
2.不让表的ID自增，然后通过web端程序去seq服务器取ID，写入双主
双主工作场景为高并发写的场景，慎用。
二.双主配置，以多实例3306和3308为例
在3306上创建一个专门用来同步数据库的用户
mysql> grant replicationslave on *.* to rep@'10.0.0.%' identified by '123456';
###*.*代表所有库和所有表
mysql> flush privileges;
mysql> select user,hostmysql.user;
mysql> show grants forrep@'10.0.0.%';
2.两个数据库的my.cnf文件里的mysqld模块添加如下黄色部分
3306配置
[root@mysql /]# sed -n '36,43p'/data/3306/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 #自增ID的间隔，如1 3 5间隔为2
auto_increment_offset = 1 #ID的初始位置
log-slave-updates
relay-log =/data/3306/relay-bin
relay-log-info-file =/data/3306/relay-log.info
3308配置
[root@mysql /]# sed -n '36,43p'/data/3308/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 #自增ID的间隔，如1 3 5间隔为2
auto_increment_offset = 2 #ID的初始位置
log-slave-updates
relay-log = /data/3308/relay-bin
relay-log-info-file =/data/3308/relay-log.info
3.重启两个数据库实例
[root@mysql /]# /data/3306/mysql restart
[root@mysql /]# /data/3308/mysql restart
4.登陆数据库确认添加的内容是否生效
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)
二.主库3306备份数据，导入主库3308
1.在主库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:所有的库 -x:全局锁表
2.将数据导入主库3308
[root@mysql ~]# ls /opt
3306_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_2017-07-03.sql
[root@mysql opt]# mysql -uroot-p123456 -S /data/3308/mysql.sock <3306_2017-07-03.sql
3.主库3308连接主库3306
[root@mysql opt]# mysql -uroot-p123456 -S /data/3308/mysql.sock <<EOF
CHANGE MASTER TO
MASTER_HOST='10.0.0.20',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456';
EOF
[root@mysql opt]# mysql -uroot-p123456 -S /data/3308/mysql.sock -e "start slave"
三.主库3308备份数据，导入主库3306
1.在主库3308上备份数据
[root@mysql ~]# mysqldump-uroot -p123456 -S /data/3308/mysql.sock -A --events -B -x --master-data=1|gzip>/opt/3308_$(date +%F).sql.gz
-A:所有的库 -x:全局锁表
2.将数据导入主库3306
[root@mysql ~]# ls /opt
3308_2017-07-03.sql.gz
[root@mysql ~]# cd /opt
[root@mysql opt]# gzip -d 3308_2017-07-03.sql.gz
[root@mysql opt]# ls
3308_2017-07-03.sql
[root@mysql opt]# mysql -uroot -p123456-S /data/3306/mysql.sock <3308_2017-07-03.sql
3.主库3306连接主库3308
[root@mysql opt]# mysql -uroot-p123456 -S /data/3306/mysql.sock <<EOF
CHANGE MASTER TO
MASTER_HOST='10.0.0.20',
MASTER_PORT=3308,
MASTER_USER='rep',
MASTER_PASSWORD='123456';
EOF
[root@mysql opt]# mysql -uroot-p123456 -S /data/3306/mysql.sock -e "start slave"
四.在两个实例上互相创建数据库，测试查看是否正常互相复制同步，经测试已可以正常同步了^_^
本文出自 “飞奔的骆驼” 博客，请务必保留此出处http://wn2100.blog.51cto.com/9915310/1944439