mysql-5.1.68linux下多資料啟動

tolilong發表於2013-02-25
MySqlLinux
Mysql多資料庫啟動方法[@more@]
Mysqld_multi
Mysqld_multi is designed to manage several mysqld processes that listen for connection on different unix socket files and tcp/ip ports. It can start or stop servers,or report their current status,The mysql instance manager is an alternative means of managing multiple servers
[root@oracle mysql5168]# mkdir data2
[root@oracle mysql5168]# mkdir data3
[root@oracle mysql5168]# chown -R mysql:mysql data
[root@oracle mysql5168]# chown -R mysql:mysql data2
[root@oracle mysql5168]# chown -R mysql:mysql data3
[root@oracle scripts]# pwd
/u02/mysql-5.1.68/scripts
[root@oracle scripts]# ./mysql_install_db --basedir=/u02/mysql5168/ --datadir=/u02/mysql5168/data2/ --user=mysql
[root@oracle scripts]# ./mysql_install_db --basedir=/u02/mysql5168/ --datadir=/u02/mysql5168/data3/ --user=mysql
備份原來的配置檔案
[root@oracle u02]# cp my.cnf my.cnf.bak
編輯 my.cnf檔案，編輯後的檔案為:
[root@oracle u02]# more my.cnf
[mysqld_multi]
mysqld =/u02/mysql5168/bin/mysqld_safe #for start mysql database
mysqladmin=/u02/mysql5168/bin/mysqladmin #for stop mysql database
user =test #the user can shutdown mysql database,the user is in mysql database
password =test
[mysqld3306]
basedir = /u02/mysql5168
datadir = /u02/mysql5168/data
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /tmp/mysql3306.pid
skip-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
server-id = 1
[mysqld3307]
basedir = /u02/mysql5168
datadir = /u02/mysql5168/data2
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /tmp/mysql3307.pid
skip-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
server-id = 1
[mysqld3308]
basedir = /u02/mysql5168
datadir = /u02/mysql5168/data3
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /tmp/mysql3308.pid
skip-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
server-id = 1
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
[mysqlhotcopy]
interactive-timeout
啟動 mysql 資料庫
[root@oracle mysql5168]# ./bin/mysqld_multi --defaults-file=/u02/my.cnf start 3308
[root@oracle mysql5168]# ./bin/mysqld_multi --defaults-file=/u02/my.cnf start 3307
[root@oracle mysql5168]# ./bin/mysqld_multi --defaults-file=/u02/my.cnf start 3306
啟動之後檢視狀態 :
[root@oracle bin]# ps -ef | grep mysql3306
root 2945 1 0 20:03 pts/1 00:00:00 /bin/sh /u02/mysql5168/bin/mysqld_safe --basedir=/u02/mysql5168 --datadir=/u02/mysql5168/data --port=3306 --socket=/tmp/mysql3306.sock --pid-file=/tmp/mysql3306.pid --skip-locking --key_buffer_size=16K --max_allowed_packet=1M --table_open_cache=4 --sort_buffer_size=64K --read_buffer_size=256K --read_rnd_buffer_size=256K --net_buffer_length=2K --thread_stack=128K --server-id=1
mysql 3089 2945 0 20:03 pts/1 00:00:00 /u02/mysql5168/libexec/mysqld --basedir=/u02/mysql5168 --datadir=/u02/mysql5168/data --user=mysql --skip-locking --key_buffer_size=16K --max_allowed_packet=1M --table_open_cache=4 --sort_buffer_size=64K --read_buffer_size=256K --read_rnd_buffer_size=256K --net_buffer_length=2K --thread_stack=128K --server-id=1 --log-error=/u02/mysql5168/data/oracle.err --pid-file=/tmp/mysql3306.pid --socket=/tmp/mysql3306.sock --port=3306
root 3432 2871 0 20:03 pts/1 00:00:00 grep mysql3306
連線 mysql資料庫
[root@oracle tmp]# mysql -S mysql3307.sock
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.1.68bruce Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.11 sec)
關閉 mysql資料庫
[root@oracle bin]# ./mysqld_multi –help à列出所有使用方法
[root@oracle ~]# cd /u02/mysql5168/share/
[root@oracle share]# ll
total 20
drwxr-xr-x 2 root root 4096 Feb 24 06:28 aclocal
drwxr-xr-x 2 root root 4096 Feb 24 06:27 info
drwxr-xr-x 4 root root 4096 Feb 24 06:27 man
drwxr-xr-x 26 root root 4096 Feb 24 06:28 mysql
-rw-r--r-- 1 root root 1720 Feb 24 20:03 mysqld_multi.log
Mysqld_multi.log是相關日誌
[root@oracle bin]# ./mysqld_multi --defaults-file=/u02/my.cnf stop 3306
使用這條命令關閉 3306資料庫的時候，mysqld_multi.log中出現如下錯誤 :
Stopping MySQL servers
/u02/mysql5168/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'test'@'localhost' (using password: YES)'
其錯誤原因是 mysql中沒有test使用者
[root@oracle tmp]# mysql -S mysql3306.sock
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.1.68bruce Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> drop user test;
Query OK, 0 rows affected (0.01 sec)
mysql> grant shutdown on *.* to test@localhost identified by 'test' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for test@localhost;
+----------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost |
+----------------------------------------------------------------------------------------------------------------------------------+
| GRANT SHUTDOWN ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
[root@oracle tmp]# /u02/mysql5168/bin/mysqld_multi --defaults-file=/u02/my.cnf stop 3306
[root@oracle tmp]# ps -ef | grep mysql3306
root 4050 1 0 22:01 pts/1 00:00:00 /u02/mysql5168/bin/mysqladmin -u test -px xx --port=3306 --socket=/tmp/mysql3306.sock shutdown
root 4055 2871 0 22:01 pts/1 00:00:00 grep mysql3306
[root@oracle tmp]# mysql -S mysql3306.sock
ERROR 2002 (HY000): Can't connect to local MySQL server through socket 'mysql3306.sock' (2)
之後 mysql資料庫便關閉了
其他埠的 mysql資料庫也可如此關閉
[root@oracle tmp]# /u02/mysql5168/bin/mysqld_multi --defaults-file=/u02/my.cnf stop 3307
[root@oracle tmp]# /u02/mysql5168/bin/mysqld_multi --defaults-file=/u02/my.cnf stop 3308
也可以一次性開啟所有的資料庫
[root@oracle tmp]# /u02/mysql5168/bin/mysqld_multi --defaults-file=/u02/my.cnf start 3306-3308
也可以一次性關閉所有的資料庫
[root@oracle tmp]# /u02/mysql5168/bin/mysqld_multi --defaults-file=/u02/my.cnf stop 3306-3308

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24237320/viewspace-1060240/，如需轉載，請註明出處，否則將追究法律責任。

相關文章