How to Back Up MySQL Database on Alibaba Cloud ECS Ubuntu 16.04

By Francis Ndungu, Alibaba Cloud Tech Share Author. Tech Share is Alibaba Cloud’s incentive program to encourage the sharing of technical knowledge and best practices within the cloud community.

MySQL is one of the most popular open source database management systems. It is widely used in websites and web applications to store data. Irrespective of its extensive use and acceptance by developers, MySQL requires backups when deployed in a production environment.

Backups are important for restoring data in case it is deleted or overwritten by mistake. When using MySQL server from Alibaba Cloud, you must consider data protection using backups and make it an essential part of your system or website even when the possibility of a database crash seems unlikely.

In this guide, we will discuss how you can back up your MySQL database from an Alibaba Cloud Elastic Compute Service (ECS) instance running Ubuntu 16.04 either manually or by using automated scripts.

Prerequisites

  1. A valid Alibaba Cloud account. You can try Alibaba Cloud for free and enjoy $300 worth in Free Trial.
  2. An Alibaba Cloud ECS instance running Ubuntu 16.04.
  3. Root user password.
  4. MySQL community server.

Step 1: Use mysqldump Command for Logical Backups

MySQL has a nice command line utility that creates logical backups called ‘mysqldump’. The tool reproduces your MySQL table structure and data without copying the actual data files and outputs the content to a file.

Logical backups are created in a format that contains essential SQL commands for recreating the database from scratch when a database needs to be restored. Thus, they give database administrators the flexibility of examining and editing table definitions before restoring data in a server if necessary.

Backups taken via the ‘mysqldump’ client may take a substantial amount of time to be restored but this option is great for small and medium-sized databases.

To use ‘mysqldump’ tool, follow the syntax codes below:

For the sake of clarity, we will use two databases; ‘sample_db_1’ and ‘sample_db_1’. We will also use ‘james’ as the MySQL user in all examples. Each sample database contains two tables (‘table_1’ and ‘table_2’).

Backing up a single MySQL table

Example

Backing up a Single MySQL Database

Example

Backing up All MySQL databases

Example

In the above examples, the output of the mysqldump command is directed to a file name ‘backup_file_name.sql’.

To check if the backup was created successfully, use the command below to list the files:

Image for post
Image for post

We have used the ‘-p’ option to allow MySQL to prompt us for a password. This ensures that our password is not logged on the server for security purposes.

Step 2: Restoring MySQL Database

You can restore any MySQL database created using mysqldump command using the syntax below:

Example

Remember to enter your password when prompted to do so.

Appending date and time on the backup file name

In the above examples, we just used an arbitrary file name to store our MySQL backups. A more professional and convenient way is to append the date on the file name.

This will help you to easily manage your backups in a chronological order and restore the most recent backup in case the MySQL server crashes:

Also, it is advisable to include the name of the database on the filename. For instance, to backup our ‘sample_db_1’ database we will use the syntax below:

Run the command below to confirm the changes:

Output

Image for post
Image for post

As you can see in the output above, our backup file name contains the year, month, day of the month and time.

Step 3: Physical MySQL Backups

Logical MySQL backups using mysqldump only works when a database server is up and running. Also, mysqldump utility can be slow for large databases.

So in case you are managing a bulky database or you can’t access a MySQL server, taking physical backups is the ideal choice.

To perform a physical MySQL backup, you will simply copy all files under the data directory usually on the path /var/lib/mysql/.

Note: You will need to be logged in as a root user to perform the steps below.

For accuracy purposes, use a nano editor to examine the content of MySQL configuration files:

You should check the value of the datadir directive as shown below:

Image for post
Image for post

Next, create a backup directory that will hold the database files using the command below:

Then, use the cp commands to recursively copy the database files to our target backup directory. Before you do this, you need to stop MySQL server if it is already running:

Step 4: Restoring the Files

When restoring your data files back to MySQL, it is a good idea to rename the current MySQL directory with a different name. Before you do this, make sure MySQL service is stopped.

Then you need to recreate the directory /var/lib/mysql one more time using the command below.

Then, copy all backup files to the folder /var/lib/mysql

Assign the right ownership of the directory by typing the command below:

Restart MySQL and your backup should be up and running

Step 5: Automating the Backup Process

Manual MySQL backups are very convenient when you want to take a quick snapshot of your databases. However, this can be a tedious process on a production environment.

Luckily, you can use a utility called automysqlbackup to handle MySQL backups automatically. The tool organizes all backups under the directory /var/lib/automysqlbackup for faster data restoration.

To install automysqlbackup, run the command below as the root user:

To manually take a backup, run the command below and make sure MySQL server is started:

To confirm if the backup was taken successfully, run the command below:

Image for post
Image for post

You can edit the automysqlbackup utility configuration file by running the command below:

However, the utility will create database backups every day, week and month for all databases in your server. You should rest assured that the tool will provide a better way of managing your backups without doing it manually.

Conclusion

In this guide, we have showed you how to create logical and physical backups from your MySQL server hosted on Alibaba Cloud Elastic Compute Service (ECS). We have also outlined the basic steps for running an automated backup using automysqlbackup utility. Running backups on your MySQL server hosted on Alibaba Cloud is the first step towards securing your data in case a disaster strikes your server.

Reference:

https://www.alibabacloud.com/blog/how-to-back-up-mysql-database-on-alibaba-cloud-ecs-ubuntu-16-04_593870?spm=a2c41.11854206.0.0

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store