大纲
一、备份类型分类
二、各备份工具对比图
三、mysqldump实现备份
四、lvm快照实现备份
五、xtrabackup实现备份
一、备份类型分类
(1)、根据备份时服务器是否能在线分类
热备份：读、写不受影响
温备份：仅可以执行读操作
冷备份：离线备份；读、写操作均中止
(2)、根据备份时数据导出方式分类
物理备份：复制数据文件
逻辑备份：将数据导出至文本文件中
(3)、根据备份时的数据集分类
完全备份：备份全部数据
增量备份：仅备份上次完全备份或增量备份以后变化的数据
差异备份：仅备份上次完全备份以来变化的数据
二、各备份工具对比图
三、mysqldump实现备份
mysqldump - a database backup program
SYNOPSIS
mysqldump [options] [db_name [tbl_name ...]]
--master-data={0|1|2}
0: 不记录二进制日志文件及路位置
1：以CHNAGE MASTER TO的方式记录位置，可用于恢复后直接启动从服务器
2：以CHANGE MASTER TO的方式记录位置，但默认为被注释
--lock-all-tables：锁定所有表
--flush-logs: 执行日志flush；
--all-databases: 备份所有库
--databases DB_NAME,DB_NAME,...: 备份指定库1、先做一次完全备份
[root@soysauce ~]# mysqldump -uroot -p --all-databases --lock-tables --flush-logs --master-data=2 > /root/data.sql Enter password:
2、查看一下二进制日志滚动的位置
[root@soysauce ~]# head -25 /root/data.sql -- MySQL dump 10.13 Distrib 5.1.73, for redhat-linux-gnu (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 5.5.28-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE=‘+00:00‘ */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO‘ */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000017‘, MASTER_LOG_POS=107; # 找到这一行 -- -- Current Database: `mysql`
3、备份一下以前的二进制日志文件之后再删除
[root@soysauce ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 23 Server version: 5.5.28-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> SHOW BINARY LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000008 | 126 | | mysql-bin.000009 | 1654 | | mysql-bin.000010 | 20386 | | mysql-bin.000011 | 126 | | mysql-bin.000012 | 477 | | mysql-bin.000013 | 126 | | mysql-bin.000014 | 399 | | mysql-bin.000015 | 235 | | mysql-bin.000016 | 150 | | mysql-bin.000017 | 4771 | | mysql-bin.000018 | 534 | | mysql-bin.000019 | 107 | +------------------+-----------+ 3 rows in set (0.00 sec) 10 rows in set (0.00 sec) mysql> PURGE BINARY LOGS TO ‘mysql-bin.000019‘; Query OK, 0 rows affected (0.07 sec) mysql> SHOW BINARY LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000019 | 107 | +------------------+-----------+ 1 row in set (0.00 sec) mysql> exit Bye
4、每天做一次增量备份，以做完增量备份的第一天为例
[root@soysauce ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 37 Server version: 5.5.28-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000019 | 107 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> FLUSH LOGS; Query OK, 0 rows affected (0.04 sec) mysql> exit Bye [root@soysauce ~]# cd /mydata/data/ [root@soysauce data]# ls ibdata1 ib_logfile1 mysql mysql-bin.000020 node1.network.com.err soysauce.err test vsftpd ib_logfile0 jiaowu mysql-bin.000019 mysql-bin.index performance_schema soysauce.pid testdb [root@soysauce data]# mkdir /bin_backup [root@soysauce data]# mysqlbinlog mysql-bin.000019 > /bin_backup/mon-incremental.sql
5、当某一天磁盘损坏，所有库丢失，先初始化mysql
[root@soysauce ~]# cd /usr/local/mysql [root@soysauce mysql]# scripts/mysql_install_db --user=mysql --datadir=/data/mysql/ Installing MySQL system tables... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: ./bin/mysqladmin -u root password ‘new-password‘ ./bin/mysqladmin -u root -h soysauce password ‘new-password‘ Alternatively you can run: ./bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd ./mysql-test ; perl mysql-test-run.pl Please report any problems with the ./bin/mysqlbug script!
6、恢复完全备份
[root@soysauce mysql]# mysql < /root/data.sql
7、恢复增量备份
[root@soysauce mysql]# mysql < /bin_backup/mon-incremental.sql
8、即使点还原
[root@soysauce mysql]# mysqlbinlog mysql-bin.000020 | mysql
总结：
一定要习惯性的备份数据、二进制日志文件、配置文件、事务日志文件
二进制文件最好存储在另一块磁盘上，不要与数据文件在同一块盘上
本文出自 “Hello,Linux” 博客，请务必保留此出处http://soysauce93.blog.51cto.com/7589461/1728378
原文：http://soysauce93.blog.51cto.com/7589461/1728378