企业级规范架构下的数据库搭建【MySQL篇】

数据库技术离不开数据库环境搭建。在讨论搭建环境之前，我先推荐一篇文章给大家：首席架构师白鳝：运维的进阶与哲学之道。这篇文章对我影响很大，在我还只是个纯粹单一技术思想的IT男时，它让我的思维格局都得到极大的提升。

这篇文章和数据库搭建的关系

可能有些小伙伴心里会有疑问：不就是个MySQL数据库搭建吗？如此简单的内容，而且网上文档满天飞。说好的尽量不讲网上已经存在的重复知识点呢？而且你讲搭建就专心讲，扯上面那些干嘛？就想说明你多崇拜白总吗？（没错，我确实极度崇拜他。）

我先简单解释下：本篇确实主讲MySQL搭建，但是我会在搭建时融入上述文章中提到的思想，这将使搭建更精益化、规范化，且更容易实现自动化甚至智能化。后续也会引申出不少基于这个搭建模式的知识及思想。我不想只分享纯粹的技术知识，我觉得规范化，架构化等思想更为重要。也希望小伙伴们通过我的分享，能将普通的技术知识应用到企业级的架构中去。

下面将分享一下我的搭建方法，可能大家会觉得步骤和常规的比起来略显繁琐，但为了更标准化、规范化，部分操作在手动操作的情况下是无法避免的。不过由于实现了标准化，这会让后续的自动化实现起来更加容易，所以也无需过于纠结操作的繁琐。

MySQL二进制规范搭建步骤

注意：命令行头标识如下： # 表示root用户执行 $ 表示mysql用户执行 mysql> 表示myql命令行执行

1、规范信息：

内容规范版本选择Linux 75.6版本：5.6.445.7版本：5.7.26主机配置4~32核8~512G卷规划软件卷：/mysql数据卷：/mydata/${PORT}LVM管理，且软件卷和数据卷需分开，不同mysql端口实例的data卷也需分开数据库目录/mydata/$PORT通过对应端口号进行区分，且在此存放如下信息：my${PORT}.cnf文件data目录：存放数据文件log 目录：存放slowlog、binlog等日志文件var 目录：存放socket等文件tmp 目录：存放临时文件软件目录5.6版本：/mysql/rdbms/5.6.445.7版本：/mysql/rdbms/5.7.26用于存放相应版本的软件工具目录工具脚本目录：/mysql/tools/script用于存放各类工具以及脚本，比如：crontab脚本、pt、orzdba、dbs、xtrabackup等工具日志目录：/mysql/tools/log用于存放工具生成的日志域名规划生产：<dbname>.pmysql.*.com.cn灾备：<dbname>.smysql.*.com.cn测试：<dbname>.mystg.*.com.cn端口号规划生产/备库/灾备：3308-3398测试：3408-3498

注：下面搭建以3308端口为例

2、划盘

2.1 查看硬盘和分区信息

# fdisk -l

2.2 找到新盘，创建pv

# pvcreate /dev/vdb

2.3 创建vg

# vgcreate vg01 /dev/vdb

2.4 在刚才新建的vg上创建10G的swap的lv分区，并初始化启动

# lvcreate -L 10240 -n lv_swap vg01 # mkswap /dev/mapper/vg01-lv_swap # swapon /dev/mapper/vg01-lv_swap

2.5 创建50G大小的软件卷lv

# lvcreate -L 50000 -n lv_rdbms vg01

2.6 将vg的剩余空间全部给新建的数据卷lv

# lvcreate -l +100%Free -n lv_data vg01

2.7 格式化创建对应lv的文件系统

# mkfs.xfs /dev/mapper/vg01-lv_data # mkfs.xfs /dev/mapper/vg01-lv_rdbms

2.8 创建挂载点

# mkdir /mysql # mkdir -p /mydata/3308

2.9 将相关卷写入/etc/fstab后mount -a

# echo "/dev/mapper/vg01-lv_rdbms /mysql xfs defaults 0 0" >> /etc/fstab # echo "/dev/mapper/vg01-lv_data /mydata/3308 xfs defaults 0 0" >> /etc/fstab # mount -a

3、创建用户和组

创建MySQL用户和组，规范gid和uid便于避免迁移后出现特殊异常

# groupadd -g 1101 mysql # useradd -g mysql -u 1102 mysql # chown -R mysql:mysql /mysql # chown -R mysql:mysql /mydata

4、调整系统参数

4.1 调整sysctl.conf

# vi /etc/sysctl.conf vm.swappiness = 0 net.ipv4.neigh.default.gc_stale_time = 120 net.ipv4.conf.all.rp_filter = 0 net.ipv4.conf.default.rp_filter = 0 net.ipv4.conf.default.arp_announce = 2 net.ipv4.conf.lo.arp_announce = 2 net.ipv4.conf.all.arp_announce = 2 net.ipv4.tcp_max_tw_buckets = 5000 net.ipv4.tcp_syncookies = 1 net.ipv4.tcp_max_syn_backlog = 1024 net.ipv4.tcp_synack_retries = 2 net.ipv6.conf.all.disable_ipv6 = 1 net.ipv6.conf.default.disable_ipv6 = 1 net.ipv6.conf.lo.disable_ipv6 = 1 kernel.sysrq = 1 net.ipv4.tcp_tw_reuse = 1 net.ipv4.tcp_tw_recycle = 1 net.ipv4.tcp_fin_timeout = 10 net.ipv4.ip_local_port_range=40000 65535 net.core.wmem_default = 87380 net.core.wmem_max = 16777216 net.core.rmem_default = 87380 net.core.rmem_max = 16777216 kernel.shmmax = 4294967295 fs.nr_open=20480000 fs.aio-max-nr = 1048576

4.2 调整limits.conf 确认/etc/security/limits.conf值大于如下值

# cat /etc/security/limits.conf * soft nofile 65535 * hard nofile 65535

4.3 参数生效

# sysctl -p

5、yum包安装

# yum -y install gcc* libaio numactl autoconf

如果需要安装orzdba工具，则需多yum如下包

# yum -y install \*perl5\* perl-Test-Simple.x86_64 perl-Time-HiRes perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-DBD-MySQL perl-DBI perl-Module-Build

6、创建目录

注意：此文档的端口以3308为例，实际端口号请以规范和需求为准

# su – mysql $ mkdir -p /mysql/tools/script $ mkdir -p /mysql/tools/log $ mkdir -p /mysql/rdbms/ $ mkdir -p /mydata/3308/ $ cd /mydata/3308 $ mkdir tmp var log data

7、数据库实例信息配置

注意：使用实际端口与版本号。格式为： 端口号:实例名:版本号

$ vi /mysql/tools/script/db_info.txt 3308:mytest:/mysql/rdbms/5.7.26

目的：可在此文件下添加多行同格式信息，用于多套mysql共存同一主机的架构。 可以通过db_info.txt以及下一步的.db_profile文件进行同主机下多套数据库的环境变量切换

8、环境变量文件

$ cd $ vi .db_profile #.db_profile #!/bin/sh awk -F: '{print $1":"$2":"$3}' /mysql/tools/script/db_info.txt if [ ! "$1" = "" ];then PORT=$1 else echo -n "Please Enter database port:"; read PORT; fi if [ "$PORT" = "" ]; then echo "you havn't Enter your mysql port!"; unset MYSQL_SERVER_NAME; unset PORT; else if awk -F: '{print $1}' /mysql/tools/script/db_info.txt|grep -w $PORT then export MYSQL_SERVER_NAME=`awk -F: '{if ($1 == "'$PORT'") {print $2; exit}}' /mysql/tools/script/db_info.txt` export MYSQL_HOME=`awk -F: '{if ($1 == "'$PORT'") {print $3; exit}}' /mysql/tools/script/db_info.txt` stty erase "^H" stty erase "^?" umask 022 MYSQL_DATA=/mydata/${PORT}/data MYSQL_LOG=/mydata/${PORT}/log TEMP=/tmp TERM=linux EDITOR=vi PATH=$MYSQL_HOME/bin:$PATH export PATH MYSQL_HOME MYSQL_DATA MYSQL_LOG TERM EDITOR TEMP alias mysqld_mm="mysqld_unsafe --read_only=0 &" alias mysqld_ms="mysqld_unsafe &" alias mysql-l="${MYSQL_HOME}/bin/mysql --login-path=root --socket=/mydata/${PORT}/var/mysql.sock" alias mysqldump="${MYSQL_HOME}/bin/mysqldump --login-path=root --socket=/mydata/${PORT}/var/mysql.sock" alias mysql_start="cd ${MYSQL_HOME}&&./bin/mysqld_safe --defaults-file=/mydata/${PORT}/my${PORT}.cnf &" alias mysql_shutdown="${MYSQL_HOME}/bin/mysqladmin -uroot -p --socket=/mydata/${PORT}/var/mysql.sock shutdown" alias mysql="${MYSQL_HOME}/bin/mysql --socket=/mydata/${PORT}/var/mysql.sock" #alias mysqladmin="${MYSQL_HOME}/bin/mysqladmin --login-path=root --socket=/mydata/${PORT}/var/mysql.sock" alias xtrabackup="/mysql/tools/xtrabackup/bin/xtrabackup --defaults-file=/mydata/${PORT}/my${PORT}.cnf --login-path=root --socket=/mydata/${PORT}/var/mysql.sock" alias myqps="${MYSQL_HOME}/bin/mysqladmin --login-path=root --socket=/mydata/${PORT}/var/mysql.sock -h localhost extended-status -r -i 1 | grep \"Questions\"" alias mytps="${MYSQL_HOME}/bin/mysqladmin --login-path=root --socket=/mydata/${PORT}/var/mysql.sock -h localhost extended-status -r -i 1 | grep -E \"Com_commit\"" alias mydml="${MYSQL_HOME}/bin/mysqladmin --login-path=root --socket=/mydata/${PORT}/var/mysql.sock -h localhost extended-status -r -i 1 | grep -E \"Com_insert|Com_update|Com_delete\"" alias orzdba="/mysql/tools/script/orzdba -lazy -rt -T -S /mydata/${PORT}/var/mysql.sock 2>/dev/null" alias mystat="sh /mysql/tools/script/mystat.sh --socket=/mydata/${PORT}/var/mysql.sock" #check mysql status MYSQLD_EXIST=`ps -ef|grep mysqld|grep $PORT |wc -l` if [ "$MYSQLD_EXIST" = "2" ];then echo "MySQL server IS running!" MASTER_EXIST=`${MYSQL_HOME}/bin/mysql --login-path=root --socket=/mydata/${PORT}/var/mysql.sock -e "show master status \G"|grep mysql-bin |wc -l` SLAVE_EXIST=`${MYSQL_HOME}/bin/mysql --login-path=root --socket=/mydata/${PORT}/var/mysql.sock -e "show slave status \G"|grep Yes|wc -l` SLAVE_NUM=`${MYSQL_HOME}/bin/mysql --login-path=root --socket=/mydata/${PORT}/var/mysql.sock -e "show slave status \G"|wc -l` READ_ONLY=`${MYSQL_HOME}/bin/mysql --login-path=root --socket=/mydata/${PORT}/var/mysql.sock -e "show global variables like '%read_only%'"|grep ON|wc -l` tmpv=`${MYSQL_HOME}/bin/mysql --login-path=root --socket=/mydata/${PORT}/var/mysql.sock -Ns -e "show global variables like 'read_only%'" | awk '{print $2}'` dbstat=`${MYSQL_HOME}/bin/mysql --login-path=root --socket=/mydata/${PORT}/var/mysql.sock -e "show databases"| grep -vE 'information_schema|mysql|test|performance_schema|Database'|paste -sd, |sed 's/,/, /g'` if [ "$MASTER_EXIST" = "1" ] && [ "$SLAVE_EXIST" = "2" ] && [ "$READ_ONLY" = "1" ];then MYSQL_STATUS="MM-ReadOnly" elif [ "$MASTER_EXIST" = "1" ] && [ "$SLAVE_EXIST" = "2" ] && [ "$READ_ONLY" = "0" ];then MYSQL_STATUS="MM-Writable" elif [ "$MASTER_EXIST" = "1" ] && [ "$SLAVE_EXIST" = "0" ] && [ "$READ_ONLY" = "0" ];then MYSQL_STATUS="MS-Master" elif [ "$MASTER_EXIST" = "0" ] && [ "$SLAVE_EXIST" = "2" ] && [ "$READ_ONLY" = "1" ];then MYSQL_STATUS="MS-Slave" elif [ "$MASTER_EXIST" = "0" ] && [ "$SLAVE_EXIST" = "0" ] && [ "$READ_ONLY" = "0" ];then MYSQL_STATUS="M-Single" elif [ "$MASTER_EXIST" = "0" ] && [ "$SLAVE_EXIST" = "0" ] && [ "$READ_ONLY" = "1" ] && [ "$SLAVE_NUM" = "0" ];then MYSQL_STATUS="INIT" else echo "Strang status! Please check immediately!" MYSQL_STATUS="CHECK" fi elif [ "$MYSQLD_EXIST" = "0" ];then echo "MySQL server IS NOT running!" MYSQL_STATUS="No-Mysql" else echo "Multi-server?! Please check immediately!" MYSQL_STATUS="CHECK" fi #check mysql status finished if [ -f ~/.color ]; then . ~/.color fi if [ "$tmpv" = "ON" ]; then dbrole='S' elif [ "$tmpv" = "OFF" ]; then dbrole='M' else dbrole='norole' ce=$C423 fi PS1="[$C241$MYSQL_STATUS$C0-$C281$MYSQL_SERVER_NAME$C0:$C171$PORT$C0$C261@$C171\H $C0\w $C211\t$C0]\n\$ "; export PS1 else echo "The mysql port not exist in /mysql/tools/script/db_info.txt." echo "Please check your port in db_info.txt." unset MYSQL_SERVER_NAME; unset PORT unset MYSQL_DATA unset MYSQL_LOG PS1="\H no-mysql \t> " fi fi

9、加载对应端口的环境变量

可以通过输入的端口号切换到对应的mysql实例环境

$ . .db_profile 3308 3308:mytest:/mysql/rdbms/5.7.26 3308 MySQL server IS NOT running!

10、软件包下载

https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

11、软件安装

$ tar xzvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /mysql/rdbms $ cd /mysql/rdbms $ mv mysql-5.7.26-linux-glibc2.12-x86_64 5.7.26 $ chown mysql:mysql -R 5.7.26

12、构造5.7标准my.cnf文件

$ vi /mydata/3308/my3308.cnf [client] socket = /mydata/3308/var/mysql.sock port = 3308 [mysqld] ############# GENERAL ############# autocommit = ON character_set_server = UTF8MB4 collation_server = UTF8MB4_BIN explicit_defaults_for_timestamp = ON lower_case_table_names = 1 port = 3308 read_only = OFF transaction_isolation = READ-COMMITTED ####### CACHES AND LIMITS ######### interactive_timeout = 600 lock_wait_timeout = 50 max_allowed_packet = 32M max_connect_errors = 10000 max_connections = 3000 max_user_connections = 2000 sort_buffer_size = 2M table_definition_cache = 600 table_open_cache = 400 table_open_cache_instances = 1 thread_cache_size = 9 thread_stack = 256K tmp_table_size = 32M ############# SAFETY ############## local_infile = OFF ###have slave add semisync_master.so;semisync_slave.so plugin-load = "validate_password.so;semisync_master.so;semisync_slave.so" ##plugin-load = "validate_password.so" skip_name_resolve = ON sql_mode = STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY ############# LOGGING ############# general_log = 0 log_queries_not_using_indexes = ON log_slow_admin_statements = ON log_warnings = 2 long_query_time = 1 slow_query_log = ON ############# REPLICATION ############# binlog_checksum = CRC32 binlog_format = ROW binlog_rows_query_log_events = ON enforce_gtid_consistency = ON expire_logs_days = 7 gtid_mode = ON log_slave_updates = ON master_info_repository = TABLE master_verify_checksum = ON max_binlog_size = 256M relay_log_info_repository = TABLE server_id = 4061333081 skip_slave_start = ON slave_net_timeout = 4 slave_sql_verify_checksum = ON sync_binlog = 1 sync_master_info = 10000 sync_relay_log = 10000 sync_relay_log_info = 10000 binlog_group_commit_sync_delay=1 binlog_group_commit_sync_no_delay_count = 10 binlog_order_commits=off ####have slave use semi sync #rpl_semi_sync_master_enabled = 1 #rpl_semi_sync_slave_enabled = 1 #rpl_semi_sync_master_timeout = 1000 ############### PATH ############## basedir = /mysql/rdbms/5.7.26 datadir = /mydata/3308/data tmpdir = /mydata/3308/tmp socket = /mydata/3308/var/mysql.sock pid_file = /mydata/3308/var/mysql.pid innodb_data_home_dir = /mydata/3308/data log_error = /mydata/3308/log/error.log general_log_file = /mydata/3308/log/general.log slow_query_log_file = /mydata/3308/log/slow.log log_bin = /mydata/3308/log/mysql-bin log_bin_index = /mydata/3308/log/mysql-bin.index relay_log = /mydata/3308/log/relay-log relay_log_index = /mydata/3308/log/relay-log.index ############# INNODB ############# innodb_file_format = Barracuda innodb_flush_method = O_DIRECT innodb_buffer_pool_size = 1024M innodb_log_file_size = 256M innodb_log_files_in_group = 4 innodb_flush_log_at_trx_commit = 1 innodb_support_xa = ON innodb_strict_mode = ON innodb_data_file_path = ibdata1:256M;ibdata2:16M:autoextend innodb_checksum_algorithm = strict_crc32 innodb_io_capacity = 180 innodb_lock_wait_timeout = 5 key_buffer_size = 64M read_buffer_size = 536870912 [mysql] ############# CLIENT ############# max_allowed_packet = 32M socket = /mydata/3308/var/mysql.sock default-character-set = utf8MB4 character_set_server = UTF8MB4 collation_server = utf8mb4_bin [mysqldump] max_allowed_packet = 32M

13、创建数据库

$ cd /mysql/rdbms/5.7.26 $ ./bin/mysqld --defaults-file=/mydata/3308/my3308.cnf --user=mysql --initialize

14、启动数据库

$ mysqld_safe --defaults-file=/mydata/3308/my3308.cnf &

或者使用封装命令：

$ which mysql_start $ mysql_start

15、登录数据库

找到临时root密码并登录

$ grep "password" /mydata/3308/log/error.log $ mysql -uroot -p

16、重置root密码

mysql> alter user 'root'@'localhost' identified by 'Mytest.2020';

17、创建监控用户

mysql> create user 'dbmonitor'@'%' identified by 'dBmonitor#2020'; Query OK, 0 rows affected (0.00 sec) mysql> exit;

18、安全登录配置

–login-path免密登录，本机所有localhost的root密码保持一致，则可共享使用

$ mysql_config_editor set --login-path=root --user=root --password Enter password: --此处输入之前修改的密码 $ ll -a $HOME | grep login -rw------- 1 mysql mysql 100 May 6 14:01 .mylogin.cnf

19、登录测试

使用封装命令进行登录测试

$ mysql-l mysql> show databases;

20、权限控制

5.7.26无需操作 主要目的是为了清除test数据库的权限，防止被恶意利用（比如恶意消耗cpu、内存、磁盘空间导致数据库异常）

mysql> drop database test; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

21、配置常用命令

$ vi /mysql/tools/script/mystat.sh mysqladmin --login-path=root $1 -h localhost extended-status -i1| awk \ 'BEGIN{flag=0; print ""; print "---------------------|--------------|--- MySQL Command Status --|-- Buffer Pool Read --|--- Threads ---|"; print "--------Time---------| QPS TPS|select insert update delete| logical physical| conn run|"; print "-------------------------------------------------------------------------------------------------------|"} $2 ~ /Queries$/ {q=$4-lq;lq=$4;} $2 ~ /Com_commit$/ {c=$4-lc;lc=$4;} $2 ~ /Com_rollback$/ {r=$4-lr;lr=$4;} $2 ~ /Com_select$/ {s=$4-ls;ls=$4;} $2 ~ /Com_insert$/ {i=$4-li;li=$4;} $2 ~ /Com_update$/ {u=$4-lu;lu=$4;} $2 ~ /Com_delete$/ {d=$4-ld;ld=$4;} $2 ~ /Innodb_buffer_pool_read_requests$/ {il=$4-lil;lil=$4;} $2 ~ /Innodb_buffer_pool_reads$/ {ip=$4-lip;lip=$4;} $2 ~ /Uptime$/ && flag >= 0 {;} $2 ~ /Threads_connected$/ {tc=$4;} $2 ~ /Threads_running$/ {tr=$4; if(flag==0){ flag=1; count=0 }else { printf(" %s ",strftime("%Y-%m-%d %H:%M:%S")); printf("|%6d %6d ", q,c+r); printf("|%6d %6d %6d %6d",s,i,u,d); printf("|%10d %11d",il,ip); printf("|%8d %6d|\n",tc,tr); } }'

22、自动备份清理脚本

$ cd /mysql/tools/script $ vi back_mylog.sh #!/bin/sh cd for my_port in `cat /mysql/tools/script/db_info.txt |awk -F ':' '{print $1}'` do . ./profile $my_port find /mydata/${my_port}/log -name "slow*.gz" -mtime +15 -exec rm {} \; find /mydata/${my_port}/log -name "error*.gz" -mtime +15 -exec rm {} \; cp /mydata/${my_port}/log/slow.log /mydata/${my_port}/log/slow`date +"%Y%m%d"`.log if [ $? -eq 0 ];then >/mydata/${my_port}/log/slow.log gzip /mydata/${my_port}/log/slow`date +"%Y%m%d"`.log echo "The slow log has been cleaned up" else echo "Error,slow log clean failed,check please" fi cp /mydata/${my_port}/log/error.log /mydata/${my_port}/log/error`date +"%Y%m%d"`.log if [ $? -eq 0 ];then >/mydata/${my_port}/log/error.log gzip /mydata/${my_port}/log/error`date +"%Y%m%d"`.log echo "The error log has been cleaned up" else echo "Error,error log clean failed,check please" fi done echo "======================================================================================="

23、配置crontab

$ cd $HOME $ vi .cron_file 30 01 * * * /bin/sh /mysql/tools/script/back_mylog.sh >> /mysql/tools/log/back_mylog.log 2>&1 $ crontab .cron_file $ crontab -l

常用命令

切换到对应端口的环境变量

[root@localhost ~]# su - mysql [mysql@localhost ~]$ . .db_profile 3308:mytest:/mysql/rdbms/5.7.26 Please Enter database port:3308 3308 MySQL server IS running! [MS-Master-mytest:3308@localhost.localdomain ~ 22:24:49]

登录数据库

$ which mysql-l alias mysql-l='/mysql/rdbms/5.7.26/bin/mysql --login-path=root --socket=/mydata/3308/var/mysql.sock' /mysql/rdbms/5.7.26/bin/mysql $ mysql-l

打印MySQL的一些常规性能指标

$ which mystat alias mystat='sh /mysql/tools/script/mystat.sh --socket=/mydata/3308/var/mysql.sock' /bin/sh [MS-Master-mytest:3308@localhost.localdomain ~ 22:28:35] $ mystat ---------------------|--------------|--- MySQL Command Status --|-- Buffer Pool Read --|--- Threads ---| --------Time---------| QPS TPS|select insert update delete| logical physical| conn run| -------------------------------------------------------------------------------------------------------| 2020-07-04 22:28:38 | 1 0 | 0 0 0 0| 0 0| 1 1| 2020-07-04 22:28:39 | 1 0 | 0 0 0 0| 0 0| 1 1| 2020-07-04 22:28:40 | 1 0 | 0 0 0 0| 0 0| 1 1| 2020-07-04 22:28:40 | 1 0 | 0 0 0 0| 0 0| 1 1|

停库

[MS-Master-mytest:3308@localhost.localdomain ~ 22:31:59] $ ps -ef | grep -w mysqld | grep -v grep mysql 29594 28565 1 22:31 pts/1 00:00:00 /mysql/rdbms/5.7.26/bin/mysqld --defaults-file=/mydata/3308/my3308.cnf --basedir=/mysql/rdbms/5.7.26 --datadir=/mydata/3308/data --plugin-dir=/mysql/rdbms/5.7.26/lib/plugin --log-error=/mydata/3308/log/error.log --pid-file=/mydata/3308/var/mysql.pid --socket=/mydata/3308/var/mysql.sock --port=3308 [MS-Master-mytest:3308@localhost.localdomain ~ 22:32:00] $ which mysql_shutdown alias mysql_shutdown='/mysql/rdbms/5.7.26/bin/mysqladmin -uroot -p --socket=/mydata/3308/var/mysql.sock shutdown' /mysql/rdbms/5.7.26/bin/mysqladmin [MS-Master-mytest:3308@localhost.localdomain ~ 22:32:06] $ mysql_shutdown Enter password: 2020-07-04T14:32:20.006299Z mysqld_safe mysqld from pid file /mydata/3308/var/mysql.pid ended [MS-Master-mytest:3308@localhost.localdomain ~ 22:32:20] $ ps -ef | grep -w mysqld | grep -v grep [MS-Master-mytest:3308@localhost.localdomain ~ 22:32:28]

启库

$ which mysql_start alias mysql_start='cd /mysql/rdbms/5.7.26&&./bin/mysqld_safe --defaults-file=/mydata/3308/my3308.cnf &' [MS-Master-mytest:3308@localhost.localdomain ~ 22:32:33] $ mysql_start [1] 29654 [MS-Master-mytest:3308@localhost.localdomain ~ 22:32:36] $ 2020-07-04T14:32:37.327395Z mysqld_safe Logging to '/mydata/3308/log/error.log'. 2020-07-04T14:32:37.401277Z mysqld_safe Starting mysqld daemon with databases from /mydata/3308/data [MS-Master-mytest:3308@localhost.localdomain ~ 22:32:38] $ ps -ef | grep -w mysqld | grep -v grep mysql 30684 29655 11 22:32 pts/1 00:00:00 /mysql/rdbms/5.7.26/bin/mysqld --defaults-file=/mydata/3308/my3308.cnf --basedir=/mysql/rdbms/5.7.26 --datadir=/mydata/3308/data --plugin-dir=/mysql/rdbms/5.7.26/lib/plugin --log-error=/mydata/3308/log/error.log --pid-file=/mydata/3308/var/mysql.pid --socket=/mydata/3308/var/mysql.sock --port=3308 [MS-Master-mytest:3308@localhost.localdomain ~ 22:32:41] $ [MS-Master-mytest:3308@localhost.localdomain ~ 22:32:42] $ mysql-l Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.26-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec)

总结

以上就是整体的搭建方案，如果还需新建mysql实例，只需在这个主机上再次按手册操作一遍即可，同版本的mysql软件可以共用，只需新增对应端口的mydata卷即可。

每次登录对应的数据库只需通过.db_profile的隐藏文件进行环境变量的切换，轻松且随意。毕竟现在的一台服务器一般不会只让你建一个实例在上面，那样太浪费了（虚拟机和云服务器不在此列）。

可能你会问这么做相当于把鸡蛋放在一个篮子里，一旦主机故障，或者单个库影响了其他库，那不是得不偿失？我之前也有这个疑问，不过都是可以解决的：如用Cgroup做资源限制，避免互相影响；用主从、keepalived、pacemaker、MHA、MGR、数据库中间件等架构避免主机异常影响全局。架构并非由单一的知识点组成，而是需要和其周边的产品功能融合，让整体更加完美。

本篇提及的搭建方法，能保证所有的资源配置、文件名、目录、版本、参数等都保持一定的标准化和规范化，这样能为后面的自动化、平台化甚至智能化提供一个良好基础。如果搭建的环境不标准、不规范，那么自动化就无从谈起。

今天就写到这，后续我会再讲讲这类搭建环境相关的拓展知识。