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

1备份概览

按备份方法分类：



HotBackup（热备）



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



ColdBackup（冷备）



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



WarmBackup（温备）



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



按备份文件内容分类：



逻辑备份



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



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



裸文件备份



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



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



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



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



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



2冷备

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



优势：



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

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

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

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

劣势：



备份文件较大

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

3逻辑备份

3.1mysqldump

mysqldump-uroot-p[arguments]>file_name

#arguments参数

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

--databasesdbx#备份指定数据库

--single-transactiondbx#备份数据库的整体架构，事务控制保证备份的一致性

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

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

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

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

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

#value=2CHANGEMASTER语句写成注释

--master-data[=value]

--events#备份事件调度器

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

--triggers#备份触发器

--hex-blob#将binary、varbinary、blog、bit列类型备份为16进制的格式

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

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



3.2SELECT…INTOOUTFILE

SELECT[column1],[column2]...

INTO

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

OUTFILE'filename'

{#fieldsorcolumns

#列分隔符

TERMINATEDBY'string'

#对于字符串的包含符

[OPTIONALLY]ENCLOSEDBY'char'

#转译符

ESCAPEDBY'char'

}

{#lines

STARTINGBY'string'

TERMINATEDBY'string'

}

FROMTABLEWHERE...



3.3逻辑备份的恢复

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



#在shell中执行

mysql-uroot-p
#在MySQL交互命令行执行

mysql>sourcexxx.sql



3.4LODADATAINFILE

通过mysqldump--tab或selectintooutfile导出的数据需通过以下的命令导入



LOADDATA[LOW_PRIORITY|CONCURRENT][LOCAL]INFILE'filename'

[REPLACE|IGNORE]

INTOTABLEtb_name

[CHARACTERSETcharset_name]

#[FIELDS|COLUMNS]

TERMINATEDBY'string'

[OPTIONALLY]ENCLOSEDBY'char'

ESCAPEDBY'char'

#[LINES]

STARTINGBY'string'

TERMINATEDBY'string'

#[IGNORENUMBERLINES]

col_nameoruser_var

SETcol_name=expr



使用LOADDATAINFILE导入需拥有FILE权限



mysql>loaddatainfile'xx.txt'intotablea;



mysql>loaddatainfile'xx.txt'intotablea;



4binlog备份

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



#查看

mysqlbinlogmysqlbin-xxx.xxx

#还原

mysqlbinlogbinlog.000001|mysql-uroot-ptest



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



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



mysqlbinlog--start-position=10000--stop-position=12000|mysql-uroot=ptest



也可使用Cstart-datetime和Cstop-datetime



5热备

5.1Ibackup

备份InnoDB表原理：



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

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

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

拷贝备份期间的重做日志

优点：



在线备份，不阻塞SQL语句

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

支持压缩备份

跨平台支持

恢复流程：



恢复表空间文件

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

5.2Xtrabackup

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



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-Rmysql:mysql/mysql



#5.启动实例

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



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

changemasterto

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.[从库]从库启动复制进程

startslave;启动连接



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快照+复制的备份架构

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



数据分布

读取负载均衡

数据库备份

高可用与故障转移

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



购买使用一诺网络美国VPS，可以极大降低初创企业、中小企业以及个人开发者等用户群体的整体IT使用成本，无需亲自搭建基础设施、简化了运维和管理的日常工作量，使用户能够更专注于自身的业务发展和创新。美国VPS低至49元/月，购买链接：https://www.enuoidc.com/vpszq.html?typeid=3