To build Xtrabackup, you must understand the backup and restoration of the Xtrabackup tool to build a master-slave architecture online. MySQL backup tools of the Enterprise Edition that MySQL has always recommended:
To build Xtrabackup, you must understand the backup and restoration of the Xtrabackup tool to build a master-slave architecture online. MySQL backup tools of the Enterprise Edition that MySQL has always recommended:
Preface:
MySQL databases are often used to build e-commerce platforms. e-commerce platforms mean that the platforms that make money for the company must be online 24 hours a day. We have also set up our own e-commerce platforms, however, the recent response requires an additional slave database to relieve the read Pressure on the master database. Baidu has many related methods on the Internet, which are summarized as follows:
This method is simple to build MySQLdump, but you need to lock the table during the operation and stop the application. This method is applicable when the system is not online. New users can build it for learning ;()
To build Xtrabackup, you must understand the backup and restoration of the Xtrabackup tool to build a master-slave architecture online.
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
MySQL has always recommended MySQL backup tool of the Enterprise Edition: MySQLbackup. With a learning attitude, you can use MySQLbakcup to build a master-slave architecture in the test environment;
Before performing the following operations, you must first make the following two settings: The parameter files of the master and slave databases have been modified, and the master and slave connected users have been created on the master database;
The detailed steps are as follows:
Database
Host Name
IP address
Synchronize users
Backup location
Primary Database
Mysql01
192.168.47.152
Server01
/Backup
Slave Database
Mysql02
192.168.47.151
2 operation steps of the master database
2.1 perform full backup for the master database. The script is as follows:
Mysqlbackup -- user = root -- password -- backup-dir =/backup-and-apply-log
The backup directory is/backup. Check whether the directory exists;
2.2 record the binlog status of the master database at this time
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000003 | 107 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
2.3 to verify that the master and slave databases are correct, you can insert data to a database in the master database and record the status.
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000003 | 500 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
2.4 copy the backup from the master database to the slave Database
[Root @ mysql01 backup] # service iptables stop
Iptables: clear firewall rules: [OK]
Iptables: Set the chain to policy ACCEPT: filter [OK]
Iptables: uninstalling module: [OK]
Root @ mysql01 backup] # scp bak.tar root @ mysql02:/backup /*
Warning: Permanently added the RSA host key for IP address '2017. 168.47.151 'to the list of known hosts.
Root @ mysql02's password:
Bak.tar 100% 69 MB 23.1 MB/s
Disable the firewall first.
3. Slave database operation steps
3.1 restore the slave Database
[Root @ mysql01 backup] # mysqlbackup -- defaults-file =/backup/server-my.cnf -- datadir =/data/mysql -- backup-dir =/backup/copy-back
MySQL Enterprise Backup version 3.11.0 Linux-3.8.13-16.2.1.el6uek.x86_64-x86_64 []
Copyright (c) 2003,201 4, Oracle and/or its affiliates. All Rights Reserved.
Mysqlbackup: INFO: Starting with following command line...
Mysqlbackup -- defaults-file =/backup/server-my.cnf -- datadir =/data/mysql
-- Backup-dir =/backup/copy-back
Mysqlbackup: INFO:
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'Copy-back' run mysqlbackup
Prints "mysqlbackup completed OK! ".
141118 16:19:35 mysqlbackup: INFO: MEB logfile created at/backup/meta/MEB_2014-11-18.16-19-35_copy_back.log
--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
Datadir =/data/mysql
Innodb_data_home_dir =/data/mysql
Innodb_data_file_path = ibdata1: 10 M: autoextend
Innodb_log_group_home_dir =/data/mysql/
Innodb_log_files_in_group = 2
Innodb_log_file_size = 5242880
Innodb_page_size = Null
Innodb_checksum_algorithm = none
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
Datadir =/backup/datadir
Innodb_data_home_dir =/backup/datadir
Innodb_data_file_path = ibdata1: 10 M: autoextend
Innodb_log_group_home_dir =/backup/datadir
Innodb_log_files_in_group = 2
Innodb_log_file_size = 5242880
Innodb_page_size = 16384
Innodb_checksum_algorithm = none
Mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
141118 16:19:35 mysqlbackup: INFO: Copy-back operation starts with following threads
1 read-threads 1 write-threads
Mysqlbackup: INFO: cocould not find binlog index file. If this is online backup then server may not have started with -- log-bin.
Hence, binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible.
141118 16:19:35 mysqlbackup: INFO: Copying/backup/datadir/ibdata1.
141118 16:19:37 mysqlbackup: INFO: Copying the database directory 'john'
141118 16:19:37 mysqlbackup: INFO: Copying the database directory 'mysql'
141118 16:19:37 mysqlbackup: INFO: Copying the database directory 'performance _ Scheme'
141118 16:19:37 mysqlbackup: INFO: Completing the copy of all non-innodb files.
141118 16:19:37 mysqlbackup: INFO: Copying the log file 'ib _ logfile0'
141118 16:19:37 mysqlbackup: INFO: Copying the log file 'ib _ logfile1'
141118 16:19:39 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in/data/mysql
141118 16:19:39 mysqlbackup: INFO: Copy-back operation completed successfully.
141118 16:19:39 mysqlbackup: INFO: Finished copying backup files to '/data/mysql'
For more details, please continue to read the highlights on the next page:
,