本文档是MySQL主从实施文档，涉及内容如下:
1. MySQL 安装，多实例配置，涉及四个实例
2. MySQL 无损复制配置
3. 压力测试，半同步性能测试
4. cgroup 资源隔离配置，MySQL资源限制 。
5. 备份优化，备份工具脚本配置
6. 灾备环境配置，四个灾备实例，级联异步复制
2. MySQL 安装
软件包版本：mysql-5.7.31-linux-glibc2.12-x86_64.tar
实例规划，四个端口，3306,3307,3308,3309
文件夹规划：/data3306/mysql, /data3307/mysql, /data3308/mysql, /data3309/mysql
2.1. 基础配置
1 、操作系统参数
2 、selinux
3 、swap 空间管理：
4 、环境变量
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
export PATH
2.2. 二进制软件安装
1 、安装环境检查，清理环境
rpm -qa | grep -i mysql
rpm -qa | grep -i mariadb_libs
yum remove mysql-libs-5.1.73-5.el6_6.x86_64
rpm -qa | grep -i mariadb_libs
rpm -qa | grep -i mysql
find / -name *mysql*
2 、创建MySQL 用户和组
创建组
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
解压安装包
tar -xvf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
重命名软件目录
mv mysql-5.7.31-linux-glibc2.12-x86_64 mysql
重新赋权
cd /usr/local/
chown -R mysql.mysql mysql/
2.3. 数据库初始化
2.3.1. 创建数据目录并赋权
#3306
mkdir -p /data3306/mysql/data
mkdir -p /data3306/mysql/log
mkdir -p /data3306/mysql/
mkdir -p /data3306/mysql/tmp
chown -R mysql.mysql /data3306
#3307
mkdir -p /data3307/mysql/data
mkdir -p /data3307/mysql/log
mkdir -p /data3307/mysql/
mkdir -p /data3307/mysql/tmp
chown -R mysql.mysql /data3307
3308
mkdir -p /data3308/mysql/data
mkdir -p /data3308/mysql/log
mkdir -p /data3308/mysql/
mkdir -p /data3308/mysql/tmp
chown -R mysql.mysql /data3308
3309
mkdir -p /data3309/mysql/data
mkdir -p /data3309/mysql/log
mkdir -p /data3309/mysql/
mkdir -p /data3309/mysql/tmp
chown -R mysql.mysql /data3309
2.3.2. 初始化参数
2.3.2.1. 3306
[mysqld_safe]
pid-file=/data3306/mysql/data/mysqld3306.pid
[mysql]
port=3306
prompt=\\u@\\d \\r:\\m:\\s>
no-auto-rehash
default-character-set= utf8mb4
[client]
port=3306
socket=/data3306/mysql/data/mysql3306.sock
[mysqld]
#dir
basedir=/usr/local/mysql
datadir=/data3306/mysql/data
server_id=1
port=3306
user=mysql
socket=/data3306/mysql/data/mysql3306.sock
pid-file=/data3306/mysql/data/mysqld3306.pid
tmpdir=/data3306/mysql/tmp
lc_messages_dir=/usr/local/mysql/share
#binlog
binlog_cache_size=512K
max_binlog_cache_size=2G
max_binlog_size=500M
binlog-format=ROW
log_bin = /data3306/mysql/log/mysql-bin
log-bin-index = /data3306/mysql/log/bin-index
relay_log = /data3306/mysql/log/mysql-relay-bin
expire_logs_days=15
sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
##### 双一
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_doublewrite = 1
#####GTID 复制
gtid-mode = ON
enforce-gtid-consistency = ON
### 线程配置
innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_purge_threads=1
### 日志
log_slow_admin_statements=1
log_warnings=2
long_query_time=3
slow_query_log=1
general_log=0
log_error=/data3306/mysql/log/mysqld_err.log
slow_query_log_file=/data3306/mysql/log/mysql_slow.log
### 分离undo
innodb_undo_tablespaces=3
innodb_undo_logs=128
innodb_undo_directory =/data3306/mysql/data
### 日志大小
innodb_log_files_in_group=3
innodb_log_file_size=1G
#innodb
#innodb_buffer_pool_instances=4
innodb_log_buffer_size=32G
innodb_max_dirty_pages_pct=70
innodb_io_capacity=2000
innodb_open_files=60000
innodb_file_format=Barracuda
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_change_buffering=inserts
innodb_adaptive_flushing=1
innodb_old_blocks_time=1000
innodb_stats_on_metadata=0
innodb_use_native_aio=0
innodb_lock_wait_timeout=120
innodb_rollback_on_timeout=0
innodb_strict_mode=1
transaction-isolation=READ-COMMITTED
#server
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci
lower_case_table_names=1
skip-external-locking
safe-user-create
local-infile=1
performance_schema=1
show_compatibility_56=1
query_cache_type=0
query_cache_size=0
query_cache_limit=1M
query_cache_min_res_unit=1K
###22M*300= 64G
max_connections=3000
max_user_connections=3000
thread_stack=512K
read_rnd_buffer_size=2M
sort_buffer_size=2M
join_buffer_size=512K
read_buffer_size=512K
max_heap_table_size = 16M
#binlog_cache_size=512K
skip-name-resolve
skip-ssl
max_connect_errors=65535
max_allowed_packet=256M
connect_timeout=8
net_read_timeout=30
net_write_timeout=60
back_log=1024
# slave ，如果事务并发不高不建议开启，反而会降低性能
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=8
relay_log_recovery=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
###### 半同步复制
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1
rpl_semi_sync_master_timeout =1000 ## 半同步降级时间
rpl_semi_sync_master_wait_no_slave = 1
rpl_semi_sync_master_wait_point = AFTER_SYNC
#myisam
key_buffer_size=64M
myisam_sort_buffer_size=64M
concurrent_insert=2
delayed_insert_timeout=300
[mysqldump]
quick
max_allowed_packet=1024M
2.3.3. 初始化数据库实例
/usr/local/mysql/bin/mysqld --defaults-file=/data3306/mysql/my.cnf --user=mysql --initialize-insecure
2.3.4. 启动实例
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data3306/mysql/my.cnf --user=mysql &
2.3.5. 实例基础配置
1 、修改密码
set password for root@localhost = password('123');
alter user 'root'@'localhost' identified by '123456';
2 、实例启动脚本
3. 配置无损复制
1 、主库创建同步用户
grant replication slave on *.* to 'u_repl'@'%' identified by '123456';
2 、备库创建同步用户，主从切换使用
grant replication slave on *.* to 'u_repl'@'%' identified by '123456';
3 、在两台MySQL 服务器上检查"have_dynamic_loading" 是否为YES
show variables like 'have_dynamic_loading';
4 、主库安装插件（主库备库都要安装）
install plugin rpl_semi_sync_master soname 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
4 、备库安装插件
install plugin rpl_semi_sync_master soname 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
5 、主库导出
mysqldump -uroot -p123456 --master-data=2 -S /data3306/mysql/data/mysql3306.sock --single-transaction --set-gtid-purged=on --all-databases >all.sql
6 、备库导入
需要reset master
reset mastser;
mysql -uroot -p123456 -S /data3306/mysql/data/mysql3306.sock < all.sql
7 、配置复制关系
change master to
master_host='192.168.56.11',
master_port=3306,
master_user='u_repl',
master_password='123456',
master_auto_position=1,
master_connect_retry=30;
8 、检查半同步状态
主库：
show status like 'Rpl_semi_sync_master_status';
备库
show status like 'Rpl_semi_sync_slave_status';
9 、参数文件配置
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1
rpl_semi_sync_master_timeout =1000 ## 半同步降级时间
rpl_semi_sync_master_wait_no_slave = 1
rpl_semi_sync_master_wait_point = AFTER_SYNC
4. 资源隔离配置
Linux7 开始，建议使用system unit 进行资源隔离，如果无法使用，使用libcgroup 工具。
4.1. cgroup 配置,libcgroup
1 、安装cgroup
yum install libcgroup libcgroup-tools
2 、检查服务状态
systemctl status cgconfig
1 、创建控制群组
cgcreate -g cpuset:/test
2 、删除控制群组
cgdelete -g cpuset:/test
3 、设置群组参数
cgset -r cpuset.cpus=0-1 test
4 、将某个进程移入子系统 ps -ef|grep mysql
cgclassify -g cpuset:/test 9532
4.2. system 单元限制CPU
5. 压力测试
5.1. 场景一：开启半同步
5.2. 场景二：关闭半同步
6. 备份工具配置
6.1. xtrabackup 安装
1 、基础依赖包配置
1 、解压安装包
tar -xvf percona-xtrabackup-2.4.20-Linux-x86_64.el7.libgcrypt153.tar.gz -C /usr/local/
重命名软件目录
mv percona-xtrabackup-2.4.20-Linux-x86_64 xtrabackup
环境变量配置
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin:/usr/local/xtrabackup/bin
6.2. xtrabackup 备份
1 、全量备份
innobackupex --user=root --password=123456 -P3306 -S /data3306/mysql/data/mysql3306.sock /data3306/mysql/backup
2 、增量备份
3 、日志备份
3 、恢复
innobackupex --apply-log --redo-only /data/backup/full/2017-01-20_10-52-43
innobackupex --copy-back /data/backup/2018-05-21_15-02-53/
innobackupex --decompress ${decompress_dir}auto_nfs_restore_xbstream/ > ${workdir}decompress.txt 2>&1
innobackupex --apply-log ${decompress_dir}auto_nfs_restore_xbstream/ > ${workdir}decompress_apply_log.txt 2>&1
innobackupex --apply-log ${decompress_dir}auto_nfs_restore_tar/ > ${workdir}tar_apply_log.txt 2>&1