> For the complete documentation index, see [llms.txt](https://close.gitbook.io/yun-wei-bi-ji/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://close.gitbook.io/yun-wei-bi-ji/centos/mysql/3mysql-zhu-cong-leng-bei.md).

# 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;
测试略.....
```