分类： 技术

如果主库挂掉了

A->B A->C

A挂了只有BC了

要检查B/C是否同步完成并且检查谁更靠前

比较是否同步完成

这个时候的状态是io_thread:NO

if(master_log_file == reley_mast_log_file) and (read_master_log_post == exec_master_log_pos)

只有当这2个条件成立 我们才认为是同步完成的

b.Master_Log_File , c.Master_Log_File比较谁更大 就是同步的更完全

如果Master_Log_File都一样，那么比较 Exec_Master_Log_Pos

GTID会自动补上来 上面是传统模式

主从数据一致性怎么去校验（怎么确认主从是同步的？）

运行一段时间之后有一些不规范的操作搞成同步不一致问题

大的库分段检测 分析数据变换情况

pt-table-checksum

利用语句级复制，在主从库上对表分段进行校验，如果值不一样，说明就不同步了

pt-table-sync

利用语句复制，所有的修复都是在主库上完成的

https://www.percona.com/   Percona toolkit 

下载最新版本

用yum安装 方便管理 把yum源弄进来

pt 大多是基于perl编写的

有部分的shell

同一组内的数据必须同一个端口

每组的端口号是全局唯一的

总结：

row 格式的binlog复制，有些情况下会比statement的快

原因是：sql_thread应用日志时,在有主建的情况，会利用主键更新数据

reset master 清掉Executed_Gtid_Set

gtid_purged 配置在slave上

slave只会读取gtid_purged这个GUID之后的数据之前的就不要了

MySQL 5.6.22的参数，总共432个

change maseter to 

show global variables;

以下所有参数都是mysqld里的

复制中的一些重点参数(最基础的)

log-bin（binlog路径）
server-id（服务标识）
log-bin-index
binlog_format（binlog格式）
binlog_cache_size
max_binlog_size
sync_binlog
expire_logs_days
log_bin_trust_function_creators

server-uuid 　以后有可能代替server-id(标识)

gtid从库没有配置service id也是也是可以的 但是主库一定要配置

Master的参数：

============================================

server-id
  唯一区别ID，同一个集群内不可重复
  可动态修改

binlog_format
  binlog日志格式：statement、row、mixed三种
  可动态修改 建议修改session的不修改全局的 推荐用row

binlog_cache_size
  binlog写入buffer
  可动态修改

max_binlog_size
  限制单个binlog大小
  可动态修改 通常配置1M

max_binlog_size
  限制单个binlog大小
  可动态修改 一般建议设置成200M/500M 根据日志产生量 默认是1G

  5min左右刷一个或者10min左右刷一个

  binlog刷新过程数据是提交不了的 所以让binlog刷新慢一点 写数据不会被卡住

sync_binlog = n
  多少个SQL之后，调用fdatasync()函数刷新binlog到disk

  默认是0，在调优中很重要

  多少个sql刷一次binlog的cache，同步到磁盘

  比如说现在写成0就是由系统来刷新binlog cache

  系统觉得binlog大了就去刷新

  对一些交易型的，账单或者扣钱的东西 建议设置成1

  让没个sql都刷新一下binlog 让binlog更安全一点

  当然设置成1了 性能也是最差的，设置成0的时候性能是最好的

  这个是对性能影响很严重的参数

  tokodb不开binlog写入可以达到5,6w

  开启之后写入只能达到2W的写入

expire_logs_days =n
  n天后自动删除binlog
  可动态修改 默认指定为7

  如果不开启就会造成binlog把磁盘占满了

  应用卡住了 也不知道怎么回事

log_bin_trust_function_creators

  默认是0 要用的话改成1就行了

  允许在存储过程调用的时候用到，如果没有涉及存储过程是用不到

log_warnings

  参数级别 默认是2  默认是0

  error log看到Abort connection拒绝连接请求

  如果不想看到这些就把值设置成1

binlog_error_action 

binlog_error_action=ABORT_SERVER

当空间满了写不进去了 会在应用层直接报错 不会直接卡在那里

5.6之前只能再error log里看到

binlog_row_image=[full|minimal|noblog]

也是5.6的

日志格式为row的时候日志里会记录修改之前的和修改之后的记录所以日志很大

利用这个参数之后只保留写后的记录

update tb set c2=xxx where id=xxx;

[minimal]

update tb 
set 
@2=xxx,
where 
@1=xxx,
@2=xxx;

没有变更到的就不记录了

Binlog_rows_query_log_events=[1|0]

默认关闭，（日志格式是row 的时候）开启参数之后会生成query event

会记录用户原始sql

gtid_mode=on

开启的时候必须设置这些参数

log-bin ,

log-slave-updates(从库上也会记录binlog 可以用来备份或者机连备份) ,  

enforce-gtid-consistency（一致性，控制哪些语句能安全的记录到binlog里）

Executed_Gtid_set 执行过的gtid集合

set gtid_next="uuid:NO."; 指定下一个事物是哪个事物

set global gtid_purged='uuid:NO.';

进行同步的时候忽略这个事件之前的,告诉主库包括NO.之前的事物都不要给我了

reset master;

才能清空，清空之后才能设置

Gtid_executed

从库上到底执行过哪些gtid操作

uuid:1-100:300-10000:20001-NNNNN

发现有空洞

有可能做了互为主从造成了空洞

如果是传统的A->B的应该是不会有空洞的

uuid:1-NNNNN

或者是set global gtid_purged开始的 到 NNNNN

和

show master status\G  

show slave status\G

一样

这个参数也需要reset master

slave的参数

============================================

server-id 

推荐配置service-id可能从库以后要挂从库或者转为主库

relay-log

从本地拿到的binlog会存到relay-log里

relay-log-index

read-only 对非super 用户起作用

set global read-only = 0|1

slave其他参数

log-slow-slave-statements
log_slave_updates
max_relay_log_size
relay-log-info-file
relay_log_purge
relay_log_recovery 
replicate-same-server-id 
skip-slave-start
slave_load_tmpdir
slave_transaction_retries

slave_parallel_workers

log-slow-slave-statements

sql_thread 执行sql超过long_query_time 会记到慢日志中

默认没开启

max_relay_log_size

设置一下relay-log的大小

slave和master不会形成长连接 每次链接就会生成一个relay log

relay-log-info-file =relay.info

relay crash recover

[root@node21 data]# cat relay-log.info 
7
./mysql-relay-bin.000008
4
mybinlog.000005
191
0
0
1
1

执行到005 位置是191

从库忽然挂掉 又起来了同步接不上

要把这个参数打开之后会从binlog005 191这个位置向主库重新请求

在gtid里做了一个变相实现

5.6.21叫 simplified_binlog_gtid_recovery 

5.6.23改名了叫binlog_gtid_recovery_simpliefied

可以从gtid里拿到已经同步到哪个gtid了只要这个gtid之后的数据就行了

5.6.21只前gtid重启之后没有这个重新请求的过程中没有crash recover处理机制

是在5.6.21之后引入进来的

而且这个参数默认是没有开启的

这个功能很好 之前从库挂掉都要去修复

replicate-same-server-id

默认没开启 自己复制自己的server id基本上不用 

skip-slave-start

建议在配置文件里要有

slave启动之后不要默认开启同步

slave_load_tmpdir

指向到tmpdir下 一般不用设置

slave_transaction_retries

sql语句在执行中上层mysql如果发生堵住的情况把某个数据区间锁住了

sql thread就会等待锁的释放 机会有一个重试的过程

当重试超过次数之后 就会报错

start slave sql_thread;

5.6引入  并行复制 库级别的复制

slave_parallel_workers 最大1024

默认是关闭的

如果sql_thread断了但是io_thread还是yes

同步断掉之后本地的relay_log 会记录很多 造成磁盘被写满

所以引入了relay_log_space_limit 限定relay log占用磁盘大小

超过了之后io_thread  会等待relay-log释放空间

relay log执行完会被删掉

SHOW SLAVE HOSTS

slave上需要配置这3个参数

report_password
report_port
report_user

实际生产中很少去配 库都是动态切换的 配置上意义不大

sync_master_info
sync_relay_info

5.5之后 可以吧master info 和 relay info存到数据库里

5.6.17之前这2个参数如果配置成1或者过小会有内存溢出错误

保持默认不变就行了

sync_relay_log   ＝  sync_binlog

========================================

复制过滤规则

1. 库级别的

2. 表级别

支持： 精确和通配符？

正向逻辑和反向逻辑

分binlog 和sql_thread两个部分

一部分可以控制记录binlog的时候记录什么

另一部分可以在sql thread里面控制执行什么

master 部分

只记录某个db的binlog

replicate-do-db = "leokim"

create database jl;

就不会记录

use jl;

insert into leokim.tb(col1) values(1);

语句格式下是记录不上去的

在行格式是能让记录的

replication-ignore-db="mysql"忽略mysql库

想忽略某个表

replicate-ignore-table=leokim.quota

复制中改库

a_db -> b_db

在slave上才能配置

replicate-rewrite-db='a_db->b_db';

把某个库映射到另一个库上

从库上的

binlog

replicate-wild-do-table
replicate-wild-ignore-table

replicate-wild-do-table="leokim.ds_%";（不是这个前缀的就不计）

mysql同步复制里有个坑

slave_net_timeout

从库和主库多少时间断开才会说连不上主库 — 默认3600S

忽然闪断2min 数据不同步 io_thread 和 sql_thread 都是ok

推荐配置成10

slave_net_timeout=10

slave_skip_errors

slive中复制，忽略那些错误

常见的：

1062 主建冲突

1032 找不到记录

一般建议不允许忽略任何错误

除非从库来不及修又要用

就先skip掉晚上用工具修复

sql_slave_skip_counter
忽略多少个复制事件，遇到个别错误（主键冲突、记录不存在等）时，可以忽略这些事件，继续复制进程
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n;
START SLAVE;
SHOW SLAVE STATUS\G
一般一次只忽略一个事件，除非很肯定，否则不要设置大于1

简单总结：

推荐gtid 管理方便不用找偏移量位置

 

server-id 推荐ip最后一位

传统配置：

#replication 
log-bin=/path/mysql_xxxx/logs/mysql-bin
server-id=[ip]port
log-bin-index=mysql-bin.index
binlog_format=row
binlog_cache_size=1M
max_binlog_size=200M
sync_binlog=0
expire_logs_days=7
log_bin_trust_function_creators=1
#master binlog filter

#slave
relay-log=relay-bin
relay-log-recovery=1

log-slow-slave-statements
log_slave_updates
slave_net_timeout=10
master-retry-count= 86400

我在主库清除了所有binlog然后在leo表里删除了几条记录又重新插入了一些

从库操作

change master to master_host='192.168.61.131',master_user='repl',master_password='repl4slave', master_auto_position=1;

可以看到1-3的都copy过来了

示例情况

我在从库插入了一条记录 

在主也插入同样记录的时候，从库同步就会报错

这个时候处理方式是 把从库上的该条重复记录删掉

然后执行

start slave sql_thread;

就继续同步过来了

同样现在从库删除一条记录

然后再去主库删除同样的记录

尽然没有报错 哈哈

update也不会报错 

主库上没有id为2的记录 从库上有id为2的记录

主库上执行delete id 2 从库上的会被干掉

binlog要换成row格式

对于gtid必须要用row格式要么会有很多错误都不爆

上面那些delete update应该出错的就会出现 slave就会起不起来了

跳过错误

set gtid_next=b9c8ef95-27ea-11e7-9c7b-000c29a8f010:10

stop slave；

begin;commit;

跳过错误

同步就继续了数据就同步过来了

初始化master和slave数据

删除所有数据

创建从库账号

前面的都是初始化的log可以不要

从库也是相同配置

change master to master_host='192.168.61.131',master_user='repl', master_password='repl4slave', master_log_file='mybinlog.000001', master_log_pos=120;

warning就是不提倡在命令行里直接写密码 哈哈

这里有个报错 说是因为server id要用不一样的 我去修改一下

还是有error

妈蛋···绑定master的mybinerlog写成mybilog了·····

后来试了一下还是有报错

最后发现是定义master的时候把密码写错了

成功了。

主库上创建leokim的库

从库上也生成了leokim这个库

主库上创建表

从库上也能看到表和数据

MYSQL复制的几种模式

Mysql中的复制可以是基于语句（Statement Level）的和基于行的（RowLevael）。
从 MySQL 5.1.12 开始，可以用以下三种模式来实现：
— 基于SQL语句的复制(statement-based replication, SBR)，
— 基于行的复制(row-based replication, RBR)，
— 混合模式复制(mixed-based replication, MBR)。
相应地，binlog的格式也有三种：STATEMENT，ROW，MIXED。

在运行时可以动态的改变binlog的格式，除了以下几种情况：
. 存储过程或者触发器中间
. 启用了NDB(Mysql cluster 主要采取的存储引擎)
. 当前会话使用 RBR 模式，并且已打开了临时表

如果binlog采用了 MIXED 模式，那么在以下几种情况下会自动将binlog的模式由 SBR 模式改成RBR 模式。
. 当DML语句更新一个NDB(Mysql cluster 主要采取的存储引擎)表时
. 当函数中包含 UUID() 时
. 2个及以上包含 AUTO_INCREMENT 字段的表被更新时
. 行任何 INSERT DELAYED 语句时
. 用 UDF (User Definition Function)时
. 视图中必须要求使用 RBR 时，例如创建视图是使用了UUID() 函数

设定主从复制模式的方法非常简单，只要在以前设定复制配置的基础上，再加一个参数：
binlog_format="STATEMENT" 
#binlog_format="ROW"
#binlog_format="MIXED"
当然了，也可以在运行时动态修改binlog的格式。例如
mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

两种模式各自的优缺点：

SBR 的优点：

1．技术比较成熟

2. binlog文件较小，log文件可读

3. binlog中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况

4. binlog可以用于实时的还原，而不仅仅用于复制

5. 主从版本可以不一样，从服务器版本可以比主服务器版本高

SBR 的缺点：

1. 不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候。

2. 调用具有不确定因素的 UDF 时复制也可能出问题
使用以下函数的语句也无法被复制：
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非启动时启用了 –sysdate-is-now 选项)

3. 对于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 语句会阻塞其他 INSERT 语句，对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响。以为需要记录上下文信息所以存储函数(不是存储过程，mysql内部存储的函数)在被调用的同时也会执行一次 NOW() 函数。

4. 确定了的 UDF 也需要在从服务器上执行
数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错
执行复杂语句如果出错的话，会消耗更多资源
 

RBR 的优点：

1. 任何情况都可以被复制，这对复制来说是最安全可靠的

2. 如果从服务器上的表如果有主键的话，复制就会快了很多

3. 从服务器上采用多线程来执行复制成为可能

4.  不会出现某些特定情况下的存储过程，或function，以及trigger 的调用和触发无法被正确复制的问题。

RBR 的缺点：

1. 相比SBR的binlog 大了很多，还原的时候可能比较慢

2. 复杂的回滚时 binlog 中会包含大量的数据

3  因为RBR是基于行级的，所以如果有alter table 之类的DDL操作产生的数据量非常大。(GRANT，REVOKE，SET PASSWORD等语句建议使用SBR模式记录).

4.  当在非事务表上执行一段堆积的SQL语句时，最好采用 SBR 模式，否则很容易导致主从服务器的数据不一致情况发生
5.  采取RBR模式记录的话，log是不可读的。（经过加密）

注：采用 RBR 模式后，能解决很多原先出现的主键重复问题。

MySQL中可以有二类用于生成唯一值性质的工具：UUID()函数和自增序列，那么二者有何区别呢？我们就此对比下各自的特性及异同点：

    1.都可以实现生成唯一值的功能；

    2.UUID是可以生成时间、空间上都独一无二的值；自增序列只能生成基于表内的唯一值，且需要搭配使其为唯一的主键或唯一索引；

    3.实现方式不一样，UUID是随机+规则组合而成的，而自增序列是控制一个值逐步增长的；

    4.UUID产生的是字符串类型值，固定长度为：36个字符，而自增序列产生的是整数类型值，长度由字段定义属性决定；

接下来，详细讲解下UUID()函数产生的值：

root@localhost : (none) 06:09:40> SELECT UUID(),LENGTH(UUID()),CHAR_LENGTH(UUID())\G

从上面的执行结果部分的信息看：

1.同一个SQL语句中，多处调用UUID()函数得到的值不相同；

2.得到的随机值由5个部分组成，且分隔符位为：中划线；

3.多次调用或执行得到的后2组值相同，若把mysqld服务器关闭，重新启动之后，会发现第四组的组与未重启前的值发生变化，然后一直不变化，只要重新启动mysqld服务就会发生变化。另外，对于同一台机器，第五组值始终不会发生变化；

4.字符个数为：36，占字节数为：36（注：系统默认字符集编码：utf8）；

针对UUID产生的值组成部分，作如下解说：

1. 前三组值是时间戳换算过来的；

2. 第四组值是暂时性保持时间戳的唯一性。例如，使用夏令时；

3. 第五组值是一个IEE 802的节点标识值，它是空间上唯一的。若后者不可用，则用一个随机数字替换。假如主机没有网卡，或者我们不知道如何在某系统下获得机器地址，则空间唯一性就不能得到保证，即使这样，出现重复值的机率还是非常小的。

UUID函数对复制的支持：

UUID函数属于不确定性函数，为此不支持MySQL 复制的STATEMENT模式，但是支持MIXED、ROW二种模式，大家可以设置2组测试模式，以5.1.系列版本为例。

测试基于命令行模式复制：

tx_isolation = REPEATABLE-READ

binlog_format = STATEMENT

 

测试基于命令行/混合模式复制：

tx_isolation = REPEATABLE-READ

binlog_format = MIXED  OR ROW

 

在主服务器上执行同一个SQL语句：

INSERT INTO  test_uuid(username) VALUES(UUID());

然后再比对主从服务器上表中存储的值，会发现基于命令行模式的：主从不一致，基于行/混合模式的：主从数据时一致；

 

建议：在复制模式下，需要用到UUID()函数，则一定要使用基于行/混合模式复制方式。

名词解释：

对于输入参数相同，且同一时间执行或一个SQL中多处调用，而得到不同值得函数，我们就称其为：不确定性函数。

备注：

在MySQL 5.1.*及更高版本有一个变种的UUID()函数，名称：UUID_SHORT()，生成一个64位无符号的整数，例如：

root@localhost : (none) 02:46:42> SELECT UUID_SHORT()\G

*************************** 1. row ***************************

UUID_SHORT(): 6218676250261585921

1 row in set (0.00 sec)

 

UUID()函数产生的值，并不适合作为InnoDB引擎表的主键，至于详细的原因，请阅读文章InnoDB引擎表的主键选型。

在mysql中，可以使用uuid 来生成主键，但是用mysql的uuid()函数 ，生成的uuid是36位的，其中包含32个字符以及4个分隔符（-），往往这个分隔符对我们来说是没有用的，可以使用mysql自带的replace函数去掉分隔符

replace(uuid(),'-','')   —->将uuid()中的‘-’，去掉，即替换成空串；

此外

upper(replace(uuid(),'-',''))用于将字符转换为大写

MySQL采用开放可插入式存储引擎架构，提供类似电源插线板的功能，其后接入的存储引擎就类似电器设备，而我们大家常用的存储以MyISAM和InnoDB为主，早期大家主要使用MyISAM引擎支持业务，随MySQL支持业务范围越来越广，存储的数据对企业越来越重要，尤其PC服务器支持的最大内存越来越大，内存的价格也越来越便宜，逐渐采用InnoDB引擎为主.二种风格迥异的存储引擎，各自内部存储算法和数据操纵实现等都竞相不同，另外InnoDB引擎与其他商业数据库产品存储引擎也不太相同，为此我们必须根据使用的存储引擎特点，设计合理的数据存储结构和数据操纵方式。本文将围绕InnoDB存储引擎的主键设计而展开，告诉大家怎样设计表的主键才是合理的做法。

讨论InnoDB引擎表的主键选型的要求之前，我们大家先简单温习下InnoDB引擎表的元数据和索引数据存储结构特点。

InnoDB引擎表的数据和索引都是存储在同一个文件中，InnoDB引擎的页大小默认为16K，且页空间使用率为15/16，为此每页能存储的数据量是有限的。主键和数据的关系是数据存储在簇索引的叶子节点中，接下来我们看下主键和数据的组织结构关系，如图1-1：

对于非簇索引，又是如何存储的呢？通过翻译官方文章，得知：

每个非簇索引的叶子节点存储的数据都包含簇索引的值，然后通过簇索引的值，可以查找到对应的元数据，我们继续看一下非簇索引的存储结构，以及与簇索引之间的关系，如图1-2：

紧接着，我们来理清楚InnoDB引擎表的簇索引为哪三类：

1.  主键也即是我们文章说的簇索引；

2.  若表中无主键，但是存在唯一索引，且字段定义为非空，则作为簇索引；

3.  表无主键，也无非空唯一索引，则内部默认隐含性创建一个长度为6个字节的字段作为簇索引字段；

mysql中每个表都有一个聚簇索引（clustered index ），除此之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。

通过上述探讨和分析，弄清楚了数据存储页的空间值和利用率、簇索引和非簇索引的存储结构，以及簇索引和元数据、簇索引和非簇索引的存储关系，以及簇索引为哪三类，那么我们建议大家一定要为InnoDB引擎表创建一个主键，没必要搞一个唯一性索引且字段定义属性非空，而不创建主键的表结构，我们再分析优秀主键具备的素质：值域范围够用且存储长度越短、值的唯一性和易比较性、数值的有序性增加，三个素质的各自优点，单独详细分析。

主键的值域够用且长度越短，将产生三个方面的优势

(1).     相同数据行数的表，数据容量越小，占用磁盘空间越少，为此可以节约物理或逻辑IO和减少内存占用；

(2).     普通索引的长度也将更短，可以减少通过普通索引搜索数据的物理或逻辑IO；

(3).     减少索引数据存储的页块裂变，从而提高页的利用率，以及减少物理IO；

主键的唯一性

(1).     主键的值要求必须唯一，且非空；

(2).     主键值每次插入或修改，都必须判断是否有相同值，为此减少索引值需要比对的长度，可以提高性能，或者间接地让其转换成比对数值大小的方式，提高其比对判断的效率；

主键值插入的有序性，将产生二个方面的优势

(1).     主键存储的块与块之间是有序的，然后块内有也是有序的，主键值的有序插入，可以减少块内的排序，节约磁盘物理IO；

(2).     主键值的有序性，可以提高数据舒顺序取速度，提高服务器的吞吐量，也可以节约物理IO；

上述讨论的数据存储结构及关系，以及主键字段要求的素质等理论知识之外，我们还需要考虑实际生产环境的业务、架构等综合因素，我们实际生产环境可能会使用四类属性作为主键：

(1).     自增序列；

(2).     UUID()函数生成的随机值；

(3).     用户注册的唯一性帐号名称，字符串类型，一般长度为：40个字符；

(4).     基于一套机制生成类似自增的值，比如序列生成器；

那么我们接下来，再分析下这四类属性各自作为表主键的优缺点：

(1).     自增序列：从小到大 或从大到小的顺序模式增加新值；数据类型也利于进行主键值比较；存储空间占用也相对最小，一般设置为：4个字节的INT类型或 8个字节的BIGINT类型；若是想进行数据水平拆分的话，也可以借助设置mysqld实例的2个参数：auto_increment_increment 和 auto_increment_offset；另外，唯一缺点就是自增序列是一个表级别的全局锁，在5.0系列大规模并发写的时候，因锁释放机制的问题容易出现瓶颈，但是5.1系列做了改进，基本上不存在此问题；

(2).     UUID()函数：值为随机性+固定部分，其值产生是无序的，且同一台服务器上产生的值相同部分为77.8%；产生的值字符个数为36，按utf8编码计算，占用的存储空间为36个字节；对于数据水平拆分支持，无需特殊设置；

(3).     使用用户注册的帐号名称，字符串类型，其值的产生依赖用户输入，为此数据基本上为无序增加，字符串的长度也是不定的，只能通过前段技术控制最短最大长度值的限制，对水平拆分支持，无需做特殊设置；

(4).     序列生成器的架构，类似自增序列，不过需要借助额外的开发工作量，以及提供一个第三方的服务，可以规避自增序列的字增全局锁的问题，提高并发，对数据水平拆分可以更好地支持；

(5).     双主复制架构的概率性碰到的场景：主服务器的数据执行成功，而没有复制到在线备用服务器时，出问题的概率确实存在，其他类型的做法，也必须人工干涉解决，都无简单且合理的自动化办法，以上四种办法都无法规避；

通过四种属性值作为主键的优缺点分析，以及对比前面我们阐述的主键需要的优秀素质，若是不考虑水平拆分的问题，带来额外设置上的麻烦，则自增序列是最佳的主键字段选择；用户的注册帐号本身要求唯一性且非空的场景下，则可以作为主键字段的选择；若是考虑水平拆分的问题，则采用自增序列生成器的架构，非常易用和可靠的实现方式，产生的值是最佳主键字段的选择；

【结束】

使用什么类型的字段属性作为主键，最关键核心的要考虑存储引擎：如何存储元数据、如何检索元数据、如何维护其内部的索引组织结构，以及我们要实现的业务是什么，最后共同决定我们，如何设计一张用于存储数据的表，以及决定操纵数据的SQL语句如何编写，再结合业务特点就决定了我们的索引如何创建，建议大家多关注InnoDB引擎内部实现原理和机制，可以阅读官方提供的一个文档InnoDB引擎内部实现，以及多分析和关注业务特点。

聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。
一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。

在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的：
聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

因此，MySQL中不同的数据存储引擎对聚簇索引的支持不同就很好解释了。
下面，我们可以看一下MYSQL中MYISAM和INNODB两种引擎的索引结构。

如原始数据为：

MyISAM引擎的数据存储方式如图：

MYISAM是按列值与行号来组织索引的。它的叶子节点中保存的实际上是指向存放数据的物理块的指针。
从MYISAM存储的物理文件我们能看出，MYISAM引擎的索引文件（.MYI）和数据文件(.MYD)是相互独立的。

而InnoDB按聚簇索引的形式存储数据，所以它的数据布局有着很大的不同。它存储数据的结构大致如下：

注：聚簇索引中的每个叶子节点包含主键值、事务ID、回滚指针(rollback pointer用于事务和MVCC）和余下的列(如col2)。

INNODB的二级索引与主键索引有很大的不同。InnoDB的二级索引的叶子包含主键值，而不是行指针(row pointers)，这减小了移动数据或者数据页面分裂时维护二级索引的开销，因为InnoDB不需要更新索引的行指针。其结构大致如下：

INNODB和MYISAM的主键索引与二级索引的对比：

InnoDB的的二级索引的叶子节点存放的是KEY字段加主键值。因此，通过二级索引查询首先查到是主键值，然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块。而MyISAM的二级索引叶子节点存放的还是列值与行号的组合，叶子节点中保存的是数据的物理地址。所以可以看出MYISAM的主键索引和二级索引没有任何区别，主键索引仅仅只是一个叫做PRIMARY的唯一、非空的索引，且MYISAM引擎中可以不设主键。

参考资料：
高性能MYSQL

5.0开始有字符集，5.1，5.5, 5.6 ,5.7

５.５以后多了一个utf8mb4 支持emuj表情

1. 能根据选择的字符集把数据库的字符集配置正确

2.如果到升级或是迁移乱，能正确的转码（４.０升级5.0 ）

字符集是一套符号和编码的规则，字符串都必须有相应的字符集

校验集是这套符号和编码的校验规则，定义字符排序规则，字符串之间比较的规则

XXX_bin

将字符串中的每一个字符用二进制数据存储，区分大小写

xxx_general_ci

ci为case insensitive

不区大小写

XXX_general_cs　

cs为case sensitive

区分大小写

结果集的字符集

•
字符集支持多层面：服务器层（server）、数据库层（database）、数据表（table）、字段（column）、连接（connection）、结果集（result）

创建的库对应server的字符集

结果集是对应client的字符集

字符集可以对库，表，字段

show create database 

show create table 

连接进来的字符集可以运态的修改：

SET NAMES latin1/gb2312/utf8;

character_set_system永远是utf8的

character_set_filesystem 是binary的

了解一下常用的字符集

latin1,utf8, gbk,gbk2312

• gbk/gb2312
•
gb2312是双字节字符集，不论中、英文字符均使用双字节来表示，为了区分中文，将其最高位都设定成1
•
gb2312是gbk的子集，gbk是gb18030的子集
• gbk包括中日韩字符的大字符集合
• 通常使用gbk字符集足够
•
国际通用性比utf8差，不过utf8占用的数据库比gbk大（三字节字符集）
•
gbk、gb2312等字符集与utf8之间都必须通过Unicode编码才能相互转换：

用gbk是可以节省一个字节的

不可见字符

字符转换

gbk、gb2312 ->Unicode ->utf8
utf8 ->Unicode  ->gbk、gb2312

• utf8
• 8-bit Unicode Transformation Format，三字节字符集
•
是Unicode的一种存储方式，可变长度字符编码，又称万国码
•
UTF-8使用可变长度字节来储存 Unicode字符，例如ASCII字母继续使用1个字节储存，重音文字、希腊字母或西里尔字母等使用2个字节来储存，而常用的汉字就要使用3个字节
•
数据库字符集尽量使用utf8(客户端连接(connect)及结果(result)字符集也采用utf8字符集，最终HTML页面亦是采用utf-8)，是的数据能很顺利的实现迁移及多种终端展示

char(30)存30个字母或是30个汉字占用的长度是一样的吗？

字母是ascii码　一个字节，30个汉字是90个字节

UTF-8使用可变长度字节来储存 Unicode字符，例如ASCII字母继续使用1个字节储存，重音文字、希腊字母或西里尔字母等使用2个字节来储存，而常用的汉字就要使用3个字节

CHAR(30)在UTF-8字符集下，最多可以写入几个英文(ASCII码)？

30个

CHAR(30)，GBK字符集，存储了30个汉字

60个

CHAR(30)，GBK字符集，存储了30个英文，字节数应该是

30个字母都是ascii码

varchar最大长度是65355字节

超过255后2个字节做长度记录

varchar(N) utf8;

(65535-2)/3;

GBK

(65535-2)/2

能多存1W多个汉字

选择合适的字符集

什么都不知道时，采用系统默认的字符集（latin1），因为在存储层，它可兼容任何编码的字节流，并且节省空间，唯一不足是检索结果不够精确

非常肯定只有中文终端用户时，可选择gbk/gb2312

为了方便数据迁移，以及多终端展示，最好是用utf8

中文字段创建索引进行where做比较可能不准 可以转成二进制进行比较

使用中文字符集的： taobao.com , qq.com

使用utf-8的： baidu.com , weibo.com

思考一下项目中如何选择字符集？

如果可以控制网站上的展示形态，可以考虑用gbk或是更小的gb2312字符集（一方面字符集比较小，另一方面占位比较少，传统中比较少带宽）

IDC 1M 　300以上的

如果控制不了用户的输入： 如webibo.com 或是基于用户名（可以有汉字的系统）一定要使用utf8

一哥们可能失恋了 写了个程序修改签名 一分钟几百次

一条记录大量update 把mysql占满了

限制某个用户触发某个指令的次数

对于同一条记录大量的update怎么处理？

偷菜游戏狂点偷菜积分满天飞

更新合并

update server

如果在内存就在内存更新

如果不在内存就用update server拉到内存里

设置更新频率，2分钟做一次更新到库

最后更新的数据 为准

数据库的字符集设置方法

字符集继承

只要设置最上层字符集就可以

default-character-set = gbk

%s/gbk/utf8/g

show [global] variables  '%char%';

set global  xxxx;

全局级对现在会话不受影响，要重启才会起作用

set xxx;

设置当前会话

set global  character_set_server=gbk;

show global variables like "%char%";
+————————–+—————————————————————+
| Variable_name            | Value                                                         |
+————————–+—————————————————————+
| character_set_client     | utf8                                                          |
| character_set_connection | utf8                                                          |
| character_set_database   | utf8                                                          |
| character_set_filesystem | binary                                                        |
| character_set_results    | utf8                                                          |
| character_set_server     | gbk                                                           |
| character_set_system     | utf8                                                          |
| character_sets_dir       | /opt/mysql/mysql-5.6.22-linux-glibc2.5-x86_64/share/charsets/ |
+————————–+—————————————————————+

show variables like "%char%";
+————————–+—————————————————————+
| Variable_name            | Value                                                         |
+————————–+—————————————————————+
| character_set_client     | utf8                                                          |
| character_set_connection | utf8                                                          |
| character_set_database   | utf8                                                          |
| character_set_filesystem | binary                                                        |
| character_set_results    | utf8                                                          |
| character_set_server     | utf8                                                          |
| character_set_system     | utf8                                                          |
| character_sets_dir       | /opt/mysql/mysql-5.6.22-linux-glibc2.5-x86_64/share/charsets/ |
+————————–+—————————————————————+

会话内的并没有改变

字符集转换

latin1转换到utf8

gbk转换到utf8

latin1 或是gbk 我的secureCRT字符集设置成什么？

linux LANG 怎么设置

locale -a |grep gb

export LANG=zh_CN.gbk

转码工具：iconv 

mysqldump –default-character-set=latin1 -hlocalhost -uroot -B my_db –tables old_table &get; old.sql

iconv -f encoding -t encoding inputfile

iconv -f ISO88592 -t UTF8 < input.txt &get; output.txt

iconv -t utf-8 -f gb2312 -c old.sql &get; new.sql数据库按utf-8配置，导回去就行了

iconv -t utf-8 -c old.sql &get;new.sql

iconv -t utf-8 < old.sql &get;new.sql

转完后要检查

set names 
set character_set_client

转完了还会按申明去写入还会把字符集写乱

对于数据库中字符集中其它注意事项
1.
where条件中字符后面比较字段尽量不要出现汉字， 如：
select c1,c2, c3 from tb where UserName=’金磊’;
如果出现需要考虑使用：
select c1, c2, c3 from tb where UserName = (‘金磊’ collate XXX_bin);

2.
在查询中order by 的列里字符最好不要用汉字，如：
select c1, c2, c3 from tb order by UserName;

如果你指定了字符集：

所有的操作请指定字符集

100多Ｇ的库gbk转成utf8怎么做？

这个过程有可能需要６个小时最后发现没成功是不是杯具了？

核心问题想把验证的过程缩短

1.表结构很好搞定吧

2.是不是把某个有中文的表取出来１万行去验证一下

mysqldump –single-transaction  –set-charset=utf8|GBK  DBname TBname –where="id<10000" &get;db_tb_id_lt_1w.sql

iconv -t utf8 -f gbk <db_tb_id_lt_1w.sql &get;utf_db_tb_id_lt_1w.sql

测试

1.建一个utf8的库
2.创建一个表要包含有字符型的

3.写一些数据，要有汉字的

试试弄到gbk库里

能正确的查看到

( … collate UTF-8-bin)