Set up MySQL, how to back up, recover, and migrate-Support(Support)-阿里云帮助中心

Overview

This topic describes the methods for backing up, recovering, and migrating a MySQL database that runs on an Alibaba Cloud Elastic Compute Service (ECS) instance.

Details

Important

Note:

If you perform risky operations, such as modifying instances or data, ensure that your instances have disaster recovery and fault tolerance capabilities to protect your data.
Before you modify the configurations or data of instances, such as ECS and ApsaraDB RDS (RDS) instances, you can create a snapshot or enable features such as RDS log backup.
If you have submitted security information, such as logon credentials, on the Alibaba Cloud platform, change your password promptly.

Refer to the following solutions as needed.

Backup and recovery

mysqldump

For more information, see the official MySQL documentation.

For more information, see Backup and recovery using mysqldump and binary logs.

xtrabackup

For more information, see Hot backup for MySQL using xtrabackup.

Migration

mysqldump

Use mysqldump to export the data, stored procedures, triggers, and functions from your self-managed database. Do not update data while the export is in progress.

To export data from your self-managed database, run the following command on the Linux command line:

mysqldump -h <server_IP_address> -u <username> -p --opt --default-character-set=utf8 --hex-blob <self_managed_database_name> --skip-triggers --skip-lock-tables > /tmp/<self_managed_database_name>.sql

To export stored procedures, triggers, and functions, run the following command on the Linux command line:

mysqldump -h <server_IP_address> -u <username> -p --opt --default-character-set=utf8 --hex-blob <self_managed_database_name> -R | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > /tmp/<self_managed_database_name>Trigger.sql

Upload the two exported files to the ECS instance. In this example, the path is /tmp.
Import the exported files into the destination instance. Run the following commands:

mysql -h <instance_endpoint> -P <instance_port> -u <instance_username> -p <database_name> < /tmp/<self_managed_database_name>.sql
mysql -h <instance_endpoint> -P <instance_port> -u <instance_username> -p <database_name> < /tmp/<self_managed_database_name>Trigger.sql

Copy physical files

Display the MySQL path.
```
show global variables like "%datadir%";
```

Compress the file.

tar -zcvf data.tar.gz sell.sql # The specific .sql file depends on your environment.

Download the file using scp.

scp <username>@<server_IP_address>:<file_path>data.tar.gz

Decompress the file.
```
tar -zxvf data.tar.gz
```
Use commands such as cp or mv to copy the data file to the MySQL data directory on the destination server.

Modify permissions.

chown -R mysql:mysql . # You must switch to the corresponding MySQL folder.

Start or restart the database service.
```
systemctl restart mysqld 
```

DTS migration

For more information, see Migrate a self-managed MySQL database connected through a leased line, VPN Gateway, or Smart Access Gateway to an RDS instance. You can replace the destination RDS instance with a MySQL instance on an ECS instance.