Use XtraBackup to implement MySQL master-slave replication and quick deployment [master-slave table lock-free]

Four Common mysql backup tools have their own disadvantages:

0 tar
Disadvantage: Hot Backup is not supported. You need to disable the mysqld server before backing up. Mysqld must also be disabled during restoration.
1 mysqldump
Disadvantages: MySQL provides mysqldump to convert data into SQL statements. This method is less efficient, and backup and restoration are slow. In addition, to ensure data consistency during the dump process, any data insertion and update operations will be suspended.
2 ibbackup
Disadvantage: payment.
3 LVM
Disadvantage: This method uses the logical volume image function to back up the entire partition online. This backup data is large and the backup performance is low.

4. Percona XtraBackup.
XtraBackup open source, hot backup, no table lock, support for innodb,
Supports partial data backup (for example, only backing up a table in a database ),
High performance, with little impact on system load during Backup.
You can use tar4ibd to directly generate compressed backup files,
Incremental backup is supported!
It has the concept of time point and can be used with mysql binary log.

Official website details ~ Http://www.percona.com/doc/percona-xtrabackup? Id = percona-xtrabackup: start

Simple Steps ~

Procedure

1. Master:/etc/my. cnf

Add datadir = /~~~~~~

Server-id

Log-bin

2. Slave:/etc/my. cnf

Server-id = 2

Datadir =/var/lib/mysql

 

3. Master: Set the slave user permissions for synchronization in the master database.

 

Grant replication slave on *. * TO '<slave_username>' @ '<slave_ip> 'identified BY' <slave_password> ';

 

4. Master: export data to slave

Xtrabackup is used to back up mysql. The advantage is that the lock table time on the master node is very short and can be used in the actual production environment. xtrabackup automatically records the location of the synchronization log file.

 

Innobackupex-1.5.1 -- stream = tar/tmp/| ssh <slave_host> "mkdir/tmp/db; tar xfi--C/tmp/db /"

In this step, the master data, including the entire table structure, is exported, compressed, copied to slave, and decompressed to the/tmp/db directory of slave.

 

5. Slave: import data to slave

Innobackupex-1.5.1 -- apply-log/tmp/db

Innobackupex-1.5.1 -- copy-back/tmp/db

Chown-R mysql. mysql/var/lib/mysql /*

6. Slave: Start Data Synchronization

 

View/var/lib/mysql/xtrabackup_binlog_info to obtain the log file and position.

 

CHANGE MASTER

MASTER_HOST = '<master_host> ',

MASTER_USER = '<slave_username> ',

MASTER_PASSWORD = '<slave_password> ',

MASTER_LOG_FILE = '<see xtrabackup_binlog_info> ',

MASTER_LOG_POS = <see xtrabackup_binlog_info>;

 

Start slave;

1 2 Next Page