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
total160drwxr-xr-x 2 7161 wheel 4096 Oct 12 2015bin-rw-r--r-- 1 7161 wheel 17987 Oct 12 2015COPYING
drwxr-xr-x 2 7161 wheel 4096 Oct 12 2015docs
drwxr-xr-x 3 7161 wheel 4096 Oct 12 2015include-rw-r--r-- 1 7161 wheel 108028 Oct 12 2015 INSTALL-BINARY
drwxr-xr-x 5 7161 wheel 4096 Oct 12 2015lib
drwxr-xr-x 4 7161 wheel 4096 Oct 12 2015 man
-rw-r--r-- 1 7161 wheel 2478 Oct 12 2015README
drwxr-xr-x 28 7161 wheel 4096 Oct 12 2015share
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@MySQL3308]# catmy.cnf
[client]
port= 3308socket= /data/3308/mysql.sock
[mysqld]
server_id=2port= 3308user=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= 1000max_connect_errors= 1000table_open_cache= 1024max_allowed_packet=128M
open_files_limit= 65535#####====================================[innodb]==============================innodb_buffer_pool_size=1024M
innodb_file_per_table= 1innodb_write_io_threads= 4innodb_read_io_threads= 4innodb_purge_threads= 2innodb_flush_log_at_trx_commit= 1innodb_log_file_size=512M
innodb_log_files_in_group= 2innodb_log_buffer_size=16M
innodb_max_dirty_pages_pct= 80innodb_lock_wait_timeout= 30innodb_data_file_path=ibdata1:1024M:autoextend
#####====================================[log]==============================log_error= /data/3308/log/mysql-error.log
slow_query_log= 1long_query_time= 1slow_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/mysq
[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
数据库启动:
[root@MySQL mysql]# bin/mysqld_safe --defaults-file=/data/3307/my.cnf --user=mysql 2>&1 >/dev/null &[1] 12140[root@MySQL mysql]# bin/mysqld_safe --defaults-file=/data/3308/my.cnf --user=mysql 2>&1 >/dev/null &[2] 12641
关闭数据库:
[root@MySQL mysql]# mysqladmin -S /data/3307/mysql.sock -uroot -p shutdown
Enter password:
[1]- Done bin/mysqld_safe --defaults-file=/data/3307/my.cnf --user=mysql 2>&1 > /dev/null[root@MySQL mysql]# mysqladmin-S /data/3308/mysql.sock -uroot -p shutdown
Enter password:
[2]+ Done bin/mysqld_safe --defaults-file=/data/3308/my.cnf --user=mysql 2>&1 > /dev/null
当然,这里的启动和关闭都可以编写成shell脚本,或者对mysql.server文件进行修改,放置在/etc/init.d路径下面,即成为服务。
多实例登陆,指定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= 3306server_id= 3306basedir=/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= 3307server_id= 3307basedir=/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 moredetails).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.