MYSQL数据的备份与恢复

 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> ALTER TABLE userlist -> ADD sn int(4) AUTO_INCREMENT PRIMARY KEY FIRST;  

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<100   //执行导出操作

   -> 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</root/userdb.sql

Enter password:                                 //验证口令


3)确认新库正常,启用新库:


mysql> 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(//创建新表

-> id int(4) NOT NULL,name varchar(24)

-> );

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;

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

| id | name |

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

| 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 id 1 end_log_pos 545 CRC32 0x98781640 Query thread_id=1 exec_time=0 error_code=0        //插入表记录的起始时间点

SET TIMESTAMP=1389528734/*!*/;

INSERT INTO tb1 VALUES


(1,'Jack'),

(2,'Kenthy'),

(3,'Bob')

/*!*/;

#140112 20:12:14 server id 1 end_log_pos 576 CRC32 0x672e96e5 Xid = 9                                                 //确认事务的时间点

... ...

#140112 20:13:51 server id 1 end_log_pos 740 CRC32 0x253837bb Query thread_id=1 exec_time=0 error_code=0         //删除表记录的时间点



 根据上述日志分析,只要恢复从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;

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

| id | name |

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

| 1 | Jack |

| 2 | Kenthy |

| 3 | Bob |

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

3 rows in set (0.00 sec)







      本文转自Jx战壕  51CTO博客,原文链接:http://blog.51cto.com/xujpxm/1386296,如需转载请自行联系原作者










上一篇:在linux docker容器中挂载使用云存储网关


下一篇:网站前端和后台性能优化15