MySQL常用操作及读书笔记 | Alan Hou的个人博客

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（每秒读写）

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

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

慢查询命令行设置

SET GLOBAL slow_query_log_file='/var/log/mysql/mysql-slow.log';
SET GLOBAL slow_query_log=1;
SET @@GLOBAL.long_query_time = 1;

SET GLOBAL slow_query_log_file='/var/log/mysql/mysql-slow.log';

SET GLOBAL slow_query_log=1;

SET @@GLOBAL.long_query_time = 1;

常用命令整理

########### 事务和锁 ###########
# 查询执行中的事务 
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

########### 事务和锁 ###########

# 查询执行中的事务

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

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;

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

配置文件

[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

[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

来自网络带说明的配置

[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

[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

# 时间戳转化为日期
SELECT DATE_FORMAT(FROM_UNIXTIME(`timestamp_field`), '%Y-%m-%d') AS 'date_formatted'

1 2	# 时间戳转化为日期 SELECT DATE_FORMAT(FROM_UNIXTIME(`timestamp_field`), '%Y-%m-%d') AS 'date_formatted'

MariaDB

sudo apt update
sudo apt install mariadb-server
sudo systemctl start mariadb.service
sudo mysql_secure_installation

sudo apt update

sudo apt install mariadb-server

sudo systemctl start mariadb.service

sudo mysql_secure_installation

Docker 相关

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

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

# 查询组 id
docker run -it --rm --entrypoint="/bin/bash" mysql:5.7 -c "cat /etc/group"
# 得到999，设置权限
sudo chown -R 999 logs

# 查询组 id

docker run -it --rm --entrypoint="/bin/bash" mysql:5.7 -c "cat /etc/group"

# 得到999，设置权限

sudo chown -R 999 logs

高性能MySQL第4版

/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)

/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/

查询配置文件位置和加载顺序

mysqld --verbose --help | grep -A 1 'Default options'

1	mysqld --verbose --help \| grep -A 1 'Default options'

MySQL最小化配置文件（MySQL 8.0）

[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

[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核数相同，然后再进行调优

MySQL常用操作及读书笔记

性能相关

慢查询命令行设置

常用命令整理

MySQL审计

配置文件

来自网络带说明的配置

SQL

MariaDB

Docker 相关

高性能MySQL第4版

MySQL最小化配置文件（MySQL 8.0）

Hi，您需要填写昵称和邮箱！