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

A valid Alibaba Cloud account. You can try Alibaba Cloud for free and enjoy $300 worth in Free Trial.
An Alibaba Cloud ECS instance running Ubuntu 16.04.
Root user password.
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

$ mysqldump [options] db_name [tbl_name ...] > backup_file_name.sql

Example

$ mysqldump -u james -p sample_db_1 table_1 > backup_file_name.sql

Backing up a Single MySQL Database

$ mysqldump [options] --databases db_name ... > backup_file_name.sql

Example

$ mysqldump -u james -p --databases sample_db_1> backup_file_name.sql

Backing up All MySQL databases

$ mysqldump [options] --all-databases  > backup_file_name.sql

Example

$ mysqldump -u james -p --all-databases > backup_file_name.sql

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:

$ ls -a

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:

$ mysql -u username -p database_name < backup_file_name.sql

Example

$ mysql -u james -p sample_db_1 < backup_file_name.sql

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:

$ mysqldump -u james -p --databases sample_db_1> $(date "+%Y_%m_%d_%H_%M_%S")_sample_db_1.sql

Run the command below to confirm the changes:

$ ls –a

Output

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:

$ nano /etc/mysql/my.cnf
$ nano /etc/mysql/mysql.conf.d/mysqld.cnf
$ nano /etc/my.cnf

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

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

$ mkdir /var/mysql_backups

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:

$ systemctl stop mysql

$ cp -R /var/lib/mysql/* /var/mysql_backups

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.

$ systemctl stop mysql
$ mv /var/lib/mysql /var/lib/mysql-old

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

$ mkdir /var/lib/mysql

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

$ cp -R /var/mysql_backups/* /var/lib/mysql

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

$ chown -R mysql:mysql /var/lib/mysql

Restart MySQL and your backup should be up and running

$ systemctl restart mysql

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:

$ apt-get install automysqlbackup

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

$ automysqlbackup

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

$ ls -a /var/lib/automysqlbackup/daily

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

$ nano /etc/default/automysqlbackup

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.

Community

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

Prerequisites

Step 1: Use mysqldump Command for Logical Backups

Step 2: Restoring MySQL Database

Appending date and time on the backup file name

Step 3: Physical MySQL Backups

Step 4: Restoring the Files

Step 5: Automating the Backup Process

Conclusion

Read next post:

francisndungu

You may also like

Comments

Raja_KT February 9, 2019 at 6:00 am

francisndungu

Related Products

PolarDB for MySQL

Database Backup

ECS(Elastic Compute Service)

Database Migration Solution