[TOC]
### 一、主从备份
*****
#### 主服务器Slave配置
#### 创建同步用户
```
mysql> grant replication slave on \*.\* to 'replicate'@'218.206.70.146' identified by '123456';
mysql> flush privileges;
```
#### 测试用户
```
mysql -h59.151.15.36 -ureplicate -p123456
```
#### 修改mysql配置文件
```
server-id = 1 //唯一id
log-bin=mysql-bin //其中这两行是本来就有的，可以不用动，添加下面两行即可.指定日志文件
binlog-do-db = test //记录日志的数据库
binlog-ignore-db = mysql //不记录日志的数据库
```
#### **重启mysql服务**
```
systemctl restart mysqld.service
systemctl start mysqld.service
systemctl stop mysqld.service
```
#### **查看主服务器状态**
`show master status\G;`
*****
#### 从服务器Slave配置
#### **修改配置文件**
```
server-id = 2
log-bin=mysql-bin
replicate-do-db = test
replicate-ignore-db = mysql,information\_schema,performance\_schema
```
#### 重启从mysql服务
```
systemctl restart mysqld.service
systemctl start mysqld.service
systemctl stop mysqld.service
```
#### 用change mster 语句指定同步位置
```
mysql>stop slave; //先停步slave服务线程，这个是很重要的，如果不这样做会造成以下操作不成功。
mysql>change master to
master_host='59.151.15.36',
master_user='replicate',master_password='123456',
master_log_file=' mysql-bin.000016 ',
master_log_pos=107;
```
#### 查看从服务器（Slave）状态
```
show slave status\G;
```
#### 查看下面两项值均为Yes，即表示设置从服务器成功
```
Slave\_IO\_Running: Yes
Slave\_SQL\_Running: Yes
```
*****
## 二、主主备份
#### 创建同步用户
服务器A：
```
mysql> grant replication slave on \*.\* to 'replicate'@'218.206.70.146' identified by '123456';
mysql> flush privileges;
```
服务器B：
```
mysql> grant replication slave on \*.\* to 'replicate'@'59.151.15.36' identified by '123456';
mysql> flush privileges;
```
#### 修改配置文件my.cnf
服务器A
```
server-id = 1
log-bin=mysql-bin
binlog-do-db = test
binlog-ignore-db = mysql
```
主－主形式需要多添加的部分
```
log-slave-updates
sync_binlog = 1
auto_increment_offset = 1
auto_increment_increment = 2
replicate-do-db = test
replicate-ignore-db = mysql,information_schema
```
服务器B：
```
server-id = 2
log-bin=mysql-bin
replicate-do-db = test
replicate-ignore-db = mysql,information\_schema,performance\_schema
```
主－主形式需要多添加的部分
```
binlog-do-db = test
binlog-ignore-db = mysql
log-slave-updates
sync_binlog = 1
auto_increment_offset = 2
auto_increment_increment = 2
```
#### 分别重启A服务器和B服务器上的mysql服务
#### 分别查A服务器和B服务器作为主服务器的状态
#### 分别在A服务器和B服务器上用change master to 指定同步位置
服务器A：
```
mysql>change master to
\>master\_host='218.206.70.146',master\_user='replicate',master\_password='123456',
\> master\_log\_file=' mysql-bin.000011 ',master\_log\_pos=497;
```
服务器B：
```
mysql>change master to
\>master\_host='59.151.15.36',master\_user='replicate',master\_password='123456',
\> master\_log\_file=' mysql-bin.000016 ',master\_log\_pos=107;
```
#### 分别在A和B服务器上重启从服务线程
`mysql>start slave;`
#### 分别在A和B服务器上查看从服务器状态
`mysql>show slave status\\G;`
#### 查看下面两项值均为Yes，即表示设置从服务器成功。
```
Slave\_IO\_Running: Yes
Slave\_SQL\_Running: Yes
```
[https://www.cnblogs.com/jianmingyuan/p/10903682.html]()
