| 订阅 | 在线投稿
分享
 
 
 

MySQL5.1体验--MySQL实例管理器mysqlmanager初试

来源:互联网网民  宽屏版  评论
2008-05-21 09:45:31

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

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

一、 mysqlmanager 配置文件

一个最常见的 mysqlmanager 配置文件如下:

[manager]port= 1999socket= /tmp/manager.sockpid-file= /tmp/manager.pidrun-as-service= truemonitoring-interval= 10default-mysqld-path= /usr/local/mysql/bin/mysqldpassword-file = /etc/mysqlmanager.passwdlog=/usr/local/mysql/bin/mysqld/mysqlmanager.log[mysqld1]basedir = /usr/local/mysqldatadir = /usr/local/mysql/data1general-log= truelog-error= /usr/local/mysql/data1/error.logpid-file= /usr/local/mysql/data1/mysql.pidport = 13306socket= /tmp/mysql13306.sock[mysqld2]basedir = /usr/local/mysqldatadir = /usr/local/mysql/data2general-log= truelog-error= /usr/local/mysql/data2/error.logpid-file= /usr/local/mysql/data2/mysql.pidport = 13307socket= /tmp/mysql13307.sock[mysqld3]basedir = /usr/local/mysqldatadir = /usr/local/mysql/data3general-log= truelog-error= /usr/local/mysql/data3/error.logpid-file= /usr/local/mysql/data3/mysql.pidport = 13308socket= /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.passwdyejr:*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.passwdyejr:*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 -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1 to server version: 1.0-betaType '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 variablewithout update_func and PLUGIN_VAR_MEMALLOC flag070619 11:17:57 [Warning] Server variable data_home_dir of plugin InnoDB was forced to be read-only: string variablewithout update_func and PLUGIN_VAR_MEMALLOC flag070619 11:17:57 [Warning] Server variable flush_method of plugin InnoDB was forced to be read-only: string variablewithout update_func and PLUGIN_VAR_MEMALLOC flag070619 11:17:57 [Warning] Server variable log_arch_dir of plugin InnoDB was forced to be read-only: string variablewithout update_func and PLUGIN_VAR_MEMALLOC flag070619 11:17:57 [Warning] Server variable log_group_home_dir of plugin InnoDB was forced to be read-only: stringvariable without update_func and PLUGIN_VAR_MEMALLOC flag070619 11:17:57 InnoDB: Started; log sequence number 0 48402070619 11:17:57 [Note] Event Scheduler: Loaded 0 events070619 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 服务器引用环境中还是很有帮助的。

 
特别声明:以上内容(如有图片或视频亦包括在内)为网络用户发布,本站仅提供信息存储服务。
 
  MySQL实例管理器(IM)是通过TCP/IP端口运行的后台程序,用来监视和管理MySQL数据库服务器实例。MySQL实例管理器适合Unix-类操作系统和Windows。   可以在mysqld_safe脚本使用MySQL实例管理器来启动和停止MySQL服务器,甚至可以从一个远程主机。MySQL实例管理器还执行mysqld_multi脚本的功能(和大多数语法)。   一、 mysqlmanager 配置文件   一个最常见的 mysqlmanager 配置文件如下:   [manager]port = 1999socket = /tmp/manager.sockpid-file= /tmp/manager.pidrun-as-service = truemonitoring-interval = 10default-mysqld-path = /usr/local/mysql/bin/mysqldpassword-file = /etc/mysqlmanager.passwdlog = /usr/local/mysql/bin/mysqld/mysqlmanager.log[mysqld1]basedir = /usr/local/mysqldatadir = /usr/local/mysql/data1general-log = truelog-error = /usr/local/mysql/data1/error.logpid-file = /usr/local/mysql/data1/mysql.pidport = 13306socket = /tmp/mysql13306.sock[mysqld2]basedir = /usr/local/mysqldatadir = /usr/local/mysql/data2general-log = truelog-error = /usr/local/mysql/data2/error.logpid-file = /usr/local/mysql/data2/mysql.pidport = 13307socket = /tmp/mysql13307.sock[mysqld3]basedir = /usr/local/mysqldatadir = /usr/local/mysql/data3general-log = truelog-error = /usr/local/mysql/data3/error.logpid-file = /usr/local/mysql/data3/mysql.pidport = 13308socket = /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.passwdyejr:*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.passwdyejr:*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 -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1 to server version: 1.0-betaType '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 variablewithout update_func and PLUGIN_VAR_MEMALLOC flag070619 11:17:57 [Warning] Server variable data_home_dir of plugin InnoDB was forced to be read-only: string variablewithout update_func and PLUGIN_VAR_MEMALLOC flag070619 11:17:57 [Warning] Server variable flush_method of plugin InnoDB was forced to be read-only: string variablewithout update_func and PLUGIN_VAR_MEMALLOC flag070619 11:17:57 [Warning] Server variable log_arch_dir of plugin InnoDB was forced to be read-only: string variablewithout update_func and PLUGIN_VAR_MEMALLOC flag070619 11:17:57 [Warning] Server variable log_group_home_dir of plugin InnoDB was forced to be read-only: stringvariable without update_func and PLUGIN_VAR_MEMALLOC flag070619 11:17:57 InnoDB: Started; log sequence number 0 48402070619 11:17:57 [Note] Event Scheduler: Loaded 0 events070619 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 服务器引用环境中还是很有帮助的。
󰈣󰈤
 
 
 
>>返回首页<<
 
 热帖排行
 
 
 
静静地坐在废墟上,四周的荒凉一望无际,忽然觉得,凄凉也很美
©2005- 王朝网络 版权所有