4.MySQL 主主(m-m) 同步生产库标准同步操作实施流程

通过MySQL参数配置使用主主前提：

1、表的主键自增。
#################################################################
#m1-m2配置文件更改：再上篇M-S同步基础上增加：
#################################################################
m1(192.168.1.31 3306)：主库配置文件中添加如下行
vim /data/3306/my.cnf 增加如下两行参数：
  1. #_______m-m m1 start________
  2. auto_increment_increment =2
  3. auto_increment_offset =1
  4. log-slave-updates
  5. log-bin = /data/3306/mysql-bin
  6. expire_logs_days =7
  7. #_______m-m m1 end________
m2(192.168.1.31 3307)：主库配置文件中添加如下行
  1. #_______m-m m2 start________
  2. auto_increment_increment =2
  3. auto_increment_offset =2
  4. log-slave-updates
  5. log-bin = /data/3307/mysql-bin
  6. expire_logs_days =7
  7. #_______m-m m2 end________
参数说明：
解决主建自增长变量冲突：
Master1：
auto_increment_increment =2 #自增ID的间隔，如1 3 5间隔为2.
auto_increment_offset =1 #ID的初始位置
(将形成1,3,5,7，...序列)
 
Master2：
auto_increment_increment =2 #自增ID的间隔，如2 4 6间隔为2.
auto_increment_offset =2 #ID的初始位置
(将形成2,4,6,8，...序列)
2、配置完以后重启数据库
  1. /data/3306/mysql stop
  2. /data/3306/mysql start
  4. /data/3307/mysql stop
  5. /data/3307/mysql start
3、接着登录数据库中验证，都 是否开启
  1. mysql> show variables like "log_%";
  2. +---------------------------------+---------------------------------+
  3. |Variable_name|Value|
  4. +---------------------------------+---------------------------------+
  5. | log_bin | ON |
  6. | log_bin_trust_function_creators | OFF |
  7. | log_error |/data/3306/mysql_oldboy3306.err |
  8. | log_output | FILE |
  9. | log_queries_not_using_indexes | OFF |
  10. | log_slave_updates | ON |
  11. | log_slow_queries | OFF |
  12. | log_warnings |1|
  13. +---------------------------------+---------------------------------+
  14. 8 rows in set (0.00 sec)
  1. mysql> show variables like "log_%";
  2. +---------------------------------+---------------------------------+
  3. |Variable_name|Value|
  4. +---------------------------------+---------------------------------+
  5. | log_bin | ON |
  6. | log_bin_trust_function_creators | OFF |
  7. | log_error |/data/3307/mysql_oldboy3307.err |
  8. | log_output | FILE |
  9. | log_queries_not_using_indexes | OFF |
  10. | log_slave_updates | ON |
  11. | log_slow_queries | OFF |
  12. | log_warnings |1|
  13. +---------------------------------+---------------------------------+
  14. 8 rows in set (0.00 sec)
  1. mysql> show variables like "auto_%";
  2. +--------------------------+-------+
  3. |Variable_name|Value|
  4. +--------------------------+-------+
  5. | auto_increment_increment |2|
  6. | auto_increment_offset |1|
  7. | autocommit | ON |
  8. | automatic_sp_privileges | ON |
  9. +--------------------------+-------+
  10. 4 rows in set (0.00 sec)
  1. mysql> show variables like "auto_%";
  2. +--------------------------+-------+
  3. |Variable_name|Value|
  4. +--------------------------+-------+
  5. | auto_increment_increment |2|
  6. | auto_increment_offset |2|
  7. | autocommit | ON |
  8. | automatic_sp_privileges | ON |
  9. +--------------------------+-------+
  10. 4 rows in set (0.00 sec)
4、把备份的MySQL数据导入从库(Slave ):
首先打包数据
  1. mysqldump -uroot -p123456 -S /data/3307/mysql.sock -A --events -B -x --master-data=1|gzip >/opt/3307_$(date +%F).sql.gz
  1. [root@mysql opt]# gzip -d 3307_2016-04-09.sql.gz
  2. [root@mysql opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock <3307_2016-04-09.sql
  1. 接着CHANGE MASTER TO
  2. [root@mysql opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock<<EOF
  3. > stop slave;
  4. > CHANGE MASTER TO
  5. > MASTER_HOST='192.168.1.31',
  6. > MASTER_PORT=3307,
  7. > MASTER_USER='rep',
  8. > MASTER_PASSWORD='123456';
  9. > EOF
5、检查状态是否同步
  1. [root@mysql opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "start slave;show slave status \G"
  2. ***************************1. row ***************************
  3. Slave_IO_State:Waitingfor master to send event
  4. Master_Host:192.168.1.31
  5. Master_User: rep
  6. Master_Port:3307
  7. Connect_Retry:60
  8. Master_Log_File: mysql-bin.000001
  9. Read_Master_Log_Pos:585
  10. Relay_Log_File: relay-bin.000004
  11. Relay_Log_Pos:253
  12. Relay_Master_Log_File: mysql-bin.000001
  13. Slave_IO_Running:Yes
  14. Slave_SQL_Running:Yes
  15. Replicate_Do_DB:
  16. Replicate_Ignore_DB: mysql
  17. Replicate_Do_Table:
  18. Replicate_Ignore_Table:
  19. Replicate_Wild_Do_Table:
  20. Replicate_Wild_Ignore_Table:
  21. Last_Errno:0
  22. Last_Error:
  23. Skip_Counter:0
  24. Exec_Master_Log_Pos:585
  25. Relay_Log_Space:403
  26. Until_Condition:None
  27. Until_Log_File:
  28. Until_Log_Pos:0
  29. Master_SSL_Allowed:No
  30. Master_SSL_CA_File:
  31. Master_SSL_CA_Path:
  32. Master_SSL_Cert:
  33. Master_SSL_Cipher:
  34. Master_SSL_Key:
  35. Seconds_Behind_Master:0 ##这个选项为0，表示正常
6、测试数据库是否  主主复制
  1. 1、首先登录3307数据库
  2. [root@mysql scripts]# mysql -uroot -p123456 -S /data/3307/mysql.sock
  3. 2、创建zhurui1 库
  4. mysql> create database zhurui1;
  5. Query OK,1 row affected (0.02 sec)
  7. 3、登录3306数据库
  8. [root@mysql opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock
  9. 4、检查zhurui1 库是否复制过来
  10. mysql> show databases;
  11. +--------------------+
  12. |Database|
  13. +--------------------+
  14. | information_schema |
  15. | beautifulgirl |
  16. | lian |
  17. | mysql |
  18. | performance_schema |
  19. | zhu |
  20. | zhurui |
  21. | zhurui1 |
  22. +--------------------+
  23. 8 rows in set (0.00 sec)
  25. mysql>
7、在zhurui 库中创建表，测试是否主主复制
  1. mysql> use zhurui
  2. Database changed
  3. mysql> CREATE TABLE `t1`(
  4. ->`id` bigint(12) NOT NULL auto_increment,
  5. ->`name` varchar(12) NOT NULL,
  6. -> PRIMARY KEY (`id`)
  7. ->);
  8. Query OK,0 rows affected (0.04 sec)
  1. mysql> desc t1;##使用desc可以查看表结构
  2. +-------+-------------+------+-----+---------+----------------+
  3. |Field|Type|Null|Key|Default|Extra|
  4. +-------+-------------+------+-----+---------+----------------+
  5. | id | bigint(12)| NO | PRI | NULL | auto_increment |
  6. | name | varchar(12)| NO || NULL ||
  7. +-------+-------------+------+-----+---------+----------------+
  8. 2 rows in set (0.34 sec)
表中插入数据：
  1. mysql> insert into t1(name) values("woduibuqini");##插入数据的sql语句
  2. Query OK,1 row affected (0.01 sec)
  4. mysql> select *from t1;##查看t1库
  5. +----+--------------+
  6. | id | name |
  7. +----+--------------+
  8. |1| zengqinglian |
  9. |3| woaini |
  10. |5| woduibuqini |
  11. +----+--------------+
  12. 3 rows in set (0.00 sec)
 
posted @ 2016-04-12 16:37  Simon92  阅读(587)  评论(0)    收藏  举报