2-1 MySQL异步复制
最后更新于：2022-04-02 07:43:36
#### 开启MySQL复制
所有数据库建立复制账户
>MHA需求，因为切换主库后，其他从库会作为主库，这样就不用单独再创建账户了、
```shell
CREATE USER 'repl'@'192.168.%' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%';
```
修改配置文件
```shell
master_info_repository = TABLE
relay_log_info_repository = TABLE
sync_binlog = 1
log-slave-updates = 1
log-bin=mysql-bin
binlog_format = ROW
server-id=168001230
```
注释：
```shell
#Master信息存储在表里
master_info_repository = TABLE
#Relaylog信息存储在表里
relay_log_info_repository = TABLE
#所有事务提交钱，写入binlog
sync_binlog = 1
#角色是从库时，产生binlog，级联复制用
log-slave-updates = 1
#binlog日志开关及名称
log-bin=mysql-bin
#binlog格式
binlog_format = ROW
#Server-id，保持唯一，建议IP地址
server-id=168001230
```
#### 配置MySQL复制
##### 查看MySQL主库Position情况
```shell
show master status\G
```
主库执行
```shell
[root@localhost][(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 323 | | | |
+------------------+----------+--------------+------------------+-------------------+
```
##### 从库创建同步配置
从库执行
```shell
CHANGE MASTER TO MASTER_HOST='192.168.0.230',
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=323;
```
出现两个warning
```shell
| Note | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure. |
| Note | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
```
建议SSL和 start slave 时指定用户名密码，不将密码保存在主库
从库执行
```shell
change master to master_host='192.168.0.230',,master_log_file='mysql-bin.000002',master_log_pos=323;
```
启动复制进程
```shell
start slave user='repl' password='repl';
show slave status\G;
```
##### 测试主库写入
##### 主库查看状态
出现Binlog Dump 线程
```shell
[root@localhost][(none)]> show processlist;
+----+------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 6 | repl | 192.168.0.236:10745 | NULL | Binlog Dump | 5014 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 8 | repl | 192.168.0.235:1619 | NULL | Binlog Dump | 101 | Master has sent all binlog to slave; waiting for more updates | NULL |
```
';