[17] Mysql主从配置-优快云博客

[17] Mysql主从配置

17.1 Mysql主从介绍

MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B会将A机器的数据再写入自己的设备中，两者数据实时同步

MySQL主从是基于binlog的，主上须开启binlog才能进行主从
大致有3个步骤：

1. 主将更改操作记录到binlog里
2. 从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里
3. 从根据relaylog里面的sql语句按顺序执行

Mysql主从复制原理：
1. 主上有一个log dump线程，用来和从的I/O线程传递binlog
2. 从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地

17.2 配置主从

配置主 master 192.168.1.11

1. 主设备安装mysql
2. 修改my.cnf,增加server-id=130和log_bin=master
3. 重启mysql服务
4. 把mysql库备份并恢复成test库，作为测试数据
   mysqldump -uroot -p000000 mysql > /tmp/mysql.sql
   mysql -uroot -p000000 -e “create database test”
   mysql -uroot -p000000 test < /tmp/mysql.sql
5. 创建用于主从同步数据的用户
   grant replication slave on *.* to 'repl'@192.168.1.55（从服务器ip） identified by '000000';
6. 锁定主服务器数据库数据，不让读写操作
   flush tables with read lock;
7. show master status;
MySQL [(none)]> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| master.000001 |   652304 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
记住以上值，从服务器配置需要使用

配置从 slave 192.168.1.55

1. 从设备安装mysql
2. 修改my.cnf,增加server-id=133（和主不一致）
3. 重启mysql
4. 将主上test库同步到从上，有以下操作
    从创建test库，把主上备份的mysql.sql拷贝至从上，然后导入从上test库
    [root@02 ~]# mysql -uroot -p000000 -e 'create database test'
    [root@02 ~]# scp 192.168.1.11:/tmp/*.sql /tmp/
    [root@localhost ~]# mysqldump -uroot -p000000 mysql < /tmp/mysql.sql 
    在同步数据库时两边的mysql库数据要保持一致
5. [root@02 ~]# mysql -uroot -p000000
   MySQL > stop slave;
6. 实现主从的关键步骤：
   change master to master_host='192.168.1.11', master_user='repl', master_password='000000', master_log_file='master.000001（主配置show master status显示的filename）', master_log_pos=652304（主配置show master status显示的Position);
   start slave;
7. 查看slave状态，判断主从是否复制成功
    MySQL > show slave status\G;
    以下字段值为Yes为配置成功
      Slave_IO_Running: Yes
      Slave_SQL_Running: Yes
      Seconds_Behind_Master: 0  主从延迟时间
8. 到主上恢复写操作
    [root@01 ~]# mysql -uroot -p000000
    MySQL > unlock tables;

几个配置参数

/etc/my.cnf中定义，可以在主上配置，也可以在从上配置

 主服务器上
 binlog-do-db=      //仅同步指定的库，多个库使用英文逗号分割
 binlog-ignore-db= //忽略指定库
 从服务器上
 replicate_do_db= //仅同步指定的库
 replicate_ignore_db= //忽略指定库
 replicate_do_table= //仅同步指定的表
 replicate_ignore_table= //忽略指定表 （联合查询，多个表同时查询时，会出现数据不完整）

 可以经常使用以下两条配置，上面4条谨慎操作
 replicate_wild_do_table=   //如aming.%, 支持通配符%
 replicate_wild_ignore_table=