mysql搭建实例_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:off1:off2:on3:on4:on5:on6: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