linux下mysql数据库备份分类及详细教程

数据库备份工具分为两个类：
  1、物理备份：是指将数据库的数据目录下的数据文件全部打包
     物理备份的优缺点：优点是数据文件、日志文件全部存在，缺点：占用磁盘空间太大
 
  物理备份方法：
  a、如使用tar打包数据目录，再使用cp复制到其它地方进行备份
  b、专业的物理备份工具,meb官方企业在线备份工具

  2、逻辑备份：是指只将数据库里面的逻辑数据打包走(逻辑数据是指数据库里有效的数据)
     逻辑备份的优缺点：优点是备份出来的纯数据,占用磁盘空间是很小的，缺点：因为备份出来的是sql语句，所以时

间较长
 
  逻辑备份方法：
  使用官方自带的命令：mysqldump命令
   使用select将查询出来的数据导出去： 此方法只适用于单条select执行语句有效。语法：select 字段

1...n  into outfile '路径' from 表名

数据备份时有两种方式：
  1、冷备份：是指数据库不对外提供业务，或数据库处于停止状态时做的备份，叫冷备份，
 好处：备份的数据一致性。
 坏处：必须要停止数据库。
 
  2、热备份：是指数据库不停止，直接做的备份，叫热备份。  --企业通常用的方式
 好处：不需要停止服务器。
 坏处：备份的数据不一致性

数据库备份时要求：
  1、不能对数据库的性能产生较大的影响。一般是在业务不繁忙的情况下做数据备份，一般是：0-6点之间
  2、备份时，要带有目标性备份。如备份某一个库，注：千万不要直接all把数据库里面的所有库全部备份出来。

===========================================================

1，sql语法备份

语法：select  xxx  into outfile filename from table...    --无论是什么存储引擎
本身是一种数据导出的方法，同时可以用来辅助备份，它可以对一个表的其中一列或者某几列做备份（导出）

例：
[root@Client opt]# mkdir backup
 
[root@Client opt]# chown mysql:mysql ./backup/

mysql> select host,user,password into outfile '/opt/backup/mysql_user.txt'  from mysql.user; --把user表

的数据导出（备份）

实例：把aaa库的emp表导出成文本，然后误删掉数据后进行恢复
mysql> select * into outfile '/tmp/mysqlbackup/emp.txt' from emp;

--导出来的emp.txt默认都是以制表符来分隔的

恢复数据两个方法：
语法： load data local infile 数据文件路径 into table 表名 

例：
mysql> create table emp(id int(3),name char(30),sex int(1),sal int(6),phone char(15),deptid int(3));
mysql> insert into emp values(1,"姓名1",1,111,"+8613800138000",10);
Query OK, 1 row affected (0.01 sec)

mysql> insert into emp values(2,"姓名2",2,112,"+8616800138000",20);
Query OK, 1 row affected (0.02 sec)

mysql> insert into emp values(3,"姓名4",0,116,"+8616800138004",30);
Query OK, 1 row affected (0.02 sec)

mysql> select * from emp;
+------+---------+------+------+----------------+--------+
| id   | name    | sex  | sal  | phone          | deptid |
+------+---------+------+------+----------------+--------+
|    1 | 姓名1 |    1 |  111 | +8613800138000 |     10 |
|    2 | 姓名2 |    2 |  112 | +8616800138000 |     20 |
|    3 | 姓名4 |    0 |  116 | +8616800138004 |     30 |
+------+---------+------+------+----------------+--------+
3 rows in set (0.00 sec)

mysql> select * into outfile '/opt/backup/emp.txt'  from emp;      --将emp表的数据备份

到/opt/backup/emp.txt
Query OK, 3 rows affected (0.00 sec)

mysql> truncate table emp;                                 --将emp表数据清空
Query OK, 0 rows affected (0.00 sec)

mysql> select *   from emp;
Empty set (0.00 sec)

mysql> load data local infile '/opt/backup/emp.txt' into table gkd2018.emp;     --将备份的数据导入到

gkd2018.emp表里
Query OK, 3 rows affected (0.02 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from emp;
+------+---------+------+------+----------------+--------+
| id   | name    | sex  | sal  | phone          | deptid |
+------+---------+------+------+----------------+--------+
|    1 | 姓名1 |    1 |  111 | +8613800138000 |     10 |
|    2 | 姓名2 |    2 |  112 | +8616800138000 |     20 |
|    3 | 姓名4 |    0 |  116 | +8616800138004 |     30 |
+------+---------+------+------+----------------+--------+
3 rows in set (0.00 sec)

==========================
2、mysqldump 逻辑备份工具
==========================

导出的是sql语句文件

mysqldump客户端可用来转储数据库或搜集数据库进行备份或将数据转移到另一个SQL服务器(不一定是一个MySQL服务器)

。转储包含创建表和/或装载表的SQL语句。

如果你在服务器上进行备份，并且表均为MyISAM表，应考虑使用mysqlhotcopy，因为可以更快地进行备份和恢复

优点：
无论是什么存储引擎，都可以用mysqldump备成sql语句
缺点:
速度较慢,导入时可能会出现格式不兼容的突发状况

提供三种级别的备份，表级，库级和全库级

shell> mysqldump [options] db_name [tables]  --表级

shell> mysqldump [options] --database DB1 [DB2 DB3...] --库级

shell> mysqldump [options] --all-database  --全库级

恢复的方法，使用反重定向  <  将备份的sql语句导入到数据库中。

表级备份实例：
# mysqldump -uroot -p123 gkd2018 b1 > /opt/backup/gkd2018_b1.sql

表级恢复实例：
方法有两种：

第一种：使用导入的方法
# mysql -uroot -p123  gkd2018  < /opt/backup/gkd2018_b1.sql

第二种：在登陆数据库的情况下，将文件读取进来
mysql> use gkd2018;
mysql> source  /opt/backup/gkd2018_b1.sql

--------------------------------------------------------------

库级备份实例：
# mysqldump -uroot -p123 --database gkd2018  > /opt/backup/gkd2018.sql

恢复实例：方法有两种：

第一种：使用导入的方法
# mysql -uroot -p123   < /opt/backup/gkd2018.sql

第二种：在登陆数据库的情况下，将文件读取进来
mysql> source  /opt/backup/gkd2018p.sql

----------------------------------------------------------

全库级备份实例：
# mysqldump -uroot -p123 --all-database  > /opt/backup/full.sql

全库恢复演示： --注意mysqldump的恢复速度较慢，所以数据太大需要的时间较长
# rm -rf  /opt/mysql/*

恢复：
第一步：初始化数据库：
# mysql_install_db --datadir=/opt/mysql/   --user=mysql

第二步：恢复全库
1：使用导入的方法
# mysql -uroot -p123   < /opt/backup/full.sql

如遇到登陆数据库密码不对，则需要跳过登陆认证动作
# vi /etc/my.cnf
skip-grant-tables       --跳过登陆授权的动作

# service mysqld restart

# mysql < /opt/backup/full.sql

第三步：将skip-grant-tables去掉
# vi /etc/my.cnf
skip-grant-tables    --注释掉

# service mysqld restart

第四步：使用原来的root帐号和密码进行登陆数据库测试。
# mysql -uroot -p123