服务器部署 CentOS、VeraCrypt、Docker、主从MySQL、Redis、备份等

2020-12-23 10:00:00

服务器系统使用 CentOS 7.x

考虑数据安全，一般会选用 VeraCrypt

安装依赖

yum install fuse fuse-libs

官网获取对应系统的最新下载链接进行安装

rpm -ivh https://launchpad.net/veracrypt/trunk/1.24-update7/+download/veracrypt-console-1.24-Update7-CentOS-7-x86_64.rpm

创建加密文件，Filesystem 请选择 Linux Ext4

veracrypt -t -c

挂载和卸载

mkdir /home/work
veracrypt /home/data /home/work
veracrypt -d

考虑各种方便，请选用 Docker 安装 MySQL 和 Redis

安装并启动

yum install docker
service docker start

若服务器在国内，不能成功拉取镜像，请参考这里配置代理

拉取并开启服务

docker pull mysql:5.7.32
docker pull redis

mkdir -p /home/work/docker/mysql/data
mkdir -p /home/work/docker/redis/data

vi /home/work/docker/mysql/mysqld.cnf
vi /home/work/docker/redis/redis.conf

docker run --privileged --name mysql -p 127.0.0.1:3306:3306 -v /home/work/docker/mysql/data:/var/lib/mysql -v /home/work/docker/mysql/mysqld.cnf:/etc/mysql/conf.d/mysqld.cnf -e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai -d mysql:5.7.32
docker run --privileged --name redis -p 127.0.0.1:6379:6379 -v /home/work/docker/redis/data:/data -v /home/work/docker/redis/redis.conf:/etc/redis.conf -e TZ=Asia/Shanghai -d redis redis-server /etc/redis.conf

可以考虑为每个容器创建 run.sh 备忘 docker run ... 命令，便于日后重置容器，譬如修改监听地址或端口

带特殊符号的数据库密码，可以加单引号或使用环境变量

-e MYSQL_ROOT_PASSWORD='qwe123!@#&'
-e MYSQL_ROOT_PASSWORD="$MYSQL_ROOT_PASSWORD"

导出和导入，确保多服务器使用相同版本

docker image save -o mysql.tar mysql:5.7.32
docker image load -i mysql.tar

Redis 配置

requirepass password
save 86400 1
stop-writes-on-bgsave-error no

MySQL 主配置

[mysqld]
event_scheduler = 1
max_connections = 10000

general_log = 1
log_timestamps = SYSTEM
general_log_file = mysql_general.log

server-id = 1
log-bin = mysql-bin
binlog-ignore-db = information_schema,mysql,performance_schema,sys,test
expire_logs_days = 15

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

MySQL 从配置

[mysqld]
server-id = 2
read_only = 1
super_read_only = 1
log-bin = mysql-bin
relay_log = mysql-relay
binlog-ignore-db = information_schema,mysql,performance_schema,sys,test
expire_logs_days = 15
log-slave-updates = ON

创建容器时若直接使用从库配置请先注释 super_read_only，成功后请取消注释，避免人为手动修改

MySQL 主从同步

SHOW MASTER STATUS\G;
CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_PORT=3306, MASTER_USER='root', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
START SLAVE;
SHOW SLAVE STATUS\G;

数据备份和恢复

只读从库定期备份

0 3 */7 * * /home/work/backup/backup.sh

#!/bin/bash
cd /home/work/backup
find . -name '202*.sql' -mtime +8 -delete
find tar -name '202*.tar.gz' -mtime +8 -delete
docker exec mysql sh -c 'exec mysqldump -uroot -p"$MYSQL_ROOT_PASSWORD" --events --routines --master-data=2 --databases game data' > $(date +%Y_%m_%d).sql
tar zcf tar/$(date +%Y_%m_%d).tar.gz $(date +%Y_%m_%d).sql

可考虑部署文件服务并定期在其它服务器拉取备份文件

docker exec -i mysql sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < /home/work/docker/mysql/all-databases.sql

若非停机备份主库却想恢复最新数据请参考 mysqldump + mysqlbinlog for Backup and Restore

日志备份

0 0 * * * /home/work/mysql_general_log/rotate.sh

#!/bin/bash
docker exec mysql sh -c 'exec mv /var/lib/mysql/mysql_general.log /var/lib/mysql/mysql_general.log.bak'
docker exec mysql sh -c 'exec mysqladmin -p"$MYSQL_ROOT_PASSWORD" flush-logs general'

cd /home/work/mysql_general_log
mv /home/work/docker/mysql/data/mysql_general.log.bak mysql_general.log
tar zcf $(date +%Y_%m_%d_%H).tar.gz mysql_general.log
find . -name '202*.tar.gz' -mtime +8 -delete