使用实例管理器轻松管理多个MySQL实例
| 4: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进程不会突然挂掉。 |
凌众科技专业提供服务器租用、服务器托管、企业邮局、虚拟主机等服务，公司网站：http://www.lingzhong.cn 为了给广大客户了解更多的技术信息，本技术文章收集来源于网络,凌众科技尊重文章作者的版权，如果有涉及你的版权有必要删除你的文章，请和我们联系。以上信息与文章正文是不可分割的一部分,如果您要转载本文章,请保留以上信息，谢谢! |
