Xtrabackup mysql backup (full process from the database)

I. background a database on the online has always been maintained by RD itself. Due to business growth, the data is now 500 GB +, and a slave database is urgently needed to log on to the machine, potholes, or mysql5.6 (we maintain 5

I. background a database on the online has always been maintained by RD itself. Due to business growth, the data is now 500 GB +, and a slave database is urgently needed to log on to the machine, potholes, or mysql5.6 (we maintain 5

I. background

One online database has always been maintained by RD. Due to business growth, the data is now 500 GB +, and a slave database is urgently needed to log on to the machine, or mysql5.6 (which we maintain is 5.5), and mysql5.6 has not been installed yet. In addition, none of them are installed according to our O & M standards, my first reaction was that this job could not be done, and the database was not allowed to be stopped for too long. So I decided to use Xtrabackup to give it a try, I have been listening to you all the time, but I have never been familiar with Xtrabackup. I think Data Backup (used for slave database) is still the best cold backup, and the speed is fast, it is also the best way to ensure data consistency, but today this situation does not seem to work, Xtrabackup !!!

2. Learn about Xtrabackup

Xtrabackup:

1. fast and reliable backup; 2. uninterrupted transaction processing during Backup; 3. Saving disk space and network bandwidth; 4. Automatic Backup verification; 5. Improving normal running time due to faster recovery time

I personally think that the backup does not need to lock the table (innodb). The backup of MyISAM tables will still be locked and Incremental backup is also supported. In short, this tool is worthy of praise.

Reference:

Iii. Implementation

1. Environment:

172.28.29.152 master database 172.28.26.138 slave database (to be implemented)

2. Install Xtrabackup on the master database

Yum install perl-Time-HiRes * yum install perl-DBD-MySQL-ywget rpm-ivh percona-xtrabackup-2.1.9-744.rhel6.x86_64.rpm

3. Back up the master database

Innobackupex -- defaults-file =/usr/local/mysql/my. cnf -- user root -- socket =/data/log/mysql. sock -- password 123456/data/xtrabackup/& # backup ps: After the backup is complete, a time point directory is generated under the/data/xtrabackup/directory, you can also add the -- no-timestamp parameter to not generate this directory and directly back up it to innobackupex -- defaults-file =/usr/local/mysql/my under/data/xtrabackup. cnf -- user root -- socket =/data/log/mysql. sock -- password 123456 -- apply-log/data/xtrabackup/2014-06-04_10-20-55/& # preparing, undo uncommitted transactions, replay redo log

Ps: ll/data/xtrabackup/2014-06-04_10-20-55/view, you find that xtrabackup will copy all the data files in your database datadir and add five files starting with xtrabackup, we are concerned about the xtrabackup_binlog_info file, because this file records the position and pos point where you need to change from the database to the binlog of the master database.

4. Install mysql5.6 from the database

Mysql in the master database is compiled and installed by RD, and the path is/usr/local/mysql/. Are you still struggling to re-compile the database? No, you can use the scp directory of the master database directly, my. cnf is also scp, and the host of the log and datadir directory must be mysql. Then you can initialize the startup library.

Scp-r/usr/local/mysql/172.28.26.138:/usr/local/chown mysql: mysql/data/log/-R # log and pid storage directory chown mysql: mysql/data/mysql/-R # datadir/usr/local/mysql/my. add server-id = 2/usr/local/mysql/scripts/mysql_install_db -- user = mysql -- basedir =/usr/local/mysql -- datadir =/data/mysql/initialization to cnf database/usr/local/mysql/bin/mysqld_safe -- defaults-file =/usr/local/mysql/my. cnf -- user = mysql & start database/usr/local/mysql/bin/mysqladmin -- sock =/data/log/mysql. sock-p123456 shut down the database and clear the datadir directory. perform the following steps:

PS: the master database has not set the server-id. This is not a pitfall. Don't worry. This parameter is dynamic and can be adjusted online. set global server_id = 1; OK.

5. The slave Database Server pulls the backup file of the master database to data_dir.

Rsync-av 172.28.29.152: xtrabackup/2014-06-04_10-20-55/data/mysql /&

6. Start the database and build a master-slave database,

/Usr/local/mysql/bin/mysqld_safe -- defaults-file =/usr/local/mysql/my. cnf -- user = mysql & cat/data/mysql/xtrabackup_binlog_infomysql-bin.008370 147669327 change master to master_host = "172.28.29.152", master_port = 3306, master_user = 'slave ', master_password = '2016 ', master_log_file = 'mysql-bin.008370 ', master_log_pos = 147669327;

This task has been completed:

This article is from the blog "diaosi O & M male" and is not reposted!