目的 在一臺服務(wù)器上做主從復(fù)制，充分利用CPU資源。下面的文檔首先說明如何在一臺服務(wù)器開啟多個MySQL
環(huán)境 MySQL 5.5
工具
mysql_install_db
mysqld_multi
配置過程
- 利用mysql_install_db生成數(shù)據(jù)庫
mysql_install_db --datadir=/var/lib/mysql2 --user=mysql
mysql_install_db --datadir=/var/lib/mysql3 --user=mysql
……
mysql_install_db命令會自動創(chuàng)建datadir目錄。
- 利用mysqld_multi工具生成配置文件
[root@ora01 ~]# 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工具的簡單說明，下面是根據(jù)實(shí)際情況，修改的配置文件。為方便測試，將多實(shí)例的配置文件命名為
mysqld_multi.cnf。未指定的其他MySQL參數(shù)，將使用默認(rèn)的。
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
#user = multi_admin
#password = my_password
[mysqld2]
socket = /var/lib/mysql2/mysql2.sock
port = 3307
#pid-file = /var/lib/mysql2/hostname.pid2
datadir = /var/lib/mysql2
#language = /usr/share/mysql/mysql/english
user = mysql
[mysqld3]
socket = /var/lib/mysql3/mysql3.sock
port = 3308
#pid-file = /var/lib/mysql3/hostname.pid3
datadir = /var/lib/mysql3
#language = /usr/share/mysql/mysql/swedish
user = mysql
注：我做測試的時(shí)候，把pid-file和language選項(xiàng)注釋掉了。
- 啟動
# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report
Reporting MySQL servers
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start
等一會兒…………，注意start后面跟具體的數(shù)字，比如start 2，則單獨(dú)開啟mysqld2這個實(shí)例。
# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report
Reporting MySQL servers
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
[root@ora01 mysql2]# netstat -tunlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 192.168.122.1:53 0.0.0.0:* LISTEN 3808/dnsmasq
tcp 0 0 :::3307 :::* LISTEN 24335/mysqld
tcp 0 0 :::3308 :::* LISTEN 24241/mysqld
tcp 0 0 :::22 :::* LISTEN 3537/sshd
udp 0 0 192.168.122.1:53 0.0.0.0:* 3808/dnsmasq
udp 0 0 0.0.0.0:67 0.0.0.0:* 3808/dnsmasq
連接
mysql -uroot -p -h127.0.0.1 -P3307
- 測試
如果--defaults-extra-file選項(xiàng)指定的配置文件中，未具體設(shè)置路徑socket，則會用mysql默認(rèn)的路徑。
mysql> show variables like 'socket';
+---------------+---------------------------+
| Variable_name | Value |
+---------------+---------------------------+
| socket | /var/lib/mysql/mysql.sock |
+---------------+---------------------------+
1 row in set (0.00 sec)
但是pid文件，會自動在數(shù)據(jù)目錄內(nèi)生成
mysql> show variables like '%pid%';
+---------------+---------------------------------+
| Variable_name | Value |
+---------------+---------------------------------+
| pid_file | /var/lib/mysql2/ora01.dh.cn.pid |
+---------------+---------------------------------+
1 row in set (0.00 sec)