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

This article mainly introduces how to backup and restore mysqldump, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

I. mysqldump

Mysqldump backup principle, go to the database to query data, and then carry on the splicing of sql statements.

Advantages: mysql comes with, easy to use, backup results easy to operate. A small amount of data can be used with mysqldump

Disadvantages: under a large amount of data, it is slow to recover using mysqldump. When mysqldump imports data, it uses SQL statements and needs to open the transaction. Use is not recommended.

two。 The syntax is as follows:

Mysqldump [arguments] > file_name

(1)。 Back up all databases

Mysqldump-- all-databases > dump.sql

(2)。 Back up the specified database

Mysqldump-- databases wwj db2 db3 > dump.sql

(3). Single-transaction gets the consistency of backup

When you enable this parameter and make a backup, make sure that no other DDL statements are executed. Ensure that the backup point-in-time consistent snapshot can be read repeatedly.

(4)-- lock-tables (- l)

During the backup process, lock the backed up tables in turn. Commonly used in myisam engines to ensure consistency. This parameter is not required for innodb engines.

If there are tables for both innodb and myisam, you can only use-- lock-tables

(5). Lock-all-tables (- x)

All tables are locked at the same time during the backup.

(6). Add-drop-database

Run drop database before create database and use it with-all-databases or-- databases

-- add-drop-table

Delete the table before creating the table

(7)-- events (- E)

Backup event Scheduler

(8)-- routines (- R)

Backup stored procedures and functions

(9). Triggers

Backup trigger

(10). Hex-blob

Back up the binary,varbinary,blog,bit column type to hexadecimal format

(11)-- tab=path (- T path)

Generate TAB split data files. For each table, create a table_name.sql file containing create table statements and an tb1_name.txt file containing data

(12)-- where='where_condition' (- w)

Export data under given conditions

(13)。 Export several tables in a database

Mysqldump-uroot-p-S / tmp/mysql3306.sock-- single-transaction test emp_range > t1.sql

(14)。 Export table structure

-- no-data,-d does not export any data, only the database table structure

Mysqldump-uroot-p-S / tmp/mysql3306.sock-- single-transaction-- all-databases-d > all.sql

(15)-- ignore-table=name

Do not export a table, e.g =-- ignore-table=database.table

(16)-- insert-ignore

Insert rows with INSERT IGNORE

(17). Log-error=name

Append warnings and errors to given file.

(18). Master-data

When mysqldump exports data, when the value of this parameter is 1, the file from mysqldump will include the statement CHANGE MASTER TO. CHANGE MASTER TO is followed by the records of file and position. This statement will be executed when the data is imported on slave, and salve will copy the binlog from the masterside according to the specified file location. By default, the value is 1. When the value is 2, chang master to is also written to the dump file, but the statement is annotated.

The master-data parameter is often used when setting up a slave database, because it is a good parameter, with a default value of 1, which by default contains change master to, which contains the record start position of file and position. When master-data=2, the file from mysqldump contains the statement CHANGE MASTER TO, which is in the commented state.

(19).-- no-create-info,-t only exports data without adding CREATE TABLE statements

(20).-- no-create-db,-n only exports data without adding CREATE DATABASE statements

(21).-- force,-f ignores SQL errors during export and continues later operations when errors occur

-- mysqldump recovery

(1) mysql-uroot-p

(2). Source / home/mysql/test_backup.sql

Or

/ usr/local/mysql/bin/mysql-uroot-p-S / tmp/mysql3306.sock

< all.sql 三.逻辑备份恢复实验 1.备份恢复所有数据库 - 备份实例下所有数据库 mysqldump -u root -p -S /tmp/mysql3306.sock --all-databases --add-drop-database --single-transaction >

/ tmp/db1.sql

-restore

Mysql-uroot-p-S / tmp/mysql3306.sock

< /tmp/db1.sql 2.备份恢复单独的数据库 -备份数据库wwj mysqldump -u root -p -S /tmp/mysql3306.sock --databases wwj --add-drop-database --single-transaction >

/ tmp/db2.sql

-restore database wwj

Mysql-uroot-p-S / tmp/mysql3306.sock

< /tmp/db2.sql 3.备份恢复某数据库中的几个表 -备份表wwj.t3, wwj.haha22 mysqldump -u root -p -S /tmp/mysql3306.sock --single-transaction --databases wwj --tables t3 haha22 >

/ tmp/db3.sql

-restore

Use wwj

Source / tmp/db3.sql

Thank you for reading this article carefully. I hope the article "how to back up and restore mysqldump" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.