MySQL DBA好帮手--dbm

转载

叶金荣 2021-07-13 15:35:04

文章标签 dbm 文章分类 MySQL 数据库

导读：

作者：蒋乐兴 MySQL-DBA 目前在 github 上维护着两套 MySQL 开源工具 mysqltools & dbm(dbm-agent dbm-center)，对机器学习和程序化交易也有些心得。

一想到你在关注我就忍不住有点紧张

一、用dbm来解放生产力

对于 dbm 来说不管你是搭建单机、主从复制、异或是 MGR 都只需要一行命令就能高效、高质量的解决；并且几乎不会有任何成本(dbm是全开源的，包含dbm-agent,dbm-center)，下面先来检验一下 dbm-agent 常来的效率提升。二、安装&初始化dbm-agent1、通过 pip3 这个 python 包管理工具来安装 dbm-agent

bash
sudo su
pip3 install dbm-agent
Installing collected packages: dbm-agent
Running setup.py install for dbm-agent ... done
Successfully installed dbm-agent-0.4.2

2、初始化 dbm-agent

bash
sudo su
dbm-agent init
# 成功后可以看到如下目录结构
tree /usr/local/dbm-agent/
├── etc
│ ├── dbma.cnf # dbm-agent 的配置文件
│ ├── init-users.sql # 在初始化数据库里将使用这个文件中的用户名和密码来创建用户
│ └── templates
│ ├── create-innodb-cluster.js
│ ├── init-users.sql.jinja
│ ├── mysql-8.0.17.cnf.jinja # mysql-8.0.17 版本对应的配置文件模板
│ ├── mysql-8.0.18.cnf.jinja # mysql-8.0.18 版本对应的配置文件模板
│ ├── mysql-8.0-init-only.jinja # 只有初始化时才用到的配置文件
│ └── mysqld.service.jinja # mysql systemd 配置文件模板
├── logs # dbm-agent 的日志文件保存目录(只要有守护进程模式下才会向这时写日志)
└── pkg

init 的时候可以通过 --init-pwd 设置 mysql 用户的密码，如果没有指定那么默认为 dbma@0352

3、下载 mysql 与 mysql-shell 的二进制安装包到 /usr/local/dbm-agent/pkg，完成后目录的内容如下

bash
tree /usr/local/dbm-agent/pkg
├── mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz # 各个软件的安装包(要自己下载并保存到这里，dbm-agent不会自动下载它
└── mysql-shell-8.0.18-linux-glibc2.12-x86-64bit.tar.gz # 各个软件的安装包(要自己下载并保存到这里，dbm-agent不会自动下载它)

下面的内容不会再加上 sudo su 了，默认都在 root 下运行

三、一行命令解决安装单机的问题

1、安装一个监听在 3306 端口上的实例，限制它最大给用 1G 内存

bash
dbma-cli-single-instance --port=3306 --max-mem=1024 install
2019-11-17 16:48:03,108 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller.install - im - INFO - 1115 - execute checkings for install mysql
2019-11-17 16:48:03,135 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._create_mysql_user - im - INFO - 864 - create user 'mysql3306' complete
2019-11-17 16:48:03,136 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._create_data_dir - im - INFO - 888 - create datadir '/database/mysql/data/3306' complete
2019-11-17 16:48:03,137 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._create_binlog_dir - im - INFO - 910 - create binary dir '/binlog/mysql/binlog/3306' complete
2019-11-17 16:48:03,138 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._create_backup_dir - im - INFO - 932 - create backup dir '/backup/mysql/backup/3306' complete
2019-11-17 16:48:03,138 - dbm-agent.dbma.mysqldeploy.MyCnfInitRender.render_template_file - im - INFO - 518 - using template 'mysql-8.0-init-only.jinja'
2019-11-17 16:48:03,138 - dbm-agent.dbma.mysqldeploy.MyCnfInitRender.set_template - im - INFO - 457 - using template file 'mysql-8.0-init-only.jinja'
2019-11-17 16:48:03,175 - dbm-agent.dbma.mysqldeploy.MyCnfInitRender.render - im - INFO - 497 - render mysql config file /tmp/mysql-init.cnf
2019-11-17 16:48:03,175 - dbm-agent.dbma.mysqldeploy.MyCnfInitRender.render_template_file - im - INFO - 524 - render template file complete
2019-11-17 16:48:03,175 - dbm-agent.dbma.mysqldeploy.MyCnfMSRender.render_template_file - im - INFO - 548 - using template 'mysql-8.0.18.cnf.jinja'
2019-11-17 16:48:03,175 - dbm-agent.dbma.mysqldeploy.MyCnfMSRender.set_template - im - INFO - 457 - using template file 'mysql-8.0.18.cnf.jinja'
2019-11-17 16:48:03,208 - dbm-agent.dbma.mysqldeploy.MyCnfMSRender.render - im - INFO - 497 - render mysql config file /etc/my-3306.cnf
2019-11-17 16:48:03,208 - dbm-agent.dbma.mysqldeploy.MyCnfMSRender.render_template_file - im - INFO - 554 - render template file complete
2019-11-17 16:48:03,208 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._render_production_cnf - im - INFO - 956 - render production cnf complete
2019-11-17 16:48:03,209 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._init_database - im - INFO - 1004 - ['/usr/local/mysql-8.0.18-linux-glibc2.12-x86_64/bin/mysqld', '--defaults-file=/tmp/mysql-init.cnf', '--initialize-insecure', '--user=mysql3306', '--init-file=/usr/local/dbm-agent/etc/init-users.sql']
2019-11-17 16:48:13,963 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._init_database - im - INFO - 1011 - init database complete
2019-11-17 16:48:13,967 - dbm-agent.dbma.mysqldeploy.MySQLSystemdRender.render - im - INFO - 666 - render systemd config file complete
2019-11-17 16:48:13,967 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._config_systemd - im - INFO - 1027 - mysql systemd config complete
2019-11-17 16:48:14,146 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._enable_mysql - im - INFO - 1038 - config mysql auto start on boot complete
2019-11-17 16:48:16,172 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._start_mysql - im - INFO - 1068 - start mysql complete
2019-11-17 16:48:16,173 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._export_path - im - INFO - 1078 - export path complete
2019-11-17 16:48:16,177 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._export_header_file - im - INFO - 1106 - export header file complete
2019-11-17 16:48:16,177 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._export_so - im - INFO - 1093 - so file has been exported
2019-11-17 16:48:16,177 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller.install - im - INFO - 1150 - install mysql single instance complete

2、检查进程是否运行

bash
ps -ef | grep mysql
mysql33+ 7418 1 1 16:48 ? 00:00:02 /usr/local/mysql-8.0.18-linux-glibc2.12-x86_64/bin/mysqld --defaults-file=/etc/my-3306.cnf
root 7463 7271 0 16:51 pts/0 00:00:00 grep --color=auto mysql

3、连接进去测试一下

sql
mysql -h127.0.0.1 -P3306 -uroot -pdbma@0352
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.18 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select user,host from mysql.user order by user;
+--------------------+-----------+
| user | host |
+--------------------+-----------+
| cloneuser | % |
| cloneuser | 127.0.0.1 |
| cloneuser | localhost |
| dbma | 127.0.0.1 |
| innodbclusteradmin | % |
| monitor | 127.0.0.1 |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| repluser | % |
| root | 127.0.0.1 |
| root | localhost |
+--------------------+-----------+
12 rows in set (0.01 sec)

四、一行命令解决增加slave的问题

上一个例子中的单机是在 172.16.192.100 这台主机上的，如果想把 172.16.192.110 作为它的 slave ；手工做的话就要“备份”，“还原”，“建立主从关系”，dbm-agent 把这些逻辑都实现了，并且只要一行命令就能解决

1、假设你已经在 172.16.192.110 主机上完成了 dbm-agent 的安装与初始化

bash
dbma-cli-build-slave --host=172.16.192.100 --port=3306 build-slave
2019-11-17 17:03:46,620 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave.remote_clone - bms - INFO - 1590 - execute checkings for install mysql
2019-11-17 17:03:46,654 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave.install - bms - INFO - 1115 - execute checkings for install mysql
2019-11-17 17:03:46,695 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._create_mysql_user - bms - INFO - 864 - create user 'mysql3306' complete
2019-11-17 17:03:46,698 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._create_data_dir - bms - INFO - 888 - create datadir '/database/mysql/data/3306' complete
2019-11-17 17:03:46,701 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._create_binlog_dir - bms - INFO - 910 - create binary dir '/binlog/mysql/binlog/3306' complete
2019-11-17 17:03:46,704 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._create_backup_dir - bms - INFO - 932 - create backup dir '/backup/mysql/backup/3306' complete
2019-11-17 17:03:46,706 - dbm-agent.dbma.mysqldeploy.MyCnfInitRender.render_template_file - bms - INFO - 518 - using template 'mysql-8.0-init-only.jinja'
2019-11-17 17:03:46,706 - dbm-agent.dbma.mysqldeploy.MyCnfInitRender.set_template - bms - INFO - 457 - using template file 'mysql-8.0-init-only.jinja'
2019-11-17 17:03:46,741 - dbm-agent.dbma.mysqldeploy.MyCnfInitRender.render - bms - INFO - 497 - render mysql config file /tmp/mysql-init.cnf
2019-11-17 17:03:46,741 - dbm-agent.dbma.mysqldeploy.MyCnfInitRender.render_template_file - bms - INFO - 524 - render template file complete
2019-11-17 17:03:46,742 - dbm-agent.dbma.mysqldeploy.MyCnfMSRender.render_template_file - bms - INFO - 548 - using template 'mysql-8.0.18.cnf.jinja'
2019-11-17 17:03:46,742 - dbm-agent.dbma.mysqldeploy.MyCnfMSRender.set_template - bms - INFO - 457 - using template file 'mysql-8.0.18.cnf.jinja'
2019-11-17 17:03:46,779 - dbm-agent.dbma.mysqldeploy.MyCnfMSRender.render - bms - INFO - 497 - render mysql config file /etc/my-3306.cnf
2019-11-17 17:03:46,779 - dbm-agent.dbma.mysqldeploy.MyCnfMSRender.render_template_file - bms - INFO - 554 - render template file complete
2019-11-17 17:03:46,779 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._render_production_cnf - bms - INFO - 956 - render production cnf complete
2019-11-17 17:03:46,779 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._init_database - bms - INFO - 1004 - ['/usr/local/mysql-8.0.18-linux-glibc2.12-x86_64/bin/mysqld', '--defaults-file=/tmp/mysql-init.cnf', '--initialize-insecure', '--user=mysql3306', '--init-file=/usr/local/dbm-agent/etc/init-users.sql']
2019-11-17 17:03:57,934 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._init_database - bms - INFO - 1011 - init database complete
2019-11-17 17:03:57,938 - dbm-agent.dbma.mysqldeploy.MySQLSystemdRender.render - bms - INFO - 666 - render systemd config file complete
2019-11-17 17:03:57,938 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._config_systemd - bms - INFO - 1027 - mysql systemd config complete
2019-11-17 17:03:58,110 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._enable_mysql - bms - INFO - 1038 - config mysql auto start on boot complete
2019-11-17 17:04:00,144 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._start_mysql - bms - INFO - 1068 - start mysql complete
2019-11-17 17:04:00,145 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._export_path - bms - INFO - 1078 - export path complete
2019-11-17 17:04:00,147 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._export_header_file - bms - INFO - 1106 - export header file complete
2019-11-17 17:04:00,148 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._export_so - bms - INFO - 1093 - so file has been exported
2019-11-17 17:04:00,148 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave.install - bms - INFO - 1150 - install mysql single instance complete
2019-11-17 17:04:00,167 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave.remote_clone - bms - INFO - 1613 - prepare execute 'set @@global.clone_valid_donor_list='172.16.192.100:3306';'
2019-11-17 17:04:00,168 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave.remote_clone - bms - INFO - 1618 - prepare execute 'clone instance from cloneuser@'172.16.192.100':3306 identified by 'dbma@0352';'
2019-11-17 17:04:03,431 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave.remote_clone - bms - INFO - 1629 - remote clone complete (mysql will auto restart,please wait)
2019-11-17 17:04:03,432 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave.build_slave - bms - INFO - 1689 - wait mysql protocol avaiable
2019-11-17 17:04:14,454 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave.build_slave - bms - INFO - 1710 - prepare execute 'change master to master_host='172.16.192.100',master_port=3306,master_user='repluser',master_password='dbma@0352',master_ssl = 1,master_auto_position=1;'
2019-11-17 17:04:14,466 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave.build_slave - bms - INFO - 1716 - prepare execute 'start slave;'
2019-11-17 17:04:14,472 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave.build_slave - bms - INFO - 1728 - rebuild mysql slave complete

2、检查主从关系是否正常

sql
mysql -uroot -pdbma@0352 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.18 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.192.100
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 151
Relay_Log_File: relay.000002
Relay_Log_Pos: 357
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes