MySQL
MySQL 是芬兰程序员 Michael Widenius 用 C 和 C++ 语言实现的跨平台、功能完善、开源数据库管理系统。 作者成立瑞典 MySQL AB 公司赞助开发和运营，首个完善正式版本发布于 1995 年； 2008 年美国 Sun Microsystems 公司收购 MySQL AB ； 2010 年美国 Oracle 公司收购 Sun Microsystems ，同年 Widenius 克隆 MySQL 创造另一个开源数据库管理系统 MariaDB 。
安装软件包
容器安装服务端 docker run --name sql -p 13306:3306 -e MYSQL_ROOT_PASSWORD=secret -e MYSQL_ROOT_HOST=% -d mysql:lts
注：在 Windows 上，3306 可能被占用导致无法通过 TCP localhost:3306 访问实例，所以上面映射 3306 为 13306 。
进入容器实例，通过命令行客户端访问服务端：
docker exec -it sql bash
MYSQL_PWD=secret mysql -h 127.0.0.1 -u root -P 13306 -e 'show databases'基础语法
进入交互终端 MYSQL_PWD=secret mysql -h 127.0.0.1 -u root -P 13306 -d myDb
int to str in custom format
DATE_FORMAT(from_unixtime(created), '%Y-%m-%d')
估算占用空间大小
统计数据库占用空间大小
SELECT table_schema "DB Name",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY table_schema;统计数据表占用空间大小
SELECT
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024 ), 2) as `Size in MB`,
round((AVG_ROW_LENGTH / 1024), 2) as `Avg row size in KB`
FROM information_schema.TABLES WHERE table_schema = 'testymd'
ORDER BY `Size in MB` DESC;管理用户和库表
添加库 CREATE DATABASE IF NOT EXISTS myDb CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
添加表 CREATE TABLE IF NOT EXISTS myTbl
添加用户 myUser 对 mydDb 库所有表读写和管理权限
create user 'myUser'@'%' identified by 'secret';
grant all privileges on mydDb.* to 'myUser'@'%' with grant option;
flush privileges;移除用户 myUser 对 myDb 库所有表读写和管理权限 REVOKE ALL PRIVILEGES ON myDb.* FROM 'myUser'@'%'
查看权限 show grants for 'root'@'%' ;
修改 docker 实例中 root 密码
docker exec -it mysql_inst bash
echo $MYSQL_ROOT_PASSWORD
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';查看表索引 show index from myTbl
临时禁启用外键约束 SET FOREIGN_KEY_CHECKS=0; SET FOREIGN_KEY_CHECKS=1;
添加表索引 CREATE INDEX idx_my_tbl_xxx ON my_tbl (column_xxx);
添加列 ALTER TABLE myTbl ADD COLUMN IF NOT EXISTS myCol VARCHAR(20) NOT NULL DEFAULT 'foo';
修改大表字段
apt install percona-toolkit
pt-online-schema-CHANGE \ --print \
--alter "ADD COLUMN my_col int NOT NULL DEFAULT 0" \
D=my_db,t=my_tbl,u=root,p=secret,h=127.0.0.1库改名
mysqladmin create newdb
mysqldump olddb | mysql newdb慢查询日志
临时/实时修改，启用记录慢查询日志：
show variables like '%slow_query_log%';
set global slow_query_log='ON';
set global long_query_time=1;查看容器实例中慢日志： tail -f /var/lib/mysql/xxx-slow.log
新开命令行执行测试 select sleep(11);
永久修改，启用慢查询日志，修改配置 /etc/my.cnf
slow_query_log = on
long_query_time = 1
# 注：容器环境不要修改此值 /var/lib/mysql/ 目录不存在可能会导致无法启动
# log-slow-queries = /var/lib/mysql/slow.log备份和恢复数据
备份指定数据库所有表
mysqldump \
-h34.124.183.108 \
-uroot \
--skip-lock-tables \
--single-transaction \
--set-gtid-purged=OFF \
--verbose \
-p \
my-db > my-db.`date +%Y%m%d`.sql批量备份表名带 my_tbl_ 前缀的所有数据表
mysql \
-N information_schema \
-e "select table_name from tables where table_name like 'my_tbl_%'" > tables.txt
mysqldump --skip-lock-tables myDb `cat tables.txt` > tables.sql合并表数据
合并 t2 数据到 t1 ，t1 t2 表结构一样 insert into t1 select * from t2 where not exists(select * from t1 where t1.name =t2.name);
t1,t2 结构一样，但是 show create table tbl 结果不一，可能会导致列错乱 (t2.c1 varchar(10）t1.c2 varchar(20)) 而合并失败， 指定列即可 insert into t1 (c1,c2) select c1,c2 from t2 where not exists(select * from t1 where t1.name =t2.name);
常见问题
mysqldump not unique table/alias when doing lock table
mysqldump --skip-lock-tables ...
mysql Ignoring query to other database
用户名参数不对导致， mysql -Uroot ... 改为 mysql -uroot ...
Failed to find valid data directory
mysqld --initialize-insecure
ERROR 1148 (42000): The used command is not allowed with this MySQL version
Load data from file mysql> load data local infile '/tmp/my_tbl.data' into table my_tbl;
Solution:
update /etc/mysql/my.cnf
[mysqld]
local-infile = 1不通版本类型计算兼容性和差异
mysql 5.7 会将字符串转换为 dateime 类型后计算/比较（注意转换后不是期望正确值），并抛出 warning
select now() - CURRENT_TIMESTAMP;
select now() - 'CURRENT_TIMESTAMP';8.0 以后，计算会抛出 warning ，比较则抛出错误。