Use XtraBackup for MySQL master-slave replication without locking tables
Xtrabackup is an open-source project of percona to implement the innodb Hot Backup function similar to InnoDB's official Hot Backup tool. It can quickly back up and restore MySQL databases. Xtrabackup contains two tools:
Xtrabackup is a tool used for hot backup of innodb and xtradb table data. It cannot back up other types of tables or data table structures;
Innobackupex is a perl script that encapsulates xtrabackup and provides the ability to back up myisam tables.
I. Installation
1. Configure the Yum Source
Rpm-Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
2. Installation
Yum install xtrabackup-y
Ii. Configure Master/Slave
Create a Master-slave backup account on the Master server
Grant replication slave on *. * TO 'slave '@' % 'identified by '123 ';
Modify the Master configuration file and restart the Mysql service.
[Mysqld]
Thread_concurrency = 32
Long_query_time = 2
Slow-query-log = on
Slow-query-log-file =/home/mysql/logs/mysql-slow.log
Skip-name-resolve
Server_id = 205
Binlog-ignore-db = mysql
Log-bin = ddsc-mysql-slave1-bin
Binlog_cache_size = 1 M
Expire_logs_days = 7
Skip-name-resolve
Relay_log =/data/mysqldb/ddsc-mysql-relay-bin
Slave-skip-errors = all
Log_slave_updates = 1
Log-bin = ddsc-mysql-bin
Binlog_format = mixed
Modify the Slave configuration file and restart the Mysql service. For the configuration content, see the main configuration file. Note: server_id cannot be consistent.
Use xtrabackup to back up the master database.
Innobackupex -- defaults-file =/etc/my. cnf -- socke =/data/mysqldb/mysql. mysql -- user = root -- password = ''/backup/
Copy the backed up file to the Slave end.
Scp-r 2016-08-08_13-53-57/root@192.168.3.225:/tmp/
Generally, this backup cannot be used for restoration, because the backup data may contain uncommitted transactions or transactions that have been committed but not synchronized to the data file. Therefore, when the data file is in an inconsistent state, we need to roll back uncommitted transactions and synchronize committed transactions to the data file, so that the data file is in a consistent state.
Innobackupex-apply-log/tmp/2016-08-08_13-53-57/
Restore data
Stop the mysqld service, clear the data file directory, and then set permissions after recovery.
Service mysqld stop
Mv/data/mysqldb/data/mysqldb_old
Mkdir-p/data/mysqldb
Innobackupex -- copy-back/tmp/2016-08-08_13-53-57/
Chown-R mysql. mysql/data/mysql/data
Service mysqld start
8. view the Location Value of the binlog file:
Cat/tmp/2016-08-08_13-53-57/xtrabackup_binlog_info
Ddsc-mysql-bin.00001912476 # these two numbers are essential for Master
9. perform the following operations on the server Load balancer instance:
CHANGE MASTER
MASTER_HOST = '1970. 168.1.xxx ',
MASTER_USER = 'rep ',
MASTER_PASSWORD = 'xxxxxx ',
MASTER_PORT = 3306,
MASTER_LOG_FILE = 'ddsc-mysql-bin.000019 ',
MASTER_LOG_POS = 12476;
10. Start the slave server
Start slave;
MySQL management-using XtraBackup for Hot Backup
MySQL open-source backup tool Xtrabackup backup deployment
MySQL Xtrabackup backup and recovery
Use XtraBackup to implement MySQL master-slave replication and quick deployment [master-slave table lock-free]
Install and use Xtrabackup from Percona to back up MySQL
XtraBackup details: click here
XtraBackup: click here
This article permanently updates the link address: