某智慧项目所有环境中,除购买了三个阿里云RDS外,还在ECS上以以下方式自建了4个数据库
一、自建数据库清单
环境级别 | 数据库地址 | 搭建方式 | 版本 | 用途 |
测试环境 | 10.70.238.247:3306 | docker | 5.6.51 | 唯一业务库 |
预发环境 | 10.100.10.203:3306 | 二进制 | 5.6.46 | 唯一业务库 |
正式环境 | 10.100.10.200:3306 | docker | 5.6.51 | 日志从库 |
正式环境 | 10.100.10.201:3306 | docker | 5.7.22 | 汇聚从库 |
二、测试数据库搭建
###测试环境使用docker的方式快速搭建一个MySQL数据库,搭建方式及配置文件如下
[root@zhgd-middleware-test middleware]# pwd
/alidata/middleware
[root@zhgd-middleware-test middleware]# cat mysql.sh
docker run -p 3306:3306 --name mysql_dev --restart always \
-v /alidata/mysql/conf/my.cnf:/etc/mysql/my.cnf \
-v /alidata/mysql/data:/usr/data \
-v /alidata/mysql/logs:/usr/logs \
-v /etc/localtime:/etc/localtime:ro \
-e 'MYSQL_ROOT_PASSWORD=XXXXXXXXXX' \
-d swr.cn-east-3.myhuaweicloud.com/syh/mysql:5.6.51
[root@zhgd-middleware-test middleware]# cat /alidata/mysql/conf/my.cnf
[client]
port=3306
[mysql]
prompt=\\u@\\d \\r:\\m:\\s>
no-auto-rehash
default-character-set=utf8
[mysqld]
port=3306
server-id=113 #主从是不一样的
character-set-server=utf8
skip-external-locking
#skip-name-resolve
max_connections=2000
max_connect_errors=1000
table_definition_cache=500
table_open_cache=500
sort_buffer_size = 4M
tmp_table_size = 64M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_type=0
query_cache_size=0
lower_case_table_names = 1
log_bin_trust_function_creators = 1
open_files_limit = 65535
max_allowed_packet=1024M
interactive_timeout=600
wait_timeout=600
#################slow log####################
slow-query_log=1
slow-query_log_file=/usr/logs/mysql.slow
long_query_time=2
####################binlog######################
log-bin=mysql-bin
binlog-format=ROW
expire_logs_days=10
log-slave-updates=1
###############INNODB################
sql_mode=''
transaction-isolation=READ-COMMITTED
#innodb_buffer_pool_size=4G #根据自己的内存配置,50%到70%之间
innodb_buffer_pool_instances = 8
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
innodb_max_dirty_pages_pct = 75
innodb_open_files = 65535
innodb_file_per_table=1
innodb_io_capacity=2000
三、预发数据库搭建
###预发环境因为有数仓的调度验证,相对数据量较大,使用二进制搭建,搭建脚本如下
[root@zhgd-mysql-pre tools]# cat mysql_install.sh
#!/bin/bash
#author:王海波
#mysql5.6数据安装
#20210610增加开机自启
SoftDir=/usr/local/mysql
DataDir=/alidata/mysql/data
LogDir=/alidata/mysql/logs
Port=3306
mysqlInstall(){
#关闭selinux
setenforce 0
sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
#关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
#安装依赖包
yum -y install autoconf libaio
#创建mysql用户
useradd mysql
#解压数据库软件
tar zxvf mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz
#移动软件目录
mv mysql-5.6.46-linux-glibc2.12-x86_64 $SoftDir
#创建数据目录
mkdir -p $DataDir
#创建日志目录
mkdir -p $LogDir
#权限
chown -R mysql:mysql $DataDir
chown -R mysql:mysql $LogDir
#环境变量
cat << EOF>> /etc/profile
##mysql
export MYSQL_HOME=$SoftDir
export MY_BASEDIR_VERSION=$SoftDir
export PATH=$SoftDir/bin:\$PATH
export LD_LIBRARY_PATH=$SoftDir/lib
#####
EOF
#环境生效
source /etc/profile
#删除默认配置文件以防止发生错误
rm -f /etc/my.cnf
#mysql配置文件
cp my.cnf $SoftDir
#初始化数据库
$SoftDir/scripts/mysql_install_db --basedir=$SoftDir --datadir=$DataDir
#权限
chown -R mysql:mysql $DataDir
chown -R mysql:mysql $LogDir
chown -R mysql:mysql $SoftDir
#拷贝启动文件
cp mysql.server /etc/init.d/mysqld
#增加自动启动
chmod +x /etc/init.d/mysqld
#启动数据库
/etc/init.d/mysqld start
chkconfig --add mysqld
chkconfig mysqld on
#更新mysql的root密码
$SoftDir/bin/mysqladmin -u root password 'XXXXXXXXXX'
#创建用户并授权
$SoftDir/bin/mysql -uroot -pXXXXXXXXXX -e "CREATE USER zzsa IDENTIFIED BY 'Pinming@1024';GRANT ALL PRIVILEGES on *.* to zzsa@'%';FLUSH PRIVILEGES;"
}
echo "--------数据库安装开始----"
mysqlInstall
source /etc/profile
echo "--------数据库安装完成-----"
###配置文件如下
[root@zhgd-mysql-pre tools]# cat my.cnf
[client]
port=3306
[mysql]
prompt=\\u@\\d \\r:\\m:\\s>
no-auto-rehash
default-character-set=utf8
[mysqld]
basedir=/usr/local/mysql
datadir=/alidata/mysql/data
port=3306
#主从是不一样的
server-id=101
character-set-server=utf8
skip-external-locking
#skip-name-resolve
max_connections=1000
max_connect_errors=1000
table_definition_cache=500
table_open_cache=500
sort_buffer_size = 4M
tmp_table_size = 64M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_type=0
query_cache_size=0
lower_case_table_names = 1
log_bin_trust_function_creators = 1
open_files_limit = 65535
max_allowed_packet=1024M
#################slow log####################
slow-query_log=1
slow-query_log_file=/alidata/mysql/logs/mysql.slow
long_query_time=2
####################binlog######################
log-bin=mysql-bin
binlog-format=ROW
expire_logs_days=10
log-slave-updates=1
################INNODB################
sql_mode=''
transaction-isolation=READ-COMMITTED
#根据自己的内存配置,50%到70%之间
innodb_buffer_pool_size=16G
innodb_buffer_pool_instances= 8
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
innodb_max_dirty_pages_pct=75
innodb_file_format=Barracuda
innodb_log_files_in_group=3
innodb_open_files=65535
innodb_file_per_table=1
innodb_io_capacity=2000
四、日志从库数据库搭建
###搭建方式如下,配置文件略,参考测试环境的配置文件
[root@zhgd-mysql-log-slave-prod scripts]# cat mysql_slave.sh
###基于docker安全基线要求
docker run -p 3306:3306 --name mysql_slave --restart always \
-v /alidata/mysql/conf:/etc/mysql/conf.d \
-v /alidata/mysql/logs:/var/log/mysql \
-v /alidata/mysql/data:/var/lib/mysql \
-v /alidata/mysql/localtime:/etc/localtime \
-v /alidata/mysql/tmp:/tmp \
-v /alidata/mysql/socket:/var/run/mysqld \
--read-only \
-e MYSQL_ROOT_PASSWORD=XXXXXXXXXX \
-d swr.cn-east-3.myhuaweicloud.com/syh/mysql:5.6.51
五、汇聚从库数据库搭建
这里我们因为BI分析需要建立一个汇聚同步,考虑到新增库表时的自动同步需求,用cloudcanal、DTS等数据同步方式皆不合适,故使用了MySQL原生的多主一从的方式,主库为智慧工地生产的中台和业务RDS。下面详细交底搭建方式
1、使用脚本备份中台RDS
因为要应用多个备份,所以不适合使用物理备份方式,这里我们使用多线程逻辑备份工具mydumper,日常运维中可使用此脚本加入crontab中做自动备份
#!/bin/bash
source /etc/profile
IP=zhgd-ztdb.mysql.rds.aliyuncs.com
DATE=`date +%Y%m%d%H%M%S`
USERNAME=ztsa
PASSWORD=XXXXXXXXXX
RMDIR=/alidata/backup/mysqlbackup/$IP/
BACKUPDIR=/alidata/backup/mysqlbackup/$IP/data/$DATE
PORT=3306
LogFile=/alidata/tools/mysqlbackup-zt.log
if [ ! -d "$BACKUPDIR" ]; then
mkdir -p $BACKUPDIR
fi
echo -e "\033[36m ---------------------开始备份 $DATE -----------------\033[0m">>$LogFile
/usr/local/bin/mydumper -h $IP -u $USERNAME -p $PASSWORD -P $PORT -k -t 4 -F 64 -c -o $BACKUPDIR
if [ $? -eq 0 ]; then
echo -e " \033[32m 三局复制中台库数据库 $i 自动备份成功\033[0m">>$LogFile
#微信报警
curl 'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=5c254a09-e588-4813-9c4c-87585b8e8f1f' \
-H 'Content-Type: application/json' \
-d '
{
"msgtype": "markdown",
"markdown": {
"content": "<font color=\"warning\">三局复制中台库数据库 '$i' 自动备份成功</font>"
}
}'
else
echo -e "\033[31m 三局复制中台库数据库 $i 备份失败\033[0m">>$LogFile
#微信报警
curl 'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=5c254a09-e588-4813-9c4c-87585b8e8f1f' \
-H 'Content-Type: application/json' \
-d '
{
"msgtype": "markdown",
"markdown": {
"content": "<font color=\"warning\">三局复制中台库数据库 '$i' 自动备份失败,请快速解决问题!!</font>"
}
}'
fi
if [ -d "$RMDIR" ]; then
find /alidata/backup/mysqlbackup/$IP/data/ -mtime +1 -name "20*" -exec rm -rf {} \;
fi
echo -e "\033[36m ---------------------备份结束 ----------------- \033[0m">>$LogFile
2、使用脚本备份业务RDS
#!/bin/bash
source /etc/profile
IP=zhgd-zyy.mysql.rds.aliyuncs.com
DATE=`date +%Y%m%d%H%M%S`
USERNAME=zzsa
PASSWORD=XXXXXXXXXX
RMDIR=/alidata/backup/mysqlbackup/$IP/
BACKUPDIR=/alidata/backup/mysqlbackup/$IP/data/$DATE
PORT=3306
LogFile=/alidata/tools/mysqlbackup-zyy.log
if [ ! -d "$BACKUPDIR" ]; then
mkdir -p $BACKUPDIR
fi
echo -e "\033[36m ---------------------开始备份 $DATE -----------------\033[0m">>$LogFile
/usr/local/bin/mydumper -h $IP -u $USERNAME -p $PASSWORD -P $PORT -k -t 4 -F 64 -c -o $BACKUPDIR
if [ $? -eq 0 ]; then
echo -e " \033[32m 三局复制业务库数据库 $i 自动备份成功\033[0m">>$LogFile
#微信报警
curl 'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=5c254a09-e588-4813-9c4c-87585b8e8f1f' \
-H 'Content-Type: application/json' \
-d '
{
"msgtype": "markdown",
"markdown": {
"content": "<font color=\"warning\">三局复制业务库数据库 '$i' 自动备份成功</font>"
}
}'
else
echo -e "\033[31m 三局复制业务库数据库 $i 备份失败\033[0m">>$LogFile
#微信报警
curl 'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=5c254a09-e588-4813-9c4c-87585b8e8f1f' \
-H 'Content-Type: application/json' \
-d '
{
"msgtype": "markdown",
"markdown": {
"content": "<font color=\"warning\">三局复制业务库数据库 '$i' 自动备份失败,请快速解决问题!!</font>"
}
}'
fi
if [ -d "$RMDIR" ]; then
find /alidata/backup/mysqlbackup/$IP/data/ -mtime +1 -name "20*" -exec rm -rf {} \;
fi
echo -e "\033[36m ---------------------备份结束 ----------------- \033[0m">>$LogFile
3、清理掉主从不用的库表sql
###处理掉中台不要导进去的库表
[root@zhgd-all-slave-prod tools]# cd /alidata/backup/mysqlbackup/zhgd-ztdb.mysql.rds.aliyuncs.com/data/20221203185701/
[root@zhgd-all-slave-prod 20221203185701]# rm -f mysql*
###处理掉子应用不要导进去的库表
[root@zhgd-all-slave-prod tools]# cd /alidata/backup/mysqlbackup/zhgd-zyy.mysql.rds.aliyuncs.com/data/20221203190301/
[root@zhgd-all-slave-prod 20221203190301]# rm -f mysql* nacos* xxl-job* agilebpm.db_uploader*
4、将两个主库数据导入从库
###使用myloader将中台数据导入数据库
[root@zhgd-all-slave-prod tools]# nohup myloader -h 10.100.10.201 -u root -p Cz#3Vf#je9ECNF3K -t 8 -o -d /alidata/backup/mysqlbackup/zhgd-ztdb.mysql.rds.aliyuncs.com/data/20221203185701/ &
###使用myloader将子应用数据导入数据库
[root@zhgd-all-slave-prod tools]# nohup myloader -h 10.100.10.201 -u root -p Cz#3Vf#je9ECNF3K -t 8 -o -d /alidata/backup/mysqlbackup/zhgd-zyy.mysql.rds.aliyuncs.com/data/20221203190301/ &
###这一步要花比较长的时间
[root@zhgd-all-slave-prod tools]# ps -ef | grep myloader
root 1154 31023 4 19:35 pts/0 00:05:19 myloader -h 10.100.10.201 -u root -p Cz#3Vf#je9ECNF3K -t 8 -o -d /alidata/backup/mysqlbackup/zhgd-ztdb.mysql.rds.aliyuncs.com/data/20221203185701/
root 2036 31023 2 21:00 pts/0 00:00:32 myloader -h 10.100.10.201 -u root -p Cz#3Vf#je9ECNF3K -t 8 -o -d /alidata/backup/mysqlbackup/zhgd-zyy.mysql.rds.aliyuncs.com/data/20221203190301/
root 2315 31023 0 21:24 pts/0 00:00:00 grep --color=auto myloader
5、修改mysql配置文件
###数据库启动脚本如下
[root@zhgd-all-slave-prod scripts]# cat mysql5.7_slave.sh
docker run -it -d \
--name mysql5.7_slave \
--restart always -p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=XXXXXXXXXX \
-v /alidata/mysql/conf/my.cnf:/etc/mysql/my.cnf \
-v /alidata/mysql/data:/var/lib/mysql \
-v /alidata/mysql/localtime:/etc/localtime \
-v /alidata/mysql/log:/var/log/mysql \
kurashitech/mysql5.7.22
###需要在my.cnf中增加以下配置,汇聚库不产生binlog
####################binlog######################
#log-bin=OFF
#binlog-format=ROW
#expire_logs_days=3
log-slave-updates=1
#####忽略的库表添加上
####################slave######################
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = nacos
replicate-ignore-db = xxl-job
replicate-ignore-db = sys
replicate-ignore-table = agilebpm.db_uploader
###
gtid_mode=on
enforce_gtid_consistency=on
master-info-repository = TABLE
relay-log-info-repository = TABLE
6、mysql中做两主一从的配置
###查看中台RDS的binlog位置点,以master status信息为准
[root@zhgd-all-slave-prod scripts]# cd /alidata/backup/mysqlbackup/zhgd-ztdb.mysql.rds.aliyuncs.com/data/20221203185701/
[root@zhgd-all-slave-prod 20221203185701]# cat metadata
Started dump at: 2022-12-03 18:57:01
SHOW MASTER STATUS:
Log: mysql-bin.000637
Pos: 203751
GTID:e699cc13-694b-11ed-a6cf-0c42a1e7eb4a:1-1005,
e6d30918-694b-11ed-a6cf-0c42a18535a8:1-22308989
SHOW SLAVE STATUS:
Host: 10.112.250.106
Log: mysql-bin.000638
Pos: 182491
GTID:e699cc13-694b-11ed-a6cf-0c42a1e7eb4a:1-1005,
e6d30918-694b-11ed-a6cf-0c42a18535a8:1-22308989
Finished dump at: 2022-12-03 19:15:35
###查看子应用RDS的binlog位置点,以master status信息为准
[root@zhgd-all-slave-prod ~]# cd /alidata/backup/mysqlbackup/zhgd-zyy.mysql.rds.aliyuncs.com/data/20221203190301/
[root@zhgd-all-slave-prod 20221203190301]# cat metadata
Started dump at: 2022-12-03 19:03:01
SHOW MASTER STATUS:
Log: mysql-bin.000255
Pos: 221996
GTID:0bdb4617-6a1f-11ed-ac30-506b4bdd9df8:1-19820651
SHOW SLAVE STATUS:
Host: 11.200.216.72
Log: mysql-bin.000257
Pos: 215471
GTID:0bdb4617-6a1f-11ed-ac30-506b4bdd9df8:1-19820651
Finished dump at: 2022-12-03 20:55:23
###在MySQL中执行以下两个跟上主从复制的命令
change master to master_host='zhgd-ztdb.mysql.rds.aliyuncs.com', master_user='ztsa', master_port=3306, master_password='XXXXXXXXXX', master_log_file='mysql-bin.000637', master_log_pos=203751 for channel 'master1';
change master to master_host='zhgd-zyy.mysql.rds.aliyuncs.com', master_user='zzsa', master_port=3306, master_password='XXXXXXXXXX', master_log_file='mysql-bin.000255', master_log_pos=221996 for channel 'master2';
start slave;
###至此,两主一从汇聚从库搭建完成