运维高级工程师必备之MySQL数据的主从复制、半同步复制和主主复-mysql教程-PHP中文網

首頁

資料庫

mysql教程

运维高级工程师必备之MySQL数据的主从复制、半同步复制和主主复

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 04:27 PM

mysql同步複製必備數據運維

运维工程师必备之MySQL数据的主从复制、半同步复制和主主复制详解原创作品，允许转载，转载时请务必以超链接形式标明文章 ? 原始出处 ? 、作者信息和本声明。否则将追究法律责任。 http://chrinux.blog.51cto.com/6466723/1204586 一、MySQL复制概述 ? ?⑴、

运维工程师必备之MySQL数据的主从复制、半同步复制和主主复制详解

原创作品，允许转载，转载时请务必以超链接形式标明文章?原始出处?、作者信息和本声明。否则将追究法律责任。http://chrinux.blog.51cto.com/6466723/1204586

一、MySQL复制概述

? ?⑴、MySQL数据的复制的基本介绍

? ?目前MySQL数据库已经占去数据库市场上很大的份额，其一是由于MySQL数据的开源性和高性能，当然还有重要的一条就是免费~不过不知道还能免费多久，不容乐观的未来，但是我们还是要能熟练掌握MySQL数据的架构和安全备份等功能，毕竟现在它还算是开源界的老大吧！

? ?MySQL数据库支持同步复制、单向、异步复制，在复制的过程中一个服务器充当主服务，而一个或多个服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。
? ?单向复制有利于健壮性、速度和系统管理：

? ?健壮性：主服务器/从服务器设置增加了健壮性。主服务器出现问题时，你可以切换到从服务器作为备份。

? ?速度快：通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的客户响应时间。SELECT查询可以发送到从服务器以降低主服务器的查询处理负荷。但修改数据的语句仍然应发送到主服务器，以便主服务器和从服务器保持同步。如果非更新查询为主，该负载均衡策略很有效，但一般是更新查询。

? ?系统管理：使用复制的另一个好处是可以使用一个从服务器执行备份，而不会干扰主服务器。在备份过程中主服务器可以继续处理更新。

? ?⑵、MySQL数据复制的原理

? ?MySQL复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。因此，要进行复制，必须在主服务器上启用二进制日志。

? ?每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新，以便从服务器可以对其数据拷贝执行相同的更新。

? ?认识到二进制日志只是一个从启用二进制日志的固定时间点开始的记录非常重要。任何设置的从服务器需要主服务器上的在主服务器上启用二进制日志时的数据库拷贝。如果启动从服务器时，其数据库与主服务器上的启动二进制日志时的状态不相同，从服务器很可能失败。

? ?将主服务器的数据拷贝到从服务器的一个途径是使用LOAD DATA FROM MASTER语句。请注意LOAD DATA FROM MASTER目前只在所有表使用MyISAM存储引擎的主服务器上工作。并且，该语句将获得全局读锁定，因此当表正复制到从服务器上时，不可能在主服务器上进行更新。当我们执行表的无锁热备份时，则不再需要全局读锁定。

? ?MySQL数据复制的原理图大致如下：

从上图我们可以看出MySQL数据库的复制需要启动三个线程来实现：

? ?其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让它发送记录在其二进制日志中的语句。主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以识别为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。第3个线程是SQL线程，是从服务器创建用于读取中继日志并执行日志中包含的更新。

? ?在前面的描述中，每个从服务器有3个线程。有多个从服务器的主服务器创建为每个当前连接的从服务器创建一个线程；每个从服务器有自己的I/O和SQL线程。

? ?这样读取和执行语句被分成两个独立的任务。如果语句执行较慢则语句读取任务没有慢下来。例如，如果从服务器有一段时间没有运行了，当从服务器启动时，其I/O线程可以很快地从主服务器索取所有二进制日志内容，即使SQL线程远远滞后。如果从服务器在SQL线程执行完所有索取的语句前停止，I/O 线程至少已经索取了所有内容，以便语句的安全拷贝保存到本地从服务器的中继日志中，供从服务器下次启动时执行。这样允许清空主服务器上的二进制日志，因为不再需要等候从服务器来索取其内容。

二、实列说明MySQL的主从复制架构和实现详细过程

? ? ?主从架构数据库的复制图如下：

其配置详细过程如下：

? ?1、环境架构：

? ? ? ?RedHat Linux Enterprise 5.8 ? ? ? ? mysql-5.5.28-linux2.6-i686.tar

? ? ? ?Master：172.16.7.1/16 ? ? ? ? ? ? ? ? Slave：172.16.7.2/16

? ?2 、安装mysql-5.5.28，需要在主节点和备节点上安装mysql

? ? ? ?Master：

? ? ? ?安装环境准备：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 为mysql的安装提供前提环境和初始化安装mysql 创建数据库目录 # mkdir /mydata/data –pv 创建mysq用户 # useradd -r mysql 修改权限 # chown -R mysql.mysql /mydata/data/ 使用mysql-5.5通用二进制包安装 解压mysql软件包 # tar xf mysql-5.5.28-linux2.6-i686.tar.gz-C /usr/local/ 创建连接，为了方便查看mysql的版本等信息 # cd /usr/local/ #ln –sv mysql-5.5.28-linux2.6-i686.tar.gzmysql 修改属主属组 # cd mysql # chown -R root.mysql ./* 初始化数据库 # scripts/mysql_install_db –user=mysql --datadir=/mydata/data/ 提供配置文件 # cp support-files/my-large.cnf /etc/my.cnf 提供服务脚本 # cp support-files/mysql.server/etc/rc.d/init.d/mysqld 添加至服务列表 # chkconfig --add mysqld # chkconfig --list mysqld # chkconfig mysqld on 编辑配置文件，提供数据目录 # vim /etc/my.cnf # The MySQL server? 修改mysqld服务器端的内容 log-bin=master-bin 主服务器二进制日志文件前缀名 log-bin-index=master-bin.index? 索引文件 innodb_file_per_table=?1?????开启innodb的一表一个文件的设置 server-id?????? =?1??????????必须是唯一的 datadir =/mydata/data??????? 数据目录路径 启动mysql服务 # servicemysqld start 为了便于下面的测试，设置环境变量 # vim/etc/profile.d/mysql.sh export PATH=$PATH:/usr/local/mysql/bin 执行环境变量脚本，使其立即生效 # . /etc/profile.d/mysql.sh

?启动服务并进行相关的测试：

?mysql的安装配置完成，下面增加一个用于同步数据的账户并设置相关的权限吧！

1 2 3 4 建立用户账户 mysql> grant replication slave on *.* to?'chris'@'172.16.%.%'?identified by?'work'; 刷新数据使其生效 mysql> flush privileges;

? ?至此我们mysql的Master设置完成，下面进行slave端的设置吧！

? ?Slave：

? ?安装环境配置：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 创建mysql数据库目录 # mkdir /mydata/data –pv 创建mysql用户 # useradd -r mysql 修改数据目录权限 # chown -R mysql.mysql /mydata/data/ 使用mysql-5.5通用二进制包安装mysql 解压mysql软件包 # tar xf mysql-5.5.28-linux2.6-i686.tar.gz-C /usr/local/ 创建连接，便于查看mysql的版本等信息 # cd /usr/local/ # ln –sv mysql-5.5.28-linux2.6-i686.tar.gzmysql 修改mysql属主属组 # cd mysql # chown -R root.mysql ./* 初始化mysql数据库 # scripts/mysql_install_db –user=mysql--datadir=/mydata/data/ 提供mysql配置文件 # cp support-files/my-large.cnf /etc/my.cnf 提供服务脚本 # cp support-files/mysql.server /etc/init.d/mysqld 添加至服务列表 # chkconfig --add mysqld 编辑配置文件 # vim /etc/my.cnf # The MySQL server #log-bin=mysql-bin????? 禁用二进制日志，从服务器不需要二进制日志文件 datadir = /mydata/data? mysql的数据目录 relay-log = relay-log?? 设置中继日志 relay-log-index = relay-log.index? 中继日志索引 innodb_file_per_table =?1 server-id?????? =?2????id不要和主服务器的一样 设置环境变量 # vim/etc/profile.d/mysql.sh export PATH=$PATH:/usr/local/mysql/bin 执行此脚本（导出环境变量） # . /etc/profile.d/mysql.sh 启动服务 # service mysqld start

? 到这slave服务的mysql安装和配置完成，下面启动slave复制吧，开启之前先查看下从服务上的二进制文件吧

1 2 3 4 5 mysql> show master status; #在Master上执行查看二进制文件 在从服务器上开启复制功能 change master to master_host='172.16.7.1',master_user='chris',master_password='work',master_log_file='master-bin.000001',master_log_pos=407; 开启复制功能 mysql>start slave;

至此我们的mysql服务器的主从复制架构已经基本完成，下面开启服务并测试测试吧~

在从服务器开启复制进程：mysql>start slave;

? ?至此我们mysql服务器的主从复制架构已经完成，但是我们现在的主从架构并不完善，因为我们的从服务上还可以进行数据库的写入操作，一旦用户把数据写入到从服务器的数据库内，然后从服务器从主服务器上同步数据库的时候，会造成数据的错乱，从而会造成数据的损坏，所以我们需要把从服务器设置成只读~方法如下：

注意：read-only = ON ，这项功能只对非管理员组以为的用户有效！

OK，此致我们的mysql基于主从架构的复制功能已经搭建全部完成~下面介绍下关于mysql数据目录下面各个文件的功能和作用！

? ?由于二进制文件的缓冲区内，当我们的服务器宕机的时候，缓存区内的数据并没有同步到二进制日志文件内的时候，那就悲剧了，缓冲区内的数据就无法找回了，为了防止这种情况的发送，我们通过设置mysql直接把二进制文件记录到二进制文件而不再缓冲区内停留。

sync-binlog = ON 在主服务器上进行设置，用于事务安全

? 从上面我们可以看到从服务器启动的时候其Slave_IO_Running: Yes和Slave_SQL_Running: Yes是自动启动的，但是有时候我们在主服务上进行的误操作等，也会直接同步到从服务器上的，要想恢复那就难了，所以我们需要关闭其自动执行功能，让其能够停止，skip-slave-start = 1 ,让其不开启自动同步，但是遗憾的是mysql5.28上已经没有了，我们可以通过停止相关线程来实现：

mysql>STOP SLAVE 或STOP SLAVE ?IO_THREAF或STOP SLAVE SQL_THREAD

注意：从服务器的所有操作日志都会被记录到数据目录下的错误日志中！

三、MySQL的半同步复制

? ?实现半同步复制的功能很简单，只需在mysql的主服务器和从服务器上安装个google提供的插件即可实现，

? ?主服务上使用semisync_master.，从服务器上使用sosemisync_slave.so插件即可实现，插件在mysql通用二进制的mysql/lib/plugin目录内。

其配置步骤如下

1、分别在主从节点上安装相关的插件

master：

1 2 3 安装插件：mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME?'semisync_master.so'; 启动模块：mysql> SET GLOBAL rpl_semi_sync_master_enabled =?1; 设置超时时间：mysql> SET GLOBAL rpl_semi_sync_master_timeout =?1000；

1 2 3 4 slave： 安装插件：msyql> INSTALL PLUGIN rpl_semi_sync_slave SONAME?'semisync_slave.so'; 启动模块：mysql> SET GLOBAL rpl_semi_sync_slave_enabled =?1; 重启进程使其模块生效：mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

? 上面的设置时在mysql进程内动态设定了，会立即生效但是重启服务以后就会失效，为了保证永久有效，需要把相关配置写到主、从服务器的配置文件my.cnf内：

1 2 3 4 5 6 7 8 在Master和Slave的my.cnf中编辑： # On Master [mysqld] rpl_semi_sync_master_enabled=1 rpl_semi_sync_master_timeout=1000???#此单位是毫秒 # On Slave [mysqld] rpl_semi_sync_slave_enabled=1

? 确认半同步功能已经启用，通过下面的操作进行查看

1 2 3 4 5 master： mysql> CREATE DATABASE asyncdb; master> SHOW STATUS LIKE?'Rpl_semi_sync_master_yes_tx'; slave> SHOW DATABASES; 其测试过程如下

然后把从服务器上的复制进程开启，

? 我们至此已经实现了mysql数据库复制的半同步方式的架构，并且通过测试查看了复制功能，下面我们进行双主模型架构吧。

四、MySQL设置主-主复制：masterslave?
1、在两台服务器上各自建立一个具有复制权限的用户；让两个数据库互为主从的关系

2、修改配置文件：

把上面的连个数据库的配置文件重新配置，其配置如下?

1 2 3 4 5 6 7 8 # 主服务器上 [mysqld] server-id =?1 log-bin = mysql-bin relay-log = relay-mysql relay-log-index = relay-mysql.index auto-increment-increment =?2???????????#每次跳两个数。 auto-increment-offset =?1??????????????#从1开始。

1 2 3 4 5 6 7 [mysqld] server-id =?2 log-bin = mysql-bin relay-log = relay-mysql relay-log-index = relay-mysql.index auto-increment-increment =?2 auto-increment-offset =?2

? 如果此时两台服务器均为新建立，且无其它写入操作，各服务器只需记录当前自己二进制日志文件及事件位置，以之作为另外的服务器复制起始位置即可

?在各个服务器上建立账号和权限，来进行同步设置

1 2 3 master： mysql> GRANT REPLICATION SLAVE ON *.* TO?'chrislee'@'172.16.%.%'?IDENTIFIED BY?'work'; mysql> flush privileges;

1 2 3 slave： mysql> GRANT REPLICATION SLAVE ON *.* TO?'chrisli'@'172.16.%.%'?IDENTIFIED BY?'work'; mysql> flush privileges

在各服务器上指定对另一台服务器为自己的主服务器即可：

1 2	`server1` `mysql> CHANGE MASTER TO MASTER_HOST='172.16.7.2',MASTER_USER='chrisli',MASTER_PASSWORD='work',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=344;`

1 2	`server2：` `mysql> CHANGE MASTER TO MASTER_HOST='172.16.7.1',MASTER_USER='chrislee',MASTER_PASSWORD='work',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=345;`

双主架构配置基本完成，下面在各自上面启动复制进程吧~并进行测试：

? ?至此我们通过上面的测试，可以看出已经实现了主主复制的功能~到此我们关于mysql数据库的主从复制、半同步复制和主主复制的架构都已经实现，东西较多~整理的不好，还望包涵~其中的错误还望各位大神指出~

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?chrinux-chris linux

本文出自 “Chris On the way” 博客，请务必保留此出处http://chrinux.blog.51cto.com/6466723/1204586

陳述

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

MySQL：世界上最受歡迎的數據庫的簡介Apr 12, 2025 am 12:18 AM

MySQL是一種開源的關係型數據庫管理系統，主要用於快速、可靠地存儲和檢索數據。其工作原理包括客戶端請求、查詢解析、執行查詢和返回結果。使用示例包括創建表、插入和查詢數據，以及高級功能如JOIN操作。常見錯誤涉及SQL語法、數據類型和權限問題，優化建議包括使用索引、優化查詢和分錶分區。

MySQL的重要性：數據存儲和管理Apr 12, 2025 am 12:18 AM

MySQL是一個開源的關係型數據庫管理系統，適用於數據存儲、管理、查詢和安全。 1.它支持多種操作系統，廣泛應用於Web應用等領域。 2.通過客戶端-服務器架構和不同存儲引擎，MySQL高效處理數據。 3.基本用法包括創建數據庫和表，插入、查詢和更新數據。 4.高級用法涉及復雜查詢和存儲過程。 5.常見錯誤可通過EXPLAIN語句調試。 6.性能優化包括合理使用索引和優化查詢語句。

為什麼要使用mysql？利益和優勢Apr 12, 2025 am 12:17 AM

選擇MySQL的原因是其性能、可靠性、易用性和社區支持。 1.MySQL提供高效的數據存儲和檢索功能，支持多種數據類型和高級查詢操作。 2.採用客戶端-服務器架構和多種存儲引擎，支持事務和查詢優化。 3.易於使用，支持多種操作系統和編程語言。 4.擁有強大的社區支持，提供豐富的資源和解決方案。

描述InnoDB鎖定機制（共享鎖，獨家鎖，意向鎖，記錄鎖，間隙鎖，下一鍵鎖）。Apr 12, 2025 am 12:16 AM

InnoDB的鎖機制包括共享鎖、排他鎖、意向鎖、記錄鎖、間隙鎖和下一個鍵鎖。 1.共享鎖允許事務讀取數據而不阻止其他事務讀取。 2.排他鎖阻止其他事務讀取和修改數據。 3.意向鎖優化鎖效率。 4.記錄鎖鎖定索引記錄。 5.間隙鎖鎖定索引記錄間隙。 6.下一個鍵鎖是記錄鎖和間隙鎖的組合，確保數據一致性。

MySQL查詢性能差的常見原因是什麼？Apr 12, 2025 am 12:11 AM

MySQL查询性能不佳的原因主要包括没有使用索引、查询优化器选择错误的执行计划、表设计不合理、数据量过大和锁竞争。1.没有索引导致查询缓慢，添加索引后可显著提升性能。2.使用EXPLAIN命令可以分析查询计划，找出优化器错误。3.重构表结构和优化JOIN条件可改善表设计问题。4.数据量大时，采用分区和分表策略。5.高并发环境下，优化事务和锁策略可减少锁竞争。

您什麼時候應該使用複合索引與多個單列索引？Apr 11, 2025 am 12:06 AM

在數據庫優化中，應根據查詢需求選擇索引策略：1.當查詢涉及多個列且條件順序固定時，使用複合索引；2.當查詢涉及多個列但條件順序不固定時，使用多個單列索引。複合索引適用於優化多列查詢，單列索引則適合單列查詢。

如何識別和優化MySQL中的慢速查詢？（慢查詢日誌，performance_schema）Apr 10, 2025 am 09:36 AM

要優化MySQL慢查詢，需使用slowquerylog和performance_schema：1.啟用slowquerylog並設置閾值，記錄慢查詢；2.利用performance_schema分析查詢執行細節，找出性能瓶頸並優化。

MySQL和SQL：開發人員的基本技能Apr 10, 2025 am 09:30 AM

MySQL和SQL是開發者必備技能。 1.MySQL是開源的關係型數據庫管理系統，SQL是用於管理和操作數據庫的標準語言。 2.MySQL通過高效的數據存儲和檢索功能支持多種存儲引擎，SQL通過簡單語句完成複雜數據操作。 3.使用示例包括基本查詢和高級查詢，如按條件過濾和排序。 4.常見錯誤包括語法錯誤和性能問題，可通過檢查SQL語句和使用EXPLAIN命令優化。 5.性能優化技巧包括使用索引、避免全表掃描、優化JOIN操作和提升代碼可讀性。

See all articles