docker-compose标准化部署MySQL

mkdir  /data/mysql_data/{dbdata,run_mysqld,cnf} -p

cd /data/mysql_data

cat > docker-compose.yml  << EOF
version: '2.1'
services:
    mysql:
        environment:
            TZ: "Asia/Shanghai"
            MYSQL_ROOT_PASSWORD: "root.COM2020" 
        user: "1001:1001"
        image: mysql:8.0.28
        container_name: "mysql6033"
        security_opt:
            - seccomp:unconfined
        restart: always
        volumes:  
            - "/data/mysql_data/dbdata:/var/lib/mysql"
            - "/data/mysql_data/run_mysqld:/var/run/mysqld"            
            - "/data/mysql_data/cnf/my.cnf:/etc/mysql/conf.d/my.cnf"  
        ports:  
            - "6033:3306"
        command: ["--lower-case-table-names=1"] 
EOF


cat > cnf/my.cnf  << EOF
[mysql]  
port=3306  
prompt="\\u@\\h [\\d]>"  
no-auto-rehash

[mysqld]
default_authentication_plugin=mysql_native_password
port=3306 
log-error=/var/lib/mysql/mysql-error.log  
datadir=/var/lib/mysql  
socket=/var/lib/mysql/mysql.sock  
max_allowed_packet=1073741824  
skip-name-resolve=1 
lower_case_table_names=1  
character_set_server=utf8mb4
collation_server=utf8mb4_general_ci
sql_mode=""

#performance setting
lock_wait_timeout = 3600
open_files_limit = 65535
back_log = 1024
max_connections=600
max_connect_errors=100000 
table_open_cache = 1024
table_definition_cache = 1024
thread_stack = 512K
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
connect_timeout=600
net_read_timeout=600
tmp_table_size = 32M
max_heap_table_size = 32M

#innodb 
innodb_buffer_pool_size=5G
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:50M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_max_undo_log_size = 4G
#innodb_io_capacity = 4000
#innodb_io_capacity_max = 8000  
innodb_open_files = 65535 
innodb_flush_method = O_DIRECT 
innodb_lru_scan_depth = 4000
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1 
innodb_print_all_deadlocks = 1
innodb_online_alter_log_max_size = 4G 
innodb_print_ddl_logs = 1
innodb_status_file = 1

#slow log  
slow_query_log = 1
long_query_time = 1
log-queries-not-using-indexes = 1
log_throttle_queries_not_using_indexes = 60
slow_query_log_file = /var/lib/mysql/slow.log

#binlog  
server_id=100
log-bin  
binlog_format=row
log_bin_trust_function_creators=on
max_binlog_size = 1G  
binlog_row_image = full 
sync_binlog = 1 
binlog_cache_size = 4M 
max_binlog_cache_size = 2G

#gtid  
#gtid_mode=on  
#enforce_gtid_consistency=on  
#log-slave-updates=1  
#log_replica_updates=1
EOF



chown -R 1001:1001 *


docker-compose up -d


mysql  -h10.21.53.19   -uroot -P6033 -p"root.COM2020" 

docker logs -f --tail 100 mysql6033

mysql_config_editor set --login-path=root-10.21.53.19  --user=root --host=10.21.53.19 --port=6033  --password

mysql --login-path=root-10.21.53.19