3、MYSQL主从冷备

Mysql 主从冷备

• A： 172.17.0.2

• B： 172.17.0.3

1. 首先创建测试数据库, <A、B 都需要创建>

create database test default charset utf8;

2. Master 上创建同步用户，并测试连通性

grant replication slave on *.* to 'replicate'@'172.17.0.3' identified by '123456';
flush privileges;

# 在 `Slave` 测试连通性
mysql -h172.17.0.2 -ureplicate -p123456 -e 'show databases'

3. 修改mysql配置文件,重启 MySQL 服务

# Master
[mysqld]
server-id = 1
log-bin=mysql-bin
binlog-do-db = test
binlog-ignore-db = mysql

# Slave
[mysqld]
server-id = 2
log-bin=mysql-bin
replicate-do-db = test
replicate-ignore-db = mysql,information_schema,performance_schema

4. 查看状态，锁表，并同步

# Master, 锁表，并获取到 File 和 Position
flush tables with read lock;
show master status\G;

# Slave， 如果同步失败的话，就 `stop slave;`  `reset slave;`
stop slave;
change master to master_host='172.17.0.2',master_user='replicate',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;

# 是否同步成功
show slave status\G;
···
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
···

5. Master 解锁表，并测试

unlock tabels;
测试略.....