MySQL主从部署（同步+半同步）
一、环境规划
1.1服务器规划
服务器 IP 版本 配置 Mysql 端口 角色
172-16-104-8 172.16.104.8 CentOS release 6.8 (Final) 4c8g 5.7.27 3306 master
172-16-104-9 172.16.104.9 CentOS release 6.8 (Final) 4c8g 5.7.27 3306 slave
1.2 目录规划
软件 目录
MySQL软件安装目录 /usr/local/mysql/mysqlserver
MySQL数据文件目录 /data/mysql/data
MySQL日志文件目录 /data/mysql/logs
MySQLbinlog日志目录 /data/mysql/binlog
MySQL配置文件目录 /etc/my.cnf
MySQL软件安装目录 /usr/local/mysql/mysqlserver
二、基础配置
2.1环境描述： 准备三台基本安装的服务器
mysql-server ：4 核 2G 内存，rhat6.5，172.16.1.43
mysql-slave ：4 核 2G 内存 ，rhat6.5，172.16.1.63
2.2、配置主机名
hostname
2.3 配置ip、域名解析
vi /etc/hosts
2.3、配置 yum 源
mkdir /yum mount /dev/cdrom /yum echo " mount /dev/cdrom /yum">>/etc/rc.local
mkdir /etc/yum.repos.d/bak
mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/bak
vim /etc/yum.repos.d/local.repo
[local_yum] name=local
yum baseurl=file:///yum
enabled=1
gpgcheck=0
2.4、关闭 iptables和 selinux
vim /etc/selinux/config
SELINUX=disabled
service iptables stop
三、安装主从
3.1 创建用户
groupadd mysql
useradd -g mysql mysql -s /sbin/nologin
id mysql
3.2 配置目录
mkdir -pv /data/mysql/{data,logs,binlog,tmp,redo}
chown -R mysql:mysql /data/mysql/
3.3 解压安装包
tar -zxvf mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz -C /usr/local
mv mysql-5.7.27-linux-glibc2.12-x86_64 mysql
3.4 配置环境变量
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
source /root/.bash_profile
3.5 安装备份工具
# 解压安装包并将文件复制到 /usr/bin
tar xvf percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz ./
cd percona-xtrabackup-2.4.4-Linux-x86_64
cp bin/innobackupex /usr/bin
cp bin/xtrabackup* /usr/bin
# 安装相关插件
yum install -y perl perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey
3.5 修改配置文件
[root@mysql-tar data]# vim /etc/my.cnf
basedir=/var/lib/mysql
datadir=/var/lib/mysql/data
socket=/var/lib/mysql/mysql.sock
log-error=/var/lib/mysql/mha-server.err
pid-file=/var/run/mysqld/mysqld.pid
port=3306
server-id=1
read_only=off
innodb_buffer_pool_size=1073741824
log_bin=mha-server
binlog_format=row
innodb_doublewrite=on
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
innodb_read_io_threads=6
innodb_write_io_threads=6
innodb_purge_threads=6
slow_query_log=ON
slow_query_log_file=chen.slow
long_query_time=2
log_queries_not_using_indexes=1
log_throttle_queries_not_using_indexes=10
innodb_data_file_path = ibdata1:100M:autoextend
show_compatibility_56=on
skip_name_resolve=on
expire_logs_days = 5
binlog_rows_query_log_events=on
relay_log_purge = 0
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR
_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
'
备库：
server-id=2
relay_log_purge=0
read_only=on
slave_preserve_commit_order=1
log_slave_updates=1
slave_parallel_type=logical_clock
slave_parallel_workers=4
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
3.6 主库
3.6.1安装mysql
#初始化
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure &
检查一下是否生成必要的文件及目录
cd data/
#启动mysql
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
#修改密码
mysql> flush privileges;
mysql> alter user 'root'@'localhost' identified by '199678';
mysql> flush privileges;
3.6.2 创建复制用户
mysql> grant replication slave,replication client on *.* to 'repl'@'172.16.1..%' identified by 'repl';
#在主库上, 设置读锁定有效, 这个操作是为了确保没有数据库操作, 以便获得一个一致性的快照如果可以确定主数据库没有读写操作， 可以不执行当前步骤
mysql> flush tables with read lock;
从当前主库获得二进制日志名和偏移量值. 这个操作的目的是为了在从数据库启动以后, 从这个点开始进行数据的恢复.
mysql> show master status;
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mha-server.000003 | 154 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3.6.3全备
创建备份目录并授权
[root@root mysql]# mkdir /backup
[root@root backup]# chown -R mysql:mysql .
[root@root mysql]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --no-timestamp /backup/
mysql> unlock tables; 主数据库备份完毕后, 恢复写操作
将备份传输到备库
3.7 从库
3.7.1恢复从库
#备份授予 MySQL 权限
[root@root backup]# chown -R mysql:mysql /backup/
#应用备份
[root@root backup]# innobackupex --copy-back /backup/
#启动数据库
mysqld_safe --defaults-file=/etc/my.cnf &
3.7.2开启同步
[root@mha-server backup]# mysql -p199678
mysql> CHANGE MASTER TO MASTER_HOST='172.16.1.43',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_PORT=3306,MASTER_LOG_FILE='mha-server.000003',MASTER_LOG_POS=154,MASTER_CONNECT_RETRY=10; //log_file、log_pos的值是上一步在主库中查到的
mysql> start slave; //启动复制线程
3.8 查看同步情况
1、show processlist
从库：
mysql> show processlist;
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 5073 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 5073 | Slave has read all relay log; waiting for more updates | NULL |
| 3 | system user | | NULL | Connect | 5073 | Waiting for an event from Coordinator | NULL |
| 4 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
4rows in set (0.00 sec)
主库：
mysql> show processlist;
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 9 | repl | 172.16.1.53:34176 | NULL | Binlog Dump | 4951 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 10 | repl | 172.16.1.63:48742 | NULL | Binlog Dump | 4744 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 12 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-------------------+------+-------------+------+---------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
2、show slave status\G;
3、添加新数据， 或者创建新表检查数据是否正常同步
四、配置半同步
4.1 部署半同步
#首先判断MySQL服务器是否支持动态增加插件
mysql> select @@have_dynamic_loading
#确认支持动态增加插件后，检查MySQL的安装目录下是否存在插件semisync_master.so和semisync_slave.so
mysql> show variables like 'plugin_dir'; #查看插件存入地址
1、master端：
#安装半同步复制插件
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
#打开半同步复制功能
mysql> set global rpl_semi_sync_master_enabled=ON;
#检查半同步复制相关参数设置
mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.01 sec)
2、slave端
#安装半同步复制插件
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
#打开slave端的半同步复制功能
mysql> set global rpl_semi_sync_slave_enabled=on;
Query OK, 0 rows affected (0.00 sec)
#检查半同步复制相关参数
mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+-------------------------------------------+------------+
2 rows in set (0.00 sec)
#开启slave复制线程并检查复制状态
mysql> start slave;
mysql> show slave status\G
4.2 半同步相关参数
rpl_semi_sync_master_enabled：
# 控制master端是否打开半同步插件，默认OFF
rpl_semi_sync_master_timeout：
#控制半同步复制退化为异步复制的时间限制。若该时间限制下master还未接收到slave的ack，半同步复制退化为异步复制。默认10000（10s），该参数一般会设置比较大，保证数据无损。
rpl_semi_sync_master_trace_level：
#master半同步复制调试跟踪等级，默认为32。
1 = general level (for example, time function failures)
16 = detail level (more verbose information)
32 = net wait level (more information about network waits)
64 = function level (information about function entry and exit
rpl_semi_sync_master_wait_for_slave_count
#master提交事务并返回客户端commit时，需要获取slave的ack的个数，默认为1。该参数同样依赖rpl_semi_sync_master_wait_no_slave的设置
rpl_semi_sync_master_wait_no_slave：
#控制当slave个数小于rpl_semi_sync_master_wait_for_slave_count参数设置时，复制模式的行为，默认为ON。
ON：当MySQL发现slave个数小于rpl_semi_sync_master_wait_for_slave_count时，MySQL复制模式不做任何变更，只要数据同步时，master接收到大于等于rpl_semi_sync_master_wait_for_slave_count个数的ack，MySQL就继续保持半同步复制模式； 若master等待ack超过rpl_semi_sync_master_timeout时间限制后仍然未接收到大于等于rpl_semi_sync_master_wait_for_slave_count个数的ack，MySQL就将半同步复制退化为异步复制。
OFF：当MySQL发现slave个数小于rpl_semi_sync_master_wait_for_slave_count时，主动将半同步复制退化为异步复制
rpl_semi_sync_master_wait_point：
#控制半同步复制下,master提交事务并返回客户端commit的时机，MySQL5.7版本下默认为after_sync。
