4.MySQL 主主(m-m) 同步生產庫標準同步操作實施流程

通過MySQL參數配置使用主主前提：

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________


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________

1. /data/3306/mysql stop
2. /data/3306/mysql start
4. /data/3307/mysql stop
5. /data/3307/mysql start

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)

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

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，表示正常

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>

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)