Using mysqldump in linux to back up a mysql database as an SQL File

In linux, use mysqldump to back up A mysql database into an SQL file. Phase 1: create A full backup mysqldump-hip address-uusername-ppassword-~ Name. SQL exports the entire database (structure and data) to the slave database in this case.

In linux, use mysqldump to back up A mysql database into an SQL file. Phase 1: create A full backup mysqldump-hip address-uusername-ppassword-~ /Name. SQL in this case, the entire database (structure and data) is exported for backup.

Using mysqldump in linux to back up a mysql database as an SQL File

Phase 1: Silly full backup

Mysqldump-h IP address-uusername-ppassword-A> ~ /Name. SQL

In this case, the entire database (structure and data) is exported and backed up into an SQL file.

Certificate -----------------------------------------------------------------------------------------------------------------------------------------------

Phase 2: adjust parameters appropriately to achieve different backup Effects

Let's first help

Mysqldump -- help

Too many help outputs will not be listed one by one.

Remote connection and backup

-H: the address of the backup server.

-U: accounts that allow remote connection

-P: password of the remote connection account

-A full backup

Common Parameters

-A, -- all-databases: Back up all databases

-- Add-drop-database: add a drop database before each create database statement.

-- Add-drop-table: add a drop table before each create table statement.

-- Add-locks adds lock tables before each TABLE is exported and then unlock table. (To make it faster to insert data to MySQL)

-- Default-character-set = name: set the character set of the exported data.

-- Opt is the same as -- quick -- add-drop-table -- add-locks -- extended-insert -- lock-tables. You should be given the fastest export possible for reading a MySQL server.

-E, -- extended-insert use the new multiline INSERT syntax. (A more compact and faster insert statement is provided ). Multiple insert statements are generated to increase the import speed.

-- Hex-blob exports binary string fields in hexadecimal format. This option is required if binary data exists. The affected field types include BINARY, VARBINARY, and BLOB.

-- Quick,-q this option is useful when exporting large tables. It forces mysqldump to directly output records from server queries rather than cache all records into memory.

-T, -- no-create-info only exports data

-D, -- no-data only exports the structure

Certificate ----------------------------------------------------------------------------------------------------------------------------------------------

Phase 3: Create a database and a table for the database

Mysqldump-h IP address-uusername-ppassword databasename> ~ /Name. SQL

Mysqldump-h IP address-uusername-ppassword databasename tablename> ~ /Name. SQL

Certificate ----------------------------------------------------------------------------------------------------------------------------------------------

Phase 4.1:

By reading text, database shards are used to back up databases into different SQL files.

For example, we have db1 db2 db3 db4 db5 ...... Db100 when the data volume in each database is not small, we need to back up data separately by database,

It is impossible for us to write 100 backup scripts in 100 databases separately, so we need to combine the loop statements in the shell script to operate.

Cat filename.txt | while read I

Do

Backup script, for example, mysqldump-h IP address-uusername-ppassword $ I> ~ /Name. SQL

Done

In this example, the name of the database is saved in filename.txt, with one row.

In combination with the linux crond service, we can automatically back up data.

Phase 4.2

If you need database/table sharding, what should you do?

In this case, the table file generated for each database is named db1.txt db2.txt db3.txt db4.txt db5.txt ....... Db100.txt

Back up database/table sharding through two cycles

Cat filename.txt | while read I

Do

Mkdir $ I

Cat polici.txt | while read

Do

Backup script, for example, mysqldump-h IP address-uusername-ppassword $ I $ a> $ I/$ a. SQL

Done

Done

Of course, we sometimes need to record the backup time

Fn = $ (date + "% Y-% m-% d _ % H: % M ")

Cat filename.txt | while read I

Do

Mkdir $ I

Cat polici.txt | while read

Do

Backup script, for example, mysqldump-h IP address-uusername-ppassword $ I $ a> $ I/$ a $ fn. SQL

Done

Done

Certificate --------------------------------------------------------------------------------------------------------------------------------------------------

Phase 5:

After the first phase, our backups are more detailed. If there is a problem with that part of the data, we will restore that part (although the granularity is still a bit rough)

However, do we think that the backup in the 4th stage is still very complicated? For example, the database name and the indicated file are a huge project. On the U.S. server, we have to add a row to the database file to create a new database.

When creating a new table, we can add one to the table file under a database. This is tedious, error-prone, and easy to forget, which makes maintenance very troublesome.

Now we have a way for him to automatically list the warehouse and tables in the database.

Mysql-e

Run

Mysql-e "show databases"

The following result is displayed.

+ -------------------- +

| Database |

+ -------------------- +

| Information_schema |

| Mysql |

| Performance_schema |

| Test |

+ -------------------- +

However, we cannot use such a result. We need to remove things from the perimeter.

Mysql-e "show databases" | sed '1d'

Information_schema

Mysql

Performance_schema

Test

For db in 'mysql-e "show databases" | sed '1d''

Do

Backup script, for example, mysqldump-h IP address-uusername-ppassword $ db> name. SQL

Done

With this reference, we can also back up database/table sharding.

Mysql databasename-e "show tables" | sed '1d'

Fn = $ (date + "% Y-% m-% d _ % H: % M ")

For db in 'mysql-e "show databases" | sed '1d''

Do

Mkdir $ db

For tables in 'mysql $ db-e "show tables" | sed '1d''

Do

Backup script, for example, mysqldump-h IP address-uusername-ppassword $ db $ tables> $ db/$ tables $ fn. SQL

Done

Done

Please correct me for the time being. If you have other better ways, please leave a message and make progress together.

This article is from the "Tian Ma xingkong" blog. Please keep this source