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)
