How to Backup and Restore MySQL Database on Linux
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:
- Use SSMS to monitor the percentage of backup and restore progress.
- Use the T-SQL stats keyword to show the percentage.
- 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:
- Restore data to a clone instance.
- 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.
Read previous post:
Read next post:
-
-
Alibaba Clouder
2,593 posts | 797 followers
Follow
Comments
-
-
Alibaba Clouder
2,593 posts | 797 followers
Follow
Related Products
-
Cloud Backup
Cloud Backup is an easy-to-use and cost-effective online data management service.
Learn More
More Posts by Alibaba Clouder
- MyBatis with a More Fluent Experience
- Alibaba Cloud Sustainability Report 2021
- Comparing CNI Models in Container Service for Kubernetes — Alibaba Cloud Series Part 1
- [Infographic] 5 Steps to Accelerate Your Digitalization in Asia
- Attackers Use the Vulnerability of ShowDoc to Spread Botnets
- Powerful: MyBatis and Three Streaming Query Methods
- What is the Difference between Spring Boot and Spring?
- What are the Differences and Functions of the Redo Log, Undo Log, and Binlog in MySQL?
- On the In-Depth Cluster Scheduling and Management
- Alibaba Technological Practices: Experiences in Cloud Resource Scheduling