MySQL复制和性能优化

复制（Replication）：通过复制执行过的语句或者数据集从主服务器上复制到一个或多个从服务器上实现多个数据库服务器之间数据库的同步。

MySQL's built-in replication capability is the foundation for building large, highperformance applications on top of MySQL.

MySQL supports two kinds of replication: statement-based replication(基于语句的复制) and row-based replication（基于行的复制）.

Statement-based (or "logical") replication has been available since MySQL 3.23, and it's what most people are using in production today

Row-based replication is new in MySQL 5.1.

Both kinds work by recording changes in the master's binary log and replaying the log on the slave

基于语句的复制：记录改变数据库的语句，将语句在从服务器上在执行一遍，效率较高。

基于行的复制：将语句执行后的结果包括行的改变或者添加整个复制到从服务器中去。

混合方式的复制：由MySQL自动来判断。

复制过程：

主服务器上线程：mysql dump

从服务器两个线程：I/O thread ,SQL thread。

过程：从服务器的I/O 线程（主服务器的远程客户端）不断尝试连接主服务器，读取其二进制日志，主服务器收到请求后将检查自己的Binary Log并根据从服务器发来的Relay Log的相关信息来确认自从上次复制之后主服务器内容是否有更新，如果有，则主服务器启动mysql dump线程，将对方所请求的数据返回给从服务器，从服务器收到数据后会将数据保存在Relay Log。SQL thread 会不定期的读取Relay Log，如果发现有更新，则读取更新的语句或者行将其保存在从服务器上。

MySQL解决的问题：数据备份、负载均衡、高可用、数据分布（异地容灾）、升级测试。

下面我们来实现基于MySQL主从复制的架构：

Master：192.168.1.11  MySQL已安装完毕

[root@station39 ~]# vim /etc/my.cnf

log-bin=master-bin     //** update  line 50

log-bin-index=master-bin.index  //** add line 51

server-id       = 1           //** line 59

重启服务；

mysql> grant replication slave,replication client on *.* to rep@'192.168.1.%' identified by 'RedHat ';

Query OK, 0 rows affected (0.05 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> show processlist\G;

OK！此时可以看到Binlog Dump线程已经启动，创建数据库，表试试：

mysql> create database mydb;

Query OK, 1 row affected (0.03 sec)

mysql> use mydb

Database changed

mysql> create table t1( id int unsigned not null primary key, name char(30));

Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values(1,'lucy'),(2,'lily');

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;

Slave：192.168.1.13  MySQl已安装完毕

[root@station26 ~]# vim /etc/my.cnf

server-id       = 2    //** line 58

relay-log = slave-relay-bin      //** line  59

relay-log-index = slave-relay-bin.index  //** line 60

重启服务；

mysql> change master to

     > master_host='192.168.1.11',

     >master_port=3306,     

     > master_user='rep',

     > master_password='RedHat ';

mysql> start slave;

mysql> show processlist\G;

OK，I/O thread,SQL thread 已经启动，等待主服务器数据发生更新……

主服务器上创建了mydb数据库和t1表，我们在从服务器中已经可以看到：

从Binary Log某一个点开始复制主服务器内容：

mysql> change master to

     > master_host='192.168.1.11',

     >master_port=3306,     

     > master_user='rep',

     > master_password='RedHat ',

     > master_log_file='master-bin.000001',

     >master_log_pos=698;

PS：如果主服务器运行一段时间后才新建一个从服务器做复制，需要先将主服务器上的数据进行备份，然后将备份数据发给从服务器并在从服务器上还原之后再做复制。

主服务器：

mysql> flush tables with read lock;

mysql> flush logs;

mysql> \q

[root@station39~]# mysqldump -uroot --all-database --master-data=2 >/root/master.sql

[root@station39 ~]# vim /root/master.sql

--CHANGE MASTER TO

MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=106; //**备份内容截止点

mysql> unlock tables;

将数据发送到从服务器上：

[root@station39 ~]# scp /root/master.sql 192.168.1.13:/root

从服务器：

[root@station26 ~]# mysql -uroot

mysql> change master to

     > master_host='192.168.1.11',

     >master_port=3306,     

     > master_user='rep',

     > master_password='RedHat ',

     > master_log_file='master-bin.000003',

     >master_log_pos=106;

日志相关参数：

mysql> show binlog events\G;

Event_type：This is the type of the event.

Server_id：This is the server ID of the server that created the event.

Log_name：The name of the file that stores the event.

Pos：This is the position of the file where the event starts; that is, it’s the first byte of the event.

End_log_pos：This gives the position in the file where the event ends and the next event starts.

Info：This is human-readable text with information about the event.

Filter 过滤功能：

在主服务器上过滤：

binlog-do-db：指定当前主服务器中仅允许客户端复制的数据库

binlog-do-ignore-db：指定当前主服务器哪些数据库的更新不记录在Binary Log中，可能会造成主服务器无法进行时间点恢复的致命错误。

在从服务器上过滤：

replicate-do-db=db：仅复制哪些数据库

If the current database of the statement is db, execute the statement

replicate-ignore-db=db：忽略哪些数据库

If the current database of the statement is db, discard the statement

replicate-do-table=table and replicate-wild-do-table=db_pattern.tbl_pattern

If the name of the table being updated is table or matches the pattern, execute updates to the table 使用通配符指定仅复制哪些表

replicate-ignore-table=table and replicate-wild-ignore-table=db_pattern.tbl_pattern

If the name of the table being updated is table or matches the pattern, discard updates to the table 使用通配符忽略哪些表

主从复制实现SSL加密功能：

主服务器兼做CA：

编辑/etc/hosts文件：

192.168.1.11    master.a.com            master

192.168.1.13    slave.a.com             slave

[root@station39 ~]# vim /etc/my.cnf

ssl  //** mysqld 中添加

重启服务；

开始制作证书：

先将主服务器配置成CA：

[root@station39 CA]# vim ../tls/openssl.cnf

dir             = /etc/pki/CA

[root@station39 CA]# umask 077;openssl genrsa 2048 > private/cakey.pem

[root@station39 CA]# openssl req -x509 -new -key private/cakey.pem -out cacert.pem

[root@station39 CA]# mkdir certs newcerts crl

[root@station39 CA]# touch index.txt serial

[root@station39 CA]# echo 01 >serial

CA已经创建完毕！

[root@station39 CA]# mkdir -pv /etc/mysql/certs

[root@station39 CA]# cd /etc/mysql/certs/

[root@station39 certs]# openssl genrsa 2048 >master_key.pem

[root@station39 certs]# openssl req -new -key master_key.pem -out master.csr

[root@station39 certs]# openssl ca -in master.csr -out master_cert.pem

给从服务器签署证书请求：

[root@station39 certs]# openssl ca -in /tmp/slave.csr -out slave_cert.pem

[root@station39 certs]# scp slave_cert.pem 192.168.1.13:/etc/mysql/certs

[root@station39 certs]# cp /etc/pki/CA/cacert.pem ./

[root@station39 certs]# scp /etc/pki/CA/cacert.pem 192.168.1.13:/etc/mysql/certs/

CA的私钥，主服务的私钥和证书都已准备好了：

开始MySQL的配置：

编辑/etc/my.cnf主配置文件：

ssl-ca = /etc/mysql/certs/cacert.pem

ssl-cert = /etc/mysql/certs/master_cert.pem

ssl-key = /etc/mysql/certs/master_key.pem

保存退出，重启服务；

mysql> grant replication slave,replication client on *.* to rep@'%.a.com' identified by 'RedHat ' require ssl;

mysql> flush privileges;

从服务器：

编辑/etc/hosts文件：

192.168.1.11    master.a.com            master

192.168.1.13    slave.a.com             slave

[root@station26 ~]# vim /etc/my.cnf

ssl  //**mysqld 中添加

重启服务；

[root@station26 ~]# mkdir -pv /etc/mysql/certs

[root@station26 ~]# cd /etc/mysql/certs/

[root@station26 certs]# openssl genrsa 2048 > slave_key.pem

[root@station26 certs]# openssl req -new -key slave_key.pem -out slave.csr

[root@station26 certs]# scp slave.csr 192.168.1.11:/tmp

CA的私钥，从服务的私钥和证书都已准备好了：

mysql> change master to

    -> master_host='master.a.com',

    -> master_user='rep',

    -> master_password='RedHat ',

    -> master_ssl=1,

    -> master_ssl_ca='/etc/mysql/certs/cacert.pem',

    -> master_ssl_cert='/etc/mysql/certs/slave_cert.pem',

    -> master_ssl_key='/etc/mysql/certs/slave_key.pem';

Query OK, 0 rows affected (0.02 sec)

mysql> start slave;

OK,此时主从服务器之间就可以使用SSL进行会话了。

层级复制：

配置一个中继服务器过程：

1 Configure the slave to forward any events executed by the slave thread by writing them to the binlog of the relay slave.

2 Change the storage engine for all tables on the relay slave to use the BLACKHOLE storage engine to preserve space and improve performance

3 Ensure that any new tables added to the relay also use the BLACKHOLE engine

mysql> SET GLOBAL STORAGE_ENGINE = 'BLACKHOLE';

relay> SHOW TABLES FROM mydb;

relay> SET SQL_LOG_BIN = 0;

relay> ALTER TABLE user_data ENGINE = 'BLACKHOLE';

…

…

relay> ALTER TABLE profile ENGINE = 'BLACKHOLE';

relay> SET SQL_BIN_LOG = 1;

MySQL优化

对查询做优化：为表添加正确的索引，并选择适当的索引类型。

对where,order by, group by中经常用到的字段做索引。

根据存储引擎来添加适当的索引：

MyISAM：B-tree，fulltext

InnodB：cluster，

       聚簇索引（索引和数据放在一块），

       非聚簇索引（索引种的指针指向数据在磁盘中的存储位置）

Memory：hash（键值对）

由于支持事务的引擎处理的额外机制比较多，所以MyISAM的性能优于InnodB

衡量索引设计好坏的两个变量：Handler_read_key（越大越好）， Handler_read_rnd_next（越小越好）。

查询缓存：

have_query_cache ：编译时是否启用了缓存功能

query_cache_limit：能够进行缓存的查询结果最大值

query_cache_min_res_unit  

query_cache_size       查询缓存大小（在内存中所能使用的缓存大小） 0 关闭缓存

query_cache_type       查询缓存类型  0 OFF  1 ON   2 DEMAND 按需缓存

query_cache_wlock_invalidate

分析查询过程，在SQL语句前插入EXPLAIN

id         当前的select在总的查询中所处的层次，1 主查询；

select_type  查询类型，SIMPLE 没有子查询的简单查询

table       查询所关联的表

type       如何使用连接  ALL 全表扫描

possible_keys  可能会用到的键或者索引

key           真正用到的索引

key_len       所匹配的索引的长度

ref           使用外键

rows          当前查询所 处理的行数

Extra         额外信息

查询优化的几个原则：

1 能用连接尽可能不使用子查询；

2 尽可能使用会话变量及临时表来代替子查询（临时表，内存表），但临时表的大小不要超过系统限制的大小；

mysql> explain select name from stu where age> (select avg(age) from stu);

使用会话变量：

mysql> select @AVG:=avg(age) from stu;

mysql> select name from stu where age >@avg;

3 显式使用字段，尽量不使用通配符；

4 尽可能对连接中使用的字段使用索引。

对事务进行优化：

1 尽可能使用小事务 KISS（Keep It Simple,Stupid）；

2 选择合适的事务隔离级别，默认可重读

3 尽可能避免死锁；

对存储例程优化：

1 遵循KISS法则；

2 存储例程中的每个SQL语句使用之前先优化；

对表设计进行优化：

1 降低冗余；

2 不要交叉使用存储引擎；

在服务器级别进行优化：

table_open_cache  表名缓存 ，同时打开在内存中表的数目

key_buffter_size   索引缓存大小

read_buffer_size   读缓存大小

thread_cache_size  线程缓存大小

binlog_cache_size  日志缓存大小

sort_buffer_size    定义排序缓存大小

MySQL性能测试

测试工具：mysqlslap

         /usr/local/mysql/sql-bench下脚本