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

Prerequisites:

The local MySQL database is installed in a 64-bit Linux system and is the same as the MySQL version of the cloud database.

The data recovery tool Percona XtraBackup 2.2.9 or above is installed in the operating system

Please download https://www.percona.com/downloads/XtraBackup/LATEST/ from Percona XtraBackup's official website

Percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm is a fast installation of rpm packages

# yum localinstall percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm

The data backup file decompression tool rds_backup_extract.sh has been installed in the operating system

Download address: http://oss.aliyuncs.com/aliyunecs/rds_backup_extract.sh?spm=a2c4g.11186623.2.6.Ng5QVG&file=rds_backup_extract.sh

Procedure:

In the Aliyun RDS management console, in the data backup list, locate the data backup you want to download, and click the corresponding download.

The specific steps are as follows:

Download the data backup file by executing the following command

Wget-c''- O. tar.gz

-c: enable breakpoint resume mode.

-O: saves the download result as the specified file (it is recommended that you use the file name included in URL).

Restore the downloaded data backup to the local MySQL database as follows:

Execute the following command to extract the downloaded data backup file.

Note: this article takes the custom path / home/mysql/data as an example. You can replace it with the actual path according to the actual situation.

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

-f: specifies the backup set file to extract.

C: specify the directory to which the file is to be extracted. Optional parameter. If not specified, extract it to the current directory.

# bash rds_backup_extract.sh-f hins2642811_data_20180227013214.tar.gz-C data

two。 Execute the following command to query the unzipped files.

# ls-l data/

3. Execute the following command to restore the unzipped backup files.

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

# innobackupex-defaults-file=data/backup-my.cnf-apply-log data

4. To avoid version problems, you need to modify the backup-my.cnf parameters as follows.

Execute the following command to edit the backup-my.cnf file as text.

# vi / home/mysql/data/backup-my.cnf

Execute the following command and comment out the following parameters.

# innodb_fast_checksum

# innodb_page_size

# innodb_log_block_size

If you report an error, these two are also annotated.

# innodb_checksum_algorithm=innodb

# innodb_log_checksum_algorithm=innodb

# innodb_undo_directory=.

# innodb_undo_tablespaces=0

# rds_encrypt_data=false

# innodb_encrypt_algorithm=aes_128_ecb

Press the ESC key, then type: wq to save and close the editor.

=

Install mysql-server

Download address: https://dev.mysql.com/downloads/mysql/

Mysql 5.6download address:

Https://cdn.mysql.com//Downloads/MySQL-5.6/MySQL-server-5.6.39-1.el6.x86_64.rpm

Mysql 5.7download address:

Https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-community-server-5.7.21-1.el6.x86_64.rpm

This version is: Server version: 5.1.73

# yum-y install mysql-server mysql-develop mysql

Upgrade the mysql version:

# yum-y remove mysql-server mysql-develop mysql

# yum localinstall MySQL-server-5.6.39-1.el6.x86_64.rpm

Error report: file / usr/share/mysql/charsets/dec8.xml from install of MySQL-server-5.6.39-1.el6.x86_64

Conflicts with file from package mysql-libs-5.1.73-8.el6_8.x86_64

See "conflicts", there is a conflict, file "/ usr/share/mysql/charsets/*"

The MySQL-server-5.6.19-1.linux_glibc2.5.x86_64 version is required, and the mysql-libs-5.1.73-8.el6_8.x86_64 version already exists in the system!

Solution:

Delete mysql-libs-5.1.73-8.el6_8.x86_64 by executing the following command:

# yum-y remove mysql-libs-5.1.73*

Then execute the following command to install MySQL:

# yum localinstall MySQL-server-5.6.39-1.el6.x86_64.rpm

The installation was successful.

Install the mysql client:

Download address: https://cdn.mysql.com//Downloads/MySQL-5.6/MySQL-client-5.6.39-1.el6.x86_64.rpm

# yum-y localinstall MySQL-client-5.6.39-1.el6.x86_64.rpm

=

5. Execute the following command to modify the file owner and determine that the file belongs to the MySQL user.

Chown-R mysql:mysql / home/mysql/data

# chown-R mysql:mysql data

6. Execute the following command to start the MySQL process.

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

# mysqld_safe-defaults-file=/opt/data/backup-my.cnf-user=mysql-datadir=/opt/data &

7. Execute the following command to log in to the MySQL database to verify that the process started successfully.

Mysql-uroot

8. After the restore is complete, the table mysql.user does not contain the users created in the RDS instance and needs to be created. Before creating a new user, execute the following command

Delete from mysql.db where user'root' and char_length (user) > 0

Delete from mysql.tables_priv where user'root' and char_length (user) > 0

Flush privileges

9. Create a user:

Error: mysql > grant all on *. * to xiaoyeyun@ "%" identified by "xiaoyeyun"

ERROR 1558 (HY000): Column count of mysql.user is wrong. Expected 43, found

forty-two。 Created with MySQL 50518, now running 50639.

Please use mysql_upgrade to fix this error.

Perform the fix as described in the problem:

Mysql_upgrade mysql-h 192.168.1.2-u xxx-p

# mysql_upgrade mysql

The user was created successfully.

Mysql > grant all on *. * to xiaoyeyun@ "%" identified by "xiaoyeyun"

Query OK, 0 rows affected (0.00 sec)

Mysql > select user,host from mysql.user

+-+ +

| | user | host |

+-+ +

| | xiaoyeyun |% | |

| | root | 127.0.0.1 | |

| | root |:: 1 |

| | root | localhost |

+-+ +

4 rows in set (0.00 sec)

Mysql >

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.