电校讲课Mysql_实验3_Mysql主从复制

9.1 Mysql主从复制高可用架构

9.1.1Mysql主从复制概念

mysql 主从复制是指数据可以从一个 mysql 数据库服务器主节点复制到一个或多个从节点。mysql 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

9.1.2Mysql主从复制原理

复制过程：

1、开启binlog日志，通过把主库的binlog传送到从库，binlog从新解析出SQL应用到从库。

2、主从复制需要3个线程（dump线程、io线程、sql线程）完成。

3、主从复制是异步的逻辑的SQL语句级的复制。

复制前提：

1、主服务一定要打开二进制日志。

2、必须两台服务器（或者是多个实例）。

3、从服务器需要一次数据初始化。

如果主从服务器都是新搭建的话，可以不做初始化,如果主服务器已经运行了很长时间了，可以通过备份将主库数据恢复到从库。

4、主库必须要有对从库复制请求的用户。

5、从库需要有relay-log设置，存放从主库传送过来的二进制日志

show variables like '%relay%'。

6、在第一次的时候，从库需要change master to 去连接主库。

7、change master信息需要存放到master.info中 show variables like '%master_info%'。

8、从库怎么知道，主库发生了新的变化?通过relay-log.info记录的已经应用过的relay-log信息。

复制架构：

当 START SLAVE 语句在从库开始执行之后,从库会开启一个IO thread(线程)，负责连接主库，请求binlog，接收binlog并写入relay-log。

每当有从库IO线程连接到主库的时候，主库都会开启一个dump thrad(线程),负责响应从IO thread的请求，将binlog投递到从库。

当 START SLAVE 语句在从库开始执行之后,从库会开启一个SQL thread(线程)，这个线程读取从库 I/O 线程写到 relay log 的更新事件并负责执行relay-log中的事件。

9.1.3Mysql主从复制使用场景

（1）读写分离业务的使用场景

（2）数据实时备份，当主从复制系统某节点发生故障时，进行数据恢复故障修复场景

（3）高可用性（HA）场景

（4）性能优化，数据库服务器扩容场景

　　随着系统中业务访问量的增大，如果是单机部署数据库，就会导致 I/O 访问频率过高。有了主从复制，增加多个数据库节点，将负载分布在多个从节点上，降低单机磁盘 I/O 访问的频率，提高单个机器的 I/O 性能。

9.1.4Mysql主从复制配置

知识点梳理

熟悉mysql 主从复制的相关配置，并在主从复制中开启GTID全局事务记录。

GTID (Global Transaction ID) 是对于一个已提交事务的编号，并且是一个全局事务唯一的编号。GITD由两部分组成的，分别是source_id 和transaction_id，GTID=source_id:transaction_id，其中source_id就是执行事务的主库的server-uuid值，server-uuid值是在mysql服务首次启动生成的，保存在数据库的数据目录中，在数据目录中有一个auto.conf文件，这个文件保存了server-uuid值。而事务ID则是从1开始自增的序列，表示这个事务是在主库上执行的第几个事务。

Mysql 主从复制状态检测：

Slave_IO_Running：线程是否打开 YES/NO/NULL；

　　Slave_SQL_Running：线程是否打开 YES/NO/NULL；

　　Seconds_Behind_Master：和主库比同步的延迟的秒数。

【实验十四：主从复制基本配置】

实验要求：

直接重新搭建主从复制环境，不通过备份恢复来初始化主从同步数据，熟悉主从复制的基本配置和日常运维。

实验环境：

主节点：192.168.56.122 redhat7.4 mysql5.7.30

从节点：192.168.56.130 redhat7.4 mysql5.7.30

实验步骤：

两个服务器节点安装mysql数据库

下载mysql-5.7.30-linux-glibc2.12-x86_64.tar安装包上传服务器对应安装目录/usr/local。

1) 创建用户

[root@hdp-01 ~]# groupadd mysql //建一个名为mysql的组

[root@hdp-01 ~]# useradd -g mysql mysql //根据建好的mysql组，建一个mysql用户

[root@hdp-01 ~]# passwd mysql //设置msyql用户密码

2)创建目录

[root@hdp-01 /]# mkdir -pv /app/mysql_gtid //创建msyql数据文件目录

3)安装mysql5.7.30

[root@mysql opt]# cd /usr/local/

[root@mysql local]# tar -xvf mysql-5.7.30-linux-glibc2.12-x86_64.tar //对MySQL安装包解压缩，解压后输出结果如下:

mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz

mysql-test-5.7.30-linux-glibc2.12-x86_64.tar.gz

[root@mysql local]# tar -zxvf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz //对MySQL安装包解压

[root@mysql local]# ln -s mysql-5.7.30-linux-glibc2.12-x86_64 mysql //解压缩后，建软链接

[root@mysql mysql]# chown mysql:mysql -R /usr/local/mysql //修改mysql安装目录权限

[root@mysql mysql]# chown mysql:mysql -R /app/mysql_gtid/ //修改mysql数据文件存放目录权限

//修改mysql配置文件，主节点配置文件如下：

[root@mysql ~]# cat /etc/my.cnf

[client]

socket = /app/mysql_gtid/mysql.sock

[mysqld]

port=13306

character_set_server=utf8

basedir=/usr/local/mysql

datadir=/app/mysql_gtid

socket=/app/mysql_gtid/mysql.sock

log-error=/app/mysql_gtid/mysqld.log

pid-file=/app/mysql_gtid/mysqld.pid

lower_case_table_names = 1

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

max_connections=5000

default-time_zone = '+8:00'

gtid_mode=ON

enforce-gtid-consistency=ON

binlog_format=row

log-bin=/app/mysql_gtid/mysql-bin

master-info-repository=TABLE

relay-log-info-repository=TABLE

server-id=1223306

//从节点my.cnf配置文件：

[root@weblogic ~]# cat /etc/my.cnf

[client]

socket = /app/mysql_gtid/mysql.sock

[mysqld]

port=13306

character_set_server=utf8

basedir=/usr/local/mysql

datadir=/app/mysql_gtid

socket=/app/mysql_gtid/mysql.sock

log-error=/app/mysql_gtid/mysqld.log

pid-file=/app/mysql_gtid/mysqld.pid

lower_case_table_names = 1

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

max_connections=5000

default-time_zone = '+8:00'

gtid_mode=ON

enforce-gtid-consistency=ON

log_slave_updates=ON

skip-slave-start=1

binlog_format=row

log-bin=/app/mysql_gtid/mysql-bin

server-id=1303306

//开始进行mysql数据库的初始化：

[root@mysql etc]# /usr/local/mysql/bin/mysqld --initialize --basedir=/usr/local/mysql --datadir=/app/mysql_gtid --user=mysql --explicit_defaults_for_timestamp //指定mysql安装目录、数据文件目录进行mysql初始化

密码:root@localhost: k5h%h9#15ffA

//将mysql启动命令做成系统服务:

[root@mysql support-files]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld //将mysql启动命令拷贝至系统服务init.d目录下

[root@mysql mysql]# /etc/init.d/mysqld start //将mysql服务启动

Starting MySQL.Logging to '/app/mysql/mysql.err'.

SUCCESS!

[root@mysql ~]# mysql -uroot -pk5h%h9#15ffA //登陆mysql数据库

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) //如上所示：提示找不到sock文件。

[root@mysql ~]# find /app -name mysql.sock //在数据文件目录查找MySQL的sock套接字文件

/app/mysql/mysql.sock

建立软链接解决报错问题：

[root@mysql ~]# ln -s /app/mysql/mysql.sock /tmp/mysql.sock //将数据文件目录下的mysql.scok文件建立软链接指向到sock文件的默认目录tmp文件夹下。

[root@mysql ~]# mysql -uroot -pk5h%h9#15ffA //登陆mysql数据库

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 2

Server version: 5.7.30-log

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show databases; //显示当前所有库名称，提示在显示库名称之前必须先重置root密码

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> set password='mQlyL_g329!p'; //重置root密码

Query OK, 0 rows affected (0.01 sec)

mysql> alter user 'root'@'localhost' password expire never; //设置root 密码只允许本地访问，且不过期。

Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges; //刷新权限，使修改的权限立即生效

Query OK, 0 rows affected (0.01 sec)

2.主节点服务器上建立复制帐户并授权

mysql> GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'My1_sYn3c56'; //在主节点服务器上创建一个具有复制权限的用户mysync

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges; //刷新权限

Query OK, 0 rows affected (0.00 sec

3.从节点服务器上开启数据同步

mysql>change master to master_host='192.168.56.122',master_user='mysync',master_password='My1_sYn3c56',master_port=13306, master_auto_position=1; //在从节点服务器上建立主从复制关系的连接，包括主服务器的ip,连接主服务器的用户名、密码、端口、开始日志复制的位置点。

Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave; //启动主从复制服务

Query OK, 0 rows affected (0.01 sec)

4. 检查验证主从同步状态

mysql> start slave ; //启动主从复制服务进程

Query OK, 0 rows affected (0.06 sec)

mysql> show slave status G ; //查看主从同步状态

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.122

Master_User: mysync

Master_Port: 13306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 624

Relay_Log_File: relay-log.000003

Relay_Log_Pos: 407

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

从上图可以看出，主从复制的IO线程和SQL线程状态都为是Yes,证明该实验的主从同步状态是正常的。

实验总结：

本章实验帮助大家学习了mysql主从复制的基本原理和配置操作，为下节实验mysql双主和mysql mha高可用架构试验奠定了基础。