MySQL常见备份方案

发布时间：2020-12-11 23:58:55 所属栏目：MySql教程来源：网络整理

导读：MySQL 常见备份方案有以下三种： ?????? mysqldump + binlog ?????? lvm + binlog?? ?????? xtrabackup 本例为方便演示，数据库里面数据为空。下面开始动手 mkdir /opt/ -p /data/ / /usr/local/mysql/ /mysql_install_db --user=mysql --datadir=/data/ /da

MySQL常见备份方案有以下三种：

?????? mysqldump + binlog

?????? lvm + binlog??

?????? xtrabackup

本例为方便演示，数据库里面数据为空。下面开始动手

mkdir /opt/-p /data///usr/local/mysql//mysql_install_db --user=mysql --datadir=/data//data/ --basedir=/usr/local/mysql/-R /data//-files/my-small.cnf /data//vim /data/3309/my.cnf #编辑配置文件
<pre class="brush:bash;toolbar:false">[client]

password???????=?your_password

port????????????=?3309
socket??????????=?/tmp/mysql.sock4

?The?MySQL?server

[mysqld]
port????????????=?3309
socket??????????=?/tmp/mysql.sock4
skip-external-locking
key_buffer_size?=?16K
max_allowed_packet?=?1M
table_open_cache?=?4
sort_buffer_size?=?64K
read_buffer_size?=?256K
read_rnd_buffer_size?=?256K
net_buffer_length?=?2K
thread_stack?=?128K
datadir?=?/data/3309/data
server-id???????=?3309
log-bin=/data/3309/binlog/mysql-bin???#二进制日志位置
binlog_format=mixed???????????????#二进制日志格式
log-error?=?/data/3309/mysql-err???????#错误日志位置?
innodb_file_per_table?=?1????????????#为每一个新数据表创建一个表空间文件
sync_binlog?=?1????????????????????#写二进制日志的时候，同步到磁盘上面

cp?/data/3309/my.cnf?/opt/backup/???#备份配置文件
mysqld_safe?--defaults-file=/data/3309/my.cnf?&??#启动mysql

1、利用mysqldump完全备份mysql，配合二进制日志备份实现增量备份

mysqldump 选项请参考http://wangweiak47.blog.51cto.com/2337362/1589304

1.1提供模拟数据

mysql -S /tmp/>> create table test (id (),comment ( rows affected (mysql> insert into test values (1,'yun zhonghe'); #插入数据
Query OK,1 row affected (0.16 sec)

1.2全量备份:

mysqldump -S /tmp/mysql.sock4 -A -B -F -x --events --triggers --routines --master-data= > /opt/backup/all_data-`date +%F--%U`.sql

1.3 模拟数据发生改变

mysql> insert into test values (, row affected ( sec)

1.4 增量备份

mysqladmin -S /tmp/mysql.sock4 flush-/data//binlog/mysql-bin.index | tail -n | head -n ` /opt/backup/ #备份二进制日志

1.5 数据损坏

rm -rf /data//

1.6 恢复

上面已经提供了步骤，直接复制过来使用。

mkdir -p /data///usr/local/mysql//mysql_install_db --user=mysql --datadir=/data//data/ --basedir=/usr/local/mysql//opt/backup/my.cnf /data//-R /data//--defaults-file=/data//my.cnf &mysql -S /tmp/mysql.sock4 #连接mysql查看数据
mysql> set sql_log_bin=0;
Query OK,0 rows affected (0.00 sec)

mysql> source /opt/backup/all_data-2015-10-01--39.sql

mysql> select * from test.test; #还缺一个数据
+------+-------------+
| id | comment |
+------+-------------+
| 1 | yun zhonghe |
+------+-------------+
1 row in set (0.00 sec)

mysql> source /tmp/incres-1.sql;

mysql> select * from test.test; #数据已恢复
+------+--------------+
| id | comment |
+------+--------------+
| 1 | yun zhonghe |
| 2 | yun zhonghe2 |
+------+--------------+
2 rows in set (0.00 sec)

mysql> set sql_log_bin = 1; #打开二进制记录开关。

1.7小结，mysqldump适合于数据量较小的场合，它的优点是有众多选项，使用起来非常灵活，缺点是数据量一旦过大，非常耗时耗力。

2、使用lvm进行全备。

lvm快照卷原理参考

http://baike.baidu.com/link?url=iEpO_zm_AbHbk-ijQa8jNcFRoPPG2NdTYb_cRoQ7mjQb_ag9g-fL7yHPXV7Atp2j3J0L5xYynM9KKLIwycW_S_

2.1 数据接着上次继续开始。

2.2 全备。

需保证数据库存放在逻辑卷组上面，才行。

2.2.1首先锁表和滚动日志

mysql> flush table with read rows affected (mysql -S /tmp/mysql.sock4 -e 'show master status;' > /backup/master.info #记录当前日志信息
mysqladmin -S /tmp/mysql.sock4 flush-logs #滚动日志

2.2.2创建快照

lvcreate -s -n snap_data -L 500M /dev/vg_node5/ created

2.2.3 解锁表

mysql> rows affected ( sec)

2.2.4 复制快照卷数据到备份目录下

mkdir -p /backup//dev/vg_node5/snap_data /mnt/-R /mnt/

2.2.5 删除快照

umount //dev/mapper/vg_node5-? [y/ successfully removed

2.3增量备份，只需和定时复制binlog到备份目录下面即可

2.4恢复，只需要直接拷贝备份目录下的文件即可

-rf /data//-R /backup/lvm/ /data//-R /data//--defaults-file=/data//my.cnf &-tnl | grep *: *:*mysql> select * from test.test; #数据未改变
+------+--------------+
| id | comment |
+------+--------------+
| 1 | yun zhonghe |
| 2 | yun zhonghe2 |
+------+--------------+

2.5小结：

lvm实现物理备份速度相对mysqldump来比较快，实现也比较简单，是不错的选择。

缺点：数据目录必须存放在lvm卷组上面

3、使用xtrabackup实现热备。

请自行到官网下载并安装对应的rpm包。

使用方法参考：详细参考：

http://www.cnblogs.com/Amaranthus/archive/2014/08/19/3922570.html

3.1创建一个具有最小权限的用户

?grant?RELOAD,?LOCK?TABLES,?REPLICATION?CLIENT?on?*.*?to?'bkuser'@'localhost'?identified?by?'123456';
mysql>?flush?privileges;

3.2完全热备。

使用innobakupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命令的目录中。

innobackupex --user=bkuser --password= --socket=/tmp/mysql.sock4 --defaults-file=/data//my.cnf /backup/xtrabackup/!/backup/xtrabackup/ --01_17--

(1)xtrabackup_checkpoints —— 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；

每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。

(2)xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。

(3)xtrabackup_binlog_pos_innodb —— 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。

(4)xtrabackup_binary —— 备份中用到的xtrabackup的可执行文件；

(5)backup-my.cnf —— 备份命令用到的配置选项信息；

3.3提供改变数据。

mysql> insert into test.test values (, row affected ( sec)

3.4增量备份

innobackupex --user=bkuser --password= --socket=/tmp/mysql.sock4 --defaults-file=/data//my.cnf --incremental /backup/xtrabackup/ --incremental-basedir=/backup/xtrabackup/--01_17--/

3.5 模拟数据损坏

rm -rf /data//

3.6增量备份恢复

innobackupex --apply-log --redo-only /backup/xtrabackup/--01_17--/--apply-log --redo-only /backup/xtrabackup/--01_17--/ --incremental-dir=/backup/xtrabackup/--01_17--/

#注意，多实例的话，仍然需要备份配置文件，启动的时候需要binlog目录。

mkdir -p /data//-R /data//opt/backup/my.cnf /data//--copy-back /backup/xtrabackup/--01_17--/ --defaults-file=/opt/backup/my.cnf #恢复数据。

3.6启动数据库查看。

小结：

xtrabacup具有如下特点。

(1)备份过程快速、可靠；

(2)备份过程不会打断正在执行的事务；

(3)能够基于压缩等功能节约磁盘空间和流量；

(4)自动实现备份检验；

(5)还原速度快；

因此建议学会熟练使用xtrabackup进行备份和还原

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!