27. 主从复制和数据库备份

主从复制

概述

提高数据库的并发能力，一方面可以使用Redis作为缓存数据库，另一方面，由于一般应用对数据库而言都是“读多写少”，可以采用数据库集群的方案，做主从架构、进行读写分离。

提升数据库高并发访问的效率，首先考虑的是如何优化SQL和索引，这种方式简单有效；其次才是采用缓存的策略，比如使用 Redis将热点数据保存在内存数据库中，提升读取的效率；最后才是对数据库采用主从架构，进行读写分离。

主从同步设计不仅可以提高数据库的吞吐量，还有以下 3 个方面的作用：

  1. 读写分离
  2. 数据备份
  3. 具有高可用性

原理

实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于3 个线程来操作，一个主库线程，两个从库线程。

d117aff5b9820d832894aacd175d7a52.png

二进制日志转储线程（Binlog dump thread）是一个主库线程。当从库线程连接的时候， 主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上加锁，读取完成之后，再将锁释放掉。

从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 （Relay log）。

从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

复制三步骤：

  1. Master将写操作记录到二进制日志（binlog）
  2. Slave将Master的binary log events拷贝到它的中继日志（relay log）
  3. Slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，而且重启后从接入点开始复制。

复制的基本原则：

  • 每个Slave只有一个Master

  • 每个Slave只能有一个唯一的服务器ID

  • 每个Master可以有多个Slave

复制存在最大的问题：延时

一主一从架构

一台主机用于处理所有写请求 ，一台从机负责所有读请求 ，架构图如下：

13276fef22dfddb53175ffb96b56de12.png

Mycat是数据库中间件，所谓中间件，是一类连接软件组件和应用的计算机软件，以便软件各部件之间的通信。

我们可以使用克隆的两台虚拟机进行实践，但必须要保证以下四处的互异性：① MAC地址 ② hostname ③ IP 地址 ④ UUID 。

修改MySQL Server的UUID：

vim /var/lib/mysql/auto.cnf
systemctl restart mysqld

一般克隆完成后，虚拟机的网卡MAC地址会自动改变；Linux部分也已经学习了主机名和IP地址的修改，不再赘述。

主机配置文件

主从所有配置项都配置在 [mysqld] 节点下：

#[必须]主服务器唯一ID
server-id=1
#[必须]启用二进制日志,指名路径。比如：自己本地的路径/log/mysqlbin
log-bin=文件路径

#[可选] 0（默认）表示读写（主机），1表示只读（从机）
read-only=0
#设置日志文件保留的时长，单位是秒
binlog_expire_logs_seconds=6000
#控制单个二进制日志大小。此参数的最大和默认值是1GB
max_binlog_size=200M
#[可选]设置不要复制的数据库
binlog-ignore-db=test
#[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
binlog-do-db=需要复制的主数据库名字
#[可选]设置binlog格式
binlog_format=STATEMENT

binlog文件格式

STATEMENT模式

基于SQL语句的复制(statement-based replication, SBR)；每一条会修改数据的sql语句会记录到binlog中。

优点：

  • 历史悠久，技术成熟
  • 不需要记录每一行的变化，减少了binlog日志量，文件较小
  • binlog中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况
  • binlog可以用于实时的还原，而不仅仅用于复制
  • 主从版本可以不一样，从服务器版本可以比主服务器版本高

缺点：

  • 不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候。

使用以下函数的语句也无法被复制：LOAD_FILE()、UUID()、USER()、FOUND_ROWS()、SYSDATE()(除非启动时启用了 --sysdate-is-now 选项)

  • INSERT ... SELECT 会产生比 RBR 更多的行级锁
  • 复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时，需要比 RBR 请求更多的行级锁
  • 对于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 语句会阻塞其他 INSERT 语句
  • 对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响
  • 执行复杂语句如果出错的话，会消耗更多资源
  • 数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错
ROW模式

基于行的复制(row-based replication, RBR)；仅记录哪条数据被修改了，修改成什么样了。

优点：

  • 任何情况都可以被复制，这对复制来说是最安全可靠的。

不会出现某些特定情况下的存储过程、function、trigger的调用和触发无法被正确复制的问题

  • 多数情况下，从服务器上的表如果有主键的话，复制就会快了很多
  • 复制以下几种语句时的行锁更少：INSERT ... SELECT、包含 AUTO_INCREMENT 字段的 INSERT、没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句
  • 执行 INSERT，UPDATE，DELETE 语句时锁更少
  • 从服务器上采用 多线程 来执行复制成为可能

缺点：

  • binlog占用空间变大
  • 复杂的回滚时 binlog 中会包含大量的数据
  • 主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，这会导致频繁发生 binlog 的并发写问题
  • 无法从 binlog 中看到都复制了些什么语句
MIXED模式

混合模式复制(mixed-based replication, MBR)；

在Mixed模式下，一般的语句修改使用statment格式保存binlog。如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog。MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

从机配置文件

要求主从所有配置项都配置在 my.cnf 的 [mysqld] 栏位下，且都是小写字母。

#[必须]从服务器唯一ID
server-id=2
#[可选]启用中继日志
relay-log=mysql-relay

重启后台mysql服务，使配置生效，注意需要关闭主从机的防火墙。

主机设置

建立账户并授权

#MySQL5.7在主机MySQL里执行授权主从复制的命令
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'从机器数据库IP' IDENTIFIED BY 'abc123';

#MySQL8要先建立账户，并授权slave：
CREATE USER 'slave1'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';
#此语句必须执行。否则会报错。
ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

查询Master的状态后需要记录File和Position的值

show master status;

执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化。

从机设置

步骤一：

配置需要复制的主机信息

CHANGE MASTER TO
MASTER_HOST='主机的IP地址',
MASTER_USER='主机用户名',
MASTER_PASSWORD='主机用户名的密码',
MASTER_LOG_FILE='mysql-bin.具体数字',
MASTER_LOG_POS=具体值;

步骤二：

#启动slave同步
START SLAVE;

如果报错，可以执行下述指令，删除之前的relay_log信息，重新执行配置一即可。

#删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件
reset slave;

同步完成后可以查看同步状态：

SHOW SLAVE STATUS\G;

当Slave_IO_Running和Slave_SQL_Running都是yes，说明同步配置成功。

停止主从同步：

stop slave;

如果停止从服务器复制功能，再使用需要重新配置主从。重新配置主从，需要在从机上执行：

stop slave;
#删除Master中所有的binglog文件，并将日志索引文件清空，重新开始所有新的日志文件(慎用)
reset master;

双主双从

8dc36f0b65d30e7e70fb427beea47271.png

同步数据一致性问题

主从同步的要求：

  • 读库和写库的数据一致(最终一致)；
  • 写数据必须写到写库；

进行主从同步的内容是二进制日志，它是一个文件，在进行网络传输的过程中就一定会存在主从延迟（比如 500ms），这样就可能造成用户在从库上读取的数据不是最新的数据，也就是主从同步中的数据不一致性问题。

主备延迟最直接的表现是，从库消费中继日志（relay log）的速度，比主库生产binlog的速度要慢。造成原因：

  1. 从库的机器性能比主库要差
  2. 从库的压力大
  3. 大事务的执行

可以采取下面的办法减少主从延迟的时间：

  1. 降低多线程大事务并发的概率，优化业务逻辑

  2. 优化SQL，避免慢SQL，减少批量操作，建议写脚本以update-sleep这样的形式完成。

  3. 提高从库机器的配置，减少主库写binlog和从库读binlog的效率差。

  4. 尽量采用短的链路，也就是主库和从库服务器的距离尽量要短，提升端口带宽，减少binlog传输的网络延时。

  5. 实时性要求的业务读强制走主库，从库只做灾备，备份。

解决一致性问题

读写分离情况下，解决主从同步中数据不一致的问题， 就是解决主从之间数据复制方式的问题，如果按照数据一致性从弱到强来进行划分，有以下 3 种复制方式。

1. 异步复制

3b78c3d3d6b76574a4e3af61bd9c79c7.png

2.半同步复制

15b8f81524c5fb5da923705071113599.png

半同步复制是通过判断从库响应的个数来决定是否返回给客户端，虽然数据一致性相比于异步复制有提升，但仍然无法满足对数据一致性要求高的场景，比如金融领域。

3.组复制

组复制技术，简称 MGR（MySQL Group Replication）。是 MySQL 在 5.7.17 版本中推出的一种新的数据复制技术，这种复制技术是基于 Paxos 协议的状态机复制。

首先我们将多个节点共同组成一个复制组，在执行读写（RW）事务的时候，需要通过一致性协议层（Consensus 层）的同意，也就是读写事务想要进行提交，必须要经过组里“大多数人”（对应 Node 节点）的同意，大多数指的是同意的节点数量需要大于 （N/2+1），这样才可以进行提交，而不是原发起方一个说了算。而针对只读（RO）事务则不需要经过组内同意，直接 COMMIT 即可。

MGR 是基于 Paxos 协议的，Paxos 算法提出来之后就作为分布式一致性算法被广泛应用，比如 Apache 的 ZooKeeper 也是基于 Paxos 实现的。

知识延展

在主从架构的配置中，如果想要采取读写分离的策略，我们可以自己编写程序，也可以通过第三方的中间件来实现。

自己编写程序的好处在于针对实时性要求高的需求，我们可以考虑哪些查询可以在主库上执行。同时，程序直接连接数据库，减少了中间件层，相当于减少了性能损耗。

采用中间件的方法有很明显的优势，功能强大，使用简单。但在客户端和数据库之间增加了中间件层会有一些性能损耗，同时商业中间件也是有使用成本的。

数据库备份与恢复

物理备份：备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比较大，MySQL中可以用xtrabackup工具来进行物理备份。

逻辑备份：对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空间小，更灵活。MySQL 中常用的逻辑备份工具为mysqldump。逻辑备份就是备份sql语句，在恢复的时候执行备份的sql语句实现数据库数据的重现。

逻辑备份

基本语法：

mysqldump –u用户名称 –h 主机名称 –p密码 待备份的数据库名称[tbname, [tbname...]]> 备份文件名称.sql

备份文件的后缀名并非一定是sql，也可以是txt

  1. 备份全部数据库：
mysqldump -uroot -p --all-databases > all_database.sql
#或
mysqldump -uroot -p -A > all_database.sql
  1. 备份部分数据库：

使用 --databases 或 -B 参数

mysqldump –u.. –h host –p --databases [数据库的名称1 [数据库的名称2...]] > 备份文件名称.sql
  1. 备份部分表：
mysqldump –u.. –h host –p 数据库名 [表名1 [表名2...]] > 备份文件名称.sql
  1. 备份单表的部分数据：
mysqldump -u.. -p 数据库名 表名 --where="过滤条件" > 备份文件名称.sql
  1. 备份时排除某些表：
mysqldump -u.. -p 数据库名 --ignore-table=数据库名.表名 > 备份文件名称.sql
  1. 仅备份结构：
mysqldump -u.. -p 数据库名 --no-data > 备份文件名称.sql
#使用grep命令，没有找到insert相关语句，表示没有数据备份。
grep "INSERT" 备份文件名称.sql
  1. 仅备份数据：
mysqldump -u.. -p 数据库名 --no-create-info > 备份文件名称.sql
#使用grep命令，没有找到create相关语句，表示没有数据结构。
grep "CREATE" 备份文件名称.sql
  1. 备份中包含存储过程、函数、事件：

mysqldump备份默认是不包含存储过程，自定义函数及事件的。可以使用--routines或-R选项来备份存储过程及函数，使用--events或-E参数来备份事件。

mysqldump -u.. -p -R -E --databases 数据库名 > 备份文件名称.sql

常用选项

运行帮助命令mysqldump --help，可以获得特定版本的完整选项列表。

--add-drop-database：在每个CREATE DATABASE语句前添加DROP DATABASE语句。
--add-drop-tables：在每个CREATE TABLE语句前添加DROP TABLE语句。
--add-locking：用LOCK TABLES和UNLOCK TABLES语句引用每个表转储。重载转储文件时插入得更快。
--all-database, -A：转储所有数据库中的所有表。与使用--database选项相同，在命令行中命名所有数据库。
--comment[=0|1]：如果设置为0，禁止转储文件中的其他信息，例如程序版本、服务器版本和主机。--skip-comments与--comments=0的结果相同。默认值为1，即包括额外信息。
--compact：产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、--skip-disable-keys和--skip-add-locking选项。
--compatible=name：产生与其他数据库系统或旧的MySQL服务器更兼容的输出，值可以为ansi、MySQL323、MySQL40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_table_options或者no_field_options。
--complete_insert, -c：使用包括列名的完整的INSERT语句。
--debug[=debug_options], -#[debug_options]：写调试日志。
--delete，-D：导入文本文件前清空表。
--default-character-set=charset：使用charsets默认字符集。如果没有指定，就使用utf8。
--delete--master-logs：在主复制服务器上，完成转储操作后删除二进制日志。该选项自动启用-master-data。
--extended-insert，-e：使用包括几个VALUES列表的多行INSERT语法。这样使得转储文件更小，重载文件时可以加速插入。
--flush-logs，-F：开始转储前刷新MySQL服务器日志文件。该选项要求RELOAD权限。
--force，-f：在表转储过程中，即使出现SQL错误也继续。
--lock-all-tables，-x：对所有数据库中的所有表加锁。在整体转储过程中通过全局锁定来实现。该选项自动关闭--single-transaction和--lock-tables。
--lock-tables，-l：开始转储前锁定所有表。用READ LOCAL锁定表以允许并行插入MyISAM表。对于事务表（例如InnoDB和BDB），--single-transaction是一个更好的选项，因为它根本不需要锁定表。
--no-create-db，-n：该选项禁用CREATE DATABASE /*!32312 IF NOT EXIST*/db_name语句，如果给出-
-database或--all-database选项，就包含到输出中。
--no-create-info，-t：只导出数据，而不添加CREATE TABLE语句。
--no-data，-d：不写表的任何行信息，只转储表的结构。
--opt：该选项是速记，它可以快速进行转储操作并产生一个能很快装入MySQL服务器的转储文件。该选项默认开启，但可以用--skip-opt禁用。
--password[=password]，-p[password]：当连接服务器时使用的密码。
-port=port_num，-P port_num：用于连接的TCP/IP端口号。
--protocol={TCP|SOCKET|PIPE|MEMORY}：使用的连接协议。
--replace，-r –replace和--ignore：控制替换或复制唯一键值已有记录的输入记录的处理。如果指定--replace，新行替换有相同的唯一键值的已有行；如果指定--ignore，复制已有的唯一键值的输入行被跳过。如果不指定这两个选项，当发现一个复制键值时会出现一个错误，并且忽视文本文件的剩余部分。
--silent，-s：沉默模式。只有出现错误时才输出。
--socket=path，-S path：当连接localhost时使用的套接字文件（为默认主机）。
--user=user_name，-u user_name：当连接服务器时MySQL使用的用户名。
--verbose，-v：冗长模式，打印出程序操作的详细信息。
--xml，-X：产生XML输出。

如果运行mysqldump没有--quick或--opt选项，mysqldump在转储结果前将整个结果集装入内存。如果转储大数据库可能会出现问题，该选项默认启用，但可以用--skip-opt禁用。如果使用最新版本的mysqldump程序备份数据，并用于恢复到比较旧版本的MySQL服务器中，则不要使用--opt 或-e选项。

数据恢复

基本语法：

mysql –uroot –p [dbname] < backup.sql
  1. 单库备份中恢复单库
mysql -uroot -p 数据库名< backup.sql

若备份文件中包含创建数据库的语句，恢复时可以省略数据库名称

  1. 全量备份恢复
mysql –uroot –p < all.sql

执行完后，MySQL数据库中就已经恢复了all.sql文件中的所有数据库。

  1. 从全量备份中恢复单库
#分离完成后我们再导入atguigu.sql即可恢复单个库
sed -n '/^-- Current Database: `数据库名`/,/^-- Current Database: `/p' all_database.sql > 数据库名.sql
  1. 从单库备份中恢复单表
#分离表结构
cat 数据库名.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `表名`/!d;q' > 表结构.sql
#分离表数据
cat 数据库名.sql | grep --ignore-case 'insert into `class`' > 表数据.sql 

mysql> use atguigu; 
mysql> source 表结构.sql;
mysql> source 表数据.sql;

物理备份和恢复

备份

直接将MySQL中的数据库文件复制出来。这种方法最简单，速度也最快。

为了保证备份的一致性。需要保证：

  1. 备份前，将服务器停止。
  2. 备份前，对相关表执行 FLUSH TABLES WITH READ LOCK 操作。这样当复制数据库目录中的文件时，允许其他客户继续查询表。同时，FLUSH TABLES语句来确保开始备份前将所有激活的索引页写入硬盘。

但实际情况可能不允许停止MySQL服务器或者锁住表 ，而且这种方法对InnoDB存储引擎的表不适用。

物理备份完毕后，执行 UNLOCK TABLES 来结算其他客户对表的修改行为。

可以考虑使用相关工具实现备份。比如， MySQLhotcopy工具。MySQLhotcopy是一个Perl脚本，它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。

恢复

要求：

  1. 必须确保备份数据的数据库和待恢复的数据库服务器的主版本号相同。

    • 只有MySQL数据库主版本号相同时，才能保证这两个MySQL数据库文件类型是相同的。

  2. InnoDB表的表空间不能直接复制，所以这种方式只对 MyISAM类型的表比较有效。

  3. 在Linux操作系统下，复制到数据库目录后，一定要将数据库的用户和组变成mysql，命令如下：

#两个mysql分别表示组和用户；“-R”参数可以改变文件夹下的所有子文件的用户和组；“dbname”参数表示数据库目录。
chown -R mysql.mysql /var/lib/mysql/dbname

表的导出与导入

导出

  1. 使用SELECT…INTO OUTFILE导出文本文件
#secure_file_priv变量的值为导出目录
SHOW GLOBAL VARIABLES LIKE '%secure%';
SELECT * FROM account INTO OUTFILE "/var/lib/mysql-files/account.txt" [FIELDS TERMINATED BY ',' ENCLOSED BY '\"'];
  1. 使用mysqldump命令导出文本文件

在指定的目录/var/lib/mysql-files/下生成了表名.sql和表名.txt文件。

mysqldump -uroot -p -T "/var/lib/mysql-files/" 数据库 表名

在上面的基础上，将字段之间用逗号隔开，字符类型的值用双引号括起来。

mysqldump -uroot -p -T "/var/lib/mysql-files/" 数据库 表名 --fields-terminated- by=',' --fields-optionally-enclosed-by='\"'
  1. 使用mysql命令导出文本文件

导出数据库中某张表的记录到文本文件：

mysql -uroot -p --execute="SELECT * FROM 表名;" 数据库> "/var/lib/mysql-files/表名.txt"

导入

  1. 使用LOAD DATA INFILE方式导入文本文件
LOAD DATA INFILE '/var/lib/mysql-files/表名.txt' INTO TABLE 数据库.表名 [FIELDS TERMINATED BY ',' ENCLOSED BY '\"'];
  1. 使用mysqlimport方式导入文本文件
mysqlimport -uroot -p 数据库 '/var/lib/mysql-files/表名.txt' [--fields-terminated-by=',' --fields-optionally-enclosed-by='\"']

数据库迁移

数据迁移（data migration）是指选择、准备、提取和转换数据，并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程。

MySQL 数据迁移方案大致可以分为物理迁移和逻辑迁移两类。

5cfc1e24a07d3bd1545fbe6dc0ee06e4.png

MySQL常用命令

  1. mysql
-u, --user=name 指定用户名
-p, --password[=name] 指定密码
-h, --host=name 指定服务器IP或域名
-P, --port=# 指定连接端口
-e, --execute=name 执行SQL语句并退出

示例：

mysql -uroot -p 数据库 -e "select * from 表名";
  1. mysqladmin

mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。

可以通过 ： mysqladmin --help 指令查看帮助文档

  1. mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog日志管理工具。

mysqlbinlog [options] log-files1 log-files2 ...
#选项：
-d, --database=name : 指定数据库名称，只列出指定的数据库相关操作。
-o, --offset=# : 忽略掉日志中的前n行命令。
-r,--result-file=name : 将输出的文本格式日志输出到指定文件。
-s, --short-form : 显示简单格式， 省略掉一些信息。
--start-datatime=date1 --stop-datetime=date2 : 指定日期间隔内的所有日志。
--start-position=pos1 --stop-position=pos2 : 指定位置间隔内的所有日志。
  1. mysqldump

mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句。

  1. mysqlimport/source

mysqlimport 是客户端数据导入工具，用来导入mysqldump 加 -T 参数后导出的文本文件。

mysqlimport [options] db_name textfile1 [textfile2...]
  1. mysqlshow

mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。

mysqlshow [options] [db_name [table_name [col_name]]]
posted @ 2023-10-29 20:42  LemonPuer  阅读(107)  评论(0)    收藏  举报