mysql

0、需要的文件

• mysql安装包
• my.cnf 配置文件
• mysql-start.sh 启动脚本

1、启动脚本文件mysql-start.sh

脚本链接

$ cat mysql-start.sh
#!/bin/bash
set -o errexit #运行某一行出错时立即退出。
set -- mysqld_safe $@ #定义启动命令
echo "\$@:$@"
#初始化传值变量
db_user=${db_user:=""}
db_password=${db_password:=""}
#如果是变量值为root则重新赋值为空
if [ "${db_user}" = "root" ]
  then db_user=""
fi
echo "db_user=$db_user"
echo "db_password=$db_password"
echo "----开始初始化mysql----------------------"
if [ ! -f /data/mysql/logs/mysql.err ];then
        mkdir -p  /data/mysql/{data,logs,conf}
        touch /data/mysql/logs/mysql.err
        chown -R mysql.mysql /data
        /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data
        cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
        chmod 777 /etc/init.d/mysqld
        ln -s /usr/local/mysql/bin/* /usr/bin/
        echo "初始化数据库完毕.数据持目录:/data/mysql"
elif [ ! -f /etc/init.d/mysqld ];then
        cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
        chmod 777 /etc/init.d/mysqld
        ln -s /usr/local/mysql/bin/* /usr/bin/
fi
echo "设置变量"
passwd="$(awk '/localhost/{print $NF}' /data/mysql/logs/mysql.err|head -1)"
mysql=( mysql --protocol=socket -uroot -hlocalhost --socket="/data/mysql/mysql.sock" )
dbinit=( mysql --connect-expired-password -uroot -p$passwd )
echo "启动数据库"
#启动数据库并判断状态
/etc/init.d/mysqld restart && sleep 3
echo "启动后查看进程"
port=$(ss -lntp|grep "3306"|wc -l)
for p in {3..0}; do
  if [ $port -ne 1 ];then
     echo "Mysql启动失败-重新启动"
     /etc/init.d/mysqld restart && sleep 2
  else
       echo "Mysql 已经启动"
       break
  fi
  if [ "$p" = 0 ];then
    echo >&2 'MySQL 启动失败!'
        exit 1
  fi
done
echo "开始初始化用户密码"
#passinit="alter  user 'root'@'localhost' identified by '';GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '' WITH GRANT OPTION;"
#if [ ! -f /data/mysql/logs/mysql.err ];then
passinit="alter  user 'root'@'localhost' identified with mysql_native_password by '';CREATE USER IF NOT EXISTS 'root'@'%';ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password by ''; grant all on *.* to 'root'@'%' with grant option"
echo "${passinit}"| "${dbinit[@]}" &> /dev/null && echo "初始化成功"
#fi
#再次重启
echo "再次重启验证密码"
for i in {2.0}; do
     if echo 'SELECT 1' | "${mysql[@]}" &> /dev/null; then
        echo 'MySQL 验证空密码成功!'
            break
     else
        sleep 1;echo "重新验证"
    fi
    if [ "$i" = 0 ]; then
    echo >&2 'MySQL 验证空密码失败!'
        exit 1
    fi
done
echo "开始进入匹配..."
if [ "${db_user}" != "" -o "${db_password}" != "" ];then
        echo "指定了变量--"
        echo "进入匹配开始"
        #用户不为空,密码不为空---创建用户名 密码,默认授权管理员权限
        if [ "${db_user}" != "" -a "${db_password}" != "" -a "${db_database}" = "" ];then
                c="CREATE USER IF NOT EXISTS '"${db_user}"'@'%'; ALTER USER '"${db_user}"'@'%' IDENTIFIED WITH mysql_native_password by '"${db_password}"'; GRANT ALL PRIVILEGES ON *.* TO '"${db_user}"'@'%' WITH GRANT OPTION;flush privileges;"
                echo "${c}"|"${mysql[@]}" &> /dev/null && echo "添加成功" || echo "创建用户失败"
        #用户名为空,密码不为空,数据库为空-------------修改root密码
        elif [ "${db_user}" = "" -a "${db_password}" != "" -a "${db_database}" = "" ];then
                c="ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password by '"${db_password}"'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;flush privileges;"
        echo $c
                echo "${c}"|"${mysql[@]}" &> /dev/null &&  echo "添加成功" || echo "修改密码失败"
        else
                echo "没有匹配项目"
                echo "help:docker xxx image db_user=xx db_password=xx db_database=xx"
                exit 1
        fi
else
        echo "没有指定变量:用户root密码为空"
fi
/etc/init.d/mysqld stop && sleep 1 && echo "关闭成功"
echo "$@" && exec $@

2、Dockerfile

vi .dockerignore
!my.cnf
!mysql-start.sh
!

#FROM centos
FROM geray/centos:v7-1
MAINTAINER "Geray <1690014753@qq.com>"
ENV HOSTNAME=mysql
ENV MYSQL_HOME="/usr/local/mysql"
ENV MYSQL_TAR="mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz"
ENV MYSQL_PATH_SRC_NAME="mysql-8.0.26-linux-glibc2.12-x86_64"
COPY mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz /usr/local/src/
#COPY my.cnf /etc/
# COPY mysql-start.sh /usr/bin/
RUN yum install -y ncurses-devel libaio libaio-devel numactl \
   && groupadd mysql \
   && useradd -r -g mysql -s /bin/false mysql \
   && tar xvf /usr/local/src/${MYSQL_TAR} -C /usr/local/ \
   && mv /usr/local/${MYSQL_PATH_SRC_NAME} ${MYSQL_HOME} \
   && mkdir ${MYSQL_HOME}/mysql-files/ \
   && chown -R mysql:mysql ${MYSQL_HOME} \
   && chmod 750 ${MYSQL_HOME}/mysql-files/ \
   && ln -s ${MYSQL_HOME}/bin/mysql /usr/bin \
   #&& ln -s ${MYSQL_HOME}/bin/mysqld /usr/bin \
   #&& ln -s ${MYSQL_HOME}/bin/mysql_ssl_rsa_setup /usr/bin \
   #&& ln -s ${MYSQL_HOME}/bin/mysqld_safe /usr/bin \
   && rm -rf /usr/local/src/*gz \
   && yum clean all \
   && rm -rf /var/cache/yum/*
EXPOSE 3306
ENTRYPOINT ["mysql-start.sh"]
CMD ["--user=mysql"]

3、my.cnf

[client]
port    = 3406
socket    = /tmp/mysql.sock
[mysql]
prompt="\u@db \R:\m:\s [\d]> "
no-auto-rehash
[mysqld]
server-id = 3306100
user    = mysql
port    = 3306
basedir    = /usr/local/mysql
datadir    = /data/mysql/
socket    = /tmp/mysql.sock
pid-file = db.pid
character-set-server = utf8mb4
skip_name_resolve = 1
open_files_limit    = 65535
back_log = 1024
max_connections = 512
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
slow_query_log_file = /data/mysql/slow.log
log-error = /data/mysql/error.log
long_query_time = 0.1
log-bin = /data/mysql/mysql-binlog
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 1G
max_binlog_size = 1G
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
#myisam_sort_buffer_size = 128M
#myisam_max_sort_file_size = 10G
#myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
master_info_repository = TABLE
relay_log_info_repository = TABLE
slave_parallel_type=LOGICAL_CLOCK
transaction_isolation = REPEATABLE-READ
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1024M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
#innodb_max_undo_log_size = 1G
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
innodb_stats_on_metadata = 0
innodb_status_file = 1
# 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快
innodb_status_output = 0
innodb_status_output_locks = 0
#performance_schema
performance_schema = 1
performance_schema_instrument = '%=on'
#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"
 # Group Replication
#server_id = 1003306
#gtid_mode = ON
#enforce_gtid_consistency = ON
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
binlog_checksum = NONE
#log_slave_updates = ON
#log_bin = binlog
#binlog_format= ROW
transaction_write_set_extraction = XXHASH64
# 未来可能被弃用的变量，会出现告警信息，binlog_expire_logs_seconds用来代替
# expire_logs_days = 7
binlog_expire_logs_seconds = 7
# 8版本弃用的变量
# loose-group_replication_group_name = 'e842862c-9b12-11e8-8131-080027f1fd08'
# internal_tmp_disk_storage_engine = InnoDB # 8版本不再支持，默认引擎
# query_cache_size = 0 # 8版本不再支持这两个参数
# query_cache_type = 0
# loose-group_replication_start_on_boot = off
# loose-group_replication_local_address = 'enmoedu:33066'
# loose-group_replication_group_seeds ='enmoedu1:33067,enmoedu2:33068,enmoedu:33066'
# loose-group_replication_bootstrap_group = off
# loose-group_replication_single_primary_mode=off
# loose-group_replication_enforce_update_everywhere_checks=true
[mysqldump]
quick
max_allowed_packet = 32M

4、构建

docker build -t geray/mysql:8.0.26 .
docker run -p3306:3306 -e db_password="1234" -v /database/data/:/data/mysql -d --name="mysql-test" geray/mysql:8.0.26