创建表
简单的方式:

CREATE TABLE person (

number INT(11),

name VARCHAR(255),

birthday DATE

);

或者是

CREATE TABLE IF NOT EXISTS person (

number INT(11),

name VARCHAR(255),

birthday DATE

);

查看mysql创建表:

> SHOW CREATE table person;



CREATE TABLE `person` (

  `number` int(11) DEFAULT NULL,

  `name` varchar(255) DEFAULT NULL,

  `birthday` date DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

查看表所有的列：

> SHOW FULL COLUMNS from person;

+----------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+

| Field    | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |

+----------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+

| number   | int(11)      | NULL            | YES  |     | NULL    |       | select,insert,update,references |         |

| name     | varchar(255) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |

| birthday | date         | NULL            | YES  |     | NULL    |       | select,insert,update,references |         |

+----------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+

创建临时表

CREATE TEMPORARY TABLE temp_person (

number INT(11),

name VARCHAR(255),

birthday DATE

);

在创建表格时，您可以使用TEMPORARY关键词。只有在当前连接情况下，TEMPORARY表才是可见的。当连接关闭时，TEMPORARY表被自动取消。这意味着两个不同的连接可以使用相同的临时表名称，同时两个临时表不会互相冲突，也不与原有的同名的非临时表冲突。（原有的表被隐藏，直到临时表被取消时为止。）您必须拥有CREATE TEMPORARY TABLES权限，才能创建临时表。

如果表已存在，则使用关键词IF NOT EXISTS可以防止发生错误。

CREATE TABLE IF NOT EXISTS person2 (

number INT(11),

name VARCHAR(255),

birthday DATE

);

注意，原有表的结构与CREATE TABLE语句中表示的表的结构是否相同，这一点没有验证。注释：如果您在CREATE TABLE...SELECT语句中使用IF NOT EXISTS，则不论表是否已存在，由SELECT部分选择的记录都会被插入

在CREATE TABLE语句的末尾添加一个SELECT语句，在一个表的基础上创建表

CREATE TABLE new_tbl SELECT [i] FROM orig_tbl;

注意，用SELECT语句创建的列附在表的右侧，而不是覆盖在表上

mysql> SELECT [/i] FROM foo;

+---+

| n |

+---+

| 1 |

+---+

mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;

mysql> SELECT * FROM bar;

+------+---+

| m    | n |

+------+---+

| NULL | 1 |

+------+---+

也可以明确地为一个已生成的列指定类型

CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;

根据其它表的定义（包括在原表中定义的所有的列属性和索引），使用LIKE创建一个空表:

CREATE TABLE new_tbl LIKE orig_tbl;

 创建一个有主键，唯一索引，普通索引的表:

CREATE TABLE `people` (

  `peopleid` smallint(6) NOT NULL AUTO_INCREMENT,

  `firstname` char(50) NOT NULL,

  `lastname` char(50) NOT NULL,

  `age` smallint(6) NOT NULL,

  `townid` smallint(6) NOT NULL,

  PRIMARY KEY (`peopleid`),

  UNIQUE KEY `unique_fname_lname`(`firstname`,`lastname`),

  KEY `fname_lname_age` (`firstname`,`lastname`,`age`)

) ;

其中peopleid是主键,以firstname和lastname两列建立了一个唯一索引,以firstname,lastname,age三列建立了一个普通索引
 
删除表
 

DROP TABLE  tbl_name;



或者是



DROP TABLE IF EXISTS tbl_name;

清空表数据

TRUNCATE TABLE table_name

MySQL支持的三种复制模式分别为：

1. asynchronous 异步复制
2. fully synchronous 全同步复制
3. semi synchronous 半同步复制

异步复制 (asynchronous replication)

原理：在异步复制中，master写数据到binlog且sync，slave request binlog后写入relay-log并flush disk
优点：复制的性能最好
缺点: master挂掉后，slave可能会丢失事务
代表：MySQL原生的复制

全同步复制 (fully synchronous replication)

原理：在全同步复制中，master写数据到binlog且sync，所有slave request binlog后写入relay-log并flush disk，并且回放完日志且commit
优点：数据不会丢失
缺点：会阻塞master session，性能太差，非常依赖网络
代表：MySQL Cluster

半同步复制 (semi synchronous replication)

1. 普通的半同步复制

原理: 在半同步复制中，master写数据到binlog且sync，且commit，然后一直等待ACK。当至少一个slave request bilog后写入到relay-log并flush disk，就返回ack（不需要回放完日志）
优点：会有数据丢失风险（低）
缺点：会阻塞master session，性能差，非常依赖网络，
代表：after commit, 原生的半同步

重点：由于master是在三段提交的最后commit阶段完成后才等待，所以master的其他session是可以看到这个提交事务的，所以这时候master上的数据和slave不一致，master crash后，slave数据丢失

2. 增强版的半同步复制（lossless replication）

原理: 在半同步复制中，master写数据到binlog且sync，然后一直等待ACK. 当至少一个slave request bilog后写入到relay-log并flush disk，就返回ack（不需要回放完日志）
优点：数据零丢失（前提是让其一直是lossless replication），性能好
缺点：会阻塞master session，非常依赖网络
代表：after sync, 原生的半同步

重点：由于master是在三段提交的第二阶段sync binlog完成后才等待, 所以master的其他session是看不见这个提交事务的，所以这时候master上的数据和slave一致，master crash后，slave没有丢失数据

重要参数:

参数	评论	默认值	推荐值	是否动态
rpl_semi_sync_master_wait_for_slave_count	至少有N个slave接收到日志	1	1	dynamic
rpl_semi_sync_master_wait_point	等待的point	AFTER_SYNC	AFTER_SYNC	dynamic
rpl_semi_sync_master_timeout	切换复制的timeout	1000（10s）	1000 (1s)	dynamic
rpl_semi_sync_master_enabled	是否开启半同步	OFF	ON	dynamic
rpl_semi_sync_slave_enabled	是否开启半同步	OFF	ON	dynamic

如何开启lossless replication:

########semi sync replication settings########

plugin_dir=/usr/local/mysql/lib/plugin

plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

loose_rpl_semi_sync_master_enabled = 1

loose_rpl_semi_sync_slave_enabled = 1

loose_rpl_semi_sync_master_timeout = 1000

实践是检验真理的唯一标准

如何检验上述after_sync 和 after_commit, 如何检验上述原理的正确性。

InnoDB commit: 三阶段提交过程
A阶段: wite prepare log 写入Xid
B阶段: write binlog
C阶段: write commit log

测试点：master上当一个事务Waiting for semi-sync ACK from slave的时候，后来的事务是在A,B,C哪个阶段卡住呢？

0，RC模式



1. semi-sync C阶段等待



假设设置time-out=100000s，当事务一提交了一个大事务，在write commit log(C阶段)时候等待，

那么第二个事务在敲commit命令的时候，是卡在哪个阶段呢？是卡在 wite prepare log（A阶段）？还是write binlog（B阶段）？还是write commit log（C阶段）



测试：semi-sync vs loss-less semi-sync



【semi-sync】 C阶段等待

0, 开启事务1，然后在slave上执行stop slave，制造timeout的情况，让其阻塞。(Waiting for semi-sync ACK from slave)

1，在开启一个事务2，事务2插入一条特殊记录（XXXXX）。 (Waiting for semi-sync ACK from slave)

2，在开启一个事务3。

2.1，测试案例：这个时候，kill -9 mysqld，造成人为的mysql crash

3，假设卡在A阶段，那么事务3，肯定是看不到事务1,2写入的记录（XXXXX）,且重启mysql后，事务2不会提交。

4，假设卡在C阶段，那么事务3，肯定是可以看见事务1，2写入的记录（XXXXX）。



经过测试：

1，是卡在C阶段，也就是说事务3是可以看见事务1，事务2的。

2，MySQL crash重启后，事务1，事务2的dml都已经提交成功，说明不是卡在A阶段



【loss-less semi-sync】B阶段等待



0, 开启事务1，然后在slave上执行stop slave，制造timeout的情况，让其阻塞。(Waiting for semi-sync ACK from slave)

1，在开启一个事务2，事务2插入一条特殊记录（XXXXX）。(Waiting for semi-sync ACK from slave)

2，在开启一个事务3

3，假设卡在A阶段，那么事务3，肯定是看不到事务1,2写入的记录（XXXXX）,且重启mysql后，事务2不会提交。。

4，假设卡在B阶段，那么事务3，肯定是可以看见事务1,2写入的记录（XXXXX）,且重启mysql后，事务1，2都会提交。。

5, 假设卡在C阶段，那么事务3，肯定是可以看见事务3写入的记录（XXXXX）。



经过测试：

1，是卡在B阶段，也就是说事务3，既看不见事务1的提交内容，也看不见事务2的提交内容，且重启mysql后，事务1，2都已经提交。。

2，MySQL crash重启后，事务1，事务2的dml都已经提交成功，说明不是卡在A阶段。

性能
semi-sync vs lossless semi-sync 的性能对比

根据以上的测试，可以得知，lossless只卡在B阶段，普通的semi-sync是卡在C阶段。
lossless的性能远远好于普通的semi-sync，即（after_sync 优于 after_commit）
因为lossless 卡在B阶段的时候可以堆积事务，可以在C阶段进行group commit。
普通的semi-sync，卡在C阶段,事务都已经commit了，并没有堆积的过程。

CAP理论:

一致性【C】
可用性【A】
分区容忍性【P】
理论：CAP 三者不可兼得，必须要牺牲一个

分区，是一定存在的，不是你想不要就不要的。所以，这里只剩下两种组合

CP 牺牲可用性

这种做法，就是保留强一致性，牺牲可用性
案例：可以将rpl_semi_sync_master_timeout设置成一个无限大的值，比如：100天，那么master和slave就强一致了，但是可用性就大打折扣

AP 牺牲一致性

这种做法，就是保留高可用性，牺牲一致性
案例：比如原生的异步复制就是这样咯。可以快速做到切换，但是一致性就没有保障

阅读分享：https://henduan.com/FJHU5

默认一般安装完成MySQL数据库root用户的密码为空，一般需要设置好root的密码，要不会造成不安全的情况发生。然而登录MySQL数据库后发现5.7版本跟5.6版本User表结构发生了变化，原本的password字段没有了，这就导致在5.7下面修改用户密码的方式跟之前的版本不同，下面会介绍2种修改方式。

1. 使用set password语句

这种方法跟以前的版本修改密码是一致的，需要登录到MySQL后使用：

set password for root@localhost = password("123.com");

2. 直接更新user表

由于MySQL版本的升级，User表的结构改变了，好多网上使用的UPDATE语句不适用新版本的表结构，在这里我通过DESC语句来查看User表的结构，结果如图：

mysql> desc User;

+------------------------+-----------------------------------+------+-----+-----------------------+-------+

| Field                  | Type                              | Null | Key | Default               | Extra |

+------------------------+-----------------------------------+------+-----+-----------------------+-------+

| Host                   | char(60)                          | NO   | PRI |                       |       |

| User                   | char(32)                          | NO   | PRI |                       |       |

| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |

| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |

| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |

| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |

| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |

| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |

| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |

| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |

| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |

| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |

| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |

| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |

| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |

| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |

| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |

| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |

| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |

| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |

| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |

| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |

| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |

| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |

| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |

| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |

| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |

| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |

| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |

| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |

| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |

| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |

| ssl_cipher             | blob                              | NO   |     | NULL                  |       |

| x509_issuer            | blob                              | NO   |     | NULL                  |       |

| x509_subject           | blob                              | NO   |     | NULL                  |       |

| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |

| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |

| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |

| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |

| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |

| authentication_string  | text                              | YES  |     | NULL                  |       |

| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |

| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |

| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |

| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |

+------------------------+-----------------------------------+------+-----+-----------------------+-------+

45 rows in set (0.00 sec)

如上发现了一些疑似用来认证的字段，根据字段类型推断authentication_string应该是存储的用户密码，之后就开始尝试修改这一字段：

update user set authentication_string = password('123.com') where user='root' and host='localhost';

更改后退出发现依然不会生效，通过查阅资料发现，还需要把plugin字段的值改为mysql_native_password。个人感觉这个字段影响的是验证方式，更改之后就可以在登录的时候使用刚刚设置的密码来验证。修改语句如下：

update user set plugin = 'mysql_native_password' where user='root' and host='localhost';

后来了解到mysql_native_password和caching_sha2_password是MySQL的两种加密认证方式，一般MySQL 5默认使用前者，而8以后的版本使用后者，在这里虽然笔者使用的是5.7.31，但我确实是在更改了这个字段值以后才能正常用密码登录的。

现在MySQL 8和PostgreSQL 10已经发布，现在是重新审视两个主要的开源关系数据库如何相互竞争的好时机。

在这些版本之前，一般的看法是，虽然Postgres在功能集及血统方面更胜一筹，但MySQL在大规模并发读/写操作方面进行了更多的大规模测试。

但是随着最新版本的发布，两者之间的差距已大大缩小。

功能对比

下面看一下通常我们认为比较时髦的功能。

特征	MySQL8	PostgreSQL10
查询和分析
通用表达式(CTEs)	✔ New	✔
视窗功能	✔ New	✔
数据类型
支持JSON	✔ Improved	✔
GIS / SRS	✔ Improved	✔
全文搜索	✔	✔
扩展性
逻辑复制	✔	✔ New
半同步复制	✔	✔ New
声明式分区	✔	✔ New

过去常常说MySQL最适合在线交易，而PostgreSQL最适合分析过程，但是现在不再是如此局面。

通用表达式（CTEs）和窗口函数一直是多数人选择PostgreSQL的主要原因。但是现在，在同一个表中employees引用对表进行递归遍历boss_id，或者在排序结果中找到中间值（或50％百分位数），在MySQL上不再是问题。

PostgreSQL上的复制缺乏配置灵活性，这是Uber转向MySQL的原因。但是现在有了逻辑复制，可以通过使用更新版本的Postgres创建副本并切换到该副本来实现零停机时间升级。截断大型时序事件表中的陈旧分区也容易得多。

在功能方面，两个数据库现在彼此相同。

二者有什么区别？

现在有一个问题就是，我们到底是选择MySQL还是PostgreSQL，那选择的原因又是什么？

生态系统就是这些因素之一。MySQL拥有一个强大的生态系统，其中包含MariaDB，Percona，Galera等变体，以及InnoDB以外的其他存储引擎，但它们也可能令人不知所措。Postgres的高端选项有限，但是随着最新版本引入的新功能，这种情况将会改变。

治理是另一个因素。每个人都在担心甲骨文（或最初为SUN）收购MySQL时，他们会毁了该产品，但过去十年来情况并非如此。实际上，收购后发展加速。Postgres在工作治理和协作社区方面拥有悠久的历史。

体系结构的基本原理不会经常更改，但是值得值得我们回顾。

特征	MySQL8	PostgreSQL10
架构	单进程	多进程
并发	多线程	fork(2)
表结构	聚合索引	Heap(堆)
页面压缩	透明	TOAST
更新	就地/回滚Segments	仅追加/ Hot
垃圾回收	清除线程	自动回收进程
Transaction Log	REDO Log (WAL)	WAL
Replication Log	Separate (Binlog)	WAL

进程对比线程

当Postgres fork一个子进程来建立连接时，每个连接最多需要10 MB的空间。与MySQL的“每次连接线程”模型相比，内存压力更大，后者在64位平台上，线程的默认堆栈大小为 256KB。（当然，线程局部排序缓冲区等可以使此开销的重要性降低，即使可以忽略不计，但仍然如此。）

即使写时复制会与父进程一起保存一些共享的，不变的内存状态，但是当您有1000个以上的并发连接时，作为基于进程的体系结构的基本开销会增加负担，并且它可能是最重要的开销之一能力计划的因素。

也就是说，如果您在30台服务器上运行Rails应用程序，其中每台服务器具有16个CPU内核和32个Unicorn worker，则您有960个连接。在所有应用程序中，可能只有不到0.1％会达到这个规模，但这是需要牢记的。

聚合索引对比堆(Heap)表

聚合索引是其中行被直接嵌入主键的B树结构内的表结构。（非聚和）堆(Heap)是规则表结构，其中填充了与索引分开的数据行。

使用聚合索引时，当您通过主键查找记录时，单个I / O将检索整行，而非聚集索引始终通过遵循引用至少需要两个I / O。由于外键引用和联接将触发主键查找，因此影响可能很大，这将占查询的绝大多数。

聚合索引的理论缺点是，在使用次级索引进行查询时，遍历树节点的次数是您首先遍历次级索引，然后遍历聚合索引（也是一棵树）的两倍。

但是，考虑到现代惯例，将自动递增的整数作为主键[1]（称为代理键），几乎总是希望拥有聚合索引。如果您要执行很多操作ORDER BY id来检索最新（或最旧）的N条记录，那就更是如此，我认为这适用于大多数记录。

Postgres不支持聚和索引，而MySQL（InnoDB）不支持堆(Heap)。但是，无论哪种方式，如果您有大量内存，则差异应该很小。

页面结构和压缩

Postgres和MySQL都具有基于页面的物理存储（8KB和16KB）。

PostgreSQL物理存储简介

页面结构看起来像上图所示。它包含一些标题，我们将在这里不进行介绍，但是它们包含有关页面的元数据。标头后面的项目是一个数组标识符，由(offset, length)指向元组或数据行的对组成。请记住，在Postgres中，可以通过这种方式将同一记录的多个版本存储在同一页面中。

MySQL的表空间结构与Oracle的表空间结构相似，它具有段，范围，页和行的多个层次结构层。

它还为UNDO提供了一个单独的部分，称为“回退部分”。与Postgres不同，MySQL将在同一区域保留同一记录的多个版本。

在两个数据库上，一行必须适合一个页面，这意味着一行必须小于8KB。（MySQL的页面中必须至少包含2行，巧合的是16KB / 2 = 8KB）

那么当列中有一个大的JSON对象时会发生什么？

Postgres使用TOAST（专用的影子表存储）。当且仅当选择行和列时，才会拉出大对象。换句话说，大量的黑盒子不会污染您宝贵的缓存。它还支持对TOASTed对象的压缩。

由于高端SSD存储供应商Fusion-io的贡献，MySQL具有称为“ 透明页面压缩”的更高级功能。它是专门为与SSD配合使用而设计的，固态硬盘的写入量与设备的寿命直接相关。

MySQL上的压缩不仅适用于页面外的大对象，而且适用于所有页面。它是通过在稀疏文件中使用打孔来实现的，稀疏文件受ext4或btrfs等现代文件系统支持。

有关更多详细信息，请参阅：通过FusionIO上的新MariaDB页面压缩显着提高性能

更新的开销

UPDATE是经常遗漏但对性能有重大影响并且可能是最具争议的主题的另一个功能。

这也是Uber放弃Postgres的另一个原因，这激起了许多Postgres拥护者的反驳。

• MySQL可能适合Uber，但不适合您
• PostgreSQL对Uber的回应（PDF）

两者都是MVCC数据库，可保留多个版本的数据以进行隔离)。

为此，Postgres将旧数据保留在堆中直到VACUUMed，而MySQL将旧数据移动到称为回滚段的单独区域。

在Postgres上，当您尝试更新时，必须复制整行以及指向该行的索引条目。部分原因是Postgres不支持聚集索引，因此从索引引用的行的物理位置不会被逻辑键抽象出来。

为了解决此问题，Postgres使用仅堆元组（HOT）尽可能不更新索引。但是，如果更新足够频繁（或者如果一个元组很大），则元组的历史记录很容易从8KB的页面大小中流出，跨越多个页面并限制了功能的有效性。修剪和/或碎片整理的时间取决于试探法。此外，将fillfactor设置为小于100会降低空间效率—这是在表创建时就不必担心的艰难折衷。

这个限制甚至更深了。由于索引元组没有有关事务的任何信息，因此直到9.2 以前一直不可能支持仅索引扫描。它是所有主要数据库（包括MySQL，Oracle，IBM DB2和Microsoft SQL Server）支持的最古老，最重要的优化方法之一。但是即使使用最新版本，当有大量的UPDATE设置Visibility Map中的脏位时，Postgres也不能完全支持仅索引扫描，而在不需要时经常选择Seq扫描。

在MySQL上，更新发生在原地，旧行数据存放在称为回滚段的单独区域中。结果是您不需要VACUUM，提交非常快，而回滚相对较慢，这对于大多数用例来说是一个较好的折衷方案。

它也足够聪明，可以尽快清除历史记录。如果将事务的隔离级别设置为READ-COMMITTED或更低，则在语句完成时将清除历史记录。

交易历史记录的大小不会影响主页。碎片是没有问题的。因此，MySQL的整体性能更好，更可预测。

垃圾回收

Postgres上的VACUUM非常昂贵，因为它可以在主堆区域中工作，从而造成直接的资源争用。感觉就像编程语言中的垃圾回收一样-它会妨碍您并让您随意暂停。

为具有数十亿条记录的表配置自动清空仍然是一个挑战。

对MySQL的清除也可能很繁重，但是由于它在单独的回滚段中使用专用线程运行，因此不会以任何方式对读取并发产生不利影响。即使使用默认设置，膨胀的回滚段也不太可能使您减速。

一个拥有数十亿条记录的繁忙表不会导致MySQL的历史记录膨胀，并且诸如存储文件大小和查询性能之类的事情几乎是可以预测和稳定的。

日志和复制

Postgres有一个唯一的交易历史事实来源，称为Write Ahead Log（WAL）。它也用于复制，称为逻辑复制的新功能可以将二进制内容实时解码为更易消化的逻辑语句，从而可以对数据进行精细控制。

MySQL维护两个单独的日志：1. 用于崩溃恢复的InnoDB特定重做日志，以及2. 用于复制和增量备份的二进制日志。

与Oracle一样，InnoDB上的重做日志是免维护的循环缓冲区，不会随着时间的推移而增长，只能在启动时以固定大小创建。这种设计可确保在物理设备上保留连续的连续区域，从而提高性能。重做日志越大，性能越好，但要从崩溃中恢复时间。

在Postgres中添加了新的复制功能后，我称之为平局。

TL和DR

令人惊讶的是，事实证明，普遍的看法仍然成立。MySQL最适合在线交易，而PostgreSQL最适合仅追加分析过程，例如数据仓库。[2]

正如我们在本文中看到的，Postgres的绝大多数复杂性源于其仅附加的，过度冗余的堆体系结构。

Postgres的未来版本可能需要对其存储引擎进行重大改进。您不必完全相信我的话- 官方Wiki上已经讨论了它，这表明是时候从InnoDB那里获取一些好主意了。

一次又一次地说MySQL正在追赶Postgres，但是这次，潮流已经改变了。

1. 顺便说一句，UUID作为主键是一个可怕的想法-密码随机性是完全设计用来杀死参考位置的，因此会降低性能。↩︎
2. 当我说Postgres非常适合分析时，我是说真的。如果您不了解TimescaleDB，它是PostgreSQL之上的包装器，可让您每秒插入100万条记录，每服务器100+十亿行。疯狂的事情。难怪亚马逊为什么选择PostgreSQL作为Redshift的基础。

英文原文: http://suo.im/6kMihv

一、前言​
关于mysql主从同步，相信大家都不陌生，随着系统应用访问量逐渐增大，单台数据库读写访问压力也随之增大，当读写访问达到一定瓶颈时，将数据库的读写效率骤然下降，甚至不可用；为了解决此类问题，通常会采用mysql集群，当主库宕机后，集群会自动将一个从库升级为主库，继续对外提供服务；那么主库和从库之间的数据是如何同步的呢？本文针对MySQL 5.7版本进行下面的分析，下面随笔者一起探究一下mysql主从是如何同步的。
 
二、MySQL主从复制原理
为了减轻主库的压力，应该在系统应用层面做读写分离，写操作走主库，读操作走从库，下图为MySQL官网给出的主从复制的原理图，从图中可以简单的了解读写分离及主从同步的过程，分散了数据库的访问压力，提升整个系统的性能和可用性，降低了大访问量引发数据库宕机的故障率。

 
三、binlog简介
MySQL主从同步是基于binlog文件主从复制实现，为了更好的理解主从同步过程，这里简单介绍一下binlog日志文件。

binlog日志用于记录所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改，它是以二进制的形式保存在磁盘中。我们可以通过mysql提供的查看工具mysqlbinlog查看文件中的内容，例如 mysqlbinlog mysql-bin.00001 | more，这里注意一下binlog文件的后缀名00001，binlog文件大小和个数会不断的增加，当MySQL停止或重启时，会产生一个新的binlog文件，后缀名会按序号递增，例如mysql-bin.00002、mysql-bin.00003，并且当binlog文件大小超过 max_binlog_size系统变量配置时也会产生新的binlog文件。
 
（一）binlog日志格式
（1） statement ： 记录每一条更改数据的sql;

• 优点：binlog文件较小，节约I/O，性能较高。
• 缺点：不是所有的数据更改都会写入binlog文件中，尤其是使用MySQL中的一些特殊函数（如LOAD_FILE()、UUID()等）和一些不确定的语句操作，从而导致主从数据无法复制的问题。

 （2） row ： 不记录sql，只记录每行数据的更改细节

• 优点：详细的记录了每一行数据的更改细节，这也意味着不会由于使用一些特殊函数或其他情况导致不能复制的问题。
• 缺点：由于row格式记录了每一行数据的更改细节，会产生大量的binlog日志内容，性能不佳，并且会增大主从同步延迟出现的几率。

 （3） mixed：一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。 （二）binlog日志内容  （三）binlog事件类型MySQL binlog记录的所有操作实际上都有对应的事件类型的，譬如STATEMENT格式中的DML操作对应的是QUERY_EVENT类型，ROW格式下的DML操作对应的是ROWS_EVENT类型，如果想了解更多请参考官方文档，有关binlog日志内容不在这里过多赘述，简单介绍一下是为了更好的理解主从复制的细节，下面我们进入正题。  四、MySQL主从复制原理mysql主从复制需要三个线程，master（binlog dump thread）、slave（I/O thread 、SQL thread）。  Master（1）binlog dump线程：当主库中有数据更新时，那么主库就会根据按照设置的binlog格式，将此次更新的事件类型写入到主库的binlog文件中，此时主库会创建log dump线程通知slave有数据更新，当I/O线程请求日志内容时，会将此时的binlog名称和当前更新的位置同时传给slave的I/O线程。 Slave（2）I/O线程：该线程会连接到master，向log dump线程请求一份指定binlog文件位置的副本，并将请求回来的binlog存到本地的relay log中，relay log和binlog日志一样也是记录了数据更新的事件，它也是按照递增后缀名的方式，产生多个relay log（ host_name-relay-bin.000001）文件，slave会使用一个index文件（ host_name-relay-bin.index）来追踪当前正在使用的relay log文件。 （3）SQL线程：该线程检测到relay log有更新后，会读取并在本地做redo操作，将发生在主库的事件在本地重新执行一遍，来保证主从数据同步。此外，如果一个relay log文件中的全部事件都执行完毕，那么SQL线程会自动将该relay log 文件删除掉。 下面是整个复制过程的原理图：  四、主从同步延迟mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生binlog，binlog是顺序写，所以效率很高，slave的I/O线程到主库取日志，效率也比较高，但是，slave的SQL线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的，不是顺序的，成本高很多，还可能存在slave上的其他查询产生lock争用的情况，由于SQL也是单线程的，所以一个DDL卡住了，需要执行很长一段事件，后续的DDL线程会等待这个DDL执行完毕之后才执行，这就导致了延时。当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，延时就产生了，除此之外，还有可能与slave的大型query语句产生了锁等待导致。 由于主从同步延迟是客观存在的，我们只能从我们自己的架构上进行设计， 尽量让主库的DDL快速执行。下面列出几种常见的解决方案：[list=1]

业务的持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。

服务的基础架构在业务和mysql之间加入memcache或者Redis的cache层。降低mysql的读压力；

使用比主库更好的硬件设备作为slave；

sync_binlog在slave端设置为0；

–logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。

禁用slave的binlog

 
五、参考资料
https://dev.mysql.com/doc/refman/5.7/en/replication.html  
http://www.linuxidc.com/Linux/2014-05/101450.htm  
http://blog.csdn.net/xiongping_/article/details/49907095  
http://www.cnblogs.com/martinzhang/p/3454358.html  
“本文转载自 linkedkeeper.com (文／张松然)”地址：http://www.linkedkeeper.com/detail/blog.action?bid=1028  

最近研发的项目对 DB 依赖比较重，梳理了这段时间使用MySQL遇到的8个比较具有代表性的问题，答案也比较偏自己的开发实践，没有 DBA专业和深入，有出入的请使劲拍砖！
 

一、MySQL读写性能是多少，有哪些性能相关的重要参数？​

这里做了几个简单压测实验，结果如下：

机器：8核CPU，8G内存

表结构(尽量模拟业务)：12个字段(1个bigint(20)为自增primary key，5个int(11)，5个varchar(512)，1个timestamp)，InnoDB存储引擎。

实验1(写)：insert => 6000/s

前提：连接数100，每次insert单条记录

分析：CPU跑了50%，这时磁盘为顺序写，故性能较高

实验2(写)：update(where条件命中索引) => 200/s

前提：连接数100，10w条记录，每次update单条记录的4个字段(2个int(11)，2个varchar(512))

分析：CPU跑2%，瓶颈明显在IO的随机写

实验3(读)：select(where条件命中索引) => 5000/s

前提：连接数100，10w条记录，每次select单条记录的4个字段(2个int(11)，2个varchar(512))

分析：CPU跑6%，瓶颈在IO，和db的cache大小相关

实验4(读)：select(where条件没命中索引) => 60/s

前提：连接数100，10w条记录，每次select单条记录的4个字段(2个int(11)，2个varchar(512))

分析：CPU跑到80%，每次select都需遍历所有记录，看来索引的效果非常明显！

几个重要的配置参数，可根据实际的机器和业务特点调整

max_connecttions：最大连接数



table_cache：缓存打开表的数量



key_buffer_size：索引缓存大小



query_cache_size：查询缓存大小



sort_buffer_size：排序缓存大小(会将排序完的数据缓存起来)



read_buffer_size：顺序读缓存大小



read_rnd_buffer_size：某种特定顺序读缓存大小(如order by子句的查询)

PS：查看配置方法：show variables like '%max_connecttions%';
 

二、MySQL负载高时，如何找到是由哪些SQL引起的？

方法：慢查询日志分析(MySQLdumpslow)
 
慢查询日志例子，可看到每个慢查询SQL的耗时：

# User@Host: edu_online[edu_online] @  [10.139.10.167]

# Query_time: 1.958000  Lock_time: 0.000021 Rows_sent: 254786  Rows_examined: 254786

SET timestamp=1410883292;

select * from t_online_group_records;

日志显示该查询用了1.958秒，返回254786行记录，一共遍历了254786行记录。及具体的时间戳和SQL语句。

使用MySQLdumpslow进行慢查询日志分析：

MySQLdumpslow -s t -t 5 slow_log_20140819.txt

输出查询耗时最多的Top5条SQL语句
 
-s：排序方法，t表示按时间 (此外，c为按次数，r为按返回记录数等)
-t：去Top多少条，-t 5表示取前5条
 
执行完分析结果如下：

Count: 1076100  Time=0.09s (99065s)  Lock=0.00s (76s)  Rows=408.9 (440058825), edu_online[edu_online]@28hosts

  select * from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > N

Count: 1076099  Time=0.05s (52340s)  Lock=0.00s (91s)  Rows=62.6 (67324907), edu_online[edu_online]@28hosts

  select * from t_online_course where UNIX_TIMESTAMP(c_updatetime) > N

Count: 63889  Time=0.78s (49607s)  Lock=0.00s (3s)  Rows=0.0 (18), edu_online[edu_online]@[10x.213.1xx.1xx]

  select f_uin from t_online_student_contact where f_modify_time > N

Count: 1076097  Time=0.02s (16903s)  Lock=0.00s (72s)  Rows=52.2 (56187090), edu_online[edu_online]@28hosts

  select * from t_online_video_info where UNIX_TIMESTAMP(v_update_time) > N

Count: 330046  Time=0.02s (6822s)  Lock=0.00s (45s)  Rows=0.0 (2302), edu_online[edu_online]@4hosts

  select uin,cid,is_canceled,unix_timestamp(end_time) as endtime,unix_timestamp(update_time) as updatetime 

  from t_kick_log where unix_timestamp(update_time) > N

以第1条为例，表示这类SQL(N可以取很多值，这里MySQLdumpslow会归并起来)在8月19号的慢查询日志内出现了1076100次，总耗时99065秒，总返回440058825行记录，有28个客户端IP用到。

通过慢查询日志分析，就可以找到最耗时的SQL，然后进行具体的SQL分析

慢查询相关的配置参数：

log_slow_queries：是否打开慢查询日志，得先确保=ON后面才有得分析



long_query_time：查询时间大于多少秒的SQL被当做是慢查询，一般设为1S



log_queries_not_using_indexes：是否将没有使用索引的记录写入慢查询日志



slow_query_log_file：慢查询日志存放路径

三、如何针对具体的SQL做优化？

使用Explain分析SQL语句执行计划

MySQL> explain select * from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > 123456789;

+----+-------------+------------------------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table                  | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+------------------------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | t_online_group_records | ALL  | NULL          | NULL | NULL    | NULL |   47 | Using where |

+----+-------------+------------------------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

如上面例子所示，重点关注下type，rows和Extra：

type：使用类别，有无使用到索引。结果值从好到坏：... > range(使用到索引) > index > ALL(全表扫描)，一般查询应达到range级别

rows：SQL执行检查的记录数

Extra：SQL执行的附加信息，如"Using index"表示查询只用到索引列，不需要去读表等

使用Profiles分析SQL语句执行时间和消耗资源：

MySQL> set profiling=1; (启动profiles，默认是没开启的)

MySQL> select count(1) from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > 123456789; (执行要分析的SQL语句)

MySQL> show profiles;

+----------+------------+----------------------------------------------------------------------------------------------+

| Query_ID | Duration   | Query                                                                                        |

+----------+------------+----------------------------------------------------------------------------------------------+

|        1 | 0.00043250 | select count(1) from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > 123456789 |

+----------+------------+----------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

MySQL> show profile cpu,block io for query 1; (可看出SQL在各个环节的耗时和资源消耗)

+----------------------+----------+----------+------------+--------------+---------------+

| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |

+----------------------+----------+----------+------------+--------------+---------------+

...

| optimizing           | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |

| statistics           | 0.000020 | 0.000000 |   0.000000 |            0 |             0 |

| preparing            | 0.000017 | 0.000000 |   0.000000 |            0 |             0 |

| executing            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |

| Sending data         | 0.000076 | 0.000000 |   0.000000 |            0 |             0 |

...

SQL优化的技巧 (只提一些业务常遇到的问题)
 
1、最关键：索引，避免全表扫描
对接触的项目进行慢查询分析，发现TOP10的基本都是忘了加索引或者索引使用不当，如索引字段上加函数导致索引失效等(如where UNIX_TIMESTAMP(gre_updatetime)>123456789)

+----------+------------+---------------------------------------+

| Query_ID | Duration   | Query                                 |

+----------+------------+---------------------------------------+

|        1 | 0.00024700 | select * from mytable where id=100    |

|        2 | 0.27912900 | select * from mytable where id+1=101  |

+----------+------------+---------------------------------------+

另外很多同学在拉取全表数据时，喜欢用select xx from xx limit 5000,1000这种形式批量拉取，其实这个SQL每次都是全表扫描，建议添加1个自增id做索引，将SQL改为 select xx from xx where id>5000 and id<6000;

+----------+------------+-----------------------------------------------------+

| Query_ID | Duration   | Query                                               |

+----------+------------+-----------------------------------------------------+

|        1 | 0.00415400 | select * from mytable where id>=90000 and id<=91000 |

|        2 | 0.10078100 | select * from mytable limit 90000,1000              |

+----------+------------+-----------------------------------------------------+

合理用好索引，应该可解决大部分SQL问题。当然索引也非越多越好，过多的索引会影响写操作性能。
 
1.1 只select出需要的字段，避免select

+----------+------------+-----------------------------------------------------+

| Query_ID | Duration   | Query                                               |

+----------+------------+-----------------------------------------------------+

|        1 | 0.02948800 | select count(1) from ( select id from mytable ) a   |

|        2 | 1.34369100 | select count(1) from ( select * from mytable ) a    |

+----------+------------+-----------------------------------------------------+

1.2 尽量早做过滤，使Join或者Union等后续操作的数据量尽量小
 
1.3 把能在逻辑层算的提到逻辑层来处理，如一些数据排序、时间函数计算等
 
PS：关于SQL优化，已经有足够多文章了，所以就不讲太全面了，只重点说自己1个感受：索引！基本都是因为索引！
 

四、SQL层面已难以优化，请求量继续增大时的应对策略？

下面是我能想到的几个方法，每个方法又都是一篇大文章了，这里就不展开。

• 分库分表
• 使用集群(master-slave)，读写分离
• 增加业务的cache层
• 使用连接池

五、MySQL如何做主从数据同步？

复制机制（Replication）master通过复制机制，将master的写操作通过binlog传到slave生成中继日志(relaylog)，slave再将中继日志redo，使得主库和从库的数据保持同步。 复制相关的3个MySQL线程：[list=1]

slave上的I/O线程：向master请求数据

master上的Binlog Dump线程：读取binlog事件并把数据发送给slave的I/O线程

slave上的SQL线程：读取中继日志并执行，更新数据库

属于slave主动请求拉取的模式 实际使用可能遇到的问题数据非强一致：CDB默认为异步复制，master和slave的数据会有一定延迟(称为主从同步距离，一般 < 1s)主从同步距离变大：可能是DB写入压力大，也可能是slave机器负载高，网络波动等原因，具体问题具体分析 相关监控命令

show processlist：查看MySQL进程信息，包括3个同步线程的当前状态show master status ：查看master配置及当前复制信息show slave status：查看slave配置及当前复制信息

六、如何防止DB误操作和做好容灾？

业务侧应做到的几点：

• 重要DB数据的手工修改操作，操作前需做到2点：1 先在测试环境操作 2 备份数据
• 根据业务重要性做定时备份，考虑系统可承受的恢复时间
• 进行容灾演练，感觉很必要

 MySQL备份和恢复操作 1、备份：使用MySQLdump导出数据

MySQLdump -u 用户名 -p 数据库名 [表名] > 导出的文件名MySQLdump -uxxx -p xxx mytable > mytable.20140921.bak.sql

2、恢复：导入备份数据

MySQL -uxxx -p xxxx < mytable.20140921.bak.sql

3、恢复：导入备份数据之后发送的写操作。先使用MySQLbinlog导出这部分写操作SQL(基于时间点或位置)如导出2014-09-21 09:59:59之后的binlog：

MySQLbinlog --database="test" --start-date="2014-09-21 09:59:59" /var/lib/MySQL/mybinlog.000001 > binlog.data.sql

如导出起始id为123456之后的binlog：

MySQLbinlog --database="test" --start-position="123456" /var/lib/MySQL/mybinlog.000001 > binlog.data.sql

最后把要恢复的binlog导入db

MySQL -uxxxx -p xxxx < binlog.data.sql

七、该选择MySQL哪种存储引擎，Innodb具有什么特性？

存储引擎简介：插件式存储引擎是MySQL的重要特性，MySQL支持多种存储引擎以满足用户的多种应用场景存储引擎解决的问题：如何组织MySQL数据在介质中高效地读取，需考虑存储机制、索引设计、并发读写的锁机制等MySQL5.0支持的存储引擎有MyISAM、InnoDB、Memory、Merge等 MyISAM和InnoDB的区别(只说重点了) 1. InnoDB，MySQL5.5之后及CDB的默认引擎

• 支持行锁：并发性能好
• 支持事务：故InnoDB称为事务性存储引擎，支持ACID，提供了具有提交、回滚和崩溃恢复能力的事务安全
• 支持外键：当前唯一支持外键的引擎

 2. MyISAM，MySQL5.5之前默认引擎

• 支持表锁：插入+查询速度快，更新+删除速度慢
• 不支持事务

 使用show engines可查看当前MySQL支持的存储引擎详情：  

八、MySQL内部结构有哪些层次？

 非专业DBA，这里只简单贴个结构图说明下。MySQL是开源系统，其设计思路和源代码都出自大牛之手，有空可以学习下。 [list=1]

Connectors：连接器。接收不同语言的Client交互

Management Serveices & Utilities：系统管理和控制工具

Connection Pool: 连接池。管理用户连接

SQL Interface: SQL接口。接受用户的SQL命令，并且返回用户需要查询的结果

Parser: 解析器。验证和解析SQL语句成内部数据结构

Optimizer: 查询优化器。为查询语句选择合适的执行路径

Cache和Buffer：查询缓存。缓存查询的结果，有命中即可直接返回

Engine：存储引擎。MySQL数据最后组织并存储成具体文件

 
作者：王昂 
原文地址：https://www.qcloud.com/community/article/233 

一、组提交介绍

1.1 什么是组提交
Binary Log Group Commit 即二进制日志组提交。这是 MySQL5.6 版本中引进的一个新的特性。为什么需要引进这个特性呢？我们知道当我们把 MySQL 的 binlog 开启的时候，MySQL 会将每个事务的操作都记录到 binlog 中，方便我们使用 binlog 来完成复制或者恢复操作。可是需要调用 fsync() 才能将缓存中被更改的 binlog 真正的写到磁盘上，保证数据的持久化。但是这是一个从内存写到磁盘的过程，I/O 比较慢。如果每次事务提交都执行一遍 fsync() 将 binlog 持久化落盘到磁盘的话，效率很低。于是就想，能不能等几个事务的 binlog 一起调用一次 fsync()，一次性落盘。减少 fsync() 的次数，从而提高效率。这就是二进制日志组提交的概念。
 

二、两阶段提交

2.1 为什么需要二阶段提交
我们知道在 MySQL 中不仅仅有 binlog，还有 redo log 和 undo log 。binlog 用来记录每个事务的操作信息，redo 是在数据库宕机恢复时使用，用来恢复数据库数据，undo 用来回滚还未被提交的数据。binlog 是在数据库 Server 层产生的，即它会记录所有存储引擎中事务的操作，而 redo 是 InnoDB 存储引擎特有的日志。

在事务提交的时候，我们需要先写入二进制日志，再写 InnoDB 存储引擎的 redo。并且要求二进制日志和 redo 要么都写，要么都不写。不然可能会出现这样的情况：在主从复制的环境下，master 提交了一个事务，先写了二进制日志，但是在要写 InnoDB 存储引擎的时候，数据库发生了宕机，此时 binlog 又已经被 slave 接收到了，slave 会执行这个事务，但是实际 master 上并没有这个事务。这就会导致主从数据的不一致。所以我们引入了二阶段提交来解决这个问题，即将写 binlog 操作个 InnoDB 提交操作通过事务变成原子的。
 
2.2 什么是二阶段提交
所谓的二阶段提交就是，我在事务提交的时候，确保先将 binlog 写入，然后再到数据引擎层提交，并且这两个操作是原子的。在 MySQL 中用内部的 XA 事务来完成，即将这两个操作包装成一个事务的概念。

上图表示了二阶段提交的过程。当一个会话中的某一事务 COMMIT 的时候，进去二阶段提交的过程。首先数据库先去协调 Server 层和 Engine，询问是否都可以开始写日志，这个过程就是图中的的 prepare 阶段。协调好两层之间的关系，Server 层和 Engine 层都表示可以写日志，这时候进入下一个过程。

第二个过程就是写 binlog 的过程，先把 binlog 写到内存中，然后调用 fsync() 将 binlog 从内存写到磁盘上。

第三个过程就是在存储引擎层提交的过程，将真实修改的数据提交到数据库中。当这一步完成才最终返回给会话一个 COMMIT 成功的信号。

这整个过程就是二阶段提交的过程，如果在 fsync() 之前数据库 crash 了，重启之后数据将会被回滚，若在 fsync() 之后 crash，则会进行重做操作。通过二阶段提交的方式就保证了存储引擎与二进制日志保持一致。
 

三、三阶段提交

3.1 为什么需要三阶段提交
上面的二阶段提交是针对单一事务提交时候的操作顺序，下面我们来看看当多个事务并发的时候会是什么样的一个情况。

现在有T1、T2、T3 三个事务需要执行，从图中可以看到数据在 fsync() 之前，三个事务已经写入到了 binlog 中，通过 fsync() 操作将 binlog 刷到磁盘。之后先是 T2 COMMIT，将数据更改更新到存储引擎层，接着是 T3 COMMIT，将数据更新到存储引擎层。这时候我们做了一个热备份的操作，有多种方式进行数据库的热备份，比如：XtraBackup等。这时候就会发生错误。会发生什么错误，我们需要先了解一下 XtraBackup 等热备工具的备份原理。

XtraBackup备份原理：直接拷贝数据库文件，并且记录下当前二进制日志中已经提交的最后一个事务标记。在新的数据库实例上完成 recovery 操作。

了解完备份原理之后，我们就可以想到上述情况下做热备会出现什么情况。因为 T2、T3 已经提交，所以备份的时候会记录下 T3 是最后一个提交的事务，会认为 T3 之前的事务都是已经提交的，由于是直接拷贝数据库文件，可以看到 T1 事务的数据还没有提交到存储引擎层，所以备份数据中还并没有 T1 的数据。如果新的数据库是用来做主从复制的话，change master to 会指向二进制日志中 T3 的位置，从 T3 事务开始往后进行复制，这样一来 T1 事务的数据就这样没了。产生这个问题的主要原因就是：事务写入二进制日志的顺序与事务在存储引擎层提交的顺序不一致。

为了解决这个问题，MySQL 引入了 prepare_commit_mutext 的机制，当事务提交的时候，需要先获得 prepare_commit_mutext 这个锁。有了这个锁就可以保证事务写入二进制日志的顺序与事务在存储引擎层提交的顺序一致。

但是这样一来，从图中我们也可以看到，原先是并发的事务，又变成了串行的，效率又变低了。只要是问题，必然存在解决方法。于是三阶段提交就出现了。
 
 
3.2 什么是三阶段提交
三阶段提交，顾名思义有三个阶段： Flush 阶段、sync 阶段、commit 阶段。分别对应的就是二进制日志写内存的阶段、二进制日志刷盘的阶段、事务提交到存储引擎层的阶段。
 

每个阶段都有 leader、follower 两种角色。当一个事务进入三个阶段中的某一个阶段，如果发现这个阶段中的队列为空，那么这个事务就会成为 leader 的角色，之后进入同一阶段的事务，发现这个阶段的队列中已经有事务存在了，那就变成 follower 角色。leader 角色的任务是安排当前阶段队列中的事务按顺序执行，并且带领队列中所有的事务进入下一个阶段。当 leader 带领队列中的事务进入下一阶段的时候，如果发现下一阶段中已经有事务存在（即下一阶段已有 leader 存在），新来的 leader 自动变成 follower 角色。

三阶段提交在每个阶段都控制了事务的顺序，从而也就控制了事务执行的整体顺序。解决了 prepare_commit_mutex 锁导致的问题，事务可以并发的执行。

参考：http://blog.itpub.net/28218939/viewspace-1975809/
           http://blog.itpub.net/28218939/viewspace-1975822/
http://mysqlmusings.blogspot.jp/2012/06/binary-log-group-commit-in-mysql-56.html  

原来编译php的时候，没有把pdo_mysql相关的参数带上，安装完后才发现。再重新编译有点费时间，所以决定单独来安装。
 
先找需要的版本，我用的是稳定的版本。要先看看说明，特别是要注意mysql的php的版本

# wget http://pecl.php.net/get/PDO_MYSQL-1.0.2.tgz   

# tar xzvf PDO_MYSQL-1.0.2.tgz   

# cd PDO_MYSQL-1.0.2   

# /usr/local/php/bin/phpize   

Configuring for:   

PHP Api Version: 20041225   

Zend Module Api No: 20060613   

Zend Extension Api No: 220060519   

# ./configure

执行完以后，报如下错误：

checking for mysql_config... not found  

configure: error: Cannot find MySQL header files under

这个错误表明系统缺省没有找到你的mysql安装目录，因此可以使用这个命令解决：

ln -s /usr/local/mysql/bin/mysql_config /usr/bin/mysql_config

这样建立了你的实际msyql安装目录和mysql_config命令的管理

经过configure就可以make了

在执行：./configure 时，又出现了一个问题：

checking for PDO includes... checking for PDO includes...  

configure: error: Cannot find php_pdo_driver.h.

检查的时候，不能找到php_pdo_driver.h，经过检查，发现在读php-config的时候，在读以前的配置。

解决方法：

./configure –with-php-config=/usr/local/php/bin/php-config (根据实际的路径的来指定)

在执行./configure --with-php-config=/usr/local/php/bin/php-config，又出现了一个问题：

error: mysql_query missing!?

解决方法：

./configure --with-php-config=/opt/php5/bin/php-config --with-pdo-mysql=/usr/local/mysql

(根据自己的实际路径，设定编译安装mysql的位置).

make && make install

注意pdo_mysql的全路径，我的是：
/usr/local/php/lib/php/extensions/no-debug-non-zts-20060613/pdo_mysql.so

然后在/usr/local/lib/php.ini加上一句：

extension=/usr/local/php/lib/php/extensions/no-debug-non-zts-20060613/pdo_mysql.so

重新启动apache即可看到已经加载pdo_mysql成功。

一、Mysql SQL Mode简介

通常来说MySQL服务器能够工作在不同的SQL模式下，并能针对不同的客户端以不同的方式应用这些模式。这样，应用程序就能对服务器操作进行量身定制以满足自己的需求。
 
这类模式定义了MySQL应支持的SQL语法，以及应该在数据上执行何种确认检查。这样，就能在众多不同的环境下、与其他数据库服务器一起更容易地使用MySQL。
 
可以使用" --sql-mode="modes" "选项，通过启动mysqld来设置默认的SQL模式。而从MySQL 4.1开始，也能在启动之后，使用SET [SESSION|GLOBAL] sql_mode='modes'语句，通过设置sql_mode变量更改其模式。
 
通常在linux下安装完mysql后，其默认的sql-mode值是空，在这种情形下mysql执行的是一种不严格的检查，例如日期字段可以插入'0000-00-00 00:00:00'这样的值，还有如果要插入的字段长度超过列定义的长度，那么mysql不会终止操作，而是会自动截断后面的字符继续插入操作，如下例：

mysql> create table t1 (c1 char(3));

mysql> insert into t1 values('abcd');

mysql> select * from t1;

+------+

| c1 |

+------+

| abc |

+------+

1 row in set (0.00 sec)

我们发现插入的字符被自动截断了，但是如果我们本意希望如果长度超过限制就报错，那么我们可以设置sql_mode为STRICT_TRANS_TABLES，如下：

mysql> set session sql_mode='STRICT_TRANS_TABLES'

这样我们再执行同样的操作，mysql就会告诉我们插入的值太长，操作被终止，如下：

mysql> insert into t1 values('abcd');

ERROR 1406 (22001): Data too long for column 'c1' at row 1

经常使用的sql_mode值：

说明：如果把sql_mode的值设置成后面的两个值（也就是我们说的严格模式），那么当在列中插入或更新不正确的值时，mysql将会给出错误，并且放弃insert/update操作。
 
在我们的一般应用中建议使用这两种模式，而不是使用默认的空或ANSI模式。但是需要注意的问题是，如果数据库运行在严格模式下，并且你的存储引擎不支持事务，那么有数据不一致的风险存在，比如一组sql中有两个dml语句，如果后面的一个出现了问题，但是前面的已经操作成功，那么mysql并不能回滚前面的操作。因此说设置sql_mode需要应用人员权衡各种得失，从而得到一个合适的选择。
 
Sql_mode的值还有很多，这里不再累述，可以参考相关的手册。
 
 

二、SQL Mode与可移植性

如果mysql与其它异构数据库之间有数据移植的需求的话，那么下面的sql_mode的组合设置可以达到相应的效果：

 

三、SQL Mode与数据效验

SQL Mode 还可以实现对数据效验和转移等功能如： 

1. 效验日期数据合法性. 
2. 在INSERT或UPDATE过程中，如果被零除(或MOD(X，0))，则产生错误 
3. 将‘"'视为识别符引号(‘`'引号字符) 
4. 禁用反斜线字符(‘\')做为字符串内的退出字符。启用NO_BACKSLASH_ESCAPES模式，反斜线则成为普通字符。 
5. 将||视为字符串连接操作符（＋）(同CONCAT())，而不视为OR。

简介

在MySQL中，数据库对应数据目录中的目录。数据库中的每个表至少对应数据库目录中的一个文件(也可能是多个，取决于存储引擎)。因此，所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。
 
在大多数Unix中数据库名和表名对大小写敏感，而在Windows中对大小写不敏感。一个显著的例外情况是Mac OS X，它基于Unix但使用默认文件系统类型(HFS+)，对大小写不敏感。然而，Mac OS X也支持UFS卷，该卷对大小写敏感，就像Unix一样。
 
变量lower_case_file_system说明是否数据目录所在的文件系统对文件名的大小写敏感。ON说明对文件名的大小写不敏感，OFF表示敏感。
 
例如在Linux下查看：

mysql> show variables like 'lower%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| lower_case_file_system | OFF   |

| lower_case_table_names | 0     |

+------------------------+-------+

2 rows in set (0.00 sec)

说明Linux系统对大小写敏感，MySQL也默认设置为对大小写敏感, 而Windows则相反。
 

大小写区分规则

Linux下：

• 数据库名与表名是严格区分大小写的；
• 表的别名是严格区分大小写的；
• 列名与列的别名在所有的情况下均是忽略大小写的；
• 变量名也是严格区分大小写的；

 windows下：

• 都不区分大小写

 Mac OS下（非UFS卷）：

• 都不区分大小写

 

参数说明

unix下lower_case_table_names默认值为 0 ；  Windows下默认值是 1 ；Mac OS X下默认值是 2 .

由大小写敏感转换为不敏感方法

如果原来所建立库及表都是对大小写敏感的，想要转换为对大小写不敏感，主要需要进行如下3步：[list=1]

将数据库数据通过mysqldump导出。

在my.cnf中更改lower_case_tables_name = 1，并重启mysql数据库。

将导出的数据导入mysql数据库。

 

注意事项

为了避免大小写引发的问题，一种推荐的命名规则是：在定义数据库、表、列的时候全部采用小写字母加下划线的方式，不使用任何大写字母
 
在任何系统中可以使用lower_case_tables_name=1。使用该选项的不利之处是当使用SHOW TABLES或SHOW DATABASES时，看不出名字原来是用大写还是小写。
 
请注意在Unix中如果以前lower_case_tables_name = 0将lower_case_tables_name设置为1之前，重启mysqld之前，必须先将旧的数据库名和表名转换为小写。

MySQL体积小，速度快，功能丰富，总体拥有成本低。它还是一个开源的关联式数据库管理系统，它的伟大成就说明了一个成功的公司是可以建立在开源之上的。
 
虽然用过mysql的人都曾对其出现的问题而抓狂，但只要是机器，总避免不了出问题的，更何况是一种每秒能保存成千上万行互联网数据，你怎么保证它一点错误都没有呢？
 
以下列举了8个开源关系型数据库的缺陷，其中不仅限于MySQL，还有是针对关系型数据库的。只有明白了关系型数据库和MySQL，才能更好地避免在使用MySQL中尽量少地遇到一些意外。
 

1、无法避免的bugs

任何一个软件包都有bug。但稍微深入了解一下，就会发现和Mysql相关的bugs自成体系。突然你就需要留心，因为NULL并不是以同样的方式出现，外键约束也没有像你想像的那样执行，连主键自动增长也会出错。

到处都避免不了小问题，而且并不总是可以修复的，这就是为什么一些人保持一个列表。还好MySQL维护着一个非常好的bug报告系统，让我们可以知道我些我们无法想像的事情，知道其他人也在经受同样的磨难。
 

2、关系表的不灵活性

关系表具有条理性是好的，但这迫使程序员要编造或把一些数据塞到已经定义好模式的列中。NoSQL之所以越来越受欢迎，其中一个原因就是它为程序员提供了足够的灵活性，以加速数据库的使用。如果一个街道地址需要增加一行，那么，你可以将它很容易地插入到一个NoSQL文档中。如果你想添加一个完整的新的数据块，无论它包含什么内容，文档模型也可以原封不动地接受你的数据，而不必改为它要求的数据格式。

假设你用整数格式建立了一个全部是邮编的表格。这个表十分高效，执行规则也很好。可是有一次，有人上传了一个使用了连字符的九位数邮编。或者说你得到了一位来自他国的客户的信件，上面写有邮政编码。这时，你会感到一切都乱了。老板要求网站要在几小时内恢复正常工作。然而，你已经没有时间重建数据库。程序员可以做什么？使用黑客手段把这位客户的邮政编码由base 64的数字格式改为base 10格式？或者设置一个使用转义编码的辅助表格，用来说明真正的邮政编码或者其他？危险的黑客无处不在，但你没有时间来搞定它。

MySQL的关联规则让每个人都诚实和谨慎，但它能强制我们避开易受攻击和欺骗的麻烦。
 

3、存储引擎混乱

总体来说，Mysql的存储引擎接口定义还算良好的。MySQL不是实际上的同一的数据库。它是由几个数据库组成，它们的大多数细节都被统一的表面掩盖了。开始时有一个MyISAM引擎，它很快但在前后一致上不能做到完备。有时你需要速度并且可以接受不一致的结果时是很好的。

当人们需要更多时，具备完整事务支持的Inno DB出现了。但这还不够。现在，它可能有20种存储引擎的选择——这足以使一个数据库管理员疯狂。当然，有时在不同的存储引擎之间切换而不必重写你的SQL是很好的，但是切换后总会带来混乱。这个表格我选择的引擎是MyISAM还是innoDB呢？或者，我决定输出的数据是CSV格式的吗？
 

4、JOIN联合查询

曾经，将数据分表保存是计算机科学史上的伟大创新。分开后的表不仅结构简单，使用上也简化了许多。但它却需要使用join语句来进行查询。

sql通过一系列join构建的复杂查询将开发者推入了困惑与绝望的深渊。而且存储引擎也需要以最优的方式来高效地解析join语句。开发者需要绞尽脑汁编写查询语句，然后数据库对其进行解析。

这就是很多注重运行速度的开发者放弃数据分表转而使用不规范数据表的原因。不区分数据实体，将所有数据保存到一个大表中——以避免复杂的查询。这样确实很快，并且服务器也不会耗尽内存。

现在的磁盘空间很廉价。8TB的磁盘已经在售，更大容量的也将上市。我们不再需要为使用join而绞尽脑汁了。
 

5、分支的混乱

毋庸置疑，一个可靠的、得到良好支持的MySQL分支，可以带来竞争和选择，但是它也引起困惑和混乱。更糟糕的是，一个称为MariaDB的ＭySQL分支，由Monty Widenius维护着。他同样也在参与编写MySQL。那么，Maria DB是真正独立的值得我们拥护的吗？或者它是MySQL？我们是否应该坚持使用由创建原始mysql数据库的组织运营的核心代码？或者我们应该加入那些被认为更聪明的，往往很酷的背叛者？

如何获取关于兼容性的信息？虽然Maria DB和MySQL十分相似，但它们之间也有差异。这就是大家一直都在争论它的原因。在性能方面，在我们查询的范围内，在两个阵营中，也许它们的工作方式相同，但也许不同，也许将来会不同。
 

6、开发MySQL的动机

虽然MySQL是一款成功的开源产品，但它仍属于商业中的一款产品，专业开发者需要靠它来获得利益，当然，最直接的利益就是薪资。当大多数用户在持续地享受开源许可证带来的最佳体验时，毫无疑问这家公司还在为赚取足够的钱来维持运营而努力。这导致自由代码在“社区版”和出售给企业的完整产品之间产生了奇怪的分岐。

我们应该对这款产品付钱吗？这种在社区版开展经营的行为是否公平？企业版中额外的功能是不是一个噱头，以引诱我们不断付费的呢？这至少说明一点，它是另一些需要回答的问题：选用哪个版本？遵照哪种许可证？选用它的哪个功能集？
 

7、原生JSON支持的缺乏

通过安装MySQL查看其年龄，然后你就知道需要添加哪些驱动程序使它变得可用。MySQL通常在3306端口上通信，一般输出的是它本身难以理解的格式化数据。如果要让你的代码和它通信，你必须添加另一层代码，将MySQL的语言转换成有用的东西。这些层的代码，以库的形式分发，经常需要人们购买一个商业许可证。

现代数据存储层通常直接以JSON通信。虽然MySQL和Maria DB现在有能力解析SQL中的JSON部分，但这还远远不够，原生的JSON接口已经被广泛使用于CouchDB、MongoDB，或任何最新的工具中。
 

8、封闭源和专有模块的兴起

虽然MySQL是开源的，但除了一些在”开源核心“周边开发的一些较新的、非开源的代码和专有模块。程序员也需要赚钱、需要生活，Oracle需要拿它的辛苦成果来换钱，这是一种现实，也是商业的性质。使用MySQL你也不可以免费得到任何东西。

要求MySQL始终坚持在一个很高的标准上，这有点不公平，因为开源的成功可能是一个圈套。它开始可以免费，但并不意味着它可以始终免费。如果企业需要更多新的功能，他们就要通过各种方式付费来获取。有时向Oracle付费，比自己来编写代码要便宜得多。有时商业的、不开源的代码是有意义的。

MySQL虽然作为一个成功的开源系统，但以上这些问题也总不可避免地出现，这就需要我们在它们发生之前有个深刻的认识，才能在今后的应用中避免不必要的麻烦。