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

最新推荐文章于 2021-03-06 16:16:32 发布

weixin_39914243

最新推荐文章于 2021-03-06 16:16:32 发布

阅读量133

点赞数

文章标签： sqlmanager mysql5

本文链接：https://blog.csdn.net/weixin_39914243/article/details/113683350

版权

作/译者：叶金荣(Email:

)，来源：http://imysql.cn，转载请注明作/译者和出处，并且不能用于商业用途，违者必究。

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 服务器引用环境中还是很有帮助的。

本文出自 “老叶茶馆” 博客，转载请与作者联系！

weixin_39914243

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
sqlmanager mysql5_[MySQL 5.1 体验]MySQL 实例管理器 mysqlmanager 初试

作/译者：叶金荣(Email:)，来源：http://imysql.cn，转载请注明作/译者和出处，并且不能用于商业用途，违者必究。 MySQL实例管理器(IM)是通过TCP/IP端口运行的后台程序，用来监视和管理MySQL数据库服务器实例。MySQL实例管理器适合Unix-类操作系统和Windows。可以在mysqld_safe脚本使用MySQL实例管理器来启动和停止MySQL服务器，甚至可以从...
复制链接

扫一扫

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

“相关推荐”对你有帮助么？