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

This article mainly gives you a brief talk about database backup mysqldump analysis. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic of database backup mysqldump analysis. I hope it can bring you some practical help.

= from a physical, logical point of view

Physical backup: backup of physical files (data files, log files) of the database operating system

Methods:

Cold backup: when the database is closed

Hot backup: the database is running, and this backup method depends on the log files of the database

Logical backup: backup of database logical components

= from the policy point of view of the database

Full backup:

Advantages: simple and convenient

Disadvantages: there is a lot of duplication of data.

Take up a lot of backup space

Long backup time

Differential backup: backup based on more content after the last full backup

Physical backup:

Yum install xz-y

Tar Jcvf / opt/mysql-$ (date+%F). Tar.xz / usr/local/mysql/data/

# back up the mysql data file at that time to form a compressed file to the compressed package under opt

The mysqldump command makes a full backup of a single library [but there are no statements created by create]

Add-d if you save the table structure

Mysqldump-u user-p password database name > saved file path, file type is sql

Example: # backup the database named school to / opt/ and name it school.sql

Mysqldump-uroot-pabc123 school > / opt/school.sql # mysqldump-uroot user-p password database name school > / opt/school.sql

= restore data, import data =

Method 1:

Database school needs to be created in advance

Mysql-uroot-pabc123 school

< school.sql 方法二： mysqldump -uroot -pabc123 --databases school >

School.sql

No need to create database school in advance

Mysql-uroot-pabc123

< school.sql 方法三 首先进入数据库，备份一份数据， drop删除的表 source 之前备份数据的文件路径，结尾不用加; 多个数据库备份： mysqldump -uroot -p --databases数据库名称1 数据库名称2 >

/ opt/ file name .sql

Integrity database backup

Back up all database structures:

Mysqldump-u user name-p-- all-databases > Save file path file type sql

Back up a complete database file:

Mysqldump-u user name-p-- databases database name > save file path file type sql

Save a backup of the contents of the tables in the database:

Mysqldump-u user name-p database name table name > save file path file type sql

Add the refresh statement for incremental backup [if the integrity backup must have been completed ahead of time, and then refresh the statement. After entering the refresh statement, the previous incremental operation will be imported into the last binary log node]

[root@client~] # mysqladmin-uroot-pabc123 flush-logs

[root@client~] # mysqlbinlog-- no-defaults-base64-output=decode-rows-v / opt/bak_sql/mysql-bin.000001 # View the binary log file and use 64-bit decoding to see the command statement.

When restoring and correcting misoperated incremental backup data: two methods: time-based and location-based

1. Restore data based on a point in time:

Scenario example: first, after the misoperation, an incremental backup is carried out, and after deleting the misoperated data table, when the data is restored.

First restore the integrity backup of the previous backup, and then view the binaries of the incremental backup

Find the point-in-time copy and paste above the wrong operation command, and then enter the command:

Mysqlbinlog-- no-defaults-- stop-datetime='2018-09-03 15purl 31purl 56'/ usr/local/mysql/data/mysql-bin.000002 | mysql- uroot-pabc123

Find the point-in-time copy and paste above the next correct action command, and then enter the command:

Mysqlbinlog-- no-defaults-- start-datetime='2018-09-03 15 start-datetime='2018 32 Swiss 16'/ usr/local/mysql/data/mysql-bin.000002 | mysql- uroot-pabc123

two。 Based on location point: the location point of misoperation is: at 887

Scenario example: first, after the misoperation, an incremental backup is carried out, and after deleting the misoperated data table, when the data is restored.

First restore the integrity backup of the previous backup, and then view the binaries of the incremental backup

Find the last possible location of the error operation: at 833, enter the command:

Mysqlbinlog-- no-defaults-- stop-position='833' / usr/local/mysql/data/mysql-bin.000002 | mysql- uroot-pabc123

Find the location point where the next operation of the error operation can be executed: at 930, enter the command:

Mysqlbinlog-- no-defaults-- start-position='930' / usr/local/mysql/data/mysql-bin.000002 | mysql- uroot-pabc123

= exit mysql, operate mysql=

Return to the path of the previous backup and execute the command (you can do the command operation without entering the database)

Mysql-uroot-p database < previously backed up file path

Operations that can be done without entering the database: need to add-e

Example syntax: mysql-u user-p password-e 'use database; show databases;'

Incremental backup, when restoring the database, you need to restore the original integrity backup before entering the command

[root@client~] # mysqlbinlog-- no-defaults mysql-bin.000002 | mysql- uroot-p

PS: when you want to restore an incremental backup, you have to restore the previous integrity backup first, enter the refresh statement for the incremental backup, and then restore the incremental backup of 000002 nodes

Database backup mysqldump analysis will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.