MySQL运维2-主从复制

合集 - mysql(25)

1.侯哥的Python分享2019-04-162.MySQL基础1-关系型数据库与非关系型数据库2022-03-173.MySQL基础2-数据库及表的操作2022-03-174.MySQL基础3-数据库增删改操作2022-03-175.MySQL基础4-数据查询07-176.MySQL基础5-用户及权限管理07-187.MySQL基础6-常用数据库函数07-188.MySQL基础7-约束07-209.MySQL基础8-多表查询07-2110.MySQL基础9-事务基础08-3111.MySQL高级1-存储引擎07-2412.MySQL高级2-SQL性能分析07-2713.MySQL高级3-索引的结构和分类07-2714.MySQL高级4-索引的使用规则07-2815.MySQL高级5-SQL优化07-3116.MySQL高级6-视图07-3117.MySQL高级7-存储过程08-1418.MySQL高级8-触发器08-1719.MySQL高级9-锁09-0420.MySQL高级10-InnoDB引擎存储架构09-1921.MySQL高级11-后台进程09-2022.MySQL高级12-事务原理 09-2023.MySQL高级13-MySQL管理工具09-2124.MySQL运维1-日志09-25

25.MySQL运维2-主从复制09-27

收起

阅读目录

• 一、主从复制概念
• 二、主从复制优点
• 三、主从复制原理
• 四、主从复制搭建
• 五、主从复制测试

一、主从复制概念

　　主从复制是指将主数据库的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：主表中插入数据，也会马上复制到从表中，同样的修改和删除数据也会同步复制过去。