MySQL碎碎念
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. 如何查看表的索引
· 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','123456');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 findByIdG*************************** 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; endcharacter_set_client: utf8collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci1 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('20160225','%w') DAY),'%Y-%m-%d') Monday, DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 7-DATE_FORMAT('20160225','%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
23mysql-bin.00005811653557192.168.244.145replrepl3306600
这样会带来安全隐患。
解决方法：修改参数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 end2016-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-winx64bin
3> 编辑配置文件my-default.ini
basedir = E:mysql-5.7.11-winx64datadir = E:mysql-5.7.11-winx64data
4> 初始化数据库
mysqld --initialize --user=mysql --console
使用console选项，启动信息会打印在终端台上，包括生成的密码。
当然，也可以直接使用mysqld --initialize，root密码只能到error日志中查找.
5> 安装mysql服务
注意：必须切换到MySQL的bin目录下，不然会将服务目录指定为C:Program FilesMySQLMySQL Server 5.7mysqld
mysqld install是安装服务
mysqld remove是删除服务
通过net start mysql开启mysql服务
C:WINDOWSsystem32>e:E:>cd mysql-5.7.11-winx64/binE:mysql-5.7.11-winx64bin>mysqld installService successfully installed.E:mysql-5.7.11-winx64bin>net start mysqlMySQL 服务正在启动 .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 1 ext -p123456 2> /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"%"}}}'
输出结果如下：
15:11:47 The ib_bp_read_ratio is 92.6106%15:11:48 No buffer pool page gets since the last printout15:11:49 No buffer pool page gets since the last printout15:11:50 No buffer pool page gets since the last printout15:11:51 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=0SET TIMESTAMP=1461215556/*!*/;insert into test.test values(uuid())/*!*/;