業(yè)務(wù)場(chǎng)景:
目前項(xiàng)目由于業(yè)務(wù)需要,增加一臺(tái)備機(jī),在主機(jī)宕機(jī)時(shí)(拔掉網(wǎng)線等),備機(jī)工作.主備機(jī)使用不同的數(shù)據(jù)庫(kù).
配置描述:
- 主機(jī): 192.168.101.179,端口:3306,版本:5.7.35
- 備機(jī): 192.168.101.57,端口:3306,版本:5.7.35
配置注意:
注意: 配置時(shí)一定要停止寫入數(shù)據(jù) 不然主從庫(kù)數(shù)據(jù)不一致會(huì)出現(xiàn)無法同步的情況
配置:
1.打開主數(shù)據(jù)庫(kù)的my.cnf文件 編輯如下
server-id = 1 #主庫(kù)id
skip-name-resolve #忽略客戶端鏈接時(shí)的主機(jī)名日志打印(可提高速度,非必選)
long_query_time=1 #查詢速度低于1秒的進(jìn)行日志記錄
innodb-file-per-table=1 #開啟獨(dú)立表空間
innodb_flush_log_at_trx_commit = 2 #提交事務(wù)的時(shí)候?qū)?redo 日志寫入磁盤中
log_warnings = 1 #記錄警告標(biāo)識(shí)
connect_timeout = 60 #鏈接超時(shí)時(shí)間
net_read_timeout = 120 #數(shù)據(jù)讀取超時(shí)時(shí)間
performance_schema_max_table_instances = 400 #檢測(cè)表對(duì)象的最大數(shù)量
log-bin=master-bin # bin日志名稱
sync-binlog=1 #使執(zhí)行1次寫入后，與硬盤同步
binlog-do-db=location #主數(shù)據(jù)庫(kù)名稱(這里替換成你的數(shù)據(jù)庫(kù)名字)
binlog-ignore-db = mysql #忽略以下庫(kù)的同步
binlog-ignore-db = performance_schema #忽略以下庫(kù)的同步
binlog-ignore-db = information_schema #忽略以下庫(kù)的同步
relay-log = relay-log #副本日志名稱
relay-log-index = relay-log.index#副本日志索引位置
binlog_format = ROW #復(fù)制方法,逐條復(fù)制
- 打開從數(shù)據(jù)庫(kù)的my.cnf文件 編輯如下(除了server-id其他配置都一樣)
server-id = 2 #從庫(kù)id
skip-name-resolve #忽略客戶端鏈接時(shí)的主機(jī)名日志打印(可提高速度,非必選)
long_query_time=1 #查詢速度低于1秒的進(jìn)行日志記錄
innodb-file-per-table=1 #開啟獨(dú)立表空間
innodb_flush_log_at_trx_commit = 2 #提交事務(wù)的時(shí)候?qū)?redo 日志寫入磁盤中
log_warnings = 1 #記錄警告標(biāo)識(shí)
connect_timeout = 60 #鏈接超時(shí)時(shí)間
net_read_timeout = 120 #數(shù)據(jù)讀取超時(shí)時(shí)間
performance_schema_max_table_instances = 400 #檢測(cè)表對(duì)象的最大數(shù)量
log-bin=master-bin # bin日志名稱
sync-binlog=1 #使執(zhí)行1次寫入后，與硬盤同步
binlog-do-db=location #主數(shù)據(jù)庫(kù)名稱(這里替換成你的數(shù)據(jù)庫(kù)名字)
binlog-ignore-db = mysql #忽略以下庫(kù)的同步
binlog-ignore-db = performance_schema #忽略以下庫(kù)的同步
binlog-ignore-db = information_schema #忽略以下庫(kù)的同步
relay-log = relay-log #副本日志名稱
relay-log-index = relay-log.index#副本日志索引位置
binlog_format = ROW #復(fù)制方法,逐條復(fù)制
3.登錄主庫(kù)(使用navicat或者直接登錄主庫(kù))
[root@localhost ~]# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13222
Server version: 5.7.35-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
4.切換數(shù)據(jù)庫(kù)
mysql> use location;
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
5.查看主庫(kù)狀態(tài)
mysql> show master status\G;
*************************** 1. row ***************************
File: master-bin.000007
Position: 894227729
Binlog_Do_DB: location
Binlog_Ignore_DB: mysql,performance_schema,information_schema
Executed_Gtid_Set:
1 row in set (0.00 sec)
記錄這些值:
File: master-bin.000007 #當(dāng)前日志文件的名字
Position: 894227729 #日志文件的位置
6.登錄從庫(kù),變更從庫(kù)的復(fù)制位置
CHANGE MASTER TO
MASTER_HOST ='192.168.101.179',
MASTER_USER ='root',
MASTER_PASSWORD ='root',
MASTER_LOG_FILE = 'master-bin.000007',
MASTER_LOG_POS = 894227729;
7.開啟從庫(kù)復(fù)制
start slave;
8.查看從庫(kù)slave狀態(tài)
show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.101.179
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000007
Read_Master_Log_Pos: 894227729
Relay_Log_File: relay-log.000037
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000002
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: 4701104
Relay_Log_Space: 689
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: 2
Master_UUID: db36dfe3-9479-11ec-b4a7-7478271b8174
Master_Info_File: /var/lib/mysql/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:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
如果顯示如下,則表示主從復(fù)制成功:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果其中有一個(gè)IO為no 則表示失敗,這時(shí)可以查看日志,看看失敗原因并糾正:
Last_IO_Error: io失敗原因
Last_SQL_Error:sql失敗原因
Slave_SQL_Running_State:從庫(kù)sql運(yùn)行狀態(tài)
9.查看從庫(kù)master狀態(tài)
mysql> show master status\G;
*************************** 1. row ***************************
File: master-bin.000002
Position: 4701104 |
Binlog_Do_DB: location
Binlog_Ignore_DB: mysql,performance_schema,information_schema
Executed_Gtid_Set:
1 row in set (0.00 sec)
記錄一下值
File: master-bin.000002
Position: 4701104
10.登錄主庫(kù),切換主庫(kù)master
CHANGE MASTER TO
MASTER_HOST ='192.168.101.57',
MASTER_USER ='root',
MASTER_PASSWORD ='root',
MASTER_LOG_FILE = 'master-bin.000002',
MASTER_LOG_POS = 4701104;
11.啟動(dòng)主庫(kù)slave
start slave;
12.查看主庫(kù)slave狀態(tài)
show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.101.57
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 4701104
Relay_Log_File: relay-log.000037
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000002
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: 4701104
Relay_Log_Space: 689
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: 2
Master_UUID: db36dfe3-9479-11ec-b4a7-7478271b8174
Master_Info_File: /var/lib/mysql/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:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
如果顯示如下,則表示主從復(fù)制成功:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果其中有一個(gè)IO為no 則表示失敗,這時(shí)可以查看日志,看看失敗原因并糾正:
Last_IO_Error: io失敗原因
Last_SQL_Error:sql失敗原因
Slave_SQL_Running_State:從庫(kù)sql運(yùn)行狀態(tài)
至此配置結(jié)束！這時(shí)當(dāng)主庫(kù)/從庫(kù) 拔掉網(wǎng)線,在啟動(dòng)時(shí)會(huì)自動(dòng)同步從/主庫(kù)信息
