為了避免硬碟損毀進而造成資料庫遺失,可以使用主從式資料庫 (Master Slave Replication) 的技術. Master 的資料庫會透過 Binlog 的機制將資料同步到 Slave .
Master 不是真的會把資料複製一份到 Slave，而是 Master 會把所有會變動到資料的指令儲存到 Binlog ,其中包含 CREATE , ALTER TABLE , INSERT , UPDATE , DELETE … (不包含 SELECT 和 SHOW 這一類不影響資料的) .
Slave 透過 IO Thread 的方式 ( IO Thread 會請 Master 發送 Binlog 有更新的部分,並儲存至 Slave 的 Relay Log )
Slave 再透過 SQL Thread 讀取剛剛 IO Thread 儲存的 Relay Log 來執行有更新的部分(指令), 讓兩邊透過非同步的方式來同步資料.
圖片出自於 – https://gilbertasm.github.io/mysql/2018/04/15/understanding-mysql-replication-coordinates.html
Master Slave Replication 主要也只是避免資料庫遺失 (或是分散 SELECT 等讀取資料的指令到 Slave 來處理),如果想要做到 分散 處理,這時候需要透過 MariaDB Galera Cluster + HAProxy ,請參考 – https://benjr.tw/95536
下面來安裝與設置 MariaDB 主從式資料庫 (Master Slave Replication)
測試環境為 CentOS 7 / CentOS 8 x86_64 (虛擬機)
- Master IP : 192.168.111.129
- Slave IP : 192.168.111.157
安裝/設定 MariaDB + Replication
-
Master
安裝 Mariadb 資料庫所需套件.
[root@localhost ~]# yum install -y mariadb-server mariadb [root@localhost ~]# systemctl start mariadb [root@localhost ~]# systemctl enable mariadb
初始化設定 MAriadb 資料庫,主要設定 MariaDB 的 root 帳號 (不同於 CentOS 系統 root 帳號) 以及其他設定.
[root@localhost ~]# mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. Set root password? [Y/n] New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB!在 my.cnf ( CentOS 7 檔案位於 /etc/my.cnf , CentOS 8 檔案位於 /etc/my.cnf.d/mariadb-server.cnf ) 設定檔 [mysqld] 區塊加入以下與 Master replication 相關設定.
[root@localhost ~]# vim /etc/my.cnf [mysqld] server_id=1 log-basename=master log-bin binlog-format=row binlog-do-db=testdb
- server_id=1
主資料庫 Master 需設定為 1. - log-basename=master
- log-bin
Master Slave Replication 主要是透過 Master 儲存會變動到資料的指令,包含 CREATE , ALTER TABLE , INSERT , UPDATE , DELETE … 到 Binlog 並與 Slave 的 Binlog 同步,Slave 再依據此 Binlog 執行指令來確保兩邊資料的同步.
這個 Binlog 可以透過 Linux 下的指令 mysqlbinlog 來查看內容.[root@localhost ~]# mysqlbinlog /var/lib/mysql/mariadb-bin.000001
或是在 Mariadb 資料庫操作介面下指令.
MariaDB [(none)]> show binlog events \G
- binlog-format=row
記錄模式有三種 statement , row , mixed (沒特別研究). - binlog-do-db=testdb
指定哪一個資料庫需要被同步,當有多個資料庫時就依序寫下資料庫名稱,如下所示.binlog-do-db=testdb1 binlog-do-db=testdb2
沒設定時就會同步所有資料庫,或是使用下面忽略的方式來設定.
- replicate_ignore_db=testdb1 (忽略指定資料庫)
- replicate_ignore_table=test1.table1 (忽略指定資料表)
重啟資料庫服務.
[root@localhost ~]# systemctl restart mariadb
登入資料庫系統,並新增剛剛要同步的資料庫 testdb (如資料庫已存在請跳過這個步驟).
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CREATE DATABASE testdb; Query OK, 1 row affected (0.00 sec)
新增需要同步資料的使用者 slaveuser 與其密碼.
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' IDENTIFIED BY '111111'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
一開始 Master 的資料需要手動同步到 Slave ,需先把資料庫設定成為唯讀 (Read lock).
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.00 sec)
這邊需要新開一個 Linux command line (離開 mysql command line 後會讓唯讀 Read lock 失效),透過 mysqldump 把所有資料備份下來並傳給 Slave (IP: 192.168.111.157)
[root@localhost ~]# mysqldump --all-databases --user=root --password --master-data > masterdatabase.sql [root@localhost ~]# scp masterdatabase.sql root@192.168.111.157:/root/ root@192.168.111.157's password: masterdatabase.sql 100% 503KB 66.5MB/s 00:00
檢視一下 Master 的狀態 ,需把 File (指令同步的檔案名稱) 與 Position (同步檔案目前位於哪一行) 記錄起來,後續 Slave 設定需要使用該參數.
MariaDB [(none)]> SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | mariadb-bin.000001 | 554 | testdb | | +--------------------+----------+--------------+------------------+ 1 row in set (0.01 sec)
這邊就可以取消唯讀了,因為已經紀錄 File (指令同步的檔案名稱) 與 Position (同步檔案目前位於哪一行),之後 Master 做了什麼改變,Slave 都知道可以從哪一行指令 (Position) 還原做起.
MariaDB [(none)]> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) MariaDB [testdb]> exit Bye
- server_id=1
-
Slave
安裝 Mariadb 資料庫所需套件.
[root@localhost ~]# yum install -y mariadb-server mariadb [root@localhost ~]# systemctl start mariadb [root@localhost ~]# systemctl enable mariadb
初始化設定 MAriadb 資料庫,主要設定 MariaDB 的 root 帳號 (不同於 CentOS 系統 root 帳號) 以及其他設定.
[root@localhost ~]# mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. Set root password? [Y/n] New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB!在 my.cnf ( CentOS 7 檔案位於 /etc/my.cnf , CentOS 8 檔案位於 /etc/my.cnf.d/mariadb-server.cnf ) 設定檔 [mysqld] 區塊加入以下與 Slave replication 相關設定.
[root@localhost ~]# vim /etc/my.cnf [mysqld] server-id = 2 replicate-do-db=testdb
- server_id=2
Slave 資料庫設定為 2 與 Master (為 1) 不相同即可. - replicate-do-db=testdb
指定哪一個資料庫需要被同步,當有多個資料庫時就依序寫下資料庫名稱,如下所示.replicate-do-db=testdb1 replicate-do-db=testdb2
重啟資料庫服務.
[root@localhost ~]# systemctl restart mariadb
匯入剛剛從 Master 得到所有資料庫的備份檔案並重啟資料庫服務.
[root@localhost ~]# mysql -u root -p < masterdatabase.sql Enter password:
回到 MariaDB 指令介面來完成與 Master 同步資料的指令.
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.111.129', MASTER_USER='slaveuser', MASTER_PASSWORD='111111', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=554; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.00 sec)
- MASTER_HOST=’192.168.111.129′
Master 的 IP. - MASTER_USER=’slaveuser’
- MASTER_PASSWORD=’111111′
Master 有權限同步資料的使用者 slaveuser 與其密碼. - MASTER_LOG_FILE=’mariadb-bin.000001′
- MASTER_LOG_POS=554;
還記得剛剛在 Master 記錄起來 File 與 Position 值 (沒有記錄起來,請從備份 Master 資料庫重新做起,從前面紀錄的階段到現階段 Master 的資料庫有可能已有變化,其 Position 會不同,這會讓 Slave 與 Master 有一段時間的資料不同步的問題).+--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | mariadb-bin.000001 | 554 | testdb | | +--------------------+----------+--------------+------------------+
檢視一下 Slave 的狀態, Slave_IO_Running 與 Slave_SQL_Running 須為 Yes,才代表成功.
MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.111.129 Master_User: slaveuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000001 Read_Master_Log_Pos: 554 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 531 Relay_Master_Log_File: mariadb-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: testdb 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: 554 Relay_Log_Space: 827 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: 1 1 row in set (0.00 sec) - server_id=2
測試 MariaDB Replication
-
Master
在 Master testdb 資料庫新增 test 資料表,並新增一筆資料.
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 6 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> USE testdb; Database changed MariaDB [testdb]> CREATE TABLE test (ID INT); Query OK, 0 rows affected (0.00 sec) MariaDB [testdb]> INSERT INTO test VALUES(1); Query OK, 1 row affected (0.00 sec) MariaDB [testdb]> SELECT * FROM test; +------+ | ID | +------+ | 1 | +------+ 1 row in set (0.00 sec)
-
Slave
在 Slave 也會看到剛剛在 Master 新增的資料.
MariaDB [(none)]> USE testdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [testdb]> SELECT * FROM test; +------+ | ID | +------+ | 1 | +------+ 1 row in set (0.00 sec)
Note: 在 Slave 新增的資料並不會同步到 Master.
遇過的錯誤訊息
- Last_IO_Errno: 1236
Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event 'mariadb-bin.000001' at 473, the last event read from 'mariadb-bin.000001' at 473, the last byte read from 'mariadb-bin.000001' at 492.'
Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
檢視一下 Salve 與 Master 的 File 與 Position 值是否相同.
MariaDB [(none)]> SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | mariadb-bin.000001 | 554 | testdb | | +--------------------+----------+--------------+------------------+
MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.111.129 Master_User: slaveuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000001 Read_Master_Log_Pos: 554 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 531 Relay_Master_Log_File: mariadb-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: testdb 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: 554 Relay_Log_Space: 827 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: 1 1 row in set (0.00 sec) - Last_SQL_Errno: 1062
MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.32.77.21 Master_User: slaveuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000003 Read_Master_Log_Pos: 49426188 Relay_Log_File: mariadb-relay-bin.000028 Relay_Log_Pos: 1900278 Relay_Master_Log_File: mariadb-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: ben Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Could not execute Write_rows_v1 event on table ben.date1; Duplicate entry '12458' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mariadb-bin.000003, end_log_pos 4 9218670 Skip_Counter: 0 Exec_Master_Log_Pos: 49218463 Relay_Log_Space: 14079633 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: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Could not execute Write_rows_v1 event on table ben.data1; Duplicate entry '12458' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mariadb-bin.000003, end_log_pos 4 9218670 Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 1 row in set (0.000 sec)需先故障排除 Slave 的 Duplicate entry 的問題,再來重啟 SLAVE.
MariaDB [(none)]> STOP SLAVE; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.32.77.21 Master_User: slaveuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000003 Read_Master_Log_Pos: 49438735 Relay_Log_File: mariadb-relay-bin.000029 Relay_Log_Pos: 5238 Relay_Master_Log_File: mariadb-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: ben 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: 49438735 Relay_Log_Space: 2121143 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: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 1 row in set (0.000 sec) - Last_SQL_Errno: 1032
在 Master 新增了 SQL 排程 ( Event Scheduler ), 這排程在 Master 也同步到 Slave , Slave 在刪除時會有問題就停止同步了,並出現以下的錯誤訊息,該怎麼解決?Last_SQL_Error: Could not execute Update_rows event on table test.t; Can't find record in 't', Error_code: 1032; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mariadb-bin.000003, end_log_pos 49218670
設定 sql_slave_skip_counter=1 (跳過一筆同步錯誤的 SQL 語法，恢復主從同步)
mysql> STOP SLAVE; Query OK, 0 rows affected (0.00 sec) mysql> SET GLOBAL sql_slave_skip_counter=1; Query OK, 0 rows affected (0.00 sec) mysql> START SLAVE; Query OK, 0 rows affected (0.00 sec)
更多關於錯誤排除請參考 – https://benjr.tw/103296
沒有解決問題,試試搜尋本站其他內容