tt's Note
运维
开发
软件
项目
脚本

mysql

启动

TIP

注：mysql5.5、mysql5.6和mysql5.7的配置基本相同

mysql5.5、mysql5.6只需去掉--log-timestamps=SYSTEM即可

mysql8.0
mysql5.7
#!/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;