MYSQL数据的备份与恢复

posir

　　MYSQL数据的备份与恢复
　　1 SQL数据导入导出
　　实验内容：
　　1．使用SQL语句将/etc/passwd文件导入userdb库userlist表，并给每条记录添加自动编号。
　　2．将userdb库userlist表中UID小于100的前10条记录导出，存为/dbak/ulist.txt文件。
　　实验实现：
　　1．将/etc/passwd文件导入MySQL数据库
　　导入后的表结构取决于/etc/passwd配置文件。若一时记不住各字段的含义，也可以查看passwd配置文件的man手册页，找到格式描述相关的说明，比如：
　　# man 5 passwd
　　.. ..
　　There is one entry per line, and each line has the format:  account:password:UID:GID:GECOS:directory:shell                                                 //各字段的顺序、大致用途
　　1）新建userdb库、userlist表
　　以数据库用户root登入MySQL服务：
　　# mysql -u root -p 123456
　　新建userdb库，切换到userdb库：
　　mysql> CREATE DATABASE userdb; Query OK, 1 row affected (0.04 sec)  mysql> USE userdb; Database changed
　　mysql> CREATE DATABASE userdb;
　　Query OK, 1 row affected (0.04 sec)
　　mysql> USE userdb;
　　Database changed
　　新建userlist表，字段设置及相关操作参考如下：
　　mysql> CREATE TABLE userlist( -> username varchar(24) NOT NULL,
　　-> password varchar(48) DEFAULT 'x',
　　-> uid int(5) NOT NULL,
　　-> gid int(5) NOT NULL,
　　-> fullname varchar(48),
　　-> homedir varchar(64) NOT NULL,
　　-> shell varchar(24) NOT NULL
　　-> );
　　Query OK, 0 rows affected (0.17 sec)
　　mysql> DESC userlist; //确认userlist表的结构：
　　2）执行导入操作
　　读取/etc/passwd文件内容，以“:”为分隔，导入到userlist表中：
　　mysql>load data infile "/etc/passwd" //执行导入表中
　　→into table userlist
　　→fields terminated by ":"
　　→lines  terminated by "\n";
　　query ok
　　3）确认导入结果
　　分别统计userlist、userlist2表内的记录个数：
　　mysql> SELECT COUNT(*) FROM userlist;
　　mysql> SELECT COUNT(*) FROM userlist2;
　　2．为userlist表中的每条记录添加自动编号
　　这个只要修改userlist表结构，添加一个自增字段即可。
　　比如，添加一个名为sn的序号列，作为userlist表的第一个字段：
　　1）添加自增主键字段sn

　　mysql>>　　2）验证自动编号结果
　　查看userlist表的前10条记录，列出序号、用户名、UID、GID、宿主目录：
　　mysql> SELECT sn,username,uid,gid,homedir -> FROM userlist LIMIT 10;
　　3．从MySQL数据库中导出查询结果
　　以将userdb库userlist表中UID小于100的前10条记录导出为/dbak/ulist.txt文件为例，首先要确保目标文件夹存在，且msyql用户有权限写入（否则导出会失败）。
　　1）确认存放导出数据的文件夹
　　# mkdir /dbbak                     //若没有此文件夹，可新建
　　# chown mysql /dbbak             //确保mysql有权限写入
　　# ls -ld /dbbak/                 //确认权限 drwxr-xr-x.
　　mysql root 4096 1月 10 17:46 /dbbak/
　　）导出userlsit表中UID小于100的前10条记录
　　如果以默认的'\n' 为行分隔，导出操作同样可不指定LINES TERMINATED BY：
　　mysql> SELECT * FROM userdb.userlist WHERE uid INTO OUTFILE '/dbbak/ulist.txt'
　　-> FIELDS TERMINATED BY ':';
　　Query OK, 26 rows affected (0.08 sec)
　　Query OK, 26 rows affected (0.08 sec)
　　3）确认导出结果
　　返回到Shell命令行，查看/dbbak/ulist.txt文件的行数：
　　# wc -l /dbbak/ulist.txt
　　4）验证两种会导出失败的情况
　　目标文件夹不存在时：
　　mysql> SELECT * FROM userdb.userlist
　　-> INTO OUTFILE '/databackup/ulist.txt'
　　-> FIELDS TERMINATED BY ':';
　　ERROR 1 (HY000): Can't create/write to file '/databackup/ulist.txt' (Errcode: 2 - No such file or directory)
　　目标文件夹存在，但是mysql没有写入权限时：
　　mysql> SELECT * FROM userdb.userlist
　　-> INTO OUTFILE '/opt/ulist.txt'
　　-> FIELDS TERMINATED BY ':';
　　ERROR 1 (HY000): Can't create/write to file '/opt/ulist.txt' (Errcode: 13 - Permission denied)
　　解决办法：1修改目录的权限 ，加入mysql组或者other+w权限
　　2 关闭selinux ，改为disabled
　　2、mysql备份与恢复
　　1．使用mysqldump进行逻辑备份（完全备份）
　　1）备份MySQL服务器上的所有库，将所有的库备份为mysql-alldb.sql文件：
　　#mysqldump -u root -p  --all-databases>/root/mysql-alldb.sql
　　//备份所有库
　　Enter password:                                 //验证口令
　　# file /root/alldb.sql         //确认备份文件类型 /root/alldb.sql: UTF-8 Unicode English text, with very long lines
　　***特别提示：若数据库都使用MyISAM存储引擎，可以采用冷备份的方式，直接复制对应数   据库目录即可；恢复时重新复制回来就行。
　　2）只备份指定的某一个库
　　将userdb库备份为userdb.sql文件：
　　#mysqldump -uroot -p userdb>userdb.sql//备份指定库
　　Enter password:                                 //验证口令
　　3）同时备份指定的多个库
　　同时备份mysql、test、userdb库，保存为mysql+test+userdb.sql文件：
　　#mysqldump -uroot -p -B mysql test userdb>mysql+test+userdb.sql
　　//备份多个库
　　Enter password:                                 //验证口令
　　4)备份指定库下的指定表
　　#mysqldump -uroot -p mysql test>mysql_test.sql //备份mysql库下的test表
　　2．使用mysql命令从备份中恢复数据库、表
　　以恢复userdb库为例，可参考下列操作。通常不建议直接覆盖旧库，而是采用建立新库并导入逻辑备份的方式执行恢复，待新库正常后即可废弃或删除旧库。
　　1）创建名为userdb2的新库：
　　mysql> CREATE DATABASE userdb2;//新建新表
　　Query OK, 1 row affected (0.00 sec)
　　2）导入备份文件，在新库中重建表及数据：
　　#mysqldump -uroot -p userdb2 USE userdb2;                             //切换到新库
　　mysql> SELECT sn,username,uid,gid,homedir    //查询数据，确认可用
　　4）废弃或删除旧库：
　　mysql> DROP DATABASE userdb;//确认新库可用后删除旧库
　　Query OK, 2 rows affected (0.09 sec)
　　3 使用binlog日志
　　1、启用binlog日志
　　1）调整/etc/my.cnf配置，并重启服务
　　# vim /etc/my.cnf //修改my.cnf配置文件
　　[mysqld]
　　.. ..
　　log-bin=mysql-bin             //启用二进制日志，并指定前缀
　　（这里可以指定存放的路径，但是要确保目录有被mysql用户写入的权限，可以更改目录的所有者）
　　.. ..
　　# service mysql restart
　　Shutting down MySQL..                         [确定]
　　Starting MySQL..                                    [确定]
　　2）确认binlog日志文件
　　新启用binlog后，每次启动MySQl服务都会新生成一份日志文件：
　　# ls /var/lib/mysql/mysql-bin.* //确认binlog启用
　　/var/lib/mysql/mysql-bin.000001   /var/lib/mysql/mysql-bin.index
　　重启MySQL服务程序，或者执行SQL操作“FLUSH LOGS;”，会生成一份新的日志：
　　# ls /var/lib/mysql/mysql-bin.*
　　/var/lib/mysql/mysql-bin.000001  /var/lib/mysql/mysql-bin.index /var/lib/mysql/mysql-bin.000002
　　//最后一个是刚刚生成的文件
　　心得总结：使用mysql命令从备份中恢复数据库、表时通常不建议直接覆盖旧库，而是采用
　　建立新库并导入逻辑备份的方式执行恢复，待新库正常后即可废弃或删除旧库。
　　2．利用binlog日志重做数据库操作
　　1）执行数据库表添加操作
　　创建db1·库tb1表，表结构自定义：
　　mysql> CREATE DATABASE db1;//创建新库
　　Query OK, 1 row affected (0.05 sec)
　　mysql> USE db1;
　　Database changed
　　mysql> CREATE TABLE tb1(//创建新表

　　->>　　-> );
　　Query OK, 0 rows affected (0.19 sec)
　　mysql> IN   SERT INTO tb1 VALUES
　　-> (1,'Jack'),//插入3条表记录
　　-> (2,'Kenthy'),
　　-> (3,'Bob');
　　Query OK, 3 rows affected (0.13 sec)
　　Records: 3 Duplicates: 0 Warnings: 0
　　确认插入的表记录数据：
　　mysql> SELECT * FROM tb1;
　　+----+--------+

　　|>　　+----+--------+
　　| 1 | Jack |
　　| 2 | Kenthy |
　　| 3 | Bob |
　　+----+--------+
　　3 rows in set (0.04 sec)
　　2）删除前一步添加的3条表记录
　　执行删除所有表记录操作：
　　mysql> DELETE FROM tb1; Query OK, 3 rows affected (0.00 sec)
　　mysql> DELETE FROM tb1;
　　Query OK, 3 rows affected (0.00 sec)
　　确认删除结果：
　　mysql> SELECT * FROM tb1;
　　Empty set (0.00 sec)
　　3）通过binlog日志恢复表记录
　　binlog会记录所有的数据库、表更改操作，所以可在必要的时候重新执行以前做过的一
　　部分数据操作，但对于启用binlog之前已经存在的库、表数据将不适用。
　　根据上述“恢复被删除的3条表记录”的需求，应通过mysqlbinlog工具查看相关日志文件
　　，找到删除这些表记录的时间点，只要恢复此前的SQL操作（主要是插入那3条记录的操作）即可。
　　# mysqlbinlog /var/lib/mysql/mysql-bin.000002
　　... ...
　　# at 415

　　#140112 20:12:14 server>　　SET TIMESTAMP=1389528734/*!*/;
　　INSERT INTO tb1 VALUES
　　(1,'Jack'),
　　(2,'Kenthy'),
　　(3,'Bob')
　　/*!*/;

　　#140112 20:12:14 server>　　... ...

　　#140112 20:13:51 server>　　根据上述日志分析，只要恢复从2014.01.12 20:12:14到2014.01.12 20:13:50之间的操作即可。可通过mysqlbinlog指定时间范围输出，结合管道交给msyql命令执行导入重做：
　　# mysqlbinlog --start-datetime="2014-01-12 20:12:14" \
　　--stop-datetime="2014-01-12 20:12:50" \ //按时间点恢复数据
　　/var/lib/mysql/mysql-bin.000002 | mysql -u root
　　-p Enter password:                                 //验证口令
　　4）确认恢复结果
　　mysql> SELECT * FROM db1.tb1;
　　+----+--------+

　　|>　　+----+--------+
　　| 1 | Jack |
　　| 2 | Kenthy |
　　| 3 | Bob |
　　+----+--------+
　　3 rows in set (0.00 sec)