Shulou(Shulou.com)06/01 Report--

Background

With the development of business, the company's business and scale continue to expand, the website has accumulated a lot of user information and data, for an Internet company, users and business data is the foundation. Once the company's data is confused or lost, it is tantamount to disaster for the Internet company. in order to prevent data loss caused by system errors or system failures, the company requires to strengthen the reliability of user data. it is required to comprehensively strengthen the backup of the data level, and be able to recover as soon as the failure occurs.

Data backup form

File backup:

The files can be stored in a package through the backup command of Linux, but they can be stored on local and remote servers, and then these files will be restored to the specified location when they are to be restored.

Database data backup:

In some industries that require high data reliability, such as banking, securities, telecommunications and so on, if accidental downtime or data loss occurs, the loss will be very heavy. For this reason, the database administrator should formulate a detailed database backup and disaster recovery strategy according to the specific business requirements, and simulate each failure.

The only way to ensure the high availability of data is to carry out rigorous testing in all possible situations. The backup of the database is a long-term process, and the recovery is only carried out after the accident, and the recovery can be regarded as the reverse process of the backup. The degree of recovery largely depends on the backup. In addition

Whether the steps taken by the database administrator during the recovery are correct or not also directly affects the final recovery results.

Data backup type

Divided by business: full backup, incremental backup and differential backup.

1. Full backup: backing up the data and data structure of the entire database

Advantages: intuitive and easy to understand

Disadvantages: 1. A large amount of backup data is duplicated, which takes up a lot of space and increases the cost.

two。 The amount of data backed up is large and takes a long time.

(Full Backup) the so-called full backup is to back up the data and data structure of the whole database. The advantage of this backup method is that it is intuitive and easy to understand. And when there is a disaster of data loss, as long as the backup files before the disaster are used, the lost data can be recovered.

However, it also has its shortcomings: first of all, because the system is fully backed up every day, there is a lot of repetition in the backup data. These duplicate data take up a lot of space, which means additional costs for users; second, because the amount of data that needs to be backed up is quite large, it takes a long time to back up. For those units with busy business and limited backup window time, it is undoubtedly unwise to choose this backup strategy.

2. Incremental backup (Incremental Backup): the data of each backup is only equivalent to the data that has been added and modified since the last backup.

Advantages: no repeatedly backed up data, saving space

Disadvantages: it is troublesome to restore data, and any problem with backup data will lead to data loss.

That is, the data backed up each time is only equivalent to the data added and modified since the last backup. The advantage of this backup is obvious: there is no duplicate backup data, which saves space and shortens the backup time. But its disadvantage is that when there is a disaster, it is more troublesome to recover data. For example, such as

If the system fails on Thursday morning and a large amount of data is lost, the system needs to be restored to the state it was on Wednesday night.

At this time, the administrator needs to first find the full backup data from Monday for system recovery, then find out Tuesday's data to restore Tuesday's data, and then find Wednesday's data to restore Wednesday's data. This is obviously much more troublesome than the first strategy. Besides, this kind of backup is reliable.

And bad sex. In this kind of backup, the relationship between the backup data is like a chain, one ring after another, and any problem with the backup data will lead to the disconnection of the whole chain.

3. Differential backup (Differential Backup): the data of each backup is equivalent to the newly added and modified data since the last full backup.

That is, the data backed up each time is relative to the new and modified data since the last full backup. The administrator first makes a full backup of the system on Monday, and then over the next few days, the administrator backs up all the data that is different from Monday (new or changed) to tape. For example, on Monday, the network administrator routinely makes a full backup of the system; on Tuesday, assuming that there is only one more asset list in the system, the administrator only needs to back up the asset list; on Wednesday, there is another product catalog in the system, so the administrator

Back up not only this catalogue, but also Tuesday's list of assets.

If there is an extra payroll in the system on Thursday, the content that needs to be backed up on Thursday is: payroll + product catalog + asset list. From this, it can be seen that the full backup takes the longest time, but the recovery time is the shortest and the operation is the most convenient. When the amount of data in the system is small, the full backup is the most reliable; differential backup can avoid the other two strategy defects, but different backup types can be combined.

Example of combined application of different backup types

Full backup and differential backup

Perform a full backup on Monday and a differential backup from Tuesday to Friday. If the data is corrupted on Friday, you only need to restore the full backup on Monday and the differential backup on Thursday. This strategy takes more time to back up data, but less time to restore data.

Full backup and incremental backup

Perform a full backup on Monday and an incremental backup from Tuesday to Friday. If the data is corrupted on Friday, you need to restore the normal backup on Monday and all incremental backups from Tuesday to Friday. This strategy takes less time to back up data, but more time to restore data.

Divided by way: can be divided into hot backup, warm backup, cold backup

Hot backup (Hot Backup) refers to a direct backup while the database is running and has no effect on the running database.

Cold backup (Cold Backup) refers to the backup when the database is stopped. This kind of backup is the simplest and generally only needs to copy the relevant database physical files.

Warm backup (Warm Backup) backup is also performed while the database is running, but it will affect the operation of the current database, such as adding a global read lock to ensure the consistency of the backup data. (when you back up a table in the database, lock the table first so that no one else can check and delete the data in the table, so that when you back up, the data in the table will not change, ensuring the consistency of the backup data.)

Physical backup: a backup made by copying data files directly (data files backed up by direct copies are in binary format)

Advantages: no additional tools, just copy, restore and copy backup files directly.

Disadvantages: related to storage engine, weak cross-platform ability

Logical backup: a backup made by "exporting" data from a database (exporting sql statements to a text larger than a file in binary format)

Advantages: can be processed by editor, easy to recover, can be recovered based on network, and help to avoid data corruption

Disadvantages: large backup files, slow backup, can not guarantee the accuracy of floating-point numbers, after the use of logical backup data recovery, it is necessary to manually rebuild the index, which consumes CPU resources

Backup flow chart

Introduction to Mysql Log

MySQL log:

Mainly include: error log, query log, slow query log, transaction log, binary log and so on.

Log is an important part of mysql database. The log file records the changes that occur during the operation of the mysql database; that is, it is used to record

Record the client connection status of the mysql database, the execution of SQL statements and error messages, etc. When the database is accidentally damaged, you can communicate with

Log to see the cause of the file error, and you can recover the data through the log file.

Mysql error log

In mysql databases, error logging is enabled by default. Also, the error log cannot be disabled. By default, the error log is stored in a data file in the mysql database. The name of the error log file is usually hostname.err. Where hostname represents the server hostname.

The error log information can be configured by itself, and the information recorded in the error log can be defined through logerror and log-warnings, where log-err defines whether the error log is enabled and where the error log is stored, and log-warnings defines whether warning information is also defined in the error log. By default, the error log records the following information: information during server startup and shutdown (not necessarily error messages, such as how mysql starts InnoDB's tablespace file, how to initialize its own storage engine, etc.), error messages while the server is running, information generated when the event scheduler runs an event, and information generated when starting the server process from the server.

Mysql-uroot-p

Select globle variables like'% log%'

Log_error can be modified through configuration files

Vim / etc/my.cnf / / as shown below: I changed the path to the error log to / var/log/mariadb/mariadb.err

Log-error=/var/log/mariadb/mariadb.err

Then restart the database service to connect to the database to view the global log, and the modification is successful

View the contents of the error log

Temporary modification:

In the Mysql error log, log_error can be defined directly as the file path or ON | OFF

Log_warings can only use 1 | 0 to define switch startup.

Permanent modification:

You can use log_error to change the location of the error log as follows:

[root@stu18 data] # vim / etc/my.cnf

[mysqld]

Log_error=DIR/ [filename]

Resolution: parameter DIR specifies the path to the error log. Parameter filename is the name of the error log. If this parameter is not specified, it defaults to the host name. Modify the configuration file and restart the mysql server to take effect.

Note: before mysql5.5.7: database administrators can delete error logs from a long time ago to ensure hard disk space on the mysql server. In the mysql database, you can use the mysqladmin command to open a new error log.

The syntax of the mysqladmin command is:

Mysqladmin-u root-pflush-logs can also be used to log in to the mysql database using the FLUSHLOGS statement to open a new error log.

Mysql query log

Query logs are turned off by default. Because the query log records all the operations of the user, it also contains information such as additions, deletions, queries, etc., in the environment of large concurrent operations, it will produce a large amount of information, resulting in unnecessary disk IO, which will affect the performance of mysql. If it is not for the purpose of debugging the database, it is recommended not to open the query log.

Mysql

Show global variables like'% log%'

Mysql slow query log

Slow query logs are used to record queries that have been executed for more than a specified time. Through the slow query log, we can find out which query statements have low execution efficiency (some query statements take a long time to execute, so we need to find and clear these query statements to optimize server performance) for optimization. It is strongly recommended to turn it on, which has little impact on server performance, but can record queries that have been executed for a long time on the mysql server. That can help us locate performance problems.

Start and set the slow query log:

1. Slow log can be enabled through the log-slow-queries option in the configuration file my.cnf.

The form is as follows:

Vim / etc/my.cnf

[mysqld]

Slow-query-log = ON

Slow-query-log-file = / var/log/mariadb/slow.log

Long-query-time = 0.01,

Where the DIR parameter specifies the storage path of the slow log; the filename parameter specifies the file name of the log, and the completion name of the generated log file is filename-slow.log. If you do not specify a storage path, the slow log is stored in the data file of the mysql database by default. If you do not specify a file name, the default file name is hostname-slow.log.

2. Define directly by logging in to the mysql server.

The way is as follows:

First, you must have global permissions; then execute mysql > set global slow_query_log=1; (temporarily effective, if the sql statement takes more than 1 second to execute, it will be called a slow query log)

How much time is exceeded by default is called slow query log?

This time value is generally set through the long_query_time option, which is measured in seconds and can be accurate to microseconds. If the query time exceeds this time value (the default is 10 seconds), the query statement will be recorded in the slow query log. View the default time value of the server as follows:

Note: the slow query time not only means that the statement itself is executed for more than 10 seconds, but also the query execution time or other reasons caused by the requisition of other resources are recorded in the slow query. So the length of the slow check represents all the time between the start of the query and the end of the query for any possible reason.

View the contents of slow query log

Mysql transaction log

Transaction: a transaction is a collection of operations that need to be committed after a series of operations are committed before they can be called transactions. (either all operations are performed or none are performed)

Transaction logs (InnoDB-specific logs) can help improve the efficiency of transactions. Using the transaction log, the storage engine only needs to modify the memory copy of the table when modifying the data, and then record the modification behavior in the transaction log on the hard disk, instead of persisting the modified data to the disk every time. The transaction log is appended, so the operation of writing the log is the sequential IPUP O in a small area of the disk, unlike the random IUnip O, which needs to move the head in multiple places on the disk, so using the transaction log method is relatively faster. After the transaction log is persistent, the modified data in memory can be slowly brushed back to disk in the background. At present, most storage engines are implemented in this way, which we usually call pre-write logs, and it is necessary to write to disk twice to modify data.

Mysql's transaction-based operation will directly change the data in the corresponding memory, and after the change, check it, and all have taken effect, but there is no net disk to write. He first writes to the transaction log, and then periodically brushes it to the disk (the transaction log is appended and written to disk, which is written in order, which greatly improves the efficiency of transactions)

If the modification of the data has been recorded in the transaction log and persisted, but the data itself has not been written back to disk, the system crashes and the storage engine can automatically recover the modified data when it is restarted. The recovery method it has depends on the storage engine.

The innodb engine is an engine that supports transactions

View the definition of the transaction log

Show global variables like'% log%'

Mysql binary log

Binary log, also known as change log, is mainly used to record modified data or mysql statements that may cause data changes, and records the time when the statement occurred, the length of execution, the data of the operation, and so on. So binary logs can be used to query what changes have been made in the mysql database. The upper limit of general size and volume is 1G.

Show global variables like'% log%'

Sql_log_bin = {ON | OFF} # is used to control whether the binary log function is turned on or off at the session level (connect mysql to execute an operation statement, which is session level, for example, import mysql directly from a file, which is not session level). The default is ON, which means that logging is enabled. The user can modify the value of this variable at the session level, but it must have SUPER permission.

Binlog_cache_size = 32768 # the default value of 32768 Binlog Cache is used in environments where binary logging (binlog) recording is turned on. It is a memory area designed by MySQL to improve the recording efficiency of binlog and to temporarily cache binlog data for a short period of time. In general, if there are no big transactions in our database and writes are not particularly frequent, 2MB~4MB is an appropriate choice. However, if our database has a large number of transactions and a large number of writes, we can increase binlog_cache_size appropriately. At the same time, we can use binlog_cache_use and binlog_cache_disk_use to analyze whether the set binlog_cache_size is enough and whether a large number of binlog_cache are cached using temporary files (binlog_cache_disk_use) due to insufficient memory.

Log_bin = mysql-bin # specifies the location of the binlog, which is under the data directory by default.

Binlog-format= {ROW | STATEMENT | MIXED} # specifies the type of binary log, which is recommended as MIXED. If the binary log is formatted but not enabled, a warning log message is generated when MySQL starts and is recorded in the error log.

Row: do not record the context of each sql statement, but only record that each piece of data has been modified

Statement: every sql statement that modifies the data is recorded

Mixed: indicates a mixture of the first two

Sync_binlog = 10 # sets how often the binary log is synchronized to the disk file, 0 means out of sync, and any positive value indicates that the binary is synchronized after every number of writes. When the value of autocommit is 1, the execution of each statement will cause binary log synchronization, otherwise, the commit of each transaction will cause binary log synchronization.

Binary logs can be enabled by editing the log-bin option in my.cnf, as follows:

Where the DIR parameter specifies the storage path of the binary file, and the filename parameter specifies the file name of the two-level file, which is in the form of filename.number,number 000001, 000002, and so on. Each time you restart the mysql service or run mysql > flush logs;, a new binary log file is generated, and the number of these log files is incremented. In addition to generating the above file, a file named filename.index is also generated. The list of all binary log files stored in this file is also known as the index of binary files.

Each time the database service is restarted, a binary log file is generated

View the binary log:

Binary logs are defined in binary format; using this format can store more information and make writing to binary logs more efficient. However, you cannot directly use the View command to open and view the binary log.

Small extension: the recording location of the binary log, usually the location of the last event execution end time, and each log file has its own metadata, so for the current version of mysql, the binary start position is usually 107.

Connect to mysql and enter several sql statements that can modify the data to generate a binary log

View specified binary log information

View the binary log from the command line:

Since you cannot directly open and view the binary log using methods such as cat, you must use the mysqlbinlog command. However, it is recommended that you do not use this to open binary log files in use when you are performing mysql read and write operations; if you do not want to open flushlogs. How the mysqlbinlog command is used:

Export information for this database:

[root@stu18 data] # mysqlbinlog mysql-bin.000017 > / tmp/a.sql

Import information for this database:

[root@stu18 data] # mysql

< a.sql 删除二进制日志信息： 二进制日志会记录大量的信息（其中包含一些无用的信息）。如果很长时间不清理二进制日志，将会浪费很多的磁盘空间。但是，删除之后可能导致数据库崩溃时无法进行恢复，所以若要删除二进制日志首先将其和数据库备份一份，其中也只能删除备份前的二进制日志，新产生的日志信息不可删(可以做即时点还原)。也不可在关闭mysql服务器之后直接删除因为这样可能会给数据库带来错误的。若非要删除二进制日志需要做如下操作：导出备份数据库和二进制日志文件进行压缩归档存储。删除二进制文件的方法如下： 使用RESET MASTER语句可以删除所有的二进制日志。该语句的形式如下： mysql>

Reset master

Query OK, 0 rowsaffected (0.17 sec)

Mysql > show binary logs

Mysql backup tool

Mysqldump: logical backup tool, suitable for all storage engines, can be used for warm backup, can achieve full backup, partial backup; for InnoDB storage engine

Hot standby is supported

Cp, tar and other file system tools: physical backup tool, suitable for all storage engines; for cold backup, full backup and partial backup

Snapshot of lvm2: almost hot standby; physical backup with the help of file system tools

Mysqlhotcopy: almost cold standby; only suitable for MyISAM storage engine

Mysql backup solution ① mysqldump+binlog: (recommended)

Full backup, incremental backup by backing up binary logs

② xtrabackup:

For InnoDB: hot backup, full backup and incremental backup are supported

For MyISAM: warm backup, only full backup is supported

③ lvm2 Snapshot + binlog:

Almost hot backup, physical backup

Syntax format of the mysqldump+binlog command

Mysqldump [OPTIONS] database [tables]: back up a single library, or one or more tables specified by the library

Mysqldump [OPTIONS]-- databases [OPTIONS] DB1 [DB2DB3...]: back up one or more libraries

Mysqldump [OPTIONS]-- all-databases [OPTIONS]: back up all libraries

Other options

-x,-- lock-all-tables: lock all tables

-l,-- lock-tables: lock the backed up table

-- single-transaction: start a large single transaction to implement backup

-C,-- compress: compressed transmission

-E,-- events: event scheduler that backs up the specified library

-R,-- routines: backup stored procedures and stored functions

-- triggers: backup trigger

-- master-data= {0 | 1 | 2}

0: no record

1: record the CHANGE MASTER TO statement; this statement is not commented

2: record as comment statement

-Fmam, Flemish, flush. logs: execute the flush logs command after locking the table

Mysqldump+binlog backup and recovery 1. Modify the mysql configuration file to open the binary log

Vim / etc/my.cnf

Log-bin = master-log

Then restart mysql

Systemctl restart mariadb

Check whether a binary log is generated after entering mysql

2. Prepare the backup directory

3. Prepare to back up databases and tables

Mysql

Create database test

Use magedu

Create table m (id int,name char (20))

4. Make a full backup

Mysqldump-- all-databases-- lock-all-tables-- flush-log-- master-data=2 > / backup/ `date +% Flying% T`-all.sql

5. Insert data into the table

Mysql

Use magedu

Show master status

Insert into M26 (id,name) values (1Magneto fuming'), (2recorder Zhangmeng')

6. Perform incremental backups and backup binary logs

Mysqlbinlog-- start-position=245-- stop-position=479 / var/lib/mysql/master-log.000002 > / backup/binlog/binlog- `date +% Flying% T`.sql

Judge the start and stop of position

Show master logs

Show binlog events in 'master-log.000002'

End to include commit submission.

7. Continue to insert data, delete the database without backup, and simulate misoperation

8. Data recovery. At last, we deleted the database without backup, so we first need to protect the last binary log. If these binaries are lost, we really can't recover them. Check the position value mysqlbinlog / var/lib/mysql/master-log.000002 before the delete operation.

9. Back up the binary log of the last operation

Mysqlbinlog-- start-position=467-- stop-position=677 / var/lib/mysql/master-log.000002 > / backup/binlog/binlog- `date +% Flying% T`.sql

Ls / backup/binlog/

10. Import all previous backups

Mysql

< /backup/2017-12-07_20\:20\:04-all.sql 导入完整备份 mysql < /backup/binlog/binlog-2017-12-07_20\:45\:17.sql 导入增量备份 mysql < /backup/binlog/binlog-2017-12-07_21\:05\:42.sql 导入删掉数据库之前的增量备份 11.查看数据库及数据 xtrabackup Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，是一款开源能够对innodb和xtradb数据库进行热备的工具。 特点： (1)备份过程快速、可靠 (2)备份过程不会打断正在执行的事务 (3)能够基于压缩等功能节约磁盘空间和流量 (4)自动实现备份检验 (5)还原速度快 实验步骤：（1）xtrabackup的安装 yum install percona-xtrabackup -y （2）完全备份 innobackupex --user=root /backup （3）添加数据 mysql -uroot create database magedu; use magedu create table m26 (id int,name char(20)); insert into m26 values (007,'fuming'),(008,'zhangmeng') （4）增量备份 innobackupex --incremental /backup/ --incremental-basedir=/backup/2017-11-16_16-53-4 （5）数据恢复准备 1.执行操作（完全备份）： innobackupex --apply-log --redo-only BASE-DIR（BASE-DIR是完全备份的目录） 例如：innobackupex --apply-log --redo-only BASE-DIR --incrementaldir=/backup/2017-11-16_17-17-52/ 2.接着执行（增量）： innobackupex --apply-log --redo-only BASE-DIR --incrementaldir=INCREMENTAL-DIR-1（INCREMENTAL-DIR-1是增量备份的目录） 例如：innobackupex --apply-log --redo-only /backup/2017-11-16_16-53-43 --incrementaldir=/backup/2017-11-16_17-17-52/ （6）恢复阶段，还原数据 mv /var/lib/mysql /var/lib/mysql.bak 模拟删除数据库 mkdir /var/lib/mysql cd /var/lib/mysql innobackupex --copy-back /backup/2017-11-16_16-53-43 恢复完全备份 lvm2快照+binlog 做实验之前我们先回顾一下lvm2-snapshot的知识 LVM快照简单来说就是将所快照源分区一个时间点所有文件的元数据进行保存，如果源文件没有改变，那么访问快照卷的相应文件则直接指向源分区的源文件，如果源文件发生改变，则快照卷中与之对应的文件不会发生改变。快照卷主要用于辅助备份文件。 实验步骤： 1、添加硬盘，并划分磁盘类型为lvm类型 echo '- - -' >

/ sys/class/scsi_host/host2/scan

two。 Zoning

T 8e is lvm.

Partx-a / dev/sdb enables the kernel to recognize new disks

3.pvcreate / dev/sdb1 add physical Volume

4.vgcreate myvg / dev/sdb1 add Volume Group

5.lvcreate-n mydata-L 5G myvg add logical Volume

6. Mkfs.ext4 / dev/mapper/myvg-mydata format logical volumes

7. Mount mount / dev/mapper/myvg-mydata / lvm_data use

8. Modify the Mysql configuration to make the data file datadir=/lvm_data on the logical volume.

9. Service mysqld restart starts the Mysql service

10. Create a database and perform operations

11. Mysql > FLUSH TABLES WITH READ LOCK; # lock the table

12. Lvcreate-L 1G-n mydata-snap-p r-s / dev/mapper/myvgmydata

# create a snapshot volume Logical volume "mydata-snap" created.

13. Mysql > UNLOCK TABLES; # unlock all tables

14. Mount / dev/myvg/mydata-snap / lvm_snap/ # Mount snap

15. Tar cvf / tmp/mysqlback.tar. / * # package physical backup

16. Umount / lvm_snap/ # Uninstall snap

17. Lvremove myvg mydata-snap # Delete snap

18. Delete mysql data rm-rf / lvm_data/*

19. Decompress and restore deleted data tar xvf / tmp/mysqlback.tar. /

20. Verify that the database data is restored correctly

Summary

Backup method

Backup speed

Recovery speed

Convenience

Features are generally used for

Mysqldump

Slow

Slow

In general, differences in storage engines can be ignored

General small and medium-sized data backup

Lvm2 Snapshot

Come on!

Come on!

Support almost hot standby with high speed

General small and medium-sized data backup

Xtrabackup

Faster

Faster

The realization of innodb hot backup requires strong storage engine.

A large-scale contribution

Cp

Come on!

Come on!

General, low flexibility

Very weak small amount of data backup

All right, that's all for today. I'll see you next time.

The above practical tutorial of Mysql to achieve enterprise log management, backup and recovery is all the content that the editor has shared with you. I hope I can give you a reference and support it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.