MySQL运维2-主从复制

一、主从复制概念

　　主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从服务器中，然后在从服务器上对这些日志重新执行也叫重做，从而使得从数据库和主库的数据保持同步。

　　MySQL支持一台主库同时向多台从库进行赋值，从库同时也可以作为其他从服务器的主库，实现链式复制。

　　一般主服务器我们叫Master, 从服务器叫Slave

二、主从复制优点

1. 主库出现问题，可以快速切换到从库提供服务。
2. 实现读写分离，降低主库的访问压力。
3. 可以在从库备份，以避免备份期间影响主库服务。

三、主从复制原理

1. Master 主库在事务提交时，会把数据变更记录在二进制日志文件binlog中。
2. 从库读取主库的二进制文件binlog，写入到从库的中继日志relay.log
3. slave重做中继日志中的事件，将改变从库自己的数据

四、主从复制搭建

　　4.1 MySQL服务器准备

　　　　首先准备两台已经安装了MySQL的主机，这里我分为准备了两台10.0.3.93(Master) 和 10.0.3.94(Slave)

　　4.2 主服务器配置

　　　　1、在MySQL的配置文件中，增加如下两行的配置，第三和第四的配置，可以根据需要配置

#MySQL服务ID,保证整个集群中唯一，默认是1
server-id=1
#是否只读，1代表只读，0代表读写
read-only=0
#忽略的数据，指不需要同步的数据库,和下面的一条配置默认如果不配置的话，即复制所有数据库
#binlog-ignore-db=mysql
#指定同步的数据库
#binlog-do-db=db01

　　　　2、配置好了之后，要重启MySQL服务。

　　　　3、登录mysql，创建远程连接的账号，并授予主从复制权限，这个账号是用来在从服务器上登录主服务用的。

# 创建一个mysql用户为houlei,并设置密码为：Se7eN521,该用户可以在任意主机连接该MySQL服务
mysql> create user 'houlei'@'%' identified with mysql_native_password by 'Se7eN521';
Query OK, 0 rows affected (0.03 sec)
# 为'houlei'@'%' 用户分配主从复制权限
mysql> grant replication slave on *.* to 'houlei'@'%';
Query OK, 0 rows affected (0.01 sec)

　　　　4、通过指令，查看二进制日志坐标，这里的数据也是需要在从服务上配置需要的

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000005 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

　　4.3 从服务器配置

　　　　1、从服务器上在MySQL的配置文件中增加下面的配置

#MySQL服务ID,保证整个集群中唯一
server-id=2
#是否只读 1代表只读 0代表读写
read-only=1

　　　　2、配置好了之后，要重启MySQL服务。

　　　　3、登录mysql,设置主库配置

　　　　　　mysql-8.0.23以后的版本，执行以下SQL

change replication source to host='xxx.xxx.xxx.xxx', source_user='xxx',soucre_password='xxx',source_log_file='xxx',source_log_pos=xxx;

　　　　　　mysql-8.0.23以前的版本，执行以下SQL，但是mysql-8.0.23以后的版本也是兼容mysql-8.0.23以前的这个SQL的，所以怕麻烦去查版本的，可以直接使用这个mysql-8.0.23以前的

change master to master_host='xxx.xxx.xxx.xxx',master_user='xxx',master_password='xxx',master_log_file='xxx',master_log_pos='xxx'

　　　　　　参数解释

• • source_host/master_host:  主库IP地址。
  • source_user/master_user：连接主库的用户名。
  • source_password/master_password：连接主库的密码。
  • source_log_file/master_log_file：binlog日志文件名。
  • source_log_pos/master_log_pos：binlog日志文件位置，这个参数是不用加引号或单引号的。

　　　　　　示例：注意该语句一定要在从库上执行

change master to master_host='10.0.3.93',master_user='houlei',master_password='Se7eN521',master_log_file='binlog.000005',master_log_pos=156

　　　　4、启动主从复制

• • mysql-8.0.23之后：start replica;
  • mysql-8.0.23之前：start slave;

　　　　5、查看主从同步状态

• • mysql-8.0.23之后：show replica status\G;
  • mysql-8.0.23之前：show slave status\G;　

　　　　说明1：这里主要看Slave_IO_Running和Slave_SQL_Running这两个是否为YES,全部为YES说明配置成功

五、主从复制测试

　　说明1：主从原始的都是只有四张系统表

　　说明2：主服务器上创建了一个数据库db01，在从服务器上查询，就马上显示了刚创建的db01

　　说明3：在主服务器上创建了一张表，马上就主从复制到了从服务器上

　　说明4：主表中插入数据，也会马上复制到从表中，同样的修改和删除数据也会同步复制过去。