当前位置： 首页 > news >正文

mysql备份数据库及恢复

news 2024/11/18 1:44:02

一、mysql表的存储引擎介绍:
mysql表的存储引擎最常见的有MyISAM和Innodb，mysql5.1及之前默认使用MyISAM，mysql5.5.5之后默认使用Innodb。这个创建表的时候可以手动指定（根据具体业务）
由于mysqldump备份数据时需要锁表来保证数据一致性，如果数据量比较大时，备份时间过长会影响线上业务，所以针对数据不是很大的数据库采用 [–lock-all-tables, -x]来锁表，对于数据量比较大备份时间较长，使用–lock-tables 和–single-transaction

MyISAM：针对数据量小，访问少使用，速度快
Innodb：针对数据量大，访问量大的

(1) 锁表参数
--lock-all-tables,  -x：    备份数据时锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁
--lock-tables,  -l：          只锁定当前表，以保证数据的一致性，缩短锁表时间 （针对mysql5.1之前的存储引擎MyISAM）
--single-transaction：不用锁表，以保证数据的一致性，缩短备份时间  （针对mysql5.5.5之后的存储引擎Innodb）(2) 查看当前数据库所支持的引擎
mysql> show engines;(3) 查看数据表使用的引擎
mysql> show create table test;

二、使用案例
注意：数据库导出最好分库备份,不要复制，最好手动敲。
1、备份某一个数据库（全局锁表）

mysqldump -uroot -p123456 -h 127.0.0.1 --default-character-set=utf8  --events  --opt  -x -B blog|gzip > /tmp/$(date +%F)_blog.gz
mysqldump -uroot -p123456 -h 127.0.0.1 --default-character-set=utf8  --events  --opt  -x -B blog > blog.sql参数：-u：指定用户名-p：指定密码--default-character-set=utf8 ：指定字符集-x：--lock-all-tables 提交请求锁定所有数据库中的所有表，以保证数据的一致性。  -A：指定所有数据库-d: 只导出表结构,不导出表数据 -t：只导出表数据    -B：导出数据库时会自动创建数据库，这样导入数据库时，就不用手动创建数据库也不用指定数据库了。-F :  --flush-logs，刷新binlog日志（生成新的binlog日志）
[root@Oldboy ~]# grep "CREATE DATABASE" wordpress.sql 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `wordpress` /*!40100 DEFAULT CHARACTER SET latin1 */;--events：  导出事件，否则会提示Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.--opt  :这只是一个快捷选项，等同于同时添加 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset 选项。本选项能让 mysqldump 很快的导出数据，并且导出的数据能很快导回。该选项默认开启，但可以用 --skip-opt 禁用。注意，如果运行 mysqldump 没有指定 --quick 或 --opt 选项，则会将整个结果集放在内存中。如果导出大数据库的话可能会出现问题。

2、备份所有数据库(全局锁表)
不要复制，最好手动敲。

mysqldump -uroot -p123456  --default-character-set=utf8 --events  --opt -x -A -B|gzip > /tmp/$(date +%F)_all_databases.gz

3、备份某一个数据库（局部锁表，保证数据一致性，缩短锁表时间）不要复制，最好手动敲。

(1) 针对存储引擎-MyISAM
mysqldump -uroot -p123456  --default-character-set=utf8  --opt --lock-tables  -B blog|gzip > /tmp/$(date +%F)_blog.gz
(2) 针对存储引擎-Innodb（不用锁表，保证数据一致性，缩短锁表时间）
mysqldump -uroot -p123456  --default-character-set=utf8  --opt --single-transaction  -B blog|gzip > /tmp/$(date +%F)_blog.gz

4、导出阿里云数据库

(1) 导出数据库
mysqldump -uredcore -p -h rm-2ze907703b7ia790w.mysql.rds.aliyuncs.com -P 3306 --default-character-set=utf8  --events  --opt  -x -B rdc_manager > /tmp/rdc_manager.sql(2) 导入数据库
mysql -uroot -p -h 127.0.0.1 -P 3306 rdc_manager < rdc_manager.sql(3) 只导出表结构和存储过程
# mysqldump -uclouddeep -p -h rm-2ze907703b7ia790w.mysql.rds.aliyuncs.com -P 3306 -ntd -R databasename > backupfile.sql (4) 遇到问题
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
# 解决
# mysqldump -uredcore -p -h deepcloudsdp-beta-rds-i.mysql.rds.aliyuncs.com -P 3306 --set-gtid-purged=OFF  rdc_manager > bate_rdc_manager20210603.sql         # 导出数据库
# mysqldump -uredcore -p -h deepcloudsdp-beta-rds-i.mysql.rds.aliyuncs.com -P 3306 --set-gtid-purged=OFF -ntd -R rdc_manager > procdure_bate_rdc_manager20210603.sql  # 导出存储过程

5、导入单个数据库

(1) 使用mysql导入 
mysql -uroot -p123456  database_name < /tmp/2016-07-04_database_name
(2) 使用source导入
mysql> use test1;                                                       进入数据库
mysql> source /tmp/2016-05-24_test1                    导入表数据

6、导入全库

(1) 使用mysql导入
[root@Oldboy tmp]# mysql -uroot -p123456 < /tmp/2016-05-24_all_databases
(2) 使用source导入
mysql> source /tmp/2016-05-24_all_databases

7、mysql备份脚本

#!/bin/bashBAK_DIR="/backup"
user="root"
pass="123456"
time=`date +%F`
echo $timeif [ ! -d $BAK_DIR ]
thenmkdir $BAK_DIR
fi/usr/bin/mysqldump -u$user -p$pass  --default-character-set=utf8    -x -B blog|gzip > ${BAK_DIR}/${time}_blog.gzfind $BAK_DIR/* -type f -name "*.gz" -mtime +7|xargs rm -f

8、生产场景备份

(1) 针对 myisam：（混合引擎以Myisam为主）
mysqldump  -uroot  -p123456 --default-character-set=utf8  -A -B  -x  --events --master-data=2   |gzip >/opt/$(date +%F)_all.sql.gz      （备份所有数据库）
mysqldump  -uroot -p123456  --default-character-set=utf8   -B -x  --events  --master-data=2    oldboy|gzip >/opt/$(date +%F)_oldboy.sql.gz  （分库备份）参数：
--default-character-set=utf8 ：指定字符集，用于给人看的方便，不影响数据库本身
-A：备份所有数据库
-B：导出数据库时会自动创建数据库，还原时无需指定数据库
-x：--all-lock-tables ：备份时锁定所有数据表以保证数据一致性，该选项自动关闭--single-transaction和--lock-tables，--lock-tables无法保证数据一致性
--master-date=2：增加binlog日志文件名以及对应的位置点 ，需要开启log-bin功能--opt：使导出和还原的速度更快（可选）
此Mysqldump命令参数是可选的，如果带上这个选项代表激活了Mysqldump命令的quick，add-drop-table，add-locks，extended-insert，lock-tables参数，也就是通过–opt参数在使用Mysqldump导出Mysql数据库信息时不需要再附加上述这些参数。
–quick：代表忽略缓冲输出，Mysqldump命令直接将数据导出到指定的SQL文件。
–add-drop-table：顾名思义，就是在每个CREATE TABEL命令之前增加DROP-TABLE IF EXISTS语句，防止数据表重名。
–add-locks：表示在INSERT数据之前和之后锁定和解锁具体的数据表，你可以打开Mysqldump导出的SQL文件，在INSERT之前会出现LOCK TABLES和UNLOCK TABLES语句。
–extended-insert (-e)：此参数表示可以多行插入。(2) 针对 InnoDB：
mysqldump  -uroot  -p123456 --default-character-set=utf8  -A -B  --single-transaction --events  --master-data=2   |gzip >/opt/$(date +%F)_all.sql.gz      （备份所有数据库）
mysqldump  -uroot -p123456  --default-character-set=utf8   -B --single-transaction  --events  --master-data=2    oldboy|gzip >/opt/$(date +%F)_oldboy.sql.gz  （分库备份）参数：
--single-transaction：针对InnoDB引擎，使用这个参数锁表，实际上并不会锁表但是依然可以保证数据一致性，这就是InnoDB和Myisam的区别

python脚本备份：

#!/usr/bin/env python
#!_*_coding:utf-8_*_
#__author__="lihongxing"
'''
mysql 备份脚本
'''
import os,time
now_time = time.strftime("%Y-%m-%d", time.localtime())
show_database_cmd = 'mysql -uroot -pmysql_748@wyx -e"show databases" | grep -v "test\|Database\|information_schema\|performance_schema"'
#列出所有需要备份的数据库
bak_cmd = "mysqldump -uroot -pmysql_748@wyx -e --disable-keys=0"    #备份的命令
bak_path = "/Data/mysql_backup/mysql_sql_bak/"
#获取所有数据库实例
def get_database(cmd):ret = os.popen(cmd)cmd_ret = ret.read().strip().split("\n")return cmd_ret
#真正执行备份的
def bak_database(show_cmd,reday_bak_cmd,bak_path):databases_list = get_database(show_cmd)for database in databases_list:try:database_obj = os.system(bak_cmd+" %s > %s%s.sql"%(database,bak_path,database)) 备份的命令except:print"%s备份异常"%(database)os.system('tar zcf /Data/mysql_backup/%s.tar.gz %s'%(now_time,bak_path))
bak_database(show_database_cmd,bak_cmd,bak_path)

总结一句话：

        databases=`'mysql -uroot -pmysql_748@wyx -e"show databases" | grep -v "test\|Database\|information_schema\|performance_schema"'mysqldump -uroot -p'xxxxx'    [参数]  ${databases} > ${databases}-$(date +%F-%M).sql

http://www.mrgr.cn/news/69079.html

相关文章：

  • 0 -vscode搭建python环境教程参考（windows)
  • 博睿数据登顶中国应用性能管理及可观测性APMO市场份额第一！
  • DVWA靶场通关——SQL Injection篇
  • Go语言的零值可用性：优势与限制
  • 鲸鱼机器人和乐高机器人的比较
  • Vue3 动态获取 assets 文件夹图片
  • 【LeetCode】每日一题 2024_11_9 设计相邻元素求和服务（构造，哈希）
  • RHCE的学习（14）
  • 2024-11-2025-03 - 通用人工智能技术 - 问卷调研 - 软考 - 流雨声
  • 域名+服务器+Nginx+宝塔使用SSL证书配置HTTPS
  • PostgreSQL 之递归查询
  • 如何在微服务架构中优化微信 Access Token 管理：解决频率限制与过期问题的最佳实践
  • SpringBoot2~~~
  • WOA-RF|鲸鱼算法-随机森林-回归-降维|多变量特征筛选降维-回归预测|Matlab
  • JAVA开源项目 服装销售平台 计算机毕业设计
  • 嵌入式linux中gpio子系统的开发与实现
  • 2024年最新互联网大厂精选 Java 面试真题集锦（JVM、多线程、MQ、MyBatis、MySQL、Redis、微服务、分布式、ES、设计模式）
  • 丹摩征文活动 |【AI落地应用实战】文本生成语音Parler-TTS + DAMODEL复现指南
  • 什么是上拉和下拉
  • 弄巧成拙的 PFC(Priority-based Flow Control)
  • SpringBoot框架：共享汽车行业的技术升级
  • 腾讯云双十一程序员的「采购单拼团攻略」
  • 进程相关内容
  • (done) gdb 在系统编程中的调试技巧
  • Redis中的数据结构
  • 四期书生大模型实战营（【基础岛】- 第1关 | 书生·浦语大模型开源开放体系）