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 | ```
';