主从库配置log-bin和server_id参数
cat /etc/my.cnf
log-bin=mysql-bin
server_id=xx
将备份传到备库
D:\backup\FMPVFSW-20200912023001.sql
在备库恢复备份数据
mysql -uibmhmzhou -p"q1w2e3Q!W@E#"
--default-character-set=utf8 < FMPVFSW-20200916023001.sql
确认同步起始点
D:\backup>more FMPVFSW-20200916023001.sql
-- MySQL dump 10.13
Distrib 5.7.21, for Win64 (x86_64)
--
-- Host: localhost
Database:
-- ------------------------------------------------------
-- Server version
5.7.21-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT
*/;
/*!40101 SET
@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET
@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS,
UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
*/;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery
from
--
-- CHANGE MASTER TO
MASTER_LOG_FILE='WIN-1L25QKK5048-bin.002430', MASTER_LOG_POS=230699133;
主备库创建同步账号
grant
replication slave, super, reload on *.* to 'repl'@'%' identified by 'P@ssw0rd';
启动同步
change master to
master_host='10.0.9.34',master_port=3306,master_user='repl',master_password=''P@ssw0rd
',master_log_file='WIN-1L25QKK5048-bin.002430',master_log_pos=230699133;
start slave;
检查同步状态
mysql>
show slave status\G
***************************
1. row ***************************
Slave_IO_State: Waiting for
master to send event
Master_Host: 10.0.101.65
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000032
Read_Master_Log_Pos: 364
Relay_Log_File:
mysqld-relay-bin.000002
Relay_Log_Pos: 509
Relay_Master_Log_File: mysql-bin.000032
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: 364
Relay_Log_Space: 665
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:
1 row in
set (0.00 sec)
从库切换为主库步骤
1.
修改参数文件注释super_read_only=ON，并重启数据库服务
2.
停止复制进程，清理复制配置信息
stop slave
reset slave all;
