MySQL 笔记

本文主要介绍有关MySQL的内容,包括一些常用配置,常见问题。根据个人使用经验总结,希望可以帮到大家。

MySQL配置

下面是我的配置

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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
[client]

port=3306

# utf8mb4 is a superset of utf8
default-character-set = utf8mb4


[mysql]

# utf8mb4 is a superset of utf8
default-character-set = utf8mb4


# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this
# file.
#
[mysqld]

# utf8mb4 is a superset of utf8
character-set-server=utf8mb4


#collation-server=utf8mb4_unicode_ci
#collation-server=utf8_general_ci

#character-set-client-handshake = FALSE

#init_connect='SET NAMES utf8mb4'

# mkdir for every database
innodb_file_per_table=1

# ignore lowercase
lower_case_table_names=1

# all import biggest 1024M file to mysql
max_allowed_packet=1024M

# The TCP/IP Port the MySQL Server will listen on
port=3306

#log

# Binary Log
log-bin=mysql-bin
binlog-format=ROW
server_id=1

# if query_time > 1s sql will log
long_query_time=1
# if query is slow, query will log version 5.6
slow-query-log=1
slow-query-log-file = /usr/local/mysql/log/slow_query.log
# slow-query-log-file=c:/professionsofware/mysql/log/slow_query.log
# version5.0 log-slow-queries=c:/professionsofware/mysql/log/slow_query.log

# log all query version5.6
general_log=ON
general_log_file = /usr/local/mysql/log/all_query.log
#general_log_file=c:/ProfessionSofware/MySQL/log/all_query.log
#version5.0 log=c:/ProfessionSofware/MySQL/log/all_query.log
# log error
#log-error=c:/professionsofware/mysql/log/mysql_error.log

#Path to installation directory. All paths are usually resolved relative to this.
#basedir="C:/ProfessionSofware/MySQL/MySQLServer5.6/"
basedir=/usr/local/mysql

#Path to the database root
#datadir="C:/ProgramData/MySQL/MySQL Server 5.6/Data/"
datadir=/usr/local/mysql/data

# The default character set that will be used when a new schema or table is
# created and no character set is defined
#character-set-server=gbk

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# The default storage engine that will be used for temporary tables
default-tmp-storage-engine=INNODB

MySQL编码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show variables like "%char%";
+--------------------------+---------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.7.16-linux-glibc2.5-x86_64/share/charsets/ |
+--------------------------+---------------------------------------------------------------+
8 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
--如果仍有编码不是utf8的,请检查配置文件,也可使用mysql命令设置:
set character_set_client = utf8;
set character_set_server = utf8;
set character_set_connection = utf8;
set character_set_database = utf8;
set character_set_results = utf8;
set collation_connection = utf8_general_ci;
set collation_database = utf8_general_ci;
set collation_server = utf8_general_ci;

show variables like 'collation_%';

show variables like 'character_set_%';

MySQL日志

MySQL默认是不开启那些日志的,如:二进制日志,错误日志,慢查询日志,查询日志等

MySQL有以下几种日志:
错误日志:-log-err
查询日志:-log
慢查询日志: -log-slow-queries
更新日志:-log-update
二进制日志:-log-bin

查看是否启用了日志

show variables like 'log_%';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> show variables like 'log_%';
+----------------------------------------+------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------------+------------------------------------------------------------+
| log_bin | ON |
| log_bin_basename | C:\ProgramData\MySQL\MySQL Server 5.6\Data\mysql-bin |
| log_bin_index | C:\ProgramData\MySQL\MySQL Server 5.6\Data\mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_error | C:\ProgramData\MySQL\MySQL Server 5.6\Data\WKQ-PC.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_throttle_queries_not_using_indexes | 0 |
| log_warnings | 1 |
+----------------------------------------+------------------------------------------------------------+
10 rows in set (0.00 sec)

开启二进制日志

开启二进制日志有两种办法

  1. 修改配置文件
    修改MySQL安装目录下的 my.ini (windows环境)
1
2
3
4
5
6
[mysqld]

# Binary Log
log-bin=mysql-bin

#在[mysqld]下添加以上两行,添加完保存文件,重启MySQL服务就可以看到二进制日志启用了
  1. 通过命令修改

显示二进制日志数目

show master logs;

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
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 139 |
| mysql-bin.000002 | 5004253 |
| mysql-bin.000003 | 139 |
| mysql-bin.000004 | 139 |
| mysql-bin.000005 | 139 |
| mysql-bin.000006 | 139 |
中间的一部分省略
| mysql-bin.000158 | 139 |
| mysql-bin.000159 | 120 |
+------------------+-----------+
159 rows in set (0.04 sec)
```

### 查看二进制日志

bin-log因为是二进制文件,不能通过记事本等编辑器直接打开查看,mysql提供两种方式查看方式

```sql
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+-------------+-----------+-------------+-----------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.5-m8-log, Binlog ver: 4 |
| mysql-bin.000001 | 120 | Stop | 1 | 139 | |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------+
2 rows in set (0.00 sec)

Log_name:此条log存在那个文件中,从上面可以看出这2log皆存在与mysql_bin.000001文件中。
Pos:logbin-log中的开始位置
Event_type:log的类型信息
Server_id:可以查看配置中的server_id,表示log是那个服务器产生
End_log_pos:logbin-log中的结束位置
Info:log的一些备注信息,可以直观的看出进行了什么操作

C:\ProgramData\MySQL\MySQL Server 5.6\data>mysqlbinlog mysql-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
#at 4
#170121 21:19:46 server id 1 end_log_pos 120 Start: binlog v 4, server v 5.6.5-m8-log created 170121 21:19:46 at startup
ROLLBACK/*!*/;
BINLOG '
8l+DWA8BAAAAdAAAAHgAAAAAAAQANS42LjUtbTgtbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADyX4NYEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAAAAAAGRkAAH+q
GBU=
'/*!*/;
#at 120
#170121 23:23:22 server id 1 end_log_pos 139 Stop
DELIMITER ;
#End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

删除二进制日志

1
2
3
4
5
6
7
8
9
10
// 删除编号000003之前的所有日志
purge binary logs to 'mysql-bin.000003';

// 删除2017-05-11 22:00:00之前的所有日志
mysql> purge master logs before '2017-05-11 22:00:00';
Query OK, 0 rows affected (0.53 sec)

// 设置参数—expire_logs_days=#(days),此参数的含义是设置日志的过期天数,过来指定的天数后日志将会被自动删除,这样将有利于减少DBA管理日志的工作量
// 这样,3天前的日志都会被删除,系统自动删除
--expire_logs_days=3

用户管理

添加用户

MySQL创建用户的方法分成三种:INSERT USER表的方法、CREATE USER的方法、GRANT的方法。

第一种
insert into mysql.user(Host,User,Password) values("%","test",password("123abc"));

第二种
create user 'user1'@'%' identified by '123abc';
create user 'user1'@'localhost' identified by '123abc';
create user 'user1'@'192.168.189.*' identified by '123abc';

第三种
grant all privileges on *.* to 'user1'@'%' identified by '123abc' with grant option;
grant all privileges on test.* to 'user1'@'192.168.10.1' identified by '123abc';

创建完记得 flush privileges;

SQL语句

1.查询结果去重

User表里name有重复的,想要不重复的name的用户的全部信息。
select *, count(distinct name) from user group by name;

删除数据表中重复记录,只保留一条

delete from 表名 where 字段ID in (select * from (select max(字段ID) from 表名 group by 重复的字段 having count(重复的字段) > 1) as b);

delete from un_visited_url where id not in ( select * from (select max(id) from un_visited_url group by unique_key having count(unique_key)>1 ) as b );

mysql分页查询

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM table LIMIT 5,10;  // 检索记录行 6-15

//为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为 -1:
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.

//如果只给定一个参数,它表示返回最大的记录行数目:
mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行

//换句话说,LIMIT n 等价于 LIMIT 0,n。

查询最后一条数据

select * from user order by id desc limit 1;

mysql中如何查看某个数据库或表占用的磁盘空间

1
2
3
4
5
6
7
-- 查整个库的状态:
select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_data_size,
concat(truncate(sum(data_free)/1024/1024,2),'MB') as data_free,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables where TABLE_SCHEMA = 'databasename';
-- 注意是**TABLE_SCHEMA** databasename
1
2
3
4
5
6
7
查单表:
select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_data_size,
concat(truncate(sum(data_free)/1024/1024,2),'MB') as data_free,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables where TABLE_NAME = 'tablename';
--  注意是**TABLE_NAME**
1
2
3
4
--删除user表
truncate table user ;

delete * from user ;
1
2
3
4
5
show variables like 'character_set_database'; # 数据库编码

show variables like 'collation_%';

show variables like 'character_set_%';

alter table user modify column sex enum('0','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
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)

References

[1] https://dev.mysql.com/doc/refman/5.7/en/preface.html
[2] https://dev.mysql.com/doc/refman/5.7/en/create-user.html
[3] https://yq.aliyun.com/topic/100
[4] http://blog.csdn.net/a351945755/article/details/28239819 version5
[5] http://blog.itpub.net/29510932/viewspace-1668784/ version5.6
[6] http://blog.csdn.net/zqtsx/article/details/24381583
[7] http://www.jb51.net/article/25360.htm mysql
[8] http://www.cnblogs.com/dapeng111/archive/2013/01/02/2842106.html mysql注释
[9] http://blog.csdn.net/cto_51/article/details/8791200 mysql中如何查看某个数据库或表占用的磁盘空间
[10] http://www.cnblogs.com/paul8339/p/5649151.html Mysql删除数据后,磁盘空间未释放的解决办法
[11] http://flyer0126.iteye.com/blog/497359
[12] http://www.cnblogs.com/hustcat/archive/2009/12/19/1627525.html
[13] http://www.cnblogs.com/xwdreamer/archive/2012/07/11/2585993.html Server Collation介绍及其变更对数据的影响
[14] https://dev.mysql.com/doc/refman/5.7/en/charset-database.html
[15] https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-conversion.html
[16] http://seanlook.com/2016/10/23/mysql-utf8mb4/
[17] http://blog.chinaunix.net/uid-23916356-id-5765908.html
[18] MySQL 超新手入门教程系列
[19] http://blog.okbase.net/haobao/archive/1213.html
[20] MySQL经典题目
[21] http://www.iswoole.com/article/2054 全面的MySQL优化参考
[22] http://www.iswoole.com/article/2053 mysql索引最左匹配原则的理解
[23] http://www.cnblogs.com/echo-something/archive/2012/08/26/mysql_int.html 详解mysql int类型的长度值问题
[24] http://imysql.com
[25] http://blog.codinglabs.org/articles/theory-of-mysql-index.html
[26] http://blog.codinglabs.org/articles/index-condition-pushdown.html