Community Blog How to Backup and Restore MySQL Database on Linux

How to Backup and Restore MySQL Database on Linux

In this article, you will learn how to backup and restore RDS for MySQL Database on linux with some information on monitoring the backup and restore process and how to reduce the backup cost.

Backup the RDS for MySQL Database

Step 1: Install MySQL Client on ECS (Linux)

1) Download MySQL Client
Run: wget http://dev.mysql.com/get/archives/mysql-5.6/MySQL-client-5.6.15-1.linux_glibc2.5.x86_64.rpm

2) Install MySQL Client

Run: rpm -ivh MySQL-client-5.6.15-1.linux_glibc2.5.x86_64.rpm
Or you can use yum install mysql to install MySQL directly in the system.

Step 2: Back up the database of ApsaraDB for RDS instance

Run the command to complete database backup:

mysqldump -h xxx.mysql.aliyun.com -u xxx -P 3306 -p --opt --default-character-set=utf8 --triggers --hex-blob db_name > /tmp/db_name.sql

After the password is entered, Mysqldump will carry out the backup and complete it automatically.
Check the SQL file generated at the backup, and run ll xx.sql.

You can also check how to roll back MySQL databases from the files you backup here.

Now Alibaba Cloud ApsaraDB for RDS MySQL configure automatic backup or manual backup in the console. For details, you can go to Back up RDS data - ApsaraDB for RDS.

Restore RDS for MySQL backup files

Step 1: Extract Backup Files

Download the extract tool first rds_backup_extract.sh and extract the backup files with the following commands:

bash rds_backup_extract.sh -f /home/mysql/backup/hins575175_xtra_20150429091224.tar.gz -C /home/mysql/data

Then you can check the files generated.

Step 2: Restore Data Files

Install Percona-XtraBackup before extracting the backup file. Then run the following command to restore the data:

innobackupex —defaults-file=/home/mysql/data/backup-my.cnf —apply-log /home/mysql/data

If it displays “completed” or “OK”, it indicates execution success.

Step 3: Modify Configuration Files

Due to version issues, comment out the innodb_fast_checksum, innodb_page_size and innodb_log_block_size in backup-my.cnf in the extracted files.

Step 4: Modify File Owner

Run chown -R mysql:mysql /home/mysql/data and check whether the file owner is mysql user.

Step 5: Start mysqld process and log in for verification

Start mysqld process and verify the startup success

mysqld_safe —defaults-file=/home/mysql/data/backup-my.cnf —user=mysql —datadir=/home/mysql/data &

Then you can log in on the client as mysql to verify the success.

Now in RDS for MySQL 5.6 High-Availability Edition, you can restore only certain databases or tables rather than the entire instance in the console. For details, you can go to Restore MySQL databases or tables.

Related Blog Posts

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

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.

Step 1: Use mysqldump Command for Logical Backups
Step 2: Restoring MySQL Database
Step 3: Physical MySQL Backups
Step 4: Restoring the Files
Step 5: Automating the Backup Process

SQL Server Best Practices: Monitoring Backup and Restore Progress

SQL Server Backup & Restore are heavy I/O read and write operations, especially when the database or database backup file is relatively large. It is therefore very important to monitor the backup and restore process. As backup and restore progresses, we can estimate the expected time of completion and any potential impact on the system. This article shows how to monitor SQL Server Backup & Restore progress.

Monitoring Backup & Restore Progress

In a SQL Server database, three main methods are available for monitoring database backup and restore progress:

  1. Use SSMS to monitor the percentage of backup and restore progress.
  2. Use the T-SQL stats keyword to show the percentage.
  3. Use Dynamic View to monitor the percentage of backup and restore completion.

Related Documentation

Restore MySQL data

You can restore data of RDS for MySQL in either of the following ways:

  1. Restore data to a clone instance.
  2. Restore certain databases or tables rather than the entire instance.

This article describes how to restore data of the entire instance to a new instance (referred to as a clone instance), verify the data on the clone instance, and transfer the data you need from the clone instance to the original instance.

How to select DBS instances

Backup data is the actual data that has been transmitted over the DBS server. Backup data is different from the file data stored on disks in your database server. The size of backup data is less than file data, and is dependent on factors such as database type, backup method, backup granularity, and the amount of free disk space.

Related Products

ApsaraDB RDS for MySQL

MySQL is one of the most popular open-source databases in the world. As a key component of the open-source software bundle LAMP (Linux, Apache, MySQL, and Perl/PHP/Python), MySQL has been widely applied to different scenarios.

ApsaraDB RDS for MySQL is an on-demand database hosting service for MySQL with automated monitoring, backup and disaster recovery capabilities.

Database Backup

Database Backup (DBS) is a secure and cost-efficient solution for continuous data protection. The service offers strong protection for data stored in multiple environments, such as enterprise data centers, hybrid clouds, public clouds, and third-party cloud vendors.

Database Celebration Offers

Alibaba Cloud Database Service has been recognized as A Strong Performer by Forrester. To celebrate the achievement and progress made by Alibaba Cloud database, we are now giving out attractive offers exclusively for our customers.

0 0 0
Share on

Alibaba Clouder

2,600 posts | 750 followers

You may also like