mysql多实例安装

多实例部署优势：

• 充分利用系统资源
  
  • mysql为单进程多线程的模型，它对多核的利用不是很好，无法充分利用系统资源。所以服务器上可以考虑多实例部署
• 资源隔离
  
  • 如果不同的业务，部署在一个实例里，那么连接数、缓存等资源都是共享的。如果某个业务压力很大的话，很可能影响另一个业务的正常运行
• 业务、模块隔离
  
  • 例如，A业务需要支持移动端，那么久需要升级数据库以支持utf8mb4字符集。而B业务则不需要。如果A、B是部署在一起的话，升级数据库必然会对B的业务造成影响

1 部署好mysql软件

[root@miles ~]# ps -ef |grep mysqld
root       3083      1  0 Dec03 ?        00:00:00 /bin/sh /usr/local/mysql5.6.27/bin/mysqld_safe --defaults-file=/data/my.cnf
mysql      3431   3083  0 Dec03 ?        00:00:47 /usr/local/mysql5.6.27/bin/mysqld --defaults-file=/data/my.cnf --basedir=/usr/local/mysql5.6.27 --datadir=/data/mysql/ --plugin-dir=/usr/local/mysql5.6.27/lib/plugin --user=mysql --log-error=/data/mysql/error.log --pid-file=/data/mysql//miles.pid --socket=/data/mysql.sock --port=3333

实例初始化
[root@miles ~]# mkdir /data/mysql2
[root@miles ~]# /usr/local/mysql5.6.27/scripts/mysql_install_db  --user=mysql --datadir=/data/mysql2 --basedir=/usr/local/mysql5.6.27

2 编辑多个配置文件，初始化多个实例

[mysql@miles data]$ cp my.cnf my1.cnf
[mysql@miles data]$ pwd
/data
[mysql@miles data]$ vi my1.cnf 
修改相关文件路径
:%s#data/mysql#data/mysql2#g
修改端口号
:%s#3334#data/3334#g

3 启动mysql实例

[root@miles ~]# /usr/local/mysql5.6.27/bin/mysqld_safe --defaults-file=/data/my1.cnf  &

[root@miles ~]# ps -ef |grep mysqld
root       3083      1  0 Dec03 ?        00:00:00 /bin/sh /usr/local/mysql5.6.27/bin/mysqld_safe --defaults-file=/data/my.cnf
mysql      3431   3083  0 Dec03 ?        00:00:53 /usr/local/mysql5.6.27/bin/mysqld --defaults-file=/data/my.cnf --basedir=/usr/local/mysql5.6.27 --datadir=/data/mysql/ --plugin-dir=/usr/local/mysql5.6.27/lib/plugin --user=mysql --log-error=/data/mysql/error.log --pid-file=/data/mysql//miles.pid --socket=/data/mysql.sock --port=3333
root      13531  11356  0 15:17 pts/0    00:00:00 /bin/sh /usr/local/mysql5.6.27/bin/mysqld_safe --defaults-file=/data/my1.cnf
mysql     13881  13531 43 15:17 pts/0    00:00:03 /usr/local/mysql5.6.27/bin/mysqld --defaults-file=/data/my1.cnf --basedir=/usr/local/mysql5.6.27 --datadir=/data/mysql2/ --plugin-dir=/usr/local/mysql5.6.27/lib/plugin --user=mysql --log-error=/data/mysql2/error.log --pid-file=/data/mysql2//miles.pid --socket=/data/mysql2.sock --port=3334

账户安全设置
[root@miles ~]# mysql -uroot  --socket=/data/mysql2.sock
mysql> select user,host,password from mysql.user;
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
| root | localhost |          |
| root | miles     |          |
| root | 127.0.0.1 |          |
| root | ::1       |          |
|      | localhost |          |
|      | miles     |          |
+------+-----------+----------+
6 rows in set (0.00 sec)

mysql> delete from user where user='';
ERROR 1046 (3D000): No database selected
mysql> delete from mysql.user where user='';
Query OK, 2 rows affected (0.01 sec)

mysql> delete from mysql.user where host<>'localhost';
Query OK, 3 rows affected (0.01 sec)

mysql> set password for root@localhost = password('beijing');
Query OK, 0 rows affected (0.00 sec)

mysql> delete from mysql.db;
Query OK, 2 rows affected (0.01 sec)

mysql> drop database test;
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

5 mysqld_multi的配置

配置多实例启动脚本
[root@miles support-files]# pwd
/usr/local/mysql5.6.27/support-files
You have new mail in /var/spool/mail/root
[root@miles support-files]# cp mysqld_multi.server /etc/init.d/mysqld_multi.server
[root@miles support-files]# vi /etc/init.d/mysqld_multi.server
basedir=/usr/local/mysql5.6.27
bindir=/usr/local/mysql5.6.27/bin

配置多实例数据库配置文件
     用mysqld_multi工具查看该配置文件的模板方法，命令为：mysqld_multi --example
     在/etc/目录下创建创建文件/etc/mysqld_multi.cnf，把mysqld_multi --example产生的文件粘进去，修改相应属性，如：mysqld,mysqladmin,socket,port,pid-file,datadir,user等。
[root@miles ~]# more /data/mysqld_multi.cnf 
[mysqld_multi]  
mysqld     = /usr/local/mysql5.6.27/bin/mysqld_safe  
mysqladmin = /usr/local/mysql5.6.27/bin/mysqladmin  
user       =root  
password   =beijing  

[mysqld1]  
defaults-file = /data/my.cnf
socket     = /data/mysql.sock  
port       = 3333  
#pid-file   = /data/mysql/mysql.pid  
datadir    = /data/mysql  
#language   = /usr/local/mysql5.6.27/share/mysql/english  
user       = mysql  

[mysqld2]  
defaults-file = /data/my1.cnf
socket     = /data/mysql2.sock  
port       = 3334 
#pid-file   = /data/mysql2/mysql.pid  
datadir    = /data/mysql2  
#language   = /usr/local/mysql5.6.27/share/mysql/english  
user       = mysql

查看数据库状态  
[root@miles ~]#  mysqld_multi --defaults-extra-file=/data/mysqld_multi.cnf report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running

启动数据库实例1,2
[root@miles ~]#  mysqld_multi --defaults-extra-file=/data/mysqld_multi.cnf start 1,2
关闭数据库实例1,2
[root@miles ~]#  mysqld_multi --defaults-extra-file=/data/mysqld_multi.cnf stop 1,2 -p beijing
启动数据库实例1
[root@miles ~]#  mysqld_multi --defaults-extra-file=/data/mysqld_multi.cnf start 1