MySQL主从复制架构

什么是主从复制

MYSQL主从同步是目前使用比较广泛的数据库架构，技术比较成熟，配置也不复杂，特别是对于负载比较大的网站，主从同步能够有效缓解数据库读写的压力。

环境规划

传统的主从复制

安装前准备工作

1、时间同步
2、上传同样的MySQL安装包到服务器中
3、关闭防火墙和selinux

开始实验

在master服务器端安装MySQL

vim mysql.sh

#!/bin/bash
yum install libaio -y
tar -zxvf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz 		#这里填写的是自己的MySQL版本
mv mysql-5.7.36-linux-glibc2.12-x86_64 /usr/local/mysql		#这里填写的是自己的MySQL版本，后面目录为mysql安装目录
useradd -r -s /sbin/nologin mysql
rm -rf /etc/my.cnf
cd /usr/local/mysql
mkdir mysql-files
chown -R  mysql:mysql  /usr/local/mysql
chown 750 /usr/local/mysql
bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql &> /root/password.txt
bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data
cp support-files/mysql.server /etc/init.d/mysqld
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
source /etc/profile

给这个脚本添加执行权限：chomd 750 ./mysql.sh
执行这个脚本：./mysql.sh
注意脚本文件和MySQL的安装包在同一目录下
vim /etc/my.cnf

[mysqld]
basedir=/usr/local/mysql			#MySQL安装目录
datadir=/usr/local/mysql/data		#mysql数据目录
port=3306
socket=/tmp/mysql.sock
character-set-server=utf8mb4
log-error=/usr/local/mysql/data/mysqld.log		#mysql日志文件
log-bin=/usr/local/mysql/data/binlog			#二进制文件位置
server-id=1

在slave服务器端安装MySQL

vim mysql.sh

#!/bin/bash
yum install libaio -y
tar -zxvf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.36-linux-glibc2.12-x86_64 /usr/local/mysql
useradd -r -s /sbin/nologin mysql
rm -rf /etc/my.cnf
cd /usr/local/mysql
mkdir mysql-files
chown -R mysql:mysql /usr/local/mysql
chown 750 /usr/local/mysql
cp support-files/mysql.server /etc/init.d/mysqld
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
source /etc/profile

给这个脚本添加执行权限：chomd 750 ./mysql.sh
执行这个脚本：./mysql.sh
注意脚本文件和MySQL的安装包在同一目录下
vim /etc/my.cnf

[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
socket=/tmp/mysql.sock
character-set-server=utf8mb4
log-error=/usr/local/mysql/data/mysqld.log
relay-log=/usr/local/mysql/data/relaylog
server-id=10

相对于主服务器MySQL的安装部署，从服务器端不需要进行初始化操作，因为器数据来自于主服务器

启动msyql

[root@master data]# service mysql start

登录msyql
先查看mysql密码：cat /root/password.txt (最后一行是密码)

mysql -uroot -p

修改mysql密码

mysql> set password=password('root');

将master主服务器的数据同步到slave从服务器

把master服务器中的/usr/local/mysql/data/auto.cnf

[root@master data]# rm auto.cnf

auto.cnf是mysql的编号，相当于人的身份证

将master服务端的/usr/local/mysql/data目录拷贝到slave服务端/usr/local/mysql

[root@master data]# rsync -av /usr/local/mysql/data root@192.168.156.20:/usr/local/mysql/

同时启动master端和slave端mysql

[root@master data]# service mysql start
[root@slave mysql]# service mysql start

配置master-slave主从同步

在master主服务器创建一个账号，用于同步数据用

mysql> grant replication slave on *.* to 'admin'@'192.168.156.%' identified by 'admin';
mysql> flush privileges;

在master端中锁表，然后查看二进制文件的名称及位置

mysql> flush tables with read lock;
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |      602 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在slave端，使用changer master to指定主服务器，并执行数据同步

change master to master_host='192.168.156.15',master_user='admin',master_password='admin',master_port=3306,master_log_file='binlog.000003',master_log_pos=602;

master_host master端主机的IP地址
master_user master端主机的user账号
master_password master端主机user密码
master_port master端主机的mysql端口
master_log_file 刚才查看到的log名称
master_log_pos 刚才查看到log对应的position值

在slave端启动slave同步

mysql> start slave;
mysql> show slave status\G

解除master端表解锁

mysql> unlock tables;

注意；不要在slave端写入数据，如果在slave端写入数据，会打破主从架构
如果不小心在slave端写入的数据不是很多，可以使用sql_slave_skip_counter跳过语句，如果写入的数量较多，需要重新搭建

mysql> set global sql_slave_skip_counter=n		//跳过n条事务
mysql> stop slave;
mysql> start slave;

基于GTIDs的主从架构(基于innodb引擎)

什么是GTIDs以及其特点

1、GTIDs：全局事务表示符，是mysql5.6新加入的一项技术
2、当使用GTIDs时，每一个事务都可以被识别并且跟踪
3、添加新的slave或者当发生故障需要将master身份迁移到slave上时，都无需考虑哪一个二进制以及哪个position值，极大简化了相关操作
4、GTIDs是完全基于事务的，因此不支持MYISAM存储引擎
5、GTID由source_id和transaction_id组成：
source_id来自于server_uuid，可以在auto.cnf中看到
transaction_id是一个序列数字，自动生成

使用GTIDs的限制条件有哪些

1.不支持非事务引擎(MYISAM)，因为可能会导致多个gtid分配给同一个事务
2.ceate table … select语句不支持
3.create/drop temporary table 语句不支持
4.必须使用enforce-gtid-consistency参数
5.sql-slave-skip-counter不支持跳过语句
6.GTID复制环境中必须要求统一开启GTID或者关闭GTID
7.在mysql5.6.7之前，使用mysql_upgrade命令会出现错误

基于GTIDs的主从复制架构
1修改配置文件支持GTIDs
master端：vim /etc/my.cnf

[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
socket=/tmp/mysql.sock
character-set-server=utf8mb4
log-error=/usr/local/mysql/data/mysqld.log
log-bin=/usr/local/mysql/data/binlog
server-id=1
gtid-mode=on
log-slave-updates=1
enforce-gtid-consistency

slave端：
rm -rf /usr/local/mysql/data/binlog.*
vim /etc/my.cnf

[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
socket=/tmp/mysql.sock
character-set-server=utf8mb4
log-error=/usr/local/mysql/data/mysqld.log
relay-log=/usr/local/mysql/data/relaylog
server-id=10
log-bin=/usr/local/mysql/data/binlog
gtid-mode=on
log-slave-updates=1
enforce-gtid-consistency
skip-slave-start

1.开启GTIDs需要在master和slave上都配置gtid-mode，log-bin,log-slave-updates,enforce-gtid-consistency(在5.6.9之前这个参数为disable-gtid-unsafe-statement)
2.slave还需要增加skip-slave-start参数，目的是开启的时候，先不开启slave，先做一些配置
3.基于GTIDs复制从服务器必须开启二进制文件

重新启动mysql服务

[root@master ~]# service mysql start
[root@slave ~]# service mysql start

主从配置为只读模式
slave和master端：

mysql> set @@global.read_only=on;

slave端配置change master to

mysql> stop slave;
mysql> restop slave;
mysql> change master to master_host='192.168.156.15',master_user='admin',master_password='admin',master_port=3306,master_auto_position=1;
mysql> start slave;
mysql> show slave status\G

关闭主从服务器的只读模式
master和slave端

mysql> set @@global.read_only=off;

查看主从架构是否成功
slave端：

mysql> show slave status\G

已经成功