mysql 表级别 增量备份_Mysql运维管理-Mysql增量备份及分库分表备份数据恢复实战12...

1. MySQL数据库的备份与恢复

1.1 Mysqldump的工作原理？

利用mysqldump命令备份的过程，实际上就是把数据从mysql库里以逻辑的sql语句的形式直接输出或者生成备份的文件的过程。

备份的数据过滤注释都是sql语句，结果如下：[root@localhost opt]# egrep -v "#|\*|--|^$" /opt/mysql_bak_B.sql

USE `cuizhong`;

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

`id` int(4) NOT NULL AUTO_INCREMENT,

`name` char(20) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

LOCK TABLES `student` WRITE;

INSERT INTO `student` VALUES (1,'zhangsan'),(2,'lisi'),(3,'wanger'),(4,'xiaozhang'),(5,'xiaowang'),(6,'???'),(7,'小红'),(8,'不认识'),(9,'李四');

UNLOCK TABLES;

1.2备份单个数据库练习多种参数使用

Mysql数据库自带了一个很好用的备份命令，就是mysqldump, 它的基本使用如下：

语法：mysqldump –u用户名 –p密码 数据库名>备份的文件名

范烈1：备份名字为cuizhong的库

a.查看备份前的数据[root@localhost~]# mysql -uroot -p123456 --default-character-set=latin1 -e "select * from cuizhong.student;"

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

| id | name |

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

| 1 | zhangsan |

| 2 | lisi |

| 3 | wanger|

| 4 | xiaozhang |

| 5 | xiaowang |

| 6 | ??? |

| 7 | 小红|

| 8 | 不认识 |

| 9 | 李四|

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

b.执行备份命令[root@localhost~]#mysqldump –uroot -p123456 cuizhong >/opt/mysql_bak.sql

c. 检查备份结果[root@localhost ~]# egrep -v "#|\*|--|^$" /opt/mysql_bak.sql

DROP TABLE IF EXISTS `student`; 删除表

CREATE TABLE `student` (

`id` int(4) NOT NULL AUTO_INCREMENT,

`name` char(20) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

LOCK TABLES `student` WRITE; 锁表

INSERT INTO `student` VALUES (1,'zhangsan'),(2,'lisi'),(3,'wanger'),(4,'xiaozhang'),(5,'xiaowang'),(6,'???'),(7,'å°çº¢'),(8,'ä¸è®¤è¯†'),(9,'æŽå››');

UNLOCK TABLES;

范烈2：设置字符集参数备份解决乱码问题

a.查看备份前数据库客户端及服务器短的字符集设置[root@localhost ~]# mysql -uroot -p123456 -e "show variables like 'character_set%';"

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

| Variable_name| Value|

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

| character_set_client | latin1 |

| character_set_connection | latin1 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results| latin1 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/mysql/share/charsets/ |

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

b.指定字符集备份，这里为—default-character-set=latinlmysqldump -uroot -p123456 --default-character-set=latin1 cuizhong>/opt/mysql_bak.sql

执行结果：[root@localhost~]#mysqldump-uroot-p123456 --default-character-set=latin1 cuizhong>/opt/mysql_bak.sql

[root@localhost ~]# egrep -v "#|\*|--|^$" /opt/mysql_bak.sql

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

`id` int(4) NOT NULL AUTO_INCREMENT,

`name` char(20) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

LOCK TABLES `student` WRITE;

INSERT INTO `student` VALUES (1,'zhangsan'),(2,'lisi'),(3,'wanger'),(4,'xiaozhang'),(5,'xiaowang'),(6,'???'),(7,'小红'),(8,'不认识'),(9,'李四');

UNLOCK TABLES;

恢复测试：[root@localhost ~]# mysql -uroot -p123456 -e "use cuizhong;drop table student;"

[root@localhost ~]# mysql -uroot -p123456 cuizhong

[root@localhost~]# mysql -uroot -p123456 -e "select * from cuizhong.student;"

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

| id | name |

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

| 1 | zhangsan |

| 2 | lisi |

| 3 | wanger|

| 4 | xiaozhang |

| 5 | xiaowang |

| 6 | ??? |

| 7 | 小红|

| 8 | 不认识 |

| 9 | 李四|

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

范例3：备份时加-B参数

和前面的备份文件对比，看看-B的作用[root@localhost ~]# cd /opt/

[root@localhost opt]# diff mysql_bak.sql mysql_bak_B.sql

18a19,26

> -- Current Database: `cuizhong`

> --

>

> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cuizhong` /*!40100 DEFAULT CHARACTER SET latin1 */; 加了一个创建库的语句

>

> USE `cuizhong`; 加了一个USE

>

> --

51c59

< -- Dump completed on 2018-01-30 4:05:37

---

> -- Dump completed on 2018-01-30 4:18:06

提示：-B参数的作用是增加创建数据库和连接数据库的命令，即下面两条语句：CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cuizhong` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `cuizhong`;

测试利用-B的备份进行恢复测试：

(1)删除cuizhong库mysql> show databases;

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

| Database |

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

| information_schema |

| cuizhong