MySQL数据库备份之复制

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

MySQL数据库备份之复制
目录:
1.MySQL复制简介
2.MySQL复制的优势与思路
3.数据复制环境之主服务器设置
4.数据复制环境之从服务器设置
5.创建复制帐号
6.获取主服务器二进制日志信息
7.对现有数据库进行快照备份
8.配置从服务器连接主服务器进行数据复制
9.数据同步验证

1. MySQL
复制

使用MySQL复制功能可以将主服务器上的数据复制到多台从服务器上。默认情况下复制是异步传输方式,从服务器不需要总是连接主服务器去更新数据。也就是说数据更新可以在远距离连接的情况下进行,甚至是在使用拨号网络的临时连接环境下也可以进行。根据自定义设置,我们可以对所有数据库或部分数据库甚至是部分数据表进行复制。通过主从复制,在企业级应用环境中就不必再担心数据库的单点故障,当一台服务器宕机时其他服务器一样可以提供非常稳定可靠的数据服务。

2. MySQL复制的优势:

  • 高性能:通过将请求分配给多台不同的从服务器提高性能与速度。在这种环境中,所有对数据库的写操作必须提供给主服务器,但读操作可以被平均地分配给多台从服务器。

  • 数据安全:数据是从主服务器复制到从服务器的,而且从服务器可以随时暂停复制,这样我们就实现数据备份与还原。

  • 远程数据分享:如果企业拥有多处位于其他地理位置的分公司,而这些分公司希望共享总部的数据库资源,就可以使用复制实现数据的共享,而且分公司不必时时连接主服务器,可以仅在需要时进行复制。

MySQL复制有两种核心的格式,一种是基于SQL语句的复制(SBRStatement Based Replication),另一种是基于行的复制(RBRRow BasedReplication),另外有时也可以使用混合模式复制。MySQL 5.6默认采用的是基于语句的复制。

MySQL服务器之间的复制是基于二进制日志机制。在主服务器上当MySQL实例进行写操作时会同时生成一条操作事件日志并写入二进制日志文件中,而从服务器则负责读取主服务器上的二进制日志,并在从服务器本机重新执行该事件,从而实现复制数据至本地服务器。当主服务器开启了二进制日志功能后,所有的SQL语句都将被记录至日志。而从服务器复制这些二进制日志的条目,并且根据自己的需要决定哪些语句需要重新在从服务器执行,而哪些语句不需要再执行。我们无法控制主服务器仅记录特定语句到日志文件。如果没有进行其他设置,所有主服务器中的日志事件都将在从服务器上重新执行,当然你可以配置从服务器仅执行主服务器中的一部分日志事件。因为从服务器会记录二进制日志的进度与位置(比如执行到了第几条语句),所以从服务器可以断开与主服务器之间的连接,并在重新建立连接后继续进行复制工作。

在具体操作过程中,主服务器与从服务器都需要配置一个唯一的ID编号。另外,所有的从服务器还必须设置主服务器的主机名、日志文件名、文件位置等参数。

一步一步操作MySQL复制

本节我们将演示如何一步一步实现MySQL服务器之间的数据复制(演示环步骤中命令提示符为master则代表指令在MySQL主服务器上运行,提示符为slave1则代表指令在MySQL从服务器上运行)。整个过程一般分为以下几个步骤:

  • 在主服务器上开启二进制日志功能,设置唯一的服务器ID编号。这些设置需要重启MySQL服务。

  • 在所有从服务器上设置唯一的服务器ID编号,这些设置需要重启MySQL服务。

  • 在主服务器上为不用的从服务器创建可以读取主服务器日志文件的账户,或使用相同的统一账户。

  • 在进行数据复制之前,你还需要记录主服务器上二进制日志的位置标记。

3.数据复制环境之主服务器设置

在实际的生产环境中,可能在我们还没有部署数据复制前,数据库中就已经存在大量数据,所以,这里我们事先创建一个测试用数据库及数据表,用来演示如何对已经存在的数据进行数据同步备份。

1
2
3
4
5
6
7
8
9
10
11
12
[root@master ~]# mysql -u root -p
mysql> create database hr;
mysql>  use  hr;
mysql> create table employees(
-> employee_id INT NOT NULL AUTO_INCREMENT,
-> name char( 20 ) NOT NULL,
-> e_mail  var char( 50 ),
-> PRIMARY KEY(employee_id));
mysql> INSERT INTO employees values
->( 1 , 'TOM' , 'tom@example.com' ),
->( 2 , 'Jerry' , 'jerry@example.com' );
mysql> exit

我们需要在主服务器上开启二进制日志并设置服务器编号,服务器唯一编号必须是1232-1之间的整数,根据自己的实际情况进行设置。进行这些设置需要关闭MySQL数据库并编辑my.cnfmy.ini文件,并在[mysqld]设置段添加相应的配置选项。关于MySQL软件的安装这里不再赘述,请参考4.11.2章节内容。

[root@master ~]# vim /etc/my.cnf

[mysqld]

log-bin=Jacob-bin#启用二进制日志,并设置二进制日志文件前缀

server-id=254#设置服务器编号

[root@master ~]# service mysqld restart

[root@master ~]# service iptables stop

l注意:在配置文件中不可以使用skip-networking参数选项,否则从服务器将无法与主服务器进行连接并复制数据。

4.数据复制环境之从服务器设置

如果从服务器ID编号没有设置,或服务器ID编号与主服务器有冲突,就必须关闭MySQL服务并重新编辑配置文件,设置唯一的服务器编号,最后重启MySQL服务。如果有多台从服务器,则所有的服务器ID编号都必须是唯一的。可以考虑将服务器ID编号与服务器IP地址关联,这样ID编号同时可以唯一标识一台服务器计算机,如采用IP地址的最后一位作为MySQL服务器ID编号。

[root@slave1 ~]# vim /etc/my.cnf

[mysqld]

server-id=2

[root@slave1 ~]# service mysqld restart

[root@slave1 ~]# service iptables stop

对于复制而言,MySQL从服务器上二进制日志功能是不需要开启的。但是,你也可以通过启用从服务器的二进制日志功能,实现数据备份与恢复,此外在一些更复杂的拓扑环境中,MySQL从服务器也可以扮演其他从服务器的主服务器。

5.创建复制帐号

执行数据复制时,所有的从服务器都需要使用账户与密码连接MySQL主服务器,所以在主服务器上必须存在至少一个用户账户及相应密码供从服务器连接。这个账户必须拥有REPLICATION SLAVE权限,你可以为不同的从服务器创建不同的账户与密码,也可以使用统一的账户与密码。MySQL可以使用CREATE USER语句创建用户,使用GRANT语句为账户赋权。如果该用户仅为数据库复制所使用,则该账户仅需要REPLICATION SLAVE权限即可。下面的例子将在MySQL主服务器上创建一个拥有复制权限的slave_cp账户,该账户可以从example.com域内的任何主机连接主服务器,密码为SlaveAdmin

1
2
3
4
[root@master ~]# mysql -u root -p
mysql> CREATE USER  'slave_cp' @ '%.example.com'  IDENTIFIED BY  'SlaveAdmin' ;
mysql> GRANT REPLICATION SLAVE ON *.* TO  'slave_cp' @ '%.example.com' ;
mysql> exit

6.获取主服务器二进制日志信息

在进行主从数据复制之前我们了解一些主服务器的二进制日志文件的基本信息,这些信息在对从服务器的设置中需要用到,这些信息包括主服务器二进制文件名称及当前日志记录位置,这样从服务器就可以知道从哪里开始进行复制操作。我们可以使用如下操作查看主服务器二进制日志数据信息:

1
2
3
4
5
6
7
8
9
[root@master ~]# mysql -u root -p
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+--------------------------------+------------+---------------------+-------------------------+--------------------------+
| File|Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------------------+------------+---------------------+-------------------------+--------------------------+
| jacob-log. 000001 | 1276 ||||
+--------------------------------+------------+---------------------+--------------------------+-------------------------+
mysql> UNLOCK TABLES;

其中,File列显示的是二进制日志文件名,Position为当前日志记录位置。

FLUSH TABLES WITH READ LOCK命令的作用是对所有数据库的所有表执行只读锁定,只读锁定后所有数据库的写操作将被拒绝,但读操作可以继续。执行锁定可以防止在查看二进制日志信息的同时有人对数据进行修改操作,最后使用UNLOCK TABLES语句对全局锁执行结束操作。

7.对现有数据库进行快照备份

如果在使用二进制日志进行数据复制以前,MySQL数据库系统中已经存在大量数据资源,对这些资料进行数据备份的一种方法使用使用mysqldump工具,在主服务器上使用该工具对数据备份后即可在从服务器上进行数据还原操作。当希望的数据达到主从一致后,就可以使用数据复制功能进行自动从此同步操作。具体操作如下(作者的环境中主服务器IP地址为172.16.0.254,从服务器IP地址为172.16.0.1),实际生产环境中需要根据自己的需要有选择的对数据库进行备份与还原:

1
2
3
[root@master ~]# mysqldump --all-databases --lock-all-tables > /tmp/dbdump.sql
[root@master ~]# scp /tmp/dbdump.sql  172.16 . 0.1 :/tmp/
[root@slave1 ~]# mysql -u root -p <dbdump.sql

8.配置从服务器连接主服务器进行数据复制

数据复制的关键操作是配置从服务器去连接主服务器进行数据复制,我们需要告知从服务器建立网络连接所有必要的信息。使用CHANGE MASTER TO语句即可完成该项工作,MASTER_HOST指定主服务器主机名或IP地址,MASTER_USER为主服务器上创建的拥有复制权限的账户名称,MASTER_PASSWORD为该账户的密码,MASTER_LOG_FILE指定主服务器二进制日志文件名称,MASTER_LOG_POS为主服务器二进制日志当前记录的位置。START SLAVE开启从服务器功能进行主从连接,SHOW SLAVE STATUS查看从服务器状态。

1
2
3
4
5
6
7
8
9
[root@slave1 ~]# mysql -u root -p
mysql> CHANGE MASTER TO
-> MASTER_HOST= '172.16.0.155' ,
-> MASTER_USER= 'data_cp' ,
-> MASTER_PASSWORD= 'SlaveAdmin' ,
-> MASTER_LOG_FILE= 'jacob-log.000001' ,
-> MASTER_LOG_POS= 1351 ;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;

9.数据同步验证

所有主从均设置完毕后,我们可以通过在主服务上创建新的数据资料,然后在从服务器上查看,所有数据将自动同步。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[root@master ~]# mysql -u root -p
mysql> create database test2;
mysql>  use  test2;
mysql> create table t_table(
->name char( 20 ),
-> age  int ,
-> note  var char( 50 ));
mysql> INSERT INTO t_table values
-> ( 'linda' , 23 'Beijing' ),
-> ( 'jerry' , 33 'shanghai' ),;
mysql> exit
[root@slave1 ~]# mysql -u root -p
mysql> select * from test2.t_table;
mysql> exit























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

相关文章
|
12天前
|
Prometheus Cloud Native 关系型数据库
实时计算 Flink版产品使用合集之binlog被清理掉的问题,并且binlog有备份,有什么方法来恢复到RDS
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
14天前
|
存储 JSON 关系型数据库
Percona XtraBackup是否支持Elasticsearch数据库备份?
【5月更文挑战第13天】Percona XtraBackup是否支持Elasticsearch数据库备份?
15 1
|
14天前
|
NoSQL 关系型数据库 MySQL
Percona XtraBackup是否支持Redis数据库备份?
【5月更文挑战第13天】Percona XtraBackup是否支持Redis数据库备份?
16 2
|
14天前
|
存储 NoSQL 关系型数据库
Percona XtraBackup是否支持MongoDB数据库备份?
【5月更文挑战第13天】Percona XtraBackup是否支持MongoDB数据库备份?
41 1
|
14天前
|
存储 关系型数据库 MySQL
Percona XtraBackup是否支持PostgreSQL数据库备份?
【5月更文挑战第13天】Percona XtraBackup是否支持PostgreSQL数据库备份?
57 1
|
14天前
|
关系型数据库 MySQL Linux
服务器Linux系统配置mysql数据库主从自动备份
这是一个基本的配置主从复制和设置自动备份的指南。具体的配置细节和命令可能因您的环境和需求而有所不同,因此建议在操作前详细阅读MySQL文档和相关资源,并谨慎操作以避免数据丢失或不一致。
34 3
|
14天前
|
关系型数据库 MySQL 数据库连接
用Navicat备份Mysql演示系统数据库的时候出:Too Many Connections
用Navicat备份Mysql演示系统数据库的时候出:Too Many Connections
48 0
|
14天前
|
SQL 存储 监控
关系型数据库做好备份
【5月更文挑战第4天】关系型数据库做好备份
39 6
关系型数据库做好备份
|
14天前
|
关系型数据库 MySQL 数据管理
MySQL通过 bin-log 恢复从备份点到灾难点之间数据
MySQL通过 bin-log 恢复从备份点到灾难点之间数据
200 0
|
14天前
|
存储 安全 关系型数据库
MySQL中使用percona-xtrabackup工具 三种备份及恢复 (超详细教程)
MySQL中使用percona-xtrabackup工具 三种备份及恢复 (超详细教程)