1. 如何修改Mysql的用户密码
mysql> update mysql.user set password=password('hello') where user='root';
mysql> flush privileges;
2. 关于分区数量的限制
Prior to MySQL 5.6.7, the maximum possible number of partitions for a given table not using the NDB storage engine was 1024. Beginning with MySQL 5.6.7, this limit is increased to 8192 partitions. Regardless of the MySQL Server version, this maximum includes subpartitions.
3. 如何查看当前使用的数据库
mysql> select database();
4. 如何查看当前数据库的版本
mysql> select version();
5. MySQL命令行导入SQL语句文件
# mysql -u root -p123456 test < 123.sql
其中，test为数据库名
6. MySQL日志文件的位置
/var/log/mysqld.log
可修改/etc/init.d/mysqld脚本进行自定义
7. 如何查看表的索引
mysql> show index from tblname;mysql> show keys from tblname;
· Table
表的名称。
· Non_unique
如果索引不能包括重复词，则为0。如果可以，则为1。
· Key_name
索引的名称。
· Seq_in_index
索引中的列序列号，从1开始。
· Column_name
列名称。
· Collation
列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
· Cardinality
索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
· Sub_part
如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
· Packed
指示关键字如何被压缩。如果没有被压缩，则为NULL。
· Null
如果列含有NULL，则含有YES。如果没有，则该列含有NO。
· Index_type
用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
· Comment
8. SQL注入
譬如在下例中，如果name传入的值为tom’ or 1=1 –‘，尽管password的值是错误的，仍然可以获取用户名和密码
mysql> insert into sql_injection values('tom','');
Query OK, 1 row affected (0.00 sec)mysql> insert into sql_injection values('scott','tiger');
Query OK, 1 row affected (0.01 sec)mysql> select * from sql_injection;
+-------+----------+
| name | password |
+-------+----------+
| tom | 123456 |
| scott | tiger |
+-------+----------+
2 rows in set (0.00 sec)mysql> select * from sql_injection where name='tom' or 1=1 --'' and password='00';
+------+----------+
| name | password |
+------+----------+
| tom | 123456 |
+------+----------+
1 row in set (0.00 sec)
9. 如何查看MySQL的存储过程。
1> 查看当前数据库存储过程。
mysql> show procedure status;
+------+----------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+----------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | findById | PROCEDURE | root@localhost | 2015-12-16 18:31:16 | 2015-12-16 18:31:16 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
+------+----------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
2> 查看某个存储过程的创建语句
mysql> show create procedure findById\G
*************************** 1. row ***************************
Procedure: findById
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `findById`(IN sid INT)
BEGIN select * from jdbc_test where id=sid; end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
3> 通过mysql数据库中的proc表查看
mysql> select db,name,type,param_list,body from mysql.proc;
+------+----------+-----------+------------+-------------------------------------------------+
| db | name | type | param_list | body |
+------+----------+-----------+------------+-------------------------------------------------+
| test | findById | PROCEDURE | IN sid INT | BEGIN select * from jdbc_test where id=sid; end |
+------+----------+-----------+------------+-------------------------------------------------+
1 row in set (0.00 sec)
10. ACID
原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
11. Can’t locate ExtUtils/MakeMaker.pm in @INC
在安装percona-toolkit的过程中，在执行perl Makefile.PL时报以上错误。
解决方法：yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
12. Can’t locate Digest/MD5.pm in @INC
在执行pt-table-checksum –help的过程中，报以上错误
解决方法：yum install perl-Digest-MD5
13. 修改root密码的三种方式
1. mysql> set password for ‘root’@’localhost’=password(‘123’); 无需刷新权限表
2. mysql> update mysql.user set password=password(“456″) where user=”root” and host=”localhost”;
mysql> flush privileges;
3. # mysqladmin -u root password “123”
14. 会话变量和全局变量
1、当服务器运行时很多的变量可以动态改变。
2、客户端只能更改自己的会话变量，不能更改其他客户端的会话变量，退出客户端时变量复原，并且不会影响其他客户端，系统变量影响全局。
3、服务器启动时，将全局变量初始化为默认值，这些默认值可以在配置文件或命令行中更改。想要更改全局变量，必须具有super权限。设置会话变量不需要特殊的权限。
4、语法：
设置global变量的值：
set global sort_buffer_size = value;
set @@global.sort_buffer_size = value;
设置会话变量的值：
set session sort_buffer_size = value;
set sort_buffer_size = value;
检索global变量的值：
select @@global.sort_buffer_size;
show global variables like ‘sort_buffer_size’;
检索session变量的值：
select @@sort_buffer_size;
select @@session.sort_buffer_size;
show session variables like ‘sort_buffer_size’;
5、设置变量时不指定global,session或local，默认使用session。
6、当使用select @@var_name检索变量时(即不指定global,session),mysql返回session值(如果存在),否则返回global值。
对于show variables,如果不指定global,session,mysql返回session的值
15. 如何将列名拼凑成一行
mysql> set @test:=”;
mysql> select @test := concat(@test,column_name,’,’) from information_schema.columns where table_name=’order_detail’;
mysql> select @test;
16. MySQL如何添加主键，外键
mysql> alter table dept modify column deptno int primary key;
mysql> alter table emp add constraint foreign key(dept_no) references dept(deptno);
17. 如何查看及修改最大连接数
mysql> show variables like ‘max_connections’;
mysql> set global max_connections=400;
18. EMS SQL Manager for MySQL
EMS SQL Manager for MySQL是一款高性能MySQL数据库服务器系统的管理和开发工具。
19. 怎么把MySQL中的数据同步到Oracle中
OGG：Goldengate
EMS SQL Manager：导出Oracle类型的sql语句
kettle：Kettle是一款国外开源的ETL工具，纯java编写，可以在Window、Linux、Unix上运行，数据抽取高效稳定。
otter：阿里的开源工具
Migration Toolkit
20. 监控工具
天兔，zabbix，MySQLMTOP，QMonitor
21. MySQL中间件
Atlas：Atlas是由 Qihoo 360, Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它在MySQL官方推出的MySQL-Proxy 0.8.2版本的基础上，修改了大量bug，添加了很多功能特性。目前该项目在360公司内部得到了广泛应用，很多MySQL业务已经接入了Atlas平台，每天承载的读写请求数达几十亿条。
主要功能：
* 读写分离
* 从库负载均衡
* IP过滤
* SQL语句黑白名单
* 自动分表
22. 如何移动innodb表
1. rename
mysql> rename table test.ratings to test1.hello;
2. mysql Innodb表空间卸载、迁移、装载
http://www.jb51.net/article/43282.htm
23. 慢查询的时间设置的是4s，但slow日志里面却记录了很多0.00几秒的查询？
与参数log_queries_not_using_indexes值有关
set @@global.log_queries_not_using_indexes=0;
24. 关于MySQL大小写敏感和校对规则
mysql中控制数据库名和表名的大小写敏感由参数lower_case_table_names控制，为0时表示区分大小写，为1时，表示将名字转化为小写后存储，不区分大小写。字段名通常都是不区分大小写的，字段值的大小写由mysql的校对规则来控制。
如何让字段名区分大小写呢？
1> 表级别
create table table_name( a varchar (20) binary);
2> 查询级别
mysql> insert into t values('abc');
Query OK, 1 row affected (0.00 sec)mysql> insert into t values('ABC');
Query OK, 1 row affected (0.00 sec)mysql> select * from t where id='abc';
+------+
| id |
+------+
| abc |
| ABC |
+------+
2 rows in set (0.00 sec)mysql> select * from t where binary id='abc' ;
+------+
| id |
+------+
| abc |
+------+
1 row in set (0.04 sec)mysql> select * from t where binary id='ABC' ;
+------+
| id |
+------+
| ABC |
+------+
1 row in set (0.00 sec)mysql> select * from t where id='abc' collate utf8_bin;
+------+
| id |
+------+
| abc |
+------+
1 row in set (0.00 sec)
3> 数据库级别
CREATE DATABASE d1 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
具体可参考：
http://www.cnblogs.com/cchust/p/3952821.html
25. MySQL同时创建多个索引
mysql> alter table test add key(id),add index(type);
26. mysqldbcompare
官方的数据比对工具
http://dev.mysql.com/doc/mysql-utilities/1.6/en/mysqldbcompare.html
27. 如何查看给定时间那一周的第一天和最后一天
mysql> SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 -DATE_FORMAT('','%w') DAY),'%Y-%m-%d') Monday, DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 7-DATE_FORMAT('','%w') DAY),'%Y-%m-%d') Sunday;
+------------+------------+
| Monday | Sunday |
+------------+------------+
| 2016-02-22 | 2016-02-28 |
+------------+------------+
1 row in set (0.00 sec)
28. 大文本编辑器
emeditor，HugeTxtSearch，LTFViewr
29. 2016-02-26 12:25:33 25762 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the ‘START SLAVE Syntax’ in the MySQL Manual for more information.
原因：默认情况下，从库用于复制的用户及其密码保存在master.info里面，如下所示：
vim /var/lib/mysql/master.info
mysql-bin.192.168.244.145
repl
repl
这样会带来安全隐患。
解决方法：修改参数master_info_repository的值，默认为file。
mysql> set @@global.master_info_repository='table';
Query OK, 0 rows affected (0.00 sec)
30. 创建用户并授权
GRANT ALL PRIVILEGES ON *.* TO ‘myuser’@’192.168.1.3’ IDENTIFIED BY ‘mypassword’ WITH GRANT OPTION;
FLUSH PRIVILEGES;
31. 查看MySQL检索配置文件的顺序
# mysqld --verbose --help |grep -A 1 "Default options"
2016-03-01 12:39:16 0 [Note] mysqld (mysqld 5.6.26-log) starting as process 8863 ...
2016-03-01 12:39:16 8863 [Note] Plugin 'FEDERATED' is disabled.
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
2016-03-01 12:39:16 8863 [Note] Binlog end
2016-03-01 12:39:16 8863 [Note] Shutting down plugin 'MyISAM'
2016-03-01 12:39:16 8863 [Note] Shutting down plugin 'CSV'
其中-A
-A NUM, –after-context=NUM
Print NUM lines of trailing context after matching lines.
32. 端口转发工具
RINETD
参考文档：http://www.linuxidc.com/Linux/2013-01/77794.htm
官方文档：http://www.boutell.com/rinetd/
33. 如何修改MySQL数据库名
http://www.jb51.net/article/49293.htm
34. 如何使用MySQL自带的文档
mysql> help content;
在网络不允许的情况下，可使用该命令查看常用的语法。
35. 在线DDL工具
1. 5.6可以在线DDL
2. pt-online-schema-change
36. 比如说我有一张订单表，我希望他只保留3个月的数据，其他数据归档到其他数据库中（跨MySQL实例）
pt-archiver
参考文档：http://blog.itpub.net/23249684/viewspace-1350033/
37. 如何查看binlog日志
mysqlbinlog -vv –base64-output=decode-rows mysqlbinlog.0001
38. Windows安装MySQL ZIP包
1> 解压文件
2> 将MySQL bin目录添加到PATH环境变量中
E:\mysql-5.7.11-winx64\bin
3> 编辑配置文件my-default.ini
basedir = E:\mysql-5.7.-winx64
datadir = E:\mysql-5.7.-winx64\data
4> 初始化数据库
mysqld –initialize –user=mysql –console
使用console选项，启动信息会打印在终端台上，包括生成的密码。
当然，也可以直接使用mysqld –initialize，root密码只能到error日志中查找.
5> 安装mysql服务
注意：必须切换到MySQL的bin目录下，不然会将服务目录指定为C:\Program Files\MySQL\MySQL Server 5.7\mysqld
mysqld install是安装服务
mysqld remove是删除服务
通过net start mysql开启mysql服务
C:\WINDOWS\system32>e:E:\>cd mysql-5.7.-winx64/binE:\mysql-5.7.-winx64\bin>mysqld install
Service successfully installed.E:\mysql-5.7.-winx64\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。
6> 登录数据库
39. Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
有5个函数代表当前时间:now,curdate,curtime,unix_timestamp和sysdate,前4个函数返回开始执行语句的时间，而sysdate讲返回函数执行时的时间
40. Sending date
Sending data状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回结果给客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。
41. 查看MySQL的实时内存命中率
mysqladmin -r -i ext -p123456 > /dev/null |awk '{if($2=="Innodb_buffer_pool_read_requests"){all_reads=$4;}else if($2=="Innodb_buffer_pool_reads") {physical_read=$4;if(all_reads==0){print strftime("%H:%M:%S"),"No buffer pool page gets since the last printout"} else {print strftime("%H:%M:%S"),"The ib_bp_read_ratio is",(1-physical_read/all_reads)*100"%"}}}'
输出结果如下：
:: The ib_bp_read_ratio is 92.6106%
:: No buffer pool page gets since the last printout
:: No buffer pool page gets since the last printout
:: No buffer pool page gets since the last printout
:: No buffer pool page gets since the last printout
计算方法可参考：http://ourmysql.com/archives/962
42. 关于float等值查询的问题
如果float没有指定精度，则查询的结果为空
mysql> create table t1(id float);
Query OK, 0 rows affected (0.07 sec)mysql> insert into t1 values(1.23);
Query OK, 1 row affected (0.00 sec)mysql> select * from t1;
+------+
| id |
+------+
| 1.23 |
+------+
1 row in set (0.00 sec)mysql> select * from t1 where id=1.23;
Empty set (0.00 sec)
但是对于double却没有这样的限制
mysql> create table t2(id double);
Query OK, 0 rows affected (0.05 sec)mysql> insert into t2 values(1.23);
Query OK, 1 row affected (0.01 sec)mysql> select * from t2 where id=1.23;
+------+
| id |
+------+
| 1.23 |
+------+
1 row in set (0.00 sec)
如何让上述的float能查询出结果呢？
第一种方式是用like，第二种方式是用format转化为String类型进行比较，2指的是四舍五入后的小数点的位数，当然，不太精确。
第三种方式是直接定义精度
mysql> select * from t1 where id like 1.23;
+------+
| id |
+------+
| 1.23 |
+------+
1 row in set (0.00 sec)mysql> select * from t1 where format(id,2)=1.23;
+------+
| id |
+------+
| 1.23 |
+------+
1 row in set (0.00 sec)mysql> alter table t1 modify id float(3,2);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> select * from t1 where id=1.23;
+------+
| id |
+------+
| 1.23 |
+------+
1 row in set (0.00 sec)
43. 在基于statement的复制情况下，主从的UUID并不一样
主的结果为：
mysql> truncate table test.test;
Query OK, 0 rows affected (0.17 sec)mysql> insert into test.test values(uuid());
Query OK, 1 row affected, 1 warning (0.05 sec)mysql> select * from test.test
-> ;
+--------------------------------------+
| name |
+--------------------------------------+
| a9270a4a-077f-11e6-a117-000c29b05336 |
+--------------------------------------+
1 row in set (0.00 sec)
从的结果为：
mysql> select * from test;
+--------------------------------------+
| name |
+--------------------------------------+
| aa4e8612-077f-11e6-8ce9-000c29de7b01 |
+--------------------------------------+
1 row in set (0.00 sec)
通过mysqlbinlog查看日志，结果如下，直接传的是UUID，而不像自增主键那样会存在上下文信息。
# at 1649
#160421 13:12:36 server id 1 end_log_pos 1755 CRC32 0xf81f5594 Query thread_id=40 exec_time=0 error_code=0
SET TIMESTAMP=1461215556/*!*/;
insert into test.test values(uuid())
/*!*/;
44. 如何修改MySQL字符集
http://www.cnblogs.com/HondaHsu/p/3640180.html
45. MyISQM Vs Innodb
http://blog.sina.com.cn/s/blog_4d398f2101011q6c.html
46. 第一个非空唯一索引作为主键如何理解
create table t1(col1 int not null,col2 int not null,unique key(col2),unique key(col1));
TABLE: name test/t1, id , flags , columns , indexes , appr.rows
COLUMNS: col1: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len ; col2: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len ; DB_ROW_ID: DATA_SYS prtype len ; DB_TRX_ID: DATA_SYS prtype len ; DB_ROLL_PTR: DATA_SYS prtype len ;
INDEX: name col2, id , fields /, uniq , type
root page , appr.key vals , leaf pages , size pages
FIELDS: col2 DB_TRX_ID DB_ROLL_PTR col1
INDEX: name col1, id , fields /, uniq , type
root page , appr.key vals , leaf pages , size pages
FIELDS: col1 col2
从FIELDS: col2 DB_TRX_ID DB_ROLL_PTR col1可以看出，col2做为了主键，而不是col1
47. 如果查看一张表的索引
1> SHOW INDEX FROM yourtable;
2>
SELECT DISTINCT
TABLE_NAME,
INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';
48. A PRIMARY KEY must include all columns in the table’s partitioning function
首先看看社区的解答
https://bugs.mysql.com/bug.php?id=29840
To enforce the uniqueness we only allow mapping of each unique/primary key value to one partition.If we removed this limitation it would mean that for every insert/update we need to check in every partition to verify that it is unique. Also PK-only lookups would need to look into every partition.What could be done is to enhance partitioning with Global Indexes, so that the unique/primary key is partitioned by those fields (or not partitioned at all) and the rest of the data are partitioned on a field not part of the unique/primary key. But this would mean that dropping a 'data' partition would need to do row-by-row deletes in the unique/primary key partitions/index. Also a PK-only lookup which accesses the whole row would need two lookups, the first on the PK index, and then on the 'data' index to retrieve the rest of the row.
个人感觉是，对于分区表，如果要实现主键的唯一性的话，则必须对所有分区进行查询。但这样带来的后果是，效率很低。
现在它这种限制的结果是追求一个分区内的唯一性，通过一个分区内的唯一性可近似的得到全局主键的唯一性（只能说可能，并不必然得到），实际上实现的是一种软的唯一性。
但即便是在同一分区内，有时候也很难实现分区内的唯一性，所以感觉官方的这个功能有点鸡肋，限制多多。
mysql> CREATE TABLE t1(
-> product_id INT,
-> store_date DATETIME,
-> PRIMARY KEY (product_id,store_date))
-> PARTITION BY RANGE (TO_DAYS(store_date) ) (
-> PARTITION p1 VALUES LESS THAN ( TO_DAYS('') ),
-> PARTITION p2 VALUES LESS THAN ( TO_DAYS('') ),
-> PARTITION p3 VALUES LESS THAN ( TO_DAYS('') ));
Query OK, 0 rows affected (0.13 sec)mysql> insert into t1 values(1,'');
Query OK, 1 row affected (0.05 sec)mysql> insert into t1 values(1,'');
Query OK, 1 row affected (0.00 sec)mysql> select * from t1;
+------------+---------------------+
| product_id | store_date |
+------------+---------------------+
| 1 | 2015-12-02 00:00:01 |
| 1 | 2015-12-02 00:00:02 |
+------------+---------------------+
2 rows in set (0.00 sec)
也有一种相近的解释
49. 查看字符集
mysql> select hex(convert('国' using gbk)), hex(convert('国' using utf8)),hex(convert('国' using utf8mb4));
+-------------------------------+--------------------------------+-----------------------------------+
| hex(convert('国' using gbk)) | hex(convert('国' using utf8)) | hex(convert('国' using utf8mb4)) |
+-------------------------------+--------------------------------+-----------------------------------+
| B9FA | E59BBD | E59BBD |
+-------------------------------+--------------------------------+-----------------------------------+
1 row in set (0.00 sec)
50. Java旧版本的下载地址
http://www.oracle.com/technetwork/java/archive-139210.html
51. MySQL Docker镜像的性能情况
MySQL官方的压测结果
http://mysqlserverteam.com/mysql-with-docker-performance-characteristics/
Percona的压测结果
https://www.percona.com/blog/2016/08/03/testing-docker-multi-host-network-performance/
52. Crash-safe slaves
https://www.percona.com/blog/2013/09/13/enabling-crash-safe-slaves-with-mysql-5-6/
http://blog.booking.com/better_crash_safe_replication_for_mysql.html
http://blog.itpub.net/22664653/viewspace-1752588/
53. 何种情况会触发统计信息的收集
1. ANALYZE TABLE [1]
2. OPTIMIZE TABLE [2]
3. When a table is opened [3]. (Don’t forget FLUSH TABLES and FLUSH TABLES WITH READ LOCK)
4. the mysql client starts if the auto-rehash setting is set on (the default) [3]
5. Metadata commands (SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES) or the matching INFORMATION_SCHEMA tables/stats [4]
6. When 1 / 16th of the table or 2Billion rows has been modified, whichever comes first. ./row/row0mysql.c:row_update_statistics_if_needed [4]
7. It is implied that some DDL for a table that causes it to be rebuilt can also trigger an update of statistics. (Ex: ALTER, CREATE INDEX, etc.) [1][2][6]
8. Execute myisamchk –stats_method=method_name –analyze [5]
http://www.khankennels.com/blog/index.php/archives/2012/05/30/updating-mysql-statistics/
54. 如何导入GTID+ROW模式的二进制日志内容
标准做法：mysqlbinlog –skip-gtids /var/lib/mysql/mysql-bin.000008 | mysql
错误做法：mysqlbinlog /var/lib/mysql/mysql-bin.000008 | mysql
mysqlbinlog –skip-gtids -vv –base64-output=decode-rows /var/lib/mysql/mysql-bin.000008 | mysql
55. 每秒获取部分status的值
#!/bin/sh
/usr/local/mysql-advanced-5.6.-linux-glibc2.-x86_64/bin/mysqladmin -h192.168.244. -P3308 -uroot -p123456 -i ext > /dev/null|\
awk -F"|" \
'BEGIN{print "---Time---|Threads_connected Threads_created Threads_running";}{
if ($ ~ /Threads_connected/){Threads_connected=$;}\
else if ($ ~ /Threads_created /){Threads_created=$;}\
else if ($ ~ /Threads_running /){Threads_running=$;}\
else if ($ ~ /Uptime /){\
printf(" %s ",strftime("%H:%M:%S"));\
printf("%18d %15d %15d\n",Threads_connected,Threads_created,Threads_running);\
}}
输出结果如下：
---Time---|Threads_connected Threads_created Threads_running
::
::
::
::
::
56. EVENT
CREATE EVENT e2_test
ON SCHEDULE
EVERY 60 SECOND
starts '2016-10-12 11:55:00'
DO
flush logs;CREATE EVENT e1_test
ON SCHEDULE
EVERY 1 SECOND
starts '2016-10-12 11:55:00'
DO
INSERT INTO test.test1(insert_time)VALUES (now());
57. 复合索引
Composite indexes work just like regular indexes, except they have multi-values keys.
If you define an index on the fields (a,b,c) , the records are sorted first on a, then b, then c.
Example:
| A | B | C |
-------------
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
58. 如何确认1min内产生的redo日志量
mysql> pager grep seq
PAGER set to 'grep seq'
mysql> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 134037580
1 row in set (0.04 sec)1 row in set (1 min 0.04 sec)Log sequence number 194195267
1 row in set (0.12 sec)mysql> nopager
PAGER set to stdout
mysql> select (194195267-134037580)/1024/1024;
+---------------------------------+
| (194195267-134037580)/1024/1024 |
+---------------------------------+
| 57.37084103 |
+---------------------------------+
1 row in set (0.21 sec)
59. 利用存储过程构造测试数据
delimiter //
create procedure p1()
begin
declare v1 int default ;
while v1 <= do
insert into test2.t1 values(v1,'a');
set v1=v1+;
end while;
end//
delimiter ;
60. ACID
原子性
事务必须是原子工作单元，对于其数据修改，要么全都执行，要么全都不执行。比如一个事务要修改100条记录，要不就100条都修改，要不就都不修改。不能发生只修改了其中的50条，而另外50条没有改的情况。
一致性
事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如B数索引或双向链表）都必须是正确的。
隔离性
由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务识别数据所处的状态，要么是另一并发事务修改它之前的状态；要么是修改它之后的状态，事务不会识别中间状态的数据。也就是说，虽然用户是在并发操作，但是事务是串行执行的。对同一个数据对象的操作，事务读写修改是有先后顺序的，不是同一时间什么事情都能同时做的。
持久性
事务完成之后，它对于系统的影响是永久性的，哪怕数据库发生了异常终止，机器掉电，只要数据库文件还是完好的，事务做的修改必须还全部存在。
61. 脏读，不可重复读，幻读
脏读
当一个事务开始更新数据，但是这一个事务并没有完成提交，这时候，第二个事务开始读取数据，把第一个事务所更改的数据读了出来。第二个事务读取的数据是临时的，而且是危险的，因为有可能第一个事务最终做rollback操作。
不可重复读
在一个事务中，我们读取某一行，得到数据，这时候，第二个事务对该行数据进行了修改，然后第一个事务再次读取这一行时，发现数据改变了。也就是在一个事务中，多次读取某一行数据，可能会得到不同的结果，这称为不可重复读。
幻读
在一个事务中，我们读取数据，发现没有特定的行，第一个事务还没有结束。这时候，第二个事务插入了该行数据，然后在第一个事务再次读取时，我们会发现该行突然出现了。这称为幻读。
62. 设置mysql客户端提示符
[mysql]
prompt=(\\u@\\h) [\\d] \\R:\\m:\\s>\\_
63. 使用正则来模糊查询
mysql> select * from test.t12;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | a |
| 3 | b |
| 3 | 12a |
| 4 | a1 |
+------+------+
5 rows in set (0.05 sec)mysql> select * from test.t12 where name REGEXP '^[a-b]';
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | a |
| 3 | b |
| 4 | a1 |
+------+------+
4 rows in set (0.00 sec)
64. MySQL终端输入密码如何不显示warning
# mysql -uroot -p123456 -h192.168.244.10 2> /dev/null
65. MySQL如何截取mysqldump备份文件中某个表的数据
譬如，获取h3表的数据
# cat 1.sql | grep “^– Table structure for table” | grep -A 1 “h3”
-- Table structure for table `h3`
-- Table structure for table `h1`
# sed -n “/^– Table structure for table \`h3\`/,/^– Table structure for table \`h1\`/p” 1.sql > temp.sql
66. 通过performance_schema.events_statements_summary_by_digest获取数据库SQL的耗时分布情况
select digest_text as query,schema_name as db,if(((sum_no_good_index_used > 0) or (sum_no_index_used > 0)),'*','') as full_scan,count_star as exec_count,sum_errors as err_count,sum_warnings as warn_count,concat(round(sum_timer_wait/ 1000000000 , 2), ' ms') as total_latency,concat(round(max_timer_wait/ 1000000000, 2), ' ms') as max_latency, concat(round(avg_timer_wait/ 1000000000, 2), ' ms') as avg_latency,concat(round(sum_lock_time/ 1000000000, 2), ' ms') as lock_latency,sum_rows_sent as rows_sent,round(ifnull((sum_rows_sent / nullif(count_star,0)),0),0) as rows_sent_avg,sum_rows_examined as rows_examined,round(ifnull((sum_rows_examined / nullif(count_star,0)),0),0) as rows_examined_avg,sum_rows_affected as rows_affected,round(ifnull((sum_rows_affected / nullif(count_star,0)),0),0) as rows_affected_avg,sum_created_tmp_tables as tmp_tables,sum_created_tmp_disk_tables as tmp_disk_tables,sum_sort_rows as rows_sorted,sum_sort_merge_passes as sort_merge_passes,digest as digest,first_seen as first_seen,last_seen as last_seen from performance_schema.events_statements_summary_by_digest order by sum_timer_wait desc\G
67. 在debug版本中定位线程操作信息
68. 日志空间满会报如下错误
-- :: [Warning] Disk is full writing '/binlog/mysql-bin.000001' (Errcode: - No space left on device). Waiting f
or someone to free space...-- :: [Warning] Retry in secs. Message reprinted in secs
-- :: [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.
The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: insert into t1 select * from t1 limit -- :: [Warning] Disk is full writing '/binlog/mysql-bin.000001' (Errcode: - No space left on device). Waiting f
or someone to free space...-- :: [Warning] Retry in secs. Message reprinted in secs
69. LOCK TABLE … READ和LOCK TABLE … WRITE
LOCK TABLE … READ和LOCK TABLE … WRITE加的是表级锁。
其中，LOCK TABLE … READ显式加读锁，会阻塞写，此时，通过show processlist查看，是“Waiting for table metadata lock”。
LOCK TABLE … WRITE显示加写锁，会堵塞读和写。
70. 最少语句模拟死锁
71. STATEMENT格式下limit导致的主从复制问题
master上
首先执行第一个会话
root@(none) 09:15:28> create table test.t1(id varchar(2));
Query OK, 0 rows affected (0.07 sec)root@(none) 09:16:00> begin;
Query OK, 0 rows affected (0.00 sec)root@(none) 09:16:06> insert into test.t1 values(1);
Query OK, 1 row affected (0.02 sec)root@(none) 09:16:14> insert into test.t1 values(2);
Query OK, 1 row affected (0.00 sec)root@(none) 09:16:16> insert into test.t1 values(3);
Query OK, 1 row affected (0.00 sec)
打开第二个会话
root@(none) 09:16:24> begin;
Query OK, 0 rows affected (0.00 sec)root@(none) 09:16:26> insert into test.t1 values('a');
Query OK, 1 row affected (0.00 sec)root@(none) 09:16:40> insert into test.t1 values('b');
Query OK, 1 row affected (0.00 sec)root@(none) 09:16:43> insert into test.t1 values('c');
Query OK, 1 row affected (0.01 sec)root@(none) 09:20:01> commit;
Query OK, 0 rows affected (0.03 sec)
提交第一个会话的事务
root@(none) 09:16:18> commit;
Query OK, 0 rows affected (0.00 sec)
master上查看test.t1表的内容
root@(none) 09:20:09> select * from test.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| a |
| b |
| c |
+------+
6 rows in set (0.01 sec)
slave上查看test.t1表的内容
(root@localhost) [(none)] 09:18:45> select * from test.t1;
+------+
| id |
+------+
| a |
| b |
| c |
| 1 |
| 2 |
| 3 |
+------+
6 rows in set (0.07 sec)
在master上对test.t1表进行带有limit子句的update操作
root@(none) 09:20:14> update test.t1 set id='d' limit 3;
Query OK, 3 rows affected, 1 warning (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 1
查看此时master中test.t1表的内容
root@(none) 09:20:53> select * from test.t1;
+------+
| id |
+------+
| d |
| d |
| d |
| a |
| b |
| c |
+------+
6 rows in set (0.00 sec)
查看此时slave中test.t1表的内容
(root@localhost) [(none)] 09:19:15> select * from test.t1;
+------+
| id |
+------+
| d |
| d |
| d |
| 1 |
| 2 |
| 3 |
+------+
6 rows in set (0.00 sec)
72. 将变量恢复到默认值
set global max_allowed_packets=default;
73. 如何设置密码过期
mysql> grant select,delete,update,insert on sbtest.* to 't1'@'%' identified by '';
mysql> alter user 't1'@'%' password expire;
以该用户登录时，会报如下错误：
[root@node1 ~]# mysql -h127.0.0.1 -ut1 -p123
mysql> \s
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> set password=password('');
Query OK, 0 rows affected (0.01 sec)
74. 开启事务的方式
1> 将自动提交设置为0
mysql> set session autocommit=0;
如果要提交一个事务，则每次都需执行commit操作。
2> start transaction
与方式1不一样的是，方式2中执行完commit操作后，这个事务就结束了。如果要重新开启一个事务，则必须再次执行start transaction命令。
75. 关于时间字段的小数部分
MySQL 5.6.4 and up permits fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision.
但是创建表时必须显示指定时间字段的精度，譬如
datetime(6)
76. index hint
mysql强制使用索引:force index(索引名或者主键PRI)
select * from t1 force index(PRI);(强制使用主键)
select * from t1 force index(col_index) ;(强制使用索引”col_index”)
select * from t1 force index(PRI,col_index);(强制使用索引”PRI和col_index”)
mysql禁止某个索引：ignore index(索引名或者主键PRI)
select * from t1 ignore index(PRI);(禁止使用主键)
select * from t1 ignore index(col_index);
select * from t1 ignore index(PRI,col_index) ;
tbl_name [[AS] alias] [index_hint_list]index_hint_list:
index_hint [, index_hint] ...index_hint:
USE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
| IGNORE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
| FORCE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)index_list:
index_name [, index_name] ...
77. MySQL加解密函数
mysql> select hex(AES_ENCRYPT('','salt'));
+--------------------------------------+
| hex(AES_ENCRYPT('','salt')) |
+--------------------------------------+
| E752FD9ED6EA38484EEFD3D01BB8578C |
+--------------------------------------+
row in set (0.00 sec)mysql> select AES_DECRYPT(UNHEX('E752FD9ED6EA38484EEFD3D01BB8578C'), 'salt');
+----------------------------------------------------------------+
| AES_DECRYPT(UNHEX('E752FD9ED6EA38484EEFD3D01BB8578C'), 'salt') |
+----------------------------------------------------------------+
| |
+----------------------------------------------------------------+
row in set (0.00 sec)
78. Starting MySQL.2018-02-25T02:33:55.126515Z mysqld_safe error: log-error set to ‘/dbdata/mysql/3306/log/mysqld.log’, however file don’t exists. Create writable for user ‘mysql’.
解决方法：手动创建并修改权限
79. 格式化gh-ost general log的内容
#!/usr/bin/python
with open(r'/var/lib/mysql/node1.log') as f,open('1.txt','w') as f1:
for line in f:
if line.startswith('2018-03-10T'):
f1.write('\n')
f1.write(line.strip('\n').strip()+' ')with open('1.txt') as f:
for line in f:
if 'Prepare' in line or 'Close stmt' in line:
continue
print line,
80. 如何让mysql客户端的输出在一行中显示
pager less -SFX
81. 如何查出mysql中没有主键的表
SELECT t.table_schema, t.table_name FROM information_schema.tables t LEFT JOIN information_schema.columns c ON t.table_schema = c.table_schema AND t.table_name = c.table_name WHERE t.table_type = 'BASE TABLE' AND t.table_schema NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema') GROUP BY t.table_schema, t.table_name HAVING sum(if(c.column_key='PRI', , )) = ;
82. 如何切割slow log
先mv，再执行flush slow log。
如果要删除的话，切忌直接rm，直接rm的话，mysqld仍然占用该文件的文件句柄，实际上并未删除。还是先mv，再执行flush slow log，最后才手动删除mv后的文件。
83. innobackupex在crontab中执行报错
报错信息如下：
sh: xtrabackup: command not found
innobackupex: Error: no 'mysqld' group in MySQL options at /usr/local/mysql/bin/innobackupex line .
解决方法：
# ln -s /usr/local/mysql/bin/xtrabackup /usr/bin/xtrabackup
84. 如何在线修改mysqld最大连接数的限制
gdb -p mysqld的pid -ex “set max_connections=5000” -batch
85. Oracle中emp表的建表语句
CREATE TABLE emp (
EMPNO INT PRIMARY KEY,
ENAME VARCHAR ( 10 ),
JOB VARCHAR ( 9 ),
MGR INT,
HIREDATE DATE,
SAL DECIMAL ( 7, 2 ),
COMM DECIMAL ( 7, 2 ),
DEPTNO INT
);INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
86. MySQLdb中开启自动提交
conn.autocommit()
87.
# mysql -S /data/mysql_data/mysql.sock
mysql: relocation error: mysql: symbol strmov, version libmysqlclient_16 not defined in file libmysqlclient.so.16 with link time reference
原因：环境变量中存在低版本的mysql
# rpm -qf `which mysql`
mysql-5.1.73-8.el6_8.x86_64
解决：mysql使用绝对路径
88. 在线修改max_connections
gdb -p 93008 -ex “set max_connections=3000” -batch
89. drop大表
create table t1_old like t1;
rename table t1 to t1_purge,t1_old to t1;
ln t1_purge.ibd t1_purge.lnk
set session sql_log_bin=;
drop table t1_purge;
set session sql_log_bin=;while true
do
truncate -c -s -1G t1_purge.lnk
filesize=`du -s t1_purge.lnk |awk '{print $1}'`
echo $filesize
if [ $filesize -eq ]
then
break
fi
sleep
done
90. 清理mysql历史记录
~/.mysql_history
