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)

原子性是指工作是多个不可分割的做事单位,事务中的操作照旧都发出,要么都不发出。 原子性是指工作是1个不可分割的办事单位,事务中的操作如故都发生,要么都不发生。 

一致性(Consistency)

作业必须使数据库从叁个一致性状态变换来其余多个一致性状态。

隔离性(Isolation)

工作的隔开分离性是四个用户并发访问数据库时,数据库为每种用户打开的业务,不能够被其余业务的操作数据所苦恼,多个冒出事务之间要相互隔断。

持久性(Durability)

持久性是指1个作业一旦被交给,它对数据库中数据的改动正是永久性的,接下去正是数据库发生故障也不应该对其有其余影响。

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. 会话变量和全局变量

一 、当服务器运转时多多的变量能够动态改变。

贰 、客户端只好改成自个儿的对话变量,不能够改变别的客户端的对话变量,退出客户端时变量复原,并且不会影响别的客户端,系统变量影响全局。

三 、服务器运营时,将全局变量开始化为暗中同意值,这几个暗中同意值能够在布署文件或指令行中更改。想要更改全局变量,必须拥有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’;

伍 、设置变量时不钦赐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

    Oracle 1

23. 慢查询的光阴设置的是4s,但slow日志里面却记录了广大0.00几秒的查询?

Oracle,     与参数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. 诸如小编有一张订单表,作者盼望他只保留5个月的数据,别的数据归档到任何数据库中(跨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. 

   
  有四个函数代表当前时刻:now,curdate,curtime,unix_timestamp和sysdate,前多个函数再次回到起先执行语句的岁月,而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,第3种艺术是用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福特Explorercol1得以看来,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. 脏读,不可重复读,幻读

脏读

当一个工作开首更新数据,然则这八个作业并不曾做到提交,那时候,第一个事情开首读取数据,把第3个事务所更改的数目读了出去。第一个工作读取的数码是暂且的,而且是间不容发的,因为有概率先个事情最终做rollback操作。

不可重复读

在一个工作中,我们读取某一行,得到数码,那时候,第2个事情对该行数据实行了改动,然后第三个工作再一次读取这一行时,发现数目变动了。也便是在三个政工中,数次读取某一行数据,也许会得到不相同的结果,这称之为不可重复读。

幻读

在多少个工作中,大家读取数据,发现没有特定的行,第二个业务还未曾终止。那时候,第一个事情插入了该行数据,然后在首先个工作再一次读取时,大家会发觉该行突然出现了。那叫做幻读。

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 … WHighlanderITE加的是表级锁。

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

LOCK TABLE … W汉兰达ITE展现加写锁,会杜绝读和写。

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;

     要是要交给1个作业,则每一趟都需实行commit操作。

     2> start transaction

   
 与办法1不均等的是,情势2中执行完commit操作后,那几个业务就过逝了。要是要重新开启1个业务,则必须另行实施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奇骏I)

      select * from t1 force index(PHavalI);(强制行使主键)

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

      select * from t1 force
index(PRI,col_index);(强制行使索引”PCR-VI和col_index”)

      mysql禁止有些索引:ignore index(索引名恐怕主键P汉兰达I)

      select * from t1 ignore index(P昂CoraI);(禁用主键)

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

 

     

相关文章