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

mysql备份数据库及恢复

article 2024/11/13 21:54:07

一、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/bash

BAK_DIR="/backup"
user="root"
pass="123456"
time=`date +%F`
echo $time

if [ ! -d $BAK_DIR ]
then
    mkdir $BAK_DIR
fi

/usr/bin/mysqldump -u$user -p$pass  --default-character-set=utf8    -x -B blog|gzip > ${BAK_DIR}/${time}_blog.gz

find $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.kler.cn/a/389098.html

相关文章：

  • 计算机网络易混淆知识点串记
  • 量化交易系统开发-实时行情自动化交易-3.4.2.Okex行情交易数据
  • STM32学习笔记------GPIO介绍
  • Spring Boot 核心配置文件
  • 深度学习服务器租赁AutoDL
  • go do sth和come do sth的区别
  • Elasticsearch和Lucene之间是什么关系？（ChatGPT回答）
  • 小米面试：什么是线程池？工作原理是什么？线程池可以动态修改吗？
  • 【python】路径与文件管理：pathlib库的现代用法
  • 【WRF后处理】基于wrf-python处理wrf运行结果wrfout_d01
  • Linux:基本开发工具
  • 【go从零单排】Rate Limiting限流
  • 成都爱尔小儿眼科及视光团队多人当选“近视防控专家委员会委员”
  • CSS3_3D变换（七）
  • Vue CLI 脚手架
  • ubuntu 22.04 防火墙 ufw
  • imu_tk配置教程（锁死ubuntu18.04，不要22.04）
  • Spark的yarn集群环境搭建
  • C++ OpenCV 理想滤波
  • 挖掘web程序中的OAuth漏洞：利用redirect_uri和state参数接管账户
  • linux centos 安装redis
  • Qt_day4_Qt_UI设计
  • 骨传导耳机排行榜前十分享：十大超值骨传导耳机测评推荐！
  • NoSQL大数据存储技术测试（3）Hadoop和HBase简介
  • AI产品经理：新兴行业的新宠儿，站在风口上的猪都能飞上天
  • UI组件---如何设置el-pagination分页组件的背景色