自建MySQL数据库搭建及配置_创建一个新的数据库,口令自定义

某智慧项目所有环境中，除购买了三个阿里云RDS外，还在ECS上以以下方式自建了4个数据库

一、自建数据库清单

二、测试数据库搭建

###测试环境使用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;
###至此，两主一从汇聚从库搭建完成