先检查本地的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.cnf stop 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
成功了!