mysql数据库主从同步之双主配置----互为主从

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

Mysql数据库复制原理:

整体上来说,复制有3个步骤:   

(1)master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,binary log events);

(2)slave将master的binary log events拷贝到它的中继日志(relay log);

(3)slave重做中继日志中的事件,将改变反映它自己的数据。

下图描述了复制的过程:

wKiom1dqUBHTmyfaAABUrjWR9Ok538.jpg-wh_50


                                  

      该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前,master在二进制日志记录这些改变。MySQL将事务串行的写入二进制日 志,即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后,master通知存储引擎提交事务。

       下一步就是slave将master的binary log拷贝到它自己的中继日志。首先,slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接,然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件,如果已经跟上master,它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中 继日志。

       SQL slave thread(SQL从线程)处理该过程的最后一步。SQL线程从中继日志读取事件,并重放其中的事件而更新slave的数据,使其与master中的数 据一致。只要该线程与I/O线程保持一致,中继日志通常会位于OS的缓存中,所以中继日志的开销很小。

        此外,在master中也有一个工作线程:和其它MySQL的连接一样,slave在master中打开一个连接也会使得master开始一个线程。复制 过程有一个很重要的限制——复制在slave上是串行化的,也就是说master上的并行更新操作不能在slave上并行操作。



具体配置


环境

MySQL-master1:10.6.1.210

MySQL-master2:10.6.1.211

OS版本:CentOS 6.6
MySQL版本:5.1.73 


一、MySQL master-master配置


1.1、修改MySQL配置文件


两台MySQL均如要开启binlog日志功能,开启方法:在MySQL-master1  配置文件/etc/my.cnf 


 [MySQLd]段中加上

user = mysql
log-bin=mysql-bin

binlog_format=mixed

relay-log=relay-bin

server-id = 1
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates=1     (log-slave-updates也可以)
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1 


在MySQL-master2 配置文件/etc/my.cnf 


 [MySQLd]段中加上

user = mysql
log-bin=mysql-bin

binlog_format=mixed
relay-log=relay-bin

server-id=10
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates=1   (log-slave-updates也可以)

slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2


将两个配置文件保存,分别重启mysql服务器


讲解几个重要的常用选项:


server_id

值必须为2到232–1之间的一个正整数值。ID值唯一的标识了复制群集中的主从服务器,因此它们必须各不相同。


binlog-do-db=database 

是要记录日志的数据库;


同步多个数据库重复设置选项 binlog-do-db=test 和replicate-do-db=test

例如                              

 binlog-do-db=test1

 replicate-do-db=test1

 binlog-do-db=test2 

 replicate-do-db=test2


binlog-ignore-db 

不要记录日志的数据库名,多个数据库中间用逗号(,)隔开;

 

log-slave-updates=1 

这个参数用来配置从服务器的更新是否写入二进制日志,这个选项默认是不打开的,但是,如果这个从服务器B是服务器A的 从服务器,同时还作为服务器C的主服务器,那么就需要开发这个选项,这样它的从服务器C才能获得它的二进制日志进行同步操作


sync_binlog=1

  这个参数直接影响mysql的性能和完整性. 在进行n次事务提交以后,Mysql将执行一次fsync之类的磁盘同步指令,同志文件系统将Binlog文件缓存刷新到磁盘。Mysql中默认的设置是sync_binlog=0,即不作任何强制性的磁盘刷新指令,这时性能是最好的,但风险也是最大的。一旦系统Crash,在文件系统缓存中的所有Binlog信息都会丢失



auto_increment_increment=2
auto_increment_offset=1 


auto_increment字段产生的数值是:1, 3, 5, 7, …等奇数ID了


auto_increment_increment = 2 

auto_increment_offset = 2 

auto_increment字段产生的数值是:2, 4, 6, 8, …等偶数ID了


这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。


另外:

master-connect-retry


master-connect-retry这个参数是用来设置在和主服务器连接丢失的时候,重试的时间间隔,默认是60秒


read-only

read-only是用来限制普通用户对从数据库的更新操作,以确保从数据库的安全性,不过如果是超级用户依然可以对从数据库进行更新操作


slave-skip-errors


在复制过程中,由于各种的原因,从服务器可能会遇到执行BINLOG中的SQL出错的情况,在默认情况下,服务器会停止复制进程,不再进行同步,等到用户自行来处理。

Slave-skip-errors的作用就是用来定义复制过程中从服务器可以自动跳过的错误号,当复制过程中遇到定义的错误号,就可以自动跳过,直接执行后面的SQL语句。


skip-slave-start

表示从mysql服务器启动时不启动同步线程,这就要在启动从服务器之后,手工启动同步线程



1.2  做同步之前要保证两个数据库数据一致.


如果主数据库有数据的话。 数据库锁表操作,不让数据再进行写入动作。

mysql> FLUSH TABLES WITH READ LOCK;


用命令mysqldump备份数据库。

master服务器执行

shell> mysqldump -uroot -p123456 test >test.sql

看主数据库的状态 mysql> show master status;

记录File  Position 项的值

注:没有锁定主服务器,这里记录的主服务器二进制日志position值可能会大于做mysqldump时的值,这将导致从服务器丢失在此期间的更新。如果可以保证在此期间主服务器不会出现创建新表的更新,那么丢失的影响不大;否则,将导致从服务器复制线程失败,这时必须在做mysqldump时锁定主服务器。

-----------------------------------------------

从服务器中my.cnf文件中加入选项 

skip-slave-start

表示从mysql服务器启动时不启动同步线程,这就要在启动从服务器之后,手工启动同步线程,在mysql> 提示符下面运行“start slave”就可以

保存my.cnf


执行

shell> mysqladmin -uroot -p123456 create test

shell> mysql -uroot -p123456 test <test.sql


启动从服务器线程

mysql>start slave;

取消主数据库锁定 mysql>UNLOCK TABLES;





1.3、将10.6.1.210设为10.6.1.211的主服务器


在10.6.1.210上新建授权用户 

MySQL> grant  replicationclient,replication  slave  on  *.*   to  'repluser'@'10.6.1.211'  identified by  '123456';

Query OK, 0 rows affected (0.00 sec)  


MySQL>flush privileges;
Query OK, 0 rows affected (0.00 sec)


查看Master数据库上的bin文件以及时间点. 登录Master服务器的mysql  后执行:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000015 |     2474 | test         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


此处,bin文件为mysql-bin.000015,节点为2474。



在10.6.1.211将10.6.1.210设为自己的主服务器


MySQL> change master to master_host='10.6.1.210',master_user=' repluser ',master_password='123456',master_log_file='MySQL-bin.000015',master_log_pos=2474;
Query OK, 0 rows affected (0.05 sec) 


红色标注部分为10.6.1.210主机中show master status;命令结果中的file、postion两个值 


MySQL> start slave;
Query OK, 0 rows affected (0.00 sec) 

  

MySQL> show slave status/G


Slave_IO_Running:Yes            #IO thread  是否运行

Slave_SQL_Running:Yes         #SQL thread是否运行


 


1.4、将10.6.1.211设为10.6.1.210的主服务器


10.6.1.211上新建授权用户 

MySQL> grant  replicationclient,replication  slave  on  *.*   to 'repluser'@'10.6.1.210'  identified by  '123456';

Query OK, 0 rows affected (0.00 sec) 

  

 MySQL>flush privileges;
Query OK, 0 rows affected (0.00 sec)


查看Master数据库上的bin文件以及时间点. 登录Master服务器的mysql  后执行:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |     1476 | test         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


此处,bin文件为mysql-bin.000004,节点为1476。




10.6.1.21010.6.1.211设为自己的主服务器

MySQL> change master to master_host='10.6.1.211',master_user=' repluser ',master_password='123456',master_log_file='MySQL-bin.000004',master_log_pos=1476;
Query OK, 0 rows affected (0.05 sec)  

MySQL> start slave;
Query OK, 0 rows affected (0.00 sec)   

MySQL>

MySQL> show slave status/G


Slave_IO_Running:Yes            #IO thread  是否运行

Slave_SQL_Running:Yes         #SQL thread是否运行



1.5 、其他命令

1.5.1
、查看复制进度
需要在主库上运行
mysql>show processlist \G;

1.5.2、主服务器上的相关命令:
show processlist;
show master status
show slave hosts
show {master|binary} logs
show binlog events
purge {master|binary} logs to 'log_name'
purge {master|binary} logs before 'date'
reset master(
老版本flush master)
set sql_log_bin={0|1}

1.5.3
、从服务器上的相关命令:
slave start
slave stop
slave stop IO_THREAD //
此线程把master段的日志写到本地
slave start IO_THREAD
slave stop SQL_THREAD //
此线程把写到本地的日志应用于数据库
slave start SQL_THREAD
reset slave
set global sql_slave_skip_counter
load data from master
show slave status(SUPER,REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //
动态改变master信息
PURGE MASTER [before 'date'] 
删除master端已同步过的日志


本文转自pizibaidu 51CTO博客,原文链接:http://blog.51cto.com/pizibaidu/1791791,如需转载请自行联系原作者


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
69
分享
相关文章
JDK、Tomcat、MariaDB数据库和Profile多环境的配置与使用
以上就是JDK、Tomcat、MariaDB数据库和Profile多环境的配置与使用的基本步骤。这些步骤可能会因为你的具体需求和环境而有所不同,但是基本的思路是一样的。希望这些信息能够帮助你更好地理解和使用这些工具。
44 17
CentOS 7系统下详细安装MySQL 5.7的步骤:包括密码配置、字符集配置、远程连接配置
以上就是在CentOS 7系统下安装MySQL 5.7的详细步骤。希望这个指南能帮助你顺利完成安装。
129 26
YashanDB异构数据库链接配置
本指南介绍在YashanDB中配置异构数据库链接(DBLINK)的方法,特别是连接至Oracle数据库的前置要求与步骤。需确保YashanDB服务端安装plugin插件包、Oracle Instant Client及libaio库,否则可能导致错误或进程崩溃。文档还提供了Oracle Instant Client和libaio库的具体安装指导,帮助管理员顺利完成配置。
YashanDB数据库服务端SSL连接配置
YashanDB支持通过SSL连接确保数据传输安全,需在服务端生成根证书、服务器证书及DH文件,并将根证书提供给客户端以完成身份验证。服务端配置包括使用OpenSSL工具生成证书、设置SSL参数并重启数据库;客户端则需下载根证书并正确配置环境变量与`yasc_env.ini`文件。注意:启用SSL后,所有客户端必须持有根证书才能连接,且SSL与密码认证独立运行。
MySQL 备份 Shell 脚本:支持远程同步与阿里云 OSS 备份
一款自动化 MySQL 备份 Shell 脚本,支持本地存储、远程服务器同步(SSH+rsync)、阿里云 OSS 备份,并自动清理过期备份。适用于数据库管理员和开发者,帮助确保数据安全。
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】数据库一主一备部署及一主两备部署时,主备手动切换方法及自动切换配置
【YashanDB知识库】数据库一主一备部署及一主两备部署时,主备手动切换方法及自动切换配置
【YashanDB知识库】数据库一主一备部署及一主两备部署时,主备手动切换方法及自动切换配置
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。

推荐镜像

更多