mysql主从同步如何同步已有的数据,(mysql数据库主从同步原理)

主从复制理论上支持无穷大的从库个数，实际情况下，受服务器带宽和读写能力的影响

请参考MySQL官方手册的建议：

理论上，通过使用单个主服务器/多从服务器设置，可以通过添加更多的从服务器来扩充系统，直到用完网络带宽，或者你的更新负载已经增长到主服务器不能处理的点。

在获得的收益开始吃平之前，为了确定可以有多少从服务器，以及可以将你的站点的性能提高多少，需要知道查询模式，并且要通过基准测试并根据经验确定一个典型的主服务器和从服务器中的读取（每秒钟读取量，或者max_reads）吞吐量和写（max_writes）吞吐量的关系。通过一个假设的带有复制的系统，本例给出了一个非常简单的计算结果。

假设系统负载包括10%的写和90%的读取，并且我们通过基准测试确定max_reads是1200_2×max_writes。换句话说，如果没有写 *** 作，系统每秒可以进行1,200次读取 *** 作，平均写 *** 作是平均读 *** 作所用时间的两倍，并且关系是线性的。我们假定主服务器和每个从服务器具有相同的性能，并且我们有一个主服务器和N个从服务器。那么，对于每个服务器（主服务器或从服务器），我们有：

reads=1200_2×writes

reads=9×writes/(N1)(读取是分离的,但是写入所有服务器)

9×writes/(N1)2×writes=1200

writes=1200/(29/(N1))

最后的等式表明了N个从服务器的最大写 *** 作数，假设最大可能的读取速率是每分钟1,200次，读 *** 作与写 *** 作的比率是9。

如上分析可以得到下面的结论：

·如果N=0（这表明没有复制），系统每秒可以处理大约1200/11=109个写 *** 作。

·如果N=1，每秒得到184个写 *** 作。

·如果N=8，每秒得到400个写 *** 作。

·如果N=17，每秒得到480个写 *** 作。

1、如果RDS上的数据没有发生增删改的 *** 作的话，可以生成物理备份或者逻辑备份，然后将物理备份和逻辑备份通过Xtrabackup或者mysqldump将数据导入到自建库。

2、如果在物理备份已经生成后对于数据库仍然有增删改的 *** 作的话，则需要您在恢复物理备份后还需要追加后续的binlog文件，可以通过API/控制台获取binlog文件去追加增量数据。

这里有一个非常需要注意的一点是在解压出来物理备份文件后除了数据和索引文件外还有得到如下图中的一些文件。

由于在物理备份上应用binlog是需要记录binlog的pos点的，所有的RDS都是采用主备架构保证高可用性的，为保证主库正常对外提供服务，备份 *** 作是在从库上执行的，但是生成出来的binlog文件却既有可能是主库或者从库上生成（两个binlog文件的pos点是不一致的）。提供给用户的也可能是这里面的任意一份，因此在物理备份文件中给用户提供了两个binlog文件的pos点，如下图：

用户在应用binlog的时候需要两个pos点都测试一下，哪个pos点可以在binlog中找到从这个pos点开始应用binlog。 

3、用户想要将RDS上的增量数据同步在自建库的binlog上可以使用DTS的数据订阅功能，可以通过SDK消费增量数据即可再自己应用该数据。

--主机开两个窗口，一个进入mysql，一个是shell

--主机阻断写 *** 作

mysql> FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected (000 sec)

mysql>

mysql>

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin000002 | 1529881 | openser | mysql,test |

+------------------+----------+--------------+------------------+

1 row in set (000 sec)

--另外一个窗口导出主机数据库

mysqldump -u root -p123456 --opt -R openser > openser20121203sql

--刚才的窗口主机解锁

mysql> UNLOCK TABLES;

Query OK, 0 rows affected (000 sec)

mysql>

--打包数据文件到从机

drop database openser;

create database openser;

mysql -u root -p123456 openser < openser20121127sql

--从机 *** 作

SLAVE STOP;

reset slave;

CHANGE MASTER TO MASTER_HOST='1921682126',

MASTER_USER='repl_user',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin000002',

MASTER_LOG_POS=1529881;

start slave;

show slave status\G

实现两个Mysql数据库之间同步同步原理：

MySQL 为了实现replication 必须打开bin-log 项，也是打开二进制的MySQL 日志记录选项。MySQL 的bin log 二

进制日志，可以记录所有影响到数据库表中存储记录内容的sql *** 作，如insert / update / delete *** 作，而不记录

select 这样的 *** 作。因此，我们可以通过二进制日志把某一时间段内丢失的数据可以恢复到数据库中（如果二进制日

志中记录的日志项，包涵数据库表中所有数据，那么， 就可以恢复本地数据库的全部数据了）。 而这个二进制日志，

如果用作远程数据库恢复，那就是replication 了。这就是使用replication 而不用sync 的原因。这也是为什么要设

置bin-log = 这个选项的原因。

在同步过程中，最重要的同步参照物，就是同步使用那一个二进制日志文件，从那一条记录开始同步。下面就介绍

下怎样实现两个Mysql数据库之间的主从同步。

一、 概述

MySQL从32315版本以后提供数据库复制（replication）功能，利用该功能可以实现两个数据库同步、主从模式、

互相备份模式的功能。本文档主要阐述了如何在linux系统中利用mysql的replication进行双机热备的配置。

二、 环境

*** 作系统：Linux 26231-42fc8 # SMP（不安装XEN）

Mysql版本：5045-4fc8

设备环境：PC（或者虚拟机）两台

三、 配置

数据库同步复制功能的设置都在MySQL的配置文件中体现，MySQL的配置文件（一般是mycnf）：在本环境下

为/etc/mycnf。

31 设置环境：

IP的设置：

A主机 IP：10100119

Mask：25525500

B主机 IP：10108112

Mask：25525500

在IP设置完成以后，需要确定两主机的防火墙确实已经关闭。可以使用命令service iptables status查看防火墙状态

。如果防火墙状态为仍在运行。使用service iptables stop来停用防火墙。如果想启动关闭防火墙，可以使用setup命

令来禁用或定制。

最终以两台主机可以相互ping通为佳。

32 配置A主(master) B从(slave)模式

321 配置A 为master

1、增加一个用户同步使用的帐号：

GRANT FILE ON TO ‘backup’@'10108112' IDENTIFIED BY ‘1234’;

GRANTREPLICATION SLAVE ON TO ‘backup’@'10108112' IDENTIFIED BY ‘1234’;

赋予10108112也就是Slave机器有File权限，只赋予Slave机器有File权限还不行，还要给它REPLICATION SLAVE的权

限才可以。

2、增加一个数据库作为同步数据库：

create databbse test;

3、创建一个表结构：

create table mytest (username varchar(20),password varchar(20));

4、修改配置文件：

修改A的/etc/mycnf文件，在mycnf配置项中加入下面配置：

server-id = 1 #Server标识

log-bin

binlog-do-db=test #指定需要日志的数据库

5、重起数据库服务：

service mysqld restart

查看server-id：

show variable like ‘server_id’；

通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的客户响应时间。SELECT查询可以发送到从服务器以降低主服务器的查询处理负荷。但修改数据的语句仍然应发送到主服务器，以便主服务器和从服务器保持同步。

如果非更新查询为主，该负载均衡策略很有效，但一般是更新查询。

主库配置：

1 修改 myini 文件

[mysqld]

log-bin = /var/lib/mysql/log-bin

binlog-do-db=lucia_test_abc

server-id = 1 

2 在主库创建新数据库

mysql> create database lucia_test_abc;

Query OK, 1 row affected (000 sec)

然后重启 mysql

3 添加 backup 账号并授权给从服务器

mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON  TO backup@’10211553’ IDENTIFIED BY '123456';

Query OK, 0 rows affected (001 sec)

ps 10211553 是从服务器的 IP 地址

4 查询主库状态 

mysql> show master status; 

记一下 FILE 及 Position 的值 后面配置从服务器的时候要用到

File：log-bin000003

Position：106

Binlog_Do_DB：lucia_test_abc

至此 主库配置完毕

从库配置：

1 在从库也创建数据库

库名字要和主库保持一致

2 修改 myini 文件

[mysqld]

server-id=2  

replicate-do-db= lucia_test_abc

保存退出，然后重启 mysql:

net stop mysql 

net start mysql

3 设置登录主数据库的账号和密码等信息

mysql>

 change master to 

master_host='10211556',master_user='root',master_password='123456', 

master_log_file='log-bin000003',master_log_pos=106;

启动从库：

mysql> start slave;

ps 

10211556 是主服务器的 IP 地址

master_log_file 就是主库的 File

master_log_pos 就是主库的 Position

4 查看从库的状态信息

mysql> show slave status \G;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

两个都是 Yes  至此 从库配置完毕

以上就是关于mysql主从同步如何同步已有的数据,(mysql数据库主从同步原理)全部的内容，包括:mysql主从同步如何同步已有的数据,(mysql数据库主从同步原理)、mysql如何实现两个数据库数据结构实时同步、mysql主从同步 如何同步已有的数据，等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！