MySQL多实例安装配置

1.环境

OS: CentOS 6.5 x64

MySQL: 5.6 for Linux (x86_64)

2.源码安装MySQL

可以参考：linux 6.5下 MySQL 5.6 源码安装

3.初始化一个新加的数据库实例（3306实例已存在）

# mkdir /u01/mysql/data3307    #创建数据库数据目录
# /u01/mysql/scripts/mysql_install_db --basedir=/u01/mysql/ --datadir=/u01/mysql/data3307/ --user=mysql

4.mysqld_multi工具

可以用mysqld_multi管理多实例，先看一下mysqld_multi工具怎么用：

[root@test1 log]# mysqld_multi --example
# This is an example of a my.cnf file for mysqld_multi.
# Usually this file is located in home dir ~/.my.cnf or /etc/my.cnf
#
# SOME IMPORTANT NOTES FOLLOW:
#
# 1.COMMON USER
#
#   Make sure that the MySQL user, who is stopping the mysqld services, has
#   the same password to all MySQL servers being accessed by mysqld_multi.
#   This user needs to have the 'Shutdown_priv' -privilege, but for security
#   reasons should have no other privileges. It is advised that you create a
#   common 'multi_admin' user for all MySQL servers being controlled by
#   mysqld_multi. Here is an example how to do it:
#
#   GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY 'password'
#
#   You will need to apply the above to all MySQL servers that are being
#   controlled by mysqld_multi. 'multi_admin' will shutdown the servers
#   using 'mysqladmin' -binary, when 'mysqld_multi stop' is being called.
#
# 2.PID-FILE
#
#   If you are using mysqld_safe to start mysqld, make sure that every
#   MySQL server has a separate pid-file. In order to use mysqld_safe
#   via mysqld_multi, you need to use two options:
#
#   mysqld=/path/to/mysqld_safe
#   ledir=/path/to/mysqld-binary/
#
#   ledir (library executable directory), is an option that only mysqld_safe
#   accepts, so you will get an error if you try to pass it to mysqld directly.
#   For this reason you might want to use the above options within [mysqld#]
#   group directly.
#
# 3.DATA DIRECTORY
#
#   It is NOT advised to run many MySQL servers within the same data directory.
#   You can do so, but please make sure to understand and deal with the
#   underlying caveats. In short they are:
#   - Speed penalty
#   - Risk of table/data corruption
#   - Data synchronising problems between the running servers
#   - Heavily media (disk) bound
#   - Relies on the system (external) file locking
#   - Is not applicable with all table types. (Such as InnoDB)
#     Trying so will end up with undesirable results.
#
# 4.TCP/IP Port
#
#   Every server requires one and it must be unique.
#
# 5.[mysqld#] Groups
#
#   In the example below the first and the fifth mysqld group was
#   intentionally left out. You may have 'gaps' in the config file. This
#   gives you more flexibility.
#
# 6.MySQL Server User
#
#   You can pass the user=... option inside [mysqld#] groups. This
#   can be very handy in some cases, but then you need to run mysqld_multi
#   as UNIX root.
#
# 7.A Start-up Manage Script for mysqld_multi
#
#   In the recent MySQL distributions you can find a file called
#   mysqld_multi.server.sh. It is a wrapper for mysqld_multi. This can
#   be used to start and stop multiple servers during boot and shutdown.
#
#   You can place the file in /etc/init.d/mysqld_multi.server.sh and
#   make the needed symbolic links to it from various run levels
#   (as per Linux/Unix standard). You may even replace the
#   /etc/init.d/mysql.server script with it.
#
#   Before using, you must create a my.cnf file either in /u01/mysql/my.cnf
#   or /root/.my.cnf and add the [mysqld_multi] and [mysqld#] groups.
#
#   The script can be found from support-files/mysqld_multi.server.sh
#   in MySQL distribution. (Verify the script before using)
#


[mysqld_multi]
mysqld     = /u01/mysql/bin/mysqld_safe
mysqladmin = /u01/mysql/bin/mysqladmin
user       = multi_admin
password   = my_password


[mysqld2]
socket     = /tmp/mysql.sock2
port       = 3307
pid-file   = /u01/mysql/data2/hostname.pid2
datadir    = /u01/mysql/data2
language   = /u01/mysql/share/mysql/english
user       = unix_user1


[mysqld3]
mysqld     = /path/to/mysqld_safe
ledir      = /path/to/mysqld-binary/
mysqladmin = /path/to/mysqladmin
socket     = /tmp/mysql.sock3
port       = 3308
pid-file   = /u01/mysql/data3/hostname.pid3
datadir    = /u01/mysql/data3
language   = /u01/mysql/share/mysql/swedish
user       = unix_user2


[mysqld4]
socket     = /tmp/mysql.sock4
port       = 3309
pid-file   = /u01/mysql/data4/hostname.pid4
datadir    = /u01/mysql/data4
language   = /u01/mysql/share/mysql/estonia
user       = unix_user3
 
[mysqld6]
socket     = /tmp/mysql.sock6
port       = 3311
pid-file   = /u01/mysql/data6/hostname.pid6
datadir    = /u01/mysql/data6
language   = /u01/mysql/share/mysql/japanese
user       = unix_user4

5.配置多实例

# cd /etc/
# vi my.cnf
[mysqld_multi]
mysqld = /u01/mysql/bin/mysqld_safe
mysqladmin = /u01/mysql/bin/mysqladmin
user = multi_admin       #登陆数据库用户
password = multi_admin      #登陆数据库密码，用于关闭数据库，两台数据库密码设置一样
[mysqld1]
user = mysql
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /u01/mysql/data3306/mysql.pid
basedir = /u01/mysql
datadir = /u01/mysql/data3306
log_error = /var/log/mysql/3306_error.log
[mysqld2]
user = mysql
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /u01/mysql/data3307/mysql.pid
basedir = /u01/mysql
datadir = /u01/mysql/data3307
log_error = /var/log/mysql/3307_error.log

6.启动实例1,2

# 单个实例启动
# mysqld_multi --defaults-file=/u01/mysql/etc/my.cnf start 1
# mysqld_multi --defaults-file=/u01/mysql/etc/my.cnf start 2
# 双实例同时启动
# mysqld_multi --defaults-file=/u01/mysql/etc/my.cnf start 1,2
# 查看MySQL是否启动
# mysqld_multi --defaults-file=/etc/my.cnf report 1,2
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
# netstat -antp |grep mysql
tcp  0    0 0.0.0.0:3306  0.0.0.0:*   LISTEN      20896/mysqld    
tcp  0    0 0.0.0.0:3307  0.0.0.0:*   LISTEN      21499/mysqld
# 加入开机启动
echo "/u01/mysql/mysqld_multi --defaults-file=/u01/mysql/etc/my.cnf start 1,2" >> /etc/rc.local

7.登录MySQL

#为新实例修改密码及创建账户
# mysql -uroot -pxxx -S /tmp/mysql3306.sock  #3306已经有初始密码
> grant shutdown on *.* to 'admin'@'localhost' identified by 'xxx' with grant option;
  
# mysql -uroot -p -S /tmp/mysql3307.sock
Enter password:  #此时密码为空
> set password for 'root'@'localhost'=password('xxx');
> grant shutdown on *.* to 'admin'@'localhost' identified by 'xxx' with grant option;

#使用TCP方式登录测试
# mysql -uroot -pxxx -P3307
> 

7.关闭实例

从MySQL 5.6 后，mysqld_mutli不能把MySQL关掉了。可以用如下方式关闭实例：

mysqladmin -P3307 -umulti-admin -p shutdown
or
mysqladmin -S /tmp/mysql3307.sock -umulti-admin -p shutdown

也可通过修改mysqld_mutli脚本使其可以关闭实例，但生产环境最好不要这样做，所以这里也就不再列出如何修改脚本的方法了。