Mariadb/MySQL多实例实战
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.安装MySQL程序
无论你喜欢哪种方式安装均可，关于源码安装,yum安装或者二进制安装，可以参考官网文档，也可以参考网络上的博客之类的，推荐参考我之前写的笔记。 博主推荐阅读： https://www.cnblogs.com/yinzhengjie/p/11733897.html
二.基于已经安装的MySQL程序启动三个实例(即三个实例公用同一套MySQL程序)
1>.创建多实例各自的数据库存储目录
[root@node105.yinzhengjie.org.cn ~]# yum -y install tree Loaded plugins: fastestmirror Determining fastest mirrors * base: mirrors.huaweicloud.com * extras: mirrors.neusoft.edu.cn * updates: mirrors.huaweicloud.com base | 3.6 kB 00:00:00 extras | 2.9 kB 00:00:00 updates | 2.9 kB 00:00:00 (1/4): base/7/x86_64/group_gz | 165 kB 00:00:06 (2/4): extras/7/x86_64/primary_db | 153 kB 00:00:06 (3/4): base/7/x86_64/primary_db | 6.0 MB 00:00:07 (4/4): updates/7/x86_64/primary_db | 2.8 MB 00:00:20 Resolving Dependencies --> Running transaction check ---> Package tree.x86_64 0:1.6.0-10.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ========================================================================================================================= Package Arch Version Repository Size ========================================================================================================================= Installing: tree x86_64 1.6.0-10.el7 base 46 k Transaction Summary ========================================================================================================================= Install 1 Package Total download size: 46 k Installed size: 87 k Downloading packages: tree-1.6.0-10.el7.x86_64.rpm | 46 kB 00:00:06 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : tree-1.6.0-10.el7.x86_64 1/1 Verifying : tree-1.6.0-10.el7.x86_64 1/1 Installed: tree.x86_64 0:1.6.0-10.el7 Complete! [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mkdir -pv /mysql/{3306,3307,3308} #创建MySQL对应实例存放目录 mkdir: created directory ‘/mysql’ mkdir: created directory ‘/mysql/3306’ mkdir: created directory ‘/mysql/3307’ mkdir: created directory ‘/mysql/3308’ [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# tree /mysql/ #三个目录存放各自的实例 /mysql/ ├── 3306 ├── 3307 └── 3308 3 directories, 0 files [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mkdir -pv /mysql/{3306,3307,3308}/{etc,socket,log,pid,data} #别分创建各个实例对应文件存储路径，目录最好见名知意。 mkdir: created directory ‘/mysql/3306/etc’ mkdir: created directory ‘/mysql/3306/socket’ mkdir: created directory ‘/mysql/3306/log’ mkdir: created directory ‘/mysql/3306/pid’ mkdir: created directory ‘/mysql/3306/data’ mkdir: created directory ‘/mysql/3307/etc’ mkdir: created directory ‘/mysql/3307/socket’ mkdir: created directory ‘/mysql/3307/log’ mkdir: created directory ‘/mysql/3307/pid’ mkdir: created directory ‘/mysql/3307/data’ mkdir: created directory ‘/mysql/3308/etc’ mkdir: created directory ‘/mysql/3308/socket’ mkdir: created directory ‘/mysql/3308/log’ mkdir: created directory ‘/mysql/3308/pid’ mkdir: created directory ‘/mysql/3308/data’ [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# tree /mysql/ #查看咱们创建的目录结构 /mysql/ ├── 3306 #用于开放3306端口的，以下3个目录功能雷同。 │ ├── data #用于存储数据 │ ├── etc #用于存储配置文件 │ ├── log #用于存储日志文件 │ ├── pid #用于存储进程的PID文件 │ └── socket #用于存储本地连接的socket文件 ├── 3307 │ ├── data │ ├── etc │ ├── log │ ├── pid │ └── socket └── 3308 ├── data ├── etc ├── log ├── pid └── socket 18 directories, 0 files [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]#
2>.创建实例对应的数据库文件
[root@node105.yinzhengjie.org.cn /usr/local/mysql]# ll /mysql/3306/data/ total 0 [root@node105.yinzhengjie.org.cn /usr/local/mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/mysql/3306/data #创建3306实例的数据库文件 Installing MariaDB/MySQL system tables in '/mysql/3306/data' ... 2019-10-26 22:17:15 140098928940864 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release. 2019-10-26 22:17:15 140098821629696 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_sla ve_pos' doesn't existOK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER ! To do so, start the server, then issue the following commands: './bin/mysqladmin' -u root password 'new-password' './bin/mysqladmin' -u root -h node105.yinzhengjie.org.cn password 'new-password' Alternatively you can run: './bin/mysql_secure_installation' which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the MariaDB Knowledgebase at http://mariadb.com/kb or the MySQL manual for more instructions. You can start the MariaDB daemon with: cd '.' ; ./bin/mysqld_safe --datadir='/mysql/3306/data' You can test the MariaDB daemon with mysql-test-run.pl cd './mysql-test' ; perl mysql-test-run.pl Please report any problems at http://mariadb.org/jira The latest information about MariaDB is available at http://mariadb.org/. You can find additional information about the MySQL part at: http://dev.mysql.com Consider joining MariaDB's strong and vibrant community: https://mariadb.org/get-involved/ [root@node105.yinzhengjie.org.cn /usr/local/mysql]# [root@node105.yinzhengjie.org.cn /usr/local/mysql]# ll /mysql/3306/data/ total 110660 -rw-rw---- 1 mysql mysql 16384 Oct 26 22:17 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Oct 26 22:17 aria_log_control -rw-rw---- 1 mysql mysql 938 Oct 26 22:17 ib_buffer_pool -rw-rw---- 1 mysql mysql 12582912 Oct 26 22:17 ibdata1 -rw-rw---- 1 mysql mysql 50331648 Oct 26 22:17 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Oct 26 22:17 ib_logfile1 drwx------ 2 mysql root 4096 Oct 26 22:17 mysql -rw-rw---- 1 mysql mysql 29032 Oct 26 22:17 mysql-bin.000001 -rw-rw---- 1 mysql mysql 19 Oct 26 22:17 mysql-bin.index -rw-rw---- 1 mysql mysql 7 Oct 26 22:17 mysql-bin.state drwx------ 2 mysql mysql 20 Oct 26 22:17 performance_schema drwx------ 2 mysql root 6 Oct 26 22:17 test [root@node105.yinzhengjie.org.cn /usr/local/mysql]#
[root@node105.yinzhengjie.org.cn /usr/local/mysql]# ll /mysql/3307/data/ total 0 [root@node105.yinzhengjie.org.cn /usr/local/mysql]# [root@node105.yinzhengjie.org.cn /usr/local/mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/mysql/3307/data Installing MariaDB/MySQL system tables in '/mysql/3307/data' ... 2019-10-26 22:38:56 140237808727872 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release. 2019-10-26 22:38:56 140237667858176 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_sla ve_pos' doesn't existOK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER ! To do so, start the server, then issue the following commands: './bin/mysqladmin' -u root password 'new-password' './bin/mysqladmin' -u root -h node105.yinzhengjie.org.cn password 'new-password' Alternatively you can run: './bin/mysql_secure_installation' which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the MariaDB Knowledgebase at http://mariadb.com/kb or the MySQL manual for more instructions. You can start the MariaDB daemon with: cd '.' ; ./bin/mysqld_safe --datadir='/mysql/3307/data' You can test the MariaDB daemon with mysql-test-run.pl cd './mysql-test' ; perl mysql-test-run.pl Please report any problems at http://mariadb.org/jira The latest information about MariaDB is available at http://mariadb.org/. You can find additional information about the MySQL part at: http://dev.mysql.com Consider joining MariaDB's strong and vibrant community: https://mariadb.org/get-involved/ [root@node105.yinzhengjie.org.cn /usr/local/mysql]# [root@node105.yinzhengjie.org.cn /usr/local/mysql]# ll /mysql/3307/data/ total 110660 -rw-rw---- 1 mysql mysql 16384 Oct 26 22:38 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Oct 26 22:38 aria_log_control -rw-rw---- 1 mysql mysql 938 Oct 26 22:38 ib_buffer_pool -rw-rw---- 1 mysql mysql 12582912 Oct 26 22:38 ibdata1 -rw-rw---- 1 mysql mysql 50331648 Oct 26 22:38 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Oct 26 22:38 ib_logfile1 drwx------ 2 mysql root 4096 Oct 26 22:38 mysql -rw-rw---- 1 mysql mysql 29032 Oct 26 22:38 mysql-bin.000001 -rw-rw---- 1 mysql mysql 19 Oct 26 22:38 mysql-bin.index -rw-rw---- 1 mysql mysql 7 Oct 26 22:38 mysql-bin.state drwx------ 2 mysql mysql 20 Oct 26 22:38 performance_schema drwx------ 2 mysql root 6 Oct 26 22:38 test [root@node105.yinzhengjie.org.cn /usr/local/mysql]#
[root@node105.yinzhengjie.org.cn /usr/local/mysql]# ll /mysql/3308/data/ total 0 [root@node105.yinzhengjie.org.cn /usr/local/mysql]# [root@node105.yinzhengjie.org.cn /usr/local/mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/mysql/3308/data Installing MariaDB/MySQL system tables in '/mysql/3308/data' ... 2019-10-26 22:39:36 140185670281024 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release. 2019-10-26 22:39:36 140185529411328 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_sla ve_pos' doesn't existOK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER ! To do so, start the server, then issue the following commands: './bin/mysqladmin' -u root password 'new-password' './bin/mysqladmin' -u root -h node105.yinzhengjie.org.cn password 'new-password' Alternatively you can run: './bin/mysql_secure_installation' which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the MariaDB Knowledgebase at http://mariadb.com/kb or the MySQL manual for more instructions. You can start the MariaDB daemon with: cd '.' ; ./bin/mysqld_safe --datadir='/mysql/3308/data' You can test the MariaDB daemon with mysql-test-run.pl cd './mysql-test' ; perl mysql-test-run.pl Please report any problems at http://mariadb.org/jira The latest information about MariaDB is available at http://mariadb.org/. You can find additional information about the MySQL part at: http://dev.mysql.com Consider joining MariaDB's strong and vibrant community: https://mariadb.org/get-involved/ [root@node105.yinzhengjie.org.cn /usr/local/mysql]# [root@node105.yinzhengjie.org.cn /usr/local/mysql]# ll /mysql/3308/data/ total 110660 -rw-rw---- 1 mysql mysql 16384 Oct 26 22:39 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Oct 26 22:39 aria_log_control -rw-rw---- 1 mysql mysql 938 Oct 26 22:39 ib_buffer_pool -rw-rw---- 1 mysql mysql 12582912 Oct 26 22:39 ibdata1 -rw-rw---- 1 mysql mysql 50331648 Oct 26 22:39 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Oct 26 22:39 ib_logfile1 drwx------ 2 mysql root 4096 Oct 26 22:39 mysql -rw-rw---- 1 mysql mysql 29032 Oct 26 22:39 mysql-bin.000001 -rw-rw---- 1 mysql mysql 19 Oct 26 22:39 mysql-bin.index -rw-rw---- 1 mysql mysql 7 Oct 26 22:39 mysql-bin.state drwx------ 2 mysql mysql 20 Oct 26 22:39 performance_schema drwx------ 2 mysql root 6 Oct 26 22:39 test [root@node105.yinzhengjie.org.cn /usr/local/mysql]#
3>.自定义MySQL各个实例的配置文件
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf #自定义MySQL实例的配置文件 [mysqld] port = 3306 datadir = /mysql/3306/data socket = /mysql/3306/socket/mysql.sock [mysqld_safe] log-error = /mysql/3306/log/mariadb.log pid-file = /mysql/3306/pid/mariadb.pid [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3307/etc/my.cnf [mysqld] port = 3307 datadir = /mysql/3307/data socket = /mysql/3307/socket/mysql.sock [mysqld_safe] log-error = /mysql/3307/log/mariadb.log pid-file = /mysql/3307/pid/mariadb.pid [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3308/etc/my.cnf [mysqld] port = 3308 datadir = /mysql/3308/data socket = /mysql/3308/socket/mysql.sock [mysqld_safe] log-error = /mysql/3308/log/mariadb.log pid-file = /mysql/3308/pid/mariadb.pid [root@node105.yinzhengjie.org.cn ~]#
4>.自定义MySQL各个实例的启动脚本
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/mysqld #!/bin/bash #******************************************************************** #Author: YinZhengjie #Email: y1053419035@qq.com #Blog： https://www.cnblogs.com/yinzhengjie/ #Description: mysql start script #******************************************************************** port=3306 mysql_user="root" mysql_pwd="yinzhengjie" cmd_path="/usr/local/mysql/bin" mysql_basedir="/mysql" mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock" function_start_mysql() { if [ ! -e "$mysql_sock" ];then printf "Starting MySQL... " ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null & else printf "MySQL is running... " exit fi } function_stop_mysql() { if [ ! -e "$mysql_sock" ];then printf "MySQL is stopped... " exit else printf "Stoping MySQL... " ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown fi } function_restart_mysql() { printf "Restarting MySQL... " function_stop_mysql sleep 2 function_start_mysql } case $1 in start) function_start_mysql ;; stop) function_stop_mysql ;; restart) function_restart_mysql ;; *) printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart} " esac [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3307/mysqld #!/bin/bash #******************************************************************** #Author: YinZhengjie #Email: y1053419035@qq.com #Blog： https://www.cnblogs.com/yinzhengjie/ #Description: mysql start script #******************************************************************** port=3307 mysql_user="root" mysql_pwd="yinzhengjie" cmd_path="/usr/local/mysql/bin" mysql_basedir="/mysql" mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock" function_start_mysql() { if [ ! -e "$mysql_sock" ];then printf "Starting MySQL... " ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null & else printf "MySQL is running... " exit fi } function_stop_mysql() { if [ ! -e "$mysql_sock" ];then printf "MySQL is stopped... " exit else printf "Stoping MySQL... " ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown fi } function_restart_mysql() { printf "Restarting MySQL... " function_stop_mysql sleep 2 function_start_mysql } case $1 in start) function_start_mysql ;; stop) function_stop_mysql ;; restart) function_restart_mysql ;; *) printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart} " esac [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3308/mysqld #!/bin/bash #******************************************************************** #Author: YinZhengjie #Email: y1053419035@qq.com #Blog： https://www.cnblogs.com/yinzhengjie/ #Description: mysql start script #******************************************************************** port=3308 mysql_user="root" mysql_pwd="yinzhengjie" cmd_path="/usr/local/mysql/bin" mysql_basedir="/mysql" mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock" function_start_mysql() { if [ ! -e "$mysql_sock" ];then printf "Starting MySQL... " ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null & else printf "MySQL is running... " exit fi } function_stop_mysql() { if [ ! -e "$mysql_sock" ];then printf "MySQL is stopped... " exit else printf "Stoping MySQL... " ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown fi } function_restart_mysql() { printf "Restarting MySQL... " function_stop_mysql sleep 2 function_start_mysql } case $1 in start) function_start_mysql ;; stop) function_stop_mysql ;; restart) function_restart_mysql ;; *) printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart} " esac [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# chmod +x /mysql/3306/mysqld #别忘记给各个MySQL实例的启动脚本添加执行权限。 [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# chmod +x /mysql/3307/mysqld [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# chmod +x /mysql/3308/mysqld
5>.启动mysql各个实例
[root@node105.yinzhengjie.org.cn ~]# ll /mysql/ total 0 drwxr-xr-x 6 mysql root 68 Oct 26 23:08 3306 drwxr-xr-x 7 root root 79 Oct 26 23:08 3307 drwxr-xr-x 7 root root 79 Oct 26 23:09 3308 [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# chown mysql:mysql /mysql/ -R #将所有实例的目录权限归属于mysql用户 [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll /mysql/ total 0 drwxr-xr-x 6 mysql mysql 68 Oct 26 23:08 3306 drwxr-xr-x 7 mysql mysql 79 Oct 26 23:08 3307 drwxr-xr-x 7 mysql mysql 79 Oct 26 23:09 3308 [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll /mysql/3306/ total 4 drwxr-xr-x 5 mysql mysql 251 Oct 26 22:17 data drwxr-xr-x 2 mysql mysql 20 Oct 26 22:59 etc drwxr-xr-x 2 mysql mysql 6 Oct 26 21:33 log -rw-r--r-- 1 mysql mysql 1277 Oct 26 23:08 mysqld drwxr-xr-x 2 mysql mysql 6 Oct 26 21:33 socket [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll /mysql/3306/etc/ total 4 -rw-r--r-- 1 mysql mysql 184 Oct 26 22:59 my.cnf [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# /mysql/3306/mysqld start #启动MySQL的3306实例，下面并分别启动3307和3308实例 Starting MySQL... [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# /mysql/3307/mysqld start Starting MySQL... [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# /mysql/3308/mysqld start Starting MySQL... [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ss -ntl #检查对应的端口号 State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 80 :::3306 :::* LISTEN 0 80 :::3307 :::* LISTEN 0 80 :::3308 :::* LISTEN 0 128 :::22 :::* [root@node105.yinzhengjie.org.cn ~]#
6>.连接各个实例检查服务是否运行正常
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -p -S /mysql/3306/socket/mysql.sock #使用指定的套接字连接。 Enter password: Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 8 Server version: 10.2.19-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> SELECT @@port; #检查当前实例的运行端口 +--------+ | @@port | +--------+ | 3306 | +--------+ 1 row in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> STATUS -------------- mysql Ver 15.1 Distrib 10.2.19-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 8 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.2.19-MariaDB MariaDB Server Protocol version: 10 Connection: Localhost via UNIX socket #注意这里的提示是基于套接字连接的，当然我们也可以基于IP地址的方式连接。 Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /mysql/3306/socket/mysql.sock Uptime: 3 min 45 sec Threads: 7 Questions: 5 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 11 Queries per second avg: 0.022 -------------- MariaDB [(none)]> QUIT Bye [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -p -h 127.0.0.1 -P 3307 #基于IP地址的方式连接MySQL Enter password: Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 8 Server version: 10.2.19-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> SELECT @@port; +--------+ | @@port | +--------+ | 3307 | +--------+ 1 row in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> STATUS -------------- mysql Ver 15.1 Distrib 10.2.19-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 8 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.2.19-MariaDB MariaDB Server Protocol version: 10 Connection: 127.0.0.1 via TCP/IP #当前连接状态是基于TCP/IP协议连接的。 Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3307 Uptime: 6 min 24 sec Threads: 7 Questions: 5 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 11 Queries per second avg: 0.013 -------------- MariaDB [(none)]> MariaDB [(none)]>
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -p -S /mysql/3308/socket/mysql.sock -P 3308 #连接MySQL的3308实例 Enter password: Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 8 Server version: 10.2.19-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> SELECT @@port; +--------+ | @@port | +--------+ | 3308 | +--------+ 1 row in set (0.00 sec) MariaDB [(none)]> STATUS -------------- mysql Ver 15.1 Distrib 10.2.19-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 8 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.2.19-MariaDB MariaDB Server Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /mysql/3308/socket/mysql.sock Uptime: 8 min 15 sec Threads: 7 Questions: 5 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 11 Queries per second avg: 0.010 -------------- MariaDB [(none)]> quit Bye [root@node105.yinzhengjie.org.cn ~]#
7>.为各个MySQL实例进行安全初始化
[root@node105.yinzhengjie.org.cn ~]# mysql_secure_installation -S /mysql/3306/socket/mysql.sock #为3306实例修改数据库密码 NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. Set root password? [Y/n] y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] y ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB! [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql_secure_installation -S /mysql/3307/socket/mysql.sock #为3307实例修改数据库密码 NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. Set root password? [Y/n] y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] y ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB! [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql_secure_installation -S /mysql/3308/socket/mysql.sock #为3308实例修改数据库密码 NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. Set root password? [Y/n] y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] y ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB! [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -h 127.0.0.1 -P 3308 -pyinzhengjie Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 17 Server version: 10.2.19-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> SELECT user,password,host FROM mysql.user; #很显然，匿名用户被删除而且root用户被加密啦~ +------+-------------------------------------------+-----------+ | user | password | host | +------+-------------------------------------------+-----------+ | root | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | localhost | | root | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | 127.0.0.1 | | root | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | ::1 | +------+-------------------------------------------+-----------+ 3 rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> STATUS -------------- mysql Ver 15.1 Distrib 10.2.19-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 17 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.2.19-MariaDB MariaDB Server Protocol version: 10 Connection: 127.0.0.1 via TCP/IP #当前连接基于TCP/IP协议连接的 Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3308 Uptime: 6 min 36 sec Threads: 7 Questions: 23 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 12 Queries per second avg: 0.058 -------------- MariaDB [(none)]>
8>.停止MySQL服务
[root@node105.yinzhengjie.org.cn ~]# ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 80 :::3306 :::* LISTEN 0 80 :::3307 :::* LISTEN 0 80 :::3308 :::* LISTEN 0 128 :::22 :::* [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# /mysql/3306/mysqld stop Stoping MySQL... [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# /mysql/3307/mysqld stop Stoping MySQL... [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# /mysql/3308/mysqld stop Stoping MySQL... [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 128 :::22 :::* [root@node105.yinzhengjie.org.cn ~]#
9>.注意事项
MySQL启动脚本很方便管理多实例，给我们带来方便的同时在安全性上却打了大大的折扣，细心的小伙伴如果看了我上面的启动脚本会发现mysql数据库root用户的密码被暴漏了。推荐为脚本文件修改权限为700且仅有root用户方能调用。
我们也可以为脚本添加开机自启动，需要把启动脚本放在"/etc/init.d"目录中，但是脚本文件得添加类似"chkconfig: 123 70 3"的行注释，否则会抛出"... does not support chkconfig"的字样。
无论你的MySQL程序基于哪种方式安装，关于MySQL不同版本实例的实战参考本篇博客即可，当然你也可以使用MySQL官网提供的"mysqld_mutl"(要求版本相同)工具来实现多实例配置。
[root@node105.yinzhengjie.org.cn ~]# mysqld_multi --help #查看多实例脚本的帮助信息 mysqld_multi version 2.20 by Jani Tolonen Description: mysqld_multi can be used to start, or stop any number of separate mysqld processes running in different TCP/IP ports and UNIX sockets. mysqld_multi can read group [mysqld_multi] from my.cnf file. You may want to put options mysqld=... and mysqladmin=... there. Since version 2.10 these options can also be given under groups [mysqld#], which gives more control over different versions. One can have the default mysqld and mysqladmin under group [mysqld_multi], but this is not mandatory. Please note that if mysqld or mysqladmin is missing from both [mysqld_multi] and [mysqld#], a group that is tried to be used, mysqld_multi will abort with an error. mysqld_multi will search for groups named [mysqld#] from my.cnf (or the given --defaults-extra-file=...), where '#' can be any positive integer starting from 1. These groups should be the same as the regular [mysqld] group, but with those port, socket and any other options that are to be used with each separate mysqld process. The number in the group name has another function; it can be used for starting, stopping, or reporting any specific mysqld server. Usage: mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...] or mysqld_multi [OPTIONS] {start|stop|report} [GNR-GNR,GNR,GNR-GNR,...] The GNR means the group number. You can start, stop or report any GNR, or several of them at the same time. (See --example) The GNRs list can be comma separated or a dash combined. The latter means that all the GNRs between GNR1-GNR2 will be affected. Without GNR argument all the groups found will either be started, stopped, or reported. Note that syntax for specifying GNRs must appear without spaces. Options: These options must be given before any others: --no-defaults Do not read any defaults file --defaults-file=... Read only this configuration file, do not read the standard system-wide and user-specific files --defaults-extra-file=... Read this configuration file in addition to the standard system-wide and user-specific files Using: --example Give an example of a config file with extra information. --help Print this help and exit. --log=... Log file. Full path to and the name for the log file. NOTE: If the file exists, everything will be appended. Using: --mysqladmin=... mysqladmin binary to be used for a server shutdown. Since version 2.10 this can be given within groups [mysqld#] Using: --mysqld=... mysqld binary to be used. Note that you can give mysqld_safe to this option also. The options are passed to mysqld. Just make sure you have mysqld in your PATH or fix mysqld_safe. Using: Please note: Since mysqld_multi version 2.3 you can also give this option inside groups [mysqld#] in ~/.my.cnf, where '#' stands for an integer (number) of the group in question. This will be recognised as a special option and will not be passed to the mysqld. This will allow one to start different mysqld versions with mysqld_multi. --no-log Print to stdout instead of the log file. By default the log file is turned on. --password=... Password for mysqladmin user. --silent Disable warnings. --tcp-ip Connect to the MariaDB server(s) via the TCP/IP port instead of the UNIX socket. This affects stopping and reporting. If a socket file is missing, the server may still be running, but can be accessed only via the TCP/IP port. By default connecting is done via the UNIX socket. --user=... mysqladmin user. Using: root --verbose Be more verbose. --version Print the version number and exit. --wsrep-new-cluster Bootstrap a cluster. [root@node105.yinzhengjie.org.cn ~]#
三.为MySQL实例设置默认字符集为utf8mb4
1>.修改服务端字符集
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 10 Server version: 10.2.19-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> STATUS -------------- mysql Ver 15.1 Distrib 10.2.19-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 10 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.2.19-MariaDB MariaDB Server Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /mysql/3306/socket/mysql.sock Uptime: 3 hours 29 min 26 sec Threads: 8 Questions: 120 Slow queries: 0 Opens: 41 Flush tables: 1 Open tables: 33 Queries per second avg: 0.009 -------------- MariaDB [(none)]> MariaDB [(none)]> QUIT Bye [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# vim /mysql/3306/etc/my.cnf [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf #需要在"[mysqld]"添加"character-set-server"属性 [mysqld] character-set-server=utf8mb4 port = 3306 datadir = /mysql/3306/data socket = /mysql/3306/socket/mysql.sock [mysqld_safe] log-error = /mysql/3306/log/mariadb.log pid-file = /mysql/3306/pid/mariadb.pid [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# /mysql/3306/mysqld restart Restarting MySQL... Stoping MySQL... Starting MySQL... [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 8 Server version: 10.2.19-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> STATUS -------------- mysql Ver 15.1 Distrib 10.2.19-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 8 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.2.19-MariaDB MariaDB Server Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /mysql/3306/socket/mysql.sock Uptime: 3 sec Threads: 7 Questions: 4 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 11 Queries per second avg: 1.333 -------------- MariaDB [(none)]> quit Bye [root@node105.yinzhengjie.org.cn ~]#
2>.修改客户端字符集
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 9 Server version: 10.2.19-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> STATUS -------------- mysql Ver 15.1 Distrib 10.2.19-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 9 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.2.19-MariaDB MariaDB Server Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /mysql/3306/socket/mysql.sock Uptime: 52 sec Threads: 7 Questions: 8 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 11 Queries per second avg: 0.153 -------------- MariaDB [(none)]> QUIT Bye [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# vim /etc/my.cnf.d/mysql-clients.cnf [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# grep -v ^# /etc/my.cnf.d/mysql-clients.cnf #修改客户端的默认字符集 [mysql] default-character-set=utf8mb4 [mysql_upgrade] [mysqladmin] [mysqlbinlog] [mysqlcheck] [mysqldump] [mysqlimport] [mysqlshow] [mysqlslap] [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 10 Server version: 10.2.19-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> STATUS -------------- mysql Ver 15.1 Distrib 10.2.19-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 10 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.2.19-MariaDB MariaDB Server Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /mysql/3306/socket/mysql.sock Uptime: 2 min 28 sec Threads: 7 Questions: 12 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 11 Queries per second avg: 0.081 -------------- MariaDB [(none)]> MariaDB [(none)]> EXIT Bye [root@node105.yinzhengjie.org.cn ~]#