构建生产环境下mysql8.0.20容器
案例
使用docker构建mysql8.0.20容器
步骤1
mkdir -p /data/mysql8.0.20/conf
把优化好的my.cnf 放到 /data/mysql8.0.20/conf/ 目录下
touch /data/mysql8.0.20/conf/my.cnf && chmod -R 777 /data
可参考的 /data/mysql8.0.20/conf/my.cnf 文件内容如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | [mysqld] server-id=1 skip-host-cache skip-name-resolve datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock secure-file-priv=/var/lib/mysql-files user=mysql max_connections=16384 lower-case-table-names=1 default-storage-engine=INNODB innodb-print-all-deadlocks = true max_binlog_size=500M sort_buffer_size=128M sync_binlog=1 binlog_rows_query_log_events=1 binlog_row_image='full' log_output=file slow_query_log=ON slow_query_log_file=/var/mysql/log/mysql-slow.log log_queries_not_using_indexes=off long_query_time=3 log_bin=ON binlog_format=ROW binlog_expire_logs_seconds=604800 binlog_cache_size = 1M binlog_row_event_max_size=16K wait_timeout=28800 interactive_timeout=28800 master_info_repository=TABLE relay_log_info_repository=TABLE autocommit=ON innodb_buffer_pool_size=8G innodb_buffer_pool_chunk_size=2G innodb_buffer_pool_instances=4 innodb_log_file_size = 512M innodb_log_buffer_size=32M innodb_thread_concurrency=17 innodb_autoextend_increment=64 innodb_concurrency_tickets=5000 innodb_old_blocks_time=1000 innodb_stats_on_metadata=0 innodb_file_per_table=1 innodb_checksum_algorithm=0 join_buffer_size=40M sort_buffer_size=128M read_rnd_buffer_size=128M max_allowed_packet=128M table_definition_cache=32767 sync_master_info=10000 sync_relay_log=10000 sync_relay_log_info=10000 event_scheduler=ON innodb_flush_neighbors=1 innodb_page_size=16K innodb_read_io_threads=8 innodb_write_io_threads=8 innodb_flush_log_at_trx_commit=1 flush_time=0 thread_stack=256k thread_cache_size=128 open_files_limit=65535 tmp_table_size=500M max_heap_table_size=500M symbolic-links=0 log-error=/var/mysql/log/mysql-error.log pid-file=/var/run/mysqld/mysqld.pid sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION [client] port=3306 socket=/var/lib/mysql/mysql.sock [mysqld_safe] open-files-limit=32767 innodb_open_files=32767 |
步骤2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | docker run \ -p 3306:3306 \ --name mysql8.0.20 \ --privileged=true \ --restart always \ -d -v /data/mysql8.0.20/data:/var/lib/mysql \ -v /data/mysql8.0.20/mysql-files:/var/lib/mysql-files \ -v /data/mysql8.0.20/conf/my.cnf:/etc/mysql/my.cnf \ -v /data/mysql8.0.20/logs:/var/mysql/log \ -v /etc/localtime:/etc/localtime \ -e MYSQL_ROOT_PASSWORD=Password@mysql \ mysql:8.0.20 \ --lower_case_table_names=1 \ --default-authentication-plugin=mysql_native_password \ --explicit_defaults_for_timestamp=true \ --character-set-server=utf8mb4 \ --collation-server=utf8mb4_general_ci |
设置权限并重启容器
1 2 3 | chmod 755 -R /data/ chmod 777 -R /data/mysql8.0.20/logs/ docker restart mysql8.0.20 |
步骤3 验证mysql容器是否做过优化
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | docker exec -it mysql8.0.20 /bin/bash root@6eb83e59eef8:/# mysql -uroot -p'Password@mysql' mysql> show variables like'%max_connections%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | max_connections | 16384 | | mysqlx_max_connections | 100 | +------------------------+-------+ 2 rows in set (0.00 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2022-12-03 15:22:18 | +---------------------+ 1 row in set (0.00 sec) |
