下文主要给大家带来备份及恢复MySQL数据库,希望这些内容能够带给大家实际用处,这也是我编辑备份及恢复MySQL数据库这篇文章的主要目的。好了,废话不多说,大家直接看下文吧。
站在用户的角度思考问题,与客户深入沟通,找到新罗网站设计与新罗网站推广的解决方案,凭借多年的经验,让设计与互联网技术结合,创造个性化、用户体验好的作品,建站类型包括:网站设计制作、成都网站建设、企业官网、英文网站、手机端网站、网站推广、主机域名、网络空间、企业邮箱。业务覆盖新罗地区。
经常有网友问,运维工作到底是什么工作,到底做什么?其实,运维工作简单的概括就两件事,第一个是保护公司的数据,第二个是网站7*24小时提供服务。
而对于数据和网站7*24小时提供服务那个跟重要呢?
这个具体要看业务和公司。
例如:银行,金融行业,数据是最重要的,一条都不能丢,可能宕机停机影响就没那么大。
再例如:百度搜索,腾讯qq聊天记录丢失几万条数据,都不算啥。
对于数据来讲,数据最核心的就是数据库核数据,当然也包括其他数据,以下主要讲解的是数据库的数据。
对于绝大数企业来讲,时区数据就时区商机,时区产品,时区客户,公司倒闭…….,因此,数据库的备份与恢复就显得十分重要了。
备份的两种方式:
1. 逻辑备份:以SQL语句的形式,将数据导出,速度慢,跨平台,跨版本,可以跨软件。
2. 物理备份,直接对数据文件备份,cp tar,速度快,不容易跨平台、跨版本。跨软件,常用方法: linux备份工具(cp tar)。
3. xtrabackup
mysql数据库自带了一个很好用的备份命令,就是mysqldump,它的基本使用如下:
命令语法:mysqldump -u 用户名-p密码 数据库名 > 备份的文件名
如下5个范例目录:
(1)备份名字为oldboy数据库
(2)解决备份文件乱码问题
(3)利用上述备份数据进行回复测试
(4)备份时使用-B参数和不适用-B参数的区别及两种备份方法。
(5)优化使用—compact参数使备份文件不输出注释,优化空间。
(6)指定压缩命令压缩备份的MySQL数据
范例操作如下:
(1)范例1:备份名字为oldboy的库
1. 备份前查看oldboy数据
[root@db01 /]# mysql -U -uroot -poldboy123 -S /data/3306/mysql.sock -------- --------- mysql> show databases; +---------------------+ | Database | +---------------------+ | information_schema | | mysql | | oldboy | +---------------------+ 6 rows in set (0.00 sec) mysql> use oldboy Database changed mysql> show tables; +------------------+ | Tables_in_oldboy | +------------------+ | student | | test | +------------------+ 2 rows in set (0.00sec) mysql> select * from test; +------+---------+ | id | name | +------+---------+ | 3 | hehe | | 5 | kaka | | 1 |oldboy | | 2 | oldgirl | | 4 | zuma | +------+---------+ 5 rows in set (0.00sec) mysql> quit Bye
2. 在命令行执行备份命令
[root@db01 /]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy>/opt/oldboy_$(date +%F).sql [root@db01 /]# ll /opt/oldboy_2016-08-28.sql -rw-r--r-- 1 rootroot 2700 Aug 28 23:25 /opt/oldboy_2016-08-28.sql
3. 备份完检查备份完的数据
[root@db01 /]# egrep -v '#|\*|--|^$'/opt/oldboy_2016-08-28.sql DROP TABLE IF EXISTS`student`; CREATE TABLE `student`( `id` int(4) NOT NULL AUTO_INCREMENT, --------------------------------- INSERT INTO `test`VALUES (3,'hehe'),(5,'kaka'),(1,'oldboy'),(2,'oldgirl'),(4,'zuma'); UNLOCK TABLES;
提示:根据以上得出的结果,我们看看到已备份的表结构语句及插入的数据整合的sql语句。
(2)范例2:如果出现乱码指定字符集参数解决备份乱码问题
假如备份数据库备份文件出现乱码情况,用以下参数解决中文乱码,过程如下
1. 查看备份的数据库客户端及服务端的字符集设置
[root@db01 opt]#mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show variableslike '%character%'" +--------------------------+--------------------------------------------+ | Variable_name |Value | +--------------------------+--------------------------------------------+ | character_set_client | utf8 | |character_set_connection | utf8 | |character_set_database | utf8 | | character_set_filesystem | binary | |character_set_results | utf8 | |character_set_server | utf8 | |character_set_system | utf8 | |character_sets_dir | /application/mysql-5.5.49/share/charsets/ | +--------------------------+--------------------------------------------+
2. 指定对应的字符集备份,这里为--default-character_set=utf8 (指定字符集参数)
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock - oldboy>/opt/oldboy_$(date +%F).sql
执行结果:
[root@db01 opt]# mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy >/opt/oldboy_$(date +%F).sql [root@db01 opt]# egrep -v '#|\*|--|^$' /opt/oldboy_2016-08-28.sql
提示:如果出现乱码情况,使用指定支付及查看,就不会在乱码了,其实,即使导出的中文乱码也没关系,导入数据库依然是正常的。
(3)范例2::利用上述备份好的文件进行数据恢复测试
[root@db01 opt]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock oldboy < /opt/oldboy_2016-08-28.sql [root@db01 opt]#mysql-uroot -poldboy123 -S /data/3306/mysql.sock -e "select * fromoldboy.test;" +--------+-----------+ | id | name | +--------+-----------+ | 3 | hehe | | 5 | kaka | | 1 | oldboy | | 2 | oldgirl | | 4 | zuma | +--------+-----------+
(4)范例4:备份时加-B参数以及使用备份文件恢复数据的两种方法
1. 查看不加-B参数备份文件和加-B备份文件区别
不加-B参数备份命令:
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy>/opt/oldboy_$(date +%F).sql
加-B参数备份命令:
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy >/opt/oldboy_B_$(date +%F).sql
使用vimdiff命令比较不加-B参数配置文件和加-B参数配置文件区别
[root@db01 /]# cd /opt/ [root@db01 opt]# vimdiff oldboy_2016-08-28.sql oldboy_B_2016-08-28.sql -- Current Database: `oldboy` -- --
# 结论:
直观看,加上-B参数的作用是增加创建数据库核连接数据库的语句了,我们使用-B的备份文件就可以直接恢复,而不适用-B的备份文件还需要先创建库才可以,区别即如下两条语句:
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `oldboy` USE `oldboy`;
第一种恢复数据(不加-B参数的备份数据护肤数据)
[root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock mysql> show databases; +---------------------+ | Database | +---------------------+ | information_schema | | mysql | | oldboy | | performance_schema | +---------------------+ 6 rows in set (0.00sec) mysql> drop database oldboy; Query OK, 2 rowsaffected (0.11 sec) mysql> show databases; +----------------------+ | Database | +----------------------+ | information_schema | | mysql | | performance_schema | +----------------------+ 5 rows in set (0.00sec) mysql> quit Bye [root@db01 opt]# [root@db01 opt]#
说明:现在使用没有带-B参数备份的备份文件来恢复数据库,在上面已经跟加-B参数的备份文件做了对比,-B参数备份文件中比不带-B参数备份的文件多了创建oldboy数据库核进入oldbou数据库,就是说-B参数的备份文件为我们节省了创建库和进入库的两个步骤,直接将备份文件导入数据库即可,还不用指定那个库,因为里面指定了使oldboy数据库。
第二种备份文件恢复数据(使用加-B参数备份文件恢复)
[root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "dropdatabase oldboy;" [root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showdatabases;" +---------------------+ | Database | +---------------------+ | information_schema | | mysql | | performance_schema | +---------------------+ [root@db01 opt]# [root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showdatabases;" +---------------------+ | Database | +---------------------+ | information_schema | | mysql | | | | performance_schema | +---------------------+
# 说明:在上面已经将加-B参数备份的配置文件和不加-B参数备份的配置文件进行对比和说明,加上-B参数在恢复数据时会自动建库并恢复数据,不加-B则还得先手动建库,所以这是加-B参数的好处,如果说详见一个数据库的数据拷贝到另一个库里可以不加-B参数,到时候直接将备份文件导入到该库即可。
提示:-B不仅只有自动建库use库的一键恢复库数据的应用,还可以指定多个库
(5)范例5:优化备份文件大小减少输出注释(debug调试)
利用mysqldump的—compact参数优化下备份结果:
[root@db01 opt]# mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock -B oldboy -----------------------
带--compact参数备份
mysqldump-uroot -poldboy123 -S /data/3306/mysql.sock -B oldboy --compact >/opt/oldboy_$(date +%F).sql egrep -v '#|\*|--|^$' /opt/oldboy_2016-08-28.sql
--compact参数说明:
注意:--compact此参数不在生产环境使用,用于调试即可。
(6)范例6:指定压缩命令压缩备份的MySQL数据
在将数据库数据备份时通过gzip命令压缩,以节省磁盘空间
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -B oldboy|>/opt/oldboy_$(date +%F).sql. [root@db01 opt]# ll /opt/ total 24 -rw-r--r-- 1root root 2700 Aug28 23:25 oldboy_2016-08-28.sql -rw-r--r-- 1root root 897 Aug 29 01:55 oldboy_2016-08-29.sql.gz -rw-r--r-- 1root root 2843 Aug28 23:54 oldboy_B_2016-08-28.sql
以上例子小结:
1. 备份数据使用-B参数,会在备份数据中增加建库及use库的语句。
2. 备份数据使用-B参数,使得后面可以直接接多个库名。
3. 备份数据使用gzip对备份的数据压缩,可以节省磁盘空间。
4. debug时可以用—compact减少输出,但不用于生产。
5. 指定字符集备份用–default-character-set=字符集(一般不适用)
利用mysqldump命令备份数据的过程,实际上就是把数据从mysql库里以逻辑的sql语句的形式直接输出或者生成备份的文件的过程。
可否举一反三,利用这个备份文件恢复的时候的原理?
备份的数据过滤掉注释,都是SQL语句,结果如下:
[root@db01 opt]# egrep -v '#|\*|--|^$'/opt/oldboy_2016-08-28.sql DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(4)NOT NULL AUTO_INCREMENT, `name`char(20) NOT NULL, `age` tinyint(2)NOT NULL DEFAULT '0', `dept`varchar(16) DEFAULT NULL, PRIMARY KEY(`id`), KEY`index_name` (`name`), KEY`index_name_dept` (`name`,`dept`), KEY`index_123` (`name`,`dept`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; LOCK TABLES `student` WRITE; UNLOCK TABLES; DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `id` int(4)NOT NULL AUTO_INCREMENT, `name`char(20) NOT NULL, PRIMARY KEY(`id`), KEY`index_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULTCHARSET=utf8; LOCK TABLES `test` WRITE; INSERT INTO `test` VALUES(3,'hehe'),(5,'kaka'),(1,'oldboy'),(2,'oldgirl'),(4,'zuma'); UNLOCK TABLES;
1. 查看当前有几个库
[root@db01 ~]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock mysql> show databases; +----------------------+ | Database | +----------------------+ | information_schema | | 3ooo | | mysql | | oldboy | #<== 备份 | oldboy_gbk | #<== 备份 | performance_schema | +----------------------+ 6 rows in set (0.00 sec)
2 开始使用-B参数备份多个库
mysqldump-uroot -poldboy123 -S /data/3306/mysql.sock oldboy_gbkoldboy >/opt/bak_$(date +%F).sql.gz egrep -v '#|\*|--|^$'/opt/bak_2016-08-29.sql.gz
-B参数说明:该参数用于导出若干个数据库,在备份结果中会加入CREATE DATABASE `db_name`和USE db `db_name`两个mysql数据库命令,-B后的参数都将被作为数据库名,该参数比较常用,当-B后的数据库列全时,用-A参数,-A参数意思是转储所有数据中的所有表。
分库备份实际上就是执行一个备份语句备份一个库,如果数据库里面有多个表,就执行多条相同的备份单个库的备份语句就可以备份多个库了,注意每个库都可以对应备份的库作为库名,结尾加sql,
分库意义: 举例,有时一个企业数据库里会有多个库,例如(www,bbs,blog),但是出问题时候的很可能是某一个库,如果在备份时把所有的库都备份成一个数据文件的话,恢复某一个库的数据时就比较麻烦了。
方法1:使用一条一条的命令备份:
mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock -B oldboy>/opt/……… mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock -B oldboy_gbk >/opt/........
方法二:利用命令拼接实现分库备份
[root@db01 ~]# mkdir /server/backup [root@db01 ~]# [root@db01 ~]# ls /server/backup/ 3ooo_2016-08-29.sql.gz oldboy_2016-08-29.sql.gz oldboy_gbk_2016-08-29.sql.gz
单标备份命令语法:mysqldump-u用户名 -p密码 数据库名 表名> 备份的文件名
(1)备份单个表:以下备份oldboy数据库的test表为例
1. 备份前查看oldboy数据库有那些表
[root@db01 ~]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show tables from oldboy;" +-----------------+ | Tables_in_oldboy| +-----------------+ | student | | test | +-----------------+
2. 开始备份单个表(test)表
[root@db01 ~]# [root@db01 ~]# egrep -v '#|\*|--|^$'/opt/tables.sql DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(4)NOT NULL AUTO_INCREMENT, `name`char(20) NOT NULL, `age`tinyint(2) NOT NULL DEFAULT '0', `dept`varchar(16) DEFAULT NULL, PRIMARY KEY(`id`), KEY`index_name` (`name`), KEY`index_name_dept` (`name`,`dept`), KEY`index_123` (`name`,`dept`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; LOCK TABLES `student` WRITE; UNLOCK TABLES;
(2)一条命令分表备份:
数据库分表备份其实就是一条一条的备份,但是又时候一条命令一条命令麻烦,可以使用一条命令将多个数据库表备份,这样更简洁,以下将student和test表备份使用一条命令,具体命令如下:
[root@db01 ~]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show tables from oldboy;" #<== 备份前查看当前oldboy数据库中有哪些表 +------------------+ | Tables_in_oldboy | +------------------+ | student | | test | +------------------+ - [root@db01 ~]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show tables from oldboy;"|egrep -v"Tables_in_oldboy"|sed -r 's#(.*)#mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy \1 >/opt/\1_tables.sql #g' mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboystudent >/opt/student_tables.sql mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy test >/opt/test_tables.sql [root@db01 ~]# [root@db01 ~]# ll -rt /opt/|tail -2 -rw-r--r-- 1root root 2010 Aug 29 12:18 student_tables.sql -rw-r--r-- 1root root 1941 Aug 29 12:18 test_tables.sql
分表备份的缺点
1.被一个完整全被,在做一个分库分表备份。
2. 脚本批量恢复SQL文件
面试题:多个库或者多个表备份到一块,如何恢复单个库或者表?
解答:
a. 第三方测试库,导入到库里,然后把需要的备份出来,最后恢复到正式库里。
b. 单表:grep 表名 bak.sql> 表名.sql
单库:循环过滤库里所有表,grep表名 bak.ssql > 表名.sql(多个表数据)
c. 实现分库分表备份。
以下备份oldboy数据库的多个表
1.查看oldboy数据库中的表,命令如下:
mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showtables from oldboy;"
2. 备份oldboy数据库中的student、test两个表,命令如下
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock >/opt/tables.sql
说明:备份多个表时在oldboy数据库后面指定多个表即可,不需要加-B参数!
命令语法:mysqldump -u用户 -p密码数据库名 表名 -d > 备份文件名
利用mysqldump –d参数值备份数据库里面的表结构,
(1)备份oldboy库的所有表的结构:
备份前输出且过检查
[root@db01 3306]# mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboytest -d –compact , /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `test` ( `id` int(4)NOT NULL AUTO_INCREMENT, `name`char(20) NOT NULL, PRIMARY KEY(`id`), KEY`index_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULTCHARSET=utf8; /*!40101 SET character_set_client =@saved_cs_client */;
2.. 以上输出正确后将其输出到备份文件中进行备份(备份全部表结果),命令如下
mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy -d>/opt/bak_oldboy.sql
命令语法:mysqldump-u用户 -p密码数据库名 表名 -t > 备份文件名
1. 备份oldboy库中test表前输出查看数据是否想要的
[root@db01 3306]# mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy test -t --compact INSERT INTO `test` VALUES(3,'hehe'),(5,'kaka'),(1,'oldboy'),(2,'oldgirl'),(4,'zuma');
2. 以上结果正确开始备份
mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy test >/opt/bak_test.sql
使用--tab参数备份的表结构和数据分离
[root@db01 3306]# mysqldump-uroot -poldboy123 -S /data/3306/mysql.sock oldboy test --compact [root@db01 3306]# ls /tmp/ a.txt b.txt
说明:通过以上的分离后,数据表和sql语句就分开备份了,一个是文本一个是sql语句
-B备份多个库(并添加create和use库的语句)。
-d值备份库表结构。
-t只备份数据(sql语句形式)。
-T分离库表和数据成不同的文件,数据是文本,非SQL语句。
binlog是什么?是记录数据库更新的SQL语句。二进制文件
mysqldump用于定时对某一时刻的数据的全备,例如:00点进行bak.sql.gz
增量备份,当有数据写入到数据库时,还会同时更新的·SQL语句写入到对应的文件里。这个文件就叫做binlog文件。
例如10点丢失数据需要恢复数据:
1. 00点时刻备份的bak.sql.gz数据还原到数据库,这个时候数据恢复到了00点。
2. 00点到10::00数据,就要从binlog里恢复
binglog文件生效需要一个参数:log-bin
个人理解:我们平时对数据库进行备份时定时备份,而不是实时备份数据,假设我们数据库北田在晚上00点备份,而我早上8点数据库出现故障导致数据丢失时,我们怎么去找回00点到8点这之间的数据呢?那就要通过binlog文件啦,而binlog文件会切割的,但是在瓦上备份00点前我们是不需要的,所以在备份时要进行刷险binlog文件,重新生成,就是说重新生成的binlog是00-8:00之间的我们需要,需要靠这之间的binlog去恢复数据,
备份刷新binlog是binlog重新生成需要使用-F参数,生成新的binlog文件
下面测试:
[root@db01 3306]#ll /data/3306/ -rw-rw---- 1 mysqlmysql 2547 Aug 28 15:11 mysql-bin.000001 -rw-rw---- 1 mysqlmysql 6282 Aug 29 01:15 mysql-bin.000002 [root@db01 3306]#mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy test >/opt/bak_test.sql [root@db01 3306]#ll /data/3306/ -rw-rw---- 1 mysqlmysql 2547 Aug 28 15:11 mysql-bin.000001 -rw-rw---- 1 mysqlmysql 6282 Aug 29 01:15 mysql-bin.000002 -rw-rw---- 1 mysqlmysql 150 Aug 29 15:47 mysql-bin.000003
binlog日志切割,确定全备和增量备份的临界点。
-F刷新binlog日志,生成新文件,将来增量恢复从这个文件开始,
--master-data参数说明:
--master-data在备份文件里面会添加CHANGEMASTER语句及binlog文件及位置点信息。
1. 使用-master-data参数备份范例,值为1:为可执行的CHANGEMASTER语句
mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy test >/opt/bak_test.sql #<== 使用--master-data=1参数进行备份 [root@db01 3306]# cat /opt/bak_test.sql #<== 看下使用该参数真假的说明内容 CHANGE MASTER TO MASTER_LOG_FILE='',MASTER_LOG_POS=
# 以上是使用--master-data=1参数增加的内容,实惠执行的,此内容会显示出binlog文件从那个开始切割,而且还有从文件里面的第几行开始
2. 使用-master-data参数备份范例,值为2:注释的--CHANGEMASTER语句。
[root@db01 3306]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sockoldboy test -F --master-data=2 >/opt/bak_test.sql [root@db01 3306]# cat /opt/bak_test.sql CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009',MASTER_LOG_POS=107;
# 以上结果表明值为1和2是不一样的,值为1的是可执行的,值为2的则是注释的
说明:一般-master-data=1时用于做从库,想inotify和backup差不多,如果是单纯的增量恢复只需要-master-data=2即可。
--master-data除啦增量恢复确定临界点外,做主从复制是作用更大
参数 | 解释说明 |
–B | 指定多个库,增加建库语句和use语句, ***** |
--compact | 去掉注释,适合调试输出,生产不适用 |
-A | 备份所有库 |
-F | 刷新binlog日志,生成新文件,将来增量恢复从这个文件开始 |
--master-data | 增加binlog日志文件名及对应的位置点(及CHANGE MASTER语句)。 --master-data=1不注释;--master-data=2注释; |
--evebts | 备份所有,如果想备份数据库中mysql库就要求使用此参数 |
-R | 备份存储过程 |
-d | 只备份库表结构,无数据。 |
-t | 只备份数据,无库表结构,SQL语句形式。 |
-T --tab=name | 库表和数据分离不同文件,数据是文本形式。 |
-x | 锁表,在某一时刻不能往里面更新数据,所以备份要小心。 |
-l –lock-tables | 只读锁表 |
--single-transaction | 适合innodb食物数据库备份 innodb表在备份时,通常启用选项—single-transaction来保证备份的一致性,实际上他的工作原理是设定本次会话的隔离级别为:REPEATABLE READ,以确保本次会话(dump)时,不会看到其他会话已经提交了的数据。 |
-q --quick | 不做缓存查询查询,直接导到标准输出,加快备份速度 |
更多参数:mysqdump –help
myisam引擎企业生产备份命令(适合所有引擎或混合殷勤)
mysqldump -uroot -poldboy123 -A -B -F -R--master-data=2 -x --events|gzip >/opt/alL__$(date +%F).sql.sql.gz
# -F也可以不用,与—master-data有些重复
innodb引擎企业生产备份命令(推荐使用的)
mysqldump -uroot -poldboy123 -A -B -F -R--master-data=2 -x --events - |gzip>/opt/all.sql.gz
# -F也可以不用,与—master-data有些重复
额外补充:
(1)mysqldump是逻辑备份说明
缺点:效率不是很高。
优点:简单,方便。可靠,迁移。
适合于数量不是很特别大的场景,打包前50G以内数据。
(2)如果超过50G可选方案如下:
1. xtrabackup物理备份工具,全备和增量。
2. 物理备份方案,从库停止SQL线程,打包,cp。
(3)什么时候会使用备份的数据?
1. 恢复数据到测试库的时候。
2. 人为通过SQL语句将数据删除的时候。
3. 做主从复制的时候。
提示:
1. 数据恢复和字符集关联很大,如果字符集不正确户导致恢复的数据乱码,
2. mysql命令以及source命令恢复数据库的原理就是把文件的SQL语句,在数据库里重新执行的过程。
5.2 利用source命令恢复数据库
进入mysql数据库控制台,mysql –uroot –p登陆后
mysql> use 数据库
然后使用source命令,后面参数为脚本文件(如这里用到的sql)。
mysql> source oldboy_db.sql #这个文件是系统路径,默认是登录mysql前的系统路径。
source实践操作如下:
[root@db01 3306]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock mysql> show databases; +----------------------+ | Database | +----------------------+ | information_schema | | mysql | | oldboy | | performance_schema | +----------------------+ 4 rows in set (0.00 sec) mysql> drop database oldboy; Query OK, 2 rows affected (0.00 sec mysql> show databases; +----------------------+ | Database | +----------------------+ | information_schema | | mysql | | performance_schema | +----------------------+ 3 rows in set (0.00 sec) mysql> Query OK, 0 rows affected (0.00 sec) ------以下内容输出省略--------- mysql> show databases; +----------------------+ | Database | +----------------------+ | information_schema | | mysql | | oldboy | | performance_schema | +----------------------+ 4 rows in set (0.00 sec)
5.3 利用mysql命令恢复(标准)
命令语法:
mysql -u用户 -p密码 -S /data/3306/mysql.sock 数据库<备份文件
提示:此命令分为两种方式恢复。取决于备份文件备份时是否指定-B参数进行备份
第一种恢复数据(不加-B参数的备份数据护肤数据)
[root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock mysql> show databases; +----------------------+ | Database | +----------------------+ | information_schema | | mysql | | oldboy | | performance_schema | +----------------------+ 6 rows in set (0.00sec) mysql> drop database oldboy; Query OK, 2 rowsaffected (0.11 sec) mysql> show databases; +---------------------+ | Database | +---------------------+ | information_schema | | mysql | | performance_schema | +---------------------+ 5 rows in set (0.00sec) [root@db01 opt]# #<== 先创建oldboy数据库 [root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock oldboy</opt/oldboy_2016-08-28.sql
第二种备份文件恢复数据(使用加-B参数备份文件恢复)
[root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "dropdatabase oldboy;" [root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showdatabases;" +---------------------+ | Database | +---------------------+ | information_schema | | mysql | | performance_schema | +---------------------+ [root@db01 opt]# [root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showdatabases;" +---------------------+ | Database | +---------------------+ | information_schema | | mysql | | | performance_schema | +---------------------+
# 说明:在上面已经将加-B参数备份的配置文件和不加-B参数备份的配置文件进行对比和说明,加上-B参数在恢复数据时会自动建库并恢复数据,不加-B则还得先手动建库,所以这是加-B参数的好处,如果说详见一个数据库的数据拷贝到另一个库里可以不加-B参数,到时候直接将备份文件导入到该库即可。
如果在导出时指定-B参数,恢复时无需指定库恢复,为什么?
因为使用-B参数导出数据时自带了useoldboy和create database oldboy;而恢复时指定类似于use oldboy;
提示:-B不仅只有自动建库use库的一键恢复库数据的应用,还可以指定多个库
方法1:
cd /opt/ gzip bak_2016-08-29.sql.gz mysql -uroot -poldboy123 -S /data/3306/mysql.sock</opt/bak_2016-08-29.sql
方法2:
使用-e参数在命令行查看有哪些数据库
[root@db01 /]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showdatabases;" +----------------------+ | Database | +----------------------+ | information_schema | | 3ooo | | mysql | | oldboy | | oldboy_gbk | | performance_schema | +----------------------+
使用-e参数在命令行执行多条mysql数据命令
[root@db01 /]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "use oldboy; select * from oldboy.test;" +----+----------+ | id name | +----+----------+ | 3 | hehe | | 5 | kaka | | 1 | oldboy | | 2 | oldgirl | | 4 | zuma | +----+----------+
利用mysql –e参数查看SQL线程执行状态
[root@db01 /]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show processlist;" +-----+-----+----------+------+---------+--------+--------+------------------+ | Id | User|Host | db | Command |Time | State | Info | +-----+-----+----------+------+---------+--------+--------+------------------+ | 30 | root|localhost | NULL | Query | 0 | NULL | showprocesslist | +-----+-----+----------+------+---------+--------+--------+------------------+
查看完整的线程状态,此参数才查看满语句是非常有用
[root@db01 /]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show full processlist;" +-----+-----+----------+------+----------+--------+--------+------------------------+ | Id | User|Host | db | Command |Time | State | Info | +-----+-----+----------+------+----------+--------+--------+------------------------+ | 30 | root|localhost | NULL | Query | 0 | NULL | showfull processlist | +-----+-----+----------+------+----------+--------+--------+------------------------+
企业案例:mysql sleep 线程过多的问题案例
mysql> show full processlist; +-----+-----+----------+------+----------+--------+--------+------------------------+ | Id | User|Host | db | Command |Time | State | Info | +-----+-----+----------+------+----------+--------+--------+------------------------+ | 30 | root|localhost | NULL | Query | 0 | NULL | showfull processlist | +-----+-----+----------+------+----------+--------+--------+------------------------+ 1 row in set (0.00 sec)
1. 解决方法:配置文件里修改
[mysqld]
interactive_timeout = 120 #<== 超时时间;此参数设置后wait_timeout自动生效。
wait_timeout = 120 #<== 超时时间
2. 其他解决方法:
PHP程序中,不使用持久链接,及使用mysql_connect而不是pconnect(JAVA调整连接池)
3. PHP程序执行完毕,应该显示调用mysql_close。
4. 逐步分析MySQL的SQL查询及慢查询日志,找到查询国漫的SQL优化之。
使用命令show variables可以查看相关参数是否在数据库中生效
# 查看mysql数据库中所有的变量
[root@db01 /]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show variables;" | Variable_name(变量名称) | Value (值的意思) | +------------------------------------+--------------------------------------+ |auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | -------------------------以下内容省略-----------------------
现在想查看mysql中的server_id,如下:
[root@db01/]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show variables ;” +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 1 | +---------------+-------+ 查看下binlog是否开启 [root@db01 /]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show variables ';" +---------------------------------+---------+ | Variable_name |Value | +---------------------------------+---------+ | log_bin | ON | | log_bin_trust_function_creators | OFF | | sql_log_bin | ON | +---------------------------------+---------+
不重启数据库修改数据库参数,但是要求重启后还能生效。
[root@db01 /]#mysql -uroot -poldboy123 -S /data/3306/mysql.sock mysql> showvariables like '%key_buffer%'; +-------------------+----------+ | Variable_name | Value | +-------------------+----------+ | key_buffer_size |16777216 | +-------------------+----------+ 1 row in set (0.00 sec) mysql> Query OK, 0 rows affected (0.07 sec) mysql> show variables like '%key_buffer%'; +------------------+----------+ | Variable_name | Value | +------------------+----------+ | key_buffer_size |33554432 | +------------------+----------+ 1 row in set (0.00 sec)
不重启数据库更改数据库参数小结:
1. set global key_buffer_size = 1024*1024*32; #<== 临时生效,重启失效
2. 配置文件也要改,编辑/etc/my.cnf,修改key_buffer_size = 32k
6.4 生产场景常用重要命令小结
show processlist; #<== 查看数据库里正在执行的SQL语句,可能无法查看完整的SQL语句
show full processlist; #<== 查看正在执行的完整SQL语句,完整显示(组好使用-e外面看)
set global key_buffer_size = 1024*1024*32; #<== 不重启数据库调整数据库参数,直接生效,重启后失效
show variables; #<== 查看数据库的配置参数信息,例如:my.cnf里参数的生效情况
例如:show variables like '%log_bin';
kill ID; #<== 杀掉SQL线程的命令,ID为线程号
show session status; #<== 查看当前会话的数据库状态信息
show global status; #<== 查看整个数据库运行状态信息,很重要,要分析并做好监控
show engine innodb status; #<== 显示innodb引擎的性能状态(早期版本show innodb status)
计算一天之内,MySQL数据库有多个insert,delete,有没有好方法?
1.定时每天0点,show global status 按天取出对比。
2.按天分析binlog日志,获取数据库不同语句的频率。
mysql常用命令参数:
-u:用户
-p:密码
-S:指定socket
-h:主机
-P:端口
-e:命令行执行数据库命令
mysqladmin的相关命令:
mysqladmin password 123456 #<== 数据库无密码设置密码
mysqladmin -uroot -p123456 password 123123 #<== 修改密码
mysqladmin -uroot -p123456 status #<== 查看状态
mysqladmin -uroot -p123456 extended-status #<== 类似show global status,查看状态信息
mysqladmin -uroot -p123456 -S /data/3306/mysql.sock -i 1 status #<== 每隔1秒查看一次
mysqladmin -uroot -p123456 flush-logs #<== 刷新binlog日志
mysqladmin -uroot -p123456 processlist #<== 查看MySQL数据库进程
mysqladmin -uroot -p123456 processlist -i 1 #<== 实时跟踪进程列表(抓慢查询)
which mysqladmin -uroot -p123456 processlist #<== 实时查询进程列表
mysqladmin -uroot -p123456 -S /data/3306/mysql.sockstutdown #<== 关机
mysqladmin -uroot -p123456 -S /data/3306/mysql.sockvariables #<== 查看变量
对于以上关于如何备份及恢复MySQL数据库,大家是不是觉得非常有帮助。如果需要了解更多内容,请继续关注我们的行业资讯,相信你会喜欢上这些内容的。
本文题目:一文教你如何备份及恢复MySQL数据库
标题来源:https://www.cdcxhl.com/article0/pjhjoo.html
成都网站建设公司_创新互联,为您提供网站维护、面包屑导航、Google、小程序开发、动态网站、网站导航
声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 创新互联