下载中心 | 网站地图 | 站内搜索 | 加入收藏

安恒公司 / 技术文章 / 安恒公司网管员手记 / *个myaql服务器运行多个数据库实例[转载]

2007-04-28 yejr  阅:    下页:
*个myaql服务器运行多个数据库实例[转载]

[MySQL 5.1 体验]MySQL 实例管理器 mysqlmanager 初试

imysql.cn 2007年06月20日08:23 作者:yejr 

MySQL实例管理器(IM)是通过TCP/IP端口运行的后台程序,用来监视和管理MySQL数据库服务器实例。MySQL实例管理器适合Unix-类操作系统和Windows。

可以在mysqld_safe脚本使用MySQL实例管理器来启动和停止MySQL服务器,甚至可以从*个远程主机。MySQL实例管理器还执行mysqld_multi脚本的功能(和大多数语法)。

*mysqlmanager 配置文件
**常见的 mysqlmanager 配置文件如下:

[manager]
port = 1999
socket = /tmp/manager.sock
pid-file= /tmp/manager.pid
run-as-service = true
monitoring-interval = 10
default-mysqld-path = /usr/local/mysql/bin/mysqld
password-file = /etc/mysqlmanager.passwd
log = /usr/local/mysql/bin/mysqld/mysqlmanager.log

[mysqld1]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data1
general-log = true
log-error = /usr/local/mysql/data1/error.log
pid-file = /usr/local/mysql/data1/mysql.pid
port = 13306
socket = /tmp/mysql13306.sock

[mysqld2]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data2
general-log = true
log-error = /usr/local/mysql/data2/error.log
pid-file = /usr/local/mysql/data2/mysql.pid
port = 13307
socket = /tmp/mysql13307.sock

[mysqld3]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data3
general-log = true
log-error = /usr/local/mysql/data3/error.log
pid-file = /usr/local/mysql/data3/mysql.pid
port = 13308
socket = /tmp/mysql13308.sock

**,第*个区间是 [manager],它用于指定 mysqlmanager 程序启动时的选项。其中 run-as-service 选项是指 mysqlmanager 以后台守护进程方式运行, default-mysqld-path 选项则指定了 mysqld 程序所在的位置。 password-file 选项则指定了连接到 mysqlmanager 的用户密码文件所在位置。

接下来,后面的 [mysqld1], [mysqld2], [mysqld3] 区间则分别设定了3个 MySQL 运行实例。这些参数就是常见的设置了,在这里不再细说。

二、 mysqlmanager 用户管理
1、添加用户

[root@localhost]# /usr/local/mysql/bin/mysqlmanager --add-user --username=yejr
[20910/136318976] [07/06/19 11:03:01] [INFO] IM: started.
[20910/136318976] [07/06/19 11:03:01] [INFO] Loading config file 'my.cnf'...
[20910/136318976] [07/06/19 11:03:01] [INFO] Loading the password database...
[20910/136318976] [07/06/19 11:03:01] [INFO] The password database loaded successfully.
Enter password:
Re-type password:
[20910/136318976] [07/06/19 11:03:04] [INFO] IM: finished.
[root@localhost]#
[root@localhost]# cat /etc/mysqlmanager.passwd
yejr:*0E558D9FBD602CDA0C9F3F7A8BC5F4F53401CD7C

2、修改密码

[root@localhost]# /usr/local/mysql/bin/mysqlmanager --edit-user --username=yejr
[20943/136318976] [07/06/19 11:05:01] [INFO] IM: started.
[20943/136318976] [07/06/19 11:05:01] [INFO] Loading config file 'my.cnf'...
[20943/136318976] [07/06/19 11:05:01] [INFO] Loading the password database...
[20943/136318976] [07/06/19 11:05:01] [INFO] Loaded user 'yejr'.
[20943/136318976] [07/06/19 11:05:01] [INFO] The password database loaded successfully.
Enter password:
Re-type password:
[20943/136318976] [07/06/19 11:05:05] [INFO] IM: finished.
[root@localhost]#
[root@localhost]# cat /etc/mysqlmanager.passwd
yejr:*9DB91006131E32B22135599033C6A9C196EC3C6B

3、删除用户

[root@localhost]# /usr/local/mysql/bin/mysqlmanager --drop-user --username=yejr
[20967/136318976] [07/06/19 11:06:30] [INFO] IM: started.
[20967/136318976] [07/06/19 11:06:30] [INFO] Loading config file 'my.cnf'...
[20967/136318976] [07/06/19 11:06:30] [INFO] Loading the password database...
[20967/136318976] [07/06/19 11:06:30] [INFO] Loaded user 'yejr'.
[20967/136318976] [07/06/19 11:06:30] [INFO] The password database loaded successfully.
[20967/136318976] [07/06/19 11:06:30] [INFO] IM: finished.

默认情况下,mysqlmanager 的密码文件是 /etc/mysqlmanager.passwd,如果你的密码文件不是放在这里,那么就需要自行指定,增加*个参数 --password-file=path_to_passwd_file,让 mysqlmanager 根据指定的位置去找到正确的密码文件。如:

[root@localhost]# /usr/local/mysql/bin/mysqlmanager --password-file=/usr/local/mysql/.mysqlmanager.passwd --add-user --username=yejr

注意:修改或者删除用户后,只有重启 mysqlmanager 才能生效,而不是立刻生效。

三、 mysqlmanager 管理
1、mysqlmanager 启动

[root@localhost]# /usr/local/mysql/bin/mysqlmanager --defaults-file=/usr/local/mysql/my.cnf
[21032/136318976] [07/06/19 11:11:03] [INFO] IM: started.
[21032/136318976] [07/06/19 11:11:03] [INFO] Loading config file '/usr/local/mysql/my.cnf'...
[21032/136318976] [07/06/19 11:11:03] [INFO] Angel: started.
[21032/136318976] [07/06/19 11:11:03] [INFO] Angel: opening log file '/usr/local/mysql/bin/mysqld/mysqlmanager.log'...
[21032/136318976] [07/06/19 11:11:03] [INFO] Angel: daemonizing...
[21032/136318976] [07/06/19 11:11:03] [INFO] Angel: exiting from the original process...
[21032/136318976] [07/06/19 11:11:03] [INFO] IM: finished.
[21033/136318976] [07/06/19 11:11:03] [INFO] Angel: preparing standard streams.

在启动 mysqlmanager 的同时,也会把它管理的所有 MySQL实例 全部启动。

[root@localhost]# mysql -uyejr -P1999 -hlocalhost -S/tmp/manager.sock -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 1.0-beta

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

(yejr:localhost:)(none)> SHOW INSTANCES;
+---------------+--------+
| instance_name | state |
+---------------+--------+
| mysqld1 | online |
| mysqld2 | online |
| mysqld3 | online |
+---------------+--------+

2、mysqlmanager 状态查看

(yejr:localhost:)(none)> SHOW INSTANCE STATUS mysqld1\G
*************************** 1. row ***************************
instance_name: mysqld1
state: online
version_number: 5.1.19
version: 5.1.19-beta for unknown-freebsd6.0 on i386 (MySQL Community Server (GPL))
mysqld_compatible: no

(yejr:localhost:)(none)> SHOW INSTANCE OPTIONS mysqld1;
+---------------+----------------------------------------------+
| option_name | value |
+---------------+----------------------------------------------+
| instance_name | mysqld1 |
| basedir | /usr/local/mysql |
| datadir | /usr/local/mysql/data1 |
| general-log | true |
| log-error | /usr/local/mysql/data1/error.log |
| pid-file | /usr/local/mysql/data1/mysql.pid |
| port | 13306 |
| socket | /tmp/mysql13306.sock |
+---------------+----------------------------------------------+

(yejr:localhost:)(none)> SHOW mysqld1 LOG FILES;
+-----------+----------------------------------+-----------+
| Logfile | Path | File size |
+-----------+----------------------------------+-----------+
| ERROR LOG | /usr/local/mysql/data1/error.log | 2976 |
+-----------+----------------------------------+-----------+

(yejr:localhost:)(none)> SHOW mysqld13306 LOG ERROR 2976\G
*************************** 1. row ***************************
070619 11:17:57 [Warning] Server variable data_file_path of plugin InnoDB was forced to be read-only: string variable
without update_func and PLUGIN_VAR_MEMALLOC flag
070619 11:17:57 [Warning] Server variable data_home_dir of plugin InnoDB was forced to be read-only: string variable
without update_func and PLUGIN_VAR_MEMALLOC flag
070619 11:17:57 [Warning] Server variable flush_method of plugin InnoDB was forced to be read-only: string variable
without update_func and PLUGIN_VAR_MEMALLOC flag
070619 11:17:57 [Warning] Server variable log_arch_dir of plugin InnoDB was forced to be read-only: string variable
without update_func and PLUGIN_VAR_MEMALLOC flag
070619 11:17:57 [Warning] Server variable log_group_home_dir of plugin InnoDB was forced to be read-only: string
variable without update_func and PLUGIN_VAR_MEMALLOC flag
070619 11:17:57 InnoDB: Started; log sequence number 0 48402
070619 11:17:57 [Note] Event Scheduler: Loaded 0 events
070619 11:17:57 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.1.19-beta' socket: '/tmp/mysql13306.sock' port: 13306 MySQL Community Server (GPL)

更多的可操作命令可以参考手册。

3、mysqlmanager 管理
停止 MySQL实例:

(yejr:localhost:)(none)> STOP INSTANCE mysqld1;
Query OK, 0 rows affected (1.81 sec)

(yejr:localhost:)(none)> SHOW INSTANCES;
+---------------+---------+
| instance_name | state |
+---------------+---------+
| mysqld1 | offline |
| mysqld2 | online |
| mysqld3 | online |
+---------------+---------+

(yejr:localhost:)(none)> START INSTANCE mysqld1;
Query OK, 0 rows affected (0.00 sec)
Instance started

(yejr:localhost:)(none)> SHOW INSTANCES;
+---------------+--------+
| instance_name | state |
+---------------+--------+
| mysqld1 | online |
| mysqld2 | online |
| mysqld3 | online |
+---------------+--------+

(yejr:localhost:)(none)> STOP INSTANCE mysqld2;
Query OK, 0 rows affected (1.81 sec)

(yejr:localhost:)(none)> STOP INSTANCE mysqld3;
Query OK, 0 rows affected (1.81 sec)

(yejr:localhost:)(none)> SHOW INSTANCES;
+---------------+---------+
| instance_name | state |
+---------------+---------+
| mysqld1 | offline |
| mysqld2 | offline |
| mysqld3 | offline |
+---------------+---------+

(yejr:localhost:)(none)> FLUSH INSTANCES;
Query OK, 0 rows affected (0.09 sec)

(yejr:localhost:)(none)> SHOW INSTANCES;
+---------------+--------+
| instance_name | state |
+---------------+--------+
| mysqld1 | online |
| mysqld2 | online |
| mysqld3 | online |
+---------------+--------+

在 MySQL实例 停止的状态下,还可以动态的修改端口等参数。
注意: FLUSH INSTANCES 语法会在 MySQL 5.2 以后不再使用。

总结:通过 mysqlmanager 我们就可以远程来管理 mysqld,包括重启,查看日志,设定系统参数等。而无需直接登录服务器或者通过 mysql 客户端登录服务器,也进*步保护了 MySQL 账户的安全,这在有较多数量的 MySQL 服务器引用环境中还是很有帮助的。

下页:   

相关文章
linux下使用ssd - 11-03-10 - 阅读: 283465
关于EtherScope II系列网络通Linux系统的几个常见问题(ES2-LAN,ES2-WLAN,ES2-PRO,ES2-LAN-SX/I,ES2-PRO-SX - 10-02-25 - 阅读: 235196
在linux下用bt协议修复远程的文件 - 09-03-13 - 阅读: 189058
快速得到mysql的历史数据状态 - 09-01-02 - 阅读: 139431
linux下利用badblocks程序在线修复坏道 - 08-12-26 - 阅读: 218770
ezmlm邮件列表使用mysql方式管理帐号 - 08-11-20 - 阅读: 170350
Hp 激光打印机 P1008在linux下的驱动 - 08-01-16 - 阅读: 253401
linux下的视频设备的复用 - 07-12-21 - 阅读: 185540
多款设备采用Qtopia软件,奇趣科技统领VoIP设备Linux开发平台 - 07-12-17 - 阅读: 247055
debian下的无盘linux系统安装要点 - 07-12-09 - 阅读: 219097
mysql的relay-bin - 07-10-16 - 阅读: 170435
mysql*化[转贴] - 07-10-02 - 阅读: 167306
linux下的后门检查程序rkhunter和杀毒程序clamav - 07-04-22 - 阅读: 311603
mysql数据库的2次灾难恢复实战 - 07-04-14 - 阅读: 198051
linux下读写ntfs分区中文编码问题 - 07-03-11 - 阅读: 239266
linux下的DV磁带视频采集和dvd刻录 - 07-02-13 - 阅读: 219979
linux内核虚拟机 kvm - 07-01-05 - 阅读: 299813
利用内存磁盘加速mysql --安恒网管员手记 - 06-06-26 - 阅读: 264963
linux下raid5磁盘阵列实施 - 06-03-13 - 阅读: 287763
linux在安恒公司的应用 ---安恒网管员手记 - 06-01-16 - 阅读: 340315

Email给朋友 打印本文
版权所有·安恒公司 Copyright © 2004   windows.anheng.com.cn   All Rights Reserved    
北京市海淀区*体南路9号 主语国际商务中心4号楼8层 (邮编100048) 电话:010-88018877