Using xtrabackup to achieve MySQL master-slave replication

Reference 1190000003063874

If we use the traditional mysqldump to implement MySQL master-slave, we need to lock the library, if the online environment will affect the operation, so we use Xtrabackup to implement MySQL master-slave configuration.

Advantages of Xtrabackup

1. Data backup can be done quickly and reliably (copying data files and tracking transaction logs)

2. Transaction processing is not interrupted during data backup (hot backup)

3. Conserve disk space and network bandwidth

4. Auto-Complete backup authentication

5. Increased online time due to faster recovery times

A. Command installation:

Yum install-y GNUPGRPM-UHV http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm (64-bit source ) RPM-UHV https://www.percona.com/redir/downloads/percona-release/percona-release-0.0-1.i386.rpm (32-bit source) Yum Install -y percona-xtrabackup[[email protected] ~]# Xtrabackup-vxtrabackup version 2.3.3 based on MySQL server 5.6.24 Linux (x86_ (Revision id:525ca7d)

Two MySQL configuration

1. MASTER:/ETC/MY.CNF plus datadir=/~~~~~~server-idlog-bin2. Slave:/etc/my.cnfserver-id=2datadir=/var/lib/mysql

3. Master: Set the slave user rights to synchronize in the master database

Grant Replication Slave on * * to ' repl ' @ ' 192.168.75.128 ' identified by ' 123123 ';

(from the host IP, and establish a synchronized user repl)

4 Master: Export Data (default is to back up all databases)

Innobackupex--defaults-file=/etc/my.cnf--user=root--password=123456/data/backup

innobackupex:backup created in directory '/data/backup/2012-04-19_10-46-32 '
innobackupex:mysql binlog position:filename ' log_bin.000027 ', position 2973624
120419 10:46:53 innobackupex:completed ok!
the last output completed ok! indicates that the backup was successful.

The backed up files are saved in the/data/backup directory, such as:
/data/backup/2012-04-19_10-46-32/
[email protected] ~]#ls/data/backup/2012-04-19_10-46-32/
backup-my.cnf ibdata1 mysql shipincon test xtrabackup_binary xtrabackup_binlog_info xtrabackup_checkpoints Xtrabackup_logfile
 
BACKUP LOG:
data files that have just been backed up are not directly available. We need a second recovery .

[email protected] ~]#  

Innobackupex--apply-log/data/backup/2012-04-19_10-46-32/

5 Copy the recovered data to the slave

Scp-r/data/backup/2012-04-19_10-46-32/[Email protected]:/data/

< Span style= "Font-family:verdana, Arial, Helvetica, Sans-serif;font-size:13.92px;background-color:rgb (255,255,255);" >

< Span style= "Font-family:verdana, Arial, Helvetica, Sans-serif;font-size:13.92px;background-color:rgb (255,255,255);" >

< Span style= "Font-family:verdana, Arial, Helvetica, Sans-serif;font-size:13.92px;background-color:rgb (255,255,255);" > Shut down from the server and switch data:  

< Span style= "Font-family:verdana, Arial, Helvetica, Sans-serif;font-size:13.92px;background-color:rgb (255,255,255);" > then pour the database

innobackupex   --defaults-file=/etc/my.cnf  -- Copy-back  2012-04-19_10-46-32 

Finally remember to change the owner and the group MySQL, otherwise you cannot restart

Chown-r Mysql:mysql * Then start mysqld:/etc/init.d/mysql start

Compared to the steps of mysqldump backup, we do not have flush tables with read lock this time.
There is also no show master status to get the log file name and coordinates.

This information is automatically saved because xtrabackup completes the backup.

We look at a file inside the backup cat there's xtrabackup_binlog_info inside .

mysql-bin.000008 120

The last step, log in to the slave database above

Stop Slave;change Master to master_host= ' 10.171.132.57 ', master_port=3306, master_user= ' repl ', master_password= ' 123123 ', master_log_file= ' mysql-bin.000008 ', Master_log_pos=120;start slave;show slave status\g; View information

If there are two yes, that means it's successful.

Test: You can delete or create a table above the master

Create Huningfei table Huningfei (id int (ten), name varchar (10));

Delete drop tables Huningfei;

This article is from the "Drifting Away" blog, make sure to keep this source http://825536458.blog.51cto.com/4417836/1803968

Using xtrabackup to achieve MySQL master-slave replication