一、MySQL數據庫的實例管理器概述：
1、MySQL數據庫的實例管理器(IM)是通過TCP/IP端口運行的後台程序，用來監視和管理MySQL數據庫服務器實例。
2、假如IM掛了，則所有的實例都會掛掉；假如實例掛了，IM會嘗試重新來啟動它。
3、IM讀取配置文件比如MY.CNF的[manager]段。
4、本文中的示例依據Linux環境下試驗。
二、配置說明:
1、配置文件如下：
[manager]
user=MySQL
default-mysqld-path = /usr/local/mysql/bin/MySQLd
socket=/tmp/manager.sock
pid-file=/tmp/manager.pid
passWord-file = /etc/MySQLmanager.passwd
monitoring-interval = 2
port = 1999
bind-address = 192.168.0.231
log = /usr/local/mysql/bin/MySQLmanager.log
run-as-service = true
[MySQLd1]
...
[MySQLd2]
...
這個有兩個配置實例，具體就不說了。見我的安裝多個實例的文章。
具體含義查看MySQLmanager --help
2、密碼文件
IM將用戶信息保存到密碼文件中。密碼文件的默認位置為/etc/MySQLmanager.passwd。
密碼應類似於：
petr:*35110DC9B4D8140F5DE667E28C72DD2597B5C848
我的MySQLmanager.passwd內容
user_all:*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
3、啟動IM
[root@localhost tmp]# /usr/local/mysql/bin/MySQLmanager
WARNING: This program is deprecated and will be removed in 6.0.
[2483/3086632640] [08/04/24 14:24:50] [INFO] IM: started.
[2483/3086632640] [08/04/24 14:24:50] [INFO] Loading config file 'my.cnf'...
[2483/3086632640] [08/04/24 14:24:50] [INFO] Manager: initializing...
[2483/3086632640] [08/04/24 14:24:50] [INFO] Manager: detected threads model: POSIX threads.
[2483/3086632640] [08/04/24 14:24:50] [INFO] Loading the passWord database...
[2483/3086632640] [08/04/24 14:24:50] [INFO] Loaded user 'user_all'.
[2483/3086632640] [08/04/24 14:24:50] [INFO] The passWord database loaded successfully.
[2483/3086632640] [08/04/24 14:24:50] [INFO] Manager: pid file (/tmp/manager.pid) created.
[2483/3086632640] [08/04/24 14:24:50] [INFO] mysqld instance 'MySQLd1' has been added successfully.
[2483/3086632640] [08/04/24 14:24:50] [INFO] mysqld instance 'MySQLd2' has been added successfully.
[2483/3076139920] [08/04/24 14:24:50] [INFO] Guardian: started.
[2483/3076139920] [08/04/24 14:24:50] [INFO] Guardian: starting 'MySQLd1'...
[2483/3076058000] [08/04/24 14:24:50] [INFO] Instance 'MySQLd1': Monitor: started.
[2483/3076058000] [08/04/24 14:24:50] [INFO] Instance 'mysqld1': Monitor: starting MySQLd...
[2483/3076139920] [08/04/24 14:24:50] [INFO] Guardian: starting 'MySQLd2'...
[2483/3076058000] [08/04/24 14:24:50] [INFO] Instance 'mysqld1': Monitor: waiting for MySQLd to stop...
[2483/3075894160] [08/04/24 14:24:50] [INFO] Instance 'MySQLd2': Monitor: started.
[2483/3075894160] [08/04/24 14:24:50] [INFO] Instance 'mysqld2': Monitor: starting MySQLd...
[2483/3075894160] [08/04/24 14:24:50] [INFO] Instance 'mysqld2': Monitor: waiting for MySQLd to stop...
[2483/3086632640] [08/04/24 14:24:50] [INFO] Manager: started.
[2483/3075976080] [08/04/24 14:24:50] [INFO] Listener: started.
[2483/3075976080] [08/04/24 14:24:50] [INFO] Listener: accepting connections on ip socket (port: 1999)...
[2483/3075976080] [08/04/24 14:24:50] [INFO] Listener: accepting connections on unix socket '/tmp/manager.sock'...
...
InnoDB: than specifIEd in the .cnf file 0 5242880 bytes!
080424 14:24:50 InnoDB: Started; log sequence number 0 46409
080424 14:24:50 [Note] Event Scheduler: Loaded 0 events
080424 14:24:50 [Note] /usr/local/mysql/bin/MySQLd: ready for connections.
Version: '5.1.23a-maria-alpha-log' socket: '/tmp/mysql1.sock' port: 3306 MySQL Community Server [Maria] (GPL)
080424 14:24:50 [Warning] 'user' entry '[email protected]' ignored in --skip-name-resolve mode.
080424 14:24:50 [Warning] 'user' entry '@localhost.localdomain' ignored in --skip-name-resolve mode.
080424 14:24:50 [Note] Event Scheduler: Loaded 0 events
080424 14:24:50 [Note] /usr/local/mysql/bin/MySQLd: ready for connections.
Version: '5.1.23a-maria-alpha-log' socket: '/tmp/mysql2.sock' port: 3309 MySQL Community Server [Maria] (GPL)
[2483/3076139920] [08/04/24 14:24:52] [INFO] Guardian: 'MySQLd1' is running, set state to STARTED.
4、連接IM
[root@localhost ~]# MySQL -uuser_all -p -S/tmp/manager.sock -P1999
三、用IM來管理MySQL數據庫
1、顯示實例的狀態和版本信息
[root@localhost ~]# MySQL -uuser_all -p -S/tmp/manager.sock -P1999
Enter passWord:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 1.0-beta
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
MySQL> show instances;
+---------------+--------+
| instance_name | state |
+---------------+--------+
| MySQLd1 | online |
| MySQLd2 | online |
+---------------+--------+
2 rows in set (0.00 sec)
關閉實例1
mysql> stop instance MySQLd1;
Query OK, 0 rows affected (0.30 sec)
MySQL> show instances;
+---------------+---------+
| instance_name | state |
+---------------+---------+
| MySQLd1 | offline |
| MySQLd2 | online |
+---------------+---------+
2 rows in set (0.00 sec)
開啟實例1
mysql> start instance MySQLd1;
Query OK, 0 rows affected (0.00 sec)
Instance started
MySQL> show instances;
+---------------+--------+
| instance_name | state |
+---------------+--------+
| MySQLd1 | online |
| MySQLd2 | online |
+---------------+--------+
2 rows in set (0.00 sec)
查看實例的版本信息
mysql> show instance status MySQLd2;
+---------------+--------+----------------+
| instance_name | state | version_number | version | MySQLd_compatible |
+---------------+--------+----------------+
| mysqld2 | online | 5.1.23 | 5.1.23a-maria-alpha for redhat-Linux-gnu on i686 (MySQL Community Server [Maria] (GPL))
| no |
+---------------+--------+----------------+
1 row in set (0.00 sec)
也可以顯示實例的選項信息
mysql> show instance options MySQLd1;
+-----------------------+-----------------------------------+
| option_name | value |
+-----------------------+-----------------------------------+
| instance_name | MySQLd1 |
| basedir | /usr/local/MySQL |
| datadir | /usr/local/MySQL/data |
| user | MySQL |
| default-character-set | utf8 |
| port | 3306 |
| socket | /tmp/MySQL1.sock |
| skip-locking | |
| skip-name-resolve | |
| key_buffer | 126M |
| max_allowed_packet | 2M |
| table_cache | 512 |
| sort_buffer_size | 2M |
| read_buffer_size | 2M |
| read_rnd_buffer_size | 4M |
| net_buffer_length | 2K |
| thread_stack | 64K |
| log-bin | MySQL.log |
| expire_logs_days | 5 |
| wait_timeout | 20 |
| pid-file | MySQLd1-localhost.localdomain.pid |
+-----------------------+-----------------------------------+
21 rows in set (0.00 sec)
也可以查詢實例的日志相關信,不再贅述。
我們可以發現，管理實例其實非常方便。
2、管理用戶
(1)、添加管理用戶
[root@localhost ~]# /usr/local/mysql/bin/MySQLmanager --add-user
WARNING: This program is deprecated and will be removed in 6.0.
[3046/3086816960] [08/04/24 14:33:13] [INFO] IM: started.
[3046/3086816960] [08/04/24 14:33:13] [INFO] Loading config file 'my.cnf'...
Enter user name: shit_all
[3046/3086816960] [08/04/24 14:33:18] [INFO] Loading the passWord database...
[3046/3086816960] [08/04/24 14:33:18] [INFO] Loaded user 'user_all'.
[3046/3086816960] [08/04/24 14:33:18] [INFO] The passWord database loaded successfully.
Enter passWord:
Re-type passWord:
[3046/3086816960] [08/04/24 14:33:23] [INFO] IM: finished.
[root@localhost ~]# cat /etc/MySQLmanager.passwd
user_all:*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
shit_all:*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
注意:必須重新啟動IM才能生效。
[root@localhost ~]# MySQL -ushit_all -p -S/tmp/manager.sock -P1999
Enter passWord:
ERROR 1045 (28000): Access denIEd. Bad username/passWord pair
（2）、修改用戶密碼
[root@localhost ~]# /usr/local/mysql/bin/MySQLmanager --edit-user
WARNING: This program is deprecated and will be removed in 6.0.
[3214/3086845632] [08/04/24 14:35:15] [INFO] IM: started.
[3214/3086845632] [08/04/24 14:35:15] [INFO] Loading config file 'my.cnf'...
Enter user name: shit_all
[3214/3086845632] [08/04/24 14:35:19] [INFO] Loading the passWord database...
[3214/3086845632] [08/04/24 14:35:19] [INFO] Loaded user 'user_all'.
[3214/3086845632] [08/04/24 14:35:19] [INFO] Loaded user 'shit_all'.
[3214/3086845632] [08/04/24 14:35:19] [INFO] The passWord database loaded successfully.
Enter passWord:
Re-type passWord:
[3214/3086845632] [08/04/24 14:35:24] [INFO] IM: finished.
(3)、刪除用戶
[root@localhost ~]# /usr/local/mysql/bin/MySQLmanager --drop-user
WARNING: This program is deprecated and will be removed in 6.0.
[3338/3086501568] [08/04/24 14:36:42] [INFO] IM: started.
[3338/3086501568] [08/04/24 14:36:42] [INFO] Loading config file 'my.cnf'...
Enter user name: shit_all
[3338/3086501568] [08/04/24 14:36:45] [INFO] Loading the passWord database...
[3338/3086501568] [08/04/24 14:36:45] [INFO] Loaded user 'user_all'.
[3338/3086501568] [08/04/24 14:36:45] [INFO] Loaded user 'shit_all'.
[3338/3086501568] [08/04/24 14:36:45] [INFO] The passWord database loaded successfully.
[3338/3086501568] [08/04/24 14:36:45] [INFO] IM: finished.
(4)、列出當前管理用戶
[root@localhost ~]# /usr/local/mysql/bin/MySQLmanager --list-user
WARNING: This program is deprecated and will be removed in 6.0.
[3366/3086087872] [08/04/24 14:37:07] [INFO] IM: started.
[3366/3086087872] [08/04/24 14:37:07] [INFO] Loading config file 'my.cnf'...
[3366/3086087872] [08/04/24 14:37:07] [INFO] Loading the passWord database...
[3366/3086087872] [08/04/24 14:37:07] [INFO] Loaded user 'user_all'.
[3366/3086087872] [08/04/24 14:37:07] [INFO] The passWord database loaded successfully.
user_all
[3366/3086087872] [08/04/24 14:37:07] [INFO] IM: finished.
[root@localhost ~]#
四、遠程管理
C:Documents and SettingsAdministrator>MySQL -uuser_all -p -P1999 -h192.168.0.2
31
Enter passWord: ******
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 3
Server version: 1.0-beta
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
MySQL> show instances;
+---------------+--------+
| instance_name | state |
+---------------+--------+
| MySQLd1 | online |
| MySQLd2 | online |
+---------------+--------+
2 rows in set (0.00 sec)
MySQL> quit
總結：
MySQL數據庫的實例管理器對於多個MySQL實例的管理是很方便的,但它本身存在兩個不足：
1、不能直接進行數據庫的SQL管理命令。
2、一定要確保IM進程不會突然掛掉。
