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
Data backup can be done quickly and reliably (copying data files and tracking transaction logs)
Transaction processing is not interrupted during data backup (hot backup)
Conserve disk space and network bandwidth
Auto-Complete backup authentication
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