MySQL实施文档

来源：这里教程网时间：2026-03-01 17:07:01 作者：

1. 文档介绍

本文档是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 、selinux3 、swap 空间管理：4 、环境变量PATH=$PATH:$HOME/bin:/usr/local/mysql/binexport PATH

2.2. 二进制软件安装

1 、安装环境检查，清理环境rpm -qa | grep -i mysqlrpm -qa | grep -i mariadb_libsyum remove mysql-libs-5.1.73-5.el6_6.x86_64rpm -qa | grep -i mariadb_libsrpm -qa | grep -i mysqlfind / -name *mysql* 2 、创建MySQL 用户和组创建组groupadd mysqluseradd -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. 创建数据目录并赋权

#3306mkdir -p /data3306/mysql/datamkdir -p /data3306/mysql/logmkdir -p /data3306/mysql/mkdir -p /data3306/mysql/tmpchown -R mysql.mysql /data3306#3307mkdir -p /data3307/mysql/datamkdir -p /data3307/mysql/logmkdir -p /data3307/mysql/mkdir -p /data3307/mysql/tmpchown -R mysql.mysql /data33073308mkdir -p /data3308/mysql/datamkdir -p /data3308/mysql/logmkdir -p /data3308/mysql/mkdir -p /data3308/mysql/tmpchown -R mysql.mysql /data33083309mkdir -p /data3309/mysql/datamkdir -p /data3309/mysql/logmkdir -p /data3309/mysql/mkdir -p /data3309/mysql/tmpchown -R mysql.mysql /data3309

2.3.2. 初始化参数

2.3.2.1. 3306

[mysqld_safe]pid-file=/data3306/mysql/data/mysqld3306.pid [mysql]port=3306prompt=\\u@\\d \\r:\\m:\\s>no-auto-rehash default-character-set= utf8mb4 [client]port=3306socket=/data3306/mysql/data/mysql3306.sock [mysqld]#dirbasedir=/usr/local/mysqldatadir=/data3306/mysql/dataserver_id=1port=3306user=mysql socket=/data3306/mysql/data/mysql3306.sockpid-file=/data3306/mysql/data/mysqld3306.pidtmpdir=/data3306/mysql/tmplc_messages_dir=/usr/local/mysql/share #binlogbinlog_cache_size=512Kmax_binlog_cache_size=2Gmax_binlog_size=500Mbinlog-format=ROWlog_bin = /data3306/mysql/log/mysql-binlog-bin-index = /data3306/mysql/log/bin-indexrelay_log = /data3306/mysql/log/mysql-relay-binexpire_logs_days=15sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ##### 双一innodb_flush_log_at_trx_commit = 1sync_binlog = 1innodb_doublewrite = 1#####GTID 复制gtid-mode = ONenforce-gtid-consistency = ON ### 线程配置innodb_read_io_threads=4innodb_write_io_threads=4innodb_purge_threads=1 ### 日志log_slow_admin_statements=1log_warnings=2long_query_time=3slow_query_log=1general_log=0log_error=/data3306/mysql/log/mysqld_err.logslow_query_log_file=/data3306/mysql/log/mysql_slow.log### 分离undoinnodb_undo_tablespaces=3innodb_undo_logs=128innodb_undo_directory =/data3306/mysql/data ### 日志大小innodb_log_files_in_group=3innodb_log_file_size=1G #innodb#innodb_buffer_pool_instances=4innodb_log_buffer_size=32Ginnodb_max_dirty_pages_pct=70 innodb_io_capacity=2000innodb_open_files=60000innodb_file_format=Barracudainnodb_file_per_table=1innodb_flush_method=O_DIRECTinnodb_change_buffering=insertsinnodb_adaptive_flushing=1innodb_old_blocks_time=1000innodb_stats_on_metadata=0innodb_use_native_aio=0innodb_lock_wait_timeout=120innodb_rollback_on_timeout=0 innodb_strict_mode=1transaction-isolation=READ-COMMITTED#serverdefault-storage-engine=INNODBcharacter-set-server=utf8collation-server=utf8_general_cilower_case_table_names=1skip-external-lockingsafe-user-createlocal-infile=1performance_schema=1show_compatibility_56=1 query_cache_type=0query_cache_size=0query_cache_limit=1Mquery_cache_min_res_unit=1K ###22M*300= 64Gmax_connections=3000max_user_connections=3000thread_stack=512Kread_rnd_buffer_size=2Msort_buffer_size=2Mjoin_buffer_size=512Kread_buffer_size=512Kmax_heap_table_size = 16M#binlog_cache_size=512K skip-name-resolveskip-sslmax_connect_errors=65535max_allowed_packet=256Mconnect_timeout=8net_read_timeout=30net_write_timeout=60back_log=1024 # slave ，如果事务并发不高不建议开启，反而会降低性能slave-parallel-type=LOGICAL_CLOCKslave-parallel-workers=8relay_log_recovery=ONmaster_info_repository=TABLErelay_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 = 1rpl-semi-sync-slave-enabled = 1rpl_semi_sync_master_timeout =1000 ## 半同步降级时间rpl_semi_sync_master_wait_no_slave = 1rpl_semi_sync_master_wait_point = AFTER_SYNC #myisamkey_buffer_size=64Mmyisam_sort_buffer_size=64Mconcurrent_insert=2delayed_insert_timeout=300 [mysqldump]quickmax_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" 是否为YESshow 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 masterreset mastser; mysql -uroot -p123456 -S /data3306/mysql/data/mysql3306.sock < all.sql7 、配置复制关系 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 = 1rpl-semi-sync-slave-enabled = 1rpl_semi_sync_master_timeout =1000 ## 半同步降级时间rpl_semi_sync_master_wait_no_slave = 1rpl_semi_sync_master_wait_point = AFTER_SYNC

4. 资源隔离配置

Linux7 开始，建议使用system unit 进行资源隔离，如果无法使用，使用libcgroup 工具。

4.1. cgroup 配置,libcgroup

1 、安装cgroupyum install libcgroup libcgroup-tools2 、检查服务状态systemctl status cgconfig 1 、创建控制群组cgcreate -g cpuset:/test2 、删除控制群组cgdelete -g cpuset:/test3 、设置群组参数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/backup2 、增量备份 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>&1innobackupex --apply-log ${decompress_dir}auto_nfs_restore_xbstream/ > ${workdir}decompress_apply_log.txt 2>&1innobackupex --apply-log ${decompress_dir}auto_nfs_restore_tar/ > ${workdir}tar_apply_log.txt 2>&1