首页 / MYSQL / MySQL的备份还原(mysqldump)

MySQL的备份还原(mysqldump)

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL的备份还原(mysqldump)，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9637字，纯文字阅读大概需要14分钟。

内容图文

MySQL的还原前提是要建立在，有完全备份和二进制日志开启的前提下，并且二进制日志文件和完全备份存放在与数据库文件不同的磁盘上，否则当磁盘发生损坏数据将无法进行恢复。

开启二进制日志

开启二进制日志需要将MySQL中的sql_log_bin和log_bin这两个选项
1.开启sql_log_bin
系统中默认开启sql_log_bin选项所以此处无需修改

MariaDB [(none)]> SHOW VARIABLES LIKE ‘sql_log_bin‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.01 sec)

2.开启log_bin
此选项需要对MySQL的配置文件进行修改，在修改之前需要先创建一个二进制日志存放的位置。注意：不要和数据库存放在统一磁盘内，不要和数据库存放在统一磁盘内，不要和数据库存放在统一磁盘内重要的事情说三遍。

[root@localhost ~]# mkdir /data/bin
[root@localhost ~]# chown -R mysql.mysql /data/bin  #将目录的属主和属组都改为mysql

二进制日志目录创建完毕后，修改配置文件，添加log-bin选项，并指定路径，此处要注意mysql-bin是二进制日志的抬头。

[root@localhost ~]# vim /etc/mysql/my.cnf
log-bin=/data/bin/mysql-bin            #mysql-bin为二进制日志的文件名的抬头

以上配置完成后重启mysql服务

[root@localhost ~]# service mysqld restart
Restarting mysqld (via systemctl):                         [  OK  ]

此时二进制日志目录下已经多出了两个二进制日志文件

[root@localhost ~]# ll /data/bin/
total 12
-rw-rw---- 1 mysql mysql 351 May  6 16:51 mysql-bin.000001
-rw-rw---- 1 mysql mysql  54 May  6 16:51 mysql-bin.index

场景一、磁盘损坏恢复

生产中会发生存放数据的磁盘突然发生损坏而造成数据丢失的情况，此时就需要结合之前所作的完全备份以及二进制日志进行将数据完全恢复

场景模拟

一、对数据库进行备份

[root@localhost ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

[root@localhost ~]# mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz

二、备份完毕后数据库发生小部分变化

MariaDB [(none)]> INSERT hellodb.students(stuid,name,gender,age) VALUE(26,‘linchong‘,‘M‘,30) ;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> INSERT hellodb.students(stuid,name,gender,age) VALUE(27,‘Lujunyi‘,‘M‘,30);
Query OK, 1 row affected (0.00 sec)

三、数据库发生破坏，数据丢失

[root@localhost ~]# rm -rf /data/mysql/*

数据恢复

一、将mysqld服务停止

[root@localhost ~]# service mysqld stop
Stopping mysqld (via systemctl):                           [  OK  ]

二、查看下二进制日志。

由于每次启动服务都会重新生成一个新的二进制日志，所以先查看下二进制日志的编号以免等下在使用二进制日志还原数据时还原了不必要的数据

[root@localhost ~]# ll /data/bin/
total 504
-rw-rw---- 1 mysql mysql    351 May  6 16:51 mysql-bin.000001
-rw-rw---- 1 mysql mysql   9388 May  6 17:01 mysql-bin.000002
-rw-rw---- 1 mysql mysql     81 May  6 17:03 mysql-bin.index

三、重启MySQL服务，初始化数据库

[root@localhost ~]# service mysqld start
Starting mysqld (via systemctl):                           [  OK  ]
[root@localhost ~]# ll /data/mysql/
total 122924
-rw-rw---- 1 mysql mysql    16384 May  6 17:01 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 May  6 17:01 aria_log_control
drwx------ 2 mysql mysql      272 May  6 16:54 hellodb
-rw-rw---- 1 mysql mysql     1298 May  6 17:01 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 May  6 17:03 ibdata1
-rw-rw---- 1 mysql mysql 50331648 May  6 17:03 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Apr 29 12:49 ib_logfile1
-rw-rw---- 1 mysql mysql 12582912 May  6 17:03 ibtmp1
-rw-rw---- 1 mysql mysql        6 May  6 17:03 localhost.localdomain.pid
-rw-rw---- 1 mysql mysql        0 Apr 29 12:57 multi-master.info
drwx------ 2 mysql root      4096 Apr 29 12:49 mysql
-rw-rw---- 1 mysql mysql      351 Apr 29 14:06 mysql-bin.000001
-rw-rw---- 1 mysql mysql      351 May  6 16:50 mysql-bin.000002
-rw-rw---- 1 mysql mysql       38 May  6 16:26 mysql-bin.index
-rw-rw---- 1 mysql mysql        0 May  6 16:50 mysql-bin.state
srwxrwxrwx 1 mysql mysql        0 May  6 17:03 mysql.sock
drwx------ 2 mysql mysql       20 Apr 29 12:49 performance_schema
drwx------ 2 mysql root         6 Apr 29 12:49 test

四、将完全备份解压

[root@localhost ~]# unxz /data/all.sql.xz

五、由于完全备份后数据又发生过改变所以需要利用二进制日志进行还原在利用二进制还原前，先查看下完全备份时二进制日志所在的位置。

[root@localhost ~]# vim /data/all.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000002‘, MASTER_LOG_POS=8946;

六、将完全备份后产生二进制日志的数据导出

[root@localhost ~]# mysqlbinlog --start-position=8946 /data/bin/mysql-bin.000002 > /data/inc.log

七、进入MySQL,停止二进制日志记录

由于接下来的操作是恢复数据，所以此处不需要让二进制日志记录数据。

MariaDB [(none)]> SET sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)

八、导入之前做的完全备份

MariaDB [(none)]> source /root/all.sql

九、导入完全备份后产生的数据

MariaDB [(none)]> source /data/inc.log

十、验证

查看数据库，以及完全备份后增加的内容是否存在

[root@localhost ~]# mysql -e ‘SHOW DATABASES;SELECT * FROM hellodb.students where stuid>25;‘
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
+-------+----------+-----+--------+---------+-----------+
| StuID | Name     | Age | Gender | ClassID | TeacherID |
+-------+----------+-----+--------+---------+-----------+
|    26 | linchong |  30 | M      |    NULL |      NULL |
|    27 | Lujunyi  |  30 | M      |    NULL |      NULL |
+-------+----------+-----+--------+---------+-----------+

场景二、误删除的恢复

生产环境中通常会出现误删除的可能性，此时就需要用到完全备份，和部分修改后的二进制日志来还原数据

场景模拟

一、对数据库进行完全备份

[root@localhost ~]# mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz

一段时间后数据库发生了误删除操作,然后又进行了部分其他的曾的操作

MariaDB [(none)]> DROP TABLE hellodb.students;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> INSERT hellodb.teachers VALUE (5,‘Jiang Jieshi‘,50,‘M‘);
Query OK, 1 row affected (0.00 sec)

此时发现了数据库hellodb.students表被删除

恢复数据库

一、停止MySQL服务

[root@localhost ~]# service mysqld stop
Stopping mysqld (via systemctl):                           [  OK  ]

二、将数据库文件内的文件清空

[root@localhost ~]# rm -rf /data/mysql/*

三、将完全备份解压

[root@localhost ~]# unxz /data/all.sql.xz

四、查看完全备份内，二进制日志的记录点

[root@localhost ~]# vim /data/all.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000003‘, MASTER_LOG_POS=494073;

五、查看二进制日志，并从二级制日志中导出数据

[root@localhost ~]# ll /data/bin/
total 508
-rw-rw---- 1 mysql mysql    351 May  6 16:51 mysql-bin.000001
-rw-rw---- 1 mysql mysql   9388 May  6 17:01 mysql-bin.000002
-rw-rw---- 1 mysql mysql 494449 May  6 17:52 mysql-bin.000003
-rw-rw---- 1 mysql mysql     81 May  6 17:03 mysql-bin.index
-rw-rw---- 1 mysql mysql      8 May  6 17:52 mysql-bin.state
[root@localhost ~]# mysqlbinlog --start-position=494073 /data/bin/mysql-bin.000003 > /data/inc.sql

六、从导出的数据中找到那条误删除的数据将其删除或注释

[root@localhost ~]# vim /data/inc.sql
#DROP TABLE `hellodb`.`students` /* generated by server */

七、重启MySQL服务

[root@localhost mysql]# service mysqld start
Starting mysqld (via systemctl):                           [  OK  ]

八、将二进制日志关闭

MariaDB [(none)]> SET sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)

九、导入完全备份

MariaDB [(none)]> SOURCE /data/all.sql

MariaDB [test]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
#此时被删除的students表已经找回
MariaDB [test]> SHOW TABLES FROM hellodb;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)
#但是误操作后新加的记录还没有找回
MariaDB [test]> SELECT * FROM hellodb.teachers ;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

十、导入二进制日志所生成的修改后的文件

MariaDB [test]> SOURCE /data/inc.sql

#导入后再次查看students表在
MariaDB [test]> SHOW TABLES FROM hellodb;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)
#新添加的记录也有了
MariaDB [test]> SELECT * FROM hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Jiang Jieshi  |  50 | M      |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)

MySQL的备份还原(mysqldump)

标签：generated system 统一 variable 文件内容 ppi like sel

本文系统来源：https://blog.51cto.com/11886307/2389632

内容总结

以上是互联网集市为您收集整理的MySQL的备份还原(mysqldump)全部内容，希望文章能够帮你解决MySQL的备份还原(mysqldump)所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/506806.html

来源：【匿名】

【上一篇】Mysql（7）---存储过程【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【MySQL的备份还原(mysqldump)】教程文章相关的互联网学习教程文章

Mysql备份还原数据库之mysqldump实例及参数详细说明【代码】

我们在运营项目的过程中肯定会遇到备份数据库，还原数据库的情况，我们一般用一下两种方式来处理：1.使用into outfile 和 load data infile导入导出备份数据这种方法的好处是，导出的数据可以自己规定格式，并且导出的是纯数据，不存在建表信息，你可以直接导入另外一个同数据库的不同表中，相对于mysqldump比较灵活机动。我们来看下面的例子：（1）下面的mysql命令是把select的mytable表中的数据导出到/home/db_bak2012文件。sele...

mysqldump 备份直接至压缩文件，提高备份及压缩时间

https://www.cnblogs.com/dbcloud/p/6289577.html 1、备份及压缩分开1备份 mysqldump --single-transaction -hlocalhost --all-databases --triggers --routines --events -P3306 -uroot -pxxxx >/data/test.sql1tar czvf test.tar.gz /data/test.sql --remove-files1 还原 tar xzvf test.tar.gz 1mysql -hlocalhost -uroot -pxxxxx </data/test.sql2、备份后直接压缩写入磁盘1备份mysqldump...

Mysqldump 常用备份操作命令

1.1.1 备份操作create user ‘backup’@’localhost’ identifiedby ‘’;grant select, reload, lock tables,replication client, show view, event, view, process on *.* to‘backup’@’localhost’;mysqldump –ubackup –p –master-data=2–single-transaction –routines –triggers –events mc_orderdb >mc_orderdb.sql 备份一个库mysqldump –ubackup –p –master-data=2–single-transaction –routines –trigge...

auto_mysqldump_data.sh

#!/bin/bash#2019年10月31日22:57:25#auto back mysql‘s databaseIPADDR="10.0.0.7"BAK_TIME=date +%F-%TMYSQL_USER="backup"MYSQL_PASS="123456"MYSQL_COM1="mysqldump"MYSQL_COM2="mysql"SQL_DIR="/data/mysqlback/"SQL_DBS="$*"if [ $# -eq 0 ]; thenecho -e "\033[33m-----------------------------------\033[0m"echo -e "\033[31m-->{Usage:(eg:$0 jf1 | jf1 jf2 .. | all )}\033[0m"echo -e "\033[33m--------------------...

使用mysqldump迁移数据【代码】

1、先停止业务，使用MySQLdump的数据导出工具，将您线下原有数据库数据导出为数据文件mysqldump -hlocalhost -uroot --default-character-set=utf8 -R db_name -p*********** > ./tmp.sql2、查看原数据库上查看用户的授权信息，拷贝到新数据库select a.user,a.host from user a where a.user = ‘user_name;show grants for user_name@‘ip‘;3、在新数据库上执行GRANT USAGE ON *.* TO ‘user_name‘@‘ip‘ IDENTIFIED BY P...

使用mysqldump对数据库备份【代码】

mysqldump是一个简单而有效的数据库备份程序，它会将需要备份的数据库的结构转换为mysql语言组成的一个文本文件，这个文本文件包含了创建这个数据库所进行的所有mysql语句，所以当我们恢复时，便是程序读取这些语句重新创建数据库、创建表以及插入数据的过程，但因为其是单线程的，所以对于比较大的数据库操作会比较慢。mysqldump支持InnoDB热备，MyISAM的温备，可以基于二进制日志进行增量备份，根据二进制文件文件中记录的操作...

mysqldump命令详解【代码】

今天遇到一个需求就是需要导出MySQL数据库中的函数和存储过程，其他对象无需导出，上网查了一些资料，感觉mysqldump命令各种参数值的研究一下。1、只导出函数和存储过程，其他无需导出[root@multi-mysql ~]# mysqldump -uoldcatm -poldcat123 -S /data/3306/mysql.sock -R -ntd oasys > /home/oldcat/data/backup/tmp.sql 参数说明： -R --routines：Dump stored routines (functions and procedures) 导出存储过程和函数。原文：h...

MySQL备份-mysqldump【代码】

一先建立备份账号create user ‘backup‘@‘localhost‘ identified by ‘pass4word‘;二赋予权限grant select,reload,lock tables,replication client,show view,event,process on *.* to ‘backup‘@‘localhost‘;附加，查看创建表的语句show create table table_name; 三备份单个数据库 mysqldump -ubackup -ppass4word --master-data=2 --single-transaction --routines --triggers --events tk_test >tk.sql 原文：https://w...

mysql使用mysqldump 与 mysqlimport进行数据库迁移

1.导入数据库1）sourcesource /home/platvt/product/pc2_create_tables.sql2）mysqlimport使用mysqlimport导入数据：出现情况：（1）出现 Error: 1146[platvt@qa-qd-62-180 tmp]$ mysqlimport -uroot -p --fields-terminated-by=‘\t‘ test students Enter password: mysqlimport: Error: 1146, Table ‘test.students‘ doesn‘t exist, when using table: students在导入之前必须数据库里面有对应的表，所以必须先在数据库里面...

mysqldump使用分库备份的脚本

#!/bin/sh#Author:LuojqBAKTIME=$(date +%Y-%m-%d%H:%M:%S)USER_PSWD=123456USER_NAME=rootMYLOGIN="mysql -u${USER_NAME} -p${USER_PSWD}"DUMP_CMD="/usr/bin/mysqldump -u${USER_NAME} -p${USER_PSWD} -B --master-data=2 --single_transaction --flush-logs --skip-add-drop-table"DATABASE="$($MYLOGIN -e "show databases;"|egrep -vi "_schema|mysql")" for dbname in ${DATABASE}doMYDIR=/data/backup/${dbname}[ ! -d ${...

逻辑备份,mysqldump,SELECT…INTO OUTFILE,恢复

逻辑备份mysqldumpmysqldump备份工具最初由Igor Romanenko编写完成，通常用来完成转存（dump）数据库的备份以及不同数据库之间的移植，例如从低版本的MySQL数据库升级到高版本的MySQL数据库，或者从MySQL数据库移植到Oracle和SQL Server等数据库等。mysqldump的语法如下：mysqldump [arguments] ＞ file_name如果想要备份所有的数据库，可以使用--all-databaes选项：mysqldump --all -databases ＞ dump.sql如果想要备份指定的数据...

mysqldump如何针对某些数据库进行备份?针对某个数据库进行备份?【代码】

需求描述:　　通过mysqldump工具对mysql服务器中的某几个数据库进行备份.　　或者就对其中的一个数据库进行备份.操作过程:1.通过--databases参数后面加上数据库的名字进行备份[mysql@redhat6 MysqlDb_Backup]$ mysqldump -uroot -p --single-transaction --master-data --flush-logs --databases top_server test01 > Backup_2018-04-09.sql Enter password: [mysql@redhat6 MysqlDb_Backup]$ ls -ltr total 2111792 -rw-rw-r-- 1...

mysqldump完全备份shell脚本

#!/bin/bash #?Date:2017-10 #?Version:0.1 #?Description:db_user="root" myisam_db="mysql" db_passwd="123456" host="localhost" backup_dir="/opt/backup/mysql/sql" log_file="/opt/backup/mysql/sql/mysql_backup.log" keep_days=30 mysql="$(which?mysql)" mysqldump="$(which?mysqldump)" db_list=$(mysql?-h$host?-u$db_user?-p$db_passwd?-e?‘show?databases‘|egrep?-v?‘Database|information_schema|mysql|performan...

MySQL--当mysqldump --single-transaction遇到alter table【代码】【图】

部分生产环境采用mysqldump --single-transaction的方式在夜间进行数据库备份，而同事恰好在备份期间执行了alter table操作，操作部分成功部分失败，为啥呢？在mysqldump对single-transaction参数的解释为：Creates a consistent snapshot by dumping all tables in asingle transaction. Works ONLY for tables stored instorage engines which support multiversioning (currentlyonly InnoDB does); the dump is NOT guaranteed...

mysql mysqldump只导出表结构或只导出数据的实现方法

mysql mysqldump 只导出表结构不导出数据复制代码代码如下:mysqldump --opt -d 数据库名 -u root -p > xxx.sql 备份数据库复制代码代码如下:#mysqldump　数据库名　>数据库备份名 #mysqldump　-A　-u用户名　-p密码　数据库名>数据库备份名 #mysqldump　-d　-A　--add-drop-table　-uroot　-p　>xxx.sql 1.导出结构不导出数据复制代码代码如下:mysqldump　--opt　-d　数据库名　-u　root　-p　>　xxx.sql　　 2.导出数据不导出结...

首页 / MYSQL / MySQL的备份还原(mysqldump)

MySQL的备份还原(mysqldump)

内容导读

内容图文

开启二进制日志

场景一、磁盘损坏恢复

场景模拟

一、对数据库进行备份

二、备份完毕后数据库发生小部分变化

三、数据库发生破坏，数据丢失

数据恢复

一、将mysqld服务停止

二、查看下二进制日志。

三、重启MySQL服务，初始化数据库

四、将完全备份解压

五、由于完全备份后数据又发生过改变所以需要利用二进制日志进行还原在利用二进制还原前，先查看下完全备份时二进制日志所在的位置。

六、将完全备份后产生二进制日志的数据导出

七、进入MySQL,停止二进制日志记录

八、导入之前做的完全备份

九、导入完全备份后产生的数据

十、验证

场景二、误删除的恢复

场景模拟

一、对数据库进行完全备份

恢复数据库

一、停止MySQL服务

二、将数据库文件内的文件清空

三、将完全备份解压

四、查看完全备份内，二进制日志的记录点

五、查看二进制日志，并从二级制日志中导出数据

六、从导出的数据中找到那条误删除的数据将其删除或注释

七、重启MySQL服务

八、将二进制日志关闭

九、导入完全备份

十、导入二进制日志所生成的修改后的文件

内容总结

内容备注

内容手机端

【MySQL的备份还原(mysqldump)】教程文章相关的互联网学习教程文章

备份 - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程