Mysqlbackup online configuration of MySQL Master/Slave Architecture

Mysqlbackup online configuration of MySQL Master/Slave Architecture

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:

1 2 Next Page