如何配置MySQL数据库主从复制

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

　　为什么使用主从复制？

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

　　2、通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的客户响应时间。但是不要同时在主从服务器上进行更新，这样可能引起冲突。

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

　　MySQL使用3个线程来执行复制功能(其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让主服务器发送二进制日志。主服务器创建一个线程将二进制日志中的内容发送到从服务器。从服 务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。第3个线程是SQL线程，从服务器使用此线程读取中继日志并执行日 志中包含的更新。SHOW PROCESSLIST语句可以查询在主服务器上和从服务器上发生的关于复制的信息。

　　默认中继日志使用host_name-relay-binnnnnnn形式的文件名，其中host_name是从服务器主机名，nnnnnn是序 列号。用连续序列号来创建连续中继日志文件，从000001开始。从服务器跟踪中继日志索引文件来识别目前正使用的中继日志。默认中继日志索引文件名为 host_name-relay-binindex。在默认情况，这些文件在从服务器的数据目录中被创建。中继日志与二进制日志的格式相同，并且可以用 mysqlbinlog读取。当SQL线程执行完中继日志中的所有事件后，中继日志将会被自动删除。

　　从服务器在数据目录中另外创建两个状态文件--masterinfo和relay-loginfo。状态文件保存在硬盘上，从服务器关闭时不会丢失。下次从服务器启动时，读取这些文件以确定它已经从主服务器读取了多少二进制日志，以及处理自己的中继日志的程度。

　　设置主从复制：

　　1、确保在主服务器和从服务器上安装的MySQL版本相同，并且最好是MySQL的最新稳定版本。

　　2、在主服务器上为复制设置一个连接账户。该账户必须授予REPLICATION SLAVE权限。如果账户仅用于复制(推荐这样做)，则不需要再授予任何其它权限。

　　mysql> GRANT REPLICATION SLAVE ON

　　-> TO 'replication'@'%yourdomaincom' IDENTIFIED BY 'slavepass';

　　3、执行FLUSH TABLES WITH READ LOCK语句清空所有表和块写入语句：

　　mysql> FLUSH TABLES WITH READ LOCK；

　　保持mysql客户端程序不要退出。开启另一个终端对主服务器数据目录做快照。

　　shell> cd /usr/local/mysql/

　　shell> tar -cvf /tmp/mysql-snapshottar /data

　　如果从服务器的用户账户与主服务器的不同，你可能不想复制mysql数据库。在这种情况下，应从归档中排除该数据库。你也不需要在归档中包括任何日志文件或者masterinfo或relay-loginfo文件。

　　当FLUSH TABLES WITH READ LOCK所置读锁定有效时（即mysql客户端程序不退出)，读取主服务器上当前的二进制日志名和偏移量值：

　　mysql > SHOW MASTER STATUS;

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

　　| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

　　| mysql-bin003 | 73 | test | manual,mysql |

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

　　File列显示日志名，而Position显示偏移量。在该例子中，二进制日志值为mysql-bin003，偏移量为73。记录该值。以后设置从服务器时需要使用这些值。它们表示复制坐标，从服务器应从该点开始从主服务器上进行新的更新。

　　如果主服务器运行时没有启用--logs-bin，SHOW MASTER STATUS显示的日志名和位置值为空。在这种情况下，当以后指定从服务器的日志文件和位置时需要使用的值为空字符串('')和4

　　取得快照并记录日志名和偏移量后，回到前一中端重新启用写活动：

　　mysql> UNLOCK TABLES；

　　4、确保主服务器主机上mycnf文件的[mysqld]部分包括一个log-bin选项。该部分还应有一个server-id=Master_id选项，其中master_id必须为1到232–1之间的一个正整数值。例如：

　　[mysqld]

　　log-bin

　　server-id=1

　　如果没有提供那些选项，应添加它们并重启服务器。

　　5、停止从服务器上的mysqld服务并在其mycnf文件中添加下面的行：

　　[mysqld]

　　server-id=2

　　slave_id值同Master_id值一样，必须为1到232–1之间的一个正整数值。并且，从服务器的ID必须与主服务器的ID不相同。

　　6、将数据备据目录中。确保对这些文件和目录的权限正确。服务器 MySQL运行的用户必须能够读写文件，如同在主服务器上一样。

　　Shell> chown -R mysql:mysql /usr/local/mysql/data

　　7、启动从服务器。在从服务器上执行下面的语句，用你的系统的实际值替换选项值：

　　mysql> CHANGE MASTER TO

　　-> MASTER_HOST='master_host_name',

　　-> MASTER_USER='replication_user_name',

　　-> MASTER_PASSWORD='replication_password',

　　-> MASTER_LOG_FILE='recorded_log_file_name',

　　-> MASTER_LOG_POS=recorded_log_position;

　　8、启动从服务器线程：

　　mysql> START SLAVE；

　　执行这些程序后，从服务器应连接主服务器，并补充自从快照以来发生的任何更新。

　　9、如果出现复制错误，从服务器的错误日志（HOSTNAMEerr）中也会出现错误消息。

　　10、从服务器复制时，会在其数据目录中发现文件masterinfo和HOSTNAME-relay-loginfo。从服务器使用这两个文 件跟踪已经处理了多少主服务器的二进制日志。不要移除或编辑这些文件，除非你确切知你正在做什么并完全理解其意义。即使这样，最好是使用CHANGE MASTER TO语句。

先在主数据库中创建新数据库rep_test。

然后编辑主数据库的myini文件

在[mysqld]节点中增加如下内容：

server-id=1 #指定唯一的ID，1至32，必须的

log-bin=mysql-log-bin #指定二进制日志存放路径，必须的

binlog-do-db=rep_test #指定要同步的数据库，必须的

#binlog-ignore-db=mysql #指定不要同步的数据库，如果指定了binlog-do-db就不用再指定该项

重启主数据库，然后在主数据库中建立一个备份账户

mysql>grant replication slave on to slave@1921681128 identified by 'slave' ;

mysql>flush privileges;

PS:identified by 指定的slave是账号slave@1921681128 的密码

显示主服务器的状态信息，并且找到File 和 Position 的值记录下来；

mysql>show master status;       

在从数据库中创建新的数据库rep_test。

然后编辑从数据库的myini文件

在[mysqld]节点中增加如下内容：

server-id=2 #指定唯一的ID，2至32，必须的，并且不能跟主数据库一样

replicate-do-db=rep_test #指定要同步的数据库，必须的

#replicate-ignore-db=mysql #指定不要同步的数据库，

重启从数据库，设置登录主数据库的账号和密码等信息，然后启动slave

mysql>change master to master_host='19216812',master_user='slave',master_password='slave', master_log_file='mysql-bin000002',master_log_pos=120; 

mysql>start slave;

查看从数据库的信息

mysql>show slave status \G;

如果出现： Slave_IO_Running: YesSlave_SQL_Running: Yes以上两项都为Yes，那说明没问题了

测试主从复制是否有效果

在主数据库中创建一个新的数据库，然后再切换到从数据库查看是否同样多出通名的数据库

配置旧数据库的主从复制

如果一开始数据库的架构不是主从复制，并且运行一段时间后已经有数据存在，那配置的方式略有不同。

编辑主数据库的myini文件，加上一下内容：

binlog-do-db=landclash

重启主数据库，然后在主数据库中锁定所有的表

mysql>flush tables with read lock；

显示主服务器的状态信息，并且找到File 和 Position 的值记录下来；

mysql>show master status;       

将主数据库data目录下需要做主从复制的数据库的同名目录拷贝到从数据库的data目录下

编辑从数据库的myini文件，加上一下内容：

replicate-do-db=landclash

重启从数据库，因为主数据库在重新配置myini后，日志文件变成新的文件，所以需要再次设置登录主数据库的账号和密码等信息

mysql>stop slave;

mysql>change master to master_host='19216812',master_user='slave',master_password='slave', master_log_file='mysql-bin000003',master_log_pos=120; 

mysql>start slave;

再次输入查看从数据库状态的命令

mysql>show slave status \G;

完成上述配置后，回到主数据库，将表解锁

mysql>unlock tables;

之后在主数据库的修改就能同步到从数据库上了。

为了保障数据的安全与稳定性，我们常用数据库的主从复制与主主复制来实现。主从复制为从机实时拷贝一份主机的数据，当主机有数据变化时，从机的数据会跟着变，当从机数据有变化时，主机数据不变；同样地，主主复制就是，多个主机之间，只要有一个主机的数据变化了，其它主机数据也会跟着变化。

添加以下内容

如果你是使用我之前那种方式启动的MySQL，那么你只需要去你相关联的宿主机的配置文件夹里面去建立一个 mycnf 然后写入上面的类容就好了。

比如：我的启动命令如下（不应该换行的，这里为了方便查看，我给它分行了）

那么我只需要在 /docker/mysql_master/conf 这个目录下创建 mycnf 文件就好了。

这个命令是需要在容器里面执行的

docker重启mysql会关闭容器，我们需要重启容器。

确保在主服务器上 skip_networking 选项处于 OFF 关闭状态, 这是默认值。 如果是启用的，则从站无法与主站通信，并且复制失败。

我的命令如下

在从服务器配置连接到主服务器的相关信息 （在容器里面的mysql执行）

上面代码的xxxxx你需要换成你的IP，docker 查看容器 IP 的命令如下：

启动的那个从服务器的线程

测试的话，你可以在主服务器里面，创建一个数据库，发现从服务器里面也有了，就成功了。

如果你还想要一个从服务器，那么你只需要按照上面配置从服务器再配置一个就行了，新建的从服务器，会自动保存主服务器之前的数据。（测试结果） 如果你上面的主从复制搞定了，那么这个主主复制就很简单了。我们把上面的从服务器也改成主服务器

1）、修改上面的从服务器的mycnf文件，和主服务器的一样（注意这个server-id不能一样）然后重启服务器 2）、在从服务器里面创建一个复制用户创建命令一样（这里修改一下用户名可以改为 repl2） 3）、在之前的主服务器里面运行下面这个代码

上面主要是教你怎么搭建一个MySQL集群，但是这里面还有很多其它的问题。也是我在学习过程中思考的问题，可能有的小伙伴上来看到文章长篇大论的看不下去，只想去实现这样一直集群功能，所以我就把问题写在下面了。

1）、MySQL的replication和pxc MySQL的集群方案有replication和pxc两种，上面是基于replication实现的。

replication: 异步复制，速度快，无法保证数据的一致性。 pxc: 同步复制，速度慢，多个集群之间是事务提交的数据一致性强。

2）、MySQL的replication数据同步的原理 我们在配置的时候开启了它的二进制日志，每次操作数据库的时候都会更新到这个日志里面去。主从通过同步这个日志来保证数据的一致性。

3）、可否不同步全部的数据 可以配置，同步哪些数据库，甚至是哪些表。

4）、怎么关闭和开始同步

5）、我就我的理解画出了，主从、主从从、主主、复制的图。

往期推荐：

利用Docker仅花1分钟时间安装好MySQL服务

Linux下MySQL 57的离线与在线安装（图文）

Linux下安装MySQL80（收藏！）

一、 概述

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

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

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的权限才可以。

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

create database test;

、创建一个表结构：

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

、修改配置文件：

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

server-id = 1 #Server标识

log-bin

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

、重起数据库服务：

service mysqld restart

查看server-id：

show variable like ‘server_id’；

还好MySQL数据库提供了一种主从备份的机制，其实就是把主数据库的所有的数据同时写到备份数据库中。实现MySQL数据库的热备份。 2、要想实现双机的热备首先要了解主从数据库服务器的版本的需求。要实现热备MySQL的版本都要高于32，还有一个基本的原则就是作为从数据库的数据库版本可以高于主服务器数据库的版本，但是不可以低于主服务器的数据库版本。 3、设置主数据库服务器： a首先查看主服务器的版本是否是支持热备的版本。然后查看mycnf(类Unix)或者myini(Windows)中MySQLd配置块的配置有没有log-bin(记录数据库更改日志)，因为MySQL的复制机制是基于日志的复制机制，所以主服务器一定要支持更改日志才行。然后设置要写入日志的数据库或者不要写入日志的数据库。这样只有您感兴趣的数据库的更改才写入到数据库的日志中。 server-id=1 //数据库的id这个应该默认是1就不用改动 log-bin=log_name //日志文件的名称， //这里可以制定日志到别的目录 如果没有设置则默认主机名的一个日志名称 binlog-do-db=db_name //记录日志的数据库 binlog-ignore-db=db_name //不记录日志的数据库以上的如果有多个数据库用","分割开 然后设置同步数据库的用户账号 MySQL> GRANT REPLICATION SLAVE ON -> TO 'repl'@'%mydomaincom' IDENTIFIED BY 'slavepass';402以前的版本, 因为不支持REPLICATION 要使用下面的语句来实现这个功能 MySQL> GRANT FILE ON -> TO 'repl'@'%mydomaincom' IDENTIFIED BY 'slavepass';设置好主服务器的配置文件后重新启动数据库 b锁定现有的数据库并备份现在的数据 锁定数据库 MySQL> FLUSH TABLES WITH READ LOCK;备份数据库有两种办法一种是直接进入到MySQL的data目录然后打包你需要备份数据库的文件夹，第二种是使用MySQLdump的方式来备份数据库但是要加上"--master-data " 这个参数，建议使用第一种方法来备份数据库