因为数据的重要性,所以要定时的备份数据,当灾难来临时有一手,确保数据不丢失。这就是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时等命令,是可执行的,特别在主从同步的时间要用到。