某市驾驶培训监管服务平台 GreatSQL 数据库适配之旅

一、项目背景

某市驾培系统主要为社会公众提供驾培单位查询和学车报名，为相关合作单位提供某市驾培监管、某市驾培考核等功能。业务信息教练车培训过程视频信息、包括培训机构基本信息、教练员基本信息和学员个人等信息，其服务范围为社会公众。信息系统定级为第三级。

某市驾培系统部署在某市政务云平台互联网区域和政务外网区域，采用 B/S 结构，使用 JAVA 语言开发。使用 JAVA 语言开发。租用了十多台台虚拟化服务器，其中应用类虚拟化服务器十多台，数据库虚拟化服务器两台，十多台虚拟化服务器的操作系统均为 CentOS v7.4，应用中间使用 Tomcat 9.0 数据库使用 MySQL v5.7.29。

某市驾驶培训监管服务平台系统拓扑

二、渗透测试、系统漏洞、专家建议（等保测评后）

根据渗透测试及漏洞扫描结果，某市驾培系统平台某市云机房所使用的十多台服务器中共发现紧急漏洞三个，高风险漏洞十三个,发现安全问题四十多个，中风险问题二十多个等，其服务器漏洞已与开发单位沟通确认并制定整改计划，将持续进行整改。

三、项目目标

将某市驾培系统目前的版本 MySQL v5.7.29 数据库升级到 GreatSQL v8.0.32（数据库名为TYU&……*IO）, 某市驾培系统目前的数据量1T，备份完的数据量 20G（进行压缩且无备份TY_tra库及日志表），备份时间 202408192200

3.1 数据库基本信息

3.2 某市驾培系统源库相关情况

a) 源库库表
库名
TY_ApolloConfigDB
TY_ApolloPortalDB    TY_Longan
TY_Mysql
TY_yg_ass
TY_yg_ins
TY_yg_site
TY_yg_ssj
TY_yg_sys
TY_yg_tra
TY_yg_tra_test
b)源库库索引：见表格
c)源库库同义词：无
d)源库库序列：无
e)源库库触发器：无
f)源库库存储过程：见表格
g)源库库视图：无
h)源库库function：无
i)源库库package：无
j)源库库package body：无
k)其他

3.3 数据量情况

数据量为订单900左右/天，培训数8w左右/天，车载数电子日志8k左右/天，分钟学时数200w左右/天，培训照片20w左右/天；

3.4 迁移停机时间

24小时内完成迁移停机。

四、项目目标上线数据迁移的步骤

五、项目服务运行拓扑

服务软件安装包

-rw-r--r-- 1 root root 121M Feb 4 03:02 greatsql-8.0.32-25.1.el7.x86_64.rpm-bundle.tar.xz  
-rw-r--r-- 1 root root 19M Feb 2 15:51 greatsql-client-8.0.32-25.1.el7.x86_64.rpm
-rw-r--r-- 1 root root 1.9M Feb 2 15:51 greatsql-devel-8.0.32-25.1.el7.x86_64.rpm
-rw-r--r-- 1 root root 2.1M Feb 2 15:51 greatsql-icu-data-files-8.0.32-25.1.el7.x86_64.rpm
-rw-r--r-- 1 root root 5.0M Feb 2 15:51 greatsql-mysql-router-8.0.32-25.1.el7.x86_64.rpm
-rw-r--r-- 1 root root 93M Feb 2 15:51 greatsql-server-8.0.32-25.1.el7.x86_64.rpm
-rw-r--r-- 1 root root 1.5M Feb 2 15:51 greatsql-shared-8.0.32-25.1.el7.x86_64.rpm
-rw-r--r-- 1 root root 1.5M Feb 2 15:51 greatsql-shell-8.0.32-16-Linux-glibc2.17-x86_64.tar.xz

某市驾培系统数据库运行拓扑

六、某市驾培系统数据库环境搭建

7.1、安装目标端操作系统并且优化

A、解决ssl的问题

安装 SSL 的相关包

openssh-9.8p1-1.el7.x86_64.rpm
openssh-clients-9.8p1-1.el7.x86_64.rpm
openssh-debuginfo-9.8p1-1.el7.x86_64.rpm
openssh-server-9.8p1-1.el7.x86_64.rpm
openssl-1.1.1h.tar，zlib-1.2.11

B、优化系统参数配置文件sysctl.conf、limits.conf、selinux

sysctl.conf：

fs.aio-max-nr = 3145728
fs.file-max = 6815744
kernel.shmmni = 4096
kernel.shmmax = 68719476736
#kernel.shmall = 8388608
kernel.shmall = 16777216
kernel.sem = 250 32000 100 128
kernel.panic_on_oops = 1
kernel.numa_balancing= 0
kernel.pid_max = 131072
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144   
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
vm.min_free_kbytes= 4048576
vm.hugetlb_shm_group =1000
vm.nr_hugepages =21845
net.ipv4.ipfrag_low_thresh=15728640
net.ipv4.ipfrag_high_thresh=16777216
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.msgmni = 2878
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 0
net.ipv4.conf.default.accept_source_route =0
net.ipv4.tcp_syncookies = 1
kernel.core_uses_pid = 1  

limits.conf：

mysql soft  nproc  65536
mysql hard  nproc  65536
mysql soft  nofile 65536
mysql hard  nofile 65536  

selinux：

SELINUX=disabled

C、系统加固

加强账号及口令安全策略

登陆失败锁定

7.2、搭建目标端 GreatSQL 环境，安装最新补丁

A、调整数据库参数

1)配置hugepage

# /etc/sysctl.conf中添加如下一行
vm.nr_hugepages=46082

# 对/etc/security/limits.conf文件进行修改
mysql soft memlock unlimited
mysql hard memlock unlimited

B、数据库实例配置文件 my.cnf 如下例所示

[client]
no-beep
socket =/usr/local/mysql/data/mysql.sock
# pipe
# socket=0.0
port=3306
[mysql]
default-character-set=utf8
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
pid-file=/usr/local/mysql/data/mysqld.pid
#skip-grant-tables
#skip-name-resolve
socket = /usr/local/mysql/data/mysql.sock
character-set-server=utf8
default-storage-engine=INNODB
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/log/slow_query.log
general_log = 0
general_log_file = /usr/local/mysql/log/general_query.log
expire-logs-days = 14

validate_password_dictionary_file='/usr/local/mysql/lib/plugin'
validate_password_check_user_name=on
plugin-load-add=validate_password.so
validate-password=FORCE_PLUS_PERMANENT
#default_password_lifetime=0

skip-external-locking = 1
skip-name-resolve = 1
explicit_defaults_for_timestamp = true
# Server Id.
server-id=22611
log_timestamps=system
log_bin =/usr/local/mysql/log/mysql-bin
log_bin_index=/usr/local/mysql/log/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/usr/local/mysql/log/mysql-relay-bin
relay_log_index=/usr/local/mysql/log/mysql-relay-bin.index
log-error = /usr/local/mysql/data/mysqld.log

#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%
#### semi sync replication settings #####
plugin_dir=/usr/local/mysql/lib/pluginplugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000
#binlog-do-db=cfdi,cloudcp
#binlog-ignore-db=mysql

max_connections=2000
query_cache_size=0
max_heap_table_size = 64
Mnet_buffer_length = 8K
table_open_cache=2000
tmp_table_size=246M
thread_cache_size=300 #限定用于每个数据库线程的栈大小。默认设置足以满足大多数应用
thread_stack = 192k
key_buffer_size=512M
read_buffer_size=4M
read_rnd_buffer_size=32M
binlog_cache_size = 16m
max_binlog_cache_size = 128m
max_binlog_size = 128m

innodb_data_home_dir = /usr/local/mysql/data/
#innodb_data_file_path = libdata1:1024M:autoextend
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=16M
innodb_buffer_pool_size=256M
innodb_log_file_size=128M
innodb_log_files_in_group =6
#innodb_log_group_home_dir = /usr/local/mysql/log/redo-log
innodb_thread_concurrency=128
innodb_autoextend_increment=1000
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=128M
max_allowed_packet=1024M
max_connect_errors=2000
open_files_limit=4161
query_cache_type=0
sort_buffer_size=32M
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
sync_binlog = 1
#innodb_flush_logs_at_trx_commit=2
innodb_support_xa=ture
#innodb_safe_binlog=1
#批量插入数据缓存大小，可以有效提高插入效率，默认为8
Mbulk_insert_buffer_size = 64M
interactive_timeout = 120
wait_timeout = 120
log-bin-trust-function-creators=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

C、数据库修改 server-id参数 每台服务器的参数ID不能相同

server-id=22611
server-id=22617
server-id=22618

重启数据库数据库实例，使如上配置生效

D、安装密码插件，密码重用限制，密码验证，双密码验证

greatsql> SELECT * FROM mysql.plugin;
#在线添加
greatsql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
greatsql> SET GLOBAL validate_password_dictionary_file = "/usr/local/mysql/lib/plugin/";
greatsql> SET GLOBAL  validate_password_check_user_name= ON;
greatsql> SHOW GLOBAL VARIABLES LIKE '%validate_password%';
greatsql> SHOW STATUS LIKE 'validate_password%';
greatsql> SET GLOBAL validate_password_policy=0;
greatsql> SELECT plugin_name,plugin_status,plugin_type,load_option,plugin_library FROM information_schema.plugins;

7.3、搭建目标端greatsql-mysql-router-8.0.32-25环境，安装最新补丁

A 、Profile文件

在 PATH= 添加 mysqlrouter 所在路径

/usr/local/greatsql-mysql-router-8.0.32/bin/，如：

PATH=$PATH:$JAVA_BIN:$ES_HOME:

/usr/local/greatsql-mysql-router-8.0.32/bin/

验证是否安装成功：

$ mysqlrouter --help

B、服务配置

$ mkdir /usr/local/greatsql-mysql-router-8.0.32/log
$ mkdir /usr/local/greatsql-mysql-router-8.0.32/data
$ chown mysql:mysql /etc/mysqlrouter.conf
$ chown -R mysql:mysql /usr/local/greatsql-mysql-router-8.0.32 

C、创建配置文件

#可参考
/usr/local/ greatsql-mysql-router-8.0.32/share/doc/mysqlrouter/sample_mysqlrouter.conf

$ cat mysqlrouter.conf

[DEFAULT]
logging_folder = /usr/local greatsql-mysql-router-8.0.32/log
plugin_folder = /usr/local/greatsql-mysql-router-8.0.32/lib/mysqlrouter
data_folder=/usr/local/greatsql-mysql-router-8.0.32/data

[logger]
level = INFO

[routing:primary]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 192.166.*.*:3306, 192.166.*.*:3306
routing_strategy = first-available

[routing:secondary]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 192.166.*.*:3306
routing_strategy = round-robin

七、迁移测试

7.1 生产库数据导出

1)查看数据库表

$ mysql -uroot -p
greatsql> USE TYU&……*IO
greatsql> SELECT * FROM test1;    

2)生产库数据导出

$ /usr/local/mysql/bin/mysqldump -uroot -h192.166.*.*-P3306 -plezhiyun@lzx -d TYU&……*IO> TYU&……*IO20240819.sql

-- 导出数据库为dbname某张表(test)结构

$ /usr/local/mysql/bin/mysqldump -uroot -plezhiyun@lzx -d TYU&……*IO > TYU&……*IO.sql   

7.2 数据导入

A、创建用户

$ mysql -uroot -h192.166.*.* -P3306 -plezhiyun@lzx -A
greatsql> DROP DATABASE TYU&……*IO;
greatsql> CREATE DATABASE IF NOT EXISTS TYU&……*IO DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

B、导入数据

数据库导入：

greatsql> connect TYU&……*IO
greatsql> SOURCE /home/TYU&……*IO.sql

八、项目目标上线数据迁移的项目计划

九、回退方案

割接窗口内迁移失败，则方案进入回退。操作步骤如下；

1. 迁移停止；

2. 修改原生产库的IP地址；

3. 将原生产库的监听打开；

4. 应用程序重新连接至原库；

十、满足渗透测试、系统漏洞、专家建议

Enjoy GreatSQL