Shulou(Shulou.com)06/01 Report--

The main content of this article is "introduction to the way to migrate the database to aurora mysql". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the introduction of the way to migrate the database to aurora mysql.

Preface

There are several ways to migrate a database to aurora mysql:

1) migrate Rds mysql to aurora mysql

Migrate using rds mysql Snapshot

Migrate using rds mysql read-only copy

The above two migration methods are very simple, you only need to operate in the aws rds console, this film will no longer carry out specific drills, please refer to the documentation

Snapshot migration: https://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Migrating.RDSMySQL.Import.html

Read-only copy migration: https://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Migrating.RDSMySQL.Replica.html

2) external mysql database front yo aurora mysql

Use the mysqldump utility to create a dump of the data, and then import the data into the existing aurora mysql database cluster. Copying full backup files and incremental files from the database to the S3 bucket and then restoring the aurora mysql database cluster from these files may be faster than migrating data using mysqldump.

1 use the mysqldump utility to create a dump of the database, and then import the data into the existing aurora mysql database cluster

Because aurora mysql is compatible with mysql, this process is similar to the process of importing mysql data into rds mysql, as shown in the document https://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html.

Its overall structure is shown in the following figure:

1.1 install and configure the mysql database

I deployed the mysql database on the halo cloud bare metal server, the specific deployment process is slightly, you can Baidu.

1.2 create a replica of the mysql database 1.2.1 set replication options

Edit the file / etc/my.cnf sudo vi / etc/my.cnf

Update the [mysqld] field as follows:

[mysqld] log-bin=mysql-bin server-id=1

Restart the mysql service service mysqld restart

1.2.2 create a backup copy of an existing database

A new database schema_xuyi has been created in the figure above. Now back up the schema_xuyi and execute the following command:

Mysqldump\-databases schema_xuyi\-master-data=2\-single-transaction\-order-by-primary\-r backup.sql\-u local_user\-p

In the figure, you can see that the backup file backup_xuyi.sql is generated

1.3Create aurora mysql database

The specific creation process is omitted and should be consistent with the previous version of the mysql database as much as possible.

Connect to the aurora mysql database remotely, and its initial state is shown below:

1.4 use the mysql command to remotely connect to the aurora mysql database and import the previous sql file

Execute the command:

Mysql-h aurora-1-instance-1.cbgpcbkn8knw.us-east-1.rds.amazonaws.com-P 3306-u admin-p

The aurora-1-instance-1.cbgpcbkn8knw.us-east-1.rds.amazonaws.com part is the terminal node of aurora mysql database, and the connection is successful.

Execute the command source backup_xuyi.sql

The refresh operation of Workbench was not found. After reconnecting to the aurora mysql database, we can see that its status is as follows:

There is already a library of schema_xuyi, which indicates that the mysqldump has been imported successfully. The purpose of this test is to verify the process of importing from external mysql to aurora. This operation is completed.

2 copy the full backup files and incremental files from the database to the S3 bucket, and then restore the aurora mysql database cluster from these files

Reference document: https://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Migrating.ExtMySQL.html#AuroraMySQL.Migrating.ExtMySQL.S3

2.1 preparation 2.1.1 install percona on the local server

The local database version is mysql5.7, and the recommended percona version is Percona XtraBackup 2.4

Execute the following command:

Yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpmyum install-y percona-xtrabackup-24.x86_64

From the image above, you can see that Percona-xtrabackup is installed successfully.

2.1.2 granting aurora mysql access to the S3 bucket

Create a bucket in the same area as the aurora mysql database

The process is relatively simple and omitted.

Create an IAM policy to access S3 resources

The corresponding policy can be created through the IAM console, the specific process is omitted, and aurora can be granted all permissions to access S3.

Create IAM roles to allow aurora mysql access to AWS services

For more information on the process of creating roles, please refer to the document: https://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.Authorizing.IAM.CreateRole.html

As shown in the following figure, a role role_aurora_to_s3 is created and the policy from the previous step is attached to that role.

Associate a role with an aurora mysql database

For the specific operation process, please see the document https://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.Authorizing.IAM.AddRoleToDBCluster.html.

As shown in the figure above, roles have been associated with the aurora mysql database. In order for the role to take effect, we also need to modify the parameter group, we choose to create a new parameter group

Where the value of the parameter "aurora_load_from_s3_role" is updated to the ARN of the previously created character.

Then modify the database options of the database instance

Apply the changes and restart the database immediately.

2.2 preparation for backing up files 2.2.1 of the database to be restored to aurora mysql

In order to distinguish from the previous database content, we specially created a new library schema_test and created a new table table_test in it, as shown in the following figure:

2.2.2 create a backup using percona xtrabackup

Full backup

Xtrabackup-user=root-password=XY-zte110-backup-target-dir=/root/backupfiles

It can be seen that a backupfiles directory is generated under the current directory, and the contents under this category are shown in the figure above.

Upload the entire backup folder to the S3 bucket through aws CLI (the upload process is omitted), and log in to the S3 console to be visible

2.3 restore aurora mysql database from S3 bucket

Log in to the aurora console and go to the database page

Click "restore from S3" on the database page, engine option-> aurora version-> We chose mysql5.7.

Click "next"

Next, go to the database details page to set it, which is similar to the process of creating a new aurora instance.

Next, configure advanced settings

From the process of this configuration, it is exactly the same as creating a new aurora instance, so it can be concluded that aurora restore from S3 is actually a new aurora instance. Finally, click "create Database"

It is true that a new database instance is generated. Be patient.

Switching to the database page, you can see that there are two aurora instances

The instance aurora-instance-xuyi-copy in the figure above is the new aurora instance restored from S3 and has been created successfully. Now go to the instance remotely to check the database status.

It can be seen that the full copy is successful. So far, it is done by restoring the aurora database through S3.

At this point, I believe you have a deeper understanding of the "introduction of database migration to aurora mysql". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.