MySQL备份

spring℡ 2023-07-17 原文

一、直接拷贝数据库文件

首先关闭数据库
[root@host1 mysql]# pwd
/var/lib/mysql
压缩、拷贝到host2
[root@host1 mysql]# tar czf db.tar.gz *
[root@host1 mysql]# scp db.tar.gz 192.168.157.11:/beifen

host2
首先关闭数据库，清理/var/lib/mysql/下的内容
[root@host2 beifen]# tar xf db.tar.gz /var/lib/mysql/
将/beifen/db.tar.gz解压到/var/lib/mysql

此时登录host2的数据库可以看到两个数据库中的内容相同

二、mysqldump备份数据库

1、备份

帮助 mysqldump --help

[root@host1 ~]# mysqldump -u登录用户 -p'密码' 数据库 表名 > 备份文件名称.sql
#数据库 表名:这里可以是指定数据库或者是某个库表
-A备份所有数据库，
-B会将创建数据库语句也备份，
-b备份数据库表的结构不备份数据。

备份数据库
[root@host1 ~]# mysqldump -uroot -p'MySQL@123' db -B  > beifen1.sql  
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@host1 ~]# ls
beifen1.sql
备份成压缩包形式
[root@host1 ~]# mysqldump -uroot -p'MySQL@123' db -B | gzip > beifen1.sql.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.

备份数据库表
[root@host1 ~]# mysqldump -uroot -p'MySQL@123' db course > beifen2.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@host1 ~]# ls beifen*
beifen1.sql  beifen1.sql.gz  beifen2.sql

2、备份还原

(1)完全备份还原

备份还原
还原数据库
方法一
在mysql中source后跟备份文件存放路径
mysql> source /backup/MySQL/beifen1.sql

方法二
[root@host1 ~]# mysql -uroot -p'MySQL@123' < /backup/MySQL/beifen1.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

这个时候就可以看到被还原的数据库

还原数据库表
mysql> source /backup/MySQL/beifen2.sql

[root@host1 MySQL]# mysql -uroot -p'MySQL@123' db < ./beifen2.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

压缩包还原
[root@host1 MySQL]# zcat ./beifen1.sql.gz | mysql -uroot -p'MySQL@123'
mysql: [Warning] Using a password on the command line interface can be insecure.

(2)增量备份

首先是要先开启数据库的二进制日志的log_bin

方法参考MySQL日志管理_spring℡的博客-CSDN博客

第一步先做完全备份

#eg

(1)创建一个新的数据库表resume.test

mysql> select * from test;
+--------+------+------+------------+------+
| name   | weig | age  | hobby      | sex  |
+--------+------+------+------------+------+
| 白熊   | 80.0 |   10 | dance,sing | m    |
| 胖达   | 75.0 |   10 | dance,sing | m    |
+--------+------+------+------------+------+
2 rows in set (0.00 sec)

mysql> insert test values ('克洛伊','35','8','sing','f'),('棕熊','76','9','dance','m');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

(2)此时完全备份

[root@host1 MySQL]# mysqldump -uroot -p'MySQL@123' --opt -F --single-transaction -B resume > /backup/MySQL/resume.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

(3)接着再往数据库表里面插入两条数据

mysql> insert test values ('小白','45','8','sing','f'),('小胖','66','9','dance','m');

(4)删除数据库resume,刷新日志

mysql> drop database resume;
Query OK, 1 row affected (0.01 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

(5)此时会增加一个新的二进制日志

[root@host1 mysql]# ll mysql-bin.00000*
-rw-r-----. 1 mysql mysql   177 Apr 17 14:34 mysql-bin.000002
-rw-r-----. 1 mysql mysql   177 Apr 17 14:54 mysql-bin.000003
-rw-r-----. 1 mysql mysql   177 Apr 17 14:59 mysql-bin.000004
-rw-r-----. 1 mysql mysql   177 Apr 17 15:28 mysql-bin.000005
-rw-r-----. 1 mysql mysql  3303 Apr 17 15:37 mysql-bin.000006
-rw-r-----. 1 mysql mysql 18407 Apr 20 15:03 mysql-bin.000007
-rw-r-----. 1 mysql mysql  2059 Apr 20 16:44 mysql-bin.000008
-rw-r-----. 1 mysql mysql   665 Apr 20 16:46 mysql-bin.000009

(6)备份还原

第一步还原完全备份
mysql> source /backup/MySQL/resume.sql

第二步还原增量备份
[root@host1 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000009

方法一：根据时间备份还原
[root@host1 MySQL]# mysqlbinlog --start-datetime="2022-04-20 16:45:32" --stop-datetime="2022-04-20 16:45:57"  /var/lib/mysql/mysql-bin.000009 -r resume1.sql
[root@host1 MySQL]# mysql -uroot -p'MySQL@123' < resume1.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

方法二：根据位置点备份还原
[root@host1 MySQL]# mysqlbinlog --start-position=293 --stop-position=455  /var/lib/mysql/mysql-bin.000009 -r resume2.sql
mysql> source /backup/MySQL/resume2.sql

mysql> select * from test;
+-----------+------+------+------------+------+
| name      | weig | age  | hobby      | sexs  |
+-----------+------+------+------------+------+
| 白熊      | 80.0 |   10 | dance,sing | m    |
| 胖达      | 75.0 |   10 | dance,sing | m    |
| 克洛伊    | 35.0 |    8 | sing       | f    |
| 棕熊      | 76.0 |    9 | dance      | m    |
| 小白      | 45.0 |    8 | sing       | f    |
| 小胖      | 66.0 |    9 | dance      | m    |
+-----------+------+------+------------+------+
6 rows in set (0.00 sec)

#GTID全局事务标识符

5.6以后添加5.7做了加强

#binlog日志的GTID新特性

(1)启动gtid

mysql> desc mysql.gtid_executed
   -> ;
+----------------+------------+------+-----+---------+-------+
| Field          | Type       | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| source_uuid    | char(36)   | NO   | PRI | NULL    |       |
| interval_start | bigint(20) | NO   | PRI | NULL    |       |
| interval_end   | bigint(20) | NO   |     | NULL    |       |
+----------------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | OFF       |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | OFF       |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

由于gtid_mode=OFF所以需要开启

[root@host1 ~]# vim /etc/my.cnf

gtid-mode=on
enforce-gtid-consistency=on

(2)使用DDL和DML语句之后查看gtid会发生改变

DDL
mysql> create database dba;
Query OK, 1 row affected (0.01 sec)
mysql> use dba
Database changed
mysql> create table test (sno char(12),name char(13),cno char(8));
Query OK, 0 rows affected (0.01 sec)

DML

开启事务mysql> begin;

mysql> insert test(sno,name) values('122110','小白');
Query OK, 1 row affected (0.00 sec)
#不管是中间进行几次插入

事务提交mysql> commit;
最后79c7390e-ad1f-11ec-9593-000c29ae9e04:只会增一

#查看binlog

mysql> SHOW binlog EVENTS in 'mysql-bin.000012';

(3)数据库还原

首先有一个数据库 dba 库里面有表tb1,且表内插入了一些简单数据。

mysql> select * from tb1;
+-----+--------+
| sno | name   |
+-----+--------+
| 100 | 小明   |
| 101 | 小李   |
+-----+--------+
2 rows in set (0.00 sec)

创完之后删除tb1

mysql> show binlog events in 'mysql-bin.000012';

此时在/backup/MySQL下编写备份还原部分内容

因为9是删表部分因此恢复1-8

（1）数据备份
因为GTID幂等性，这里我添加了--skip-gtids

[root@host1 MySQL]# mysqlbinlog  --skip-gtids --include-gtids="79c7390e-ad1f-11ec-9593-000c29ae9e04:1-8" mysql-bin.000012 -r gtid_1.sql

此时就有了gtid_1.sql

（2）数据恢复
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

[root@host1 MySQL]# mysql -u root -p'MySQL@123'< gtid_1.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

此时在数据库中就可以查看被删除的数据库已经库表

三、mydumper备份数据库

安装：Gitee 极速下载/mydumper

yum install https://github.com/mydumper/mydumper/releases/download/v0.11.5/mydumper-0.11.5-1.el7.x86_64.rpm

1、备份 mydumper

mydumper -uroot -pMySQL@123 -B 数据库名 -T 表名 -o /存放路径

备份数据库db

[root@localhost ~]# mydumper -u root -p MySQL@123 -B db -o /db
[root@localhost ~]# tree /db
/db
├── db.course.00000.sql
├── db.course-metadata
├── db.course-schema.sql
├── db-schema-create.sql
├── db.student.00000.sql
├── db.student-metadata
├── db.student-schema.sql
└── metadata

0 directories, 8 files

2、还原 myloader

myloader -u root -p MySQL@123 -d /db -o -B db

先右边再左边

eg：备份数据库表 db.course，删除表后还原

备份
[root@localhost db]# mydumper -u root -p MySQL@123 -B db -T course -o /db11
#这里-B后面跟数据库名，-T后面跟表名，-o代表输出到那个位置
[root@localhost db]# cd /db11
[root@localhost db11]# ls
db.course.00000.sql  db.course-metadata  db.course-schema.sql  db-schema-create.sql  metadata

删除表course
mysql> drop table course;
Query OK, 0 rows affected (0.02 sec)

恢复表course
[root@localhost db11]# myloader -u root -p MySQL@123 -d /db11 -B db  -o
这里直接恢复数据库就会恢复表内容

四、借用windows下的软件备份

1、首先先授权一个用户

grant all on . to jim@'%' identified by 'Jim@12345';

2、此时软件登录数据库

将需要备份的数据库如下备份，后删除再导入即可

五、lvm备份、恢复

1、数据迁移到逻辑卷

先关闭mysql服务，虚拟机添加一块新的磁盘（这里添加6G）

lvs：查看磁盘分区情况

lsblk：磁盘剩余内存

此时重启虚拟机即可

（1）进行逻辑卷管理

创建物理卷
[root@localhost ~]# pvcreate /dev/sdb
  Physical volume "/dev/sdb" successfully created.

创建卷组
[root@localhost ~]# vgcreate vg1 /dev/sdb
  Volume group "vg1" successfully created

创建逻辑卷
[root@localhost ~]# lvcreate -n lv_mysql -L 4G vg1
  Logical volume "lv_mysql" created.
 
格式化逻辑卷（xfs的文件系统只支持扩大逻辑卷，ext4的文件系统支持扩大和缩小逻辑卷）
[root@localhost ~]# mkfs.xfs /dev/vg1/lv_mysql

（2）将/var/lib/mysql/下所有内容压缩打包到/backup/MySQL/

[root@localhost mysql]# tar -czvf /backup/MySQL/mysql.tar.gz .
查看
[root@localhost mysql]# tar -tvf /backup/MySQL/mysql.tar.gz

（3）将lv_mysql挂载到/var/lib/mysql下此时mysql下内容将自动被清空，接着进行解压

[root@localhost mysql]# mount /dev/vg1/lv_mysql /var/lib/mysql
[root@localhost mysql]# tar xf /backup/MySQL/mysql.tar.gz -C .

（4）接着检查所属用户和组是否为mysql，如果不是则需要修改

[root@localhost lib]# ls -ld mysql
drwxr-x--x. 15 mysql mysql 4096 May  4 16:42 mysql

此时启动mysql即可
systemctl start mysqld

（5）登录数据库

show databases;
和原来数据库内容一样

#创建用户并授权方法

mysql> grant all on *.* to admin@'192.168.157.%' identified by 'Admin@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

2、快照备份数据库

(1)锁表（读锁）

mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)

(2)逻辑卷创建快照

[root@host1 ~]# lvcreate -n lv_mysql_s -L 500M -s /dev/vg1/lv_mysql
  Logical volume "lv_mysql_s" created.

[root@host1 ~]# lvs
  LV         VG     Attr       LSize   Pool Origin   Data%  Meta%  Move Log Cpy%Sync Convert
  root       centos -wi-ao---- <17.00g                                                      
  swap       centos -wi-ao----   2.00g                                                      
  lv_mysql   vg1    owi-aos---   4.00g                                                      
  lv_mysql_s vg1    swi-a-s--- 500.00m      lv_mysql 0.00

(3)解锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

(4)挂载

[root@host1 ~]# mkdir /mnt/mysql -p
[root@host1 ~]# mount -o nouuid /dev/vg1/lv_mysql_s /mnt/mysql

#如果没有rsync需要进行安装（yum install rsync -y）

(5)备份

[root@host1 mnt]# rsync -av /mnt/mysql/ /mysql_back/

(6)此时关掉mysql 配置/etc/my.cnf

(7)接着启动mysql并登录可以看到数据库表和开始一样

(8)卸载快照并删除

[root@host1 ~]# umount /mnt/mysql/ && lvremove /dev/vg01/lv_mysql_s

#脚本方式进行（1）-（8）操作

cd /mysql/scripts

[root@host1 scripts]# vim my.sh

#!/bin/bash
back_dir=/backup/`date +%F`
[ -d $back_dir ]|| mkdir -p $back_dir
echo "flush tables with read lock; system lvcreate -n lv_mysql_s -L 500M -s /dev/vg1/lv_mysql;unlock tables;" |mysql -uroot -pMySQL@123 &>/dev/null
[ -d /mnt/mysql/ ] || mkdir -p /mnt/mysql/
mount -o nouuid /dev/vg1/lv_mysql_s /mnt/mysql/
rsync -a /mnt/mysql/ $back_dir
if [ $? -eq 0 ];then
       umount /mnt/mysql/ && lvremove -f /dev/vg01/lv_mysql_s &>/dev/null
fi

执行脚本

[root@host1 scripts]# sh my.sh

MySQL 备份 E5 E6 数据库 linux centos7/rhel7 运维

有关MySQL备份的更多相关文章

使用canal同步MySQL数据到ES - 2
文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co
ruby-on-rails - 无法安装 mysql2 0.3.14 gem - 2
我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin
ruby - 如何使用 ruby mysql2 执行事务 - 2
我已经开始使用mysql2gem。我试图弄清楚一些基本的事情——其中之一是如何明确地执行事务(对于批处理操作，比如多个INSERT/UPDATE查询)。在旧的ruby-mysql中，这是我的方法:client=Mysql.real_connect(...)inserts=["INSERTINTO...","UPDATE..WHEREid=..",#etc]client.autocommit(false)inserts.eachdo|ins|beginclient.query(ins)rescue#handleerrorsorabortentirelyendendclient.commi
ruby-on-rails - 当我通过 rvm 使用 rails3 时，如何在 ubuntu 上安装 mysql2 gem？ - 2
我正在尝试绕过rails配置这个极其复杂的迷宫。到目前为止，我设法在ubuntu上设置了rvm(出于某种原因，ruby在ubuntu存储库中已经过时了)。我设法建立了一个Rails项目。我希望我的测试项目使用mysql而不是mysqlite。当我尝试“rakedb:migrate”时，出现错误:“!!!缺少mysql2gem。将其添加到您的Gemfile:gem'mysql2'”当我尝试“geminstallmysql”时，出现错误，告诉我需要为安装命令提供参数。但是，参数列表很大，我不知道该选择哪些。如何通过在ubuntu上运行的rvm和mysql获取rails3？谢谢。
Centos7-yum安装mysql-修改密码-无密码登录-安全配置 - 2
目录1、yum安装mysql修改密码(1)在mysql里面修改(2)第二种方式，利用mysqladmin修改密码2、没有密码，登录mysql修改密码3、mysql的安全设置1、yum安装mysql在CentOS中默认安装有MariaDB（MySQL的一个分支），安装完成之后可以直接覆盖MariaDB。rpm-qa|grepmariadb查询是否安装了mariadbrpm-e--nodepsmariadb-libs-5.5.60-1.el7_5.x86_64卸载mariadwgethttp://dev.mysql.com/get/mysql57-community-release-el7-11.
ruby - 安装 dm-mysql-adapter 时出错 - 2
我是Ruby的新手。我安装了DataMapper并且正在尝试安装dm-mysql-adapter-1.0.2gem。但是当我尝试安装时，出现以下错误。我正在使用ubuntu操作系统。vinoth@vinoth-laptop:~/Downloads$geminstalldm-mysql-adapter-1.0.2----with-mysql-lib=/usr/lib/mysql----with-mysql-conf=/usr/bin/mysqlWARNING:Installingto~/.gemsince/home/vinoth/gemsand/home/vinoth/gems/bina
ruby-on-rails - gem 列表中的 mysql2 gem 但获取项目找不到 gem - 2
我目前正在构建一个需要mysql2gem的RoR项目。我成功安装了gem。因为它出现在我的gem列表中。[root@vc2cmmka035538nsimple_cms]#gemlist***LOCALGEMS***actionmailer(3.2.3)actionpack(3.2.3)activemodel(3.2.3)activerecord(3.2.3)activeresource(3.2.3)activesupport(3.2.14,3.2.3)arel(3.0.2)bigdecimal(1.1.0)builder(3.2.2,3.0.0)bundler(1.1.5)c2c_li
ruby - 如何在 heroku 中使用自己的 mysql 数据库服务器？ - 2
我想使用托管在我自己服务器上的mysql数据库。我已经更改了DATABASE_URL和SHARED_DATABASE_URL配置变量以指向我的服务器，但它仍在尝试连接到heroku的amazonaws服务器。我该如何解决？最佳答案根据Herokudocumentation，更改DATABASE_URL是正确的方法。Ifyouwouldliketohaveyourrailsapplicationconnecttoanon-Herokuprovideddatabase,youcantakeadvantageofthissamemec
ruby - 如何设置mysql2时区选项以删除查询警告 - 2
使用mysql2做查询总是得到警告/usr/local/lib/ruby/gems/1.9.1/gems/mysql2-0.2.6/lib/active_record/connection_adapters/mysql2_adapter.rb:463:warning::database_timezoneoptionmustbe:utcor:local-defaultingto:local我确实看到了时区选项Mysql2现在支持两个时区选项::database_timezone-thisisthetimezoneMysql2willassumefieldsarealreadystored
ruby-on-rails - 将 Rails/ClearDB App 推送到 Heroku 错误 'Can' t 连接到 '127.0.0.1' 上的 MySQL 服务器 - 2
每次我跑:gitpushherokumaster我收到以下错误:Running:rakeassets:precompilerakeaborted!Can'tconnecttoMySQLserveron'127.0.0.1'我在运行rails-vRails3.2.11和ruby-vruby1.9.3p194(2012-04-20revision35410)[x86_64-darwin12.2.0]我已经通过HerokuCLI安装了ClearDB，它似乎工作正常，但我无法找出这个错误。这是我用于生产的yml:production:adapter:mysql2encoding:utf8hos