mysql多实例安装与ssl认证
mysql多实例安装有两种形式：
- 同一数据库版本的多实例安装。
- 不同数据库版本的多实例安装。
同一数据库的多实例安装：
在同一台机器上安装4台mysql数据库实例。
从官网下载MySQL5.6版本的二进制免编译安装包，地址如下MySQL5.6下载
和安装一台mysql实例一样，首先要创建mysql用户，创建datadir目录。
[root@test2 mysql]# useradd -M mysql #创建dataidr目录，因为要安装4个MySQL数据库实例，需要创建4个datadir目录，如下 [root@test2 mysql]# cd /data [root@test2 data]# ls mysql mysql1 mysql2 mysql3 #修改datadir的目录权限均为MySQL [root@test2 data]# chonw -R mysql:mysql mysql*
【单机MySQL5.6安装步骤】在MySQL的解压包中有一个INSTALL-BINARY文件，这里面包含了MySQL的单机安装步骤。
[root@test2 mysql]# ls bin data etc INSTALL-BINARY man my-new.cnf mysql-test scripts sql-bench COPYING docs include lib my.cnf mysql.sock README share support-files [root@test2 mysql]# pwd /usr/local/mysql [root@test2 mysql]# ###因为文件比较内容比较多，仅贴了linux安装MySQL的步骤 shell> groupadd mysql shell> useradd -r -g mysql -s /bin/false mysql shell> cd /usr/local shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz shell> ln -s full-path-to-mysql-VERSION-OS mysql shell> cd mysql shell> chown -R mysql . shell> chgrp -R mysql . shell> scripts/mysql_install_db --user=mysql shell> chown -R root . shell> chown -R mysql data shell> bin/mysqld_safe --user=mysql &
创建完目录之后需要实例化数据库，因为我们需要安装4个MySQL数据库实例，因此要实例化四次。
cd /usr/local/mysql ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql1 ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql2 ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql3 ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql4
实例化之后编写配置文件，MySQL的默认端口是3306，四个MySQL实例的端口分别为3306,3307,3308,3309.
[mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld mysqladmin = /usr/local/mysql/bin/mysqladmin log = /tmp/mysql-mulit.log [mysqld0] log_bin user=mysql basedir = /usr/local/mysql datadir = /data/mysql port = 3306 server_id = 6 skip-grant-tables socket = /tmp/mysql0.sock sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysqld1] log_bin user=mysql basedir = /usr/local/mysql datadir = /data/mysql1 port = 3307 server_id = 7 skip-grant-tables socket = /tmp/mysql1.sock sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysqld2] log_bin user=mysql basedir = /usr/local/mysql datadir = /data/mysql2 port = 3308 server_id = 8 skip-grant-tables socket = /tmp/mysql2.sock sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysqld3] log_bin user=mysql basedir = /usr/local/mysql datadir = /data/mysql3 port = 3309 server_id = 9 skip-grant-tables socket = /tmp/mysql3.sock sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
说明： 我们使用了mysld_multi命令，因此在配置文件的开始，我们配置了mysqld_mulit命令的位置。mysqladmin也是一样。log指定了mysqld_multi的日志位置。
后面使用[mysqldX]的形式区分每一个mysql实例的基本配置，在每个实例中要区分每个MySQL的端口号，每个MySQL的套接字文件名，为了使用套接字本地连接，还有每个mysql的datadir指定到对应的目录，要设置每个数据库的server_id不一样。
然后就是启动mysql实例。
[root@test2 mysql]# mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld0 is not running MySQL server from group: mysqld1 is not running MySQL server from group: mysqld2 is not running MySQL server from group: mysqld3 is not running 由上可知mysql实例并没有运行。 mysqld_multi start 0 #启动第一个MySQL实例 mysqld_multi stop 0 #停止指定的MySQL实例 mysqld_multi start #启动所有的MySQL实例 mysqld_multi stop #停止所有的MySQL实例 ------------------------------------------------ [root@test2 mysql]# mysqld_multi start [root@test2 mysql]# netstat -lntp Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1018/sshd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1282/master tcp 0 0 :::22 :::* LISTEN 1018/sshd tcp 0 0 :::3306 :::* LISTEN 27486/mysqld tcp 0 0 :::3307 :::* LISTEN 27563/mysqld tcp 0 0 :::3308 :::* LISTEN 27566/mysqld tcp 0 0 :::3309 :::* LISTEN 27569/mysqld [root@test2 mysql]# mysql -S /tmp/mysql3.sock # 使用套接字连接MySQL，也可以指定对应的端口号1进行连接 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.28-log Source distribution Copyright (c) 2000, 2015, 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>
至此MySQL多实例（同一版本）已经安装完毕。
不同版本的MySQL多实例安装
在同一台机器上分别安装MySQL5.6版本和MySQL5.7版本，如下：
需要特别注意的是MySQL5.7在这里初始化的时候需要加上参数basedir：
[root@test2 mysql57]# ./bin/mysqld --initialize --user=mysql --datadir=/data/mysql2 --basedir=/usr/local/mysql57/ 2018-10-03T07:20:55.438258Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2018-10-03T07:20:56.669045Z 0 [Warning] InnoDB: New log files created, LSN=45790 2018-10-03T07:20:56.850596Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2018-10-03T07:20:56.922260Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: dee17558-c6dc-11e8-aee5-fa1dae125200. 2018-10-03T07:20:56.924517Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2018-10-03T07:20:56.925248Z 1 [Note] A temporary password is generated for root@localhost: xelpo#9Vekek [root@test2 mysql57]#
和上面的方法一样，我们使用mysqld_mulit的方法来安装mysql5.6和MySQL5.7。
配置文件如下：
[mysqld_multi]
mysqld = /usr/local/mysql57/bin/mysqld
mysqladmin = /usr/local/mysql57/bin/mysqladmin
log = /tmp/mysql-mulit.log
[mysqld1]
log_bin
user=mysql
basedir = /usr/local/mysql
datadir = /data/mysql1
port = 3307
server_id = 7
skip-grant-tables
socket = /tmp/mysql1.sock
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld4]
log_bin
basedir = /usr/local/mysql57
datadir = /data/mysql2
port = 3310
server_id = 11
socket = /tmp/mysql2.sock
user=mysql
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
然后使用mysqld_multi命令启动mysql。
[root@test2 local]# mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld1 is not running MySQL server from group: mysqld2 is not running [root@test2 local]# mysqld_multi start [root@test2 local]# mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld1 is running MySQL server from group: mysqld2 is running [root@test2 local]# netstat -lntp Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1018/sshd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1282/master tcp 0 0 :::22 :::* LISTEN 1018/sshd tcp 0 0 :::3307 :::* LISTEN 858/mysqld tcp 0 0 :::3310 :::* LISTEN 869/mysqld [root@test2 local]#
这样单机多实例已经安装完成！
提示：
1.我们可以把配置文件中【mysqd_mulit】中的mysqld指向文件中的mysqld_safe命令，这样是以守护进程的方式启动mysql，在mysql意外宕机后，mysqld_safe后台进程可以自动把启动MySQL！
2.MySQL多实例安装时，配置文件中的参数配置是可以继承的，我们可以把一些通用的配置文件，配置在mysqld的标签下面，对于每个数据库各自独立的参数配置，可以放置在各自的标签中。
mysql数据库的连接
第一种连接：在本机使用socket连接：
连接测试的时候有个问题如下：
[root@test2 local]# mysql -S /tmp/mysql1.sock #连接MySQL5.6 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.22-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, 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> select version(); +------------+ | version() | +------------+ | 5.7.22-log | +------------+ 1 row in set (0.00 sec) mysql> #连接MySQL5.7 [root@test2 ~]# mysql -S /tmp/mysql2.sock -p123qwE#! Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 21 Server version: 5.7.22-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, 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 statemen mysql> select version(); +------------+ | version() | +------------+ | 5.7.22-log | +------------+ 1 row in set (0.00 sec) mysql> #上面显示两个都是5.7的版本，不知道为什么？
【这个问题求答案？】
mysql除了上面的使用socket连接外，还可以使用用户名和密码，端口号的方式连接：
[root@test2 local]# mysql -uroot -P 3307 #不指定主机默认使用套接字 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/usr/local/mysql/mysql.sock' (2) [root@test2 local]# mysql -uroot -h10.0.102.204 -P 3307 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.22-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, 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> #MySQL5.7默认必须使用密码连接 [root@test2 ~]# mysql -uroot -p123qwE#! -h127.0.0.1 -P 3310 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 23 Server version: 5.7.22-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, 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>
当一个主机上有很多数据库实例时，这种连接会很不方便也必将容易混乱，使用login-path参数连接：
MySQL的安全连接
MySQL的ssl连接参照博文：https://www.cnblogs.com/mysql-dba/p/7061300.html
查看是否开启了ssl认证：
mysql> show variables like "%ssl%"; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | | +---------------+----------+ 9 rows in set (0.01 sec) mysql>
开启ssl认证的方法：
第一种在安装的时候开启：
./bin/mysqld --initialize --user=mysql --datadir=/data/mysql2 --basedir=/usr/local/mysql57/
./bin/mysql_ssl_rsa_setup #执行这个初始化操作，会产生一系列用于ssl认证的文件
第二种：数据库安装之后开启：
[root@test3 bin]# service mysqld stop Shutting down MySQL.. SUCCESS! [root@test3 bin]# mysql_ssl_rsa_setup Generating a 2048 bit RSA private key ........................................................................................................................+++ ..............................+++ writing new private key to 'ca-key.pem' ----- Generating a 2048 bit RSA private key ....+++ .........................+++ writing new private key to 'server-key.pem' ----- Generating a 2048 bit RSA private key .+++ ........................................+++ writing new private key to 'client-key.pem' ----- [root@test3 bin]# chown -R mysql:mysql /data/mysql #修改生成的文件的属主与属组 [root@test3 bin]# service mysqld start Starting MySQL. SUCCESS! mysql> show variables like "%ssl%"; #mysql会自动检查datadir目录下面是否有认证文件，若有则自动启动认证！若是这一步仍然没有自动启动，请查看配置文件是否配置来skip-ssl +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | ca.pem | | ssl_capath | | | ssl_cert | server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | server-key.pem | +---------------+-----------------+ 9 rows in set (0.00 sec) mysql> [root@test3 bin]#
上面的这一步生成来几个文件：
[root@test2 data]# ll *.pem -rw------- 1 mysql mysql 1675 Apr 23 22:29 ca-key.pem #ca私钥 -rw-r--r-- 1 mysql mysql 1074 Apr 23 22:29 ca.pem #ca证书，客户端连接需要使用 -rw-r--r-- 1 mysql mysql 1078 Apr 23 22:29 client-cert.pem #客户端证书 -rw------- 1 mysql mysql 1675 Apr 23 22:29 client-key.pem #客户端私钥 -rw------- 1 mysql mysql 1675 Apr 23 22:29 private_key.pem #私钥/公钥对的，私有成员 -rw-r--r-- 1 mysql mysql 451 Apr 23 22:29 public_key.pem #私钥/公钥对的，共有成员 -rw-r--r-- 1 mysql mysql 1078 Apr 23 22:29 server-cert.pem #服务端证书文件 -rw------- 1 mysql mysql 1675 Apr 23 22:29 server-key.pem #服务端私钥文件
把上面的三个标记红色的文件传递给客户端，客户端在连接的时候分别制定--ssl-ca, --ssl-cert, --ssl-key即可！
创建一个用户：
mysql> grant select on *.* to "dba"@"localhost" identified by "123456";
修改用户必须使用ssl认证：
mysql> alter user "dba"@"localhost" require ssl; Query OK, 0 rows affected (0.00 sec)
测试：
[root@test3 bin]# mysql -udba -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'dba'@'localhost' (using password: YES) #使用ssl认证： [root@test3 bin]# mysql -udba --ssl-ca=/data/mysql/ca.pem --ssl-cert=/data/mysql/client-cert.pem --ssl-key=/data/mysql/client-key.pem Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.7.22-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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. You are enforcing ssl conection via unix socket. Please consider switching ssl off as it does not make connection via unix socket any more secure. mysql>
测试一下ssl的加密：
在10.0.102.214上创建两个mysql用户，在10.0.102.204上连接，测试其加密性：
grant all privileges on *.* to "test1"@"10.0.102.204" identified by "123456"; grant all privileges on *.* to "test2"@"10.0.102.204" identified by "123456"; alter user "test2"@"10.0.102.204" require ssl; #test1用户可以使用密码连接，test2用户必须使用ssl连接。
把ssl连接所需的证书拷贝到10.0.102.214机器上：
scp ca.pem client-cert.pem client-key.pem 10.0.102.204:/root/ssl
在204的机器上用test1登录测试：
[root@test2 ssl]# mysql -utest1 -h10.0.102.214 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.7.22-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, 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> use cmdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> insert into cmdb_userinfo values(10, "bejing", "3223"); Query OK, 1 row affected (0.01 sec) mysql> select * from cmdb_userinfo limit 2; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 6 | use1r | pwd1 | | 7 | wxz | 123 | +----+----------+----------+ 2 rows in set (0.00 sec) mysql> #在214上抓包如下： [root@test3 mysql]# tshark -i eth0 -R "tcp.port == 3306 && mysql.query" -T fields -e frame.time -e "ip.src" -e "mysql.query" Running as user "root" and group "root". This could be dangerous. Capturing on eth0 Oct 3, 2018 18:01:23.709292000 10.0.102.204 select @@version_comment limit 1 Oct 3, 2018 18:01:28.344180000 10.0.102.204 SELECT DATABASE() Oct 3, 2018 18:01:28.346248000 10.0.102.204 show databases Oct 3, 2018 18:01:28.346967000 10.0.102.204 show tables Oct 3, 2018 18:02:02.569678000 10.0.102.204 insert into cmdb_userinfo values(10, \"bejing\", \"3223\") Oct 3, 2018 18:02:26.728092000 10.0.102.204 select * from cmdb_userinfo limit 2 #可以看到执行的命令都可以通过抓包看到
在204机器上用test2的ssl测试：
[root@test2 ssl]# mysql -utest2 --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -h10.0.102.214 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.7.22-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, 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> use cmdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> insert into cmdb_userinfo values(11, "hongkong", "343223"); Query OK, 1 row affected (0.02 sec) mysql> select * from cmdb_userinfo limit 2; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 6 | use1r | pwd1 | | 7 | wxz | 123 | +----+----------+----------+ 2 rows in set (0.00 sec) mysql> #抓包测试 [root@test3 mysql]# tshark -i eth0 -R "tcp.port == 3306 && mysql.query" -T fields -e frame.time -e "ip.src" -e "mysql.query" Running as user "root" and group "root". This could be dangerous. Capturing on eth0 #什么也没有
虽然mysql服务器开启来ssl连接的认证，但是若客户端有请求它仍然允许未加密的连接。可以通过打开require_secure_transport选项来解决这个问题。 这需要所有连接都使用SSL或本地Unix套接字。 由于只能从服务器本身访问Unix套接字，因此对远程用户开放的唯一连接选项将使用SSL。
只想要某个用户使用ssl连接
测试过程中发现一个问题如下：
#建立来一个用户，要求使用ssl连接
root@(none) 11:24:30>create user "ssltest"@"%" identified by "123456"; Query OK, 0 rows affected (0.00 sec) root@(none) 11:25:28>grant all privileges on *.* to "ssltest"@"%"; Query OK, 0 rows affected (0.00 sec) root@(none) 11:25:41>alter user "ssltest"@"%" require ssl; Query OK, 0 rows affected (0.00 sec) root@(none) 11:25:52>flush privileges; Query OK, 0 rows affected (0.00 sec)
在另一台服务器上，使用客户端连接，发现不使用ssl仍然可以连接：
[root@monitor ~]# mysql -ussltest -p123456 -h10.9.8.222 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 39 Server version: 5.7.19-log Source distribution Copyright (c) 2000, 2017, 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. ssltest@(none) 11:26:59>
#修改用户属性为x509.
root@(none) 11:26:00>alter user "ssltest"@"%" require x509; Query OK, 0 rows affected (0.00 sec) root@(none) 11:28:11>flush privileges; Query OK, 0 rows affected (0.00 sec)
#再进行连接测试
[root@monitor ~]# mysql -ussltest -p123456 -h10.9.8.222 #直接使用密码连接错误 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'ssltest'@'10.9.8.223' (using password: YES) [root@monitor ~]# mysql -ussltest -p123456 -h10.9.8.222 --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem #需要使用ssl认证 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 41 Server version: 5.7.19-log Source distribution Copyright (c) 2000, 2017, 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. ssltest@(none) 11:29:14>
