Backup scheme for MySQL database using the Percona-xtrabackup tool

Backup scheme for MySQL database using the Percona-xtrabackup tool

Need to back up the MySQL host 172.16.155.23
Host 172.16.155.22 for backup MySQL

Purpose: The MySQL database on the 155.23 host is backed up on a daily basis and synchronized to the remote host

1. Install the Backup tool

Yum localinstall-y percona-xtrabackup-2.2. 9-5067. el6.x86_64.rpm

2. Write the BACKUP database script:

Vim/data/scripts/backdb.SH#!/bin/Bashback_time=`Date+%y%m%d%h%m%S ' Backpath="/app/mysqlbak"CD $backpath&&/bin/mkdir$back _timeEcho "start back ${back_time}">>/app/mysqlbak/backdb.log# completely backup/usr/bin/innobackupex--defaults-file="/etc/my.cnf"--user=root--password=pass--socket=/tmp/mysql.sock $backpath/$back _time/usr/bin/SCP-P58422-L200000-R $backpath/$back _time172.16.155.22:/app/mysqlbak/# Delete theDays ago Backup/bin/Find/app/mysqlbak-type D-mtime + the-execRM-RF {} \;Echo "end back ${back_time}">>/app/mysqlbak/backdb.log

chmod +x/data/scripts/backdb.sh

3. Configure 155.23 password-free access 155.22
[[email protected] 20171127]# ssh-keygen-t rsa-p '
If the SSH port is not the default port 22, use the following command:
Ssh-copy-id-i/root/.ssh/id_rsa.pub "-p 58422 [email protected]"

Scheduled tasks, backed up 1:1 every night:
1 1 * * */bin/bash/data/scripts/bakdb.sh >/dev/null 2>&1

4. Target backup server configuration, delete backup from 7 days ago:
Mkdir-p/app/mysqlbak/

2 2 * * */bin/find/app/mysqlbak/-mtime +7-exec rm-rf {} \;

Temporary scenarios for the database:
1. Configure both servers to start the MySQL service, one of which is currently running server and the other as a standby
2. Back up data once a day and then transfer it to a remote server, removing backups from 10 days ago
3. If the MySQL outage is in use, restore the latest data to backup MySQL, then restore the latest data to the backup MySQL
Remember to modify the parse/etc/hosts file
172.16.155.23 db.mysql.com

Specific actions for recovery:

Recovery readiness:
The target server is installed with the same MySQL version as the original server (see the appendix below)

# 1. Full backup of the existing MySQL database
# completely backup
/usr/bin/innobackupex--defaults-file= "/etc/my.cnf"--user=root--password=pass--socket=/tmp/mysql.sock/app/ Mysqlbak/back_time

# 2. Transfer the backup data to the target machine
Scp-p 58422-r 2017-11-27_16-52-50 172.16.155.22:/app/mysqlbak/

# 3. Target Machine Execution:

# So now we're going to make the data file consistent by rolling back uncommitted transactions and synchronizing committed transactions to data files
Innobackupex--user=root--password=pass--defaults-file=/tmp/my.cnf--apply-log/app/mysqlbak/2017-11-27_16-52-50

# 4. Switch off the service and migrate the existing data directory
Service Mysqld Stop
# Cd/app/data
[Email protected] data]# MV MyData mydata_old
[Email protected] data]# mkdir MyData
[Email protected] data]# chown-r mysql.mysql MyData
# 5. Execute innobackupex Restore command
Innobackupex--defaults-file=/etc/my.cnf--user=root--password=pass--copy-back/app/mysqlbak/2017-11-27_16-52-50

# 6. Modify permissions to start the service
Chown-r Mysql.mysql/app/data/mydata
Service mysqld Start

Installing the database mysql5.6 binary version
installation package mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz

Groupadd-r MySQL
Useradd-g mysql-r-s/sbin/nologin MySQL
TAR-ZXVF mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz-c/usr/local

Cd/usr/local
LN-SV mysql-5.6.36-linux-glibc2.5-x86_64 MySQL

CD MySQL
Chown-r root.mysql./*

Mkdir/app/data/mydata-p
Chown-r Mysql.mysql/app/data/mydata
Mkdir/app/data/binlogs
Chown-r Mysql.mysql/app/data/binlogs

Cd/usr/local/mysql
scripts/mysql_install_db--datadir=/app/data/mydata--user=mysql

Copy the service script to the startup item
CP Support-files/mysql.server/etc/rc.d/init.d/mysqld
Chkconfig--add mysqld
Chkconfig--list mysqld

\CP my.cnf/etc/my.cnf

Vim/etc/my.cnf

[Client]
Port = 3306
Socket =/tmp/mysql.sock
Default-character-set = UTF8

[Mysqld]
Port = 3306
innodb_file_per_table = 1
Init-connect = ' SET NAMES utf8mb4 '
Character-set-server = Utf8mb4
Default_storage_engine = InnoDB
Skip-name-resolve
Skip-external-locking

DataDir =/app/data/mydata
Log-bin=/app/data/binlogs/master-bin
Binlog_format=row

Socket=/tmp/mysql.sock

Interactive_timeout = 28800
Wait_timeout = 28800

Sql_mode=no_engine_substitution,strict_trans_tables

[Mysqldump]
Quick
Max_allowed_packet = 16M

[Myisamchk]
Key_buffer_size = 8M
Sort_buffer_size = 8M
Read_buffer = 4M
Write_buffer = 4M

# Start Service
Service mysqld Start

Delete anonymous user, configure root password
mysql> Delete from user where host= ':: 1 ';
mysql> Delete from user where host= ' sdtw03 ';
mysql> Delete from user where host= ' localhost ' and user= ';

mysql> Update user Set Password=password (' Pass ') where user= ' root ';
mysql> flush Privileges;

Add Yun with all permissions
Mysql>grant all privileges on * * to [e-mail protected] identified by ' Pass ';
Mysql>flush privileges;

Backup scheme for MySQL database using the Percona-xtrabackup tool