mysql常用语句汇总威尼斯人6799.com

1. 怎样校订Mysql的顾客密码

mysqld 常规MySQL服务器
mysqld-opt 优化mysql服务器,提供部分作用能够发掘越来越好的效应
mysqld-max 与mysqld同样,但足以支撑立异,更具实验性质的功能(更不平静)

mysql> update mysql.user set password=password('hello') where user='root';
mysql> flush privileges;

++安装mysql
参见自带的INSTALL-SOURCE文件
$ ./configure ?prefix=/app/mysql-5.0.51a ?with-charset=utf8
?with-extra-charsets=utf8,gb2312,utf8

2. 有关分区数量的约束

++启动/关闭mysql
$ path/mysqld_safe -user=mysql &
$ /mysqladmin -p shutdown

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.

++修改root口令
$ mysqladmin -u root -p password ‘新密码’

3. 什么样查看当前使用的数据库

++查看服务器状态
$ path/mysqladmin version -p

   mysql> select database();

++连接远端mysql服务器
$ path/mysql -u 用户名 -p #连东瀛机
$ path/mysql -h 远程主机IP -u 顾客名 -p#老是远程MYSQL服务器

4. 哪些查看当前数据库的版本

++成立/删除 数据库或表
$ mysqladmin -u root -p create xxx
mysql> create database 数据库名;
mysql> create TABLE items (
id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
symbol CHAR(4) NOT NULL,
username CHAR(8),
INDEX sym (symbol),INDEX …..
UNIQUE(username)
) type=innodb;
mysql> drop database [if exists] 数据库名
mysql> create table 表名;
mysql> drop table 表名;

   mysql> select version();

++查看数据库和查看数据库下的表
mysql> show databases;
mysql> show tables;
mysql> show table status;
mysql> desc 表名; #翻看具体表结构消息
mysql> SHOW CREATE DATABASE db_name #显示创制db_name库的语句
mysql> SHOW CREATE TABLE tbl_name #展现创造tbl_name表的说话

5. MySQL指令行导入SQL语句文件

++创立客户
mysql> grant select,insert,update,delete,alter on mydb.* to
[email protected]
identified by “abc”;
mysql> grant all privileges on *.* to
[email protected]”%”
identified by “abc”;
mysql> flush privileges;

   # mysql -u root -p123456 test < 123.sql

++客商管理
mysql> update user set password=password (’11111′) where
user=’test1′; #修改test1密码为111111
mysql> DELETE FROM user WHERE User=”testuser” and Host=”localhost”;
#去除客商帐号
mysql> SHOW GRANTS FOR user1; #展示创立user1客户的grant语句

   个中,test为数据库名

++mysql数据库的备份和苏醒
$ mysqldump -uuser -ppassword -B DB_name [–tables table1 –tables
table2] > exportfile.sql
$ mysql -uroot -p xxx < aaa.sql #导入表
$ mysqldump -u 客商名 -p 数据库名 表名> 导出的公文名 ##导出单独的表

6. MySQL日记文件的任务

++导出叁个数据库结构
$ mysqldump -u wcnc -p -d ?add-drop-table smgp_apps_wcnc
>wcnc_db.sql
-d 未有数据 ?add-drop-table 在种种create语句以前扩大一个drop table

   /var/log/mysqld.log

++忘记mysql密码
先结束全体mysql服务进度
$ mysqld_safe ?skip-grant-tables & mysql
mysql> use mysql;
mysql> update user set password=password(’111111′) where
user=’root’;
mysql> flush privileges;
下一场重启mysql并以新密码登录就可以

   可改良/etc/init.d/mysqld脚本进行自定义

++当前利用的数据库
mysql> select database();

7. 什么样查看表的目录  

===数据库日常操作维护====
++创建表
mysql> create table table_name
(column_name datatype {identity |null|not null},f_time
TIMESTAMP(8),…)ENGINE=MyISAM AUTO_INCREMENT=3811 DEFAULT
CHARSET=utf8;
例: CREATE TABLE guest (name varchar(10),sex varchar(2),age
int(3),career varchar(10));
# desc guest可查看表结构新闻
# TIMESTAMP(8) YYYYMMDD 在那之中(2/4/6/8/10/12/14)对应分化的年华格式
mysql> SHOW CREATE TABLE tbl_name #来得创制tbl_name表的话语

mysql> show index from tblname;

++创制索引
能够在建表的时候步向index indexname (列名)创制索引,
也能够手工业用命令生成 create index index_name on table_name
(col_name[(length)],… )
mysql> CREATE INDEX number ON guest (number(10));
mysql> SHOW INDEX FROM tbl_name [FROM db_name] #展现现成索引
mysql> repair TABLE date QUICK; #索引列相关变量变化后自行重建索引

mysql> show keys from tblname;

++查询及常用函数
mysql> select t1.name, t2.salary from employee AS t1, info AS t2
where t1.name = t2.name;
mysql> select college, region, seed from tournament ORDER BY region,
seed;
mysql> select col_name from tbl_name WHERE col_name > 0;
mysql> select DISTINCT …… [DISTINCT关键字能够除去重复的记录]
mysql> select DATE_FORMAT(NOW(),’%m/%d/%Y’) as DATE,
DATE_FORMAT(NOW(),’%H:%m:%s’) AS TIME;
mysql> select
CURDATE(),CURTIME(),YEAR(NOW()),MONTH(NOW()),DAYOFMONTH(NOW()),HOUR(NOW()),MINUTE(NOW());
mysql> select
UNIX_TIMESTAMP(),UNIX_TIMESTAMP(20080808),FROM_UNIXTIME(UNIX_TIMESTAMP());
mysql> select PASSWORD(”secret”),MD5(”secret”); #加密密码用
mysql> select count(*) from tab_name order by id [DESC|ASC];
#DESC倒序/ASC正序

· 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

*
函数count,AVG,SUM,MIN,MAX,LENGTH字符长度,LTLX570IM去除起头的无效,RTEnclaveIM去后面部分空格,T奥德赛IM(str)去除首部尾巴部分空格,LETF/安德拉IGHT(str,x)再次来到字符串str的右侧/侧面x个字符,SUBST酷路泽ING(str,x,y)重临str中的x地方起至地方y的字符mysql>
select BINARAV4Y ‘ross’ IN (’Chandler’,’Joey’, ‘Ross’);
#BINAWranglerY严刻检查大小写

8. SQL注入

* 相比运算符IN,BETWEEN,IS NULL,IS NOT NULL,LIKE,REGEXP/福睿斯LIKE
mysql> select count(*),AVG(number_xx),Host,user from mysql.user
GROUP by user [DESC|ASC] HAVING user=root; #分组并计算次数/平均值

譬喻说在下例中,就算name传入的值为tom’ or 1=1
–‘,尽管password的值是荒唐的,还是能够收获客商名和密码

++UNIX_TIMESTAMP(date)
再次回到叁个Unix时间戳记(从’1969-01-01 00:00:00′红霉素T开首的秒数)
mysql> select UNIX_TIMESTAMP();
mysql> select UNIX_TIMESTAMP(’1997-10-04 22:23:00′);
mysql> select FROM_UNIXTIME(875996580); #基于时间戳记算出日期

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)

++调节标准函数
mysql> select if(1<10,2,3), IF(55>100,’true’,’false’);
#IF()函数有八个参数,第一个是被判别的表明式,若是表明式为真,再次来到第二个参数,借使为假,重回第一个参数.
mysql> select CASE WHEN (2+2)=4 THEN “OK” WHEN (2+2)<>4 THEN
‘NOT OK’ END AS status;

9. 哪些查看MySQL的存款和储蓄进度。

++系统音信函数
mysql> select DATABASE(),VERSION(),USER();
mysql> select BENCHMARK(9999999,LOG(RAND()*PI())) AS PERFORMANACE;
#二个测验mysql运算质量工具

    1> 查看当前数据仓库储存款和储蓄进程。

++将wp_posts表中post_content字段汉语字”old”替换为”new”
mysql> update wp_posts set
post_content=replace(post_content,’old’,’new’)

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)

++改造表结构
mysql> alter table table_name alter_spec [, alter_spec …]
例:alter table dbname add column userid int(11) not null primary key
auto_increment;
那样,就在表dbname中增加了八个字段userid,类型为int(11)。

     2> 查看有个别存款和储蓄进程的创办语句

++调节列顺序
mysql> alter table tablename CHANGE id id int(11) first;

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)

++更改表中数据
insert [into] table_name [(column(s))] values (expression(s))
例:mysql>insert into mydatabase
values(’php’,’mysql’,’asp’,’sqlserver’,’jsp’,’oracle’);
mysql> create table user select host,user from mysql.user where
1=0;
mysql> insert into user(host,user) select host,user from mysql.user;

   3> 通过mysql数据库中的proc表查看

++改正表名
命令:rename table 原表名 to 新表名;

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)

++表的数量更新
mysql> update table01 set field04=19991022[, field05=062218] where
field01=1;

10. ACID

++删除数据
mysql> delete from table01 where field01=3;
#如若想要清空表的有所记录,建议用truncate table tablename实际不是delete
from tablename.

原子性(Atomicity)

++SHELL提醒符下运维SQL命令
$ mysql -e “show slave statusG ”

原子性是指工作是三个不可分割的劳作单位,事务中的操作依旧都发生,要么都不发出。 原子性是指专业是一个不可分割的专门的学业单位,事务中的操作依然都发生,要么都不爆发。 

++坏库扫描修复
cd /var/lib/mysql/xxx && myisamchk playlist_block

一致性(Consistency)

++insert into a (x) values (’11a’)
出现: ata truncated for column ‘x’ at row 1
消除办法:
在my.ini里找到
sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_Create_USER,NO_ENGINE_SUBSTITUTION”
把内部的STENVISIONICT_TRANS_TABLES,去掉,然后重启mysql就ok了

作业必需使数据库从叁个风度翩翩致性状态转换来此外二个风流罗曼蒂克致性状态。

++复制表
mysql> create table target_table like source_table

隔离性(Isolation)

++innodb扶持工作
新表:create TABLE table-name (field-definitions) TYPE=INNODB;
旧表: alter TABLE table-name TYPE=INNODB;
mysql> start transaction #标识叁个作业的始发
mysql> insert into….. #数据变动
mysql> ROLLBACK或commit #回滚或提交
mysql> SET AUTOCOMMIT=1; #安装自动提交
mysql> select @@autocommit; #查看当前是还是不是自动提交

作业的隔绝性是四个客商并发访问数据库时,数据库为每三个客户展开的事体,不可能被其余交事务情的操作数据所苦闷,多少个冒出事务之间要相互隔开。

++表锁定相关
mysql> LOCK TABLE users READ; # 对user表举办只读锁定
mysql> LOCK TABLES user READ, pfolios WRITE #多表锁调控
mysql> UNLOCK TABLES; #无需钦命锁定表名字,
MySQL会自行消释全体表锁定

持久性(Durability)

=====一些mysql优化与治本======
++管理用命令
mysql> show variables #查看全体变量值
? max_connections 数据库允许的最大可连接数,
#亟需加大max_connections可以在my.cnf中加入set-variable =
max_connections=32004,能够对与下部的threads_connected值决定是或不是须要增大.

悠久性是指二个事情大器晚成旦被交给,它对数据库中数据的改正正是恒久性的,接下去就是数据库产生故障也不应该对其有别的影响。

show status [like ….];
? threads_connected 数据库当前的连接线程数
#FLUSH STATUS 能够重新载入参数一些流速计

11. Can’t locate ExtUtils/MakeMaker.pm
in @INC

show processlist;
kill id;

在安装percona-toolkit的进度中,在实践perl Makefile.PL时报以上错误。

++my.cnf配置
?Enable Slow Query Log
long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log
log-queries-not-using-indexes

鸡犬不留办法:yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

# mysqldumpslow -s c -t 20 host-slow.log #访问次数最多的二十一个sql语句
# mysqldumpslow -s r -t 20 host-slow.log #回到记录集最多的十八个sql

12. Can’t locate Digest/MD5.pm in
@INC

?others
max_connections=500 #用过的最安卡拉接数SHOW Status like
‘max_used_connection’;
wait_timeout=10 #悬停全体空闲时间抢先 10 秒的接连
table_cache=64 #其余时间展开表的总的数量
ax_binlog_size=512M #巡回早前二进制日志的最大局面
max_connect_errors = 100

在施行pt-table-checksum  –help的历程中,报以上错误

query_cache_size = 256M #查询缓存
#可用 SHOW STATUS LIKE ‘qcache%’;查看命中率
#FLUSH STATUS重新载入参数流量计, FLUSH QUESportageY CACHE清缓存

减轻方式:yum install perl-Digest-MD5

thread_cache = 40
#线程使用,SHOW STATUS LIKE ‘Threads_created %’; 值飞速增添的话考虑加大

13. 修改root密码的三种方式

key_buffer = 16M
#show status like ‘%key_read%’; Key_reads
代表命中磁盘的显要字诉求个数
#A: 到底 Key Buffer 要设定多少才够啊? Q: MySQL 只会 Cache
索引(*.MYI),因而参谋全部 MYI文件的总大小

1. mysql> set password for ‘root’@’localhost’=password(‘123’);
不须求刷新权限表

sort_buffer_size = 4M #询问排序时所能使用的缓冲区大小,每连接独享4M
#show status like ‘%sort%’; 如sort_merge_passes非常大,就代表加大

2. mysql> update mysql.user set password=password(“456″) where
user=”root” and host=”localhost”;

sort_buffer_sizesort_buffer_size = 6M
#查询排序时所能使用的缓冲区大小,这是每连接独享值6M
read_buffer_size = 4M #读查询操作所能使用的缓冲区大小
join_buffer_size = 8M #一同查询操作所能使用的缓冲区大小
skip-locking #撤销毁文件件系统的表面锁
skip-name-resolve
thread_concurrency = 8  #最大并发线程数,cpu数量*2
long_query_time = 10 #Slow_queries记数器的询问时间阀值

    mysql> flush privileges;

http://www.bkjia.com/Mysql/489697.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/489697.htmlTechArticlemysqld 常规MySQL服务器 mysqld-opt
优化mysql服务器,提供一些意义能够开掘越来越好的成效 mysqld-max
与mysqld同样,但能够扶助改革,更具实验性质的功能…

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:Ali的开源工具

     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

    威尼斯人6799.com 1

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 

23
mysql-bin.000058
11653557
192.168.244.145
repl
repl
3306
60
0

   那样会带来安全祸患。

   解决方法:修正参数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. 诸如作者有一张订单表,笔者梦想她只保留四个月的多寡,别的数据归档到别的数据库中(跨MySQL实例卡塔尔

      pt-archiver

     
参照他事他说加以考查文书档案:威尼斯人6799.com,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.11-winx64
datadir = E:\mysql-5.7.11-winx64\data

     4> 开始化数据库

     mysqld –initialize –user=mysql –console

     使用console选项,运转新闻会打字与印刷在终端台上,满含生成的密码。

     当然,也得以平素动用mysqld
–initialize,root密码只可以到error日志中查找.

     威尼斯人6799.com 2

   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.11-winx64/bin

E:\mysql-5.7.11-winx64\bin>mysqld install
Service successfully installed.

E:\mysql-5.7.11-winx64\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

      6> 登入数据库

       威尼斯人6799.com 3

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 printout
15:11:49 No buffer pool page gets since the last printout
15:11:50 No buffer pool page gets since the last printout
15: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: 0

mysql> 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 23, flags 1, columns 5, indexes 2, appr.rows 0
  COLUMNS: col1: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; col2: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
  INDEX: name col2, id 25, fields 1/4, uniq 1, type 3
   root page 3, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  col2 DB_TRX_ID DB_ROLL_PTR col1
  INDEX: name col1, id 26, fields 1/2, uniq 1, type 2
   root page 4, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  col1 col2

       从FIELDS: col2 DB_TRX_ID DB_ROLL_PT智跑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('20151202') ),
    ->     PARTITION p2 VALUES LESS THAN ( TO_DAYS('20151203') ),
    ->     PARTITION p3 VALUES LESS THAN ( TO_DAYS('20151204') ));             
Query OK, 0 rows affected (0.13 sec)

mysql> insert into t1 values(1,'20151202000001');
Query OK, 1 row affected (0.05 sec)

mysql> insert into t1 values(1,'20151202000002');
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)  

      也可能有生机勃勃种恍若的解释

   
  https://www.quora.com/Mysql-Why-only-unique-primary-keys-must-have-all-the-columns-used-in-partitioning-expression

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/

     威尼斯人6799.com 4

     Percona的压测结果

   
 https://www.percona.com/blog/2016/08/03/testing-docker-multi-host-network-performance/

     威尼斯人6799.com 5

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://mysqlserverteam.com/relay-log-recovery-when-sql-threads-position-is-unavailable/?utm_source=tuicool&utm_medium=referral

      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.23-linux-glibc2.5-x86_64/bin/mysqladmin -h192.168.244.10 -P3308 -uroot -p123456 -i 1 ext 2> /dev/null|\
awk -F"|" \
'BEGIN{print "---Time---|Threads_connected Threads_created Threads_running";}{
if ($2 ~ /Threads_connected/){Threads_connected=$3;}\
else if ($2 ~ /Threads_created /){Threads_created=$3;}\
else if ($2 ~ /Threads_running /){Threads_running=$3;}\
else if ($2 ~ /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
 11:13:52                  2             145               1
 11:13:53                  2             145               1
 11:13:54                  2             145               1
 11:13:55                  2             145               1
 11:13:56                  2             145               1

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 |
-------------
| 1 | 2 | 3 |
| 1 | 4 | 2 |
| 1 | 4 | 4 |
| 2 | 3 | 5 |
| 2 | 4 | 4 |
| 2 | 4 | 5 |

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 0;
  while v1 <=100000 do
    insert into test2.t1 values(v1,'a');
    set v1=v1+1;
  if v1 %1000 =0 then 
    commit;
  end if;
  end while;
end//
delimiter ;

 

60. ACID

原子性

事情必得是原子专业单元,对于其数据修改,要么全都实践,要么全都不推行。比如一个作业要修正100条记下,要不就100条都更正,要不就都不更正。不可能发出只改进了里面包车型大巴50条,而除此以外50条未有改的意况。

一致性

事情在达成时,必需使全体的数据都保持豆蔻年华致状态。在相关数据库中,全数规规矩矩都不得不运用于事情的校订,以保险全部数据的完整性。事务甘休时,全体的此中数据结构(如B数索引或双向链表卡塔尔国都必需是不容置疑的。

隔离性

由并发事务部做的更动必需与其他其余并发办事处做的更改隔开。事务识别数据所处的情景,要么是另后生可畏并发事务改过它在此之前的动静;要么是修正它今后的气象,事务不会识别中间状态的多寡。也正是说,尽管客户是在产出操作,可是职业是串行实施的。对同多少个数据对象的操作,事务读写修改是有前后相继顺序的,不是同期什么业务都能况兼做的。

持久性

专门的学业达成今后,它对于系统的熏陶是恒久性的,哪怕数据库爆发了老大终止,机器掉电,只要数据库文件或然完全的,事务做的退换必需还全方位存在。

61. 脏读,不可重复读,幻读

脏读

当多少个事务开头更新数据,可是那三个事情并从未成功提交,那时候,第一个专业初步读取数据,把第一个事务厅改进的多少读了出来。第一个事情读取的数据是临时的,何况是生死攸关的,因为有希望率先个事情最后做rollback操作。

不行重复读

在三个业务中,大家读取某风度翩翩行,获得数码,此时,第一个事情对该行数据开展了退换,然后第三个职业再度读取那风度翩翩行时,发掘数目变动了。也正是在一个事务中,数次读取某意气风发行数据,恐怕会收获不一样的结果,那名称叫不可重复读。

幻读

在一个思想政治工作中,大家读取数据,开掘未有特定的行,第一个业务还并没有终止。那时候,第1个事情插入了该行数据,然后在首先个业务再次读取时,大家会意识该行忽地冒出了。那称之为幻读。

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版本中定位线程操作消息

威尼斯人6799.com 6

68. 日记空间满会报如下错误

2017-03-21 18:13:50 2387 [Warning] Disk is full writing '/binlog/mysql-bin.000001' (Errcode: 28 - No space left on device). Waiting f
or someone to free space...2017-03-21 18:13:50 2387 [Warning] Retry in 60 secs. Message reprinted in 600 secs
2017-03-21 18:14:57 2387 [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 102017-03-21 18:15:51 2387 [Warning] Disk is full writing '/binlog/mysql-bin.000001' (Errcode: 28 - No space left on device). Waiting f
or someone to free space...2017-03-21 18:15:51 2387 [Warning] Retry in 60 secs. Message reprinted in 600 secs

69. LOCK TABLE … READ和LOCK TABLE …
WRITE

LOCK TABLE … READ和LOCK TABLE … W凯雷德ITE加的是表级锁。

个中,LOCK TABLE … READ显式加读锁,会梗塞写,那个时候,通过show
processlist查看,是“Waiting for table metadata lock”。

LOCK TABLE … W昂科威ITE展现加写锁,会杜绝读和写。

70. 起码语句模拟死锁

威尼斯人6799.com 7

71.
STATEMENT格式下limit招致的主从复制难题

     master上

     首西施行第2个会话

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 '123';
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('123456');
Query OK, 0 rows affected (0.01 sec)

74. 开启事务的措施

     1> 将机关提交设置为0

     mysql> set session autocommit=0;

     假使要交给贰个作业,则每便都需进行commit操作。

     2> start transaction

   
 与方法1不意气风发致的是,格局第22中学实践完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(索引名恐怕主键P奥迪Q5I)

      select * from t1 force index(P奥德赛I);(强制行使主键)

      select * from t1 force index(col_index)
;(强制行使索引”col_index”)

      select * from t1 force
index(PRI,col_index);(强制行使索引”P宝马7系I和col_index”)

      mysql禁止有些索引:ignore index(索引名或然主键PEscortI)

      select * from t1 ignore index(PENCOREI);(禁绝接受主键)

      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] ...