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.
terms | explain |
---|---|
zabbix | Open source monitoring software |
keepalived | Highly available software |
xtrabackup | percona open source hot standby tool |
binlog | mysql records the binary log of all modified data |
mysqlbinlog | Binary file parsing tool provided by mysql |
InnoDB | mysql is another storage engine that supports row locking and transaction security |
MyISAM | A storage engine in mysql does not support row locks and transactions |
.frm | The table metadata file stores the definition information of the table structure |
.ibd | InnoDB multi table space (exclusive) storage mode, one data file for each table |
ibdata | InnoDB shared storage mode. All tables share one or more data files |
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:
- Reload, lock tables (unless -- no lock parameter is specified) to enable flush tables with read lock;
- Replication client has the ability to obtain binary file backup;
- Create tablespace has the ability to restore the entire tablespace and import tables;
- 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
- 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)
- 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