Xtrabackup backup and restore of MySQL

There are two concepts to note in operating MySQL:

Do all remember flush privileges;

Grant all on * * to [e-mail protected] ' localhost ' identified by ' password ' with GRANT OPTION

Remember to add with GRANT OPTION

=========================

2,rds MySQL uses open source software Xtrabackup to back up the MySQL database. You need to download the software and then use the software to recover it.

Put the downloaded file *.tar.gz on the Linux service

mkdir/mysql2/cd/mysql2/

TAR-IZXVF *.tar.gz

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

RM-RF MySQL #这里删除mysql目录是为了以后连接数据库权限

3, the key is here, we need to configure MY.CNF, if you have a Linux server already has an instance, then now you have to create an instance (that is, a multiple MySQL service)

/usr/local/lnmp/mysql/bin/mysql_install_db--user=mysql--datadir=/mysql2/

Vi/etc/my.cnf

[Client]
Default-character-set = UTF8

[Mysqld_multi]
Mysqld =/mysql/bin/mysqld_safe
Mysqladmin =/mysql/bin/bin/mysqladmin
user = root

[Mysqld1]
Port = 3306
Socket =/tmp/mysql.sock
DataDir =/date/
Pid-file =/date/mysql1.pid
Log-error =/date/mysql_error.log
Skip-locking
Max_connections = 600
Event_scheduler=on
Server-id = 1

[Mysqld2]
Port = 3307
Socket =/tmp/mysql2.sock
DataDir =/mysql2
Pid-file =/mysql2/mysql.pid
Log-error =/date/mysql_error2.log
Innodb_data_file_path=ibdata1:200m:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=1048576000 #这三行很重要, in the/mysql2/mysql-my.cnf, copy it, or the InnoDB table will not open

Launch instance

/MYSQL/BIN/MYSQLD_MULTI--DEFAULTS-FILE=/ETC/MY.CNF Start 1

/MYSQL/BIN/MYSQLD_MULTI--DEFAULTS-FILE=/ETC/MY.CNF Start 2

Stopping instance methods

Kill-9 ' Ps-ef | grep 3307 | Grep-vgrep | awk ' {print $} ' | Xargs '

Modify MySQL Database user name

Mysql-uroot-pxxxx-s/tmp/mysql2.sock

Grant all on * * to [e-mail protected] ' localhost ' identified by ' xxxx ' with GRANT OPTION

4, the 3rd step above is for a multiple instances, if an instance of the words do not need to be so troublesome, the 3rd step is as follows:

Vi/etc/my.cnf

[Mysqld]

********

Innodb_data_file_path=ibdata1:200m:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=1048576000 #只要加这三行就可以了

********

~~~~~~~~~~~~

How to reset your password:

Method One:
1. Turn off MySQL
# Service Mysqld Stop
2. Blocking permissions
# Mysqld_safe--skip-grant-table
Screen appears: Starting demo from .....
3. Start a new terminal input
# mysql-u Root MySQL
mysql> UPDATE user SET Password=password (' NewPassword ') where user= ' root ';
Mysql> FLUSH privileges;//Remember to say this, otherwise if you close the previous terminal, the original error will appear
Mysql> \q

~~~~~~~~~~~~~

Things to remember when restoring:

MySQL error: The MySQL server is running with the--skip-grant-tables option so it cannot execute this statement

The MySQL server is running with the--skip-grant-tables option so it cannot execute this statement

Workaround:

mysql> set global read_only=0;
(Turn off read-only properties for the new Main library)

Flush privileges;

Set global Read_only=1; (read/write Zodiac)

Flush privileges;

~~~~~~~~~~~~~~~~~

Ali's Operation guide:

1. OverviewRDS MySQL uses open source software XtrabackupMake a backup of the MySQL database. You need to download the software and then use the software to recover it. 1.1 software that needs to be installedThe current version of RDS using Xtrabackup is 2.0.6, Clickand select the original that corresponds to your operating system version. For example, we download the rhel6/x86_64 version here. and install using the RPM command: sudo rpm-ivh percona-xtrabackup-2.0.6-521.rhel6.x86_64.rpm 1.2 Quick Resume ManualThis is a quick recovery note for users who understand the Linux environment and the RDS Management console. Refer to "Step by step instructions" later in this article for more information. This assumes that the backup file downloaded from the management console is backup-20130819.tar.gz and the recovery process is performed under/some-user-dir. Unzip backup file (* need to add-i parameter *) CP bak.tar.gz/some-user-dir Cd/some-user-dir tar-izxvf BAK.tar.gz Restore data to a consistent state Innobackupex--de FAULTS-FILE=./BACKUP-MY.CNF--apply-log./This step if you see an output similar to the following, indicating a successful execution: 111225 01:08:13 innobackupex:completed ok! At this time, the current directory of data is a complete data file can be used, copy all the files under the directory to the database data directory, and then open the database, that is, complete the recovery. 2. Step by step instructions 2.1 Download Backup files in the admin console2.1.1 In the RDS console backup and Recovery list, select a backup file to download, such as 2.1.2 Click the Download, the dialog box will pop up, download the backup file to the computer, such as 2.1.3 download file successfully, upload the backup file to the Linux system, After the upload is successfully detected on the Linux system, such as 2.2 Unzip the backup file2.2.1 Decompression backup File Execution command: Tar vizxf hins47160_xtra_20130819181504.tar.gz, such as 2.2.2 query extracted after the generated file, where the blue font of the directory file for backup generated when RDS exists database 2.3 Recovering data FilesGo to the Unzip directory and execute the following command: Innobackupex--defaults-file=./backup-my.cnf--apply-log./This step if you see an output similar to the following, indicating that the execution was successful: 2.4 Configuration file ModificationDue to the version issue, please extract the innodb_fast_checksum,innodb_page_size in the file backup-my.cnf, innodb_log_block_size comment out and add datadir=/xxx/ xxx/(backup file decompression path), such as: 2.5 reload MySQL system library and modify file owner2.5.1 users can delete the existing MySQL directory, rebuild the MySQL system library to have the root permissions of the database to delete the MySQL library, command: RM-RF MySQL and confirm the deletion succeeds, such as: 2.5.2 initialize mysql default library command: Mysql_ install_db--user=mysql--datadir=/xxx/xxx/(the directory where the backup file belongs) and confirm that the operation was successful, such as: 2.5.3 Modify the file is the main command: Chown-r mysql:mysql/xxx/xxx/(the backup file belongs to directory) and check that the file belongs to the MySQL user 2.6 Start the MYSQLD process and login verification2.6.1 start the mysqld process and verify the startup success command: Mysqld_safe--DEFAULTS-FILE=/XXX/XXX/BACKUP-MY.CNF & (the directory where the backup files belong) (Click to view full image) 2.6.2 Client login mysql authentication command: Mysql-u root-p, 3. Other issuesDue to software limitations, the current recovery is only supported under Linux. Linux recovered data files, regardless of Windows, Linux, MySQL can be normal use

Xtrabackup backup and restore of MySQL