Terms and definitions

1. Introduction to migration tools

2. Architecture diagram

3. Pre inspection

3.1. Check host physical memory

3.2. Check disk space

3.3. Check the operating system version

3.4. Check current host IO

3.5. Check the current MySQL server concurrency

3.6. Check the current MySQL server thread

3.7. Get the size of each database

3.8. Get the number of tables of different storage engines in each library

3.9. Storage preparation

3.10. xtrabackup installation

4. Database physical backup

4.1. Parameter description of xtrabackup

4.2. xtrabackup full backup

4.2.1. Permission check

4.2.2. Create backup directory

4.2.3. Full backup

4.2.4. prepare for full backup

4.2.5. Verify whether the backup is successful

5. Create slave library for physical backup

5.1. Mount backup NAS disk

5.2. Create instance

5.3. Backup and recovery

5.4. Master slave construction

5.5. Data inspection

6. Zabbix application cutting

6.1. Modify master-slave Zabbix service configuration

6.2. Stop the keepalived service on the standby zabbix machine

6.3. Restart the main Zabbix service

6.4. Start the keepalived service on the standby zabbix machine

6.5. Web interface reconfiguration

6.6. Application verification

--

Terms and definitions

The following terms, definitions and abbreviations apply to this document.

1 Introduction to migration tools

Xtrabackup is a data backup tool for InnoDB. It supports online hot backup (data reading and writing will not be affected during backup). It is a good substitute for the commercial backup tool InnoDB Hotbackup. Xtrabackup has two main tools: xtrabackup and innobackupex. Xtrabackup can only back up InnoDB and XtraDB data tables, and only back up data files (. ibd), not data table structure files (. frm) and MyISAM data tables. Therefore, when using xtrabackup for recovery, you must have corresponding table structure files (. frm); innobackupex-1.3.1 encapsulates xtrabackup, which is a script encapsulation. Therefore, InnoDB and MyISAM can be backed up at the same time, but a read lock needs to be added when processing MyISAM.

2 architecture diagram

framework

3 pre inspection

In full backup, first determine the data object to be backed up, the size of the backup database and the space where the backup files are stored, and check the basic situation of the host.

3.1 check host physical memory

# free -g
total       used       free     shared    buffers     cached
Mem:           126        103         96          0          0         22
-/+ buffers/cache:          6        119
Swap:          124          0        124

3.2 checking disk space

# df  -h
 file system            Capacity used available used%% Mount point
/dev/sda1             769G  229G  502G  32% /
tmpfs                  64G  260K   64G   1% /dev/shm
/dev/sda3             940G  276G  617G  31% /app
/dev/mapper/Mysqlvg-Mysqllv
                      493G  349G  119G  75% /data
192.168.203.41:/mnt/zxdfs/CM_South15/root/cccloud_tenant_id10001154
                      1.0T     0  1.0T   0% /mysql

3.3 check the operating system version

#uname -a
Linux Mysqlmaster2 2.6.32-131.0.15.el6.x86_64 #1 SMP Tue May 10 15:42:40 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux

3.4 check current host IO

# iostat -cdx 2

Focus on the parameter:% util indicates that the disk is busy. Generally, if the value exceeds 80%, it indicates that the disk may be busy.

3.5 check the current MySQL server concurrency

# mysqladmin extended -i1 -uroot -p  -S /tmp/mysql.sock |grep Threads_running
 

3.6 check current MySQL server threads

mysql> SELECT * FROM performance_schema.threads WHERE processlist_command<>'sleep' AND processlist_id IS NOT NULL\G
*************************** 1. row ***************************
          THREAD_ID: 27
               NAME: thread/sql/compress_gtid_table
               TYPE: FOREGROUND
     PROCESSLIST_ID: 1
   PROCESSLIST_USER: NULL
   PROCESSLIST_HOST: NULL
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Daemon
   PROCESSLIST_TIME: 1815110
  PROCESSLIST_STATE: Suspending
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: NULL
       THREAD_OS_ID: 16362
*************************** 2. row ***************************
          THREAD_ID: 101886
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 101860
   PROCESSLIST_USER: repl
   PROCESSLIST_HOST: 10.20.234.157
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Binlog Dump
   PROCESSLIST_TIME: 177564
  PROCESSLIST_STATE: Master has sent all binlog to slave; waiting for more updates
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: TCP/IP
       THREAD_OS_ID: 24901
*************************** 3. row ***************************
          THREAD_ID: 123682
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 123656
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: Sending data
   PROCESSLIST_INFO: SELECT * FROM performance_schema.threads WHERE processlist_command<>'sleep' AND processlist_id IS NOT NULL
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 14484
*************************** 4. row ***************************
          THREAD_ID: 94022
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 93996
   PROCESSLIST_USER: repl
   PROCESSLIST_HOST: 10.20.234.158
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Binlog Dump
   PROCESSLIST_TIME: 259239
  PROCESSLIST_STATE: Master has sent all binlog to slave; waiting for more updates
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: TCP/IP
       THREAD_OS_ID: 31926
4 rows in set (0.00 sec)

3.7 get the size of each database

mysql> SELECT TABLE_SCHEMA,CONCAT(ROUND(SUM(DATA_LENGTH)/1024/1024),'MB') AS DATA_LENGTH,CONCAT(ROUND(SUM(INDEX_LENGTH)/1024/1024),'MB') AS INDEX_LENGTH,CONCAT(ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024),'MB') AS TOTAL_SIZE FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN('information_schema','performance_schema','mysql','test') GROUP BY TABLE_SCHEMA ORDER BY 4 DESC;

3.8 get the number of tables of different storage engines in each library

mysql> SELECT TABLE_SCHEMA,ENGINE,COUNT(1) AS C_TABLES FROM information_schema.TABLES WHERE TABLE_SCHEMA 
NOT IN('information_schema','performance_schema','mysql','sys','test') GROUP BY TABLE_SCHEMA, ENGINE ORDER BY 3 DESC;;

3.9 storage preparation

Allocate a NAS disk for backup

3.10 xtrabackup installation

#Configure yum source
# yum -y install  perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL libev
#Upload the media package to the / tmp directory
# cd /tmp
# rpm -ivh percona-xtrabackup-24-2.4.10-1.el7.x86_64.rpm

4. Database physical backup

4.1 parameter description of xtrabackup

What is introduced here is xtrabackup Some parameters of are for reference only

--defaults-file
 appoint my.cnf Location of parameter file[Must be specified in this configuration file datadir]，Change parameters should be specified during full backup, add backup and recovery, otherwise they may not be found datadir，Note that this parameter can only be placed in innobackupex The position of the first parameter after the command.
 
--apply-log
 with xtrabackup of--prepare parameter,Normally,After the backup is completed, the data cannot be used for recovery operations because the backed up data may contain uncommitted transactions or transactions that have been committed but have not been synchronized to the data file. Therefore, the data file still handles the inconsistent state at this time.--apply-log The function of is to roll back uncommitted transactions and synchronize committed transactions to the data file to make the data file in a consistent state. For a full backup recovery without incremental backup, perform full backup before recovery prepare Add this parameter when.

--apply-log  --redo-only
 Force backup of logs only redo,skip rollback,This is necessary for incremental backups. For full backup+For incremental recovery, before recovery, perform full backup and incremental recovery respectively prepare,Then it's all ready prepare And per increment prepare This parameter should be added--apply-log  --redo-only，
Don't just add--apply-log And forget--redo-only. And for the last increment prepare, --redo-only Yes or no.


--copy-back
 Copy the backup data file to mysql Server datadir 
  
--remote-host=HOSTNAME
 adopt ssh Store backup data on the process server
  
--stream=[tar]
Backup file output format, This document can be found in XtarBackup binary Obtained from the file. Parameters in use stream=tar When backing up, your xtrabackup_logfile May be temporarily placed/tmp Directory,If you write a large amount concurrently during backup, xtrabackup_logfile It could be big(5G+),It'll probably fill you up/tmp catalogue,You can use parameters--tmpdir Specify a directory to solve this problem.
  
--tmpdir=DIRECTORY
 When specified--remote-host or --stream Time, Directory for temporary storage of transaction logs, Default use MySQL The temporary directory specified in the configuration file tmpdir 
  
--use-memory=*
This parameter is in prepare When using,control prepare Time innodb Memory used by the instance
 
--databases=LIST
 List the that need to be backed up databases,If this parameter is not specified,All include MyISAM and InnoDB Tabular database Will be backed up

 
--slave-info
 Backup slave Library, add--slave-info An additional one will be generated under the backup directory xtrabackup_slave_info file, The main log file and the offset are saved here, The contents of the file are similar to: CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=0 

  
--incremental-basedir 
Specifies the directory of the previous full backup. This parameter is used in incremental backup


--incremental
 A new directory will be generated to store incremental backup data. This parameter is used during incremental backup


--incremental-dir 
Specify the combination of incremental backup and full database backup to create a new full backup directory. This parameter is set before restoring the backup file prepare When operating, it should be consistent with--apply-log --redo-only Parameters are used together

--socket=SOCKET
 appoint mysql.sock Location so that the backup process can log in mysql, During full backup and additional backup, you should specify the change parameter, otherwise the local connection may not be available mysql

4.2 xtrabackup full backup

4.2.1 authority check

During the backup process, the system user needs to have read-write permission to the system file directory where the backup data is stored. When backing up the database, it is recommended to have the following permission capabilities:

1. Reload, lock tables (unless -- no lock parameter is specified) to enable flush tables with read lock;
2. Replication client has the ability to obtain binary file backup;
3. Create tablespace has the ability to restore the entire tablespace and import tables;
4. Super is used to start and close the environment of copying threads from the server.

4.2.2 create backup directory

# mkdir -p /backup/mysql_backup
# mount 192.168.0.13:/backup /backup/mysql_backup

4.2.3 full backup

1. Execute full command

# Innobackupex -- defaults file = 'path to mysql parameter file'
--user=root   --password=root    --socket='SOCKET Path to file'    /backup/mysql_backup/
innobackupex Key parameters:--defaults-file ,--socket(See document 3 for parameter description.1)

1. View the output after the above all standby command

Xtrabackup: The latest check point (for incremental): '470308686'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (470308686)

//During the backup process, the log thread will be terminated and the previous checkpoint will be used as the log backup time point.

4.2.4 prepare for full backup

All ready

# Innobackupex -- defaults file = 'path to mysql parameter file' -- user = root -- password = root -- SOCKET = 'path to SOCKET file'
--apply-log    /backup/mysql_backup/

innobackupex Key parameters:--defaults-file ,--socket ,--apply-log(See document 4 for parameter description.1)

It is worth noting here that the current full database backup only backs up the transactions with checkpoint '470308686' sequence number mentioned above and the data synchronized to the disk. The transactions that have been executed but have not been committed are still stored in memory (innodb buffer), resulting in the inconsistency of the current data file.

Assuming that the full backup is to be recovered, the uncommitted transactions should be rolled back to make the data files in a consistent state. Therefore, it is very important to remember to perform a prepare operation (- - apply log) on the full backup after performing the full backup. When executing -- apply log, you must specify the previous backup directory (because you need to obtain the correct xtrabackup_checkpoints file).

By default, the - apply log parameter only calls the system's 100M memory. If the current innodb buffer pool is relatively large, the data synchronization time in memory may be long. You can speed up the backup by defining the memory size, such as the parameter -- use memory = 4G.

4.2.5 verify whether the backup is successful

----When you see the following information in the above operation, it indicates that the full backup has been successful
151205 12:22:42  innobackupex: completed OK!

innobackupex: completed OK appears in both operations in 3.2.3 (full backup) and 3.2.4 (full backup prepare) above! String indicates that the full backup is successful.

5 physical backup creation slave Library

5.1 mount backup NAS disk

The physical backup data attached to the master library is transferred to the new slave library host

# mount 192.168.0.13:/backup /backup/mysql_backup

5.2 create instance

Use the instance creation script to create an empty mysql instance for physical backup recovery. After creating an empty instance, stop the instance and delete some files under the instance.

(New library (from host)
1. new mysql The empty instance information is as follows:
    base_dir:  /data/mysql/db_ngoc
 Profile:/data/mysql/db_ngoc/conf/ngoc.cnf
  
2. Stop the instance
   /data/mysql/db_ngoc/bin/shutdown.sh
   
3. Delete some files under this instance (be sure to confirm clearly before deleting) IP And instance paths)
   cd /data/mysql/db_ngoc
   rm -rf ./data/*
   rm -rf ./ulog/*
   rm -rf ./rlog/*

5.3 backup and recovery

Restore the physical backup data to the new MYSQL empty instance

(New library (from host)
1. Restore backup files to empty instances
   innobackupex --defaults-file=/data/mysql/db_ngoc/conf/ngoc.cnf --copy-back /backup/mysql_backup
2. Modify data directory permissions
   chown mysql. -R /data/mysql

5.4 master slave setup

According to the "gtid and position" information of the backup time recorded in the "xtrabackup_info" file in the physical backup data, the master-slave data replication is built to synchronize the data from time to time

(New (execute from Library)
1. start-up mysql Instance and log in
   /data/mysql/db_ngoc/bin/startup.sh
   /data/mysql/db_ngoc/bin/login.sh

2. empty master/slave Relevant information
   reset master;
   reset slave all;
   show master status;
   
3. Set the starting point of data synchronization GTID(come from: xtrabackup_info file)
   set global  gtid_purged ="7d58ee1a-93a0-11e7-8ff5-f44c7f785650:1-3,d2cf8b03-939f-11e7-99db-407d0f46034d:1-14193879"
   
4. Use command to build master-slave
   CHANGE MASTER TO
     MASTER_HOST='',
     MASTER_USER='',
     MASTER_PASSWORD='',
     MASTER_PORT=,
     MASTER_AUTO_POSITION=1;
  
5. Start master-slave replication and view the status
   start slave;
   show slave status\G

5.5 data inspection

After the master-slave setup is completed, verify the number of master-slave data

1. The master and slave execute the following commands respectively
   mysqlshow -u zabbix -pzabbix -S /data/mysql/db_ngoc/mysql.sock --count zabbix
 Main library execution result

2. Execution results from database

6 Zabbix application cutting

6.1 modify master-slave Zabbix service configuration

(new Zabbix (executed on the primary and standby machines)
# cp /zabbix/server/etc/zabbix_server.conf /zabbix/server/etc/zabbix_server.conf.bak
# Sed - I "s / dbhost =. * / < new slave address > / g" / ZABBIX / server / etc / ZABBIX_ server.conf

6.2 stop the reserved service on the standby zabbix machine

# systemctl stop keepalived

6.3 restart the main Zabbix service

(main zabbix On board execution)
# systemctl restart zabbix-server

6.4 start the keepalived service on the standby zabbix machine

(prepare zabbix On board execution)
# systemctl stop keepalived

6.5 Web interface reconfiguration

Browser access: http://<zabbix ip>/setup.php

6.6 application verification

Browser access: http://<zabbix ip>/zabbix.php