业务场景:
目前项目由于业务需要,增加一台备机,在主机宕机时(拔掉网线等),备机工作.主备机使用不同的数据库.
配置描述:
- 主机: 192.168.101.179,端口:3306,版本:5.7.35
- 备机: 192.168.101.57,端口:3306,版本:5.7.35
配置注意:
注意: 配置时一定要停止写入数据 不然主从库数据不一致会出现无法同步的情况
配置:
1.打开主数据库的my.cnf文件 编辑如下
server-id = 1 #主库id
skip-name-resolve #忽略客户端链接时的主机名日志打印(可提高速度,非必选)
long_query_time=1 #查询速度低于1秒的进行日志记录
innodb-file-per-table=1 #开启独立表空间
innodb_flush_log_at_trx_commit = 2 #提交事务的时候将 redo 日志写入磁盘中
log_warnings = 1 #记录警告标识
connect_timeout = 60 #链接超时时间
net_read_timeout = 120 #数据读取超时时间
performance_schema_max_table_instances = 400 #检测表对象的最大数量
log-bin=master-bin # bin日志名称
sync-binlog=1 #使执行1次写入后，与硬盘同步
binlog-do-db=location #主数据库名称(这里替换成你的数据库名字)
binlog-ignore-db = mysql #忽略以下库的同步
binlog-ignore-db = performance_schema #忽略以下库的同步
binlog-ignore-db = information_schema #忽略以下库的同步
relay-log = relay-log #副本日志名称
relay-log-index = relay-log.index#副本日志索引位置
binlog_format = ROW #复制方法,逐条复制
- 打开从数据库的my.cnf文件 编辑如下(除了server-id其他配置都一样)
server-id = 2 #从库id
skip-name-resolve #忽略客户端链接时的主机名日志打印(可提高速度,非必选)
long_query_time=1 #查询速度低于1秒的进行日志记录
innodb-file-per-table=1 #开启独立表空间
innodb_flush_log_at_trx_commit = 2 #提交事务的时候将 redo 日志写入磁盘中
log_warnings = 1 #记录警告标识
connect_timeout = 60 #链接超时时间
net_read_timeout = 120 #数据读取超时时间
performance_schema_max_table_instances = 400 #检测表对象的最大数量
log-bin=master-bin # bin日志名称
sync-binlog=1 #使执行1次写入后，与硬盘同步
binlog-do-db=location #主数据库名称(这里替换成你的数据库名字)
binlog-ignore-db = mysql #忽略以下库的同步
binlog-ignore-db = performance_schema #忽略以下库的同步
binlog-ignore-db = information_schema #忽略以下库的同步
relay-log = relay-log #副本日志名称
relay-log-index = relay-log.index#副本日志索引位置
binlog_format = ROW #复制方法,逐条复制
3.登录主库(使用navicat或者直接登录主库)
[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.切换数据库
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.查看主库状态
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 #当前日志文件的名字
Position: 894227729 #日志文件的位置
6.登录从库,变更从库的复制位置
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.开启从库复制
start slave;
8.查看从库slave状态
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)
如果显示如下,则表示主从复制成功:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果其中有一个IO为no 则表示失败,这时可以查看日志,看看失败原因并纠正:
Last_IO_Error: io失败原因
Last_SQL_Error:sql失败原因
Slave_SQL_Running_State:从库sql运行状态
9.查看从库master状态
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.登录主库,切换主库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.启动主库slave
start slave;
12.查看主库slave状态
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)
如果显示如下,则表示主从复制成功:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果其中有一个IO为no 则表示失败,这时可以查看日志,看看失败原因并纠正:
Last_IO_Error: io失败原因
Last_SQL_Error:sql失败原因
Slave_SQL_Running_State:从库sql运行状态
至此配置结束！这时当主库/从库 拔掉网线,在启动时会自动同步从/主库信息
