Xiaopei's DokuWiki

These are the good times in your life,
so put on a smile and it'll be alright

User Tools

Site Tools


it:mysql

MySQL

MySQL 的 doc 真难看.

建表时 int(10) 的 10 是展示时的长度,储存时长度是固定的!

查慢 SQL,补索引

INFORMATION_SCHEMA

MySQL :: MySQL 5.5 Reference Manual :: 20 INFORMATION_SCHEMA Tables

没有 DATABASES 表? 各数据库信息在 SCHEMATA 表中!

查看表最后修改时间/行数/大小

SELECT TABLE_NAME, UPDATE_TIME, TABLE_ROWS, concat(DATA_LENGTH/1024/1024, ' MB'), concat(INDEX_LENGTH/1024/1024,' MB') FROM TABLES WHERE TABLE_SCHEMA="database_name";

引擎

myisam 是默认的, 适合小型的应用或项目

innodb 支持事务处理和外键, 更适合大项目

tokudb Percona TokuDB

可以用 latin,比 utf8 性能高几倍。而 latin 中如果不得不放中文,可以用 urlencode

增量备份

MySQL 可通过 binary log 实现增量备份. binary log 中记录了自上一次备份(完整或增量)后, 数据库变动的 SQL 表示.

打开 binlog

binlog 默认关闭, 打开需做如下配置:

/etc/mysql/my.cnf
[mysqld]
# 省略其他配置...
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
 
log_bin			= /var/log/mysql/mysql-bin.log
binlog_do_db		= testbinlog
# binlog_ignore_db	= include_database_name
 
# 当设置了 do_db 时, 只备份 do_db
# 当设置了 ignore_db 时, 除 ignore_db 外都备份

备份

完整备份

$ mysqldump --flush-logs --lock-all-tables --databases $BAK_DB > full.sql

增量备份

$ mysqladmin flush-logs

打开 log_bin 后, mysql 会不断将对数据库的操作记录在 bin.log(二进制) 中, 而 flush-logs 让 mysql 结束在前一个 log 中记录, 新开 log, 这样, 前一个 log 便可安全地备份了.

但 flush-logs 有副作用, 它同时会将其他日志如 error.log flush.

查看

$ mysqlbinlog mysql-bin.000001 | grep -v '^#' | grep -v '/\*' | less

还原

参考

mysqldiff

tool and CPAN suite backend for comparing MySQL database schemas

Examples

# compare table definitions in two files
mysqldiff db1.mysql db2.mysql
 
# compare table definitions in a file 'db1.mysql' with a database 'db2'
mysqldiff db1.mysql db2
 
# interactively upgrade schema of database 'db1' to be like the
# schema described in the file 'db2.mysql'
mysqldiff -A db1 db2.mysql
 
# compare table definitions in two databases on a remote machine
mysqldiff --host=remote.host.com --user=myaccount db1 db2
 
# compare table definitions in a local database 'foo' with a
# database 'bar' on a remote machine, when a file foo already
# exists in the current directory
mysqldiff --host2=remote.host.com --password=secret db:foo bar

snippets

show processlist

  • show full processlist
  • SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST

死锁 deadlock

  • 记录所有死锁:修改配置(运行时,不需重启)innodb_print_all_deadlocks on
  • 查看死锁: SHOW ENGINE INNODB STATUS

在 group by 或别的列中使用 ALIAS in group by / other columns

答案是不行

SQL is implemented as if a query was executed in the following order:

  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause

For most relational database systems, this order explains which names (columns or aliases) are valid because they must have been introduced in a previous step.

general log

记录所有 SQL

mysql>set global general_log_file='/tmp/general.lg';    #设置路径,可选,默认在 /var/lib/mysql/{some_md5}.log

mysql>set global general_log=on;    # 开启general log模式

mysql>set global general_log=off;   # 关闭general log模式

update based on join

# MySQL UPDATE JOIN syntax:
 
UPDATE tableA a
LEFT JOIN tableB b ON
    a.name_a = b.name_b
SET
    validation_check = IF(start_dts > end_dts, 'VALID', '')
 
 
# ANSI SQL syntax:
 
UPDATE tableA SET validation_check = 
    (SELECT IF(start_DTS > end_DTS,'VALID','') AS validation_check
        FROM tableA
        LEFT JOIN tableB ON name_A = name_B
        WHERE id_A = tableA.id_A)

mysql < 导入时报错 ERROR 2006

ERROR 2006 (HY000): MySQL server has gone away - Stack Overflow

# Adding this line into my.cnf file solves my problem.
max_allowed_packet=64M
 
# You can also log into the database as root (or SUPER privilege) 
# and do:
set global max_allowed_packet=64*1024*1024
# doesn't require a MySQL restart as well 

另外导入时可以设置 –default-character-set=utf8,避免由于字符集问题误判 Duplicate entry for key

身份证号统计

; 年龄:
(YEAR(CURDATE()) - SUBSTRING(`card_id`,7,4)) AS 年龄,
 
; 所属省份:
CASE LEFT(`card_id`,2) WHEN 11 THEN '北京' WHEN 12 THEN '天津' WHEN 13 THEN '河北' WHEN 14 THEN '山西' WHEN 15 THEN '内蒙古' WHEN 21 THEN '辽宁'
    WHEN 22 THEN '吉林' WHEN 23 THEN '黑龙江' WHEN 31 THEN '上海' WHEN 32 THEN '江苏' WHEN 33 THEN '浙江' WHEN 34 THEN '安徽' WHEN 35 THEN '福建' WHEN      36 THEN '江西'
    WHEN 37 THEN '山东' WHEN 41 THEN '河南' WHEN 42 THEN '湖北' WHEN 43 THEN '湖南' WHEN 44 THEN '广东' WHEN 45 THEN '广西' WHEN 46 THEN '海南' WHEN 50 THEN '重庆' WHEN 51 THEN '四川'
    WHEN 52 THEN '贵州' WHEN 53 THEN '云南' WHEN 54 THEN '西藏' WHEN 61 THEN '陕西' WHEN 62 THEN '甘肃' WHEN 63 THEN '青海' WHEN 64 THEN '宁夏' WHEN 65 THEN '新疆' END AS 所属省份,
 
; 性别:
IF (MOD(SUBSTRING(`card_id`,17,1),2),'男','女') AS 性别

查看几位数、量级

不完全精准,但对整体没有太大影响

select ceil(log10(wealth+1)) as wlevel, count(1)
from hosts
group by wlevel;
+----------+------------+
|   wlevel |   count(1) |
|----------+------------|
|        0 |         45 |
|        1 |         86 |
|        2 |        181 |
|        4 |         91 |
|        5 |        166 |
|        6 |        387 |
|        7 |        658 |
|        8 |        337 |
|        9 |         48 |
+----------+------------+
9 rows in set
Command Time: 0.000s
Format Time: 0.001s

查看性能

MySQL :: MySQL 5.7 Reference Manual :: 14.7.5.30 SHOW PROFILE Syntax

mysql> SET profiling = 1;
Query OK, 0 ROWS affected (0.00 sec)
 
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 ROWS affected, 1 warning (0.00 sec)
 
mysql> CREATE TABLE T1 (id INT);
Query OK, 0 ROWS affected (0.01 sec)
 
mysql> SET ROW_COUNT = 100;
# 看 100 条 profiles
 
mysql> SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | Query                    |
+----------+----------+--------------------------+
|        0 | 0.000088 | SET PROFILING = 1        |
|        1 | 0.000136 | DROP TABLE IF EXISTS t1  |
|        2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+
3 ROWS IN SET (0.00 sec)
 
mysql> SHOW PROFILE;
+----------------------+----------+
| STATUS               | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating TABLE       | 0.000056 |
| After CREATE         | 0.011363 |
| query END            | 0.000375 |
| freeing items        | 0.000089 |
| logging slow query   | 0.000019 |
| cleaning up          | 0.000005 |
+----------------------+----------+
7 ROWS IN SET (0.00 sec)
 
mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| STATUS             | Duration |
+--------------------+----------+
| query END          | 0.000107 |
| freeing items      | 0.000008 |
| logging slow query | 0.000015 |
| cleaning up        | 0.000006 |
+--------------------+----------+
4 ROWS IN SET (0.00 sec)
 
mysql> SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
| STATUS               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000038 |   0.000002 |
| creating TABLE       | 0.000056 | 0.000028 |   0.000028 |
| After CREATE         | 0.011363 | 0.000217 |   0.001571 |
| query END            | 0.000375 | 0.000013 |   0.000028 |
| freeing items        | 0.000089 | 0.000010 |   0.000014 |
| logging slow query   | 0.000019 | 0.000009 |   0.000010 |
| cleaning up          | 0.000005 | 0.000003 |   0.000002 |
+----------------------+----------+----------+------------+
7 ROWS IN SET (0.00 sec)

按小时 group(MySQL Group By Hours)

SELECT   CONCAT(HOUR(created), ':00-', HOUR(created)+1, ':00') AS Hours
  ,      COUNT(*) AS `usage`
FROM     history
WHERE    created BETWEEN '2012-02-07' AND NOW()
GROUP BY HOUR(created)

Too many connections

mysql 检查 connections

/* Connections 是历史连接,
   The number of connection attempts (successful or not) to the MySQL server */
mysql> SHOW STATUS LIKE 'Conn%';
+---------------+-------+
| Variable_name | VALUE |
+---------------+-------+
| Connections   | 8     | 
+---------------+-------+
1 ROW IN SET (0.00 sec)
 
 
/* Threads_connected 或 show processlist 是当前连接 */
mysql> SHOW STATUS LIKE '%onn%';
+--------------------------+-------+
| Variable_name            | VALUE |
+--------------------------+-------+
| Aborted_connects         | 0     | 
| Connections              | 8     | 
| Max_used_connections     | 4     | 
| Ssl_client_connects      | 0     | 
| Ssl_connect_renegotiates | 0     | 
| Ssl_finished_connects    | 0     | 
| Threads_connected        | 4     | 
+--------------------------+-------+
7 ROWS IN SET (0.00 sec)
 
/*
   Time 列是当前状态的保持时间,
        The time in seconds that the thread has been in its current state. 
   Info 列是最后运行的 SQL
        The statement the thread is executing, or NULL if it is not executing any statement.        
*/
mysql> SHOW processlist;
+----+------+-----------------+--------+---------+------+-------+------------------+
| Id | USER | Host            | db     | Command | TIME | State | Info             |
+----+------+-----------------+--------+---------+------+-------+------------------+
|  3 | root | localhost       | webapp | Query   |    0 | NULL  | SHOW processlist | 
|  5 | root | localhost:61704 | webapp | Sleep   |  208 |       | NULL             | 
|  6 | root | localhost:61705 | webapp | Sleep   |  208 |       | NULL             | 
|  7 | root | localhost:61706 | webapp | Sleep   |  208 |       | NULL             | 
+----+------+-----------------+--------+---------+------+-------+------------------+
4 ROWS IN SET (0.00 sec)

关闭进程:在 mysql 中 kill processlist 的 id

``` KILL 3; ```

事务导致的问题

现象:

  • 数据更新或新增后数据经常自动回滚。
  • 表操作总报 Lock wait timeout exceeded 并长时间无反应

解决方法:

  • 应急方法:show processlist; kill掉出现问题的进程
  • 根治方法:select * from innodb_trx 查看有是哪些事务占据了表资源。
  • 我的方法:设置MySQL锁等待超时 innodb_lock_wait_timeout=50 ,autocommit=on

select for update 不是行锁,还会锁索引:Mysql 数据库死锁过程分析(select for update)_Mysql_脚本之家

bash 检查 connections

$ mysqladmin status
 
Uptime: 4661  Threads: 1  Questions: 200  Slow queries: 0  Opens: 16  Flush
tables: 1  Open tables: 6  Queries per second avg: 0.043

refs

修改 character set

# 修改表
ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8;
# 修改数据库
ALTER DATABASE `database_name` DEFAULT CHARACTER SET utf8;
 
# 检查
USE information_schema;
# 表
SELECT * FROM TABLES WHERE TABLE_SCHEMA='database_name';
# 数据库
SELECT * FROM SCHEMATA;

导入脚本

$ mysql < foo.sql

# 忽略运行时的错误(如重复 insert 等)
$ mysql -f < foo.sql

从命令行运行, 并将结果导出至文件

-e for execute

$ mysql -e 'QUERY' > result.out

mysqldump 常用参数

phpmyadmin 的 Export 功能更全, 如果只能用命令行的话, 可以先参考 phpmyadmin 的功能, 再查文档找 mysqldump 实现该功能的 options

  • –no-data, -d
    no data,structure only
  • –opt
    same as specifying –add-drop-table, –add-locks, –create-options, –disable-keys, –extended-insert, –lock-tables, –quick, and –set-charset. All of the options that –opt stands for also are on by default because –opt is on by default.
  • –compact
    the same as specifying –skip-add-drop-table, –skip-add-locks, –skip-comments, –skip-disable-keys, and –skip-set-charset options.
  • –disable-keys
    For each table, surround the INSERT statements with statements to disable and enable keys.
  • 但是 mysqldump 没有 CREATE TABLE IF NOT EXISTS 的选项, 需要 dump 后 sed 替换
  • –skip-extended-insert
    insert 一行一个 value, 较之一个 insert 多行 value(extended insert) 效率很低
  • –complete-insert, -c
    Use complete INSERT statements that include column names
  • –databases, –tables, –where
    Dump by query1)
    mysqldump --databases X --tables Y --where="1 limit 1000000"

赋予某用户表名符合某一规则的所有表的所有权限

GRANT ALL ON `foo\_%`.* TO foo@'localhost' IDENTIFIED BY ''

replace, 字符串替换

# e.g.1
mysql> UPDATE table_foo SET field_bar=REPLACE(field_bar, 'cancle', 'cancel');
 
# e.g.2
mysql> SELECT token, REPLACE(token, '|database', '') FROM USER;
+----------------------+---------------------------------+
| token                | REPLACE(token, '|database', '') |
+----------------------+---------------------------------+
| 030015|DATABASE      | 030015                          |
| 050150|DATABASE      | 050150                          |
...
+----------------------+---------------------------------+
36 ROWS IN SET (0.00 sec)

更多字符串函数 string functions: MySQL :: MySQL 5.0 Reference Manual :: 12.5 String Functions

substring_index 贴心函数!

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'
 
mysql> SELECT SUBSTRING_INDEX('79297284 201709-2', ' ', 1);
        -> '79297284'
mysql> SELECT SUBSTRING_INDEX('79297284 201709-2', ' ', -1);
        -> '201709-2'

insert from select query

INSERT INTO _auth(token, password) 
SELECT concat('nk', REPLACE(token, "|nankai.edu.cn", "")) AS token, 
       'password' AS password  FROM USER 
WHERE token LIKE "%|nankai.edu.cn" ;

copy table, 复制表

CREATE TABLE student2 SELECT * FROM student

date, 日期时间函数

UNIX_TIMESTAMP() # 获得当前时间的 TIMESTAMP
FROM_UNIXTIME(unix_timestamp[,format]) # 将 TIMESTAMP 转为可读形式

change user password

mysqladmin

$ mysqladmin -u USER [-p OLDPASSWORD] password NEWPASSWORD

mysql

mysql> use mysql;
mysql> update user set password=password("NEWPASSWORD") where user='USER';
mysql> flush privileges;

删除密码:set password=null

忘了 root 密码后重置密码:Recover MySQL root Password - nixCraft

rename table

RENAME TABLE `tbl_name` TO `new_tbl_name`

root 远程登陆

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '';
FLUSH PRIVILEGES;
it/mysql.txt · Last modified: 2017/12/27 15:15 by admin