06.19 MySQL数据库主从复制服务及优化（高级）

第一章 MySQL日志分类

1. 错误日志
MySQL启动以及运行过程中的重大错误。

cat /etc/my.cnf:[mysqld_safe]log-error = /application/mysql/logs/oldboy.err

2. 普通查询日志
##客户端连接数据库执行语句时产生该日志，默认情况关闭的

mysql> show variables like '%general%';+------------------+--------------------------------------+| Variable_name    | Value                                |+------------------+--------------------------------------+| general_log      | ON                                   || general_log_file | /application/mysql/data/mysql_52.log |+------------------+--------------------------------------+set global general_log = ON;set global general_log_file = /application/mysql/data/db02.log;

3. 二进制日志

【1】二进制日志作用第一个是记录MySQL数据的增量数据，用来做增量数据恢复，没有二进制日志功能，MySQL的备份将无法完整还原数据。【2】第二个是实现主从复制功能。数据库重启会自动刷新binlog为新文件。设置参数自动删除binlog设置参数自动删除binlog是每个管理员都应该做的，参数设置示例如下。假设参数为：expire_logs_days = 7  #<==删除7天前的日志purge binary logs to 'oldboy-bin.000002';PURGE MASTER LOGS BEFORE '2017-06-21 12:08';reset master;人为配置切割及调整。【3】二进制日志索引文件除了很多按序列生成的binlog文件列表外，还有一个索引文件，例如下文里的oldboy-bin.index。【4】设置参数自动删除binlog设置参数自动删除binlog是每个管理员都应该做的，参数设置示例如下。expire_logs_days = ##<==删除7天前的日志  在my.cnfpurge binary logs to 'oldboy-bin.000002';【5】binlog缓存参数show variables like 'binlog_cache_size';show variables like '%max_binlog_size%';show variables like 'sync_binlog';  ##<==改为1，保存数据不丢失【6】语句模式优点：以SQL语句形式记录，数据怎么执行SQL，它就怎么记录SQL。语句模式下仅记录一条：update test set name=oldboy；binlog数据量小，占用空间小，IO性能高，拷贝快。缺点：记录的内容比较粗放，有可能丢东西。例如：函数 存储 过程触发器功能在主从复制的时候有可能复制不一致。【7】行模式基于每一行的修改记录的。update test set name=oldboy；影响100万行。binlog里就差不多记录100万条语句。binlog数据量大，占用空间大，IO性能低，拷贝慢。记录详细，函数 存储 过程触发器功能在主从复制的时候也会数据一致。【8】混合模式结合行模式和SQL语句模式的优点.混合模式默认采用语句模式记录日志当函数 存储 过程触发器功能的时候,自动改为行模式.混合模式默认采用语句模式记录日志，会在一些特定情况下会将记录模式切换为行级模式记录，这些特殊情况包含不限于有：当函数中包含UUID（）时。当表中有自增列（AUTO_INCREMENT）列被更新时。当执行触发器（tigger）或者（stored function）等特殊功能时。当FOUND_ROWS()、ROW_COUNT()、USE（）、CURRENT_USER()、CURRENT_USER等执行时。【9】修改二进制日志模式mysql> SET GLOBAL binlog_format = 'STATEMENT';mysql> SET GLOBAL binlog_format = 'ROW';mysql> SET GLOBAL binlog_format = 'MIXED';

4. 慢查询日志

慢查询参数 解释说明 slow_query_log 慢查询开启开关，默认值是OFF。 slow_query_log_file 记录慢查询语句的文件，文件名“主机名-show.log” long_query_time 记录大于指定N秒的SQL语句，默认是10秒，也可以使用微秒单位 log_queries_not_using_indexes 记录没有使用到索引的SQL语句，默认值是OFF min_examined_row_limit 记录结果集大于N行的SQL语句，默认是0行。 log_slow_admin_statements 记录管理的慢SQL语句。例如alter table 、analyze table、check teble、create index、drop index、optimize table、repair table log_throttle_queries_not_using_indexes 限制每分钟写入记录的慢SQL语句的数量，默认值为0，表示没限制。

#慢查询日志重要参数配置show-query-log = ON                   #<==慢查询开启开关long_query_time = 2                   #<==记录大于2秒的SQL语句log_queries_not_using_indexes = ON    #<==没有使用到索引的SQL语句slow-query-log-file = /application.mysql/slow.log  #<==记录SQL语句的文件min_examined_row_limit = 800          #<==记录结果集大于800行的SQL语句slow-query-log = ONlong_query_time = 2log_queries_not_using_indexes = ONmin_examined_row_limit = 800slow-query-log-file = /application/mysql/logs/slow.log1、MySQL的日志种类有哪些？2、MySQL普通查询日志的特点和配置方法？3、MySQL二进制日志的作用特点与配置？4、如何正确删除MySQL二进制日志？5、MySQL二进制日志的三种模式及特点？6、企业中如何选择MySQL二进制日志模式？7、MySQL二进制日志的模式配置调整？8、如何记录及分析MySQL慢查询日志？

第二章 MySQL数据库字符集

1. 什么是字符集
大家都知道，计算机只能识别0和1这样的二进制数据，无论是处理计算机程序，还是进行科学运算，最终都要转换为二进制数据来完成操作；例如，我们输入一个数字“8”，就会被计算机识别成二进制数字“1000”。

但是，计算机要处理的数据不仅仅是数字，还会有字母，这个时候，就有了ASCII妈系统。因为字母共有26中变化，算上大小写才52种变化，即使加上特殊的英文标点符号、特殊字符、变化也不多，因此用8位二进制数可以表达256种字符，也就是说，这就足够胜任处理英文字符的工作了。

但是，各个国家的语言文字不同，不仅仅是数字、字母以及特殊字符。例如中国的汉子数量就有数万之多，常用的有几千个。这时使用ASCII编码就无法满足需求，于是就有了GBK、BIG5、GB2312这类的字符编码，采用16位禁止数可以表达65535个汉字，这对于常用的汉字使用来说就足够用了。

现在，简体中文环境下，常用的编码除了GB2312和GB18030之外，还会用到UTF-8，GBK是专用做中文的字符编码规范，UTF是通用转换格式的缩写，又称为万国码，理论上来说可以表达各种文字的编码格式。
那么我们现在就应该明白了，字符编码其实就是将人类使用的汉字、英文字母、特殊符号等信息，通过预先设定的转换规则，将其转换为计算机可以识别的二进制数字的一种编码方式。
MySQL数据库的字符集不仅包括字符集（character），还包括校对规则（collation）。其中，校对规则的作用是定义比较字符串的方式。

假设我们希望比较两个字符串的值，A和B，最简单的方法是查找编码：A为0，B为1.因为0小于1，所以可以说A小于Ｂ。我们做的仅仅是在我们的字符集上应用了一个校对规则。可见，校对规则是一套规则，作用是对编码进行比较。

2. MySQL如何选择字符集
如果存储的各种各样的语言文字，可选 UTF-8，这是目前国内应用最为广泛的字符集，没有之一。

如只需支持中文，并且数据量很大，此外，还有大量运算，可选择GBK，理论上可以获得更高的性能，但不推荐使用了。

对于新型的互联网以及移动互联网的混合业务，推荐使用UTF8mb4字符集代UTF8字符集。总之，如果没有极特别的需求，请选择UTF8或utf8mb4作为数据库的字符集。
 
如果使用开源程序可以根据上述说明选择， 如果是公司开发人员开发产品的话，选择权就在开发人员手里，DBA
只能建议。

3. MySQL字符集配置

4. 操作系统级别

[root@db02 logs]# source /etc/sysconfig/i18n[root@db02 logs]# [root@db02 logs]# echo $LANGzh_CN.UTF-8

5. 操作系统客户端级别ssh

6. MySQL实例级别

方法1：前文在编译安装MySQL的时候就曾指定过如下服务器端字符集。cmake . -DDEFAULT_CHARSET=utf8 \-DDEFAULT_COLLATION=utf8_general_ci \-DWITH_EXTRA_CHARSETS=all \方法2：[mysqld]character-set-server=utf8

7. 数据中的库级别

CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */create database oldboy DEFAULT CHARACTER SET UTF8 DEFAULT COLLATE = utf8_general_ci;help create database;show character set;

8. 表级别（含字段级别）

CREATE TABLE `test` (  `id` int(4) NOT NULL AUTO_INCREMENT,  `name` char(20) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

9. MySQL客户端级别

方法1：临时生效单条命令法。mysql> set names utf8;Query OK, 0 rows affected (0.00 sec)方法2：通过修改my.cnf实现修改mysql客户端的字符集，配置方法如下。[client]default-character-set=utf8

10. 程序代码级别

alter database oldboy CHARACTER SET utf8 collate utf8_general_ci;alter table t1 CHARACTER SET latin1;

11. 生产环境中更改数据库（含数据）字符集的方法
对于已经有数据的库表，要对字符集进行调整，就需要先将数据导出，然后更改数据库环境，更改建库和表的字符集后，重新导入数据，这样才能实现相应的调整。
整个过程如下：
1) 确保数据库不要更新，然后导出所有数据为SQL文件。
2) 针对导出的数据进行字符集替换（替换表和库），例如把gbk改为utf8
3) 修改my.cnf配置文件，更改MySQL客户端即服务端字符集，重启生效。
4) 导入更改过新字符集的库表的数据，包括表结构语句，然后提供服务。
5) 更改操作系统，SSH客户端，以及程序为对应新字符集。
另外，更改字符集时，要从小的字符集集合更改为大的字符集集合，不然可能丢失数据。

第三章 MySQL5.6支持的引擎

1. 如何设置引擎

CREATE TABLE `student` (  `Sno` int(10) NOT NULL COMMENT '学号',  `Sname` varchar(16) NOT NULL COMMENT '姓名',  `Ssex` char(2) NOT NULL COMMENT '性别',  `Sage` varchar(16) default NULL,  `Sdept` varchar(16) default NULL COMMENT '学生所在系别',  KEY `ind_sage` (`Sage`),  KEY `ind_sno` (`Sno`)) ENGINE=MyISAM DEFAULT CHARSET=utf8

2. 或alter修改

方法1：利用SQL命令语句修改引擎，具体命令如下。ALTER TABLE oldboy ENGINE = INNODB;ALTER TABLE oldboy ENGINE = MyISAM;

3. MyISAM引擎

MyISAM引擎在MySQL 5.5.5以前的版本中，MyISAM引擎是MySQL关系数据库管理系统的默认储存引擎。columns_priv.frm #<==存放表的定义columns_priv.MYD #<==存放表的数据columns_priv.MYI #<==存放表的索引mysql> use mysqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show create table columns_priv\G*************************** 1. row ***************************       Table: columns_privCreate Table: CREATE TABLE `columns_priv` (  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',  `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges'1 row in set (0.00 sec)

4. MyISAM引擎的存储方式
每一个MyISAM引擎的表都对应与硬盘上的三个文件。这是哪个文件有一样的文件名，但是有不同的扩展名，用于只是其类型和用途，比如.frm文件用于保存表的定义，这个文件并不是MyISAM引擎的一部分，而是服务器的一部分；.myd用于保存表的数据； .myi是表的索引文件，.myd和.myi是MyISAM的关键点。

5. MyISAM引擎特点

6. MyISAM引擎适用生产业务场景
MyISAM引擎可以使用的生产业务场景。
1) 不需要事务支持并且对数据一致性要求不高的业务（例如转账就不行）
2) 读请求多的应用，读写都频繁的场景则不适合。
3) 读写并发访问相对较低的业务。
4) 数据修改响度比较少的业务
5) 硬件资源比较差的服务器
6) 使用读写分离的MySQL从库可以使用MyISAM
要强调的是，当下99%的企业业务场景，都不许需要MyISAM了，而是选择更有优势的innoDB。

7. InnoDB引擎

mysql> show create table test\G*************************** 1. row ***************************       Table: testCreate Table: CREATE TABLE `test` (  `id` int(4) NOT NULL AUTO_INCREMENT,  `name` char(20) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf81 row in set (0.00 sec)

8. InnoDB引擎的存储方式

9. InnoDB引擎表空间结构说明

概念名称 说明 表空间（Tablespaces） 表空间是一个逻辑的概念，表空间里存放的是表的数据和索引，这些表的数据和索引又有不同的存储方式，表空间最终体现的是磁盘上数据库的各种物理数据文件。 独立表空间（Independent Tablespaces） 在开启innodb的innodb_file_per_table = on这个参数（5.6以后默认开启）之后，每一个新建的innodb表，数据库目录下都会多出来一个对应的存放该表数据的.ibd文件 共享表空间（Shared Tablespaces） 5.6版本以前MySQL的默认配置就是共享表空间模式，即所有的表的数据都会放在一个或几个大数据文件中存放。 页（Pages） MySQL的每个表空间都是由若干个页（pages）组成，且每个实例里的每个表空间内都有相同的页（pages）大小，默认值是16KB，可以通过innodb_pages_size调整页大小，每个页（page）中包含了表的数据。组成表空间数据的最小单位是页（page）。 区段（Extents） 在表空间中，系统会吧每个若干个页（pages）进行分组管理，这个组就叫做区段（extents），默认是一个区段（Extents）是64个页大小。 段（segments） 段（segments）是由不同的多个区段（Extents）组成的更大的分组。当一个段（segments）增加的时候，innodb第一次分配32个页（pages）给这个段，此后，innodb开始分配整个区段（extents）给这个段，innodb可以一次添加4个区段（extents）给一个大的段（segments）从而确保数据存储时能有一个良好的顺序性。

mysql> show variables like "innodb_file_per_table";+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| innodb_file_per_table | ON    |+-----------------------+-------+1 row in set (0.02 sec)

10. InnoDB引擎内部知识结构图
简单的说，innodb表空间分为共享表空间和独立表空间（推荐）两种，表空间里存放数据的最小单位是页（page），每个页（page）默认值为16KB；多个连续的页（page）（默认是64个）组成一个区段（Extents）；而多个区段（Extents）和页（pages）构成一个端（segments）。初始时，innodb首先为每个段（segments）分配32个页（pages）之后根据实际需要再将区段分配给段，innodb可以一次添加4个区给一个大的段，从而确保数据存储时能有一个良好的顺序性。

11. InnoDB引擎日志分类

相关名词 说明 Redo日志 Redo日志，也成事务日志，是innodb引擎的重要组成部分，作用是记录innodb引擎中每一个数据发生的变化信息。主要用于保证innodb数据的完整性，以及丢失数据后的恢复，同时可以有效提升数据库的IO等性能。Redo日志对应的配置参数为innodb_log_file_size和innodb_log_file_in_group Undo日志 Undo日志是记录事务的逆向逻辑操作或者逆向操作或者逆向物理操作对应的数据变化的内容，Undo日志默认存放在共享表空间里面的（ibdata*文件），和Redo日志功能不同Undo日志主要用于回滚数据库崩溃前未完整提交的事务数据，确保数据恢复前后一致。 LSN LSN全拼log Sequence Number，中文是日志序列号，是一个64位的整型数字，LSN的作用是记录Redo日志，使用LSN唯一标识一条变化的数据 Checkpoint 用来表示数据崩溃后，应恢复的redo log的起始点。

12. InnoDB引擎特点

13. InnoDB引擎适用生产业务场景

14. InnoDB引擎重要参数

15. InnoDB引擎调优基本方法
1) 主键尽可能小，避免给Secondary index带来过的大的空间负担。
2) 建立有效索引，避免全表扫描，因为会使用表锁。
3) 尽可能缓存所有的索引和数据，提高响应速度，减少磁盘IO消耗。
4) 在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交。有开关可以控制提交方式。
5) 合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性
6) 避免主键更新，因为这会带来大量的数据移动。

以InnoDB引擎数据库为例：备份数据库中所有库的所有数据的命令为：mysqldump -B --master-data=2 --single-transaction -A |gzip >/opt/all.sql.gz以InnoDB引擎数据库为例：备份数据库中所有库的所有数据的命令为：mysqldump -B --master-data=2 -A |gzip >/opt/all.sql.gzmysqldump -B --master-data=2 -x -A |gzip >/opt/all.sql.gz

第五章 MySQL主从复制

1. MySQL主从复制介绍
MySQL数据库的主从复制方案，和使用scp|rsync等命令进行的文件级别复制类似，都是数据的远程传输，只不过MySQL的主从复制是其自带的功能，无需借助第三方工具，而且，MySQL的主从复制并不是数据库磁盘上的文件直接拷贝，而是通过逻辑的binlog日志复制到要同步的服务器本地，然后由本地的线程读取日志里面的SQL语句重新应用到MySQL数据库中。

2. MySQL主从复制架构图

3. 应用场景1：从服务器作为主服务器的实时数据备份

4. 应用场景2：主从实现读写分离，从服务器实现负载均衡
主从服务器架构可通过程序（PHP、Java等）或代理软件（mysql-proxy、Amoeba）实现对用户（客户端）的请求读写分离，即让从服务器仅仅处理用户的select查询请求，降低用户查询相应时间及读写同时在主服务器上带来的访问压力。对于更新的数据（例如update、insert、delete语句）仍然交给主服务器处理，确保主服务器和从服务器保持实时同步。

5. 应用场景3：把多个从服务器根据业务重要性进行拆分访问
可以把几个不同的从服务器，根据公司的业务进行拆分。例如：有为外部用户提供查询服务的从服务器，有内部DBA用来数据备份的从服务器，还有为公司内部人员提供访问的后台、脚本、日志分析及供开发人员查询使用的从服务器。这样的拆分除了减轻主服务器的压力外，还可以使数据库对外部用户浏览、内部用户业务处理及DBA人员的备份等互不影响。

6. MySQL主从复制原理重点小结
1) 主从复制是异步逻辑的SQL语句级的复制
2) 复制时，主库有一个IO线程，从库有两个线程IO线程和SQL线程
3) 实现主从复制的必要条件是主库要开启binlog功能
4) 作为复制的所有MySQL节点的server-id都不能相同
5) binlog文件只记录对数据库有更改的SQL语句，不记录任何查询语句，对数据库不做变更的，例如select show。

7. MySQL主从复制实践

【1】停止单实例[root@mysql_52 ~]# /etc/init.d/mysqld stop【2】开启主库binlog和server IDvim /data/3306/my.cnf[root@mysql_52 3306]# egrep -i "log-bin|server-id" my.cnf log-bin = /data/3306/mysql-binserver-id = 6【3】重启3306/data/3306/mysql stop/data/3306/mysql start【4】从库检查serverID[root@db02 ~]# egrep -i "server-id|log-bin" /data/3307/my.cnf#log-bin = /data/3307/mysql-binserver-id = 7【5】主库创建同步用户grant replication  slave on *.* to 'rep'@'172.16.1.%' identified by 'oldboy123';select user,host from mysql.user;【6】锁表flush table with read lock;show master status;【7】导出数据全备mysqldump -A -B --master-data=2 --single-transaction -S /data/3306/mysql.sock |gzip>/data/backup/all_$(date +%F).sql.gzll /data/backup/all_$(date +%F).sql.gz-rw-r--r-- 1 root root 178499 Jun 28 13:35 /data/backup/all_2017-06-28.sql.gz【8】解锁unlock table;【9】从库导入数据gzip -d all_2017-06-28.sql.gz mysql -S /data/3307/mysql.sock <all_2017-06-28.sql[root@db02 backup]# sed -n '22p' all_2017-06-28.sql-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120;【10】登录从库CHANGE MASTER TO  MASTER_HOST='172.16.1.52', MASTER_PORT=3306,MASTER_USER='rep', MASTER_PASSWORD='oldboy123', MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=799;【11】开启复制开关start slave;【12】查看同步状态[root@db02 backup]# mysql -S /data/3307/mysql.sock -e "show slave status\G"|egrep "_Running|Behind_Master"|head -3               Slave_IO_Running: Yes            Slave_SQL_Running: Yes        Seconds_Behind_Master: 0

第六章 MySQL主从复制常见问题

1. MySQL从库开启binlog日志
从库需要记录binlog的应用场景为：当前的从库还要作为其他从库的主库，例如：级联复制或双主互为主从场景的情况下。从库记录binlog日志的方法。
在从库的my.cnf中加入如下参数，然后重启服务生效即可。

log-slave-updates ##必须要有这个参数。log-bin = /data/3307/mysql-binexpire_logs_days = 7 ##相当于find /data/3307/ -type f -name "mysql-bin.000*" -mtime +7 |xargs rm -f 

2. 问题一：一个主库的从库太多，导致复制延迟
建议从库数量3~5个为宜，要复制的从节点数量过多，会导致复制延迟。

3. 问题二：从库硬件比主库差，导致复制延迟
查看master和slave的系统配置，可能会因为机器配置的问题，包括磁盘IO、CPU、内存等各方面因素造成复制的延迟，一般发生在高并发大数据写入场景。

4. 问题三：慢SQL语句过多
假如一条SQL语句，执行时间是20秒，那么从执行完毕，到从库上能查到数据也至少是20秒，这样就延迟了20秒了。SQL语句的优化一般要作为常规工作不断的监控和优化，如果是单个SQL的写入时间长，可以修改后分多次写入。通过查看慢查询日志或show full processlist命令找出执行时间长的查询语句或者大的事务。

5. 问题四：主从复制的设计问题
例如，主从复制单线程，如果主库写并发太大，来不及传送到从库就会导致延迟。更高版本的MySQL可以支持多线程复制，门户网站则会自己开发多线程同步功能。

6. 问题五：主从库之间的网络延迟
主从库的网卡、网线、连接的交换机等网络设备都可能成为复制的瓶颈，导致复制延迟，另外，跨公网主从复制很容易导致主从复制延迟。

7. 问题六：主库读写压力大，导致复制延迟
主库硬件要搞好一点，架构的前端要加buffer以及缓存层。

8. 通过read-only参数让从库只读访问
read-only参数选项可以让服务器只允许来自从服务器线程或具有SUPER权限的数据库用户进行更新。可以确保从服务器不接受来自用户端的非法用户更新。

read-only参数允许数据库更新的条件为：
1) 具有SUPER权限的用户可以更新，不受read-only参数影响，例如:管理员root。
2) 来自从服务器线程可以更新，不受read-only参数影响，例如：前文的rep用户。在生产环境中，可以在从库slave中使用read-only参数，确保从库数据不被非法更新。

read-only参数配置的方法如下：

kaill mysqld 或者mysqladmin -S /data/3307/mysql.sock shutdownmysqld_safe --defaults-file=/data/3307/my.cnf --read-only &方法二：在my.cnf里【mysqld】模块下加read-only参数，然后重启数据库配置如下:[mysqld]read-only

第七章 MySQL实现主从读写分离方案

1. 通过程序实现读写分离（性能和效率最佳）
PHP和Java程序都可以通过设置多个连接文件轻松地实现对数据库的读写分离，即当语句关键字为select时，就是连接读库的连接文件，若为update、insert、delete时，则连接写库的连接文件。
通过程序实现读写分离的缺点就是需要开发人员对程序进行改造，使其对下层不透明，但这种方式更容易开发和实现，适合互联网业务场景。

2. 通过开源的软件实现读写分离
Maxcale、Atlas、Mycat等代理软件也可以实现读写分离功能，这些软件的稳定性和功能一般，不建议生产使用。绝大多数公司常用的还是在应用端开发程序实现读写分离。

3. 大型门户独立开发DAL层综合软件
百度、阿里等大型门户都有开发牛人，会花大力气开发适合自己业务的读写分离、负载均衡、监控报警、自动扩容、自动收缩等一系列功能的DAL层软件。

4. web用户授权问题解决方案

主库上对web用户授权如下：用户：web 密码：oldboy123  端口：3306  主库VIP：10.0.0.7权限：SELECT, INSERT, UPDATE, DELETE命令：GRANT SELECT, INSERT, UPDATE, DELETE ON `web`.* TO 'web'@'10.0.0.%' identified by 'oldboy123';从库上对web用户授权如下：用户：web 密码：oldboy123  端口：3306  从库VIP：10.0.0.8权限：SELECT主从复制授权方法:1.mysql库不复制.replicate-ignore-db=mysqlbinlog-ignore-db=mysql2.从库回收INSERT, UPDATE, DELETE(revoke)3.read-only

5. 为什么用MySQL双主复制
(一) 用户期待解决写并发，但双主又不太会增加写法。

(二) 如果要增加写并发。
分库：www/bbs/blog，每个库一套几圈
分表：横拆，总拆
自动化扩容（百度前台数据库集群），自动收缩。

(三) 虽是双主但要单写，目的实现主库宕机角色快速切换
正常主宕机切换到从，需要开启binlog，取消read-only，更改用户授权等操作。
而双主就不需要这些了，可以直接就是VIP的切换。

6. 主主复制常见两种方法：
通过MySQL参数配置使表的主键自增，通过增加auto_increment。
M1库：每张表主键都写单数，例如1,3,5，…..
M2库：每张表主键都写偶数，例如2,4,6,……
优点：前端网站程序不需要做任何修改。
应用：尽量不要双写，而是单写，作用可以作为主库宕机的备用切换选择（不用改配置即可做角色切换）

#________m-m m1________auto_increment_increment    = 2auto_increment_offset       = 1log-slave-updateslog-bin = mysql-binexpire_logs_days = 7#________m-m m1 end________binlog-ignore-db=information_schemabinlog-ignore-db=mysql===========m-m m2 ================auto_increment_increment    = 2auto_increment_offset       = 2log-slave-updateslog-bin = mysql-binexpire_logs_days = 7binlog-ignore-db=information_schemabinlog-ignore-db=mysql===================

第二种双主：
使用序列（sequence）服务器，由序列服务器提供ID，程序写数据库时，请求序列服务器分配ID，按照分发的ID号顺序写入。银行排号机器
优点：ID号连续
缺点：增加了序列服务器，引入了单点，同时，程序要改很多。
总的建议，不要用双写模式，不是很可靠。

第八章 MySQL数据库的优化

1. 硬件层面优化
数据库物理机采购：
a) cpu运算：64位cpu，一台机器2-16颗cpu，至少2-4颗。L2越大越好
b) 内存：96G-256G。3-4个实例，32G-64G，1-2个实例
c) disk磁盘IO：机械盘ssd>sas>sata
raid阵列卡至少4块盘，0>10(推荐)>5>1
案例：
百度某部分IBM服务器为48核CPU，内存96G，一台服务器跑3-4个实例
sina服务器dell R510居多，CPU是E5210，48G内存，磁盘12*300G SAS，组RAID10

2. 操作系统层面优化
1) 一定要选择x86_64系统，推荐使用CentOS6.8 Linux，关闭NUMA特性。
2) 将操作系统分区和数据分区分开，不仅仅是逻辑上，还包括物理上。
3) 避免使用swap交换分区
4) 避免使用软件磁盘阵列（软raid）
5) 避免使用LVM逻辑卷（可逻辑扩容不好）
6) 删除服务器上未使用的安装包和守护进程。

3. Linux内核参数优化

#减少TIME_WAIT,提高TCP效率net.ipv4.tcp_tw_recycle = 1net.ipv4.tcp_tw_reuse = 1#减少处于FIN-WAIT-2连接状态的时间，使系统可以处理更多的连接。net.ipv4.tcp_fin_timeout = 2#减少TCP keepalive连接侦测的时间，使系统可以处理更多的连接。net.ipv4.tcp_keepalive_time = 600#提高系统支持的最大SYN半连接数（默认1024）net.ipv4.tcp_max_syn_backlog = 16384#减少系统SYN连接重试次数（默认5）net.ipv4.tcp_synack_retries = 1net.ipv4.tcp_syn_retries = 1#在内核放弃建立连接之前发送SYN包的数量#允许系统打开的端口范围net.ipv4.ip_local_port_range = 4000    65000#优化系统套接字缓冲区net.core.rmem_max=16777216   #最大socket读buffernet.core.wmem_max=15777215   #最大socket写buffernet.core.wmem_default = 8388608 #该文件指定了接受套接字缓冲区大小的缺省值（以字节为单位）net.core.rmem_default =8388608#优化TCP接受/发送缓冲区net.ipv4.tcp_rmem=4096 87380 16777216net.ipv4.tcp_wmem=4096 87380 16777216net.ipv4.tcp_mem = 94500000 915000000 927000000#优化网络设备接收队列net.core.netdev_max_backlog=3000net.core.somaxconn = 32768#其它优化net.ipv4.tcp_timestamps = 0net.ipv4.tcp_max_orphans = 3276800net.ipv4.tcp max tw buckets = 360000

4. MySQL数据库层面优化

[mysql]no-auto-rehash[mysqld]user    = mysqlport    = 3307socket  = /data/3307/mysql.sockbasedir = /application/mysqldatadir = /data/3307/dataopen_files_limit    = 1024back_log = 600max_connections = 800max_connect_errors = 3000table_open_cache = 614external-locking = FALSEmax_allowed_packet =8Msort_buffer_size = 1Mjoin_buffer_size = 1Mthread_cache_size = 100thread_concurrency = 2query_cache_size = 2Mquery_cache_limit = 1Mquery_cache_min_res_unit = 2k#default_table_type = InnoDBthread_stack = 192K#transaction_isolation = READ-COMMITTEDtmp_table_size = 2Mmax_heap_table_size = 2M#long_query_time = 1#log_long_format#log-error = /data/3307/error.log#log-slow-queries = /data/3307/slow.logpid-file = /data/3307/mysql.pidlog-bin = /data/3307/mysql-binlog-slave-updatesrelay-log = /data/3307/relay-binrelay-log-info-file = /data/3307/relay-log.infobinlog_cache_size = 1Mmax_binlog_cache_size = 1Mmax_binlog_size = 2Mexpire_logs_days = 7key_buffer_size = 16Mread_buffer_size = 1Mread_rnd_buffer_size = 1Mbulk_insert_buffer_size = 1Mlower_case_table_names = 1skip-name-resolveslave-skip-errors = 1032,1062replicate-ignore-db=mysqlserver-id = 7innodb_additional_mem_pool_size = 4Minnodb_buffer_pool_size = 32Minnodb_data_file_path = ibdata1:128M:autoextendinnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodb_log_file_size = 4Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120innodb_file_per_table = 0[mysqldump]quickmax_allowed_packet = 2M[mysqld_safe]log-error=/data/3307/oldboy_3307.errpid-file=/data/3307/mysqld.pid

#自动清理binlog> show binary logs;> show variables like '%log%';> set global expire_logs_days = 10;

参数优化 http://www.xuliangwei.com/xubusi/213.html
关于库表的设计规范
1) 推荐utf8字符集，移动互联网utf8mb4
2) 固定字符串的列尽可能多用订场char，少用varchar。
存储可变长度的字符串时使用varchar而不是char节省空间，因为固定长度的char，而varchar长度不固定
3) 所有的innodb表都设计一个无业务用途的自增列作为主键；
4) 字段长度满足需求前提下，尽可能选择长度小的
5) 字段属性尽量都加上not null约束；
6) 尽可能不适用text类型。确实需要建议拆分到子表中
7) 读取数据时，只选取所需要的列，不要每次都select * ，避免产生严重的随机读问题；
8) 对一个varchar创建索引时，通长取其50%（甚至更小）左右长度创建前缀索引就足以满足80%以上的查询需求了，没必要创建整列的全长度索引；
9) 多用复合索引，少用多个独立索引，尤其是一些基数太小的列就不要创建独立索引。

问题1、既然索引可以加快查询速度，那么就给所有的列建索引吧？
解答：因为索引不但占用存储空间，而且更新数据时还需要维护索引数据的，因此，索引时一把双刃剑，并不是越多越好，例如：数十到白航的小表上无需建立索引，插入更新频繁，读取比较少的表也不需要建立索引。

问题2、需要在哪些列上创建索引才能加快查询速度呢？
select user,host from mysql.user where password=..;索引一定要创建在where后的条件列上，而不是select后的选择数据的列上。另外，我们要尽量选择在唯一值多的大表上的列建立索引，例如，男女性别列唯一值少， 不适合建立索引。
查看表的唯一值数量：

select count(distinct user)from mysql.user;

1.主键索引CREATE TABLE `test` (  `age` tinyint(2) DEFAULT NULL,  `id` int(4) NOT NULL AUTO_INCREMENT,  `name` char(20) NOT NULL,  `sex` char(4) DEFAULT NULL,  `qq` varchar(15) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

建表语句加入联合索引：某sns产品生产正式建表语句use sns;set names gbk;CREATE TABLE `subject_comment_manager` (  `subject_comment_manager_id` bigint(12) NOT NULL auto_increment COMMENT '主键',  `subject_type` tinyint(2) NOT NULL COMMENT '素材类型',  `subject_primary_key` varchar(255) NOT NULL COMMENT '素材的主键',  `subject_title` varchar(255) NOT NULL COMMENT '素材的名称',  `edit_user_nick` varchar(64) default NULL COMMENT '修改人',  `edit_user_time` timestamp NULL default NULL COMMENT '修改时间',  `edit_comment` varchar(255) default NULL COMMENT '修改的理由',  `state` tinyint(1) NOT NULL default '1' COMMENT '0代表关闭，1代表正常',  PRIMARY KEY  (`subject_comment_manager_id`),  KEY `IDX_PRIMARYKEY` (`subject_primary_key`(32)),   KEY `IDX_SUBJECT_TITLE` (`subject_title`(32))  KEY `index_nick_type` (`edit_user_nick`(32),`subject_type`)#<==联合索引，此行为新加的，用于给大家讲解的。实际表语句内没有此行。) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

show create table mysql.user\GPRIMARY KEY (`Host`,`User`) #<==特殊的联合索引，主键联合索引。

在哪个列上创建索引比较好？解答：唯一值多的大表的用于查询的条件列上创建索引。记录数:1000行+select count(*) from mysql.user;查唯一值:select count(distinct user) from mysql.user;select count(distinct user,host) from mysql.user;

1、创建索引相关命令集合创建主键索引：alter table student change id id int primary key auto_increment; 删除主键索引（主键列不能自增）：alter table student drop primary key;创建普通索引：alter table student add index index_dept(dept);根据列的前n个字符创建普通索引create index index_dept on student(dept(8));根据多个列创建联合普通索引create index ind_name_dept on student(name,dept);根据多个列的前n个字符创建联合普通索引create index ind_name_dept on student(name(8),dept(10));创建唯一索引create unique index uni_ind_name on student(name);查看索引desc student;show index from student;删除普通索引与唯一索引：alter table student drop index index_dept;drop index index_dept on student;查看表记录唯一值的数量：select count(distinct user) from mysql.user;select count(distinct user,host) from mysql.user;创建索引的基本知识小结：1、索引类似书籍的目录，会加快查询数据的速度。2、要在表的列（字段）上创建索引。3、索引会加快查询速度，但是也会影响更新的速度，因为更新要维护索引数据。4、索引列并不是越多越好，要在频繁查询的表语句where后的条件列上创建索引。5、小表或重复值很多的列上可以不建索引，要在大表以及重复值少的条件列上创建索引。6、多个列联合索引有前缀生效特性。7、当字段内容前N个字符已经接近唯一时，可以对字段的前N个字符创建索引。8、索引从工作方式区分，有主键、唯一、普通索引。9、索引类型会有BTREE（默认）和hash（适合做缓存（内存数据库））等。http://blog.csdn.net/dyllove98/article/details/9631303 2、有关alter和create命令创建索引的帮助

5. 网站集群架构上的优化
1) 服务器上跑多示例2-4个
2) 主从复制一主无从，采用mixed或row模式，尽量不要跨机房同步（尽量远程写本地读）
3) 定期使用pt-table-checksum、pt-table-sync来检查并修复MySQL主从复制的数据差异；
4) 业务拆分：搜索功能，like ‘%阿萨德%’ 一般不要用MySQL数据库。
5) 业务拆分：某些业务应用使用nosql持久化存储，例如redis粉丝关注，好友关系等等
6) 数据库前端必须要加cache。例如memcached/redis，用户登录，商品查询。
7) 动态的数据静态化。整个文件静态化，页面片段静态化。
8) 数据库集群与读写分离，一主多从，通过程序或者dbproxy进行集群读写分离
9) 单表超过800W，拆库拆表，人工拆表拆库（登录、商品、订单）
10) 选择从库进行备份

6. MySQL数据库管理流程，制度优化
7. MySQL数据库安全优化