mysql的多实例安装与关闭

 

先检查本地的mysql是否已启动，如果启动了的话，先将其关闭：

[root@rhel131 mysql]# ps -ef | grep mysql
root      2833     1  0 10:31 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/rhel131.pid
mysql     3159  2833  0 10:31 ?        00:00:02 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/rhel131.err --pid-file=/usr/local/mysql/data/rhel131.pid --socket=/tmp/mysql.sock --port=3306
root      4414  4362  0 10:38 pts/1    00:00:00 grep mysql
[root@rhel131 mysql]# ps -ef | grep cnf
root      4416  4362  0 10:38 pts/1    00:00:00 grep cnf
[root@rhel131 mysql]# ls
bin      docs            lib     my-new.cnf  scripts    support-files
COPYING  include         man     mysql-test  share
data     INSTALL-BINARY  my.cnf  README      sql-bench
[root@rhel131 mysql]# mysqladmin shutdown

本机已有一个mysql实例，安装的目录为=/usr/local/mysql/data，端口号为默变的3306，现打算安装另外两个实例。

 

[root@rhel131 mysql]# mkdir data2
[root@rhel131 mysql]# mkdir data3

[root@rhel131 data2]# chown -R mysql.mysql /usr/local/mysql/data2
[root@rhel131 data2]# chown -R mysql.mysql /usr/local/mysql/data3

[root@rhel131 mysql]# mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data2 --user=mysql

[root@rhel131 mysql]# mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data3--user=mysql

 

这时要确认一下data2/mysql下是否已生成了一些文件

[root@rhel131 mysql]# pwd
/usr/local/mysql/data3/mysql

[root@rhel131 mysql]# ls | wc -l
79

 

编辑my.cnf文件，添加新实例的配置

 

在/etc/my.cnf中添加下面的信息：

 

[root@rhel131 mysql]# cat /etc/my.cnf

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = test
password = test

[mysqld3306]

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
server-id = 1
socket = /tmp/mysql3306.sock
pid-file = /tmp/mysql3306.pid
#skip-locking
#skip-name-resolve
back_log = 384
key_buffer_size = 16K
max_allowed_packet = 1M
max_connections = 768
max_connect_errors = 10000000
wait_timeout = 10
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld3307]

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data2
port = 3307
server-id = 1
socket = /tmp/mysql3307.sock
pid-file = /tmp/mysql3307.pid
#skip-locking
#skip-name-resolve
back_log = 384
key_buffer_size = 16K
max_allowed_packet = 1M
max_connections = 768
max_connect_errors = 10000000
wait_timeout = 10
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld3308]

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data3
port = 3308
server-id = 1
socket = /tmp/mysql3308.sock
pid-file = /tmp/mysql3308.pid
#skip-locking
#skip-name-resolve
back_log = 384
key_buffer_size = 16K
max_allowed_packet = 1M
max_connections = 768
max_connect_errors = 10000000
wait_timeout = 10
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[client]
port    = 3306
socket  = /tmp/mysql.sock

 

下面来试着启动新的实例：

 

[root@rhel131 mysql]# mysqld_multi --defaults-file=/etc/my.cnf start 3307

[root@rhel131 mysql]# ps -ef | grep 3307
root      2488     1  0 12:10 pts/1    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data2 --port=3307 --server-id=1 --socket=/tmp/mysql3307.sock --pid-file=/tmp/mysql3307.pid --back_log=384 --key_buffer_size=16K --max_allowed_packet=1M --max_connections=768 --max_connect_errors=10000000 --wait_timeout=10 --table_open_cache=4 --sort_buffer_size=64K --read_buffer_size=256K --read_rnd_buffer_size=256K --net_buffer_length=2K --thread_stack=128K --sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
mysql     2852  2488  0 12:10 pts/1    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data2 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --server-id=1 --back-log=384 --key-buffer-size=16K --max-allowed-packet=1M --max-connections=768 --max-connect-errors=10000000 --wait-timeout=10 --table-open-cache=4 --sort-buffer-size=64K --read-buffer-size=256K --read-rnd-buffer-size=256K --net-buffer-length=2K --thread-stack=128K --sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES --log-error=/usr/local/mysql/data2/rhel131.err --pid-file=/tmp/mysql3307.pid --socket=/tmp/mysql3307.sock --port=3307
root      3676  4362  0 12:12 pts/1    00:00:00 grep 3307

[root@rhel131 mysql]# mysqld_multi --defaults-file=/etc/my.cnf start 3308

[root@rhel131 mysql]# ps -ef | grep 3308
root      2888     1  0 12:10 pts/1    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data3 --port=3308 --server-id=1 --socket=/tmp/mysql3308.sock --pid-file=/tmp/mysql3308.pid --back_log=384 --key_buffer_size=16K --max_allowed_packet=1M --max_connections=768 --max_connect_errors=10000000 --wait_timeout=10 --table_open_cache=4 --sort_buffer_size=64K --read_buffer_size=256K --read_rnd_buffer_size=256K --net_buffer_length=2K --thread_stack=128K --sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
mysql     3246  2888  0 12:10 pts/1    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data3 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --server-id=1 --back-log=384 --key-buffer-size=16K --max-allowed-packet=1M --max-connections=768 --max-connect-errors=10000000 --wait-timeout=10 --table-open-cache=4 --sort-buffer-size=64K --read-buffer-size=256K --read-rnd-buffer-size=256K --net-buffer-length=2K --thread-stack=128K --sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES --log-error=/usr/local/mysql/data3/rhel131.err --pid-file=/tmp/mysql3308.pid --socket=/tmp/mysql3308.sock --port=3308
root      3678  4362  0 12:12 pts/1    00:00:00 grep 3308

 

[root@rhel131 mysql]# mysqld_multi --defaults-file=/etc/my.cnf start 3306

[root@rhel131 mysql]# ps -ef | grep 3306
root      3284     1  0 12:10 pts/1    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --port=3306 --server-id=1 --socket=/tmp/mysql3306.sock --pid-file=/tmp/mysql3306.pid --back_log=384 --key_buffer_size=16K --max_allowed_packet=1M --max_connections=768 --max_connect_errors=10000000 --wait_timeout=10 --table_open_cache=4 --sort_buffer_size=64K --read_buffer_size=256K --read_rnd_buffer_size=256K --net_buffer_length=2K --thread_stack=128K --sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
mysql     3645  3284  0 12:10 pts/1    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --server-id=1 --back-log=384 --key-buffer-size=16K --max-allowed-packet=1M --max-connections=768 --max-connect-errors=10000000 --wait-timeout=10 --table-open-cache=4 --sort-buffer-size=64K --read-buffer-size=256K --read-rnd-buffer-size=256K --net-buffer-length=2K --thread-stack=128K --sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES --log-error=/usr/local/mysql/data/rhel131.err --pid-file=/tmp/mysql3306.pid --socket=/tmp/mysql3306.sock --port=3306
root      3680  4362  0 12:13 pts/1    00:00:00 grep 3306

这里可看查看到三个实例的sock和pid文件：

[root@rhel131 mysql]# ll /tmp
total 12
srwxr-xr-x  1 root  root  0 Sep 26 14:33 mapping-root
-rw-rw----  1 mysql mysql 5 Oct 17 12:10 mysql3306.pid
srwxrwxrwx  1 mysql mysql 0 Oct 17 12:10 mysql3306.sock
-rw-rw----  1 mysql mysql 5 Oct 17 12:10 mysql3307.pid
srwxrwxrwx  1 mysql mysql 0 Oct 17 12:10 mysql3307.sock
-rw-rw----  1 mysql mysql 5 Oct 17 12:10 mysql3308.pid
srwxrwxrwx  1 mysql mysql 0 Oct 17 12:10 mysql3308.sock

要想关闭这些实例，则mysql数据库下必须要有test用户，在my.cnf里指定需要test用户来关闭的。

 

下面先试一下，没有test用户时能否关闭：

[root@rhel131 mysql]# [root@rhel131 mysql]# mysqld_multi --defaults-file=/etc/my.cnfstop 3307

-bash: [root@rhel131: command not found
[root@rhel131 mysql]#  mysqld_multi --defaults-file=/etc/my.cnf stop 3307        [root@rhel131 mysql]# ps -ef | grep 3307
root      2488     1  0 12:10 pts/1    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data2 --port=3307 --server-id=1 --socket=/tmp/mysql3307.sock --pid-file=/tmp/mysql3307.pid --back_log=384 --key_buffer_size=16K --max_allowed_packet=1M --max_connections=768 --max_connect_errors=10000000 --wait_timeout=10 --table_open_cache=4 --sort_buffer_size=64K --read_buffer_size=256K --read_rnd_buffer_size=256K --net_buffer_length=2K --thread_stack=128K --sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
mysql     2852  2488  0 12:10 pts/1    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data2 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --server-id=1 --back-log=384 --key-buffer-size=16K --max-allowed-packet=1M --max-connections=768 --max-connect-errors=10000000 --wait-timeout=10 --table-open-cache=4 --sort-buffer-size=64K --read-buffer-size=256K --read-rnd-buffer-size=256K --net-buffer-length=2K --thread-stack=128K --sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES --log-error=/usr/local/mysql/data2/rhel131.err --pid-file=/tmp/mysql3307.pid --socket=/tmp/mysql3307.sock --port=3307
root      3695  4362  0 12:17 pts/1    00:00:00 grep 3307

 

从日志文件/usr/local/mysql/share/mysqld_multi.log可以看出错误信息：

Stopping MySQL servers

Warning: Using a password on the command line interface can be insecure.
^G/usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'test'@'localhost' (using password: YES)'
mysqld_multi log file version 2.16; run: Thu Oct 17 13:44:37 2013

 

下面就来新建test用户

 

[root@rhel131 tmp]# mysql -S ./mysql3306.sock

mysql> grant shutdown on *.* to 'test'@'localhost' identified by 'test' with grant option;
Query OK, 0 rows affected (0.03 sec)

 

最后再来关闭试试：

[root@rhel131 share]#  mysqld_multi --defaults-file=/etc/my.cnf stop 3306
[root@rhel131 share]# ps -ef | grep 3306
root      3786  4362  0 13:54 pts/1    00:00:00 grep 3306

成功了！