MySQL DBA好帮手

Wesley13
• 阅读 608

导读：

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

MySQL DBA好帮手

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

目录

  • 用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 DBA好帮手

MySQL DBA好帮手

MySQL DBA好帮手

扫码加入MySQL技术Q群

（群号：****650149401）

MySQL DBA好帮手

点“在看”给我一朵小黄花MySQL DBA好帮手

MySQL DBA好帮手

本文分享自微信公众号 - 老叶茶馆（iMySQL_WX）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。

mysqlmysql创建数据库sql优化infodbm运维开发运维开发
点赞
收藏
评论区
推荐文章
blmius blmius
•
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时，报错：原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等，这样可以更容易地在不同的环境中使用MySQL。全局s
4072
0
0
helloworld_34035044 helloworld_34035044
•
2年前
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时，如何在新增行里自动增加id呢？能新增整数排序id吗？目前可以在新增行里自动增加id，但只能用uuid函数增加UUID编码，不能新增整数排序id。uuid函数说明：获取一个UUID，可以在填报表中用来创建数据ID语法：uuid()或uuid(sep)参数说明：sep布尔值，生成的uuid中是否包含分隔符''，缺省为
1113
0
0
待兔 待兔
•
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
1047
0
0
Jacquelyn38 Jacquelyn38
•
3年前
2020年前端实用代码段，为你的工作保驾护航
有空的时候，自己总结了几个代码段，在开发中也经常使用，谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
2361
0
0
Stella981 Stella981
•
3年前
Python3：sqlalchemy对mysql数据库操作，非sql语句
Python3：sqlalchemy对mysql数据库操作，非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
1127
0
0
Wesley13 Wesley13
•
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败，请联系管理员！');中国在东8区所以加8方法二：selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
1288
0
0
Wesley13 Wesley13
•
3年前
00：Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork)，斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟，已经成为Web应用程序的首选开发语言。Java是简单易学，完全面向对象，安全可靠，与平台无关的编程语言。
1231
0
0
Wesley13 Wesley13
•
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
1487
0
0
京东云开发者 京东云开发者
•
1年前
为什么mysql不推荐使用雪花ID作为主键
作者：毛辰飞背景在mysql中设计表的时候，mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一)，而是推荐连续自增的主键id，官方的推荐是auto_increment，那么为什么不建议采用uuid，使用uuid究
1013
0
0
Python进阶者 Python进阶者
•
9个月前
Excel中这日期老是出来00：00：00，怎么用Pandas把这个去除
大家好，我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下：这日期老是出来00：00：00，怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下：pd.toexcel之前把这
484
0
0
Wesley13
Wesley13
Lv1
最大的安心是自律温柔和爱自己。
文章
1.2w
粉丝
20
获赞
1.7k
热门文章
JavPlayer：AI破坏马赛克，大量马赛克破坏版影片流出
2020年最新可用的磁力搜索引擎&种子搜索网站，持续整理分享
8个在线接收手机短信验证码的免费网络服务整理
IDM 激活破解工具
java.net.ConnectException：Connection timed out：connect 出现连接超时的问题？