当前位置: 首页 > 图文教程 > 数据库 > MYSQL > 用实例管理器轻松管理多个MySQL实例

MYSQL
MySQL 实用命令
Mysql 默认字符集设置方法（免安装版）
MySQL 数据库对服务器端光标的限制
MySQL UPDATE更新语句精解
Table ‘xxx’ is marked as crashed and should be repaired 错误解决方法参考
mysql 读写分离（基础篇）
mysql 读写分离（实战篇）
processlist命令 查看mysql 线程
MySQL 数据类型 大全
MySQL 密码设置
MYSQL代码 定期备份Mysql数据库
汇总整理MYSQL相关操作命令
MySQL 数据库两台主机同步实战(linux)
MSSQL 添加字段说明
MySQL 查询某个字段不重复的所有记录
mysql 设置默认的时间值
mysqlreport显示Com_中change_db占用比例高的问题的解决方法
解决mysql不能插入中文Incorrect string value
mysql从执行.sql文件时处理\n换行的问题
mysql 主从服务器的简单配置

No. « 8 9 10 11 »

技术文章搜索

关键字

MYSQL 中的 用实例管理器轻松管理多个MySQL实例

出处:互联网   整理: 软晨网（RuanChen.com）   发布: 2009-10-17   浏览: 130 ::
收藏到网摘: n/a

一、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 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

总结：

MySQL数据库的实例管理器对于多个MySQL实例的管理是很方便的,但它本身存在两个不足：

1、不能直接进行数据库的SQL管理命令。

2、一定要确保IM进程不会突然挂掉。