查看“高性能MySQL笔记(下)”的源代码
←
高性能MySQL笔记(下)
跳转至:
导航
、
搜索
因为以下原因,你没有权限编辑本页:
该页面已被保护以防止编辑和其他操作。
您可以查看并复制此页面的源代码:
[[category:MySQL]] 相关文章:[[高性能MySQL笔记(上)]]<br /> 淘宝的MySQL博客[http://mysql.taobao.org/monthly/ 月报] [http://mysql.taobao.org 首页] =第十章复制= ==10.2配置复制== [http://blog.csdn.net/jesseyoung/article/details/41942809 mysql主从复制-CHANGE MASTER TO 语法详解]<br /> 主主及主从示例: <pre > 1.两主互为主从 (1)在主1上执行 grant replication slave on *.* to slave@'10.161.0.168' identified by 'lemon123'; (2)在主2上执行 CHANGE MASTER TO MASTER_HOST='10.161.0.145',MASTER_USER='slave',MASTER_PASSWORD='lemon123',MASTER_LOG_FILE='mysql-bin.000020',MASTER_LOG_POS=107; start slave; (3)在主2上执行 grant replication slave on *.* to slave@'10.161.0.145' identified by 'lemon123'; (4)在主1上执行 CHANGE MASTER TO MASTER_HOST='10.161.0.168',MASTER_USER='slave',MASTER_PASSWORD='lemon123',MASTER_LOG_FILE='mysql-bin.000021',MASTER_LOG_POS=107; 2.主从复制 (1)在主1上执行 grant replication slave on *.* to slave@'10.161.0.164' identified by 'lemon123'; grant replication slave on *.* to slave@'10.161.0.165' identified by 'lemon123'; grant replication slave on *.* to slave@'10.161.0.169' identified by 'lemon123'; (2)在三个从库上分别执行 CHANGE MASTER TO MASTER_HOST='10.129.150.89',MASTER_USER='slave',MASTER_PASSWORD='lemon123',MASTER_LOG_FILE='mysql-bin.000020',MASTER_LOG_POS=261; </pre> ===10.2.1创建复制账号=== GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'192.168.0.%' IDENTIFIED BY 'YourPassword'; ===10.2.2配置主库和备库=== 在<big>'''主库'''</big>的my.cnf文件中增加或修改如下内容: log_bin = mysql-bin server_id = 10 不要在my.cnf中设置master_port或master_host这些选项,它们已被废弃,只会导致问题没有好处 重启后检查主库状态: <pre> mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 98 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) </pre> 在<big>'''备库'''</big>的my.cnf文件中增加或修改如下内容(事实上只有sever_id是必须的): <pre> log_bin = mysql-bin server_id = 2 relay_log = /var/lib/mysql/mysql-relay-bin log_slave_updates = 1 read_only= 1 </pre> 其中 ;relay_log:允许备库将其重放的事件也记录到自身的二进制日志中 ;read_only:如果可能的话最好使用read_only选项,该选项会阻止任何没有特权权限的线程修改数据。但不是很实用,尤其是那些需要在备库建表的应用 ===10.2.3启动复制=== 连到主: CHANGE MASTER TO MASTER_HOST='server1', MASTER_USER='repl', MASTER_PASSWORD='p4ssword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0; 启动: START SLAVE; 查看状态:'''SHOW SLAVE STATUS\G;''' [[文件:ShowSlaveStatusTest.png|400px|缩略图|居中|结果]] 查看复制线程列表:'''SHOW PROCESSLIST\G''' [[文件:ShowProcesslistReplication.png|600px|缩略图|居中|查看线程列表]] ===10.2.5推荐的复制配置=== 如果用的InnoDB强烈推荐如下配置(这些都是默认配置): {| |- | innodb_flush_logs_at_trx_commit=1 || # Flush every log write |- | innodb_support_xa=1 || # MySQL 5.0 and newer only |- | innodb_safe_binlog || # MySQL 4.1 only, roughly equivalent to innodb_support_xa |} 主库(一定要指定日志文件的绝对路径): :log_bin=/var/lib/mysql/mysql-bin # Good; specifies a path and base name : #log_bin # Bad; base name will be server’s hostname 备库(一定要指定中继日志的绝对路径): :relay_log=/path/to/logs/relay-bin :skip_slave_start :read_only #除了复制线程和其它超级线程之外不能修改 如果正在使用MySQL5.5并且不介意额外的fync()导致的性能开销,最好设置如下选项: :sync_master_info = 1 :sync_relay_log = 1 :sync_relay_log_info = 1 ==10.4复制拓扑== 记住下面的基本原则: *一个MySQL备库只能有一个主库 *每个备库必须有一个唯一的服务器ID *一个主库可以有多个备库 *如果打开了log_slave_updates选项,一个备库可以把其主库上的数据变化传播到其他备库 ===10.4.8定制的复制方案=== ;创建日志服务器:日志服务器不需要任何数据,它的目的仅仅是将数据提供给别的数据服务器 :它更加容易重放且/或者过滤二进制日志事件,它对崩溃后重启恢复很有帮助。同时对基于时间点的恢复也很有帮助,在第15章我们会讨论。 :可以从[http://mysqlsandbox.net mysqlsandbox] 上获得一个沙箱脚本来创建日志服务器。 :示例,假设有 somelog-bin.000001, somelog-bin.000002 两个日志文件。把它放在MySQL日志文件夹中,如/var/log/mysql,然后编辑my.cnf: ::log_bin = /var/log/mysql/somelog-bin ::log_bin_index = /var/log/mysql/somelog-bin.index :我们再来创建日志索引文件(然后启动日志服务器并通过 SHOW MASTER LOGS 来确保其找到日志文件): :: # /bin/ls −1 /var/log/mysql/somelog-bin.[0-9]* > /var/log/mysql/somelog-bin.index ==10.5复制和容量规划== ===10.5.2备库什么时候开始延迟=== ;监控方式:如果有复制延迟的曲线图则要观察图上的一些短暂的延迟骤升 ;测试法:如果将备库停止一个小时,然后开启并在1个小时内赶上,说明只消耗了一半的容量。也就是说备库在一个小时内赶上了两个小时内所有的变更 ==10.6复制的管理和维护== ===10.6.1监控复制=== 在主库上可以使用 SHOW MASTER LOGS 命令来查看当前主库的二进制日志位置和配置(该命令用于给PURGE MASTER LOGS命令决定使用哪些参数) [[文件:ShowMasterLogs.png|300px|缩略图|居中|SHOW MASTER LOGS]] 此外还可以通过SHOW BINLOG EVENTS来查看复制事件: [[文件:ShowBinlogEventsExample.png|500px|缩略图|居中|SHOW BINLOG EVENTS IN 'mysql-bin.000223' FROM 13634\G]] ===10.6.2测量备库延迟=== ;Seconds_behind_master:SHOW SLAVE STA TUS中的Seconds_behind_master 可以显示延迟,但不准确。 ;heartbeat record:最好的方式是使用heartbeat record :这是一个在主库上每秒执行一次的时间戳。'''延时 = 备库当前时间戳 - 心跳时间翟''' :pt-heartbeat 脚本是复制心跳最流行的一种实现 ===10.6.3确定主备是否一致=== 可能使用工具:'''pt-table-checksum''' <br /> [http://blog.chinaunix.net/uid-16844903-id-3360228.html 利用pt-table-checksum做主从一致性校验 ]<br /> 通常情况下可以在主库上运行该工具: '''$ pt-table-checksum --replicate=test.checksum <master_host>''' 该命令会检查所有的表,并将结果插入到test.checksum 中。当查询在备库执行完就可以简单的比较主备之间的不同了。pt-table-checksum能够发现服务器所有的备库,在每台备库上运行查询,并自动输出结果 ===10.6.4从主库重新同步备库=== 最简单的办法是mysqldump转储受影响的数据并重新导入<br /> 但问题多多尤其在一个繁忙的数据库里;或从一百万行找出一千行的不同,是非常浪费资源的 ;pt-table-sync:可以解决该问题。该工具能高效地查找及解决服务器间的不同 :它是结合pt-table-checksum生成的checksum表来工作的,所以只能同步那些已知的不同步的表的数据块 ===10.6.5改变主库=== 整个过程最困难的是获取新主库上合适的二进制日志的位置,这样才可以从和老主库相同的逻辑位置开始复制<br /> ====计划内提升==== [https://www.douban.com/note/446551760/ RESET MASTER 和RESET SLAVE] ====计划外提升==== ====确定期望的日志位置==== 如果有备库和新主库的位置不相同,则需要找到该备库最后一条执行的事件在新主库的二进制日志中相应的位置,然后执行CHANGE MASTER TO 。可以通过mysqlbinlog工具来找到备库执行的最后一条查询,然后在主库找到同样一条查询,进行简单的计算即可得到。示例命令如下(不需要用户名密码):<br /> mysqlbinlog mysql-bin.000011 | tail -n 30 [[文件:MysqlbinlogTest2.png|800px|缩略图|居中| mysqlbinlog ]] <big>例子——提升server2为新主库:</big><br /> 假设server1是server2和server3的主库,其中server1已经崩溃。根据SHOW SLAVE STATUS获得Master_Log_File/Read_Log_Pos的值,server2已经执行完server1的所有二进制日志,但server3还不是最新的数据。如下图所示: [[文件:BinlogposExample.png|800px|缩略图|居中|当server1崩溃,server2已追赶上,但server3的复制落后]] *根据以上结果,server2已经跟执行完主库上所有二进制日志,但server3还不是最新数据 *应该在server3的CHANGE MASTER TO语句赋予一个什么样的参数呢?server3在1493停止,比server2的1582要小89字节。server2正在向偏移量为8167的二进制日志写入,8167-89=8078,因此理论上我们应将server3指向server2的日志偏移量为8087的位置。最好去确认下这个位置附近的日志事件(使用mysqlbinlog) *假设我们观察到事件是一样的,下面这条语句可以完成从server3到server2的切换:'''CHANGE MASTER TO MASTER_HOST="server2", MASTER_LOG_FILE="mysql-bin.000009", MASTER_LOG_POS=8078;''' ===10.6.6在一个主主配置中交换角色=== [http://dinglin.iteye.com/blog/1694593 master_pos_wait函数与MySQL主从切换] ==10.7复制的问题和解决方案== [http://blog.sina.com.cn/s/blog_70207ec20102v9vf.html mysql开启二进制日志记录文件 简介: MySQL的二进制日志] ===10.7.1数据损坏或丢失的错误=== ;主库意外关闭:如果没有设置sync_binlog,当主库故障重启后可能在二进制日志里丢失一些事务。这时解决方案是从备库的下一个二进制日志的开头读日志。但一些事件会永久性的丢失。 :建议使用pt-table-checksum工具检查主备一致性,以便于修复。可以通过开启sync_binlog来避免事件丢失,innodb_flush_log_at_trx_commit没有设置为1也可能导致数据丢失(但不会损坏) ;备库意外关闭:可能会导致同步信息没来得及写入master.info,备库可能桧重新尝试一些二进制日志事件,可能导致唯一索引错误。唯一的办法就是忽略这些错误 :可以用pt-slave-restart来帮助完成这一点 ;主库上的二进制文件损坏:除了忽略,别无选择。可以在主库上执行 '''FLUSH LOGS''' 命令,这样主库会开始一个新的日志文件,然后将备库指向该文件开始的地方。 :也可以尝试去发现损坏区域的结束位置。某些情况下可以通过 <big>'''SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1'''</big> 来忽略一个损坏的事件。如果有多个损坏的事件,就需要重复该事件。但如果有太多损坏的事件,损坏的事件头会阻止服务器去找到下一个事件。这种情况下,可能不得不手动找到下一个完好的事件 ;备库上的二进制文件损坏:可以重新CHANGE MASTER TO ;数据改变,但事件仍是有效SQL:不幸的是,MySQL甚至不能察觉这种损坏。 ;数据改变且事件是无效SQL:可以通过mysqlbinlog提取事件,并看到一些错乱的数据:UPDATE tbl SET col????????????????? 可以通过偏移量的方式找到下一个事件 ;数据遗漏并且/或者事件的长度是错误的: ;某些事件已经损坏或被覆盖,或者偏移量已经改变并且下一个事件的起始偏移量也是错误的:以上这两种情况 mysqlbinlog 已经起不了多少作用。 :通过一些简单的分析后可用 mysqlbinlog --start-position来跨过损坏的事件。或者使用CHANGE MASTER TO 的 MASTER_LOG_POS参数。以下是找到偏移量的两个例子: :例1:$ mysqlbinlog mysql-bin.000113 | egrep '^# at ' :例2:strings -n 2 -t d mysql-bin.000113 ::然后找到'G结尾的行,它们是固定长度事件头的一部分。([http://www.cppblog.com/prayer/archive/2009/07/22/90836.html linux命令strings]) ===10.7.10丢失的临时表=== 尽量不用(在备库上使用的影响会小一点)<br /> 另一个办法是使用伪临时表:在一个temp库上创建,如表top_users_1234,其中1234是函数CONNECTION_ID()的返回值。可能通过清理线程将其移除——通过SHOW PROCESSLIST 来获得活跃的连接列表,并将其与表名中的连接ID比较 ===10.7.12InnoDB锁引起的锁争用=== ;替换INSERT...SELECT:在主库上先执行 SELECT INTO OUTFILE 再执行 LOAD DATA INFILE。这种方法更快,而且不需要加锁<br /> 以下是加锁示意图: [[文件:InsertSelectLock.png|500px|缩略图|居中|两个事务更新数据,使用共享锁串行化更新]] ===10.7.13在主—主复制结构中写入两台数据库=== 可以使用auto_increment_increment 和 auto_increment_offset 。但自增问题只是所有问题的一小部分 ===10.7.14过大的复制延迟=== ;log_slow_slave_statements:在备库打开该选项,在标准的MySQL慢查询日志能够记录MySQL5.1及更新版本中执行的语句,这样就可以找到哪些语句执行的慢了。最好配合''pt-query-digest''使用 除了购买更快的磁盘和CPU,备库没有太多的优化空间<br /> *一个简单的办法是设置 innodb_flush_log_at_trx_commit = 2 来实现,这样事务会提交的快些(见8.5.1节) *还可以在备库上禁止二进制日志记录; *设置 innodb_locks_unsafe_for_binlog = 1 *设置MyISAM的 delay_key_write = ALL 但这些都是牺牲安全为代价的,如果要把备库提升为主库要记得把这些选项设置回安全值。<br /> '''不要重复写操作中代价较高的部分'''<br /> ;LOAD DATA INFILE: :可能先从备库执行,再回传到主库,防止在每个备库上重放。下面例子有效避免了在主库及其它备库上执行昂贵的GROUP BY操作: :先在备库上执行(summary_db是备库的特别保留的数据库): :: ''REPLACE INTO summary_db.summary_table (col1, col2, ...) -> SELECT col1, sum(col2, ...) FROM main_db.enormous_table GROUP BY col1;'' :现在就可以执行SELECT INTO OUTFILE,然后再执行LOAD DATA INFILE,将结果加载到主库中。 '''在复制之外并行写入'''<br /> *一个很好的例子:之前讨论的归档,可以禁止归档查询写到二进制日志中去 *另一个例子,根据网上总结(非教材内容,需要在主备所有机器都执行,容易不一致)[http://www.javali.org/document/tips-on-replication-in-load-data-infile-in-mysql.html Mysql中load data infile主从复制注意点] 。 ::在主库、备库上执行如下语句: :::''time mysql -e "set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=0; load data local infile 'infile.txt' into table load_test fields terminated by '\t' lines terminated by '\n' (col1, col2);"'' ::注意: time命令是用于统计执行时间的。set sql_log_bin=0 等的设置仅针对本次连接有效。 '''为复制线程预取缓存'''<br /> ===10.7.15来自主库过大的包=== ;max_ allowed_packet:如果该值在主备不匹配。主库可能会记录一个备库认为过大的包。当备库获取二进制日志时可能会碰到各种各样的问题,包括无限的报错和重试、或者中继日志损坏 ===10.7.16受限的复制还宽=== 可以开启 slave_compressed _protocol 选项。可减少三分之一的带宽 ==10.8复制有多快== 时间插件[http://datacharmer.blogspot.com/2006/04/measuring-replication-speed.html NOW_USEC] P290、P493<br /> ==10.9MySQL复制的高级特性== ;半同步:在某些场景下半同步能提高性能,在主库关闭sync_binlog的情况下保证更加安全。写入远程的内存(一台备库的反馈)比写入本地的磁盘(写入并刷新)要更快。有些性能测试表明性能提高了两倍! ;多线程复制:MySQL5.6已提供 =十一章可扩展的MySQL= ==11.1什么是可扩展性== 可扩展性表明了,当需要增加资源以执行更多工作时系统能获得划算的同等提升。以下是可扩展性定律的三种模型:<br /> [[文件:ScalabilityLaw2.png|600px|缩略图|居中|线性扩展、Amdahl扩展及USL扩展定律]] ==11.2扩展MySQL== ===11.2.4向外扩展=== ;3.选择分区键: [[文件:PatitionTheoryDemo.png|500px|缩略图|居中|两个数据模型,一个易于分片,另一个则难以分片]] ;4.多个分区键——一个例子::一个社交网站下的读书俱乐部站点,该站点可以显示任何书籍的任何评论,也能显示用户读过或评论过的所有书籍。假设用户数据和书籍数据都进行了分片设计。而评论同时拥有用户ID和评论ID,这样就跨越了两个分片的边界。实际上无须冗余存储两份评论数据,替代方案是: :将评论和用户数据一起存储,然后把每个评论的标题和ID与书籍数据存储在一起。这样在渲染大多数关于某本书的评论的视图时无须同时访问用户和书籍数据存储,如果需要显示完整的评论内容,可以从用户数据存储中获得。 ;5.跨分片查询:可以使用数据队列服务(如Gearman)来聚合查询,再用PHP等应用使用队列服务的聚合结果。 :跨分片查询也可以借助汇总表来执行。可以遍历所有分片来生成汇总表并将结果在每个分片上冗余存储。也可以把汇总表放到另外一个数据存储中,这样就只需要存储一份了。 ;6.分配数据、分片和节点:应该让分片比节点节点容量小很多。可以假设100G的表,则可以<big>'''1G/分片'''</big>。 :简单说,“易于管理的大小”是指保持表足够小,以便能在<big>'''5或10分钟内提供日常维护操作'''</big>,例如 ALTER TABLE、CHECK TABLE 或者 OPTIMIZE TABLE操作 ;7.在节点上部署分片:以下是一些常用的用法 :*每个分片使用单一数据库,并且数据库名要相同。典型的应用场景是需要每个分片都能镜像到原应用的结构。这在部署多个应用实例,并且每个实例对应一个分片时很有用。 :*将多个分片的表放到一个数据库中,在每个表名上包含分片号(如 bookclub.comments_23)这种配置下,单个数据库可以支持多个分片 :*为每个分片使用一个数据库,并在数据库中包含所有应用需要的表。在数据库名中包含分片号(如bookclub_23.comments, bookclub_23.users )。 :*<big>库和表都加分片号 如 bookclub_23.comments_23</big> :*每个节点上多个MySQL实例,每个实例上有一个或多个分片。可以使用以上任意组合方式分片 :如果为已有应用分片,结果往往是一个节点对应一个分片 ;8.静态分配:如:'''SELECT CRC32(111) % 100;''' :缺点:分片很大且数量不多时,很难平衡负载;无法处理热点数据;修改分片策略困难 ;9.动态分配: ;14.分片工具: :;JAVA:Hibernate Shards (http://shards.hibernate.org) 、 HiveDB (http://www.hivedb.org) :;PHP :*Shard-Query (http://code.google.com/p/shard-query/) :*ScaleBase (http://www.scalebase.com) :*ScalArc (http://www.scalarc.com) :*dbShards (http://www.dbshards.com). '''另外几个集群中间件''' [http://blog.csdn.net/lichangzhen2008/article/details/44708227 TDDL、Amoeba、Cobar、MyCAT架构比较] ===11.2.6通过集群扩展=== ;1.MySQL Cluster:当使用MySQL来控制NDB时,行的主键就是键,其他的列是值。 :需要 高速连接设备+大量内存(服务器间需要大量内存) :缺点: :*复杂查询现在支持的不是很好,如那些关联和聚合的查询 :*它是一个事务型的数据库,但不支持MVCC,所以需要读锁,也不做任何死锁检测 ;2.Clustrix:它能直接替代MySQL。与MySQL Cluster很像;关键不同点是,Clustrix是完全的分布式执行并且缺少顶层的代理,它能很好的理解MySQL协议,无需MySQL进行协议转换。 :实验表明,Clustrix能提供高性能和扩展性 ;3.ScaleBase:是一个软件代理。相似的还有 ScaleArc、dbShards ===11.2.7内向扩展=== *如果应用层认为关联表具有层次关系,那么归档的顺序也应该和它一样 *当解除归档时,可以设置一检查点。如用户的最后登录时间 保持活跃数据独立 *如可以将user表划分为:active_users 和 inactive_users 表。 *MySQL表分区,能够帮助把'''最近'''的数据留在内存中。 *基于时间的数据分区。一般而言新数据要比老数据活跃 ::如 可以通过分片,把老数据放到一台低配机器上,把新数据放到活跃的节点,该节点使用更大的内存和快速硬盘。 ::可以使用动态分片轻松实现这种策略。如:分片目录表可能定义如下: [[文件:ActiveUserShard.png|300px|缩略图|居中|active shard]] ==11.3负载均衡== ===11.3.1直接连接=== ;基于会话分离(推荐):可以在会话层设置一个标志位,表明用户做了更新,就将该用户的查询在一段时间内总是指向主库。 ;基于版本的分离:这个基于会话的分离相似:你可以跟踪对象的版本号及/或时间戳,通过从备库读取对象的版本或时间以戳来判断数据是否足够新。如,在用户在发表了一篇文章后,可以更新用户的版本,这样就会从主库去读取数据了。 ;基于全局版本/会话的分离:当写时执行一次SHOW MASTER STATUS,把日志坐标写到REDIS,然后在备上SHOW SLAVE STATUS,把结果同之前缓存比较,如果备库更新则可以安全写入。 :pt-heartbeat可以监控延迟 ===11.3.2引入中间件=== 如HAProxy ===11.3.3一主多备间的负载均衡=== ;过滤和数据分区:可以使用复制过滤技术在相似的备库上对数据进行分区。 :但没有内建的办法在行级别上进行复制过滤。你可以使用一些独创的技术实现,如用触发器和一些不同的表
返回
高性能MySQL笔记(下)
。
导航菜单
个人工具
创建账户
登录
命名空间
页面
讨论
变种
视图
阅读
查看源代码
查看历史
更多
搜索
相关站点
站长博客
分类
DB
WEB
iOS
Android
深度学习
技术收集
素材收集
历史
常用网址
导航
首页
最近更改
随机页面
帮助
常用管理页面
五笔字根表
工具
链入页面
相关更改
特殊页面
页面信息