Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "MySQL how to backup and restore files". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

MySQL Backup and Recovery

Database is divided into logical backup\physical backup; physical backup is divided into cold backup and hot backup

A. Copy data files directly to a safe place for storage

B. Use MYSQLHOSTCOPY to backup data

C. Backup data using MYSQLDUMP

D. Use MYSQL synchronous replication to realize real-time data synchronous backup

1) Logical backup

There are two main types of logical backups:

An insert statement that generates data as a complete reproduction of the data in the current database

l One is to record the data in the text after dividing the data of the database table by a specific delimiter through logical backup software.

For the first insert generation we can use mysql's own tool mysqldump directly. The downside of this approach is that it can lead to inconsistent or incomplete data.

Solution: One is to add write lock to the database system to provide only database query services; the second is for storage engines that support transactions, INNODB BDB can achieve consistency and integrity of backup data by controlling the entire backup process in a transaction; and it can not affect the normal operation of the database.

How to ensure consistency of data backup?

Data consistency can be achieved in two ways:

First, extract all data at the same time

For transaction-supported storage engines, such as Innodb or BDB, you can control the entire backup process in the same transaction by using the "--single-transaction" option.

For example:

# mysqldump --single-transaction test > test_backup.sql

Second, the data in the database is in a static state

By locking table parameters

--lock-tables locks one database table at a time, this parameter is true by default (see backup content example above);

--lock-all-tables Lock all tables at once, applicable to cases where dump tables are in different databases

mysql fields terminated by ','

-> optionally enclosed by '"'

-> lines terminated by '\n' --Default

-> from tb1 limit 50;

The recovery method is to load data infile and mysqlimport commands.

This process is quite complex and requires real-time recovery testing to ensure backup data is available.

2) Physical backup

Database physical backup, the main object is the database physical data files, log files and configuration files. What are the physical data files?

One is the log file 6 categories: error log, binary Log, update log (replaced by bin log after 5.0), query log, slow query log, innodb redo log.

The second is the data file: for myisam, the.frm table structure information, the.myd data information, and the index information of the.myi data. For Innodb,.ibd files (private tablespaces) and.ibdata (shared tablespaces) files.

Three is replication file: Master.info is stored in the slave data directory, storing the slave and master related information, relay log and relay log index mainly store the binary log information read by the I/O process from the Master, and then the SQL thread on the slave side reads the parsed log information from the binary log and converts it into query statements that the master can execute. index is the path to binarylog, i.e. the directory file.

The fourth is the system file: for example, my.cnf, pid file is a process file in mysqld application program to store its own process id, and socket file, which only exists under linux, can be directly connected to mysql without tcp/ip network protocol.

l If you are doing cold backup, copy all data files and log files directly to the place where the backup set is stored.

l Hot standby methods have different schemes for a few databases

For MYISAM storage engines, this is done by locking database tables to prevent writes, copying physical files directly, or by mysql's special mysqlhotcopy program.

flush tables with read lock

cp -R test /tmp/backup/test

unlock tables;

For innodb database engine, there is a commercial software ibbackup, online physical backup function. There is also an open source tool xtrabackup;

If you backup the INNODB data file during backup, it locks the entire library and starts copying non-transaction engine data such as MYISAM and.frm; so if you have more MYISAM tables, the lock will last for a long time. If it is running on the main library, be careful.

Also, incremental backups can only be done via xtrabackup, which actually backs up innobd logs.

4 xtrabackup/ibbackup

xtrabackup --backup --datadir=/var/lib/mysql/ --target-

dir=/data/backups/mysql/

xtrabackup --backup --defaults-file=/etc/my.cnf --target-

dir=/data/backups/mysql/

Physical backup is the backup of data files, the image point is cp data files, but the real backup is naturally not cp so simple.

(1) Using the xtrabackup tool

Is an open source tool for backing up MySQL databases.

Main features:

. Online hot backup. You can backup innodb and myisam. Innovb mainly applies the recovery principle. Myisam copies files directly.

. Stream backup is supported. Backup to disk, tape and remote host. - stream=tar ./ | ssh user@remotehost cat ">" /backup/dir/

. Support incremental backups. You can leverage lsn and base backup directories for incremental backups.

. Support recording master log and master position information on slave.

. Support multiple processes at the same time hot backup, xtrabackup stability or good.

（2）LVM

Features: hot standby, support for all local disk-based storage engines, fast backup, low overhead, easy to maintain integrity, fast recovery, etc.

（3）cp + tar

Use the method of directly copying database files for packaging backup. It is necessary to pay attention to the execution steps: locking table, backup and table solution.

Recovery is also very simple, directly copied to the previous database file storage directory.

Note: For Innodb engine tables, you also need to backup log files, i.e. ib_logfile* files. Because when Innodb tables are corrupted, you can rely on these log files to recover.

（4）mysqlhotcopy

Mysql hotcopy is a perl program that locks tables, flushes tables, and cp or scp to quickly back up databases.

It is the fastest way to back up a database or individual tables, but it can only run on the machine where the database files (including datasheet files, data files, index files) reside.

mysqlhotcopy can only be used to backup MyISAM.

(5) Use mysql master-slave copy

Mysql replication refers to the transfer of DDL and DML operations from the master database to the slave server through bin-logs, and then re-execute these logs on the slave server, so that the slave server and the master server maintain data synchronization.

3) MySQL backup method

1.mysqldump

Inefficient, backups and restores are slow, and any data inserts and updates are suspended

2.mysqlhotcopy

mysqlhotcopy is a backup specifically for myisam tables, during which any data inserts and updates are suspended.

3. Prepare a slave server for backup (master-slave mode)

4. xtrabackup is an open source project of percona, which can hot backup innodb ,XtraDB,MyISAM(lock table)

Xtrabackup has two main tools: xtrabackup and innobackup.

xtrabackup can only backup InnoDB and XtraDB tables, but not MyISAM tables

innobackup-1.5.1 encapsulates xtrabackup, which is a script encapsulation, so it can backup innodb and myisam at the same time, but it needs to add a read lock when processing myisam.

? Xtra backup principle

Memorize the LSN number at the beginning, and then start copying the file while running a background process that monitors the redo log and copies the changes down to xtrabackup_logfile.

innobackupex can back up myisam tables and frm files. When xtrabackup is complete, flush tables with read lock to avoid data changes, and then flush all myisam tables to disk. When the copy is complete, release the lock.

4） mysqlbinlog

mysqlbinlog is also a recovery tool that processes binary files based on point in time

? Backup: direct copy

? Reduction:

ü Time point restore: mysqlbinlog --stop-date="2005-04-20 9:59:59"

/var/log/mysql/bin.123456 | mysql -u root -pmypwd

mysqlbinlog --start-date="2005-04-20 10:01:00"

/var/log/mysql/bin.123456| mysql -u root -pmypwd

? Log Point Restore:

mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \

| mysql -u root -pmypwd

mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 \

| mysql -u root -pmypwd \

LVM stands for Logical Volume Manager, a mechanism for managing disk partitions in Linux environments.

LSN definition:

5) Log serial number

Log Sequence Number (LSN) identifies where a particular log file is recorded in the log file.

LSN from DB2? Many components in the product are used to maintain database consistency and integrity. Among other things, LSNs play an important role in commit and rollback operations, crash and roll-forward recovery, and synchronization of database operations in partitioned database environments.

The growth rate of LSNs in log files is directly related to database activity. That is, the LSN increases as transactions occur and entries are written to the log file. The more activity there is in the database, the faster the LSN grows.

Upper limit of log serial number

In DB2 V9.5 and earlier, the log sequence number (LSN) was a 6-byte number. Starting with FP3, LSNs range from 0x0000 0000 (when the database is first created) to 0xFFFF 0000 0000 (about 256 terabytes). Before FP3, the upper limit was 0xFFFF FFFF. As records are added to the log file, LSNs grow over the lifetime of the database.

"MySQL how to backup and restore files" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

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.