MySQL复制和性能优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介:

复制(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自动来判断。

复制过程:

wps_clip_image-4495

主服务器上线程: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;

wps_clip_image-21732

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;

wps_clip_image-7237

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;

wps_clip_image-6584

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

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

wps_clip_image-20364

wps_clip_image-26801

从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 < master.sql

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;

wps_clip_image-24014

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中,可能会造成主服务器无法进行时间点恢复的致命错误。

wps_clip_image-12079

在从服务器上过滤:

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 使用通配符忽略哪些表

wps_clip_image-16891

主从复制实现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 中添加

重启服务;

wps_clip_image-17310

开始制作证书:

先将主服务器配置成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

wps_clip_image-4499

[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

wps_clip_image-32480

[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的私钥,主服务的私钥和证书都已准备好了:

wps_clip_image-25101

开始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 中添加

重启服务;

wps_clip_image-30648

[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

wps_clip_image-30679

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

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

wps_clip_image-30728

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进行会话了。

层级复制:

wps_clip_image-27127

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

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

wps_clip_image-8212

衡量索引设计好坏的两个变量:Handler_read_key(越大越好), Handler_read_rnd_next(越小越好)。

查询缓存:

wps_clip_image-28591

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

wps_clip_image-10743

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);

wps_clip_image-17162

使用会话变量:

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

wps_clip_image-5760

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

wps_clip_image-7360

3 显式使用字段,尽量不使用通配符;

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

对事务进行优化:

1 尽可能使用小事务 KISS(Keep It Simple,Stupid);

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

wps_clip_image-12810

3 尽可能避免死锁;

对存储例程优化:

1 遵循KISS法则;

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

对表设计进行优化:

1 降低冗余;

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

在服务器级别进行优化:

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

wps_clip_image-15418

key_buffter_size   索引缓存大小

wps_clip_image-14572

read_buffer_size   读缓存大小

thread_cache_size  线程缓存大小

binlog_cache_size  日志缓存大小

sort_buffer_size    定义排序缓存大小

wps_clip_image-17602

MySQL性能测试

测试工具:mysqlslap

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










本文转自 490999122 51CTO博客,原文链接:http://blog.51cto.com/lyp0909/561857,如需转载请自行联系原作者
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
4月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第三篇(MySQL性能优化)
MySQL数据库进阶第三篇(MySQL性能优化)
|
23天前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
173 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
12天前
|
存储 关系型数据库 MySQL
mysql-性能优化(一)
mysql-性能优化(一)
|
20天前
|
关系型数据库 MySQL 数据处理
针对MySQL亿级数据的高效插入策略与性能优化技巧
在处理MySQL亿级数据的高效插入和性能优化时,以上提到的策略和技巧可以显著提升数据处理速度,减少系统负担,并保持数据的稳定性和一致性。正确实施这些策略需要深入理解MySQL的工作原理和业务需求,以便做出最适合的配置调整。
75 6
|
7天前
|
SQL 存储 关系型数据库
深入 MySQL 的执行计划与性能优化
深入 MySQL 的执行计划与性能优化
18 0
|
2月前
|
SQL 关系型数据库 MySQL
说一下MySQL主从复制的原理?
【8月更文挑战第24天】说一下MySQL主从复制的原理?
52 0
|
2月前
|
存储 关系型数据库 MySQL
"深入探索MySQL临时表:性能优化利器,数据处理的灵活之选"
【8月更文挑战第9天】MySQL临时表专为存储临时数据设计,自动创建与删除,仅在当前会话中存在,有助于性能优化。它分为本地临时表和全局临时表(通过特定逻辑模拟)。创建语法类似于普通表,但加TEMPORARY或TEMP关键字。适用于性能优化、数据预处理和复杂查询,需注意内存占用和事务支持问题。合理使用可大幅提升查询效率。
109 2
|
2月前
|
SQL canal 关系型数据库
(二十四)全解MySQL之主从篇:死磕主从复制中数据同步原理与优化
兜兜转转,经过《全解MySQL专栏》前面二十多篇的内容讲解后,基本对MySQL单机模式下的各方面进阶知识做了详细阐述,同时在前面的《分库分表概念篇》、《分库分表隐患篇》两章中也首次提到了数据库的一些高可用方案,但前两章大多属于方法论,并未涵盖真正的实操过程。接下来的内容,会以目前这章作为分割点,开启MySQL高可用方案的落地实践分享的新章程!
796 1
|
3月前
|
SQL 存储 数据库
MySQL设计规约问题之性能分析工具如Sql explain、show profile和mysqlsla在数据库性能优化中有什么作用
MySQL设计规约问题之性能分析工具如Sql explain、show profile和mysqlsla在数据库性能优化中有什么作用
|
4月前
|
SQL 关系型数据库 MySQL
MySQL性能优化实战:从索引策略到查询优化
MySQL性能优化聚焦索引策略和查询优化。创建索引如`CREATE INDEX idx_user_id ON users(user_id)`可加速检索;复合索引考虑字段顺序,如`idx_name ON users(last_name, first_name)`。使用`EXPLAIN`分析查询效率,避免全表扫描和大量`OFFSET`。通过子查询优化分页,如LIMIT配合内部排序。定期审查和调整策略以提升响应速度和降低资源消耗。【6月更文挑战第22天】
633 2
下一篇
无影云桌面