#mysqlbackup

[larevuegeek]

Comment réinitialiser le mot de passe Root sous Mysql ou MariaDb ?

Vous avez perdu le mot de passe ROOT de votre serveur MysQL et vous n'arrivez plus à vous connecter ? Pas de panique, il est possible de réinitialiser le mot de passe principal moyennant quelques lignes de commande.

[thecampcodes]

Tutorial: How to Backup MySQL Database using PHP About How to Backup MySQL Database using PHP To beautify the presentation of this tutorial, I've used Bootstrap which is included in the downloadable of this tutorial, but if you want, you can download Bootstrap using this link. Creating a Backup Function Next, we create the function that backup and download our MySQL database. Please create ... https://bit.ly/3vB7Bcn

[singhkalpana007]

Now it’s easy to keep customer’s data safely thanks to SuiteCRM Backup which can perform this function. 

 Outright Store offers this Backup extension where you can use it thrice a day for every user and some advanced features it has. 

Click here to know more -: http://bit.ly/2jZMbWu 

[storeoutrightcrms]

Automation of Backup and Restore of MySQL Databases Using SugarCRM or SuiteCRM. Backup all your important data from remote locations. Click here to know additional features -: https://store.outrightcrm.com/product/suitecrm-backup-restore-manager/

[outrightsystems-blog]

Importance of #Databackup and recovery process!

So you are into a business!, which runs on Crucial Data, data manipulated the whole day. It does not matter how big you are, data is important.

#SuiteCRMBackup

#DBBackup

#MySqlBackup

[sandeep2363]

Reset the password for the user in MySQL Server

Reset the password for the user in MySQL Server

Change the password for the user in MySQL Server Connect with superuser root and check the user present: mysql> select host,user from mysql.user; +-----------+------------------+ | host | user | +-----------+------------------+ | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | mysqlbackup | | localhost | mysqlbackup1 | | localhost | root | |…

[all-database-solutions]

Restoring a Failed member in InnoDB Cluster

Restoring a Failed member in InnoDB Cluster For MySQL DBA its a common issue that one of the MySQL InnoDB Cluster node got corrupted. In that case we need to restore MySQL cluster node from mysqlbackup. Usually backup in MySQL InnoDB cluster is taken from one of the node in the cluster, if the node corrupted and backup node are same then you can simply restore the backup and start group replication. But if the backup is node taken from the corrupted node ,We need to take some additional steps. In this blog we will discuss about the same scenario. Assume that we have three MySQL node mysqlhost01,mysqlhost02 & mysqlhost03. And the node which got corrupted is mysqlhost03 and backup is taken from the mysqlhost02.We will follow below steps to restore failed node mysqlhost03: 1) Move the MySQL backup from mysqlhost02 to mysqlhost03. 2) Change parameter group_replication_start_on_boot to false.set parameter group_replication_start_on_boot to false so that post MySQL restart Group replication services are not started automatically. Connect to mysqlhost03Server: mysql>select * from performance_schema.persisted_variables where variable_name like 'group_replication_start_on_boot%'; mysql>set persist group_replication_start_on_boot=false; 3) Stop MySQL on node mysqlhost03 and take backup of the existing auto.cnf and mysqld-auto.cnf . This is required to preserve the UUID for the mysqlhost03. 4) Remove all the data file and other database related files.Check the variables innodb_data_home_dir, innodb_log_group_home_dir, and innodb_undo_directory and make sure there is no files in these directory else backup restoration might fail. 5) Restore the backup mysqlhost02 to mysqlhost03. (Using Restore MySQL InnoDB Cluster from mysqlbackup) mysqlbackup --defaults-file=/etc/my.cnf \ --datadir=/var/lib/mysql \ --backup-image=/backups/my.mbi_2206_1429  \ --backup-dir=/tmp/restore_`date +%d%m_%H%M` copy-back-and-apply-log The command above assumes that the binary logs and relay logs on mysqlhost02 and mysqlhost03 have the same base name and are at the same location on the two servers. If these conditions are not met, you should use the --log-bin and --relay-log options to restore the binary log and relay log to their original file paths on mysqlhost03 . For example, if you know that on mysqlhost03  the binary log's base name is mysqlhost03 -bin and the relay-log's base name is mysqlhost03 -relay-bin, your restore command should look like:mysqlbackup --defaults-file=/etc/my.cnf \  --datadir=/var/lib/mysql \  --backup-image=/backups/my.mbi_***_****  \  --log-bin=mysqlhost03-bin --relay-log=mysqlhost03-relay-bin \  --backup-dir=/tmp/restore_`date +%d%m_%H%M` copy-back-and-apply-log 6) Restore auto.cnf and mysqld-auto.cnf files backed up step 3.For automatic rejoining of the restore node mysqlhost03 the UUID and other system variable must be same as is was before restore. In order to achieve same we need to restore the auto.cnf and mysqld-auto.cnf file. 7) Start MySQL on mysqlhost03 and verify parameter gtid_execute.MySQL> systemctl start mysqld Check the value of the gtid_execute and gtid in the backup_gtid_executed.sql file . These two values must be same, In order to start replication from the correct gtid value. If these value are not same,then the change the gtid_execute value to same as that of the value in the backu_gtid_executed.sql. mysql> SET SQL_LOG_BIN=OFF; mysql> SOURCE backup_gtid_executed.sql mysql> SET SQL_LOG_BIN=ON; Continue Reading... 

[873872182-blog]

定时备份mysql数据库并上传到阿里云OSS

一 场景分析

这个应用场景是很多小企业或者个人开发者建站后，为保证数据安全，通常都会定时备份我们线上平台的重要数据，但是这个数据备份了后会越来越多，在有限的ECS存储空间中用这种备份文件来消耗是在是有些浪费，所以建议是直接将备份好的文件上传到OSS存储，OSS的单价相对ECS上硬盘的单价要便宜。

二 实���步骤

1 环境介绍

在聊实施步骤之前我们先来看看我的应用环境：

所在平台：Aliyun ECS 云服务器，CentOS 7.5 。只有40GB的硬盘，空间非常局限

所安装的MYSQL 5.7，是直接建在阿里云的ECS上

云端存储采用阿里云OSS，直接买40GB一年的存储包，9元钱。但是需要注意因为只买了存储包，在使用中还要注意会产生流量，具体收费已阿里云平台为主。

使用到的工具有 MYSQL client端、linux压缩工具gzip、阿里云OSS命令行工具Ossutil

2 操作步骤

2.1 本地备份mysql数据库

本地备份mysql数据库是非常简单的mysql命令，随便百度一大把教程，我就不多说了直接上代码：

mysqldump -u root -p123456 sys > /data/mysqlbackup/sys.sql

mysqldump是mysql备份命令； -u表示指定用户名，如:-uscott 表示指定用户名为scott，当然mysql没有scott用户，scott是Oracle的用户； -p表示指定密码，如-p123456，表示该用户的密码为123456； sys表示的是数据库名称，这里使用的是users数据库，如果要备份其他数据库请将sys换成对应名称即可； > /data/mysqlbackup/sys.sql 表示指定将备份文件存放在/data/mysqlbackup/文件夹内，命名为sys.sql 如图：

一般情况备份后产生的.sql文件比较大，占用空间多，所以建议进行压缩，方便传输，下��介绍试一下使用的压缩命令和解压缩命令：

gzip sys.sql # 压缩sys.sql 文件为.gz 格式 gunzip zxvf sys.sql.gz # 解压缩 sys.sql.gz

从上图中可以看出在sys.sql文件没有压缩之前有276K，在使用gzip压缩后生成的.gz压缩文件只有21K；

2.2 使用shell脚本实现mysql备份

shell 脚本是在linux 上强大的执行语言，我们要将刚刚在上面执行的备份语句、压缩语句 形成一个shell脚本，执行xxx.sh即可把备份、压缩自动执行完成； 下面贴出我自己写的执行脚本如下：

#!/bin/bash #备份保存路径 backup_dir=~/Downloads/mysqlbackup/ #日期 dd=`date +%Y-%m-%d-%H-%M-%S` #备份工具 tool=mysqldump # 主机 host=127.0.0.1 #用户名 username=root #密码 password=root #需要备份的数据库 database_name=sys #判断文件夹不存在则创建 if [ ! -d $backup_dir ]; then mkdir -p $backup_dir; fi #将备份文件压缩 mysqldump -uroot -proot sys | gzip > ~/Downloads/mysqlbackup/sys-$filename.sql $tool -h$host -u$username -p$password $database_name | gzip > $backup_dir/$database_name-$dd.sql.gz echo "文件备份 $backup_dir/$database_name-$dd.sql.gz 成功！！！" #将备份日志写入log文件 echo "create $backup_dir/$database_name-$dd.dump " >> $backup_dir/$database_name.log echo "$backup_dir/$database_name-$dd.sql.gz 已将该次操作日志记录到$database_name.log文件！！！"

将以上脚本内容创建到.sh文件中，如我创建在mysqldump.sh 文件中，由于权限问题，该脚本不具有执行权限，所以还需要先改权限：

sudo chmod 755 mysqldump.sh

如下图修改文件权限效果：

调整完该文件权限后开始执行该脚本：

./mysqldump.sh

执行完成后查看是否已生成对应的文件，然后解压后使用mysql的source命令导��数据库验证是否正常：

查看上传我执行的情况可以看出数据库sys已备份成功，并且解压后能倒入mysql数据库(这步就不演示了)，查看sys.log可以看出日志已记录成功！如图：

接下来我们介绍一下ossutil工具。

2.3 将本地文件上传到oss

ossutil工具的详细介绍直接去找马爸爸，我给你们电话，哦不给你们地址：ossutil详细介绍请点此 介绍一下安装ossutil，从官网文档中介绍了如何安装试用oss，这里我简单介绍一下：

第一步：下载ossutil 下载ossutil，使用：

curl -o ossutilmac64 http://gosspublic.alicdn.com/ossutil/1.6.6/ossutilmac64 # 不同平台对应不同的下载方式，请自行查看官网文档

我这是将文档下载到/tmp 文件夹中并修改文件名称为ossutil，如下图：

下载完成后需要修改文件为可以执行权限,并创建软连接到/usr/local/bin目录中，以方便在命令行能直接只用ossutil命令：

sudo chmod 755 ossutil

第二步：配置ossutil 配置ossutil，在官方文档中有详细介绍，如下操作：

./ossutil64 config 该命令将创建一个配置文件，在其中存储配置信息。 请输入配置文件路径（默认为：/home/user/.ossutilconfig，回车将使用默认路径。如果用户设置为其它路径，在使用命令时需要将--config-file选项设置为该路径）： 未输入配置文件路径，将使用默认配置文件：/home/user/.ossutilconfig。 对于下述配置，回车将跳过相关配置项的设置，配置项的具体含义，请使用"help config"命令查看。 请输入endpoint：http://oss-cn-shenzhen.aliyuncs.com 请输入accessKeyID：yourAccessKeyID 请输入accessKeySecret：yourAccessKeySecret 请输入stsToken：

endpoint：填写Bucket所在地域的域名信息，可参考访问域名和数据中心。

accessKeyID：查看方式请参考创建AccessKey。

accessKeySecret：查看方式请参考创建AccessKey。

stsToken：非必配项，若采用STS临时授权方式访问OSS需要配置该项，否则置空即可。stsToken生成方式参考临时访问凭证。

个人建议不要修改.ossutilconfig文件的存储路径！ 配置完成后尝试使用cp命令推送一张图片到oss中，关于cp命令请查看官方文档。

ossutil cp ~/Downloads/mysqlbackup/sys.log oss://hyooss/test/ # 将~/Downloads/mysqlbackup目录下的sys.log文件上传到oss的Bucket为 hyooss的test文件夹下

对应oss中的文件如图：

第三步：删除oss上的指定文件 说的再多都没用，因为现在的年轻人都没啥耐心！🔥

ossutil rm oss://hyooss/test/sys.log

rm命令的详细操作建议想了解更多的同学上官网去了解，这就不多介绍了！

以上三步就基本满足今天咱们要用的内容了，要了解更多建议上阿里云官网了解ossutil官方文档！

2.4 使用shell将已备份的mysql文件上传到oss，删除oss上的制定文件

结合2.2中介绍的使用shell脚本备份压缩mysql脚本，现在我们也创建一个备份上传到oss的脚本。 首先创建一个.sh文件用于将shell命令整理好； 其次将ossutil的cp操作命令���入.sh文件后将文件改为可执行文件； 第三执行该.sh文件，查看执行效果，检查oss上是否已有上传的文件； 第四在该.sh文件中加入删除云端oss上指定文件的命令，执行该.sh脚本，查看执行效果。 完事(so easy 🥰)！！！

三 经验总结与效果展示

建议大家手动的将第二大点中的内容整理后形成一个完成的.sh文件，实现一键上传的功能，我这不建议直接把源码贴出来，毕竟希望大家能自己学习总结，如果有同学需要看完成的.sh文件内容，请直接发邮件到我邮箱，我将会吧该.sh文件回复给各位需要的朋友👬！ 好吧，下面直接看看我这边执行完整脚本的情况：

加料区： 通常在我们写好sh脚本后都会在linux建立定时任务，定时执行该备份文件，如下图查看我的linux上已有的定时任务：

要增加定时任务直接使用crontab —e，在该界面加入要定时执行的命令即可~

[globalmediacampaign]

How to Restore Selected Table/Schema from full backup using MySQL Enterprise Backup(MEB)

  Before i talk about “Selective Restore” , it is more important to know below details:- What is MySQL Enterprise Backup ? Features of MySQL Enterprise Backup. Benchmarking “mysqldump” Vs “mysql enterprise backup” What is TLR (table level Recovery) Limitations of TLR Conclusion  What is MySQL Enterprise Backup ? It is a multi-platform, high-performance tool, offering rich features like “hot” (online) backup, incremental and differential backup, selective backup and restore, support for direct cloud storage backup, backup encryption and compression, and many other valuable features. This MEB is bundled with MySQL Commercial Editions. More info:- https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/intro.html Features of MySQL Enterprise Backup MySQL Enterprise Backup delivers: ·       "Hot" Online Backups - Backups take place entirely online, without interrupting MySQL transactions ·       High Performance - Save time with faster backup and recovery ·       Incremental Backup - Backup only data that has changed since the last backup ·       Partial Backup - Target particular tables or tablespaces ·       Full Instance Backup - Backs up data, as well as configuration and other information to easily create a complete "replica" ·       Advanced Optimistic Backup - Uses heuristics to optimize and reduce backups and shorten recovery time by assessing usage patterns ·       Fast Recovery - Get servers back online and create replicated servers ·       Point-in-Time Recovery (PITR) - Recover to a specific transaction ·       Online "Hot" Selective Restore - bring back only selected tables into a running database ·       Direct Cloud Storage Backup via S3 and Swift APIs - Backup and Restore directly to/from Oracle Storage Cloud, S3 and other Cloud Storage using AWS S3 API ·       Advanced LZ4 Compression - Support highly efficient, low impact and ultra fast LZ4 compression, as well as LZMA and zlib ·       AES 256 encryption - Built in 256-bit Advanced Encryption Standard (AES) encryption to secure all the sensitive backup data ·       NEW! Supports MySQL TDE - Enables secure archival quality backup and restore of TDE encrypted database files and keys ·       Streaming "Zero storage" Single Step Backup and Restore - Run a full or partial backup from one server and a restore to another in one streamed step without staged storage ·       Backup Validation - Provides assurance checks to confirm backup integrity and quality by confirming that internal pages are valid and file checksums match. ·       Exclude Tables - Exclude unnecessary tables from your Backups, saving backup time and space ·       Binlog and Relay log backup - Simplifies cloning source to replica servers for HA replication ·       Include Tables - Backup only required tables for better granularity and usability. ·       Continuous Monitoring - Monitor the progress and disk space usage ·       Selective Backup/Restore - An efficient and transportable method to backup InnoDB tables using Transportable Tablespaces ·       Table renaming on restore of Transportable Tablespace (TTS) backups ·       Compression - Cut costs by reducing storage requirements up to 90% ·       Backup to Tape - Stream backup to tape or other media management solutions ·       Partial Restore - Recover targeted tables or tablespaces ·       Restore to a Separate Location - Rapidly create clones for fast replication setup ·       Reduce Failures - Use a proven high quality solution from the developers of MySQL ·       Multi-platform - Backup and Restore on Linux, Windows, Mac & Solaris   Benchmarking “mysqldump” Vs “mysql enterprise backup”   References:- https://www.mysql.com/products/enterprise/backup.html  What is TLR (table level Recovery) Table-Level Recovery (TLR) allows selected tables (or schemas) to be restored from a backup (be it a full backup , a partial backup, or a backup created using transportable tablespaces (TTS) ). This features only available from MySQL 8.0.20 on wards. Earlier version to restore a particular table or else schema it is only supported for TTS (transportable table space)backups. Now , starting with MySQL 8.0.20 , users will get flexibility to take backups without TTS options and restore selective tables from full snapshot. As Example:- Suppose , I have taken Backup “DBbackup.img”  ,this backup image contains backup of 03 Schemas (Sales , World ,Sakila) , after some days I wanted to restore particular table from World database , let’s say “Country” Table. How to RESTORE “country table “ from full backup “DBbackup.img”  ? To Restore:- C:Program FilesMySQLMySQL Enterprise Backup 8.0>mysqlbackup --host=127.0.0.1 --protocol=tcp -uroot  -pChandan123  --include-tables="^world.country" --backup-dir="D:SampleBackupRestoreFolderfullbackuptmp441" --backup-image="D:SampleBackupRestoreFolderfullDBbackup.img" copy-back-and-apply-log     Sample Output MySQL Enterprise Backup  Ver 8.0.21-commercial for Win64 on x86_64 (MySQL Enterprise - Commercial) Copyright (c) 2003, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Starting with following command line ...  mysqlbackup --host=127.0.0.1 --protocol=tcp -uroot -pxxxxxxxxxx         --include-tables=^world.country         --backup-dir=D:SampleBackupRestoreFolderfullbackuptmp441         --backup-image=D:SampleBackupRestoreFolderfullDBbackup.img         copy-back-and-apply-log   200815 15:30:08 MAIN    INFO: Backup Image MEB version string: 8.0.21 [2020-06-16  18:15:12] 200815 15:30:08 MAIN    INFO: MySQL server version is '8.0.21' IMPORTANT: Please check that mysqlbackup run completes successfully.            At the end of a successful 'copy-back-and-apply-log' run mysqlbackup            prints "mysqlbackup completed OK!". 200815 15:30:09 MAIN    INFO: Importing table: `world`.`country`. 200815 15:30:09 MAIN    INFO: Analyzing table: `world`.`country`. 200815 15:30:09 MAIN    INFO: Importing table: `world`.`countrylanguage`. 200815 15:30:09 MAIN    INFO: Analyzing table: `world`.`countrylanguage`. 200815 15:30:09 MAIN    INFO: Apply-log operation completed successfully. 200815 15:30:09 MAIN    INFO: Backup has been restored successfully.   mysqlbackup completed OK!  general requirements for performing a TLR or partial restore: ·      The destination server must be running. ·      The destination server must be using the same page size that was used on the server on which the backup was made. ·      The innodb_file_per_table option must be enabled on the destination server. For non-TTS backups: ·      The tables being restored must already exist on the destination server, in the same table definition. For TTS backups: ·      The tables being restored must not already exist on the destination server. Limitations for a TLR or partial restore: ·      Individual partitions cannot be selectively restored. Tables selected by the --include-tables and --exclude-tables options are always restored in full. ·      Partial restores cannot be performed with incremental backups. ·      Binary, relay, and undo logs are not restored. ·      To Restore a particular table if it is associated with FKs then Restoration will failed , so to over this run mysql> set global foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql> set global foreign_key_checks=1; Query OK, 0 rows affected (0.00 sec)  More info about TLR:-https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/restore.partial.html Conclusion Backup are must!!! , MySQL Enterprise Backup helps us to solve difficult problems in day to day activities, provision new instance, compliance with regulatory requirements. Most important you will get immediate support from MySQL Support engineer. https://mysqlsolutionsarchitect.blogspot.com/2020/08/how-to-restore-selected-tableschema.html

[all-database-solutions]

Restore MySQL InnoDB Cluster from mysqlbackup(MySql Enterprise Backup)

[all-database-solutions]

One of the most common activity is to perform database restore using old backup or performing PIT recovery for database. In this blog , We will show you how to perform restore and recovery of the MySQL database using backup taken via mysqlbackup.

[sandeep2363]

Backup and Restore MySQL database using MySQLDUMP

Backup and Restore MySQL database using MySQLDUMP

Logical backup of database in MySQL Two types of backup:Physical backup tools include the mysqlbackup of MySQL Enterprise Backup for InnoDB or any other tables,or file system-level commands (such as cp, scp, tar, rsync) for MyISAM tables.Logical backup tools include the mysqldump program. Its generate output in forms of SQL scripts. Note:–For InnoDB tables, perform an online backup that takes…

[globalmediacampaign]

MySQL Backup Strategies – Building MySQL DR Solutions

MySQL Backup Strategies – What you should know before considering MySQL DR solutions ?  MySQL powers all the major internet properties, Which include Google, Facebook, Twitter, LinkedIn, Uber etc. So how do we plan for MySQL disaster recovery and what are the most common MySQL DR tools used today for building highly reliable database infrastructure operations ? There can be several reasons for a MySQL database outage: hardware failure, power outage, human error, natural disaster etc. We may not be able prevent all the disaster from happening but investing on a robust disaster recovery plan is very important for building fault-tolerant database infrastructure operations on MySQL.  Every MySQL DBA is accountable for developing a disaster recovery plan addressing data sensitivity, data loss tolerance and data security. Functionally you have several database backup strategies available with MySQL: Full backup – Full backup backs up the whole database, This also include transaction log so that the full database can be recovered after a full database backup is restored. Full database backups represent the database at the time the backup finished. Full backups are storage resource intensive and takes more time to finish. If you have for a large database, we strongly recommend to supplement a full database backup with a series of differential database backups. Differential backup – A differential backup is based on the most recent, previous full data backup. A differential backup captures only the data that has changed since that full backup. The full backup upon which a differential backup is based is known as the base of the differential. Full backups, except for copy-only backups, can serve as the base for a series of differential backups, including database backups, partial backups, and file backups. The base backup for a file differential backup can be contained within a full backup, a file backup, or a partial backup. The differential backups are most recommended when the subset of a database is modified more frequently than the rest of the database. Incremental backup – A incremental backup contains all changes to the data since the last backup. Both differential and incremental backup does only backing up changed files. But they differ significantly in how they do it, and how useful the result is.while an incremental backup only includes the data that has changed since the previous backup, a differential backup contains all of the data that has changed since the last full backup. The advantage that differential backup offers over incremental backups is a shorter restore time. Because, the backup has to be reconstituted from the last full backup and all the incremental backups since. MySQL Backup tools The following are the list of MySQL backup tools (logical and physical) discussed in this blog: mysqldump mysqldump is a MySQL client utility which can be used to perform logical backups  , The mysqldump generate output in SQL ( default and most commonly used to reproduce MySQL schema objects and data), CSV, other delimited text or XML format. We have copied below the restrictions of mysqldump: mysqldump does not dump performance_schema or sys schema be default. To enforce dumping or logical backup of any of these schema objects, You have to explicitly mention them –databases option or if you want to just dump performance_schema use –skip-lock-tables option. mysqldump does not dump the INFORMATION_SCHEMA schema. mysqldump does not dump the InnoDB CREATE TABLESPACE statements. mysqldump does not dump the NDB Cluster ndbinfo information database. mysqldump includes statements required to recreate the general_log and slow_query_log tables for dumps of the mysql database. But, Log table contents are not dumped Script to dump all the databases:  shell> mysqldump --all-databases > all_databases.sql Script to dump the entire database: shell> mysqldump db_name > db_name_dump.sql Script to dump several databases with one command: shell> mysqldump --databases db_name1 [db_name2 ...] > databases_dump.sql mysqlpump  The mysqlpump is another client utility for logical backup of MySQL database like mysqldump which is capable of parallel processing of databases and other schema objects with databases to perform high performance dumping process, We listed below mysqlpump most compelling features: MySQL dump with parallel processing capabilities for databases and other objects within databases. MySQL user accounts will be dumped as account-management statements (CREATE USER, GRANT) rather than as inserts into the mysql system database. By using mysqlpump you can create a compressed output. Much faster compared to mysqldump. Because, The InnoDB secondary indexes are created after rows are inserted to the table. P.S. – We have blogged about “How to use mysqlpump for faster MySQL logical backup ? ” here MySQL Enterprise Backup  MySQL Enterprise Backup is a hot / online backup tool for MySQL ( optimized for InnoDB only though capable of backup and restore of tables created on other storage engines supported by MySQL ) capable of performing full, incremental and differential backup. MySQL Enterprise Backup also support cloud storage backup, backup encryption and compression. We have explained most compelling MySQL Enterprise Backup 8.0 features below: Transparent page compression for InnoDB. Backup history available for all members of Group Replication by making sure backup_history table is updated on primary node after each mysqlbackup operation. Storage engine of the mysql.backup_history table on a backed-up server has switched from CSV to InnoDB. mysqlbackup now supports encrypted InnoDB undo logs . mysqlbackup now supports high performance incremental backup by setting page tracking functionality on MySQL (set –incremental=page-track). Much better MySQL Enterprise Backup 8.0 troubleshooting with now mysqlbackup prints a stack trace after being terminated by a signal. Selective restores of tables or schema from full backup for Table-Level Recovery (TLR) Percona XtraBackup  Percona XtraBackup is an open source MySQL hot backup solution from Percona addressing incremental, fast, compressed and secured backup for InnoDB optimally. Most of our customers users Percona XtraBackup for DR of their MySQL infrastructure, The following features makes Percona XtraBackup obvious choice for MySQL Backup and DR: Hot backup solution for InnoDB without blocking / locking transaction processing. Point-in-time recovery for InnoDB. MySQL incremental backup support. Percona XtraBackup supports incremental compressed backups. High performance streaming backup support for InnoDB. Parallel backup and copy-back support for faster backup and restoration. Secondary indexes defragmentation support for InnoDB. Percona XtraBackup support rsync to minimize locking. Track Percona XtraBackup history with Backup history table. Percona XtraBackup supports offline backup. Conclusion We always recommend a combination of multiple backup strategies / tools for maximum data reliability and optimal restoration. We cannot have a common backup strategy for all the customers, It depends on factors like infrastructure, MySQL distribution, database size, SLA etc. Backups are most import components in database infrastructure operations and we follow zero tolerance DR for building highly available and fault-tolerant MySQL infrastructure. Do you want to engage MinervaDB Remote DBA for MySQL Disaster Recovery (DR) and Database Reliability Engineering (Data SRE) ? Business Function Contact CONTACT GLOBAL SALES (24*7) (844) 588-7287 (USA) (415) 212-6625 (USA) (415) 650-4682 (USA) (778) 644-0767 (Canada) (778) 770-5251 (Canada) +44 7 480549332 (UK) TOLL FREE PHONE (24*7) (844) 588-7287 MINERVADB FAX +1 (209) 314-2364 MinervaDB Email - General / Sales / Consulting contact@minervadb.com MinervaDB Email - Support support@minervadb.com MinervaDB Email -Remote DBA remotedba@minervadb.com Shiv Iyer Email - Founder and Principal shiv@minervadb.com CORPORATE ADDRESS: CALIFORNIA MinervaDB Inc., 340 S LEMON AVE #9718 WALNUT 91789 CA, US CORPORATE ADDRESS: DELAWARE MinervaDB Inc., PO Box 2093 PHILADELPHIA PIKE #3339 CLAYMONT, DE 19703 The post MySQL Backup Strategies – Building MySQL DR Solutions appeared first on The WebScale Database Infrastructure Operations Experts. https://minervadb.com/index.php/2020/06/11/mysql-backup-strategies-building-mysql-solutions/