mysql多实例安装

mysql多实例安装有两种方法：

• 一种是每个实例分别使用自己的my.cnf文件；
• 一种是多个实例使用同一个my.cnf文件，放在/etc/路径下面，使用mysql自带的mysqld_multi工具管理

这里先介绍第一种方法，即每个实例使用自己的my.cnf文件：

上传文件并解压

[root@MySQL ~]# cd /usr/local/
[root@MySQL local]# ls mysql*
mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz
[root@MySQL local]# tar zxvf mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz

解压完毕之后创建软连接

[root@MySQL local]# ln -s mysql-5.7.9-linux-glibc2.5-x86_64 mysql

[root@MySQL local]# cd mysql
[root@MySQL mysql]# ll
total 160
drwxr-xr-x 2 7161 wheel 4096 Oct 12 2015 bin
-rw-r--r-- 1 7161 wheel 17987 Oct 12 2015 COPYING
drwxr-xr-x 2 7161 wheel 4096 Oct 12 2015 docs
drwxr-xr-x 3 7161 wheel 4096 Oct 12 2015 include
-rw-r--r-- 1 7161 wheel 108028 Oct 12 2015 INSTALL-BINARY
drwxr-xr-x 5 7161 wheel 4096 Oct 12 2015 lib
drwxr-xr-x 4 7161 wheel 4096 Oct 12 2015 man
-rw-r--r-- 1 7161 wheel 2478 Oct 12 2015 README
drwxr-xr-x 28 7161 wheel 4096 Oct 12 2015 share
drwxr-xr-x 2 7161 wheel 4096 Oct 12 2015 support-files

创建用户及组

[root@MySQL mysql]# groupadd mysql
[root@MySQL mysql]# useradd -r -g mysql mysql

为mysql路径下面的文件进行用户和组的修改

[root@MySQL mysql]# chown -R mysql .
[root@MySQL mysql]# chgrp -R mysql .

创建数据文件和日志文件路径

[root@MySQL local]# mkdir /data/{3308,3307}/{data,log}
[root@MySQL mysql]# chown -R mysql:mysql /data

创建my.cnf文件，分别在/data/3307和/data/3308路径下面：以3308为例

[root@MySQL 3308]# pwd
/data/3308
[root@MySQL 3308]# cat my.cnf 
[client]
port = 3308
socket = /data/3308/mysql.sock

[mysqld]
server_id=2
port = 3308
user = mysql
character-set-server = utf8mb4
default_storage_engine = innodb
log_timestamps = SYSTEM
socket = /data/3308/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3308/data
pid-file = /data/3308/mysql.pid
max_connections = 1000
max_connect_errors = 1000
table_open_cache = 1024
max_allowed_packet = 128M
open_files_limit = 65535
#####====================================[innodb]==============================
innodb_buffer_pool_size = 1024M
innodb_file_per_table = 1
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_purge_threads = 2
innodb_flush_log_at_trx_commit = 1
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
innodb_max_dirty_pages_pct = 80
innodb_lock_wait_timeout = 30
innodb_data_file_path=ibdata1:1024M:autoextend

#####====================================[log]==============================
log_error = /data/3308/log/mysql-error.log 
slow_query_log = 1
long_query_time = 1 
slow_query_log_file = /data/3308/log/mysql-slow.log

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

初始化数据库：

 mysql5.7.14版本初始化时候已经抛弃了defaults-file参数文件，所以在初始化时候指定配置文件会出错，同时必须保证datadir为空，这里的是5.7.9，所以可以指定defaults-file进行初始化

[root@MySQL mysql]# bin/mysqld --defaults-file=/data/3308/my.cnf --user=mysql --initialize-insecure --datadir=/data/3308/data --basedir=/usr/local/mysql
[root@MySQL mysql]# bin/mysqld --defaults-file=/data/3307/my.cnf --user=mysql --initialize-insecure --datadir=/data/3307/data --basedir=/usr/local/mysql

设置加密连接
[root@MySQL mysql]# bin/mysql_ssl_rsa_setup --datadir=/data/3307/data
[root@MySQL mysql]# bin/mysql_ssl_rsa_setup --datadir=/data/3308/data

创建启动脚本：

# cp /usr/local/mysql/support-files/mysql.server  /etc/rc.d/init.d/mysqld3306
# cp /usr/local/mysql/support-files/mysql.server  /etc/rc.d/init.d/mysqld3307
# cp /usr/local/mysql/support-files/mysql.server  /etc/rc.d/init.d/mysqld3308
# chmod 755 /etc/rc.d/init.d/mysqld3306
# chmod 755 /etc/rc.d/init.d/mysqld3307
# chmod 755 /etc/rc.d/init.d/mysqld3308

# vi /etc/rc.d/init.d/mysqld3308  #编辑 （3306,3307同理）   
basedir=/usr/local/mysql   #MySQL程序安装路径    
datadir=/data/3308  #MySQl数据库存放目录 （这里注意要填my.cnf的上一层目录）


修改
$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &
变成
$bindir/mysqld_safe --defaults-file="$datadir/my.cnf" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &

启动数据库：

# service mysqld3306 start
# service mysqld3307 start
# service mysqld3308 start

多实例登陆，指定socket
mysql -S /data/3306/mysql.sock -uroot -p

mysql远程连接方式：
[root@MySQL bin]# mysql -uroot -p -P3308 -h localhost

添加自动启动
shell> cat /etc/rc.local
shell> echo "/usr/local/mysql/bin/mysqld_multi --defaults-extra-file=/etc/my.cnf start 3306,3307" >> /etc/rc.local

开启防火墙
shell> vi /etc/sysconfig/iptables
-A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 3307 -j ACCEPT

shell> service iptables restart


/*防火墙基本命令:

0）查看当前规则
iptables -L -n --line-number
service iptables status

1） 临时生效，重启后复原
保存: service iptables save
开启： service iptables start
关闭： service iptables stop
重启: service iptables restart

2） 永久性生效，重启后不会复原
开启： chkconfig iptables on
关闭： chkconfig iptables off
*/

第二种方式：

这种方式只是修改/etc/my.cnf文件，配置如下，配置多个实例，分别对每个实例进行初始化，初始化不需要指定defaults-file，默认为/etc/my.cnf。

多实例启动方式：
mysqld_multi start {port1,port2,...}

数据库登陆和关闭的方式和第一种一样

多实例配置文件
[root@MySQL log]# cat /etc/my.cnf

[mysqld_multi] 
mysqld = /usr/local/mysql/bin/mysqld_safe 
mysqladmin = /usr/local/mysql/bin/mysqladmin 
#user = root 
#password = rootpwd 

[mysqld3306] 
port = 3306 
server_id = 3306 
basedir =/usr/local/mysql 
datadir =/data/3306/data
log-bin=/data/3306/log/mysql-bin 
socket =/data/3306/mysql.sock 
log-error =/data/3306/log/mysqld.log 
pid-file =/data/3306/mysqld.pid 

[mysqld3307] 
port = 3307 
server_id = 3307 
basedir =/usr/local/mysql 
datadir =/data/3307/data 
log-bin=/data/3307/log/mysql-bin 
socket =/data/3307/mysql.sock 
log-error =/data/3307/log/mysqld.log 
pid-file =/data/3307/mysqld.pid

遇到错误：

[root@MySQL mysql]# bin/mysqld --initialize-insecure --defaults-file=/data/3306/my.cnf --user=mysql --datadir=/data/3306/data --basedir=/usr/local/mysql
2017-06-26T12:33:38.809622Z 0 [ERROR] unknown variable 'defaults-file=/data/3306/my.cnf'
2017-06-26T12:33:38.809657Z 0 [ERROR] Aborting

这里是个BUG，无论在启动还是初始化，必须把defaults-file这个参数放在第一位，否则就会报错，正确如下：

[root@MySQL mysql]# bin/mysqld --defaults-file=/data/3306/my.cnf --user=mysql --initialize-insecure --datadir=/data/3306/data --basedir=/usr/local/mysql
mysqld: [Warning] World-writable config file '/data/3306/my.cnf' is ignored.
2017-06-26T12:34:12.901074Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-06-26T12:34:13.520944Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-06-26T12:34:13.616526Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-06-26T12:34:13.709685Z 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: c2f799c6-5a6b-11e7-b7ff-08002714955b.
2017-06-26T12:34:13.714696Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-06-26T12:34:13.716948Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.