数据库备份mysqldump应用总结

首页 > 代码库 > 数据库备份mysqldump应用总结

数据库备份mysqldump应用总结

2024-08-12 02:34:00 221人阅读
[root@ansible ~]# mysqldump -uroot -p123456 xxx > /opt/xxx.sql   #备份数据库xxx
[root@ansible ~]# egrep -v "#|\*|--|^$" /opt/xxx.sql 
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,‘martin‘),(2,‘martin‘),(3,‘tom‘),(4,‘marry‘),(5,‘jacky‘),(6,‘脗铆‘);
UNLOCK TABLES;
[root@ansible ~]# mysqldump -uroot -p123456 xxx --default-character-set=latin1 > /opt/xxx1.sql     #指定字符集导出数据库
[root@ansible ~]# egrep -v "#|\*|--|^$" /opt/xxx1.sql                                          
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,‘martin‘),(2,‘martin‘),(3,‘tom‘),(4,‘marry‘),(5,‘jacky‘),(6,‘马‘);   可以看到乱码已经不存在了，建表默认是latin1
UNLOCK TABLES;
[root@ansible ~]# mysql -uroot -p123456 -e "use xxx;drop table test;"   删除表test
[root@ansible ~]# mysql -uroot -p123456 -e "use xxx;show tables;"  可以看到test表已经没有了
[root@ansible ~]# 
[root@ansible ~]# 
[root@ansible ~]# mysql -uroot -p123456 xxx < /opt/xxx1.sql   导回数据库
[root@ansible ~]# 
[root@ansible ~]# mysql -uroot -p123456 -e "use xxx;show tables;"   再次查看test表已经有了
+---------------+
| Tables_in_xxx |
+---------------+
| test          |
+---------------+
[root@ansible ~]# 
[root@ansible ~]# mysqldump -uroot -p123456 -B xxx --default-character-set=latin1 > /opt/xxx1_B.sql   加 -B 选项导出
[root@ansible ~]# cd /opt/
[root@ansible opt]# diff xxx1.sql xxx1_B.sql   对比没有加 -B选项  和加 -B选项时候的区别
18a19,26
> -- Current Database: `xxx`
> --
> 
> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `xxx` /*!40100 DEFAULT CHARACTER SET latin1 */;
> 
> USE `xxx`;
> 
> --
51c59
< -- Dump completed on 2016-08-13 23:07:34
---
> -- Dump completed on 2016-08-13 23:17:33
说明：直观看 加了 -B 参数的作用是在导出数据库的时候增加了 创建数据库和连接数据库的命令了，即如下两条语句
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `xxx` /*!40100 DEFAULT CHARACTER SET latin1 */;
 
USE `xxx`;
[root@ansible opt]# mysql -uroot -p123456 -e "drop database xxx;show databases;"      删除数据库xxx               
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
[root@ansible opt]# mysql -uroot -p123456  < /opt/xxx1_B.sql        因为这是加了 -B 选项导出来的数据，所以前面不需要再指定数据库                                       
[root@ansible opt]# mysql -uroot -p123456 -e "show databases;"       可以看到数据库已经恢复了           
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| xxx                |
+--------------------+
[root@ansible opt]# mysqldump -uroot -p123456 -B xxx --default-character-set=latin1|gzip > /opt/xxx1_B.sql.gz   用gzip压缩后再导出
[root@ansible opt]# ll
-rw-r--r--  1 root root 2069 Aug 13 23:17 xxx1_B.sql
-rw-r--r--  1 root root  805 Aug 13 23:33 xxx1_B.sql.gz   可以看到压缩后导出的数据大小 小了很多
总结：
1、导出数据用-B参数
2、用gzip对备份的数据压缩
mysqldump 的工作原理
利用mysqldump命令备份数据的过程，实际上就是把数据从mysql库里面以逻辑的sql语句的形式输出
备份多个库
[root@centos02 opt]# mysqldump -uroot -p123456 -B test zabbix | gzip > /opt/mul.sql.gz
[root@centos02 opt]# ll
total 4900
-rw-r--r--  1 root root 5012554 Aug 15 07:52 mul.sql.gz
-B 参数是关键，表示连接多个库，并且增加 use db; 和 create database db的信息
[root@centos02 opt]# mysql -uroot -p123456 -e "show databases;"|grep -Evi "database|info|perf"
martin
martin_gbk
martin_utf8
mysql
test
zabbix
[root@centos02 opt]# cat mysql.sh   #备份数据库多个库的脚本
#!/bin/bash
for dbname in `mysql -uroot -p123456 -e "show databases;"|grep -Evi "database|info|perf"`
do
    mysqldump  -uroot -p123456  --events -B ${dbname}|gzip > /opt/${dbname}.sql.gz
done 
[root@centos02 opt]# sh mysql.sh 
[root@centos02 opt]# ll -h
total 5.1M
-rw-r--r-- 1 root root  549 Aug 15 08:34 martin_gbk.sql.gz
-rw-r--r-- 1 root root  845 Aug 15 08:34 martin.sql.gz
-rw-r--r-- 1 root root  547 Aug 15 08:34 martin_utf8.sql.gz
-rw-r--r-- 1 root root  199 Aug 15 08:34 mysql.sh
-rw-r--r-- 1 root root 149K Aug 15 08:34 mysql.sql.gz
-rw-r--r-- 1 root root  545 Aug 15 08:34 test.sql.gz
-rw-r--r-- 1 root root 4.9M Aug 15 08:34 zabbix.sql.gz
mysql> use martin;
Database changed
mysql> show tables;   
+------------------+
| Tables_in_martin |
+------------------+
| student          |
| student1         |
+------------------+
2 rows in set (0.00 sec)
备份单个表
[root@centos02 opt]# mysqldump -uroot -p123456 martin student > one.sql        
martin指的是库名  student指的是表名   这时候不能加 -B参数了   因为 -B参数会将后面识别的都是库名
备份多个表
[root@centos02 opt]# mysqldump -uroot -p123456 martin student student1 > two.sql
martin指的是库名  student student1 指的是两个表名
[root@centos02 alertscripts]# mysqldump -uroot -p123456 -d martin student1 
只备份student1  表的结构  martin代表数据库
[root@localhost ~]# mysqldump -uroot -p123456 -A -B --events|gzip > /opt/all.sql.gz   -A代表所有数据库
[root@localhost mysql]# ll
-rw-rw---- 1 mysql mysql      236 Aug 11 23:14 mysql-bin.000001
-rw-rw---- 1 mysql mysql      488 Aug 12 00:51 mysql-bin.000002
-rw-rw---- 1 mysql mysql      722 Aug 12 23:46 mysql-bin.000003
-rw-rw---- 1 mysql mysql      229 Aug 13 01:06 mysql-bin.000004
-rw-rw---- 1 mysql mysql      244 Aug 17 14:41 mysql-bin.000005
-rw-rw---- 1 mysql mysql       95 Aug 17 14:41 mysql-bin.index
[root@localhost mysql]# mysqldump -uroot -p123456 -A -B -F --events|gzip > /opt/all.sql.gz   -F 会刷新bin-log
[root@localhost mysql]# ll
-rw-rw---- 1 mysql mysql      236 Aug 11 23:14 mysql-bin.000001
-rw-rw---- 1 mysql mysql      488 Aug 12 00:51 mysql-bin.000002
-rw-rw---- 1 mysql mysql      722 Aug 12 23:46 mysql-bin.000003
-rw-rw---- 1 mysql mysql      229 Aug 13 01:06 mysql-bin.000004
-rw-rw---- 1 mysql mysql      287 Aug 17 14:50 mysql-bin.000005
-rw-rw---- 1 mysql mysql      149 Aug 17 14:50 mysql-bin.000006
-rw-rw---- 1 mysql mysql      149 Aug 17 14:50 mysql-bin.000007
-rw-rw---- 1 mysql mysql      106 Aug 17 14:50 mysql-bin.000008
-rw-rw---- 1 mysql mysql      152 Aug 17 14:50 mysql-bin.index
[root@localhost mysql]# mysqldump -uroot -p123456 --master-data=http://www.mamicode.com/1 --compact martin       #--master-data=1 该参数会找bin-log位置"show master status\G;"    
*************************** 1. row ***************************
            File: mysql-bin.000008
        Position: 106
[root@localhost mysql]# mysqldump -uroot -p123456 --master-data=http://www.mamicode.com/2 --compact martin       #--master-data=2 该参数会找bin-log位置，但是语句被注释，实际并不执行>

mysqldump的关键参数说明
1、-B 指定多个库，会增加建库语句和use语句
2、--compact 去掉注释，适合调试输出 生产环境不用
3、-A 备份所有库
4、-F 刷新binlog日志
5、--master-data=http://www.mamicode.com/1 增加binglog日志文件名及对应的位置点
6、-x 锁表
7、-l  只读锁表
8、-d  只备份表结构
9、-t  只备份数据
10、--single-transaction  适合innodb事务数据库备份

生产场景myisam备份：
mysqldump -uroot -p123456 -A -B --master-data=http://www.mamicode.com/1 -x --events|gzip > /opt/all.sql.gz>
本文出自 “厚德载物” 博客，谢绝转载！
数据库备份mysqldump应用总结
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。
联系
我们
首页 > 代码库 > 数据库备份mysqldump应用总结

数据库备份mysqldump应用总结

看完仍有疑问？有类似问题直接问程序猿
