Mysqldump Backup common options and backup scripts

1. Backup command:

# mysqldump-u ' root '-P ' 12345 '-h 127.0.0.1 \

-Q--single-transaction-r-e \

--triggers--default-character-set=utf8 \

-B Test | gzip > Test. ' Date +%f_%t '. sql

2. Backup script:

# cat/dbback.sh

#!/bin/bash

Datetime= ' Date +%f_%t '

Dbname= (MySQL test)

echo $datetime >/backdb.log

For DB in ${dbname[*]}

Do

echo "############################## db back $db begin time is $ (date +%f_%t) ##############################" >>/b Ackdb.log

Mysqldump-u ' root '-P ' 12345 '-H 127.0.0.1-q--single-transaction-r-e--triggers--default-character-set=utf8-b ${db} | gzip >/${db}.$ (Date +%f_%t). sql.gz

echo "############################## db back $db end time is $ (date +%f_%t) ##############################" >>/b Ackdb.log

Sleep 2;

echo "" >>/backdb.log

echo "" >>/backdb.log

Done

Sleep 5

Find/-name "*.sql.gz"-type f-mtime +45 | Xargs RM-RF

[Email protected] ~]#

3, Command description:

-Q,--quick don ' t buffer query, dump directly to stdout. Do not cache queries and dump them directly to stdout.

--single-transaction

Creates a consistent snapshot by dumping all tables in a

Single transaction. Works Tables stored in

Storage engines which support multiversioning (currently

only InnoDB does); The dump is not a guaranteed to be

Consistent for other storage engines. While a

--single-transaction dump is in process, to ensure a

Valid dump file (correct table contents and binary log

Position), no other connection should use the following

Statements:alter table, DROP table, RENAME table,

TRUNCATE TABLE, as consistent snapshot is not isolated

from them. Option automatically turns off--lock-tables.

Create a consistent snapshot by dumping all tables into one unified snapshot

Single transaction. Used only for storage in

The storage engine supports multiple versions (currently

Only innodb); rubbish is not guaranteed.

Consistency of other storage engines. and

-Single transaction dumps during the process to ensure

Valid dump file (correct table content and binary log

Location), no other connection should use the following

Statements: Changing tables, drop-down tables, renaming tables,

Truncatetable, consistent snapshots are not isolated

from them. option to automatically close-lock the table.

-R,--routines Dump stored routines (functions and procedures).

Dump stored routines (functions and programs).

-E,--events Dump events. Dumping events.

--triggers dump triggers for each dumped table. Dump trigger for the dump.

--default-character-set=name Set the default character set.

-B,--databases Dump several databases. Note the difference in usage; Inch

This case no tables is given. All name arguments is

Regarded as database names. ' Use db_name; ' 'll be

Included in the output.

Dumps several databases. Pay attention to the differences in usage;

There are no tables in this case. All name Parameters

As the database name. "Use db_name;

Included in the output

--master-data[=#] This causes the binary log position and filename to be

appended to the output. If equal to 1, would print it as a

Change MASTER command; If equal to 2, that command would

Be prefixed with a comment symbol. This option would turn

--lock-all-tables on, unless--single-transaction is

Specified too (in which case a global read lock are only

Taken a short time at the beginning of the dump; Don ' t

Forget to read about--single-transaction below). In all

Cases, any action on logs would happen at the exact moment

of the dump. Option automatically turns--lock-tables

Off.

This results in a binary log location and file name

Append to output. If it equals 1, it will be printed as a

Change the main command, or if it equals 2, the command will

With a comment symbol. This option will open

-Lock all tables unless-single trade

Specified (in this case, a global read lock is unique

The time at which the dump begins is very short;

Forget to read-the following single transaction. Total

Case, any action on the log will take place at the exact moment

The dump. option to automatically open the lock table

Shut down.

Mysqldump Backup common options and backup scripts