MySQL 5.7.28 主从复制实现

1.环境准备

       1.MySQL版本：5.7.28

       2.两台已安装 MySQL 的 Linux服务器  （Linux如何安装MySQL，请跳转：CentOS 6.2 安装 MySQL 5.7.28）

          IP：192.168.204.201(master)   192.168.204.202(slave)

2.主从复制原理

原理解析：

  1. Master 将操作记录到二进制日志(binary log)中 （这些记录叫做二进制日志事件，binary log events）

   2. Slave 通过 I/O Thread 异步将 master 的 binary log events 拷贝到它的中继日志(relay log)；

   3. Slave 执行 relay 日志中的事件，匹配自己的配置，将需要执行的数据在 slave 服务上执行一遍，从而达到复制数据的目的。

3.主从复制如何配置

  Ⅰ.Master 配置（附：图）

     1.在 Master 上创建一个复制用户，该用户具有复制主库数据权限，具有replication slave 权限（仅仅是一个复制数据的用户）

创建用户命令：create user 'repl' identified by 'repl';       （用户名为：repl    密码为：repl，可自定义）

    2.给新建的用户赋予权限(replication slave 权限)

赋予权限命令：grant replication slave on *.* to 'repl'@'%' identified by 'repl';

    3.编辑 my.cnf 配置文件(在 [mysqld] 类下)

server-id=201
log-bin=/var/lib/mysql/mysql_binlog
binlog-do-db=db_user  (这个配置也可以不加，则除了 binlog-ignore-db 外的库，都备份)
binlog-do-db=db_order
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
slave-skip-errors=1

     4.重启 MySQL 服务

重启SQL服务命令：service mysqld restart

      5.此处排除需要备份数据库步骤，仅做学习操作；

备份要同步的数据库   请使用 mysqldump 命令来完成。

      6.通过 show master status，查看 master db状态

  Ⅱ.Slave 配置（附：图）

    1.修改 slave 的my.cnf配置文件，加入如下内容

#指定唯一id(一般IP最后一位)
server-id=202
#relay中继日志存储的位置(非必选,默认位置在数据文件的目录)。即：存储主库的binlog event主库binlog：记录主库发生过的修改事件
relay-log=/var/lib/mysql/slave-relay-bin
#relay中继日志索引存储的位置(同relay-log,非必选,默认位置在数据文件的目录)
relay-log-index=/var/lib/mysql/slave-relay-bin.index
#只读(保证slave同步的同时,普通用户处于只读状态.super权限用户不受只读控制,为了确保所有用户包括super权限用户也不能进行其他操作，需要执行给所有表加锁命令:flush tables with read lock;这样使用具有super权限的用户登录数据库，想要发生数据变化的操作时，也会提示表被锁定不能修改的报错)
read_only=1
#指定slave复制哪个库
replicate_do_db=db_user
replicate_do_db=db_order
#指定slave只对某个表进行复制
replicate_do_table=db_user.user_info

#二进制日志过期清理时间。默认值为0，表示不自动清理。

​​​expire_logs_days=7

    2.重启 MySQL 服务

命令：service mysqld restart

    3.slave 接入 master 的服务，配置信息如下(注意标红位置）

       此处要注意 log_file 的名称和 log_pos 的值，其余host、port、user和password为主数据库复制用户设置的账号和密码

change master to
    master_host='192.168.204.201',
    master_port=3306,
    master_user='repl',
    master_password='repl',
    master_log_file='mysql_binlog.000005',
    master_log_pos=154;

   4.如果 slave 配置出错啥的，可以通过如下命令重置 slave 

命令：reset slave;

   5.启动 slave 服务

命令：start slave;

       表示同时启动 I/O 线程 和 SQL线程。I/O线程从主库读取binlog，并存储到relay log中继日志文件中。SQL线程读取中继日志，解析后，在从库重放。(也可以只启动 I/O 线程 或者 SQL线程 一个)

    6.查看 slave 状态

命令：show slave status\G;

  Ⅲ.至此，主从复制配置成功，

       接下来进行测试操作

4.测试主从复制(通过Navicate来测试)

   1.slave 从库新增一个用户 lzb，赋予 lzb 用户 select 权限（所有 MySQL权限，请点击查看：MySQL权限）

      

   2.目前配置为：

      ①master 只复制 db_user 和 db_order 库，

      ②slave 只复制 db_user 库的 user_info 表，和 db_order库的所有表

      ③slave 库 所有权限（普通用户+root权限用户）对其只有读操作，不具有其他操作

  3.Master 执行如下SQL操作：

-- -------------------db_user数据库操作------------------
-- 创建db_user数据库
create database db_user;
					-- -----------------结果：slave库会复制 db_user 库

-- 切换到db_user数据库
use db_user;

-- (db_user库)创建user_info表
create table user_info(
	id int(11) NOT NULL AUTO_INCREMENT,
	name varchar(255) DEFAULT NULL default '',
	PRIMARY KEY (id)
);                       

-- (db_user库,user_info表)插入2条数据
insert into user_info(id,name) values(1,'Mary');
insert into user_info(id,name) values(2,'Lucy');
					-- -----------------结果：slave库会复制 user_info 表

-- (db_user库)创建address_info表
create table address_info(
	id int(11) NOT NULL AUTO_INCREMENT,
	address varchar(255) DEFAULT NULL default '',
	PRIMARY KEY (id)
);

-- (db_user库,address_info表)插入2条数据
insert into address_info(id,address) values(1,'BeiJing');
insert into address_info(id,address) values(2,'ShangHai');
					-- ---因为设置salve只对db_user中user_info表复制
					-- ---结果：slave库不会复制 address_info 表

-- -------------------db_order数据库操作------------------
-- 创建db_order数据库
create database db_order;
					-- -----------------结果：slave库会复制 db_user 库

-- 切换到db_order数据库
use db_order;

-- (db_order库)创建order_info表
create table order_info(
	id int(11) NOT NULL AUTO_INCREMENT,
	num int(11) NOT NULL,
	PRIMARY KEY (id)
);

-- (db_order库,order_info表)插入2条数据
insert into order_info(id,num) values(1,15);
insert into order_info(id,num) values(2,8);

-- (db_order库)创建order_info表
create table order_ohter(
	id int(11) NOT NULL AUTO_INCREMENT,
	num int(11) NOT NULL,
	PRIMARY KEY (id)
);

-- (db_order库,order_ohter表)插入2条数据
insert into order_ohter(id,num) values(10,23);
insert into order_ohter(id,num) values(89,85);
					-- -----------------结果：slave库会复制 db_order 库中的所有表
					

   4.主从复制-----测试结果图示：

5. Slave只读问题

  1.MySQL只读问题

     1. 对于数据库读写状态，主要靠 "read_only"全局参数来设定。

     2. 默认情况下，数据库是用于读写操作的，所以 read_only 参数也是 0 或 false 状态，这时候不论是本地用户还是远程访问数据库的用户，都可以进行读写操作；

     3. 如需设置为只读状态，将 read_only 参数设置为1或 true 状态，但设置 read_only=1 状态有两个需要注意的地方：

          ①read_only=1 只读模式，不会影响slave同步复制的功能，所以在MySQL slave库中设定了read_only=1后，通过 show slave status\G 命令查看 salve 状态，可以看到 salve 仍然会读取 master 上的日志，并且在slave库中应用日志，保证主从数据库同步一致；
          ②read_only=1 只读模式，可以限定普通用户进行数据修改的操作，但不会限定具有 super 权限的用户的数据修改操作；在MySQL中设置read_only=1后，普通的应用用户进行insert、update、delete等会产生数据变化的DML操作时，都会报出数据库处于只读模式不能发生数据变化的错误，但具有 super 权限的用户，例如在本地或远程通过 super 权限用户登录到数据库，还是可以进行数据变化的DML操作；

  2.super权限用户怎么控制读写？

     1. 为了确保所有用户，包括具有 super 权限的用户也不能进行读写操作，就需要执行给所有的表加读锁的命令 。flush tables with read lock;这样使用具有 super 权限的用户登录数据库，想要发生数据变化的操作时，也会提示表被锁定不能修改的报错。

       ①这样通过 设置 "read_only=1" 和 "flush tables with read lock;" 两条命令，就可以确保数据库处于只读模式，不会发生任何数据改变。

       ②但同时由于加表锁的命令对数据库表限定非常严格，如果在slave从库上执行这个命令后，slave 库可以从 master 读取binlog 日志，但不能够应用日志，slave 库不能发生数据改变，当然也不能够实现主从同步了，这时如果使用 "unlock tables;" 解除全局的表读锁，slave就会应用从 master 读取到的 binlog 日志，继续保证主从库数据库一致同步。所以 表锁无法解决 super权限用户的读写问题

     2. 为了保证主从同步可以一直进行，在 slave 库上要保证具有 super 权限用户只能在本地登录，不会发生数据变化，其他远程连接的应用用户只按需分配为 select、insert、update、delete等权限，保证没有super权限，则只需要将salve设定"read_only=1"模式，即可保证主从同步，又可以实现从库只读。

    3. 设定 "read_only=1" 只读模式开启的解锁命令为设定 "read_only=0"；设定全局锁 "flush tables with read lock;"，对应的解锁模式命令为："unlock tables;".

    4. 当然设定了read_only=1后，所有的select查询操作都是可以正常进行的。

  3.本例 slave 中，root用户登录具有 super权限，故他可以随意删除修改数据，所以我们将slave库上的 root 用户修改为只能在本地登录即可。

      3.1 修改已有用户，host 为 % ，随意登录情况

           

      3.2 创建具有 super 权限的新用户，设置 Host 为只允许本机登录

           

---

End