mysql/mariadb主從複製架構配置及過程中出現的問題

標籤：rman   ant   IO_error   grants   linux   install   問題   安裝外掛程式   null   

兩台CentOS7系虛擬機器主機：
分別是：主伺服器172.16.75.1，從伺服器172.16.75.2
使用的是mariadb-5.5.56，即centOS內建的軟體版本
為了使實驗結果顯示精準，此處關閉兩台伺服器的防火牆和SELinux：
[[email protected] ~]# setenforce 0
[[email protected] ~]# iptables -F

一、首先在主伺服器172.16.75.1上配置：
在/etc/my.cnf中配置如下：
[mysqld]
###定義二進位日誌的存放位置###
log_bin=/var/lib/mysql/binlog
###配置server_id來保證伺服器的唯一性###
server_id=101
innodb_file_per_table=ON
###跳過網域名稱反解###
skip_name_resolve=ON
###每寫入一次二進位日誌事件，就會刷寫入磁碟一次，一般為1，防止二進位日誌尚未刷寫至磁碟時宕機導致資料丟失，保證資料完整###
sync_binlog=1
###事務每次提交都會將事務日誌緩衝區中的日誌寫入作業系統的緩衝區並立即調用fsync()刷寫到磁碟中，即使系統奔潰也不會丟失任何資料###
innodb_flush_log_at_trx_commit=1

儲存退出，重啟mariadb服務。

登入mysql互動模式，在Master上建立一個用於實現複製功能的使用者賬戶，並授權Replication slave，Replication client許可權：
MariaDB [(none)]> grant replication slave on . to ‘repuser‘@‘%‘ identified by ‘reppass‘;
Query OK, 0 rows affected (0.01 sec)
然後更新授權，防止重新整理不及時，無法登入：
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
查看授權使用者：
MariaDB [(none)]> show grants for "repuser"@"%";
+--------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]% |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON . TO ‘repuser‘@‘%‘ IDENTIFIED BY PASSWORD ‘*304A91F0E46BBB1E641D3D95E225E9AAA27077CE‘ |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

記錄下Master上開始複製前的二進位記錄檔名及位置座標，在從伺服器上配置需要用到File檔案名稱和響應的Position位置座標：
MariaDB [(none)]> show master status;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000014 | 553 | | |
+---------------+----------+--------------+------------------+
1 row in set (0.01 sec)

這裡實驗採用的是備份主伺服器上的全部資料庫資料和表，可以通過mysqldump命令來進行完全備份：
[[email protected] ~]# mysqldump --all-databases --lock-tables > alldb.sql
而後將備份的alldb.sql檔案發送到從伺服器端（這裡發送至從伺服器的root的家目錄）：
[[email protected] ~]# scp alldb.sql 172.16.72.2:/root/

二、在從伺服器172.16.75.2上配置：
在/etc/my.cnf中配置如下：
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
#設定伺服器id，區分主從#
server_id=201
#中繼日誌存放位置#
relay_log=/var/lib/mysql/slavelog
#設定從伺服器全域唯讀#
#read_only=ON或者set @@global.read_only=ON#
set @@global.read_only=ON

**補充**：關於read_only，可以防止從伺服器修改資料使得主從伺服器端資料不一致（因為從伺服器沒有開啟二進位日誌記錄，所以從伺服器上修改的資料將不為主伺服器所知），但是該伺服器參數僅能限制那些不具有"SUPER"許可權的使用者的寫操作行為(例如root使用者除外)，在低版本的mariadb中可以在從伺服器上開啟mysql會話，並使用"flush tables with read lock;"給所有表加讀鎖，用來禁止root使用者在從伺服器上進行寫操作；在後期的mariadb版本中，會提供限制root使用者在從伺服器上的寫入權限的參數：root_read_only。

儲存退出，重啟Mariadb服務。

進入Mariadb互動模式中，首先將備份好的資料匯入到從伺服器上：
MariaDB [(none)]> source /root/alldb.sql
然後給所有表加鎖，禁止root使用者進行寫操作：
MariaDB [(none)]> flush tables with read lock;
在Slave上使用CHANGE MASTER TO...語句來指定Master的相關屬性資訊：
在指定之前確保，slave複製線程已經關閉：
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.06 sec)
開始指定master：
MariaDB [(none)]> change master to master_host=‘172.16.75.1‘, master_port=3306, master_user=‘repuser‘, master_password=‘reppass‘, master_log_file=‘binlog.000014‘, master_log_pos=553;
Query OK, 0 rows affected (0.02 sec)
在Slave上開啟複製線程：
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
查看主從複製架構狀態：
MariaDB [(none)]> show slave status\G;
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.75.1
Master_User: repuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000014
Read_Master_Log_Pos: 553
Relay_Log_File: slavelog.000002
Relay_Log_Pos: 526
Relay_Master_Log_File: binlog.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: 553
Relay_Log_Space: 813
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: 101
1 row in set (0.00 sec)

ERROR: No query specified

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果這IO線程和SQL線程都顯示YES，則代表搭建成功。

測試：在主伺服器中搭建一個資料庫，如果從伺服器相應的出現建立的資料庫，則代表測試沒有問題。
在主伺服器172.16.75.1中mysql互動模式中：
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| backuptest |
| mysql |
| mytest |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
建立新資料庫testdb：
MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.07 sec)

在從伺服器172.16.75.2上，查詢資料庫顯示如下，出現主伺服器端建立的資料庫testdb：
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| backuptest |
| mysql |
| mytest |
| performance_schema |
| testdb |
| zabbix |
+--------------------+
7 rows in set (0.07 sec)

至此，mysql的主從複製架構搭建成功。

搭建配置過程中出現的問題：
1.出現串連主伺服器端不成功的問題：
MariaDB [(none)]> show slave status\G;
1. row
Slave_IO_State: Connecting to master
Master_Host: 172.16,75.1
Master_User: repuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000014
Read_Master_Log_Pos: 553
Relay_Log_File: slavelog.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: binlog.000014
Slave_IO_Running: Connecting
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: 553
Relay_Log_Space: 245
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2005
Last_IO_Error: error connecting to master ‘[email protected],75.1:3306‘ - retry-time: 60 retries: 86400 message: Unknown MySQL server host ‘172.16,75.1‘ (2)
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.01 sec)

ERROR: No query specified

此時需要，清理/var/lib/mysql/下的master.info和relay-log.info，清除上一次複製的標誌位記錄和中繼日誌記錄資訊，然後重啟從伺服器的mariadb服務，串連恢複正常：
[[email protected] mysql]# rm -fr master.info
[[email protected] mysql]# rm -fr relay
[[email protected] mysql]# systemctl restart mariadb.service

2.出現禁止已經授權使用者的請求串連問題，Slave_IO_Running: Connecting：
MariaDB [(none)]> show slave status\G;
1. row
Slave_IO_State: Connecting to master
Master_Host: 172.16.75.1
Master_User: repuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000011
Read_Master_Log_Pos: 245
Relay_Log_File: slavelog.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: binlog.000011
Slave_IO_Running: Connecting
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: 245
Relay_Log_Space: 245
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1045
Last_IO_Error: error connecting to master ‘[email protected]:3306‘ - retry-time: 60 retries: 86400 message: Access denied for user ‘repuser‘@‘172.16.75.2‘ (using password: YES)
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)

ERROR: No query specified

解決辦法：
這裡實驗所遇到的主要是主伺服器端授權更新不及時，導致授權的使用者無效，自然無法串連：
在主伺服器端mariadb的互動模式中：
MariaDB [(none)]> flush privileges;
查看授權使用者是否生效：
MariaDB [(none)]> show grants for "repuser"@"%";
+--------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]% |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON . TO ‘repuser‘@‘%‘ IDENTIFIED BY PASSWORD ‘*304A91F0E46BBB1E641D3D95E225E9AAA27077CE‘ |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

這裡在尋找Slave_IO_Running: Connecting的問題中，主要參考了以下部落格（附上連結）：
54606894
50504321
18707599

3.在從伺服器端匯入備份好的主伺服器資料庫檔案時，第一次我採用了直接在shell命令列中輸入命令：mysql -uroot -p < alldb.sql ，發現等待很長時間無響應
這時候嗎，發現在主伺服器端初次實驗時，備份所有資料庫和表之前加了讀鎖：
FLUSH TABLES WITH READ LOCK;
加讀鎖沒有問題，最重要的是在匯入從伺服器端時，切記要把讀鎖解開，否則就會出現我一萬年都導不進去的現象，如果要在不解鎖的情況下導進去的話，只能在從伺服器mariadb的互動模式中匯入：
Mariadb [(none)]source /root/alldb.sql

以上就是mysql/mariadb一主一從複製架構的全部過程和小問題解析。

關於mysql雙主複製架構：
兩台MySQL伺服器之間互為主從關係，所以要在兩台伺服器上都開啟二進位日誌和中繼日誌功能；
雙主架構的架構目的：為了防止單點故障；

雙主架構可能存在的架構問題：
1.資料不一致的風險大幅提升；
2.在一些表的某個或某些欄位中，如果使用auto_increment修飾符設定了欄位的自動成長，可能會造成資料紊亂，結果會導致對資料的插入、修改或刪除等操作執行失敗；
定義其中一個伺服器主節點的自動成長的值全部為奇數；
auto_increment_increment=2
auto_increment_offset=1

定義另一個伺服器主節點的自動成長的值全部為偶數；    auto_increment_increment=2    auto_increment_offset=2使用上述方式設定的序號，可以避免主鍵衝突，但是有可能出現數值的不連續現象，也可以專門提供一台伺服器，用來產生序號；

配置方案：
在配置過程中，需要注意以下幾個問題：
1.兩台MySQL伺服器的server_id必須設定為不同的值；
2.均需啟動二進位日誌和中繼日誌，並且要保證mysql使用者對於兩種記錄檔都有寫入權限；
3.對於存在自動成長ID的表，保證不會發生主鍵衝突的問題；
4.在兩台MySQL伺服器上均需授權使用者進行複製操作；
5.在兩台MySQL伺服器上均需使用CHANGE MASTER TO語句指定對方伺服器的複製屬性值；

設定檔/etc/my.cnf中：
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
log_bin=binlog
server_id=101
sync_binlog=1
innodb_flush_log_at_trx_commit=1
relay_log=slavelog
auto_increment_offset=1
auto_increment_increment=2

授權使用者：
MariaDB [(none)]> grant replication slave on . to ‘repuser‘@‘%‘ identified by ‘reppass‘;

指定master為另一台主伺服器：
MariaDB [(none)]> change master to master_host=‘172.16.75.2‘,
master_user=‘repuser2‘,
master_password=‘reppass‘,
master_port=3306,
master_log_file=‘binlog.000001‘,
master_log_pos=245;

[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
log_bin=binlog
server_id=202
sync_binlog=1
innodb_flush_log_at_trx_commit=1
relay_log=slavelog
auto_increment_offset=2
auto_increment_increment=2

授權使用者：
MariaDB [(none)]> grant replication slave on . to ‘repuser2‘@‘%‘ identified by ‘reppass‘;

指定master為第一台伺服器：
MariaDB [(none)]> change master to master_host=‘172.16.75.1‘,master_user=‘repuser‘,master_password=‘reppass‘,master_port=3306,master_log_file=‘binlog.000005‘,master_log_pos=245;

在第一台主伺服器端和第二台主伺服器上分別啟動從複製線程：
start slave;

雙主架構和一主一從架構之間搭建過程當中並沒有太多不同，除了設定增長步長的問題，還需要把之前一主一從架構中的從伺服器中的唯讀關閉，其他遇到的問題基本與一主一從問題一致，有了一主一從的基本經驗，部署雙主架構不是問題。

關於半同步複製架構：
在一主多從的MySQL架構中，可以讓Master與眾多的Slave中的一台伺服器保持同步複製，與其他的Slave繼續使用預設的非同步複製；

如果想要讓MySQL支援半同步複製，需要額外的外掛程式；使用rpm包安裝的MySQL/MariaDB，外掛程式預設存放於：/usr/lib64/mysql/plugin
semisync_master.so --> rpl_semi_sync_master;
semisync_slave.so --> rpl_semi_sync_slave;

安裝外掛程式的方法：
MariaDB [(none)] > INSTALL PLUGIN plugin_name SONAME ‘SO_FILE_NAME‘;

安裝半同步外掛程式：
安裝主伺服器的半同步外掛程式：
MariaDB [(none)] > install plugin rpl_semi_sync_master soname ‘semisync_master.so‘;

查看主伺服器上的與半同步複製有關的伺服器參數：    MariaDB [hellodb]> show global variables like ‘%semi%‘;    +------------------------------------+-------+    | Variable_name                      | Value |    +------------------------------------+-------+    | rpl_semi_sync_master_enabled       | OFF   |    | rpl_semi_sync_master_timeout       | 10000 |    | rpl_semi_sync_master_trace_level   | 32    |    | rpl_semi_sync_master_wait_no_slave | ON    |    +------------------------------------+-------+查看主伺服器上與半同步複製有關的狀態參數：    MariaDB [hellodb]> show global status like ‘%semi%‘;    +--------------------------------------------+-------+    | Variable_name                              | Value |    +--------------------------------------------+-------+    | Rpl_semi_sync_master_clients               | 0     |    | Rpl_semi_sync_master_net_avg_wait_time     | 0     |    | Rpl_semi_sync_master_net_wait_time         | 0     |    | Rpl_semi_sync_master_net_waits             | 0     |    | Rpl_semi_sync_master_no_times              | 0     |    | Rpl_semi_sync_master_no_tx                 | 0     |    | Rpl_semi_sync_master_status                | OFF   |    | Rpl_semi_sync_master_timefunc_failures     | 0     |    | Rpl_semi_sync_master_tx_avg_wait_time      | 0     |    | Rpl_semi_sync_master_tx_wait_time          | 0     |    | Rpl_semi_sync_master_tx_waits              | 0     |    | Rpl_semi_sync_master_wait_pos_backtraverse | 0     |    | Rpl_semi_sync_master_wait_sessions         | 0     |    | Rpl_semi_sync_master_yes_tx                | 0     |    +--------------------------------------------+-------+安裝從伺服器的半同步外掛程式：    MariaDB [(none)] > install plugin rpl_semi_sync_slave soname ‘semisync_slave.so‘;查看從伺服器的與半同步複製相關的伺服器參數：    MariaDB [(none)]> show global variables like ‘%semi%‘;    +---------------------------------+-------+    | Variable_name                   | Value |    +---------------------------------+-------+    | rpl_semi_sync_slave_enabled     | OFF   |    | rpl_semi_sync_slave_trace_level | 32    |    +---------------------------------+-------+查看從伺服器的與半同步複製相關的狀態參數：    MariaDB [(none)]> show global status like ‘%semi%‘;    +----------------------------+-------+    | Variable_name              | Value |    +----------------------------+-------+    | Rpl_semi_sync_slave_status | OFF   |    +----------------------------+-------+

測試半同步複製的方法：
主伺服器：
MariaDB [hellodb]> set @@global.rpl_semi_sync_master_enabled=ON;
從伺服器：
MariaDB [(none)]> set @@global.rpl_semi_sync_slave_enabled=ON;

正常情況下，主伺服器上進行的所有的資料修改，會立即同步到開啟了半同步負責的從伺服器上；驗證半同步降級的方法：    在從伺服器上，關閉IO_THREAD線程，而後再在主伺服器上進行資料修改操作，為了等待從伺服器的同步資料更新，主伺服器會阻塞所有的其他寫操作，直到收到從伺服器的確認資訊為止；但是如果超過rpl_semi_sync_master_timeout伺服器參數所規定的時間，主伺服器仍然沒有收到從伺服器資料同步的確認資訊，則自動降級為非同步模式；

以上就是mysql/mariadb複製架構得配置，由低及高，從多到少，步步來，求穩。

下面附上複製過程中監控及相關的維護操作：
1.二進位日誌的監控和清理：
SHOW MASTER | BINARY LOGS;
SHOW MASTER STATUS;
SHOW BINLOG EVENTS IN ‘binlog_file‘;
PURGE MASTER | BINARY LOGS TO ‘log_name‘ | BEFORE datetimme_expr;

    2.複製的監控：        主伺服器：            SHOW MASTER | BINARY LOGS;            SHOW MASTER STATUS;            SHOW BINLOG EVENTS IN ‘binlog_file‘;        從伺服器：            SHOW SLAVE STATUS\G    3.判斷主從節點的資料是否一致：        在建立表時，使用CHECKSUM=1選項，為此表開啟校正和功能；        為了能夠判斷表的校正和，Percona提供了一個偵查工具：pt_table_checksum；    4.主從節點資料不一致：        修複的方法通常有兩種：            1.刪除資料，重新複製；            2.將主伺服器的資料進行完全備份，到從伺服器上恢複；

mysql/mariadb主從複製架構配置及過程中出現的問題