注册    登录
关注
Py学习  »  DATABASE

MySQL DBA好帮手--dbm

老叶茶馆 • 5 年前 • 520 次点击  

导读：

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


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


目录

  • 用dbm来解放生产力

  • 安装&初始化dbm-agent

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

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

  • 一行命令解决搭建MGR集群的问题

  • dbm-agent建设出来的实例质量如何

一、用dbm来解放生产力

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

二、安装&初始化dbm-agent
1、 通过 pip3 这个 python 包管理工具来安装 dbm-agent
  1.    bash
  2.    sudo su

  4.    pip3 install dbm-agent

  6.    Installing collected packages: dbm-


    
agent
  7.      Running setup.py install for dbm-agent ... done
  8.    Successfully installed dbm-agent-0.4.2

2、 初始化 dbm-agent

  1.    bash
  2.    sudo su

  4.    dbm-agent init

  6.    # 成功后可以看到如下目录结构
  7.    tree /usr/local/dbm-agent/

  9.    


    
├── etc
  10.    │   ├── dbma.cnf          # dbm-agent 的配置文件
  11.    │   ├── init-users.sql    # 在初始化数据库里将使用这个文件中的用户名和密码来创建用户
  12.    │   └── templates
  13.    │       ├── create-innodb-cluster.js
  14.    │       ├── init-users.sql.jinja
  15.    │       ├── mysql


    
-8.0.17.cnf.jinja    # mysql-8.0.17 版本对应的配置文件模板
  16.    │       ├── mysql-8.0.18.cnf.jinja    # mysql-8.0.18 版本对应的配置文件模板
  17.    │       ├── mysql-8.0-init-only.jinja # 只有初始化时才用到的配置文件
  18.    │       └── mysqld.service.jinja      # mysql systemd 配置文件模板
  19.    ├── logs    # dbm-agent 的日志文件保存目录(只要有守护进程模式下才会向这时写日志)
  20.    └── pkg

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

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

  1.    bash
  2.    tree /usr/local/dbm-agent/pkg

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

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


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

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

  1.    bash
  2.    dbma-cli-single-instance --port=3306 --max-mem=1024 install

  4.    2019-11-17 16:48:03,108 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller.install - im -


    
 INFO - 1115 - execute checkings for install mysql
  5.    2019-11-17 16:48:03,135 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._create_mysql_user - im - INFO - 864 - create user 'mysql3306' complete
  6.    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
  7.    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
  8.    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
  9.    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' 
  10.    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' 
  11.    2019-11-17 16:48:03,175 -


    
 dbm-agent.dbma.mysqldeploy.MyCnfInitRender.render - im - INFO - 497 - render mysql config file /tmp/mysql-init.cnf
  12.    2019-11-17 16:48:03,175 - dbm-agent.dbma.mysqldeploy.MyCnfInitRender.render_template_file - im - INFO - 524 - render template file complete
  13. 


    
   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' 
  14.    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' 
  15.    2019-11-17 16:48:03,208 - dbm-agent.dbma.mysqldeploy.MyCnfMSRender.render - im - INFO - 497 - render mysql config file /etc/my-3306.cnf
  16.    2019-11-


    
17 16:48:03,208 - dbm-agent.dbma.mysqldeploy.MyCnfMSRender.render_template_file - im - INFO - 554 - render template file complete
  17.    2019-11-17 16:48:03,208 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._render_production_cnf - im - INFO - 956 -


    
 render production cnf complete
  18.    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']
  19.    2019-11-17 16:48:13


    
,963 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._init_database - im - INFO - 1011 - init database complete
  20.    2019-11-17 16:48:13,967 - dbm-agent.dbma.mysqldeploy.MySQLSystemdRender.render - im - INFO - 666 - render systemd config file complete
  21.    2019-11


    
-17 16:48:13,967 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._config_systemd - im - INFO - 1027 - mysql systemd config complete
  22.    2019-11-17 16:48:14,146 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._enable_mysql - im - INFO - 1038 - config mysql 


    
auto start on boot complete
  23.    2019-11-17 16:48:16,172 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._start_mysql - im - INFO - 1068 - start mysql complete
  24.    2019-11-17 16:48:16,173 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._export_path 


    
- im - INFO - 1078 - export path complete
  25.    2019-11-17 16:48:16,177 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._export_header_file - im - INFO - 1106 - export header file complete
  26.    2019-11-17 16:48:16,177 


    
- dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller._export_so - im - INFO - 1093 - so file has been exported
  27.    2019-11-17 16:48:16,177 - dbm-agent.dbma.mysqldeploy.SingleInstanceInstaller.install - im - INFO - 1150 - install mysql single instance complete

2、 检查进程是否运行

  1.    bash
  2. 


    
   ps -ef | grep mysql
  3.    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                                                      
  4.    root       7463   7271  0


    
 16:51 pts/0    00:00:00 grep --color=auto mysql

3、 连接进去测试一下

  1.    sql
  2.    mysql -h127.0.0.1 -P3306 -uroot -pdbma@0352

  4.    mysql: [Warning] Using a password on the command line interface can be insecure.
  5.    Welcome to the MySQL monitor.  Commands 


    
end with ; or \g.
  6.    Your MySQL connection id is 8
  7.    Server version: 8.0.18 MySQL Community Server - GPL

  9.    Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

  11.    


    
Oracle is a registered trademark of Oracle Corporation and/or its
  12.    affiliates. Other names may be trademarks of their respective
  13.    owners.

  15.    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  17.    mysql> select user,host from mysql.user order by user;                                           
  18.    +--------------------+-----------+
  19. 


    
   | user               | host      |
  20.    +--------------------+-----------+
  21.    | cloneuser          | %         |
  22.    | cloneuser          | 127.0.0.1 |
  23.    | cloneuser          | localhost |
  24.    | dbma               | 127.0.0.1 |
  25.    | innodbclusteradmin | %         


    
|
  26.    | monitor            | 127.0.0.1 |
  27.    | mysql.infoschema   | localhost |
  28.    | mysql.session      | localhost |
  29.    | mysql.sys          | localhost |
  30.    | repluser           | %         |
  31.    | root               | 127.0.0.1 |
  32. 


    
   | root               | localhost |
  33.    +--------------------+-----------+
  34.    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 的安装与初始化

  1.    bash
  2.    dbma-cli-build-slave --host=172.16.192.100 --port=3306 build-slave

  4. 


    
   2019-11-17 17:03:46,620 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave.remote_clone - bms - INFO - 1590  - execute checkings for install mysql
  5.    2019-11-17 17:03:46,654 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave.install - bms -


    
 INFO - 1115  - execute checkings for install mysql
  6.    2019-11-17 17:03:46,695 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._create_mysql_user - bms - INFO - 864  - create user 'mysql3306' complete
  7.    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
  8.    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
  9.    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
  10.    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' 
  11.    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' 
  12.    2019-11-17 17:03:46,741 - dbm


    
-agent.dbma.mysqldeploy.MyCnfInitRender.render - bms - INFO - 497  - render mysql config file /tmp/mysql-init.cnf
  13.    2019-11-17 17:03:46,741 - dbm-agent.dbma.mysqldeploy.MyCnfInitRender.render_template_file - bms - INFO - 524  - render template file complete
  14. 


    
   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' 
  15.    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' 
  16.    2019-11-17 17:03:46,779 - dbm-agent.dbma.mysqldeploy.MyCnfMSRender.render - bms - INFO - 497  - render mysql config file /etc/my-3306.cnf
  17.    2019-11-17


    
 17:03:46,779 - dbm-agent.dbma.mysqldeploy.MyCnfMSRender.render_template_file - bms - INFO - 554  - render template file complete
  18.    2019-11-17 17:03:46,779 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._render_production_cnf - bms - INFO - 956  -


    
 render production cnf complete
  19.    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']
  20.    2019-11-17 17:03:57,


    
934 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._init_database - bms - INFO - 1011  - init database complete
  21.    2019-11-17 17:03:57,938 - dbm-agent.dbma.mysqldeploy.MySQLSystemdRender.render - bms - INFO - 666  - render systemd config file complete
  22.    2019-11-


    
17 17:03:57,938 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._config_systemd - bms - INFO - 1027  - mysql systemd config complete
  23.    2019-11-17 17:03:58,110 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._enable_mysql - bms - INFO - 1038  - config mysql 


    
auto start on boot complete
  24.    2019-11-17 17:04:00,144 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._start_mysql - bms - INFO - 1068  - start mysql complete
  25.    2019-11-17 17:04:00,145 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._export_path -


    
 bms - INFO - 1078  - export path complete
  26.    2019-11-17 17:04:00,147 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._export_header_file - bms - INFO - 1106  - export header file complete
  27.    2019-11-17 17:04:00,148 -


    
 dbm-agent.dbma.mysqldeploy.MySQLBuildSlave._export_so - bms - INFO - 1093  - so file has been exported
  28.    2019-11-17 17:04:00,148 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave.install - bms - INFO - 1150  - install mysql single instance complete
  29.    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';' 
  30.    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';' 
  31.    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)
  32.    2019-11-17 17:


    
04:03,432 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave.build_slave - bms - INFO - 1689  - wait mysql protocol avaiable
  33.    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;'
  34.    2019-11-17 17:04:14,466 - dbm-agent.dbma.mysqldeploy.MySQLBuildSlave.build_slave - bms - INFO - 1716  - prepare execute 'start slave;' 
  35.    2019-11-17 17:04:14,472 - dbm-agent.dbma.


    
mysqldeploy.MySQLBuildSlave.build_slave - bms - INFO - 1728  - rebuild mysql slave complete

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

  1.    sql
  2.    mysql -uroot -pdbma@0352 -h127.0.0.1

  4.    mysql: [Warning] Using a password on the command line interface can be insecure.
  5.    Welcome to the MySQL monitor.  Commands end with ; or


    
 \g.
  6.    Your MySQL connection id is 12
  7.    Server version: 8.0.18 MySQL Community Server - GPL

  9.    Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

  11.    Oracle is a registered trademark of Oracle Corporation


    
 and/or its
  12.    affiliates. Other names may be trademarks of their respective
  13.    owners.

  15.    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  17.    mysql> show slave status \G
  18.    *************************** 1. row ***************************
  19.                   Slave_IO_State: Waiting for master to send event
  20. 


    
                     Master_Host: 172.16.192.100
  21.                      Master_User: repluser
  22.                      Master_Port: 3306
  23.                    Connect_Retry: 60
  24.                  Master_Log_File: mysql-bin.000002
  25.              Read_Master_Log_Pos: 151
  26.                   Relay_Log_File: relay.000002
  27.                    Relay_Log_Pos: 357
  28. 


    
           Relay_Master_Log_File: mysql-bin.000002
  29.                 Slave_IO_Running: Yes
  30.                Slave_SQL_Running: Yes


五、一行命令解决搭建MGR集群的问题

这里不再举例了可以直接到 github 上看 dbm-agent 的说明, mysql-group-replication,mysql-shell,innodb-cluster,mysql-router 个个都支持。


六、dbm-agent建设出来的实例质量如何

1、 一份完整、按需优化&渲染过后的配置文件

  1. ini
  2.    #cat /etc/my-3306.cnf
  3.    [mysqld]
  4.    # basic
  5.    user                                     = mysql3306
  6. 


    
   basedir                                  = /usr/local/mysql-8.0.18-linux-glibc2.12-x86_64
  7.    datadir                                  = /database/mysql/data/3306
  8.    server_id                                = 1388
  9.    port                                     = 3306
  10.    bind_address                             = *
  11.    admin_address                            = 127.0.0.1
  12.    mysqlx_port                              = 


    
33060
  13.    admin_port                               = 33062
  14.    socket                                   = /tmp/mysql-3306.sock
  15.    mysqlx_socket                            = /tmp/mysqlx-33060.sock
  16.    pid_file                                 = /tmp/mysql-3306.pid  
  17.    character_set_server                     = utf8mb4
  18.    open_files_limit                         = 102000
  19.    max_prepared_stmt_count                  = 1048576  
  20.    skip_name_resolve                        = 1     


    

  21.    super_read_only                          = OFF
  22.    log_timestamps                           = system
  23.    event_scheduler                          = OFF
  24.    auto_generate_certs                      = ON
  25.    activate_all_roles_on_login              = ON
  26.    end_markers_in_json                      = OFF
  27.    tmpdir                                   = /tmp/
  28.    max_connections                          = 128
  29.    autocommit                               = ON
  30.    sort_buffer_size                         = 256K
  31. 


    
   join_buffer_size                         = 256K
  32.    eq_range_index_dive_limit                = 200

  34.    ## table
  35.    big_tables                               = OFF
  36.    sql_require_primary_key                  = OFF
  37.    lower_case_table_names                   = 1
  38.    auto_increment_increment                 = 1
  39.    auto_increment_offset                    = 1
  40.    table_open_cache                         = 4000
  41.    table_definition_cache                   = 2000
  42. 


    
   table_open_cache_instances               = 32

  44.    ## net
  45.    max_allowed_packet                       = 1G
  46.    connect_timeout                          = 10
  47.    interactive_timeout                      = 28800
  48.    net_read_timeout                         = 30
  49.    net_retry_count                          = 10
  50.    net_write_timeout                        = 60
  51.    net_buffer_length                        = 32K

  53.    ## logs
  54.    log_output                               


    
= FILE
  55.    general_log                              = OFF
  56.    general_log_file                         = general.log
  57.    # error
  58.    log_error                                = err.log
  59.    log_statements_unsafe_for_binlog         = ON
  60.    # slow
  61.    slow_query_log                           = ON
  62.    slow_query_log_file                      = slow.log
  63.    long_query_time                          = 2
  64.    log_queries_not_using_indexes            = OFF
  65. 


    
   log_slow_admin_statements                = OFF
  66.    log_slow_slave_statements                = OFF
  67.    # binlog
  68.    log_bin                                  = /binlog/mysql/binlog/3306/mysql-bin
  69.    binlog_checksum                          = none
  70.    log_bin_trust_function_creators          = ON
  71.    binlog_direct_non_transactional_updates  = OFF
  72.    binlog_expire_logs_seconds               = 604800
  73.    binlog_error_action                      = ABORT_SERVER
  74. 


    
   binlog_format                            = ROW
  75.    max_binlog_stmt_cache_size               = 1G
  76.    max_binlog_cache_size                    = 1G
  77.    max_binlog_size                          = 1G
  78.    binlog_order_commits                     = ON
  79.    binlog_row_image                         = FULL
  80.    binlog_row_metadata                      = MINIMAL
  81.    binlog_rows_query_log_events             = ON
  82.    binlog_stmt_cache_size                   = 32K
  83.    log_slave_updates                        = ON
  84.    binlog_transaction_dependency_history_size =


    
25000
  85.    binlog_transaction_dependency_tracking   = WRITESET
  86.    sync_binlog                              = 1
  87.    binlog_cache_size                        = 96K
  88.    binlog_group_commit_sync_delay           = 0
  89.    binlog_group_commit_sync_no_delay_count  = 0

  91.    ## replication
  92.    rpl_semi_sync_master_enabled             = 1
  93.    rpl_semi_sync_slave_enabled              = 1
  94.    rpl_semi_sync_master_timeout             = 1000
  95.    rpl_semi_sync_master_wait_point          = AFTER_SYNC
  96. 


    
   rpl_semi_sync_master_wait_no_slave       = ON
  97.    rpl_semi_sync_master_wait_for_slave_count = 1
  98.    master_info_repository                   = table
  99.    sync_master_info                         = 10000
  100.    skip_slave_start                         = OFF
  101.    slave_load_tmpdir                        = /tmp/
  102.    plugin_load_add                          = semisync_master.so
  103.    plugin_load_add                          = semisync_slave.so
  104.    relay_log                                = relay
  105.    sync_relay_log                           = 


    
10000
  106.    sync_relay_log_info                      = 10000
  107.    relay_log_info_repository                = table
  108.    slave_preserve_commit_order              = ON
  109.    slave_parallel_type                      = logical_clock
  110.    slave_parallel_workers                   = 2
  111.    slave_max_allowed_packet                 = 1G



  115.    ## gtid
  116.    gtid_mode                                = ON
  117.    binlog_gtid_simple_recovery              = ON
  118.    enforce_gtid_consistency                 = ON
  119. 


    
   gtid_executed_compression_period         = 1000

  121.    ## clone
  122.    plugin-load-add                          = mysql_clone.so
  123.    clone                                    = FORCE_PLUS_PERMANEN

  125.    # engines
  126.    default_storage_engine                   = innodb
  127.    default_tmp_storage_engine               = innodb
  128.    internal_tmp_mem_storage_engine          = TempTable

  130.    ## innodb
  131.    innodb_data_home_dir                     = ./
  132. 


    
   innodb_data_file_path                    = ibdata1:64M:autoextend
  133.    innodb_page_size                         = 16K
  134.    innodb_default_row_format                = dynamic
  135.    innodb_log_group_home_dir                = ./
  136.    innodb_redo_log_encrypt                  = OFF
  137.    innodb_online_alter_log_max_size         = 256M
  138.    innodb_undo_directory                    = ./
  139.    innodb_undo_log_encrypt                  = OFF
  140.    innodb_undo_log_truncate                 = ON
  141.    innodb_max_undo_log_size                 = 1G
  142. 


    
   innodb_rollback_on_timeout               = OFF
  143.    innodb_rollback_segments                 = 128
  144.    innodb_log_checksums                     = ON
  145.    innodb_checksum_algorithm                = crc32
  146.    innodb_log_compressed_pages              = ON
  147.    innodb_doublewrite                       = ON
  148.    innodb_commit_concurrency                = 0
  149.    innodb_read_only                         = OFF
  150.    innodb_dedicated_server                  = OFF
  151.    innodb_old_blocks_pct                    = 37
  152. 


    
   innodb_old_blocks_time                   = 1000
  153.    innodb_random_read_ahead                 = OFF
  154.    innodb_read_ahead_threshold              = 56
  155.    innodb_max_dirty_pages_pct_lwm           = 20
  156.    innodb_max_dirty_pages_pct               = 90
  157.    innodb_lru_scan_depth                    = 1024
  158.    innodb_adaptive_flushing                 = ON
  159.    innodb_adaptive_flushing_lwm             = 10
  160.    innodb_flushing_avg_loops                = 30
  161.    innodb_buffer_pool_dump_pct              = 50
  162. 


    
   innodb_buffer_pool_dump_at_shutdown      = ON
  163.    innodb_buffer_pool_load_at_startup       = ON
  164.    innodb_buffer_pool_filename              = ib_buffer_pool
  165.    innodb_stats_persistent                  = ON
  166.    innodb_stats_on_metadata                 = ON
  167.    innodb_stats_method                      = nulls_equal
  168.    innodb_stats_auto_recalc                 = ON
  169.    innodb_stats_include_delete_marked       = ON
  170.    innodb_stats_persistent_sample_pages     = 20
  171.    innodb_stats_transient_sample_pages      = 8
  172.    innodb_status_output                     = OFF
  173. 


    
   innodb_status_output_locks               = OFF
  174.    innodb_buffer_pool_dump_now              = OFF
  175.    innodb_buffer_pool_load_abort            = OFF
  176.    innodb_buffer_pool_load_now              = OFF
  177.    innodb_thread_concurrency                = 0
  178.    innodb_concurrency_tickets               = 5000
  179.    innodb_thread_sleep_delay                = 15000
  180.    innodb_adaptive_max_sleep_delay          = 150000
  181.    innodb_read_io_threads                   = 4
  182.    innodb_write_io_threads                  = 4
  183. 


    
   innodb_use_native_aio                    = ON
  184.    innodb_flush_sync                        = OFF
  185.    innodb_spin_wait_delay                   = 6
  186.    innodb_purge_threads                     = 4
  187.    innodb_purge_batch_size                  = 300
  188.    innodb_purge_rseg_truncate_frequency     = 128
  189.    innodb_deadlock_detect                   = ON
  190.    innodb_print_all_deadlocks               = ON
  191.    innodb_lock_wait_timeout                 = 50
  192.    innodb_table_locks                       = ON
  193. 


    
   innodb_sync_array_size                   = 1
  194.    innodb_sync_spin_loops                   = 30
  195.    innodb_print_ddl_logs                    = OFF
  196.    innodb_replication_delay                 = 0
  197.    innodb_cmp_per_index_enabled             = OFF
  198.    innodb_disable_sort_file_cache           = OFF
  199.    innodb_numa_interleave                   = OFF
  200.    innodb_strict_mode                       = ON
  201.    innodb_sort_buffer_size                  = 1M
  202.    innodb_fast_shutdown                     = 1
  203. 


    
   innodb_force_load_corrupted              = OFF
  204.    innodb_force_recovery                    = 0
  205.    innodb_temp_tablespaces_dir              = ./#innodb_temp/
  206.    innodb_tmpdir                            = ./
  207.    innodb_temp_data_file_path               = ibtmp1:64M:autoextend
  208.    innodb_page_cleaners                     = 4
  209.    innodb_adaptive_hash_index               = ON
  210.    innodb_adaptive_hash_index_parts         = 8
  211.    innodb_flush_log_at_timeout              = 1
  212.    innodb_fsync_threshold                   =


    
 0
  213.    innodb_fill_factor                       = 90
  214.    innodb_file_per_table                    = ON
  215.    innodb_autoextend_increment              = 64
  216.    innodb_open_files                        = 100000
  217.    innodb_buffer_pool_chunk_size            = 128M
  218.    innodb_buffer_pool_instances             = 1
  219.    innodb_log_files_in_group                = 8
  220.    innodb_log_file_size                     = 64M
  221.    innodb_flush_neighbors                   = 0
  222. 


    
   innodb_io_capacity                       = 4000
  223.    innodb_io_capacity_max                   = 20000
  224.    innodb_autoinc_lock_mode                 = 2
  225.    innodb_change_buffer_max_size            = 25
  226.    innodb_flush_method                      = O_DIRECT
  227.    innodb_log_buffer_size                   = 64M
  228.    innodb_flush_log_at_trx_commit           = 1
  229.    innodb_buffer_pool_size                  = 256M


  232.    ####  for performance_schema
  233.    performance_schema                                                      =OFF
  234. 


    
   performance_schema_consumer_global_instrumentation                      =ON
  235.    performance_schema_consumer_thread_instrumentation                      =ON
  236.    performance_schema_consumer_events_stages_current                       =ON
  237.    performance_schema_consumer_events_stages_history                       =ON
  238.    performance_schema_consumer_events_stages_history_long                  =OFF
  239.    performance_schema_consumer_statements_digest                           =ON
  240.    performance_schema_consumer_events_statements_current                   =ON
  241.    performance_schema_consumer_events_statements_history                   =ON
  242.    performance_schema_consumer_events_statements_history_long              =OFF
  243.    performance_schema_consumer_events_waits_current                        =ON
  244.    performance_schema_consumer_events_waits_history                        =ON
  245. 


    
   performance_schema_consumer_events_waits_history_long                   =OFF
  246.    performance-schema-instrument                                           ='memory/%=COUNTED'



  250.    # -- ~ _ ~    ~ _ ~     ~ _ ~ --
  251.    # base on mysql-8.0.18
  252.    # generated by https://www.sqlpy.com 2019年10月14日 21时47分11秒
  253.    # wechat: jianglegege
  254.    # email: 1721900707@qq.com
  255.    # -- ~ _ ~ --

2、 把规范和流程物化到 dbm-agent 的每一个环节，而不是口头上或文档上;更加详细的内容可以参考 github dbm-agent







扫码加入MySQL技术Q群

（群号：650149401）

   



点“在看”给我一朵小黄花


Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/50253
Reply
 
520 次点击  
登录后回复
关于   移动版
Py学习 - 专注于Python技术发展的社区(原Django社区)
沪ICP备11025650号