Use XtraBackup for MySQL master-slave replication without locking tables

Source: Internet
Author: User
Tags types of tables percona perl script

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:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.