mysql搭建多主一從源複製環境
問題描述：搭建過一主多從的環境，由於資料庫數據一致性要求高，有些情景會搭建一主多從的架構，搭建多主一從的模式，相對來說適合數據整合，將多個業務的庫整合到一起，方便做查詢，也可以當做一個監控其他主庫數據的作用。但是搭建這樣的模式極易造成數據衝突，同步的庫名一致，導致主從異常，同時也對網路並發提出一定得要求
環境介紹：
正常可以將資料庫分別放在不通的伺服器上，本文方便測試，以下三個資料庫都放在了同一台伺服器上
IP：192.168.163.21 3306--主庫 server_id:29 3307--主庫 server_id:27 3308--收集從庫 server_id:28
1.配置3306主庫參數
[mysqld] basedir=/application/mysql datadir=/data/3306/data socket=/data/3306/mysql.sock port=3306 log-error=/data/3306/mysql.log log_bin=/data/3306/mysql-bin binlog_format=row #skip-name-resolve server-id=29 gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 # 需要同步庫 binlog-do-db = dev3306 # 不需要同步庫 binlog-ignore-db = mysql binlog_ignore_db = information_schema binlog_ignore_db = performation_schema binlog_ignore_db = sys
配置複製用戶
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'root1234'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
重啟3306埠
systemctl restart mysqld3306
2.配置3307主庫參數
[mysqld] basedir=/application/mysql datadir=/data/3307/data socket=/data/3307/mysql.sock port=3307 log-error=/data/3307/mysql.log log_bin=/data/3307/mysql-bin binlog_format=row skip-name-resolve server-id=27 gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 # 需要同步庫 binlog-do-db = dev3307 # 不需要同步庫 binlog-ignore-db = mysql binlog_ignore_db = information_schema binlog_ignore_db = performation_schema binlog_ignore_db = sys
配置複製用戶
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'root1234';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
重啟3307埠
systemctl restart mysqld3307
mysql> show master status; +------------------+----------+--------------+--------------------------------------------------+-----------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+--------------------------------------------------+-----------------------------------------------+ | mysql-bin.000015 | 362 | dev3306 | mysql,information_schema,performation_schema,sys | 55826e3e-7f15-11eb-a9e3-000c2959ac6a:1-236538 | +------------------+----------+--------------+--------------------------------------------------+-----------------------------------------------+ 1 row in set (0.00 sec)
3.配置3307從庫參數
[mysqld] basedir=/application/mysql datadir=/data/3308/data port=3308 socket=/data/3308/mysql.sock log-error=/data/3308/mysql.log log_bin=/data/3308/mysql-bin binlog_format=row skip-name-resolve server-id=28 gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 lower_case_table_names = 1 # 表名不區分大小寫 master_info_repository = table relay_log_info_repository = table
重啟生效
systemctl restart mysqld3308
mysql> show master status; +------------------+----------+--------------+--------------------------------------------------+-------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+--------------------------------------------------+-------------------------------------------+ | mysql-bin.000011 | 362 | dev3307 | mysql,information_schema,performation_schema,sys | 1a0da16a-7f15-11eb-a6b4-000c2959ac6a:1-16 | +------------------+----------+--------------+--------------------------------------------------+-------------------------------------------+ 1 row in set (0.00 sec)
4.開啟同步
mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.163.21', -> MASTER_PORT=3306, MASTER_USER='slave', -> MASTER_PASSWORD='root1234', -> MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=362 for channel '29'; 7, MASTER_USER='slave', MASTER_PASSWORD='root1234', MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=362 for channel '27';Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> mysql> mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.163.21', -> MASTER_PORT=3307, MASTER_USER='slave', -> MASTER_PASSWORD='root1234', -> MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=362 for channel '27'; Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave ;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.163.21 Master_User: slave Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000010 Read_Master_Log_Pos: 630 Relay_Log_File: mha4-relay-bin-27.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000010 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 630 Relay_Log_Space: 529 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 27 Master_UUID: 1a0da16a-7f15-11eb-a6b4-000c2959ac6a Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 1bf45cd1-7f15-11eb-a810-000c2959ac6a:1-13 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: 27 Master_TLS_Version: *************************** 2. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.163.21 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000014 Read_Master_Log_Pos: 630 Relay_Log_File: mha4-relay-bin-29.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000014 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 630 Relay_Log_Space: 529 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 29 Master_UUID: 55826e3e-7f15-11eb-a9e3-000c2959ac6a Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 1bf45cd1-7f15-11eb-a810-000c2959ac6a:1-13 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: 29 Master_TLS_Version: 2 rows in set (0.01 sec)
5.驗證同步
在從庫上手動創建dev3306和dev3307
3306
mysql> use dev3306; Database changed mysql> mysql> show tables; Empty set (0.00 sec) mysql> mysql> create table t1; ERROR 1113 (42000): A table must have at least 1 column mysql> mysql> mysql> create table t1 (id int); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values(1),(2),(3); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0
3307
mysql> use dev3307; Database changed mysql> mysql> create table t2(id int); Query OK, 0 rows affected (0.00 sec) mysql> insert into t2 values(4),(5),(6); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0
3308
mysql> select * from dev3306.t1; +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) mysql> select * from dev3307.t2; +------+ | id | +------+ | 4 | | 5 | | 6 | +------+ 3 rows in set (0.00 sec)
6.啟動和停止單獨slave進程
mysql> stop slave for channel '27'; Query OK, 0 rows affected (0.01 sec) mysql> start slave for channel '27'; Query OK, 0 rows affected (0.00 sec)
7.但是以上主庫參數設置binlog-do-db也會帶來弊端，用這個參數限制同步哪些庫的話，但是又想整體對mysql中所有的庫做主從，就會比較麻煩。限制了同步某一個庫，其他的庫沒法做實時同步，就不能保證mysql中其他庫的安全性。另一方面如果不加這個參數進行多主一從的模式，進行全庫都複製同步，就會造成數據衝突，從而主從異常
binlog-do-db=需要複製的資料庫名，如果複製多個資料庫，重複設置這個選項即可
binlog-ignore-db=不需要複製的資料庫苦命，如果複製多個資料庫，重複設置這個選項即可
參考文檔：//blog.csdn.net/wangsdsdfds/article/details/105809232
