MySQL(及其同门MariaDB)无疑在开源数据库中使用量依然是最高的，仅 WordPress 就声称互联网上近一半的网站都是用的它，而 WordPress 使用的正是 MySQL。本文中不对基础的 CRUD 操作进行缀述，主要记录一些常用配置以及对 高性能MySQL第4版（Early Release，英文版尚未出版）中的一些学习笔记。
官方示例数据库，如sakila：https://dev.mysql.com/doc/index-other.html
workbench：https://dev.mysql.com/downloads/workbench/
一条语句的查询：连接器->分析器->优化器->执行器
WAL: Write-Ahead Logging
性能相关
操作系统查看磁盘 iops（每秒读写）
|
1 2 3 |
fio -filename=io_test -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest # 对应 MySQL配置参数 innodb_max_dirty_pages_pct |
查看影响性能的锁：information_schema中的长事务表 innodb_trx， 锁：innodb_locks，阻塞事务 innodb_lock_waits
8.0新增 performance_schema 库，锁：data_locks，阻塞事务 data_lock_waits，MDL锁：metadata_locks
慢查询命令行设置
|
1 2 3 |
SET GLOBAL slow_query_log_file='/var/log/mysql/mysql-slow.log'; SET GLOBAL slow_query_log=1; SET @@GLOBAL.long_query_time = 1; |
常用命令整理
|
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 |
########### 事务和锁 ########### # 查询执行中的事务 SELECT * FROM information_schema.INNODB_TRX; # 查看正在锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; # 查看等待锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; # 查询事务隔离级别 show variables like 'transaction_isolation'; # 查询长事务（如超过60秒） select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60 # 重建主键 alter table T engine=InnoDB ########### binlog ########### # 配置 server_id = 1 log_bin = /var/log/mysql/mysql-bin.log # log_bin = mysql-bin max_binlog_size = 1G binlog_format = row binlog_row_image = full expire_logs_days = 7 # 读取 binlog mysqlbinlog -v --base64-output=DECODE-ROWS /var/log/mysql/xxx.log > xxx.txt # 还原 mysqlbinlog -v --base64-output=DECODE-ROWS --start-position=xxx --stop-position=xxx /var/log/mysql/bin-log.000001 ########### 账户相关 ########### ALTER USER 'userxxx'@'xx.xx.xx.xx' ACCOUNT LOCK; # 账号锁定 ALTER USER 'userxxx'@'xx.xx.xx.xx' ACCOUNT UNLOCK; # 账号解锁 ########### 主从同步 ########### # 主库需配置 server_id、binlog并设置权限 CREATE USER repl_user IDENTIFIED BY 'repl_passwd'; grant replication slave on *.* to 'repl_user'@'xx.xx.xx.xx' identified by 'repl_passwd'; FLUSH PRIVILEGES; # 查看状态，其中的文件名在从库命令中的master_log_file需使用 show master status; # 从库配置 server-id=2 replicate-rewrite-db = source_db->dst_db # 如数据库名称不致 relay-log=relay-bin read-only =0 replicate-ignore-db = mysql replicate-ignore-db = test replicate-ignore-db = information_schema replicate-do-table= dst_db.dst_tbl # 如需仅同步指定数据表 # MySQL命令行指定主库 change master to master_host='xx.xx.xx.xx',master_port=3306,master_user='repl_user',master_password='repl_passwd',master_log_file=’master-bin.000001’,master_log_pos=xxx; # 开启/关闭 start slave; stop slave; # 命令行查看 show global variables like ‘%log_bin%’; show slave status\G # 备份 mysqldump # 常用参数：在同一事务中保持数据一致性：--single-transaction 全量备份的同时配合 binlog 增量备份（新记录写入binlog，并进行记录）--flush-logs --master-data=2 XtraBackup |
MySQL审计
MySQL社区版虽然支持审计功能，但并没有自带 server_audit.so，需要通过 MariaDB 的安装包进行添加。下载时版本需要对应，MySQL 5.7对应：https://downloads.mariadb.org/mariadb/5.5/。bin_log 可以帮我们记录一切操作，进而做数据恢复和主从同步等操作，而审计更多地用于记录操作以及所执行的用户、IP 等信息，因而可以追踪到异常操作，或者简单说确定应该由谁来背锅，所以一般不需要全量记录，server_audit_events即可配置进行记录的事件。
快速下载地址：mariadb-5.5.68-linux-x86_64.tar.gz，获取mariadb-5.5.68-linux-x86_64/lib/plugin/server_audit.so
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SHOW GLOBAL VARIABLES LIKE 'plugin_dir'; # 将server_audit.so放到对应目录，如 /usr/lib/mysql/plugin/ # 安装 INSTALL PLUGIN server_audit SONAME 'server_audit.so'; show variables like '%audit%'; set global server_audit_events='CONNECT,QUERY_DML_NO_SELECT'; #开启审计功能 set global server_audit_logging=on; #默认存放路径，可以不写，默认到data文件下 set global server_audit_file_path=server_audit.log; # /var/lib/mysql/server_audit.log #设置文件大小 set global server_audit_file_rotate_size=100000000; # 单位为B # 指定日志文件的数量，如果为0日志将从不轮转 set global server_audit_file_rotations=4; # 强制日志文件在当前时间点切割 set global server_audit_file_rotate_now=ON; # 卸载 UNINSTALL PLUGIN audit_log; |
| Type | Description | Introduced |
|---|---|---|
| CONNECT | Connects, disconnects and failed connects—including the error code | |
| QUERY | Queries executed and their results in plain text, including failed queries due to syntax or permission errors | |
| TABLE | Tables affected by query execution | |
| QUERY_DDL | Similar to QUERY, but filters only DDL-type queries (CREATE, ALTER, DROP, RENAME and TRUNCATE statements—except CREATE/DROP [PROCEDURE / FUNCTION / USER] and RENAME USER (they're not DDL) | MariaDB 5.5.42. MariaDB 10.0.17, MariaDB 10.1.4 |
| QUERY_DML | Similar to QUERY, but filters only DML-type queries (DO, CALL, LOAD DATA/XML, DELETE, INSERT, SELECT, UPDATE, HANDLER and REPLACE statements) | MariaDB 5.5.42, MariaDB 10.0.17, MariaDB 10.1.4 |
| QUERY_DML_NO_SELECT | Similar to QUERY_DML, but doesn't log SELECT queries. (since version 1.4.4) (DO, CALL, LOAD DATA/XML, DELETE, INSERT, UPDATE, HANDLER and REPLACE statements) | MariaDB 5.5.42, MariaDB 10.0.17, MariaDB 10.1.4 |
| QUERY_DCL | Similar to QUERY, but filters only DCL-type queries (CREATE USER, DROP USER, RENAME USER, GRANT, REVOKE and SET PASSWORD statements) | MariaDB 5.5.43, MariaDB 10.0.18, MariaDB 10.1.5 |
配置文件
|
1 2 3 4 5 6 7 |
[mysqld] plugin_load_add=server_audit.so # 防止插件卸载 server_audit_events='CONNECT,QUERY_DML_NO_SELECT' server_audit_logging=on server_audit_file_rotate_size=100000000 server_audit_file_rotations=7 |
来自网络带说明的配置
|
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 |
[client] port=3306 default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] log-error=/mysql/logs/error.log # 慢查询 slow_query_log = on long_query_time=2 slow-query-log-file =/var/log/mysql/mysql-slow.log secure-file-priv='' character-set-client-handshake = FALSE #服务器端的端口号 port=3306 #MySQL数据库数据文件的目录 datadir=/mysql/data character-set-server = utf8mb4 collation-server = utf8mb4_general_ci init_connect='SET NAMES utf8mb4' #MySQL软件的存储引擎 default-storage-engine=INNODB # Set the SQL mode to strict #MySQL软件的SQL模式 sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" #MySQL软件的最大连接数 max_connections=200 #MySQL软件的查询缓存 query_cache_size=0 #MySQL软件内存中可以存储临时表的最大值 tmp_table_size=11M #MySQL软件中可以保留的客户端连接线程数 thread_cache_size=8 #MySQL软件重建索引时允许的最大临时文件的大小 myisam_max_sort_file_size=100G #MySQL软件重建索引时允许的最大缓存大小 myisam_sort_buffer_size=22M #MySQL软件中最大关键字缓存大小 key_buffer_size=10M #MySQL软件全扫描MyISAM表时的缓存大小 read_buffer_size=64K #MySQL软件可以插入排序好数据的缓存大小 read_rnd_buffer_size=256K #MySQL软件用户排序时缓存大小 sort_buffer_size=256K #*** INNODB Specific options *** #关于INNODB存储引擎参数设置 #关于提交日志的时机,0表示每秒redo log从内存刷入硬盘，1表示每个事务刷盘，依此类推，binlog将事务写入磁盘的配置为sync_binlog=1 innodb_flush_log_at_trx_commit=1 #存储日志数据的缓存区的大小 innodb_log_buffer_size=1M #缓存池中缓存区大小 innodb_buffer_pool_size=52M #日志文件的大小 innodb_log_file_size=26M #允许线程的最大数 innodb_thread_concurrency=9 |
SQL
|
1 2 |
# 时间戳转化为日期 SELECT DATE_FORMAT(FROM_UNIXTIME(`timestamp_field`), '%Y-%m-%d') AS 'date_formatted' |
MariaDB
|
1 2 3 4 |
sudo apt update sudo apt install mariadb-server sudo systemctl start mariadb.service sudo mysql_secure_installation |
Docker 相关
|
1 2 3 4 5 6 7 |
mkdir data logs # vi my.cnf docker run --name mysql -d \ -v /home/xxx/mysql:/mysql \ -v /home/xxx/mysql/my.cnf:/etc/mysql/my.cnf \ -e MYSQL_ROOT_PASSWORD=xxxx \ mysql:5.7 |
1、[ERROR] Could not open file ‘/mysql/logs/error.log’ for error logging: Permission denied
|
1 2 3 4 |
# 查询组 id docker run -it --rm --entrypoint="/bin/bash" mysql:5.7 -c "cat /etc/group" # 得到999，设置权限 sudo chown -R 999 logs |
高性能MySQL第4版
|
1 2 3 4 5 6 7 8 9 |
/proc/sys/net/ipv4/ip_local_port_range /proc/sys/net/ipv4/tcp_max_syn_backlog /proc/sys/net/ipv4/tcp_fin_timeout oom_adj or oom_score_adj echo 0 > /proc/sys/vm/swappiness vmstat 5 # 5表示每5秒钟，单位是 block，通常为1024 Bytes iostat -dx 5 # Little's law concurrency = (r/s + w/s) * (svctm/1000) |
文件系统推荐XFS，ext4也可接受
http://collectl.sourceforge.net/
查询配置文件位置和加载顺序
|
1 |
mysqld --verbose --help | grep -A 1 'Default options' |
MySQL最小化配置文件（MySQL 8.0）
|
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 |
[mysqld] # GENERAL datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock pid_file = /var/lib/mysql/mysql.pid user = mysql port = 3306 # INNODB innodb_buffer_pool_size = <value> innodb_log_file_size = <value> innodb_file_per_table = 1 innodb_flush_method = O_DIRECT # LOGGING log_error = /var/lib/mysql/mysql-error.log log_slow_queries = /var/lib/mysql/mysql-slow.log # OTHER tmp_table_size = 32M max_heap_table_size = 32M max_connections = <value> thread_cache_size = <value> table_open_cache = <value> open_files_limit = 65535 # 可以更大，开销较小 [client] socket = /var/lib/mysql/mysql.sock port = 3306 |
innodb_dedicated_server通常占用50% – 75%的内存
innodb_thread_concurrency可先设置成和 CPU核数相同，然后再进行调优
