mysql多实例安装

多实例部署优势：

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

1 部署好mysql软件

[root@miles ~]# ps -ef |grep mysqldroot       3083      1  0 Dec03 ?        00:00:00 /bin/sh /usr/local/mysql5.6.27/bin/mysqld_safe --defaults-file=/data/my.cnfmysql      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 mysqldroot       3083      1  0 Dec03 ?        00:00:00 /bin/sh /usr/local/mysql5.6.27/bin/mysqld_safe --defaults-file=/data/my.cnfmysql      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=3333root      13531  11356  0 15:17 pts/0    00:00:00 /bin/sh /usr/local/mysql5.6.27/bin/mysqld_safe --defaults-file=/data/my1.cnfmysql     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.sockmysql> 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 selectedmysql> 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-filesYou 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.serverbasedir=/usr/local/mysql5.6.27bindir=/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.cnfsocket     = /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.cnfsocket     = /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 reportReporting MySQL serversMySQL server from group: mysqld1 is runningMySQL 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