mysql
启动
Tip
注：mysql5.5、mysql5.6和mysql5.7的配置基本相同
mysql5.5、mysql5.6只需去掉--log-timestamps=SYSTEM即可
#!/bin/bash
echo 'Asia/Shanghai' > /etc/timezone
######################
# mysql 8.0 启动模板
#######################
port=3306
mysqlx_port=$((33060+${port}-3306))
password="password"
mysql_version="8.0"
# mysql 数据文件夹
mkdir -p $(pwd)/log-${port}
# mysql bin-log 文件夹
mkdir -p $(pwd)/data-${port}/log/mysql-bin
# mysql relay-log 文件夹
mkdir -p $(pwd)/data-${port}/log/relay-bin
# 赋权
chown -R 999.root $(pwd)/data-${port}
chown -R 999.root $(pwd)/log-${port}
cat << EOF > $(pwd)/data-${port}/.my.cnf
[client]
user=root
password=${password}
EOF
chown 999:root $(pwd)/data-${port}/.my.cnf
chmod 600 $(pwd)/data-${port}/.my.cnf
container_name="mysql-${port}"
docker stop ${container_name}
docker rm ${container_name}
docker run -d --name ${container_name} \
--restart=always \
--net host \
-v /etc/timezone:/etc/timezone:ro \
-v /etc/localtime:/etc/localtime:ro \
-e TZ=Asia/Shanghai \
-v $(pwd)/data-${port}/.my.cnf:/root/.my.cnf \
-v $(pwd)/data-${port}/mysql:/var/lib/mysql \
-v $(pwd)/data-${port}/log/mysql:/var/log/mysql \
-v $(pwd)/data-${port}/log/mysql-bin:/var/log/mysql-bin \
-v $(pwd)/data-${port}/log/relay-bin:/var/log/relay-bin \
-v $(pwd)/log-${port}/:/var/log/mysql \
-e MYSQL_ROOT_PASSWORD=${password} \
--health-cmd="mysqladmin ping -h 127.0.0.1 -P ${port} -u root -p${password}" \
--health-interval=10s \
--health-timeout=5s \
--health-retries=5 \
mysql:${mysql_version} \
--port=${port} \
--mysqlx-port=${mysqlx_port} \
--pid-file=/var/lib/mysql/mysql.pid \
--socket=/var/lib/mysql/mysql.sock \
--datadir=/var/lib/mysql \
--default-time-zone='+8:00' \
--log-timestamps=SYSTEM \
--default-storage-engine=innodb \
--innodb-file-per-table \
--character-set-server=utf8mb4 \
--collation-server=utf8mb4_general_ci \
--init-connect='SET NAMES utf8mb4' \
--lower-case-table-names=1 \
--performance-schema=ON \
--skip-name-resolve \
--innodb-buffer-pool-size=32M \
--federated \
--skip-external-locking \
--myisam-recover-options=FORCE,BACKUP \
--max-allowed-packet=16M \
--max-connect-errors=1000000 \
--max-connections=300 \
--key-buffer-size=32M \
--tmp-table-size=32M \
--max-heap-table-size=32M \
--thread-cache-size=50 \
--open-files-limit=65535 \
--table-definition-cache=1024 \
--table-open-cache=2048 \
--innodb-flush-method=O_DIRECT \
--innodb-log-files-in-group=2 \
--innodb-log-file-size=48M \
--innodb-flush-log-at-trx-commit=1 \
--innodb-file-per-table=1 \
--innodb-buffer-pool-size=256M \
--log-error=/var/log/mysql/mysql-error.log \
--slow-query-log=1 \
--slow-query-log-file=/var/log/mysql/mysql-slow.log \
--log-output='FILE' \
--long-query-time=1 \
--log-queries-not-using-indexes=off \
--log-throttle-queries-not-using-indexes=1000 \
--symbolic-links=0 \
--server_id=1 \
--sync-binlog=1 \
--log-bin=ON \
--log-bin=/var/log/mysql-bin/mysql-bin \
--log-bin-index=/var/log/mysql-bin/mysql-bin.index \
--binlog-format=ROW \
--binlog-expire-logs-seconds=2592000 \
--max-binlog-size=1G \
--federated \
--skip-external-locking \
--skip-name-resolve \
--sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION \
--authentication-policy=caching_sha2_password \
--default-storage-engine=INNODB \
--binlog-ignore-db=mysql,information_schema,performance_schema \
--auto-increment-increment=1 \
--auto-increment-offset=1 \
--replicate-ignore-db=mysql,information_schema,performance_schema \
--relay-log=/var/log/relay-bin/relay-bin \
--log-replica-updates=ON \
--replica-skip-errors=all \
# 日志切割
function cut_mysql_log {
cat << EOF > /etc/logrotate.d/docker-mysql-${port}
$(pwd)/log-${port}/*.log {
daily
olddir $(pwd)/log-${port}/compress
missingok
rotate 7
compress
dateext
dateformat .%Y%m%d
notifempty
sharedscripts
prerotate
mkdir -p $(pwd)/log-${port}/compress
endscript
postrotate
/usr/bin/docker exec -i ${container_name} mysqladmin --socket=/var/lib/mysql/mysql.sock -uroot -p${password} flush-logs
endscript
}
EOF
sleep 5
logrotate -v -f /etc/logrotate.d/docker-mysql-${port}
echo "日志目录:$(pwd)/data-${port}/mysql"
echo "数据目录:$(pwd)/log-${port}/"
echo "binlog目录:$(pwd)/data-${port}/log/mysql-bin"
}
# 刷新日志切割配置
cut_mysql_log
# binlog-expire-logs-seconds=2592000 binlog 日志保存 30 天
# max-binlog-size=1G 单个 binlog 文件大小
# 进入 mysql 交互
# password=;port=;docker run -it -v ./data-${port}/mysql/mysql.sock:/tmp/mysql.sock --rm mysql:8.0 mysql -uroot -p${password} -S /tmp/mysql.sock
# 执行 sql 文件
# password=;port=;docker run -i -v ./data-${port}/mysql/mysql.sock:/tmp/mysql.sock --rm mysql:8.0 mysql -uroot -p${password} -S /tmp/mysql.sock database_name < select.sql
# 执行 sql 命令
# password=;port=;docker run -i -v ./data-${port}/mysql/mysql.sock:/tmp/mysql.sock --rm mysql:8.0 mysql -uroot -p${password} -S /tmp/mysql.sock -e "select 1"
配置解析
--datadir=/var/lib/mysql # MySQL数据文件的存储目录
--default-time_zone='+8:00' # 默认时区
--default-storage-engine=innodb # 默认存储引擎为InnoDB
--innodb_file_per_table # 为每个InnoDB表创建独立的数据文件
--character-set-server=utf8mb4 # 服务器端字符集为UTF-8mb4
--collation-server=utf8mb4_general_ci # 服务器端字符集的排序规则
--init-connect='SET NAMES utf8mb4' # 在新连接建立时执行的SQL语句
--lower-case-table-names=1 # 表名不区分大小写
--performance_schema=ON # 启用性能模式，用于收集性能相关的统计信息
--sql_mode='' # 设置SQL模式，此处为空表示采用默认模式
--skip-name-resolve # 禁用MySQL的DNS解析，使用IP地址进行连接
--innodb_buffer_pool_size=32M # 设置InnoDB缓冲池大小为32MB
--federated # 启用Federated存储引擎，允许在不同MySQL服务器之间共享数据
--skip-external-locking # 禁用外部锁定，MySQL使用内部锁定管理
--key-buffer-size=32M # 设置MyISAM索引缓存的大小
--myisam_recover_options=FORCE,BACKUP # 指定MyISAM存储引擎的恢复选项
--max-allowed-packet=16M # 设置单个网络数据包的最大大小
--max-connect-errors=1000000 # 在连接错误达到指定次数后，封锁主机
--max_connections=300 # 设置最大同时连接数
--tmp-table-size=32M # 设置临时表的最大大小
--max-heap-table-size=32M # 设置最大堆表的大小
--thread-cache-size=50 # 设置线程缓存的大小
--open-files-limit=65535 # 设置MySQL进程能打开的文件数的最大限制
--table-definition-cache=1024 # 缓存表定义的数量
--table-open-cache=2048 # 缓存打开表的数量
--innodb-flush-method=O_DIRECT # 设置InnoDB刷新日志的方式
--innodb-log-files-in-group=2 # 设置InnoDB日志文件的数量
--innodb-log-file-size=256M # 设置InnoDB日志文件的大小
--innodb-flush-log-at-trx-commit=1 # 每次事务提交时刷新InnoDB日志
--innodb-file-per-table=1 # 为每个InnoDB表创建独立的数据文件
--innodb-buffer-pool-size=1G # 设置InnoDB缓冲池大小为1GB
--log-error=/var/log/mysql/mysql-error.log # 指定错误日志文件的路径
--long-query-time=1 # 指定慢查询的阈值时间
--log-queries-not-using-indexes=off # 关闭记录未使用索引的查询的日志
--log-throttle-queries-not-using-indexes=1000 # 记录未使用索引的查询日志的限制数量
--slow-query-log=1 # 启用慢查询日志
--slow-query-log-file=/var/log/mysql/mysql-slow.log # 指定慢查询日志文件的路径
--symbolic-links=0 # 禁止MySQL在数据目录中使用符号链接
--server_id=1 # 设置MySQL服务器的唯一标识
--sync-binlog=1 # 将二进制日志同步写入磁盘
--log_bin=ON # 启用二进制日志记录
--log_bin=/var/lib/mysql/mysql-bin # 指定二进制日志文件的路径
--log_bin_index=/var/lib/mysql/mysql-bin.index # 指定二进制日志索引文件的路径
--binlog_format=ROW # 设置二进制日志的格式为行级
--expire_logs_days=60 # 指定保留二进制日志的天数
--max_binlog_size=2G # 设置二进制日志文件的最大大小
--federated # 启用Federated存储引擎，允许在不同MySQL服务器之间共享数据
--skip-external-locking # 禁用外部锁定，MySQL使用内部锁定管理
--skip-name-resolve # 禁用DNS解析
--sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION # 设置SQL模式
--default_authentication_plugin=mysql_native_password # 设置默认的身份验证插件
--default-storage-engine=INNODB # 默认存储引擎为InnoDB
--binlog-ignore-db=mysql,information_schema,performance_schema # 指定不记录的数据库
--auto-increment-increment=1 # 设置AUTO_INCREMENT列的增量值
--auto-increment-offset=1 # 设置AUTO_INCREMENT列的偏移量
--replicate-ignore-db=mysql,information_schema,performance_schema # 在复制中要忽略的数据库
--relay_log=/var/log/relay-bin/relay-bin # 中继日志文件的路径
--log-slave-updates=ON # 记录复制更新
--slave-skip-errors=all # 在从库上跳过所有错误启动主从节点
修改启动文件中的--server_id
方法一(保险)
在主节点查询执行到哪里了
show master status;锁定主库数据库表
flush tables with read lock;复制数据到从库
cp -R data-3306 data-3307修改从库的uuid
Tip
在auto.conf中，在别的mysql库中执行SELECT UUID();
解除主库锁定表
unlock tables;从库执行开启从
change master to master_host='xx.xx.xx.xx', master_user='root', master_password='password', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=1;
start slave;方法二
备份
使用备份命令, 然后把备份文件拷贝到新服务器并解压(gzip -d test.sql.gz)
docker run -i --net host --rm -e MYSQL_ROOT_PASSWORD=password mysql:8.0 \
mysqldump --host=127.0.0.1 --port=3306 --user=root --password=password --single-transaction --master-data=2 --all-databases | gzip > test.sql.gz导入数据
mysql -uroot -ppassword < test.sql开启同步
先查看备份的时候是执行到哪里cat test.sql | head -n 40, 拿着这个binlogid和pos在新库中执行命令
reset slave all;
change master to master_host='127.0.0.1', master_user='root', master_password='password', master_port=3306,master_log_file='mysql-bin.000003',master_log_pos=319017483;
start slave;从节点改成主节点
STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL read_only = OFF;操作用户
查询用户
SELECT user FROM mysql.user;创建只读用户
create user 'readonly_user'@'%' identified by 'password';
flush privileges;
GRANT SELECT ON *.* TO 'readonly_user'@'%' WITH GRANT OPTION;修改密码
alter user 'readonly_user'@'%' identified by 'password1';删除用户
DROP USER 'readonly_user'@'%';全库备份
docker run -i --net host --rm -e MYSQL_ROOT_PASSWORD=password mysql:8.0 \
mysqldump --host=127.0.0.1 --port=3306 --user=root --password=password --single-transaction --master-data=2 --all-databases | gzip > test.sql.gz恢复到全库备份的时候
# 解压sql
gzip -d test.sql.gz
# 登录mysql
mysql -uroot -ppassword
# 执行sql
source test.sql;增量备份
自行备份/var/lib/mysql目录下的mysql-bin.xxx
查看全库备份sql中前20行，其中有当前备份执行到哪个binlog的信息
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=156;这时候需要把这个binlog后面的二进制文件按时间段导出成sql
docker run -i --net host --rm -v $(pwd):/tmp/ -e MYSQL_ROOT_PASSWORD=password mysql:8.0 \
bash -c "mysql -h 127.0.0.1 -P 3306 -u root -p\$MYSQL_ROOT_PASSWORD -e 'mysqlbinlog --start-datetime='2024-02-02 12:00:00' --stop-datetime='2024-02-02 17:00:00' /tmp/mysql-bin.000006' > /tmp/006bin.sql"如果想要恢复的场景是因为误删数据，需要打开导出的sql，删除delete语句，示例:
# at 803
#240202 13:06:01 server id 1 end_log_pos 882 CRC32 0x9af10291 Query thread_id=35 exec_time=0 error_code=0
SET TIMESTAMP=1706850361/*!*/;
BEGIN
/*!*/;
# at 882
#240202 13:06:01 server id 1 end_log_pos 938 CRC32 0xbf1da44e Table_map: `etc_plus`.`test` mapped to number 516
/** 删除片段
# at 938
#240202 13:06:01 server id 1 end_log_pos 977 CRC32 0xc1a484e2 Delete_rows: table id 516 flags: STMT_END_F
BINLOG '
OXi8ZRMBAAAAOAAAAKoDAAAAAAQCAAAAAAEACGV0Y19wbHVzAAR0ZXN0AAEPAvwDAQIBLU6kHb8=
OXi8ZSABAAAAJwAAANEDAAAAAAQCAAAAAAEAAgAB/wABADXihKTB
'/*!*/;
**/
# at 977
#240202 13:06:01 server id 1 end_log_pos 1008 CRC32 0x9df867ab Xid = 5176
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;恢复增量备份
# 登录mysql
mysql -uroot -ppassword
# 执行sql
source test.sql;执行sql文件
docker run -i --net host --rm -e MYSQL_ROOT_PASSWORD=password -v ./:/tmp mysql:8.0 \
mysql -h xx.xx.xx.xx -P 3306 -u root mydatabase < /tmp/init.sql查看占用大小
查看数据库的总大小
SELECT table_schema AS `Database`,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS `Size (MB)`
FROM information_schema.tables
WHERE table_schema = '你的数据库名'
GROUP BY table_schema;查看所有数据库的大小
SELECT table_schema AS `Database`,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS `Size (MB)`
FROM information_schema.tables
GROUP BY table_schema
ORDER BY `Size (MB)` DESC;技巧
初始化sql
在docker映射阶段把sql文件映射到/docker-entrypoint-initdb.d中
-v $(pwd)/init-db/:/docker-entrypoint-initdb.d/然后在sql文件中最前面加上以下sql就可以完成建库的时候初始化sql
以下是test库的初始化
CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE test;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;