MySQL

1. docker pull mysql:5.7
2. docker run --name mysql-5 -d --restart=always -e MYSQL_ROOT_PASSWORD=<password> -p 127.0.0.1:3306:3306 mysql:5.7
3. docker exec -it mysql-5 mysql -uroot -p<password>

docker MySQL 连接终端不能输入中文，指定 LANG 登录 docker exec -it mysql-5 env LANG=C.UTF-8 bash 。

Docker MySQL 设置 utf8mb4 编码

https://hub.docker.com/_/mysql/

docker run -it -d --name xxx --restart=always --network qwerty -e MYSQL_ROOT_PASSWORD=xxx  -p 3306:3306 mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

官方文档说设置 --character-set-server=utf8mb4 和 --collation-server=utf8mb4_unicode_ci 即可，但事实上是不够的，还需要修改配置：在宿主机上，添加配置文件 mysql.cnf ：

[client]
default-character-set=utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[mysql]
default-character-set=utf8mb4

然后将配置 docker cp 到 docker 容器的 /etc/mysql/conf.d ，重启容器（启动时会自动加载 conf.d 下的 *.cnf ）。 这样才能正常的支持 utf8mb4 编码。

还有一种办法是基于官方镜像，重新制作镜像，在 Dockerfile 中将配置 CP 到镜像中。

但是导入的数据库，emoji 表情显示为 ? ，字符也都没有问题，具体原因还没找到。

• 建库: CREATE DATABASE db_name charset="utf8mb4"
• 删库: DROP DATABASES db_name
• 查看当前使用的数据库: select database()

• 建表: CREATE TABLE tbl_name (<col_name_1> <TYPE1>2 [,..<col_name_n> <TYPEn>])
• 删表: DROP TABLE tbl_name
• 显示表的结构: desc tbl_name, show columns from tbl_name
• 增加列: ALTER TABLE tbl_name ADD col_name TYPE
• 删除列: ALTER TABLE tbl_name DROP col_name
• 修改列: ALTER TABLE tbl_name MODIFY col_name TYPE
• 改变表的名字: ALTER TABLE tbl_name RENAME new_tbl_name
• 总条目数: SELECT COUNT(*) FROM tbl_name
• 查看建表语句: SHOW CREATE TABLE tbl_name \G

索引:

• 查看索引: show index from tblname or show keys from tblname
• 创建索引:
  • ALTER TABLE table_name ADD INDEX index_name (column_list)
  • ALTER TABLE table_name ADD UNIQUE (column_list)
  • ALTER TABLE table_name ADD PRIMARY KEY (column_list)
  • CREATE INDEX index_name ON table_name (column_list)
  • CREATE UNIQUE INDEX index_name ON table_name (column_list)
• 删除索引:
  • DROP INDEX index_name ON table_name
  • ALTER TABLE table_name DROP INDEX index_name
  • ALTER TABLE table_name DROP PRIMARY KEY

查询记录:

• sql = "select * from 数据表 where 字段名=字段值 order by 字段名 [desc]"
• sql = "select * from 数据表 where 字段名 like '%字段值%' order by 字段名 [desc]"
• sql = "select top 10 * from 数据表 where 字段名 order by 字段名 [desc]"
• sql = "select * from 数据表 where 字段名 in ('值1','值2','值3')"
• sql = "select * from 数据表 where 字段名 between 值1 and 值2"
• SELECT * FROM oss_shopemployee WHERE phonenum NOT REGEXP '^[0-9]+$': 判断某一列值是否为数字
• DELETE FROM oss_shopemployee WHERE phonenum NOT REGEXP '^[0-9]+$': 判断某一些是否不是数字

更新数据记录:

• sql="update 数据表 set 字段名=字段值 where 条件表达式"
• sql="update 数据表 set 字段1=值1,字段2=值2 字段n=值n where 条件表达式"

删除数据记录:

• sql="delete from 数据表 where 条件表达式"
• sql="delete from 数据表" (将数据表所有记录删除)

添加数据记录:

• sql="insert into 数据表 (字段1,字段2,字段3 …) values (值1,值2,值3 …)"
• sql="insert into 目标数据表 select * from 源数据表" (把源数据表的记录添加到目标数据表)

DISTINCT 使用:

位置: DISTINCT 只能放到开头，但是与其他查询函数一起使用的时候，没有位置限制: select play_id, count(distinct(task_id)) from task;

举例:

• 在 count 计算不重复的记录的时候能用到: SELECT COUNT( DISTINCT player_id ) FROM task; 就是计算表中 id 不同的记录有多少条
• 在需要返回记录不同的 id 的具体值的时候可以用: SELECT DISTINCT player_id FROM task; 返回表中不同的 id 的具体的值
• 上面的 情况2 对于需要返回 mysql 表中 2 列以上的结果时会有歧义: SELECT DISTINCT player_id, task_id FROM task; 实际上返回的是 player_id 与 task_id 同时不相同的结果,也就是 DISTINCT 同时作用了两个字段， 必须得 player_id 与 task_id 都相同的才被排除了, 与我们期望的结果不一样,我们期望的是 player_id 不同被过滤, 在这种情 况下，distinct 同时作用了两个字段，player_id, task_id。这时候可以考虑使用 group_concat 函数来进行排除,不过这个 mysql 函数是在 mysql4.1 以上才支持的
• 其实还有另外一种解决方式, 就是使用 SELECT player_id, task_id, count(DISTINCT player_id) FROM task 虽然这样的返回结果多了一列无用的count数据(有时也许就需要这个数据)
• 同时我们还可以利用下面的方式解决上面遇到的歧义问题通过 group by 分组: select player_id, task_id from task group by player_id

• 查询所有用户: select User from mysql.user
• 创建用户: create user username identified by 'password'
• 删除用户: delete from mysql.user where User="xxx" -> flush privileges
• 授权: grant all privileges on db_name.tablename to username
• 撤销权限: REVOKE PRIVILEGES ON db_name.* FROM 'user_name'@'localhost';
• 创建用户并赋权限: grant all privileges on db_name.* to news_user@'localhost' identified by 'news_password' @ 后面是限制的主机，可以是 IP 或者 IP 段, % 表示任何地方。
• 查看用户权限: SHOW GRANTS FOR user_name@localhost 或者 SELECT * FROM mysql.user WHERE user='user_name' \G

mysqldump -h${hostname} -u${username} -p${password}  --add-drop-table --databases ${db_name} > backupfile.sql

同时进行压缩： mysqldump -h${hostname} -u${username} -p${password} --add-drop-table --databases ${db_name} | gzip > backupfile.sql.gz

全量备份： mysqldump –all-databases > allbackupfile.sql

如果只备份表结构，不备份数据，加上 -d 选项即可。

mysql -h${hostname} -u${username} -p${password} < backupfile.sql

解压缩，并还原： gunzip backupfile.sql.gz | mysql -h${hostname} -u${username} -p${password} ${db_name}

• BLOB 四种类型: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
• TEXT 四种类型: TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT

区别:

1. BLOB 被视为二进制字符串, TEXT 被视为非二进制字符串
2. BLOB 列没有字符集，并且排序和比较基于列值字节的数值值。TEXT 列有一个字符集，并且根据字符集的校对规则对值进行排序和比较

BLOB 和 TEXT 与 VARBINARY 和 VARCHAR 的不同

1. BLOB 和 TEXT 列不能有默认值
2. 当保存或检索 BLOB 和 TEXT 列的值时不删除尾部空格。(这与 VARBINARY 和 VARCHAR 列相同）
3. 对于 BLOB 和 TEXT 列的索引，必须指定索引前缀的长度。对于 CHAR 和 VARCHAR，前缀长度是可选的.

# my.cnf
[client]
port            = 3306
socket          = /log/mysql56/mysql.sock

[mysqld]
port            = 3306
socket          = /log/mysql56/mysql.sock
pid-file        = /log/mysql56/mysqld.pid
log-error       = /log/mysql56/error.log
datadir         = /data/mysql56
tmpdir          = /data/mysql56
slow_query_log_file = /log/mysql56/slow.log
relay-log = mysqld-relay-bin
long_query_time = 1
slow_query_log = 1
sql_mode = ''
old_passwords = 0

back_log        = 1024
open_files_limit = 65535
explicit_defaults_for_timestamp = 1
default-storage-engine=InnoDB
performance_schema = 0
max_connections = 16384
table_open_cache = 8192
thread_concurrency = 32

max_connect_errors = 10000
interactive_timeout = 512
wait_timeout = 256
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 2M
join_buffer_size = 2M
# 8 + (max_connections / 100)
thread_cache_size = 1024
#query_cache_size = 64M
query_cache_limit = 16M
#default_table_type = INNODB

skip-external-locking
skip-name-resolve
server-id       = 0424

#*** master ***
log-bin= bin-log
binlog_format = mixed
binlog_rows_query_log_events = 1
expire_logs_days = 3

replicate_wild_ignore_table = mysql.%
replicate_wild_ignore_table = test.%
replicate_wild_ignore_table = information_schema.%
replicate_wild_ignore_table = performance_schema.%

#*** MyISAM Specific options ***
key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 16M

innodb_buffer_pool_instances = 8
innodb_old_blocks_time = 1000
innodb_buffer_pool_size = 36G
innodb_log_group_home_dir = /data/mysql56
innodb_data_home_dir  = /data/mysql56
innodb_data_file_path = ibdata1:1G:autoextend
innodb_autoextend_increment = 64
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_thread_concurrency = 32
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 128M
innodb_log_files_in_group = 2
innodb_log_file_size = 1G
innodb_open_files = 16384
innodb_file_per_table = 1
innodb_purge_threads = 1
innodb_stats_persistent = 0
innodb_io_capacity = 500
innodb_adaptive_flushing_lwm = 20
innodb_use_native_aio = 1
innodb_spin_wait_delay = 96
innodb_adaptive_hash_index = 0
innodb_sync_spin_loops = 100
innodb_file_format = barracuda
#innodb_doublewrite = 1
#innodb_checksum_algorithm = NONE

### slave ###
#skip_slave_start
#slave_compressed_protocol = 1
slave_parallel_workers = 4
master-info-repository = TABLE
relay-log-info-repository = TABLE
slave_type_conversions = ALL_NON_LOSSY

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 65535
#pid-file         = logs/mysqld.pid

开启:

slow_query_log = 1
slow_query_log_file = /data/log/mysql/mysql-slow.log
long_query_time = 0.01
log-queries-not-using-indexes

分析工具一：mysqldumpslow（官方自带）

• -s 是 order 的顺序，说明写的不够详细，俺用下来，包括看了代码，主要有 c, t , l, r和 ac, at, al, ar，分别是按照 query 次数，时间，lock 的时间和返回的记录数来排序，前面加了 a 的是倒序
• -t 是top n的意思，即为返回前面多少条的数据
• -g 后边可以写一个正则匹配模式，大小写不敏感的

mysqldumpslow -s c -t 20 host-slow.log

分析工具二：mysqlsla（第三方）

https://github.com/daniel-nichter/hackmysql.com/tree/master/mysqlsla