深圳全飞鸿

标题: MYSQL数据库的同步建置(standby) [打印本页]
作者: zhgc    时间: 2019-9-11 23:22
标题: MYSQL数据库的同步建置(standby)
本帖最后由 zhgc 于 2020-11-12 21:49 编辑

MYSQL数据库的同步建置

配置过程：

1. 创建一个复制用户，具有replication slave 权限

  1. mysql>grant replication slave on *.* to 'repl'@'192.168.0.109' identified by 'repl';
  2. mysql>flush privileges;
复制代码




2. 编辑my.ini、my.cnf文件
找到server-id项，并修改，可以任意改一个，但不要是1，本例设为107
然后再设置配置项log-bin，值设为一个绝对路径，此值用于存储二进制日志文件。注意，例如此例中配置为D:\MysqlLogBin\log，意思是日志文件都放在D:\MysqlLogBin文件夹下，且日志文件都以log开头

配置效果如下:


设置binlog-do-db，值为要同步的数据库名，如果备份多个数据库，重复设置这个选项即可
设置log-slave-updates=1，这个参数一定要加上，否则不会给更新（可以理解为sql的update）的记录些到二进制文件
设置slave-skip-errors=1，意思是跳过错误，继续执行复制操作

其他可选配置：
binlog-ignore-db=xxx，意思是不需要备份的数据库名，如果备份多个数据库，重复设置这 个选项即可

配置效果如下：


重启master的mysql
在命令行输入：   show master status;

查看作为master的状态:

其中binlog-do-db就是刚才配置的binlog-do-db，而File和Position要记着，后面配置从库（slave）要用到


3. 配置slave
修改my.ini
配置server-id，可配置任意值，但不能是默认值1，也不能跟master的相同，本例配为109
效果如下：


配置replicate-do-db，意思是从库被同步的数据库，效果如下


重启mysql



打开mysql的命令行


开始在slave配置master的配置

输入：stop slave;
输入：（注意：以下的换行在命令行输入时真的要回车换行，有些行有逗号也真的要输入）
change master to
master_host='192.168.3.45',
master_user='repl',
master_password='repl',
master_log_file='log.000004',
master_log_pos=2910;

其中master_host是master的ip，master_user和master_password是master库用于同步的用户和密码，master_log_file和master_log_pos都是master的信息

输入时类似下图:


输入：
start slave;

重启数据库

到此配置完毕

在此可以用命令

show slave status\G;

看看作为slave的配置和状态，其中Slave_IO_Running和Slave_SQL_Running值应为Yes


完成！






注意事项：

1. 做了MySQL主从复制以后，使用mysqldump对数据备份时，一定要注意按照如下方式：

mysqldump --master-data --single-transaction --user=username --password=password dbname> dumpfilename

这样就可以保留 file 和 position 的信息，在新搭建一个slave的时候，还原完数据库， file 和 position 的信息也随之更新，接着再start slave 就可以很迅速

的完成增量同步！


2. 需要限定同步哪些数据库，有3个思路：

1）在执行grant授权的时候就限定数据库；

2）在主服务器上限定binlog_do_db = 数据库名；

3）主服务器上不限定数据库，在从服务器上限定replicate-do-db = 数据库名；
作者: zhgc    时间: 2019-9-12 00:04
本帖最后由 zhgc 于 2019-9-12 13:57 编辑

设置读锁

mysql>flush tables with read lock;

备份要同步的数据库

mysqldump test > test.sql

解锁

mysql>unlock tables;


flush tables with read lock:
关闭所有打开的表，同时对于所有数据库中的表都加一个读锁，
直到显示地执行unlock tables，
该操作常常用于数据备份的时候
作者: e54f    时间: 2019-9-12 14:16
MySQL的主从同步，又称为复制（replication），是一种内置的高可用高性能集群解决方案

主要功能有：
数据分布：同步不需要很大带宽，可以实现多数据中心复制数据。
读取的负载均衡：通过服务器集群，可以通过DNS轮询、Linux LVS等GSLB（全局负载均衡）方式，降低主服务器的读压力。
数据库备份：复制是备份的一部分，但并不能代替备份。还需要与快照相结合。
高可用性和故障转移：从服务器可以快速切换为主服务器，减少故障的停机时间和恢复时间。

主从同步分为3步：
主服务器（master）把数据更改记录到二进制日志（binlog）中。
从服务器（slave）把主服务器的二进制日志复制到自己的中继日志（relay log）中。
从服务器重做中继日志中的日志，把更改应用到自己的数据库上，达到数据的一致性。
主从同步是一个异步实时的同步，会实时的传输，但存在执行上的延时，如果主服务器压力很大，延时也会相应扩大。

通过上面的图，可以看到一共需要3个线程：
主服务器的日志传送线程：负责将二进制日志增量传送到备机
从服务器的I/O线程：负责读取主服务器的二进制日志，并保存为中继日志
从服务器的SQL线程，负责执行中继日志
作者: e54f    时间: 2019-9-12 14:34
本帖最后由 e54f 于 2019-9-12 14:36 编辑

做了MySQL主从复制以后，使用mysqldump对数据备份时，一定要注意按照如下方式：

mysqldump --master-data --single-transaction --user=username --password=password dbname> dumpfilename

加与不加，文档内容差异部分如下:

  1. --
  2. -- Position to start replication or point-in-time recovery from
  3. --

  5. CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000090', MASTER_LOG_POS=107;
复制代码


小结：

　　--master-data选项的作用就是将二进制的信息写入到输出文件中，在这里是写入到备份的sql文件中。



欢迎光临 深圳全飞鸿 (http://www.nagomes.com/disc/) Powered by Discuz! X3.2