How to backup Mysql Databases on Amazon RDS
Published: 2022-12-12 18:57:18


<!-- wp:heading {"level":1} -->
<h1>How to backup Mysql Databases on Amazon RDS</h1>
<!-- /wp:heading -->

<p><span data-preserver-spaces="true">Cloud-based services let you scale capacity as your workload grows and automate the task of operating and maintaining your infrastructure at a lower cost.</span></p>
<p><span data-preserver-spaces="true">There are several best approaches to backup Mysql databases on Amazon RDS . Backing up data can be challenging, and the number of solutions on the market varies greatly. It's mandatory to find one that works well with the specific needs of your business. To learn more about backing up MySQL databases on AWS, including the benefits, limitations, and common ways to <a href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.html">back up MySQL databases,</a> visit the </span><a class="editor-rtfLink" href="https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-db-backup/#:~:text=To%20back%20up%20your%20Amazon,computer%20or%20Amazon%20EC2%20instance." target="_blank" rel="noopener"><span data-preserver-spaces="true">Amazon RDS guide . </span></a></p>
<h2><span data-preserver-spaces="true">Best approaches to Backup Mysql Databases on Amazon RDS  </span></h2>
<p><strong><span data-preserver-spaces="true">Physical Backup </span></strong></p>
<p><span data-preserver-spaces="true">Database snapshots consist of copies of the entire contents of the databases of MySQL databases. The purpose of this type of Backup is often to prevent data loss in the event of a catastrophic server failure. You can take physical database snapshots of a self-managed MySQL database running on-premises or on Amazon EC2 instances using the tool Percona XtraBackup. You cannot use Percona XtraBackup to create database snapshots for RDS for MySQL instances.</span></p>
<p><strong><span data-preserver-spaces="true">How to do physical Backup</span></strong></p>
<p><span data-preserver-spaces="true">Amazon S3 is an excellent alternative to storing backup files, and Amazon RDS is a fantastic way to protect your on-premises data by backing it up to the cloud. If you're not familiar with these services, here are some things to consider when setting up the environment you need to back up your files:</span></p>
<ul>
<li><span data-preserver-spaces="true">An amazon S3 bucket to store your backup files.</span></li>
<li><span data-preserver-spaces="true">Percona XtraBackup's Backup of your on-premises database.</span></li>
<li><span data-preserver-spaces="true">An IAM role that grants Amazon RDS permission to access the bucket.</span></li>
</ul>
<p><span data-preserver-spaces="true">The command-line tool Percona XtraBackup can create a complete backup of your MySQL database files, which can be restored from Amazon S3.</span></p>
<p><span data-preserver-spaces="true">This example creates an entire MySQL database backup and stores it in the /on-premises/s3-restore/backup folder.</span></p>
<p> </p>

<!-- wp:code -->
<pre class="wp-block-code"><code>xtrabackup --backup --user=&lt;myuser&gt; --password=&lt;password&gt; --target-dir=&lt;/on-premises/s3-restore/backup&gt;</code></pre>
<!-- /wp:code -->

<p></p>
<p><span data-preserver-spaces="true">There are different formats in which you can compress your Backup. You can compress your Backup in any of these:</span></p>
<ul>
<li><span data-preserver-spaces="true">Gzip (.gz)</span></li>
</ul>
<p><span data-preserver-spaces="true"></span></p>

<!-- wp:code -->
<pre class="wp-block-code"><code>xtrabackup --backup --user=&lt;myuser&gt; --password=&lt;password&gt; --stream=tar \

&nbsp; &nbsp;--target-dir=&lt;/on-premises/s3-restore/backup&gt; | gzip - | split -d --bytes=500MB \

&nbsp; &nbsp;- &lt;/on-premises/s3-restore/backup/backup&gt;.tar.gz</code></pre>
<!-- /wp:code -->

<p></p>
<ul>
<li><span data-preserver-spaces="true">Tar (.tar)</span></li>
</ul>

<!-- wp:code -->
<pre class="wp-block-code"><code>xtrabackup --backup --user=&lt;myuser&gt; --password=&lt;password&gt; --stream=tar \

&nbsp; &nbsp;--target-dir=&lt;/on-premises/s3-restore/backup&gt; | split -d --bytes=500MB \

&nbsp; &nbsp;- &lt;/on-premises/s3-restore/backup/backup&gt;.tar</code></pre>
<!-- /wp:code -->

<p><!-- /wp:code --></p>
<p></p>
<ul>
<li><span data-preserver-spaces="true">Percona XBStream (.xbstream)</span></li>
</ul>
<pre class="wp-block-code"><code>xtrabackup --backup --user=&lt;myuser&gt; --password=&lt;password&gt; --stream=xbstream \

&nbsp; &nbsp;--target-dir=&lt;/on-premises/s3-restore/backup&gt; | split -d --bytes=500MB \

&nbsp; &nbsp;- &lt;/on-premises/s3-restore/backup/backup&gt;.xbstream</code></pre>
<p></p>
<p><span data-preserver-spaces="true">For further details, you can visit this&nbsp;</span><a class="editor-rtfLink" href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.html" target="_blank" rel="noopener"><span data-preserver-spaces="true">User Guide</span></a><span data-preserver-spaces="true">&nbsp;by Amazon.</span></p>
<p><strong><span data-preserver-spaces="true">Logical Backup</span></strong></p>
<p><span data-preserver-spaces="true">A logical backup saves the content of a database, the structure of the database, or both in a logical form. It makes sense that people use this kind of Backup when creating a new machine, installing a new software package, and restoring data onto a new platform. For example, if your site gets a spike in traffic, you can take a logical backup to cloud-based storage to restore it to a machine with more space. Using tools like mysqldump or MyDumper, you can create a logical backup of your MySQL database.</span></p>
<p><strong><span data-preserver-spaces="true">Method 1 :</span></strong></p>
<p><strong><span data-preserver-spaces="true">Using the Flat Files</span></strong></p>
<p><span data-preserver-spaces="true">When performing an in-database import of data, it is much more efficient to use tables instead of files. There are multiple methods for importing data from flat files to a database. One of the easiest methods is to use the LOAD DATA INFILE statement, which can be very quick. If you create your flat files at the database level, they are created on a per-table basis, so parallel loading the data to the database is also possible. However, you cannot load flat files without an RDS for the MySQL database. So you can resolve the issue with the following steps :</span></p>
<p><strong><span data-preserver-spaces="true">1) Use Aws Migration services&nbsp;</span></strong></p>
<p><span data-preserver-spaces="true">While you can use AWS Database Migration Service to move your data to Amazon S3, you can also migrate data to the service directly from RDS for MySQL databases. As the first step, you need to connect to your RDS instance using the AWS console. Next, select the database and click on the "RDS Instances" tab. From there, you can access the "Copy DB" link. Once you do, you'll be directed to the "Choose a source database" screen. From here, choose the source instance. Then, select "Copy DB to S3" in the drop-down menu and click on "Create a copy of myDB." This will initiate the copy process to Amazon S3. For more information on Amazon S3 and how to configure it, visit</span><a class="editor-rtfLink" href="https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.S3.html" target="_blank" rel="noopener"><span data-preserver-spaces="true">&nbsp;this website</span></a><span data-preserver-spaces="true">.</span></p>
<p><span data-preserver-spaces="true">2) MySQL's Command Line Interface (CLI)&nbsp;</span></p>
<p><span data-preserver-spaces="true">It is used to work with databases on the command line. If you've installed the CLI and it's available, you can use the command prompt window to create flat files. Copy and paste this code there&nbsp;</span></p>
<pre class="wp-block-code"><code>mysql -h &lt;endpoint&gt; -u &lt;username&gt; -p --silent --batch --quick --raw -e "select * from &lt;database_name&gt;.&lt;table_name&gt; limit 10" &gt; &lt;filename&gt;.csv</code></pre>
<p></p>
<p><strong><span data-preserver-spaces="true">Method 2 :</span></strong></p>
<p><strong><span data-preserver-spaces="true">Create multiple files</span></strong></p>
<p><span data-preserver-spaces="true">Back up your data using separate files split into multiple files (one file per table). You can create an archive of individual database files by splitting the Backup into multiple files. This lets you import them in parallel, and it also helps to reduce the time of data import to your databases and provides faster rollback. When the tables are large, you can split the table data into separate files larger than 500 MB.</span></p>
<p><span data-preserver-spaces="true">Here's one way to create multiple dump files for each table within a database.</span></p>
<p><!-- wp:code --></p>
<pre class="wp-block-code"><code>mysqldump -h &lt;endpoint&gt; -u &lt;username&gt; -p &lt;database_name&gt; &lt;tablename&gt; &gt; &lt;dumpfilename.sql&gt;</code></pre>
<p><!-- /wp:code --></p>
<p><span data-preserver-spaces="true">To import the files, copy and paste the following command&nbsp;</span></p>
<p><span data-preserver-spaces="true"><!-- wp:code --></span></p>
<pre class="wp-block-code"><code>MySQL -h &lt;endpoint&gt; -u &lt;username&gt; -p &lt;database_name&gt; &lt; &lt;dumpfilename.sql&gt;</code></pre>
<p><span data-preserver-spaces="true"><!-- /wp:code --></span></p>
<p><strong><span data-preserver-spaces="true">Using primary key order</span></strong></p>
<p><span data-preserver-spaces="true">Load data in primary key order is the fastest way to insert rows, and it's essential for tables that don't fit entirely within the buffer pool. You'll need to back up in primary key order to get the fastest approach. Use the following command&nbsp;</span></p>
<p><span data-preserver-spaces="true"><!-- wp:code --></span></p>
<pre class="wp-block-code"><code>mysqldump -h &lt;endpoint&gt; -u &lt;username&gt; -p --order-by-primary &lt;database_name&gt; &lt;tablename&gt; &gt; &lt;dumpfilename.sql&gt;</code><span data-preserver-spaces="true"><!-- /wp:code --></span></pre>
<p><strong><span data-preserver-spaces="true">Import data without a secondary and full-text index</span></strong><span data-preserver-spaces="true">&nbsp;</span></p>
<p><span data-preserver-spaces="true">For enormous datasets, it's much faster to load your data into a table that doesn't have a secondary or full-text index. It will help if you also considered creating the index after you've loaded the data into the table. Once you have the index, you can delete the table with the unneeded index. In the case of a full-text index, as soon as the document is inserted, it's tokenized, so the individual words and associated data are inserted into the full-text index.</span></p>
<p><strong><span data-preserver-spaces="true">How to enable Automated Backup&nbsp;</span></strong></p>
<p><span data-preserver-spaces="true">It is pretty simple to enable automated backups in Amazon RDS. You need to perform these steps:</span></p>
<ol>
<li><span data-preserver-spaces="true">Open the Amazon RDS console.</span></li>
<li><span data-preserver-spaces="true">From the navigation pane, choose Databases.</span></li>
<li><span data-preserver-spaces="true">Choose the DataBase instance you want to modify.</span></li>
<li><span data-preserver-spaces="true">Select the Modify button.</span></li>
</ol>
<p><span data-preserver-spaces="true">The Modify DB instance page will appear. Now, you can choose the backup retention period. This allows you to back up the data at any time you want. After choosing the period,&nbsp;</span><strong><span data-preserver-spaces="true">click Continue</span></strong><span data-preserver-spaces="true">. Finally, you can choose the Modify DB instance option to save your changes and enable the automated backups.</span></p>
<p><strong><span data-preserver-spaces="true">Why is a logical Backup preferred instead of a physical one</span></strong></p>
<p><span data-preserver-spaces="true">If you implement a physical backup, you can create a backup of your database using Percona XtraBackup. You can restore that Backup to the AWS instance using the Restore from Amazon S3 option. However, with Percona, you won't be able to restore data to an existing RDS for the MySQL instance.</span></p>
<p></p>