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

I don't know if you know anything about similar articles on how to migrate RDS for MySQL data to local MySQL. Today, I'm here to tell you a little bit about it. If you are interested, let's take a look at the body. I believe you will gain something after seeing how to migrate RDS for MySQL data to local MySQL.

First of all, you need to download the same version of the database installation package or binary code package as the online Aliyun database (compiled and can be used directly)

Links are as follows:

Http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz

Unzip:

Root@ubuntu:/data#tar-xzvf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz

Root@ubuntu:/data# mv mysql-5.6.36-linux-glibc2.5-x86_64 mysql

Root@ubuntu:/data#cd mysql

Root@ubuntu:/data/mysql#

The root@ubuntu:/data/mysql# ls / / unzipped file is shown below. You can use it directly without compiling and installing the source code.

Bin COPYING data docs include lib man my.cnf my-new.cnf mysql-test README scripts share sql-bench support-files

The next step is to extract the backup files of the online database:

Root@ubuntu:/data/mysql#cd.. /

Root@ubuntu:/data# tar-xzvf h7351_data_20170804032.tar.gz-C / data/mariadb/

After the decompression is complete, restore the data file:

You need to install the innobackupex command first, so install this package file:

Root@ubuntu:/data#apt-get install percona-xtrabackup

Root@ubuntu:/data#innobackupex-- defaults-file=./backup-my.cnf-- apply-log-- ibbackup xtrabackup_56. / you must add the following-- ibbackup xtrabackup_56. Here I am 56, because my database version is version 5.6.

Finally, the system displays innobackupex: completed OKPC, and the data is recovered successfully.

After the data recovery is successful, modify the owner and group of the files (including the mariadb directory) in the mariadb directory of the recovery data:

Create a new mysql user:

Root@ubuntu:/data#useradd mysql

Root@ubuntu:/data# chown-R mysql.mysql mariadb/ modifies the owner and group of the decompressed data file, and changes all files in the mariadb directory to mysql by cascading

The configuration file needs to be modified before initialization

The directory where the data files of the root@ubuntu:~#cd / data/mariadb / / database are extracted

Root@ubuntu:/data/mysql/scripts# cd / data/mariadb/

Root@ubuntu:/data/mariadb# vi backup-my.cnf / / my configuration file is as follows:

Comment out the innodb_fast_checksum, innodb_page_size and innodb_log_block_size in the decompressed file backup-my.cnf and add datadir=/data/mariadb

# This MySQL options file was generated by innobackupex.

# The MySQL server

[mysqld]

Innodb_checksum_algorithm=innodb

# innodb_log_checksum_algorithm=innodb

Innodb_data_file_path=ibdata1:200M:autoextend

Innodb_log_files_in_group=2

Innodb_log_file_size=1572864000

# innodb_fast_checksum=false

# innodb_page_size=16384

# innodb_log_block_size=512

Innodb_undo_directory=.

Innodb_undo_tablespaces=0

Datadir=/data/mariadb

# rds_encrypt_data=false

# innodb_encrypt_algorithm=aes_128_ecb

Initialization operation:

Enter the location of the newly extracted mysql installation / data/mysql

Root@ubuntu:~# cd / data/mysql/

Root@ubuntu:/data/mysql# ls

Bin COPYING data docs include lib man my.cnf my-new.cnf mysql-test README scripts share sql-bench support-files

Root@ubuntu:/data/mysql# cd scripts/

Root@ubuntu:/data/mysql/scripts#

Root@ubuntu:/data/mysql/scripts#. / mysql_install_db-- user=mysql-- datadir=/data/mariadb-- basedir=/data/mysql-- defaults-file=/data/mariadb/backup-my.cnf / / you need to specify the configuration file here, otherwise it will look in the / etc/ directory during initialization.

The presence of OK indicates successful initialization.

The next step is to create a shortcut:

Root@ubuntu:/data/mariadb# cd / data/mysql/support-files/ enter this directory where mysql is installed

Root@ubuntu:/data/mysql/support-files# ls

Binary-configure magic my-default.cnf mysqld_multi.server mysql-log-rotate mysql.server

Root@ubuntu:/data/mysql/support-files# cp mysql.server / etc/init.d/mysqld / / copy the startup file to the / etc/init.d/ directory

Make some modifications to root@ubuntu:/data/mysql/support-files#vi / etc/init.d/mysqld / /. The specific modification locations are as follows:

Basedir=/data/mysql

Datadir=/data/mariadb

When you save and exit after modification, you need to pay attention to another place, that is, the configuration file. If you do not specify a configuration file when starting, it will call the / etc/my.cnf file, so you need to specify that there are two methods. One is to modify it directly in / etc/init.d/mysqld, such as:

# conf=/etc/my.cnf

Conf=/data/mariadb/backup-my.cnf

Another method is to directly copy the backup-my.cnf file in the mariadb directory to / etc/my.cnf, as shown in

Root@ubuntu:/data/mariadb# cp backup-my.cnf / etc/my.cnf / / so that you don't have to modify the conf in the / etc/init.d/mysqld file

Start mysql:

Root@ubuntu:/data/mariadb#/etc/init.d/mysqld start / / No error is reported, which means it is normal

Root@ubuntu:/data/mariadb#ps aux | grep mysql / / check whether there is a mysql process

Root 5706 0.0 4464 1624 pts/0 S 09:53 0:00 / bin/sh / data/mysql/bin/mysqld_safe-- datadir=/data/mariadb-- pid-file=/data/mariadb/ubuntu.pid

Mysql 5900 0.3 7.2 1572664 586972 pts/0 Sl 09:53 0:08 / data/mysql/bin/mysqld-basedir=/data/mysql-datadir=/data/mariadb-plugin-dir=/data/mysql/lib/plugin-user=mysql-log-error=/data/mariadb/ubuntu.err-pid-file=/data/mariadb/ubuntu.pid

Root 6009 0.0 13464 2124 pts/1 S + 10:29 0:00 grep-color=auto mysql

Root@ubuntu:/data/mariadb#

Attach the actions on the Aliyun document:

Operation steps

This example takes the local CVM as the RHEL6/x64 system and the backup file storage path / home/mysql/ as an example.

Download the physical backup file of the cloud database and upload it to the target cloud server. For backup file acquisition methods, see download backup data. If the destination CVM can access the source instance, you can also use wegt "url" to download backup files. Where url is the download address of backup files.

Switch the path to the path where the backup file is located.

Cd / home/mysql/

Extract the backup file.

Tar vizxf filename.tar.gz

Where filename.tar.gz is the backup file name.

Check whether the extracted file contains the correct database.

Cd filename/

Ll

The system is shown below, where db0dz1rv11f44yg2, mysql and test are the databases that exist in the cloud database.

-rw-r--r-- 1 root root 269 Aug 19 18:15 backup-my.cnf

Drwxr-xr-x 2 root root 4096 Aug 21 10:31 db0dz1rv11f44yg2

-rw-rw---- 1 root root 209715200 Aug 7 10:44 ibdata1

Drwxr-xr-x 2 root root 4096 Aug 21 10:31 mysql

Drwxr-xr-x 2 root root 4096 Aug 21 10:31 test

-rw-r--r-- 1 root root 10 Aug 19 18:15 xtrabackup_binary

-rw-r--r-- 1 root root 23 Aug 19 18:15 xtrabackup_binlog_info

-rw-r--r-- 1 root root 77 Aug 19 18:15 xtrabackup_checkpoints

-rw-r--r-- 1 root root 2560 Aug 19 18:15 xtrabackup_logfile

-rw-r--r-- 1 root root 72 Aug 19 18:15 xtrabackup_slave_info

Restore the data file.

Innobackupex-defaults-file=./backup-my.cnf-apply-log.

If the system displays innobackupex: completed Oklahs, the data recovery is successful.

Modify the configuration file. Comment out the innodb_fast_checksum, innodb_page_size, and innodb_log_block_size in the extracted file backup-my.cnf, and add datadir=/home/mysql, as shown below.

# This MySQL options file was generated by innobackupex-1.5.1.

# The MySQL Server

[mysqld]

Innodb_data_file_path=ibdata1:200M:autoextend

Innodb_log_files_in_group=2

Innodb_log_file_size=524288000

# innodb_fast_checksum=0

# innodb_page_size=16364

# innodb_log_block_size=512

Datadir=/home/mysql/

Reinstall the MySQL system library to obtain the root permission of the database.

Rm-rf mysql

Mysql_install_db-user=mysql-datadir=/home/mysql/

If the system displays as follows, the mysql system library is reinstalled successfully.

Installing MySQL system table...

OK

Filling help table...

OK

Modify the file owner.

Chown-R mysql:mysql / home/mysql/

Start the mysqld process.

Mysqld_safe-- defaults-file=/home/mysql/backup-my.cnf &

Log in to the database using the client.

Mysql-u root-p

Verify that the database is complete.

Show databases

If the system shows that it is selected, the database is restored successfully.

+-+

| | Database |

+-+

| | information_schema |

| | db0dz1rv11f44yg2 |

| | mysql |

| | performance_schema |

| | test |

+-+

Export using logical backup files

This example takes the local CVM as the RHEL6/x64 system and the backup file storage path / home/mysql/ as an example.

Operation steps

Download the logical backup file of the cloud database and upload it to the target cloud server. For backup file acquisition methods, see download backup data. If the destination CVM can access the source instance, you can also use wegt "url" to download backup files. Where url is the download address of backup files.

Switch the path to the path where the backup file is located.

Cd / home/mysql/

Extract the backup file.

Tar vizxf filename.tar.gz

Where filename.tar.gz is the backup file name.

Extract the sql compressed file.

Gunzip filename.sql.gz

Where filename.sql.gz is the name of the sql compressed file.

Perform a logical import operation to import the data into the target database.

Mysql-u userName-p-h hostName-P port dbName < filename.sql

Where filename.sql is the decompressed sql file.

After reading the article on how to migrate RDS for MySQL data to local MySQL, what do you think? If you want to know more about it, you can continue to follow our industry information section.

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.