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

I. data backup

Backup types: hot backup, warm backup and cold backup

Hot backup: read and write are not affected

Warm backup: only read operations can be performed

Cold backup: offline backup, read and write operations are aborted

Backup method: physical backup and logical backup:

Physical backup: copy data files quickly, save all the data structures of the database, if the data is strongly related to the database

Resulting in poor portability of physical backups

Logical backup: export data to a text file, slow speed and loss of floating-point precision; easy to use text processing tools

Directly deal with it, the portability is strong; disadvantages: floating-point data may lose precision; backup data is more occupied

Storage space; compressed can greatly save space; not suitable for full backup of large databases.

Backup strategy: full backup, incremental backup and differential backup:

Full backup: backing up all data

Incremental backup: back up only the data that has changed since the last full backup or incremental backup

Differential backup: only data that has changed since the last full backup is backed up; (differential backup requires more space than incremental backup)

What the database needs to back up: data, configuration files, binary logs, transaction logs

MySQL backup tool:

Mysqldump: logical backup tool, MyISAM (warm backup), InnoDB (hot backup)

Mysqldump: (add the following options when backing up)

-- databases DB1,DB2,... # backup those databases

-- all-databases # backup all databases

MyISAM: warm backup (options need to be added as follows)

-- lock-all-tables # Lock all tables during backup

-- lock-tables # Lock only the backed-up tables when backing up a table

InnoDB: hot backup (options need to be added as follows)

-- single-transaction # needs to add this item when performing a hot backup to indicate that it is performing a hot backup

-- flush-logs # scroll down the log file when backing up

-- events # backup event

-- routines # backup stores events and functions

-- triggers # backup trigger

-- master-data= {0 | 1 | 2} the file after backup records the log location (generally set to 2)

Mysqlhotcopy: physical backup tool, warm backup

File system tools:

Cp: cold standby tool

Lv: snapshot function of logical volume, almost hot backup:

Mysql > FLUSH TABLES; refresh the table

Mysql > LOCK tables lock table

Create a snapshot: release the lock and then copy the data

Third-party backup tools:

Ibbackup: a commercial tool

Xtrabackup: an open source tool

Backup using mysqldump command (logical backup tool)

The mysqldump command backs up the data in the database into a text file. The structure of the table and the data in the table are stored in the generated text file.

The mysqldump command works simply. It first finds out the structure of the table that needs to be backed up, and then generates a CREATE statement in the text file. Then, convert all the records in the table into an INSERT statement. With these statements, you can then create the table and insert the data.

1. To back up a database, refresh and lock the table before backup, otherwise the table will be modified to backup inconsistency.

Basic mysqldump syntax:

Mysqldump-u username-p dbname table1 table2...-> BackupName.sql

Where:

The dbname parameter represents the name of the database

The table1 and table2 parameters indicate the name of the table that needs to be backed up. If empty, the entire database is backed up.

The BackupName.sql parameter table designs the name of the backup file, which can be preceded by an absolute path. The database is usually divided into a file with the suffix sql

Options:

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

0: binary log files and their locations are not recorded

1: record the location in the way of CHANGE MASTER TO, which can be used to start the slave server directly after recovery

2: record the location as CHANGE MASTER TO, but the default is to be commented out

-- lock-all-tables: lock all tables

-- flush-logs: perform log flush refresh

If all the tables in the specified library are of InnoDB type, you can use-- single-transaction to start the hot backup

Example: use root users to back up test database steps:

The first step is to refresh and lock the table:

Mysql > FLUSH TABLES WITH READ LOCK; # refresh the table and lock the table by reading

Query OK, 0 rows affected (0.00 sec)

Then back up the table through mysqldump:

[root@lamp ~] # mysqldump-uroot-p test > / root/stu.sql # backup the test database to the / root directory

Enter password:

[root@lamp ~] # ls / root

Anaconda-ks.cfg Music Public Templates stu.sql

Unlock the table after the backup:

Mysql > UNLOCK TABLES; # unlock table

Query OK, 0 rows affected (0.00 sec)

The version of MySQL, the hostname of the backup, and the database name are recorded at the beginning of the file.

What starts with "-" in the file is a comment in SQL language, and what begins with "/ *! 40101" is a comment related to MySQL. 40101 is the version number of the MySQL database. If the version of MySQL is higher than 1.11, the content between / *! 40101 and * / is executed as a SQL command, and if it is lower than 4.1.1, it is treated as a comment.

* * *

* instance: simulate that all libraries are broken to achieve full backup and point-in-time restore.

Fully back up all databases first:

The first step is to refresh and lock the table:

Mysql > FLUSH TABLES WITH READ LOCK; # refresh the table and read the lock table (this step can also be used in conjunction with mysqldump without having to operate alone)-- flush-logs-- lock-all-tables has the same meaning.

Query OK, 0 rows affected (0.00 sec)

[root@lamp ~] # mysqldump-uroot-p-- master-data=2-- flush-logs-- all-databases-- lock-all-tables > / root/alldatabases.sql #-master-data=2 records the location by CHANGE MASTER TO, but defaults to be commented out,-- flush-logs refreshes log files,-- all-databases backs up all libraries,-- lock-all-tables locks all tables and backs up to the / root directory. (full backup of all databases)

Enter password:

[root@lamp ~] # less / root/alldatabases.sql # View the current binary log in the full backup is 000020

-- MySQL dump 10.13 Distrib 5.5.28, for Linux (x86 / 64)

-Host: localhost Database:

-

-- Server version 5.5.28-log

/ * 40101 SET @ OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT * /

/ * 40101 SET @ OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS * /

/ * 40101 SET @ OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION * /

/ * 40101 SET NAMES utf8 * /

/ * 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /

/ * 40103 SET TIME_ZONE='+00:00' * /

/ * 40014 SET @ OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 * /

/ * 40014 SET @ OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 * /

/ * 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' * /

/ * 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 * /

-- Position to start replication or point-in-time recovery from

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000020', MASTER_LOG_POS=107

-- Current Database: `hellodb`

CREATE DATABASE / *! 32312 IF NOT EXISTS*/ `hellodb` / *! 40100 DEFAULT CHARAC

.

Mysql > PURGE BINARY LOGS TO 'mysql-bin.000020'; # simulates log information before the current binary is deleted. (it is best to save the log file somewhere else before deletion, in case it is restored to the previous state.)

Query OK, 0 rows affected (0.01 sec)

Mysql > SHOW BINARY LOGS; # View all the log information, leaving only 000020

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000020 | 107 |

+-+ +

1 row in set (0.00 sec)

Mysql > USE studb

Database changed

Mysql > SELECT * FROM tutors; # query the information of table tutors

+-+

| | TID | Tname | Gender | Age | |

+-+

| | 1 | HongQigong | M | 93 | |

| | 2 | HuangYaoshi | M | 63 | |

| | 3 | HuangRong | F | 46 | |

| | 4 | HuYidao | M | 65 | |

| | 5 | XiaoLongnv | F | 28 | |

| | 6 | HuFei | M | 45 | |

| | 7 | GuoXiang | F | 32 | |

+-+

Mysql > DELETE FROM tutors WHERE Age > 80th # delete rows of table tutors over 80 years old

Query OK, 1 rows affected (0.01sec)

Mysql > SELECT * FROM tutors;# query the information of table tutors

+-+

| | TID | Tname | Gender | Age | |

+-+

| | 2 | HuangYaoshi | M | 63 | |

| | 3 | HuangRong | F | 46 | |

| | 4 | HuYidao | M | 65 | |

| | 5 | XiaoLongnv | F | 28 | |

| | 6 | HuFei | M | 45 | |

| | 7 | GuoXiang | F | 32 | |

+-+

Mysql >\ Q

[root@lamp ~] # mysql-uroot-p # Log in to mysql

Enter password:

Mysql > FLUSH LOGS; # refresh scrolling log

Query OK, 0 rows affected (0.01 sec)

Mysql >\ Q

[root@lamp ~] # cd / mydata/data/# enter the mysql data folder

[root@lamp data] # ls

Hellodb ib_logfile0 lamp.err mydb mysql-bin.000020 mysql-bin.index stu

Ibdata1 ib_logfile1 lamp.pid mysql mysql-bin.000021 performance_schema test

[root@lamp data] # mysqlbinlog mysql-bin.000020 > / root/mon-incremental.sql # exports the binary log file of mysql to sql format data (and names it the first incremental backup)

[root@lamp data] # mysql-uroot-p # Log in to mysql

Enter password:

Mysql > USE studb

Database changed

Mysql > INSERT INTO tutors (Tname) Values ('stu123'); # insert a row of data into table tutors

Query OK, 1 rows affected (0.01sec)

Mysql >\ Q

[root@lamp data] # ls

Hellodb ib_logfile0 lamp.err mydb mysql-bin.000020 mysql-bin.index

Ibdata1 ib_logfile1 lamp.pid mysql mysql-bin.000021 performance_schema

[root@lamp data] # cp mysql-bin.000021 / root # copy the binary log files of the last operation to a directory other than the data directory / root

[root@lamp data] # rm-rf. / * # Delete all data in the data directory of the database (all library data of the simulation database is lost)

[root@lamp data] # ls # View the current directory, the data has been emptied

[root@lamp data] # service mysqld stop # mysql cannot be stopped or started at this time because the data files have been emptied

MySQL server PID file could not be found! [FAILED]

[root@lamp data] # killall mysqld # can only force the termination of the mysqld process at this time

[root@lamp ~] # cd / usr/local/mysql/ # enter the installation directory of mysql

[root@lamp mysql] # ls

Bin data include lib mysql-test scripts sql-bench

COPYING docs INSTALL-BINARY man README share support-files

[root@lamp mysql] # scripts/mysql_install_db-- user=mysql-- datadir=/mydata/data/ # initializes the mysql database and specifies the relevant options.-- datadir specifies the data storage directory of the database.

Installing MySQL system tables...

OK

Filling help tables...

OK

.

Cd. ;. / bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

Cd. / mysql-test; perl mysql-test-run.pl

Please report any problems with the. / bin/mysqlbug script!

[root@lamp mysql] # service mysqld start # restart the database after successful initialization, because it has been initialized, so the password of the database is empty.

Starting MySQL... [OK]

[root@lamp mysql] # mysql

< /root/alldatabases.sql #先恢复完全备份的内容 [root@lamp mysql]# mysql < /root/mon-incremental.sql #恢复第一次增量备份 [root@lamp mysql]# mysqlbinlog /root/mysql-bin.000021 >

/ root/temp.sql # exports the last binary log before the failure to sql format data

[root@lamp mysql] # mysql

< /root/temp.sql #把二进制日志还原至数据库 至此就手动进行了一次mysql所有库的及时点备份还原恢复,但是此还原数据库的方法会导致产生很多mysql的二进制日志文件。 正确的恢复方法是： 恢复数据前，临时关闭mysql记录二进制日志功能，待恢复后再开启记录二进制日志的功能； mysql>

SET sql_log_bin=0; # temporarily turns off the binary logging function

Query OK, 0 rows affected (0.00 sec)

Mysql >\. / root/alldatabases.sql # restore the contents of the full backup (\. Indicates that the file contents of the directory behind the import are read

Can also be replaced with the SOURCE command)

Mysql >\. / root/mon-incremental.sql # restore the first incremental backup

Mysql > SET sql_log_bin=1; # turn on the binary logging function after the database is restored

*

2. Back up multiple databases

Syntax: mysqldump-u username-p-- all-databases > / root/back.sql: back up all libraries

Mysqldump-u username-p-- databases dbname2 dbname2 > Backup.sql

The-- databases option is added, followed by multiple databases

Mysqldump-u root-p-- databases test mysql > / root/backup.sql

3. Back up all databases

The syntax for the mysqldump command to back up all databases is as follows:

Mysqldump-u username-p-all-databases > / root/BackupName.sql

Example:

Mysqldump-u-root-p-all-databases > / root/all.sql

Second, directly copy the entire database directory

MySQL has a very simple backup method, which is to copy the database files in MySQL directly. This is the simplest and fastest way.

Before that, however, stop the server so that the data in the database will not change during replication. If there are still data writes in the process of copying the database, it will cause data inconsistency. This is fine in a development environment, but it is difficult to allow backup servers in a production environment.

Note: this method does not apply to tables of the InnoDB storage engine, but is convenient for tables of the MyISAM storage engine. At the same time, the version of MySQL should be the same when restoring.

Quick backup using the mysqlhotcopy tool (physical backup tool)

You can tell it's a hot backup when you look at the name. Therefore, mysqlhotcopy supports not stopping MySQL server backups. Also, mysqlhotcopy backs up faster than mysqldump. Mysqlhotcopy is a perl script that is mainly used on Linux systems. It uses LOCK TABLES, FLUSH TABLES, and cp for quick backups.

Principle: first add a read lock to the database that needs to be backed up, then use FLUSH TABLES to write the data in memory back to the database on the hard disk, and finally, copy the database files that need to be backed up to the target directory.

The format of the command is as follows:

[root@localhost ~] #

Dbname: database name

BackupDir: which folder to back up to

Common options:

-- help: view mysqlhotcopy help

-- allowold: if the same backup file exists in the backup directory, add the old backup file to _ old

-- keepold: if the same backup file exists in the backup directory, do not delete the old backup file, but rename the old file

-- flushlog: record updates to the database in the log after this seniority

-- noindices: only data files are backed up, not index files

-- user= user name: used to specify the user name, which can be replaced by-u

-- password= password: used to specify a password, which can be replaced by-p. When using-p, there is no space between the password and-p

-- port= port number: used to specify the access port, which can be replaced by-P

-- socket=socket file: used to specify the socket file, which can be replaced by-S

Mysqlhotcopy is not included with mysql. You need to install the database interface package of Perl. The download address is http://dev.mysql.com/downloads/dbi.html.

Currently, the tool can only back up tables of type MyISAM.

II. Data restoration

1. The syntax for restoring a database backed up with the mysqldump command is as follows:

Mysql-u root-p [dbname]

< backup.sql 　　示例： mysql -u root -p < C:\backup.sql 　　2、还原直接复制目录的备份 　　通过这种方式还原时，必须保证两个MySQL数据库的版本号是相同的。MyISAM类型的表有效，对于InnoDB类型的表不可用，InnoDB表的表空间不能直接复制。 自动备份脚本： 要求： 1，每天4点备份mysql数据； 2，为节省空间，删除超过3个月的所有备份数据； 3，删除超过7天的备份数据，保留3个月里的 10号 20号 30号的备份数据； #创建shell文件vim backup_mysql.sh#!/bin/bash mysqldump -uroot -p --master-data=2 --flush-logs --all-databases --lock-all-tables >

/ root/dbdata/mysqlbak/ `date +% Y% m% d`.sqlfind / root/dbdata/mysqlbak/-mtime + 7-name'* [1-9] .sql'- exec rm-rf {}\; find / root/dbdata/mysqlbak/-mtime + 90-name'* .sql'- exec rm-rf {}\; # create a scheduled task crontab-e0 4 * / data/dbdata/backup_mysql.sh

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.