mysql 备库重做

mysql 8.0.11 版本

#   主库操作：

ssh 10.2.30.143(主库 端口36000)
mysql -u root -h 127.0.0.1 -P 3306 -p
输入密码

## 锁定只读

flush table with read lock;

## 退出
\q;

## dump出主库所有数据

mysqldump --set-gtid-purged=OFF --single-transaction --master-data=1 --all-databases > /tmp/xxl.sql

## 重新登录

mysql -u root -h 127.0.0.1 -P 3306 -p

输入密码
##查看下同步点位

show master status;
mysql> show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000718 | 637406306 | | | |
+------------------+-----------+--------------+------------------+-------------------+

得到：master_log_file='mysql-bin.000718', master_log_pos=637406306 为备库change master 找到同步点

##解锁
unlock tables;

退出登录
\q;

## 从主库拷贝数据到备库
cd /tmp
docker cp mysql_dev:/tmp/xxl.sit ./
scp xxl.sit root @10.2.30.176:/tmp

#备库操作

##登录备库(10.2.30.176 36000)

ssh 10.2.30.176
cd /tmp

##修改数据文件权限

chmod +777 xxl.sit

##拷贝数据到备库容器内
docker cp xxl.sit mysql_dev:/tmp/

## 登录备库

mysql -u root -h localhost -P 3306 -p

输入密码

##停止从库

stop slave;

##重置从库

reset slave all;

## 退出登录

\q;

##导入主库的数据：

mysql -u root -p < /tmp/xxl.sql

##在从库上配置需要同步的主库信息

mysql -u root -h localhost -P 3306 -p
change master to master_host='10.2.30.143', master_port=36000, master_user='repl', master_password='123456', master_log_file='mysql-bin.000718', master_log_pos=637406306;

##开启同步

start salve;

##查看同步状态

show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.2.30.143
Master_User: repl
Master_Port: 36000
Connect_Retry: 60
Master_Log_File: mysql-bin.000718
Read_Master_Log_Pos: 637423446
Relay_Log_File: edu-mysql-relay-bin.000002
Relay_Log_Pos: 17462
Relay_Master_Log_File: mysql-bin.000718
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: 637423446
Relay_Log_Space: 17674
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_UUID: a863a004-4af3-11e9-9e32-0242ac110002
Master_Info_File: mysql.slave_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:
Master_public_key_path:
Get_master_public_key: 0

这两个是 yes  证明主从同步ok

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

测试验证：

修改主库数据，同步跟主库数据保持一致，即可。