OracleMySQL碎碎念

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','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 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

    Oracle 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. 如我出一样摆放订单表明,我想他只保留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.11-winx64
datadir = E:\mysql-5.7.11-winx64\data

     4> 初始化数据库

     mysqld –initialize –user=mysql –console

     使用console选项,启动信息会打印在终端台上,包括生成的密码。

     当然,也得以一直利用mysqld
–initialize,root密码只有能够顶error日志中查找.

     Oracle 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> 登录数据库

       Oracle 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_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('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/

     Oracle 4

     Percona的压测结果

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

     Oracle 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操作。

不可再读

于一个事情中,我们读取某同执行,得到数码,这时候,第二独工作对该行数据进行了改动,然后第一个业务再次读取这一行时,发现数变动了。也就是于一个事情中,多次读取某平等尽数据,可能会见拿走不同之结果,这叫做不可再读。

幻读

以一个事务中,我们读取数据,发现没特定的施行,第一只事情还从未终止。这时候,第二独工作插入了该行数据,然后于率先个业务再次读取时,我们见面意识该行突然冒出了。这称为幻读。

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

Oracle 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 … WRITE加的凡表级锁。

其间,LOCK TABLE … READ显式加读锁,会卡住写,此时,通过show
processlist查看,是“Waiting for table metadata lock”。

LOCK TABLE … WRITE显示加写锁,会堵塞读与描绘。

70. 无限少语句模拟死锁

Oracle 7

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 '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免均等的是,方式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_indexOracle)
;(强制行使索引”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] ...

 

     

相关文章