mysql cp 备份_MySQL备份和还原系列二:cp冷备份 和 mysqldump

如果要备份,请确保 mysql 打开 log-bin,有了 binarylog,mysql 才可以在必要的时候做完整恢复,或基于时间点的恢复,或基于位置的恢复。

我的数据存放目录为:mysql> SHOW VARIABLES LIKE ‘%datadir%‘;

+---------------+-----------------+

| Variable_name | Value           |

+---------------+-----------------+

| datadir       | /var/lib/mysql/ |

+---------------+-----------------+

1、建立目录用于存放二进制日志[root@localhost ~]# mkdir /mybinlog

[root@localhost ~]# chown mysql:mysql /mybinlog

2、编辑配置文件,启用二进制日志[root@localhost ~]# vi /etc/my.cnf

### 二进制日志目录及文件名前缀

log-bin = /mybinlog/mysql-bin

## 然后重启mysqld服务

[root@localhost ~]# service mysqld restart

3、查看生成的binlog日志[root@localhost ~]# ls /mybinlog/

mysql-bin.000001  mysql-bin.index

4、准备测试数据mysql> CREATE DATABASE mydb;

mysql> USE mydb;

### 创建 myisam 引擎的表

mysql> CREATE TABLE myisam_tbl(

->     id INT NOT NULL AUTO_INCREMENT,

->     name VARCHAR(50),

->     PRIMARY KEY(id)

-> ) ENGINE=myisam DEFAULT charset=utf8;

mysql> INSERT INTO myisam_tbl(name)

-> VALUES(‘one‘),(‘joy‘),(‘li‘),(‘tom‘),(‘jerry‘),(‘hello‘);

### 造数据的好办法

mysql> INSERT INTO myisam_tbl(name)

-> SELECT name FROM myisam_tbl;

### 创建innodb引擎的表

mysql> CREATE TABLE innodb_tbl(

->     id INT NOT NULL AUTO_INCREMENT,

->     name VARCHAR(50),

->     PRIMARY KEY(id)

-> ) engine = innodb DEFAULT charset=utf8;

# 把myisam_tbl的数据弄到innodb_tbl表

mysql> INSERT INTO innodb_tbl(name)

-> SELECT name FROM myisam_tbl;

一、直接拷贝数据库文件(文件系统备份工具 cp),物理备份(适合小型数据库)

标准流程:锁表->刷新表到磁盘->停止服务->拷贝文件->解锁

冷备份步骤:

备份:

1.停掉 mysql 服务,在操作系统级别备份 mysql 的数据文件。

2.重启 mysql 服务,备份重启以后生成的 binlog。

恢复:

1.停掉 mysql 服务,在操作系统级别恢复 mysql 的数据文件。

2.重启 mysql 服务,使用 mysqlbinlog 恢复自备份以来的 binlog。

1、在终端1# 刷新,打开读锁

mysql> FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected (0.00 sec)

2、在终端2# 创建备份目录

[root@localhost ~]# mkdir /mnt/mysql-$(date +%F)

# 以归档模式拷贝所有的数据文件

[root@localhost ~]# cp -a /var/lib/mysql/* /mnt/mysql-2014-07-21/

3、回到第一个终端# 解锁

mysql> UNLOCK TABLES;

Query OK, 0 rows affected (0.10 sec)

4、模拟数据库损坏, 删除原来的所有数据文件[root@localhost ~]# rm -rf /var/lib/mysql/*

# 然后关闭Mysql服务

[root@localhost ~]# service mysqld stop

### 我是通过yum安装的mysql,是可以顺利停掉服务的。

# 如果不能停掉 ERROR! MySQL server PID file could not be found! , 那么

[root@localhost ~]# ps -ef | grep mysql

[root@localhost ~]# killall mysqld

###进行数据库初始化

#1 编译安装的 ,请根据实际情况指定参数

[root@localhost ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/mydata/data/ --user=mysql

#2 yum安装的

[root@localhost ~]# /usr/bin/mysql_install_db

# 初始化后,我们的数据已经丢失

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| test               |

+--------------------+

3 rows in set (0.00 sec)

5、恢复过程,复制完全备份的数据文件到数据目录中# \cp  转义别名,不然复制时老是提醒是否覆盖

[root@localhost ~]# \cp -a /mnt/mysql-2014-07-21/* /var/lib/mysql/

# 启动Mysql

[root@localhost ~]# service mysqld start

mysql> SHOW DATABASES;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mydb               |

| mysql              |

| test               |

+--------------------+

mysql> USE mydb;

Database changed

mysql> show tables;

+-----------------+

| Tables_in_mydb  |

+-----------------+

| innodb_tbl      |

| myisam_tbl      |

| test_myisam_tbl |

+-----------------+

3 rows in set (0.00 sec)

OK, 我们看到数据已经恢复。cp命令,对其进行的备份,速度快,还原速度几乎最快,但是灵活度很低,可以跨系统,但是跨平台能力很差,适合小型数据库备份!

二、mysqldump逻辑备份数据库(完全备份+增加备份,速度相对较慢,适合中小型数据库)(MyISAM是温备份,InnoDB是热备份)

逻辑备份步骤:

备份:

1.选择在系统空闲时,比如在夜间,使用 mysqldump –F(flush-logs)备份数据

库。# mysqldump –u root –p*** pointcard –F > pointcard.sql

2.并备份 mysqldump 开始以后生成的 binlog。

恢复:

1. 停掉应用,执行 mysql 导入备份文件.mysql –u root –p*** pointcard 

2. 使用 mysqlbinlog 恢复自 mysqldump 备份以来的 binlog。mysqlbinlog $HOME/data/mysql-bin.123456 | mysql -u root –p***mysqldump -u -h -p--all-databases  --databases 

--events            #备份事件

--flush-logs    #在数据库导出之前先执行FLUSH LOGS

--lock-all-tables   #锁定所有表

--lock-tables    #锁定某些表

--master-data=n    #(指定备份点:log fiel name and position)

--opt      #(PITR 精确恢复,时间点恢复)

--routines    #(备份存储过程、存储函数)

--triggers    #(备份触发器)

--single-transaction#(为事务性数据库提供备份)

--flush-logs        # 滚动日志

--where     #(指定过滤条件,只备份符合条件数据)

由于mysqldump针对不同的引擎有所差异,所以务必清楚引擎类型

温备:

在使用MyISAM引擎中,只能使用温备份,这时候要防止数据的写入,所以先加上读锁。这时候可以进入数据库手动加读锁。这样比较麻烦,在mysqldump工具中直接有一个加锁的选项# mysqldump --databases mydb --lock-all-tables --flush-logs> /tmp/backup-`date +%F-%H-%M`.sql

### 备份所有库(完全备份)

# mysqldump --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs > /root/mybackup/`date +%F-%H-%M`.full.sql

如果是针对某张表备份,只要在数据库名称后面加上表名称就行了# mysqldump -uroot  -p mydb course > /root/mydb.sql备份表course

# mysqldump -uroot  -p --databases mydb dnsdata > /root/mydb.sql  同时备份两个数据库

这里注意,要实现时间点的恢复,加上--flush-logs选项,在使用备份文件恢复后,然后再基于二进制日志进行时间点的恢复

热备:

如果使用的是InnoDB引擎,就不必进行对数据库加锁的操作,加一个选项既可以进行热备份:--single-transaction# mysqldump --databases mydb --single-transaction  --flush-logs --master-data=2 > /tmp/backup-`date +%F-%H-%M`.sql

实验:

1、备份前的binlog日志mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000005 |      106 |              |                  |

+------------------+----------+--------------+------------------+

2、备份所有库(完全备份)[root@localhost ~]# mysqldump -uroot -p --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs > /root/mybackup/$(date +%F-%H-%M).full.sql

[root@localhost ~]# ls mybackup/

2014-07-21-16-53.full.sql

3、查看备份后的binlog, 主要是--flush-logs选项mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000006 |      106 |              |                  |

+------------------+----------+--------------+------------------+

4、我们向myisam_tbl表插入几条新数据mysql> INSERT INTO myisam_tbl(name)

-> VALUES(‘new-1‘), (‘new-2‘), (‘new-3‘);

# 查看binlog

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000006 |      263 |              |                  |

+------------------+----------+--------------+------------------+

5、备份binlog(增量备份)[root@localhost ~]# cp /mybinlog/mysql-bin.000006 /root/mybackup/$(date +%F-%H-%M).binlog.000006

6、模拟误操作(删除myisam_tbl表)mysql> drop table myisam_tbl;

Query OK, 0 rows affected (0.01 sec)

7、恢复

恢复前先关闭对恢复过程的二进制日志记录,因为记录恢复语句是毫无意义的mysql> set sql_log_bin=0;        -- 关闭binlog

Query OK, 0 rows affected (0.00 sec)

mysql> \. /root/mybackup/2014-07-21-16-53.full.sql

mysql> use mydb;

mysql> show tables;

+-----------------+

| Tables_in_mydb  |

+-----------------+

| innodb_tbl      |

| myisam_tbl      |

| test_myisam_tbl |

+-----------------+

mysql> SELECT *

FROM myisam_tbl

ORDER BY id DESC

LIMIT 5;

+----+-------+

| id | name  |

+----+-------+

| 12 | hello |

| 11 | jerry |

| 10 | tom   |

|  9 | li    |

|  8 | joy   |

+----+-------+

ok,现在已经恢复到完全备份时的状态,但我们最后插入的三条数据没有恢复。[root@localhost ~]# mysqlbinlog /root/mybackup/2014-07-21-17-02.binlog.000006 | mysql -uroot -p mydb

Enter password: mysqlbinlog: unknown variable ‘default-character-set=utf8‘

mysqlbinlog报错: unknown variable ‘default-character-set=utf8‘

it‘s because inside the my.cnf  gotdefault-character-set=utf8the default-character-set is deprecated in 5.5. we should use instead:character-set-server = utf8

or add --no-defaults

mysqlbinlog --no-defaults -v logbin-log.000003 > logbin003.sql

==============END=================[root@localhost ~]# mysqlbinlog --no-defaults /root/mybackup/2014-07-21-17-02.binlog.000006 | mysql -uroot -p mydb

mysql> set sql_log_bin=1;        -- 打开binlog

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT *  FROM myisam_tbl ORDER BY id DESC LIMIT 5;

+----+-------+

| id | name  |

+----+-------+

| 15 | new-3 |

| 14 | new-2 |

| 13 | new-1 |

| 12 | hello |

| 11 | jerry |

+----+-------+

OK, 恢复成功。

时间点的恢复:

1.如果今天上午 10 点发生了误操作,可以用以下语句用备份和 binglog 将数据恢复到故

障前:mysqlbinlog --no-defaults --stop-date="2014-07-24 9:59:59"

/var/log/mysql/bin.123456 | mysql -u root –pmypwd

2.跳过故障时的时间点,继续执行后面的 binlog,完成恢复mysqlbinlog --no-defaults --start-date="2014-07-24 10:01:00"

/var/log/mysql/bin.123456 | mysql -u root -pmypwd

位置恢复:

和时间点恢复类似,但是更精确,步骤如下:mysqlbinlog  --no-defaults --start-date="2014-07-24 9:55:00" --stop-date="2014-07-24

10:05:00" /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

ab68f7f777a7281d4346e21ff2f5475f.png

该命令将在/tmp 目录创建小的文本文件,编辑此文件,找到出错语句前后的位置号 ,

例如前后位置号分别是 368312 和 368315。恢复了以前的备份文件后,你应从命令

行输入下面内容:mysqlbinlog --no-defaults --stop-position="368312" /var/log/mysql/bin.123456

| mysql -u root -pmypwd

mysqlbinlog --no-defaults --start-position="368315" /var/log/mysql/bin.123456

| mysql -u root -pmypwd

上面的第 1 行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置

直到二进制日志结束的所有事务。因为 mysqlbinlog 的输出包括每个 SQL 语句记录

之前的 SET TIMESTAMP 语句,恢复的数据和相关 MySQL 日志将反应事务执行的原时

间。

注意点

恢复的时刻关闭二进制日志mysql>set sql_log_bin=0;

因为这是基于逻辑备份方式,在恢复日志时会执行sql语句插入数据,而恢复时候插入数据的日志没有意义。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值