MySQL多实例.md

MySQL5.7多实例配置

数据库实例1配置文件

# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
port=3306
socket=/tmp/mysql.sock
log_error=error.log
user=mysql
skip-name-resolve

[client]
user = root
password = redhat

[mysql]
prompt=(\u@\h) [\d]>\_

[mysqld1]
port = 3307
datadir = /data/mysql_1
socket = /tmp/mysql.sock1

数据库初始化

# mysqld --initialize-insecure --datadir=/data/mysql_1

mysql1多实例配置

在配置文件中新增如下内容：

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /usr/local/mysql/mysqld_multi.log

多实例初始化：

# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
# mysqld_multi start 1
# ss -tnlp |grep mysql
LISTEN     0      80                       :::3306                    :::*      users:(("mysqld",2094,16))
LISTEN     0      80                       :::3307                    :::*      users:(("mysqld",3097,20))
# mysql -S /tmp/mysql.sock1 -u root -hlocalhost -p
Enter password:

(root@localhost) [(none)]> set password = "redhat";
Query OK, 0 rows affected (0.00 sec)

说明：实例启动后连接密码要么是空，要么是将密码生成在数据目录的错误日志中，其次在登录系统后最好将root密码设置成统一格式的，这样在今后登录就不用在输入密码了。

mysql2多实例配置

在配置文件中新增如下内容：

# cat /etc/my.cnf
[mysqld2]
port = 3308
datadir = /data/mysql_2
socket = /tmp/mysql.sock2
# mysqld --initialize-insecure --datadir=/data/mysql_2
# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is not running
# mysqld_multi start 2
# mysql -S /tmp/mysql.sock2 -uroot -hlocalhost -p
Enter password:

(root@localhost) [(none)]> set password = "redhat";
Query OK, 0 rows affected (0.00 sec)

# mysql -S /tmp/mysql.sock2

启动关闭注意事项

我们通过mysql 提供的/etc/inti.d/mysqld脚本启动的实例与mysqld_multi两种启动与关闭互不影响。他们的配置文件会先使用[mysqld]中配置的，但是如果多实例中自己配置和[mysqld]中的配置重合则会覆盖[mysqld]中的配置。
mysqld_multi关闭实例一定要指定实例名称，不然会自动将所有的实例都给关闭了，这个需要注意。

# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
# mysqld_multi stop
# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
# mysqld_multi start 1
# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is not running
# mysqld_multi start 2
# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
# mysqld_multi stop 2
# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is not running
# mysqld_multi start 2

3.同时先前定义的3306端口的mysql 也是也是可以通过mysqld_multi的方式启动，需要将配置文件新增如下内容：

# cat /etc/my.cnf
···
[mysqld100]
port = 3306
datadir = /data/mysql
socket = /tmp/mysql.sock
···
# /etc/init.d/mysqld stop
Shutting down MySQL..                                      [  OK  ]
# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld100 is not running
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
# /etc/init.d/mysqld status
MySQL is not running                                       [FAILED]
# mysqld_multi start 100
# /etc/init.d/mysqld status
MySQL running (4700)                                       [  OK  ]

4.同时mysql还提供了mysqld_multi.server的系统服务启动脚本，如果有需要则可以拷贝到/etc/init.d/目录中进行启动与关闭。
5.在配置文件的mysqld_multi中，我们配置的mysql多实例的启动命令是mysqld_safe，关闭命令是mysqladmin。其中mysqld_safe是负责监控mysqld进程的脚本，如果mysqld意外挂掉是会将mysqld进程重新启动。意外挂掉例如：kill -9 ${mysql_pid}。
6.在配置mysqld_multi关闭多实例的时候我们使用的关闭的帐号密码是在[client]中定义的帐号密码，并且此帐号密码在所有mysql实例中必须都是相同且存在，不然是无法通过mysqld_multi命令管理mysql实例的启动与关闭。
7.mysqld_multi启动的帐号密码如果定义在[mysqld_multi]中，密码选项配置为pass而不是client端中配置的password选项。

多实例之不同版本安装

安装mysql5.6：

# tar xf mysql-5.6.31-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
# ln -sv /usr/local/mysql-5.6.31-linux-glibc2.5-x86_64 /usr/local/mysql56
# cd /usr/local/mysql56
# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql

配置5.6多实例：

[mysqld3]
server-id = 13
basedir=/usr/local/mysql56/
datadir=/data/mysql_3
socket = /tmp/mysql.sock3
port = 3356

启动5.6实例：

# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld100 is running
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is not running
# mysqld_multi start 3
# mysql -S /tmp/mysql.sock3 -uroot -hlocalhost -p
Enter password: 

(root@localhost) [(none)]> set password = password('redhat');
Query OK, 0 rows affected (0.05 sec)