Mysql根据需要备份数据

因为数据的重要性,所以要定时的备份数据,当灾难来临时有一手,确保数据不丢失。这就是DBA的职责,保证数据的安全和持续服务。

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

mysql数据库自带了一个很好的备份命令,就是Mysqldump,它的基本使用如下:

语法:mysqldump -u 用户名 -p 数据库名 >备份的文件名

1、备份单个表

语法:mysqldump -u用户名 -p密码 数据库名 表名>备份的文件名

  1. [root@server ~]# mysqldump -uroot -p123456 www pet >/tmp/pet.sql 

提示:此时www为库名,后面就是pet表了。

  1. DROP TABLE IF EXISTS `pet`; 
  2.  
  3. CREATE TABLE `pet` ( 
  4.  
  5.   `name` varchar(20) NOT NULL, 
  6.  
  7.   `owner` varchar(20) DEFAULT NULL, 
  8.  
  9.   `species` varchar(20) DEFAULT NULL, 
  10.  
  11.   `sex` char(1) DEFAULT NULL, 
  12.  
  13.   `birth` date DEFAULT NULL, 
  14.  
  15.   `death` date DEFAULT NULL 
  16.  
  17. ENGINE=MyISAM DEFAULT CHARSET=utf8
  18.  
  19. LOCK TABLES `pet` WRITE; 
  20.  
  21. INSERT INTO `pet` VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL),('Fluffy','Harold','cat','f','1993-02-04',NULL),('Claws','Gwen','cat','m','1994-03-17',NULL),('Buffy','Harold','dog','f','1989-05-13',NULL),('Fang','Benny','dog','m','1990-08-27',NULL),('Bowser','Diane','dog','m','1979-08-31','0000-00-00'),('Chirpy','Gwen','bird','f','1998-09-11',NULL),('Whistler','Gwen','bird','','1997-12-09',NULL),('Slim','Benny','snake','m','1996-04-29',NULL); 

 

2、备份多个表

  1. [root@server ~]# mysqldump -uroot -p123456 www pet student >/tmp/pet_student.sql 

但是这样备份是把两个表的数据也备份在一个文件里了,在企业中一个库有大表小表的,有时候可能是小表出了问题。只需要恢复一个小表,上面的多表备份就很难拆开了。所要就要用到分库分表备份。

  1. mysqldump -uroot -p123456 www pet >/tmp/pet.sql 
  2.  
  3. mysqldump -uroot -p123456 www student >/tmp/student.sql 

 或者利用For循环实现简单分库备份。

 

  1. [root@server ~]# vi fenku_bak_mysql.sh 
  2.  
  3. for dbname in `mysql -uroot -p123456 -e "show databases;"|egrep -v"database|infor|per 
  4.  
  5. for|perfor|test|mysql"` 
  6.  
  7. do  
  8.  
  9.         mysqldump -uroot -p123456 --events -B $dbname|gzip >/tmp/bak/${dbname}_bak.sql. 
  10.  
  11. gz 
  12.  
  13. done 

3、备份表结构 -d

 

  1. [root@server ~]# mysqldump -uroot -p123456 -d www pet student >/tmp/table.sql 
  2.  
  3. [root@server ~]# egrep -v "#|\*|--|^$" /tmp/table.sql  
  4.  
  5. DROP TABLE IF EXISTS `pet`; 
  6.  
  7. CREATE TABLE `pet` ( 
  8.  
  9.   `name` varchar(20) NOT NULL, 
  10.  
  11.   `owner` varchar(20) DEFAULT NULL, 
  12.  
  13.   `species` varchar(20) DEFAULT NULL, 
  14.  
  15.   `sex` char(1) DEFAULT NULL, 
  16.  
  17.   `birth` date DEFAULT NULL, 
  18.  
  19.   `death` date DEFAULT NULL 
  20.  
  21. ENGINE=MyISAM DEFAULT CHARSET=utf8
  22.  
  23. DROP TABLE IF EXISTS `student`; 
  24.  
  25. CREATE TABLE `student` ( 
  26.  
  27.   `id` int(11) NOT NULL, 
  28.  
  29.   `name` varchar(20) NOT NULL, 
  30.  
  31.   `sex` char(1) DEFAULT NULL 
  32.  
  33. ENGINE=MyISAM DEFAULT CHARSET=utf8

 

4、只备份表数据:-t   --no-create-info   Don't write table creation info

 

  1. [root@server ~]# mysqldump -uroot -p123456 -t www pet student >/tmp/data.sql 
  2.  
  3. [root@server ~]# egrep -v "#|\*|--|^$" /tmp/data.sql  
  4.  
  5. LOCK TABLES `pet` WRITE; 
  6.  
  7. INSERT INTO `pet` VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL),('Fluffy','Harold','cat','f','1993-02-04',NULL),('Claws','Gwen','cat','m','1994-03-17',NULL),('Buffy','Harold','dog','f','1989-05-13',NULL),('Fang','Benny','dog','m','1990-08-27',NULL),('Bowser','Diane','dog','m','1979-08-31','0000-00-00'),('Chirpy','Gwen','bird','f','1998-09-11',NULL),('Whistler','Gwen','bird','','1997-12-09',NULL),('Slim','Benny','snake','m','1996-04-29',NULL); 
  8.  
  9. UNLOCK TABLES; 
  10.  
  11. LOCK TABLES `student` WRITE; 
  12.  
  13. UNLOCK TABLES; 

 

mysqldump -uroot -p123456 -A -B --events |gzip >/tmp/a.sql.gz #备份Mysql里所有数据(包括库和表)

 

-F:刷新binlog参数;

 

  1. [root@server tmp]# ls /data/mysql/ 
  2.  
  3. bbs      ib_logfile0  mysql             mysql-bin.index  server.pid  www 
  4.  
  5. ibdata1  ib_logfile1  mysql-bin.000001  server.err       test 
  6.  
  7. [root@server tmp]# mysqldump -uroot -p123456 -A -B -F --events |gzip >/tmp/b.sql.gz 
  8.  
  9. [root@server tmp]# ls /data/mysql/ 
  10.  
  11. bbs          ib_logfile1       mysql-bin.000002  mysql-bin.000005  server.pid 
  12.  
  13. ibdata1      mysql             mysql-bin.000003  mysql-bin.index   test 
  14.  
  15. ib_logfile0  mysql-bin.000001  mysql-bin.000004  server.err        www 

 

 

--master-data=1 :此参数会自动定位到binlog文件和位置点

 

  1. [root@server tmp]# mysqldump -uroot -p123456 --master-data=1 www 
  2.  
  3. -- MySQL dump 10.13  Distrib 5.1.73, for unknown-linux-gnu (x86_64) 
  4.  
  5. -- 
  6.  
  7. -- Host: localhost    Database: www 
  8.  
  9. -- ------------------------------------------------------ 
  10.  
  11. -- Server version   5.1.73-log 
  12.  
  13.   
  14.  
  15. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; 
  16.  
  17. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; 
  18.  
  19. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; 
  20.  
  21. /*!40101 SET NAMES utf8 */; 
  22.  
  23. /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; 
  24.  
  25. /*!40103 SET TIME_ZONE='+00:00' */; 
  26.  
  27. /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; 
  28.  
  29. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; 
  30.  
  31. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; 
  32.  
  33. /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; 
  34.  
  35.   
  36.  
  37. -- 
  38.  
  39. -- Position to start replication or point-in-time recovery from 
  40.  
  41. -- 
  42.  
  43.   
  44.  
  45. CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005'MASTER_LOG_POS=106

 

mysqldump的关键参数说明:

--help

-B指定多个库,备份出来的文件会有建库语句和use语句。

--compact 去掉注释,适合调试输出,一般生产环境不用

-A所有库

-F刷新binlog日志

--master-data 增加binlog日志文件名及对应的位置点。

-x --lock-all-tables  锁表  所有人都不能对表操作了。

-l--lock-tables 只读锁表 不能写只可读

-d只备份表结构

-t只备份数据

--single-transaction 适合innodb事务数据备份

 

Innodb表在备份时,通常启动选项--single-transaction来保证备份的一致性,实际上它的工作原理是设定本次会话的隔离级别为:REPEATABLE READ,以确保本次会话(dump)时,不会看到其它会话已经提交了的数据。

Myisam:常规备份命令

  1. mysqldump -uroot -p123456 -A -B -F --master-data=2 -x|gzip>/tmp/all.sql.gzS 

 

Innodb:常规备份命令(工作中推荐使用的引擎)

 

  1. mysqldump -uroot -p123456 -A -B -F --master-data=2 --single-transaction|gzip>/tmp/all.sql.gz 

--master-data作用:

--master-data=2 结果:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=106;

 

--master-data=1 结果:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=106;

 

区别在于前面的:“--” ,此符号在mysqlbinlog日志是注释作用,意思说=2是只给出位置点而已,=1时等命令,是可执行的,特别在主从同步的时间要用到。