mysql多实例搭建
一）多实例安装
[root@mysqlmaster01 ~]# mkdir /data/mysql_data2
[root@mysqlmaster01 ~]# mkdir /data/mysql_data3
[root@mysqlmaster01 ~]# mkdir /data/mysql_data4
[root@mysqlmaster01 ~]# cp -rf /data/mysql_data/mysql/ /data/mysql_data2
[root@mysqlmaster01 ~]# cp -rf /data/mysql_data/mysql/ /data/mysql_data3
[root@mysqlmaster01 ~]# cp -rf /data/mysql_data/mysql/ /data/mysql_data4
[root@mysqlmaster01 ~]# chown -R mysql.mysql /data/mysql_data2
[root@mysqlmaster01 ~]# chown -R mysql.mysql /data/mysql_data3
[root@mysqlmaster01 ~]# chown -R mysql.mysql /data/mysql_data4
配置my.cnf参数
[root@mysqlmaster01 ~]# cat /etc/my.cnf
[client]
user=root
#password = 123456
socket = /tmp/mysql.sock
[mysqld_multi]
user=david
pass=Aa123456
（注意：以上2个用户名和密码，必须事先用root登录localhost，授权该用户拥有shutdown权限）
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /usr/local/mysql/multi.log
[mysqld1]
server-id = 11
datadir=/data/mysql_data1
socket=/tmp/mysql.sock1
port=3306
user=mysql
performance_schema=off
bind_address=10.2.11.226
skip_name_resolve=1
innodb_buffer_pool_size=32M
log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
long_query_time = 2
log_bin = bin.log
[mysqld2]
server-id = 12
datadir=/data/mysql_data2
socket=/tmp/mysql.sock3
port=3307
user=mysql
performance_schema=off
bind_address=10.2.11.226
skip_name_resolve=1
innodb_buffer_pool_size=32M
log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
long_query_time = 2
log_bin = bin.log
[mysqld3]
server-id = 13
datadir=/data/mysql_data3
socket=/tmp/mysql.sock3
port=3308
user=mysql
performance_schema=off
bind_address=10.2.11.226
skip_name_resolve=1
innodb_buffer_pool_size=32M
log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
long_query_time = 2
log_bin = bin.log
[mysqld4]
server-id = 14
datadir=/data/mysql_data4
socket=/tmp/mysql.sock4
port=3309
user=mysql
performance_schema=off
bind_address=10.2.11.226
skip_name_resolve=1
innodb_buffer_pool_size=32M
log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
long_query_time = 2
log_bin = bin.log
[root@mysqlmaster01 ~]# mysqld_multi start 1
[root@mysqlmaster01 ~]# mysqld_multi start 2
[root@mysqlmaster01 ~]# mysqld_multi start 3
[root@mysqlmaster01 ~]# mysqld_multi start 4
查看：
[root@mysqlmaster01 mysql_data1]# ll
total 110628
-rw-r-----. 1 mysql mysql 56 Nov 23 13:30 auto.cnf
-rw-r-----. 1 mysql mysql 177 Nov 23 13:30 bin.000001
-rw-r-----. 1 mysql mysql 13 Nov 23 13:30 bin.index
-rw-r-----. 1 mysql mysql 9335 Nov 23 13:30 error.log
-rw-r-----. 1 mysql mysql 215 Nov 23 13:30 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Nov 23 13:30 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Nov 23 13:30 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Nov 23 13:30 ib_logfile1
drwxr-x---. 2 root root 4096 Nov 23 13:29 mysql
-rw-r-----. 1 mysql mysql 185 Nov 23 13:30 slow.log
[root@mysqlmaster01 mysql_data2]# ll
total 122928
-rw-r-----. 1 mysql mysql 56 Nov 23 13:30 auto.cnf
-rw-r-----. 1 mysql mysql 154 Nov 23 13:30 bin.000001
-rw-r-----. 1 mysql mysql 13 Nov 23 13:30 bin.index
-rw-r-----. 1 mysql mysql 21180 Nov 23 13:30 error.log
-rw-r-----. 1 mysql mysql 12582912 Nov 23 13:30 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Nov 23 13:30 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Nov 23 13:30 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Nov 23 13:30 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Nov 23 11:52 mysql
-rw-r-----. 1 mysql mysql 5 Nov 23 13:30 mysqlmaster01.test.com.pid
-rw-r-----. 1 mysql mysql 185 Nov 23 13:30 slow.log
（其他的类似）
查看
[root@mysqlmaster01 mysql_data3]# ss -tunlp|grep 33*
tcp LISTEN 0 80 10.2.11.226:3306 *:* users:(("mysqld",8937,14))
tcp LISTEN 0 80 10.2.11.226:3307 *:* users:(("mysqld",10191,14))
tcp LISTEN 0 80 10.2.11.226:3308 *:* users:(("mysqld",10487,14))
tcp LISTEN 0 80 10.2.11.226:3309 *:* users:(("mysqld",9831,14))
客户端登录：
[root@mysqlmaster01 data]# mysql -u root -p -S /tmp/mysql.sock4
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20-log MySQL Community Server (GPL)
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.
mysql>
问题1：
由于mysql目录中有一些其他引擎的，如.CSV或.ibd，通过cp直接拷贝到新的数据目录，不能直接使用。
例如需要开启某些特定的时候，这时候就会报该表不存在的错误
解决办法：
建议不要直接拷贝mysql目录，建议针对每个实例进行初始化一次，建议用方法二
第二种方法：每个实例的mysql数据库不一样
1）先建立4个数据目录，并授权mysql
[root@mysqlmaster01 ~]# mkdir /data/mysql_data1
[root@mysqlmaster01 ~]# mkdir /data/mysql_data2
[root@mysqlmaster01 ~]# mkdir /data/mysql_data3
[root@mysqlmaster01 ~]# mkdir /data/mysql_data4
[root@mysqlmaster01 ~]# chown -R mysql.mysql /data/mysql_data2
[root@mysqlmaster01 ~]# chown -R mysql.mysql /data/mysql_data2
[root@mysqlmaster01 ~]# chown -R mysql.mysql /data/mysql_data3
[root@mysqlmaster01 ~]# chown -R mysql.mysql /data/mysql_data4
[root@mysqlmaster01 data]# mysqld_multi start
Installing new database in /data/mysql_data1
2017-11-23T07:01:17.586587Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-11-23T07:01:20.262722Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-11-23T07:01:20.781961Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-11-23T07:01:20.906340Z 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: 1c362b80-d01c-11e7-8b36-5254006fdb68.
2017-11-23T07:01:20.914259Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-11-23T07:01:20.914769Z 1 [Note] A temporary password is generated for root@localhost: NLRkTWz0*118
Installing new database in /data/mysql_data2
2017-11-23T07:01:27.118069Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-11-23T07:01:30.663893Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-11-23T07:01:31.268511Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-11-23T07:01:31.348118Z 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: 226f7556-d01c-11e7-8cdf-5254006fdb68.
2017-11-23T07:01:31.359957Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-11-23T07:01:31.360777Z 1 [Note] A temporary password is generated for root@localhost: N1I+qXlfryrK
Installing new database in /data/mysql_data3
2017-11-23T07:01:37.639565Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-11-23T07:01:40.316276Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-11-23T07:01:40.829721Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-11-23T07:01:40.906120Z 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: 2821e4e8-d01c-11e7-99cc-5254006fdb68.
2017-11-23T07:01:40.910204Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-11-23T07:01:40.910899Z 1 [Note] A temporary password is generated for root@localhost: 0Ip4hLFb;s;m
Installing new database in /data/mysql_data4
2017-11-23T07:01:47.200346Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-11-23T07:01:50.377236Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-11-23T07:01:50.739399Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-11-23T07:01:50.814170Z 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: 2e09bdfe-d01c-11e7-a8da-5254006fdb68.
2017-11-23T07:01:50.818480Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-11-23T07:01:50.819161Z 1 [Note] A temporary password is generated for root@localhost: rf.Kle8hlm8i
[root@mysqlmaster01 ~]# ss -tunlp|grep 33
tcp LISTEN 0 80 10.2.11.226:3306 *:* users:(("mysqld",17901,29))
tcp LISTEN 0 80 10.2.11.226:3307 *:* users:(("mysqld",17914,15))
tcp LISTEN 0 80 10.2.11.226:3308 *:* users:(("mysqld",17918,14))
tcp LISTEN 0 80 10.2.11.226:3309 *:* users:(("mysqld",17917,14))
客户端登录：
[root@mysqlmaster01 data]# mysql -u root -p -S /tmp/mysql.sock1
Enter password: NLRkTWz0*118
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log
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.
mysql> set password="Aa123456";
Query OK, 0 rows affected (0.04 sec)
mysql> exit
Bye
配置启动服务
[root@mysqlmaster01 data]# cd /usr/local/mysql
[root@mysqlmaster01 mysql]# cp support-files/mysqld_multi.server /etc/init.d/mysqld_multi
[root@mysqlmaster01 mysql]# chkconfig --add mysqld_multi
[root@mysqlmaster01 mysql]# chkconfig mysqld_multi on
[root@mysqlmaster01 mysql]# chkconfig --list|grep mysqld_multi
mysqld_multi 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@mysqlmaster01 ~]# /etc/init.d/mysqld_multi start
[root@mysqlmaster01 ~]# ss -tunlp|grep 33
[root@mysqlmaster01 ~]# ss -tunlp|grep 33
tcp LISTEN 0 80 10.2.11.226:3306 *:* users:(("mysqld",2066,34))
[root@mysqlmaster01 ~]# ss -tunlp|grep 33
tcp LISTEN 0 80 10.2.11.226:3306 *:* users:(("mysqld",2066,34))
tcp LISTEN 0 80 10.2.11.226:3307 *:* users:(("mysqld",2074,15))
tcp LISTEN 0 80 10.2.11.226:3308 *:* users:(("mysqld",2090,15))
tcp LISTEN 0 80 10.2.11.226:3309 *:* users:(("mysqld",2094,27))
[root@mysqlmaster01 ~]#
[root@mysqlmaster01 ~]#
[root@mysqlmaster01 ~]# /etc/init.d/mysqld_multi stop
[root@mysqlmaster01 ~]# ss -tunlp|grep 33
[root@mysqlmaster01 ~]#
解决办法：https://www.cnblogs.com/qizhelongdeyang/p/6292966.html
