MySQL 笔记
本文主要介绍有关MySQL的内容，包括一些常用配置，常见问题。根据个人使用经验总结，希望可以帮到大家。
MySQL配置
下面是我的配置
[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=INNODBMySQL编码
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)--如果仍有编码不是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_%';
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)开启二进制日志
开启二进制日志有两种办法
- 修改配置文件
修改MySQL安装目录下的 my.ini (windows环境)
[mysqld]
# Binary Log
log-bin=mysql-bin
#在[mysqld]下添加以上两行，添加完保存文件，重启MySQL服务就可以看到二进制日志启用了- 通过命令修改
显示二进制日志数目
show master logs;
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提供两种方式查看方式
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存在那个文件中，从上面可以看出这2条log皆存在与mysql_bin.000001文件中。
Pos:log在bin-log中的开始位置
Event_type:log的类型信息
Server_id:可以查看配置中的server_id,表示log是那个服务器产生
End_log_pos：log在bin-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*/;
删除二进制日志
# 删除编号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;
show variables like 'character_set_database'; # 数据库编码
show variables like 'collation_%';
show variables like 'character_set_%';优化
ß
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] mysql preface
[2] mysql create-user
[3] MySQL和阿里云RDS应用和实践宝典-云栖社区-阿里云
[4] MYSQL 5.x 启用日志，和查看日志
[5] MySQL 5.6x开启慢查询日志
[6] mysql5.6++版本打开慢查询日志
[7] MySQL中show命令方法得到表列及整个库的详细信息(精品珍藏)
[8] MySQL的注释方式
[9] mysql中如何查看某个数据库或表占用的磁盘空间
[10] Mysql删除数据后，磁盘空间未释放的解决办法
[11]
[12] 理解MySQL——复制(Replication)
[13] Server Collation介绍及其变更对数据的影响
[14] Database Character Set and Collation
[15]
[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