美国vps服务器的MySQL备份与恢复

已关闭留言

美国vps服务器的MySQL备份与恢复

1 备份概览

按备份方法分类:

Hot Backup(热备)

在线备份,不影响数据库运行

Cold Backup(冷备)

离线备份,通过拷贝数据库物理文件

Warm Backup(温备)

在线进行,但需要加读锁保证数据一致性

按备份文件内容分类:

逻辑备份

备份后的内容是可读的,通常形式为SQL语句的文本文件,或是表内的实际数据

优势:呈现直观,适用于升级、迁移等工作,缺点:恢复用时较长

裸文件备份

拷贝数据库的物理文件,不关心数据库的运行状态,通常这类备份恢复的时间较短

按备份数据库的内容分类:

完全备份:对当前数据库的完整备份

增量备份:在完全备份的基础上,对更新的数据进行备份

日志备份:指的是对数据库二进制日志的备份

2 冷备

备份内容:数据表结构定义文件(*.frm)、共享表空间文件(ibdata)、独立表空间文件(*.ibd)、重做日志文件(ib_logfile),建议增加配置文件(my.cnf)

优势:

备份简单,仅需拷贝相关文件

备份文件有利于在不同操作系统、不同MySQL版本之间恢复

恢复简单,仅需把文件恢复到指定位置

恢复速度快,无需执行SQL语句,无需重建索引

劣势:

备份文件较大

跨平台、操作系统、软件版本可能会出现问题

3 逻辑备份

3.1 mysqldump

mysqldump -uroot -p [arguments] > file_name

#arguments参数

–all-databases #备份所有数据库

–databases dbx #备份指定数据库

–single-transaction dbx #备份数据库的整体架构,事务控制保证备份的一致性

–lock-tables #一般用于MyISAM,备份时仅可读

–lock-all-tables #对所有架构所有表上锁

–add-drop-databases #create前先运行drop,需配合–alldatabases使用

#通过转存的文件建立一个salve replication

#value=1 转存文件中记录CHANGE MASTER语句

#value=2 CHANGE MASTER语句写成注释

–master-data[=value]

–events #备份事件调度器

–routines #备份存储过程和函数

–triggers #备份触发器

–hex-blob #binaryvarbinaryblogbit列类型备份为16进制的格式

–tab=path #产生tab分割的数据文件,可使用–fields-terminated-by改变分隔符

–where #导出给定条件的数据

3.2 SELECT … INTO OUTFILE

SELECT [column1],[column2] …

INTO

#导出的文件名称需拥有mysql用户权限且文件不存在

OUTFILE ‘filename’

{#fields or columns

#列分隔符

TERMINATED BY ‘string’

#对于字符串的包含符

[OPTIONALLY] ENCLOSED BY ‘char’

#转译符

ESCAPED BY ‘char’

}

{#lines

STARTING BY ‘string’

TERMINATED BY ‘string’

}

FROM TABLE WHERE …

3.3 逻辑备份的恢复

mysqldump备份的文件为逻辑SQL语句,可用以下两种方法恢复

#shell中执行

mysql -uroot -p < xxx.sql

#MySQL交互命令行执行

mysql> source xxx.sql

3.4 LODA DATA INFILE

通过mysqldump –tabselect into outfile导出的数据需通过以下的命令导入

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE ‘filename’

[REPLACE | IGNORE]

INTO TABLE tb_name

[CHARACTER SET charset_name]

#[FIELDS | COLUMNS]

TERMINATED BY ‘string’

[OPTIONALLY] ENCLOSED BY ‘char’

ESCAPED BY ‘char’

#[LINES]

STARTING BY ‘string’

TERMINATED BY ‘string’

#[IGNORE NUMBER LINES]

col_name or user_var

SET col_name = expr

使用LOAD DATA INFILE导入需拥有FILE权限

mysql> load data infile ‘xx.txt’ into table a;

mysql> load data infile ‘xx.txt’ into table a;

4 binlog备份

在配置文件启动binlog日志服务

#查看

mysqlbinlog mysqlbin-xxx.xxx

#还原

mysqlbinlog binlog.000001 | mysql -uroot -p test

binlog为逻辑备份日志,可在MySQL命令行窗口使用source命令导入

也可设置偏移量指定二进制日志的位置,以跳过某些不想被恢复的语句

mysqlbinlog –start-position=10000 –stop-position=12000 | mysql -uroot =p test

也可使用�start-datetime�stop-datetime

5 热备

5.1 Ibackup

备份InnoDB表原理:

记录备份开始的重做日志文件LSN

拷贝共享表空间文件和独立表空间文件

拷贝后记录重做日志的检查点LSN

拷贝备份期间的重做日志

优点:

在线备份,不阻塞SQL语句

备份性能好,通过复制数据库物理表空间文件和重做日志文件

支持压缩备份

跨平台支持

恢复流程:

恢复表空间文件

应用重做日志文件恢复InnoDB存储引擎表

5.2 Xtrabackup

Xtrabackup是一个对InnoDB做数据备份的工具,支持在线热备份(备份时不影响数据读写),是商业备份工具InnoDB Hotbackup的一个很好的替代品

5.2.1 全量备份

备份

#备份

/usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –defaults-file=/home/mysql/etc/my.cnf –user=root –password=pass –host=127.1 –slave-info –tmpdir=/mysql/bak/full/ /mnt/sdfs/xxxx

恢复

#1.停库,清空data目录

mysqladmin -uroot -h127.1 -P3306 -p’123456′ shutdown;

rm -rf /mysql/data/*;

#2.恢复应用日志

/usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –defaults-file=/home/mysql/etc/my.cnf –apply-log –user=root –password=pass –host=127.1 /mnt/sdfs/***

#3.恢复拷贝文件

/usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –defaults-file=/home/mysql/etc/my.cnf –user=root –password=pass –host=127.1 –copy-back /mnt/sdfs/***

#4.文件修改权限

chown -R mysql:mysql /mysql

#5.启动实例

mysqld_safe –defaults-file=/home/mysql/etc/my.cnf &

#6.[从库]从库需重新连接master

change master to

master_host=’192.168.100.20′,

master_user=’repl’,

master_port=3306,

master_password=’123456′,

master_log_file=’mysql-bin.000001′,

master_log_pos=307;

#7.[从库]从库启动复制进程

start slave; 启动连接

5.2.2 增量备份

增量备份依赖于全备份,xtrabackup通过比较全备的终点与当前的LSN,生成区间内的备份文件,备份时也存在redo日志监控线程,备份过程中的增加的LSN也会写入日志中

/usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –user=root –host=’127.0.0.1′ –password=’123456′ –incremental /root/back –incremental-basedir=/opt/back/2022-05-13_17-46-53

可进行多次增备,恢复需依次准备

#准备全备份日志

/usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –apply-log –redo-only /opt/back/2022-05-13_17-33-14

#准备增备日志

/usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –apply-log –redo-only /opt/back/2022-05-13_17-33-14 –incremental-dir=/opt/back/2022-05-13_17-33-14

#再次准备整体

/usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –apply-log /opt/back/2022-05-13_17-33-14

5.3 快照备份

基于LVM对分区执行快照工作,Linux在硬盘和分区上建立一个逻辑层提高分区管理的灵活性,将离散的磁盘分区连接成一个整块的卷组,形成存储池

LVM使用写时复制技术创建快照,创建时仅拷贝元数据,不存在数据操作,创建过程较快。创建成功后,原始卷上有写操作时快照会跟踪原始卷的变化,将数据在改变之前拷贝到快照预留空间中,称之写时复制

6 复制

6.1 复制原理

复制是MySQL提供的一种高可用、高性能的方案,用于建立大型应用,步骤分为三步:

主服务器将数据更新到二进制日志中

从服务器把主服务器的而二进制日志拷贝到自己的中继日志中

从服务器重做中继日志,将更新应用到自己的数据库中

同步的过程是异步的,基本上也是实时进行的,取决于主服务器的压力

从服务器有两个线程,

I/O线程:读取主服务器的二进制日志,并保存为中继日志

SQL线程:复制执行中继日志

6.2 快照+复制的备份架构

复制用于备份的主要功能:

数据分布

读取负载均衡

数据库备份

高可用与故障转移

为解决数据误操作的问题,可以在从服务器建立分区快照,避免复制对误操作的处理能力