封尘网

让学习成为一种习惯!

Mysql根据需要备份数据

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

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

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

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

备份单个表

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

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

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

DROP TABLE IF EXISTS `pet`;

CREATE TABLE `pet` (
  `name` varchar(20) NOT NULL,
  `owner` varchar(20) DEFAULT NULL,
  `species` varchar(20) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `death` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

LOCK TABLES `pet` WRITE;

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);

备份多个表

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

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

mysqldump -uroot -p123456 www pet >/tmp/pet.sql

mysqldump -uroot -p123456 www student >/tmp/student.sql

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

[root@server ~]# vi fenku_bak_mysql.sh

for dbname in `mysql -uroot -p123456 -e "show databases;"|egrep -v"database|infor|per

for|perfor|test|mysql"`

do

    mysqldump -uroot -p123456 --events -B $dbname|gzip >/tmp/bak/${dbname}_bak.sql.gz

done

备份表结构 -d

[root@server ~]# mysqldump -uroot -p123456 -d www pet student >/tmp/table.sql
[root@server ~]# egrep -v "#|\*|--|^$" /tmp/table.sql

DROP TABLE IF EXISTS `pet`;
CREATE TABLE `pet` (
  `name` varchar(20) NOT NULL,
  `owner` varchar(20) DEFAULT NULL,
  `species` varchar(20) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `death` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `sex` char(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

[root@server ~]# mysqldump -uroot -p123456 -t www pet student >/tmp/data.sql
[root@server ~]# egrep -v "#|\*|--|^$" /tmp/data.sql

LOCK TABLES `pet` WRITE;

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);

UNLOCK TABLES;
LOCK TABLES `student` WRITE;
UNLOCK TABLES;

备份Mysql里所有数据(包括库和表)

mysqldump -uroot -p123456 -A -B --events |gzip >/tmp/a.sql.gz

-F:刷新binlog参数;

[root@server tmp]# ls /data/mysql/
bbs      ib_logfile0  mysql             mysql-bin.index  server.pid  www
ibdata1  ib_logfile1  mysql-bin.000001  server.err       test
[root@server tmp]# mysqldump -uroot -p123456 -A -B -F --events |gzip >/tmp/b.sql.gz

[root@server tmp]# ls /data/mysql/
bbs          ib_logfile1       mysql-bin.000002  mysql-bin.000005  server.pid
ibdata1      mysql             mysql-bin.000003  mysql-bin.index   test
ib_logfile0  mysql-bin.000001  mysql-bin.000004  server.err        www

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

[root@server tmp]# mysqldump -uroot -p123456 --master-data=1 www
-- MySQL dump 10.13  Distrib 5.1.73, for unknown-linux-gnu (x86_64)
--
-- Host: localhost    Database: www
-- ------------------------------------------------------
-- Server version   5.1.73-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
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:常规备份命令

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

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

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时等命令,是可执行的,特别在主从同步的时间要用到。

提醒:本文最后更新于 2844 天前,文中所描述的信息可能已发生改变,请谨慎使用。