mysqlmanager实例管理器总结

好久没有写文章了，今天来看看MYSQL的实例管理器（MYSQLMANAGER）。
一、简单介绍：
1、MySQL实例管理器(IM)是通过TCP/IP端口运行的后台程序，用来监视和管理MySQL数据库服务器实例。（如果你之前用过MYSQLD_MULTI就很清楚了。）
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]root@localhost.loca[/email]ldomain' 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 Settings\Administrator>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
Bye

总结：MYSQL的实例管理器对于多个MYSQL实例的管理还是很方便的。
不过有两个缺点
1、不能直接进行数据库的SQL管理命令。
2、一定要保证IM进程不挂掉。