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

I. brief introduction

Developed by MySQL AB, it is the most popular open source SQL database management system with the following main features:

1. It is a database management system.

2. It is an associated database management system.

3. It is a kind of open source software, and there are a lot of shared MySQL software available

4. MySQL database server is fast, reliable and easy to use.

5. MySQL server works in client / server mode, or in embedded system.

The InnoDB storage engine saves InnoDB tables in a tablespace that can be created by several files. In this way, the size of the table can exceed the maximum capacity of individual files. Tablespaces can include raw disk partitions, making large tables possible. The maximum capacity of the tablespace is 64TB.

II. MySQL backup data

2.1. Backup Typ

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

2.2. Backup mode

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: slow data export to text files and loss of floating-point precision; easy to deal with directly with text tools with strong portability

Disadvantages: floating-point data may lose precision; backed-up data takes up more storage space

Compression can greatly save space; it is not suitable for full backup of large databases.

2.3. Backup strategy

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

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)

2.4. Backup tool

2.4.1. Mysqlhotcopy: physical backup tool, warm backup

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

The backup mode of mysqlhotcopy is faster than that of mysqldump. Mysqlhotcopy is a perl script that is mainly used on Linux systems.

Use LOCK TABLES, FLUSH TABLES, and cp for quick backups.

Backup principle:

First add a read lock to the database that needs to be backed up, and then use FLUSH TABLES to write the data in memory back to the database on the hard disk

Finally, copy the database files that need to be backed up to the target directory.

Command format:

[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

This tool is not included with mysql and needs to be installed with Perl's database interface package; it can only back up tables of type MyISAM.

Mysqlhotcopy download address: http://dev.mysql.com/downloads/dbi.html

2.4.2. 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

2.4.3. Third-party backup tools:

Ibbackup: a commercial tool

Xtrabackup: an open source tool

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

Mysqldump: (add the following options when backing up)

-- databases DB1,DB2,... # which databases are backed up

-- 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)

2.4.4.1. Copy the entire database directory directly

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 backing up, the server must be stopped to ensure 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 stop services to back up the server in a production environment.

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.

2.4.4.2. Backup using the 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.

How it works: it first finds out the structure of the table that needs to be backed up, and then generates a CREATE statement in a 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.

2.4.4.2.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

Dbname: represents the name of the database

Table1 and table2: indicates the name of the table that needs to be backed up. If empty, the entire database is backed up.

BackupName.sql: indicates the name of the design 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 locks all tables

-- flush-logs performs log flush refresh

-- single-transaction. If the table type is InnoDB, you can start the hot standby. Do not lock the table during the hot backup.

Mysql > FLUSH TABLES WITH READ LOCK; # Lock table

[root@lamp ~] # mysqldump-uroot-p studb > / root/studb0616.sql # backup

Mysql > unlock tables; # unlock

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

First, 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)

Second, 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 Public Templates stu.sql

Third, unlock the table after 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.

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

* * *

First, 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 separately)

-- flush-logs-- lock-all-tables has the same meaning.

Step 2, fully back up all databases

[root@lamp] # mysqldump-uroot-p-master-data=2-- flush-logs-- all-databases-- lock-all-tables > / root/alldatabases.sql

#-master-data=2 records the location as CHANGE MASTER TO, but is commented out by default

-- flush-logs executes log flush and log scrolls

-- all-databases backs up all libraries

-- lock-all-tables locks all tables and backs up to the / root directory. (full backup of all databases)

-- single-transaction if the table type is InnoDB, this option starts the hot backup.

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 * /

...

/ * 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

...

The third step is to simulate and delete database logs, numeric values, and all data.

Mysql > PURGE BINARY LOGS TO 'mysql-bin.000020'

# simulate the 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 > 80; # Delete rows of table tutors ages older than 80

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/ # go to 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

# Export the binary log file of mysql to sql format data (and name 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 file 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]

Step 4, recover all data

[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/

# initialize the mysql database and specify the relevant options-- datadir specifies the data storage directory of the database.

Installing MySQL system tables...

OK

Filling help tables...

OK

To start mysqld at boot time you have to copy

Support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER!

To do so, start the server, then issue the following commands:

. / bin/mysqladmin-u root password' new-password'

. / bin/mysqladmin-u root-h lamp password' new-password'

Alternatively you can run:

. / bin/mysql_secure_installation

Which will also give you the option of removing the test

Databases and anonymous user created by default. This is

Strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

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 database 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

# Export the last binary log before the failure to sql format

[root@lamp mysql] # mysql

< /root/temp.sql #把二进制日志还原至数据库至此就手动进行了一次mysql所有库的及时点备份还原恢复, 但是此还原数据库的方法会导致产生很多mysql的二进制日志文件。 [root@lamp mysql]# mysqlbinlog /root/mysql-bin.000021 | mysql -uroot -p #和以上两步通过temp.sql中转一样的效果 正确的恢复方法是： 关闭二进制日志功能后备份，再启用二进制日志功能 恢复数据前，临时关闭mysql记录二进制日志功能，待恢复后再开启记录二进制日志的功能； mysql>

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

Mysql > SELECT @ @ sql_log_bin; # confirm the status of the current session

Mysql >\. / root/alldatabases.sql

# restore the contents of a full backup (\. Indicates reading the file contents of the directory behind the import, or you can replace it 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.4.4.2.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

-- events

-- routines

-- triggers

2.4.4.2.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

2.4.4.2.4, back up a single table in the database

For the backup and restore of only a single table, the backup and restore can be achieved more quickly through the SELECT command.

And through this method, the data of the tables in one database are imported into the tables in another database.

2.4.4.2.4.1. Backup format:

SELECT * INTO OUTFILE'/ PATH/TO/somefile.txt' FROM table_name [WHERE CLAUSE]

# backup the data in the table or the data that meets the WHERE statement and save it on the server.

Table_name: tables to be backed up

WHERE: conditions met, optional.

/ PATH/TO: the path directory on the server, and this directory must be the user who executed the SELECT statement with write permission, otherwise it cannot be backed up.

2.4.4.2.4.2. Restore format:

LOAD DATA INFILE'/ PATH/TO/somefile.txt' INTO TABLE table_name

Table_name: the name of the table to be restored, which must first exist in the database.

/ PATH/TO: the path where the backup is stored

Mysql > CREATE TABLE tutor LIKE tutors; # create an empty table tutor following the framework of the tutors table

Example: data from one database table is backed up and imported into another database table

Mysql > USE jiaowu

Database changed

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

+-+

| | TID | Tname | Gender | Age | |

+-+

| | 1 | HongQigong | M | 93 | |

| | 3 | Miejueshitai | F | 72 | |

| | 4 | OuYangfeng | M | 76 | |

| | 5 | YiDeng | M | 90 | |

| | 6 | YuCanghai | M | 56 | |

| | 7 | Jinlunfawang | M | 67 | |

| | 8 | HuYidao | M | 42 | |

| | 9 | NingZhongze | F | 49 | |

| | 10 | stuu01 | M | 30 | |

+-+

9 rows in set (0.00 sec)

Mysql > SELECT * INTO OUTFILE'/ tmp/tutor.txt' FROM tutors

Query OK, 9 rows affected (0.01sec)

Mysql > CREATE TABLE tutor LIKE tutors

Query OK, 0 rows affected (0.03 sec)

Mysql > DESC tutor

+-- +

| | Field | Type | Null | Key | Default | Extra | |

+-- +

| | TID | smallint (5) unsigned | NO | PRI | NULL | auto_increment |

| | Tname | varchar (50) | NO | | NULL |

| | Gender | enum ('Flying Magazine M') | YES | | M | |

| | Age | tinyint (3) unsigned | YES | | NULL |

+-- +

4 rows in set (0.01sec)

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

Empty set (0.04 sec) # No data yet

Mysql > LOAD DATA INFILE'/ tmp/tutor.txt' INTO TABLE tutor

Query OK, 9 rows affected (0.04 sec)

Records: 9 Deleted: 0 Skipped: 0 Warnings: 0

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

+-+

| | TID | Tname | Gender | Age | |

+-+

| | 1 | HongQigong | M | 93 | |

| | 3 | Miejueshitai | F | 72 | |

| | 4 | OuYangfeng | M | 76 | |

| | 5 | YiDeng | M | 90 | |

| | 6 | YuCanghai | M | 56 | |

| | 7 | Jinlunfawang | M | 67 | |

| | 8 | HuYidao | M | 42 | |

| | 9 | NingZhongze | F | 49 | |

| | 10 | stuu01 | M | 30 | |

+-+

9 rows in set (0.00 sec)

Third, MySQL data restore-often do fire rehearsal! 3.1. The syntax to restore 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 　　3.2. 还原直接复制目录的备份 　　通过这种方式还原时，必须保证两个MySQL数据库的版本号是相同的。MyISAM类型的表有效，对于InnoDB类型的表不可用，InnoDB表的表空间不能直接复制。 　　3.3. 自动备份脚本： 1，每天4点备份mysql数据； 2，为节省空间，删除超过3个月的所有备份数据； 3，删除超过7天的备份数据，保留3个月里的 10号 20号 30号的备份数据； [root@mysql ~]# vim Full_backup_mysql.sh #!/bin/bash cd /root/dbdata/mysqlbak echo "You are in backup dir : /root/dbdata/mysqlbak" echo "It is starting backup ..." mysqldump -uroot -p --master-data=2 --flush-logs --all-databases --lock-all-tables >

. / `date +% Y% m% d`.sql

Find / root/dbdata/mysqlbak/-mtime + 7-name'* [1-9] .SQL'- exec rm-rf {}\

Find / root/dbdata/mysqlbak/-mtime + 90-name'* .sql'- exec rm-rf {}\

# create scheduled tasks

Crontab-e

0 4 * / data/dbdata/backup_mysql.sh

Mysql > show global variables like'% log%'; # View all global variables related to the log

Mysql > show master status; # View binary log information

Mysql > set sql_log_bin=0; # setting not to record binary logs

Attached 1:mysqldump is often used for logical backup of MySQL database

Http://www.cnblogs.com/feichexia/p/MysqlDataBackup.html

?

-end---14--

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.