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

MYSQL
MySQL忘记密码恢复密码的实现方法
mysql 时间转换函数的使用方法
使用mysql的disctinct group by查询不重复记录
mysql备份恢复mysqldump.exe几个常用用例
超详细mysql left join,right join,inner join用法分析
mysql 5.0.67最新版替代MySQL 5.0.51b版本官方下载
比较详细的MySQL字段类型说明
mysql 记录不存在时插入 记录存在则更新的实现方法
MYSQL基础之连接MYSQL、修改密码、添加用户
mysql数据库优化必会的几个参数中文解释
mysql中文排序注意事项与实现方法
MySQL 5.0触发器参考教程
MySQL5创建存储过程的示例
MYSQL5 masterslave数据同步配置方法
mysql数据库导出xml的实现方法
MySql增加用户、授权、修改密码等语句
Mysql默认设置的危险性分析
用MySQL创建数据库和数据库表代码
Mysql如何避免全表扫描的方法
mysql的校对规则引起的问题分析

No. « 6 7 8 9 »

技术文章搜索

关键字

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

出处:互联网   整理: 软晨网（RuanChen.com）   发布: 2009-10-17   浏览: 103 ::
收藏到网摘: 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进程不会突然挂掉。