docker-compose mysql8.0.24

构建环境：docker、docker-compose

配置文件

vim /docker/docker-compose.yml

version: "3.9"
services:
  fcat-mysql:
    image: mysql:8.0.24
    container_name: fcat-mysql
    restart: always
    ports:
      - "16009:3306"
    volumes:
      - ./fcat-mysql/logs:/logs
      - ./fcat-mysql/data:/var/lib/mysql
    command: [
          'mysqld',
          '--innodb-buffer-pool-size=80M',
          '--character-set-server=utf8mb4',
          '--collation-server=utf8mb4_unicode_ci',
          '--default-time-zone=+8:00',
          '--lower-case-table-names=1',
          '--sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
        ]
    environment:
      MYSQL_DATABASE: 'db_1'
      MYSQL_ROOT_PASSWORD: 123456
    deploy:
      resources:
        limits:
          memory: 2048M

运行

docker up  -d

配置my.conf文件

[mysqld]
port		= 3306
socket		= /tmp/mysql.sock
datadir     = /var/lib/mysql
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 64M
max_allowed_packet = 1G
table_open_cache = 600
sort_buffer_size = 1M
net_buffer_length = 4K
read_buffer_size = 1M
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 16M
thread_cache_size = 32
tmp_table_size = 256M
default_authentication_plugin = mysql_native_password
lower_case_table_names = 1
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

explicit_defaults_for_timestamp = true
#skip-name-resolve
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id = 1
binlog_expire_logs_seconds = 600000
slow_query_log=1
slow-query-log-file=/var/lib/mysql/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
early-plugin-load = ""


innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 4G
innodb_log_file_size = 128M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 10
innodb_write_io_threads = 10
# 本来没有
innodb_doublewrite=0
innodb_flush_method=O_DIRECT

secure_file_priv=/var/lib/mysql

[mysqldump]
quick
max_allowed_packet = 500M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 1M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

docker-compose.yml指定my.conf

version: "3.9"
services:
  fcat-mysql:
    image: mysql:8.0.24
    container_name: fcat-mysql
    restart: always
    cap_add:
      - SYS_NICE
    ports:
      - "3306:3306"
    volumes:
      - ./fcat-mysql/logs:/logs
      - ./fcat-mysql/data:/var/lib/mysql
      - ./fcat-mysql/conf/my.cnf:/etc/mysql/my.cnf
    command: [
          'mysqld',
          '--character-set-server=utf8mb4',
          '--collation-server=utf8mb4_unicode_ci',
          '--default-time-zone=+8:00',
          '--lower-case-table-names=1'
        ]
    environment:
      MYSQL_DATABASE: 'db_1'
      MYSQL_ROOT_PASSWORD: 123456
    deploy:
      resources:
        limits:
          memory: 6048M 
       

MySQL的配置优化

MySQL的配置优化是一个复杂的过程，需要根据具体的应用场景、硬件资源以及数据库的实际负载进行调整。上述配置文件涵盖了MySQL服务器的很多方面，包括连接处理、缓冲区大小、日志管理等。下面是一些基于通用情况的建议，但请注意，在应用这些建议之前，最好在测试环境中验证它们对你的特定环境的影响。

• max_connections（最大连接数）

max_connections = 500：这个参数设置允许的最大并发连接数。如果你的服务器硬件资源允许，且观察到频繁达到最大连接限制，可以考虑增加此值。但请注意，每增加一个连接，都会增加MySQL服务器的内存使用量。确保服务器有足够的内存来支持更多的连接。

• innodb_buffer_pool_size（InnoDB缓冲池大小）

innodb_buffer_pool_size = 256M：这是InnoDB存储引擎最重要的参数之一，它定义了InnoDB缓冲池的大小，该缓冲池用于缓存表数据和索引。对于具有足够内存的生产环境服务器，建议将此值设置为可用内存的60%-80%。

• innodb_log_file_size（InnoDB日志文件大小）

innodb_log_file_size = 128M：这个参数设置了InnoDB的日志文件大小。较大的日志文件可以提高事务处理的效率，但也会增加恢复数据库的时间。根据你的事务量调整大小，一般推荐的范围是128M到2G之间。

• thread_cache_size（线程缓存大小）

thread_cache_size = 32：当客户端断开连接时，如果线程缓存未满，客户端的线程将被放入缓存中。增加此值可以减少因为频繁创建和销毁线程而带来的开销。如果你的服务器面临大量的新连接，可以适当增加这个值。可以通过监控Threads_created状态变量来判断是否需要增加。

• table_open_cache（表打开缓存）

table_open_cache = 256：这个参数设置了服务器层面可以缓存的表的数量。对于访问很多表的应用，增加这个值可以提高性能。

• max_allowed_packet（最大允许包大小）

max_allowed_packet = 100G：这个设置可能过大，除非你确实有需要传输非常大的数据包。过大的值可能会导致内存分配问题。根据实际需要调整，一般几百MB到1GB已经足够大部分应用场景。

• tmp_table_size/max_heap_table_size（内存临时表大小）

tmp_table_size = 64M：这个参数和max_heap_table_size（未在配置中显示，但应考虑一并调整）控制了内存临时表的最大大小。如果超过这个大小，MySQL将使用磁盘临时表。对于执行大量复杂查询（如GROUP BY或DISTINCT操作）的数据库，增加这些值可以提高性能。

• query_cache_size（查询缓存大小）

MySQL 8.0 已经移除了查询缓存功能，所以不需要调整此参数。

• slow_query_log/long_query_time（慢查询日志）

这些参数帮助识别执行时间超过long_query_time秒的查询。根据你的业务需求调整long_query_time，并定期检查慢查询日志以优化查询性能。

• key_buffer_size = 512M

key_buffer_size参数指定了MyISAM表索引块的缓冲区大小。如果你的数据库主要使用MyISAM存储引擎，并且服务器有足够的内存，那么增加key_buffer_size可以提高索引读取的性能，从而加速查询速度。然而，如果你的数据库主要使用InnoDB存储引擎，那么这个参数的调整就不会带来太大的性能提升，因为InnoDB使用innodb_buffer_pool_size参数来缓存数据和索引。512M对于拥有较多MyISAM表的数据库可能是合理的，但确保留有足够的内存给操作系统和其他需要（特别是InnoDB缓冲池）。

• sort_buffer_size = 1M

sort_buffer_size参数控制MySQL为排序操作分配的内存缓冲区大小。每个需要排序的线程都会分配这么多内存。默认值通常足够小型到中型的排序操作。虽然增加此值可以加速大型的排序操作，但是过大的值可能会消耗大量内存，因为每个排序的线程都会请求这么多内存，这可能会导致系统性能下降。1M是一个相对保守的值，对于大多数应用来说应该足够，但如果你的应用需要频繁执行大型排序操作，可能需要根据实际情况进行调整。

• read_buffer_size = 2M

read_buffer_size参数设置了MyISAM表顺序扫描时每个线程的缓冲区大小。这意味着当进行全表扫描时，MySQL会为每个扫描分配这么多内存。默认值通常较小，增加这个值可以提高顺序扫描的速度，但与sort_buffer_size类似，如果设置得过大，可能会影响系统性能，尤其是在并发场景下。2M对于大多数情况来说是一个比较合理的设置。

• write_buffer_size = 2M

write_buffer_size参数定义了MyISAM表写操作的缓冲区大小。当MyISAM表的数据文件需要扩展时，MySQL会使用这个缓冲区来收集插入操作，然后一次性写入磁盘。这个参数的默认值通常较小，而2M的设置可以提高写入性能，特别是在有大量插入操作的情况下。

• innodb_flush_log_at_trx_commit

对于 MySQL 的 InnoDB 存储引擎来说非常重要，它直接影响数据库的事务安全性（ACID中的持久性）和性能。这个参数有三个可选值：1、2、0，每个值对应不同的日志刷新行为：

innodb_flush_log_at_trx_commit = 1

这是默认设置，提供最高级别的数据持久性保证。在这种模式下，每次事务提交时，InnoDB 都会将日志写入日志文件并立即刷新到磁盘。这确保了即使发生崩溃，最近提交的事务也不会丢失。虽然这提供了最好的数据安全性，但可能会因为每个事务都需要进行磁盘I/O操作而降低性能。 innodb_flush_log_at_trx_commit = 2

在这个设置下，每次事务提交时，InnoDB 会将日志写入日志文件，但不是立即刷新到磁盘，而是每秒刷新一次。这种方式提高了性能，因为减少了磁盘I/O操作的次数，但是在发生崩溃的情况下，最后一秒内的事务可能会丢失。这是一个折中方案，适用于那些可以接受极端情况下少量数据丢失的场景。 innodb_flush_log_at_trx_commit = 0

在这种模式下，InnoDB 不会在每次事务提交时写入日志文件，而是每秒写入一次。这意味着如果数据库崩溃，你可能会丢失最近一秒内的所有事务。这种设置提供了最高的性能，但牺牲了数据的持久性保证。 选择哪种模式取决于你的具体需求。如果你的应用对数据的完整性有严格的要求，建议使用默认的设置 1。如果性能是你的主要关注点，并且可以接受极端情况下的少量数据丢失，可以考虑使用 2 或 0。

• innodb_doublewrite

是 MySQL 中的一个配置参数，它控制着 InnoDB 存储引擎的双写缓冲区（doublewrite buffer）功能。这个功能对于保护数据库在系统崩溃或硬件故障情况下的数据完整性至关重要。

双写缓冲区的工作原理： 当启用双写缓冲区时（innodb_doublewrite=1，默认设置），InnoDB 在将数据页写入表空间文件之前，会先将其写入到一个称为双写缓冲区的特殊区域。然后，这些数据页会从双写缓冲区被写入到实际的表空间文件中。如果在写入过程中发生崩溃，InnoDB 可以在重启时使用双写缓冲区中的副本来恢复那些可能已损坏的页。

双写缓冲区的优点： 数据完整性保护：双写缓冲区提供了一种机制，确保即使在发生崩溃的情况下，也能防止页面部分写入（partial page writes）导致的数据损坏。这对于确保数据库的持久性和数据一致性至关重要。

恢复加速：在系统崩溃后，双写缓冲区可以加速数据恢复过程，因为它提供了一个额外的数据副本来修复可能损坏的页。

双写缓冲区的缺点： 性能开销：由于每次写入操作都需要额外写入双写缓冲区，因此可能会引入一定的性能开销。对于写密集型应用来说，这可能会成为性能瓶颈。 配置建议： 对于大多数生产环境，推荐保持 innodb_doublewrite 参数启用（默认设置），以保证高数据完整性和安全性。

在某些特殊情况下，如果确定系统的硬件已经提供类似的数据保护机制，或者对性能有极端要求并且可以接受较低的数据安全保障，可能会考虑禁用双写缓冲区（innodb_doublewrite=0）。然而，这种做法需要非常谨慎，并且充分评估相关风险。

总的来说，innodb_doublewrite 参数是 InnoDB 数据完整性策略的一个重要组成部分，对于绝大多数场景，建议保持其默认启用状态，除非有充分的理由和充分的风险评估来支持更改这一设置。

• innodb_flush_method

参数控制了 InnoDB 存储引擎如何将数据和日志刷新到磁盘。这个参数的设置直接影响到数据库的性能以及数据的安全性，因为它涉及到数据写入磁盘的方式。不同的设置选项可以在不同的操作系统和硬件配置上提供更好的性能或更高的数据安全性。

innodb_flush_method 的主要设置选项包括：
fdatasync：使用 fdatasync() 函数来刷新数据到磁盘。这个方法确保数据的持久性，但不一定刷新文件的元数据（如最后修改时间）。这是 Linux 系统上的默认设置。
O_DIRECT：绕过操作系统的缓存，直接将数据写入磁盘。这可以减少双重缓存（应用程序和操作系统层面的缓存）带来的开销，通常在具有大量内存且缓存命中率较高的系统上表现较好。
O_DSYNC：类似于 fdatasync，但是在某些平台上，O_DSYNC 在写入时同时更新文件的元数据。
ALL_O_DIRECT：对数据文件和日志文件都使用 O_DIRECT 方式进行写入。这种方式尝试最小化操作系统缓存的影响，可能会提高某些负载下的性能。 选择合适的 innodb_flush_method： 选择哪种刷新方法取决于多个因素，包括你的操作系统、硬件配置（特别是存储系统），以及你对性能和数据安全性的需求。例如：

如果你在使用 SSD，并且关心写入放大（write amplification）和性能，可能会考虑使用 O_DIRECT 或 ALL_O_DIRECT 以减少不必要的缓存层。 对于需要最大化数据安全性的环境，确保选择的方法能够及时将数据刷新到磁盘是至关重要的。 注意事项： 使用 O_DIRECT 或 ALL_O_DIRECT 可能需要确保数据库文件和日志文件的对齐，特别是在使用 SSD 的情况下，以获得最佳性能。 不同的 innodb_flush_method 设置可能会对 I/O 子系统的压力产生显著影响，建议在生产环境中应用任何更改之前进行充分的测试。 操作系统和 MySQL 版本也可能影响可用的 innodb_flush_method 选项，因此在尝试更改此设置之前，请参考当前 MySQL 版本的官方文档。 总结来说，innodb_flush_method 是一个重要的配置参数，它需要根据具体的运行环境和需求谨慎设置。正确的设置可以帮助平衡性能和数据安全性。

备份数据库并恢复

[root@localhost mes]# docker exec -it fcat-mysql  bash
root@fee2d8c35218:/# mysqldump -u root -p123456 db1 > db1.sql
root@fee2d8c35218:/# CREATE DATABASE db2;
root@fee2d8c35218:/# mysql -u root -p123456 db2 < db1.sql

修改数据库密码

[root@localhost mes]# docker exec -it fcat-mysql  bash
root@fee2d8c35218:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.24 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'root'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.04 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.05 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)