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

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.

$ mysqldump [options] db_name [tbl_name ...] > backup_file_name.sql
$ mysqldump -u james -p sample_db_1 table_1 > backup_file_name.sql
$ mysqldump [options] --databases db_name ... > backup_file_name.sql
$ mysqldump -u james -p --databases sample_db_1> backup_file_name.sql
$ mysqldump [options] --all-databases  > backup_file_name.sql
$ mysqldump -u james -p --all-databases > backup_file_name.sql
$ ls -a

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
$ mysql -u james -p sample_db_1 < backup_file_name.sql

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.

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

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.

$ nano /etc/mysql/my.cnf
$ nano /etc/mysql/mysql.conf.d/mysqld.cnf
$ nano /etc/my.cnf
$ mkdir /var/mysql_backups
$ 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
$ mkdir /var/lib/mysql
$ cp -R /var/mysql_backups/* /var/lib/mysql
$ chown -R mysql:mysql /var/lib/mysql
$ 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.

$ apt-get install automysqlbackup
$ automysqlbackup
$ ls -a /var/lib/automysqlbackup/daily
$ nano /etc/default/automysqlbackup

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.

--

--

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
Alibaba Cloud

Alibaba Cloud

4.97K Followers

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com