MySQL配置多实例

1. mysqld读取my.cnf的顺序

第一,首先读取/etc/my.cnf,多实例这个配置文件不会存在。
第二,$datadir/my.cnf,在data目录下寻找此配置文件。
第三,defaultfile=/path/my.cnf 通常写在命令行上,mysqld_safe defaultfile=/tmp/my.cnf &等执行。
第四,~/my.cnf 当前用户下的配置文件。

mysqld多进程运行的必要条件,就是pid文件,datadir,socket,port是独立分开的。

2. 实现多实例

2.1 典型多实例参数

[root@rhel5 ~]# cat /etc/my.cnf
[mysqld_multi]
mysqld                 = /usr/bin/mysqld_safe
mysqladmin             = /usr/bin/mysqladmin
user                   = root
log                    = /tmp/mysqld_multi.log

[mysqld1]
socket                 = /mysqldata1/mysql_3301.sock
port                   = 3301
pid-file               = /mysqldata1/mysql_3301.pid
datadir                = /mysqldata1
log_error              = /mysqldata1/mysql_3301.error
user                   = mysql
character_set_server   = utf8
lower_case_table_names = 1
init_connect           = 'SET NAMES utf8'

[mysqld2]
socket                 = /mysqldata2/mysql_3302.sock
port                   = 3302
pid-file               = /mysqldata2/mysql_3302.pid
datadir                = /mysqldata2
log_error              = /mysqldata2/mysql_3302.error
user                   = mysql
character_set_server   = utf8
lower_case_table_names = 1
init_connect           = 'SET NAMES utf8'

2.2 初始化多个实例

# mysql_install_db –user=mysql –basedir=/usr –datadir=/mysqldata1
# mysql_install_db –user=mysql –basedir=/usr –datadir=/mysqldata2

2.3 检查多实例状态

[root@rhel5 ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running

2.3 启动多实例

[root@rhel5 ~]# mysqld_multi start -no-log
mysqld_multi log file version 2.16; run: 一 3月 24 12:09:14 2014

Starting MySQL servers
140324 12:09:14 mysqld_safe Logging to ‘/mysqldata1/mysql_3301.error’.
140324 12:09:14 mysqld_safe Logging to ‘/mysqldata2/mysql_3302.error’.
140324 12:09:14 mysqld_safe Starting mysqld daemon with databases from /mysqldata1
140324 12:09:14 mysqld_safe Starting mysqld daemon with databases from /mysqldata2

[root@rhel5 mysqldata2]# mysqld_multi start -no-log
mysqld_multi log file version 2.16; run: 一 3月 24 12:17:11 2014

Starting MySQL servers
140324 12:17:11 mysqld_safe Logging to ‘/mysqldata2/mysql_3302.error’.
140324 12:17:11 mysqld_safe Logging to ‘/mysqldata1/mysql_3301.error’.
140324 12:17:11 mysqld_safe A mysqld process already exists
140324 12:17:11 mysqld_safe A mysqld process already exists

2.4 停止多实例

[root@rhel5 mysqldata2]# mysqld_multi stop -no-log
mysqld_multi log file version 2.16; run: 一 3月 24 12:18:45 2014

Stopping MySQL servers
/usr/bin/mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘root’@’localhost’ (using password: NO)’
/usr/bin/mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘root’@’localhost’ (using password: NO)’

2.5 登录MySQL特定实例

#通过sock文件登录
mysql -uroot -p -S /mysqldata1/mysql_3301.sock

#通过端口登录
mysql -uroot -p -P3301

#查看socket文件
mysql> SHOW VARIABLES LIKE ‘socket’;

#查看pid文件
mysql> SHOW VARIABLES LIKE ‘%pid%’;

2.6 启动MySQL实例特殊方法

/usr/bin/mysqld_safe –socket=/mysqldata1/mysql_3301.sock \
–port=3301 –pid-file=/mysqldata1/mysql_3301.pid –datadir=/mysqldata1 \
–log_error=/mysqldata1/mysql_3301.error –user=mysql –character_set_server=utf8 \
–lower_case_table_names=1 –init_connect=”SET NAMES utf8″ &

/usr/bin/mysqld_safe –socket=/mysqldata2/mysql_3302.sock \
–port=3302 –pid-file=/mysqldata2/mysql_3302.pid –datadir=/mysqldata2 \
–log_error=/mysqldata2/mysql_3302.error –user=mysql –character_set_server=utf8 \
–lower_case_table_names=1 –init_connect=”SET NAMES utf8″ &

2.7 停止MySQL实例

# mysqladmin -uroot -p123456 -S/mysqldata1/mysql_3301.sock shutdown
# mysqladmin -uroot -p123456 -S/mysqldata2/mysql_3302.sock shutdown

2.8 多实例参数样例

[root@rhel5 mysqldata]# 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 /usr/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     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = multi_admin
password   = my_password

[mysqld2]
socket     = /tmp/mysql.sock2
port       = 3307
pid-file   = /var/lib/mysql2/hostname.pid2
datadir    = /var/lib/mysql2
language   = /usr/share/mysql/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   = /var/lib/mysql3/hostname.pid3
datadir    = /var/lib/mysql3
language   = /usr/share/mysql/mysql/swedish
user       = unix_user2

[mysqld4]
socket     = /tmp/mysql.sock4
port       = 3309
pid-file   = /var/lib/mysql4/hostname.pid4
datadir    = /var/lib/mysql4
language   = /usr/share/mysql/mysql/estonia
user       = unix_user3

[mysqld6]
socket     = /tmp/mysql.sock6
port       = 3311
pid-file   = /var/lib/mysql6/hostname.pid6
datadir    = /var/lib/mysql6
language   = /usr/share/mysql/mysql/japanese
user       = unix_user4

关于紫砂壶

感悟技术人生
此条目发表在MySQL分类目录,贴了标签。将固定链接加入收藏夹。