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

Production mysqldump parameters

Mysqldump-uroot'- pxx'-Q-all-databases-lock-all-tables-routines-triggers-events-master-data=2-flush-logs-socket=mysql.sock-set-gtid-purged=OFF

Mysqldump backup parameters

Then there are the concrete steps to solve the problem, first backing up the data. The-master-data parameter and-single-transaction are not added to the backup. The reason is that the-master-data disables the-lock-tables parameter, which disables-lock-all-tables when used with-single-transaction. At the beginning of the backup, the global read lock is obtained. The-single-transaction parameter sets the default level to REPEATABLE READ and executes START TRANSACTION at the start of the backup. During the backup, other connections cannot execute the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. MySQL synchronous tamping, if the above parameters are added, mysqldump will also tamp. Mysqldump can FLUSH TABLES, LOCK TABLES, if there is a-master-data parameter, it will cause Waiting for table flush. Again, with the-single-transaction parameter, it still results in Waiting for table flush. In addition, you can also see Waiting for table metadata lock, which does the operation of DROP TABLE at this time. You can stop MySQL synchronization at this point to avoid this problem.

Reference oldbody

Full database backup

#! / bin/bash

# mysqldump to fully backup mysql data

If [- f / root/.bash_profile]; then

Source / root/.bash_profile

Fi

BakDir=/opt/mysqlbak/full

LogFile=/opt/mysqlbak/full/bak.log

Date= `date +% Y% m% d`

Begin= `date + "Y% m / d% H:%M:%S" `

Cd $BakDir

DumpFile=$Date.sql

GZDumpFile=$Date.sql.tgz

Mysqldump-uroot-pendant xxxxxxx'--all-databases-- lock-all-tables-- routines-- triggers-- events-- master-data=2-- flush-logs-- socket=/opt/3306/mysql.sock-- set-gtid-purged=OFF > $DumpFile

Tar zcvf $GZDumpFile $DumpFile

If [- f $DumpFile]; then

Rm-rf $DumpFile

Fi

Last= `date + "Y% m / d% H:%M:%S" `

Echo start: $Begin end: $Last $GZDumpFile succ > > $LogFile

Sleep 1

Find / opt/mysqlbak/full-name "* .tgz"-mtime + 3-exec rm-rf {}\

Sub-library backup

#! / bin/bash

If [- f / root/.bash_profile]; then

Source / root/.bash_profile

Fi

MysqlUser=root

PassWord='xxxxxx'

Port=3306

Socket= "/ opt/$Port/mysql.sock"

MysqlCmd= "mysql-u$MysqlUser-p$PassWord-S $Socket"

Database= `$ MysqlCmd-e "show databases;" | egrep-v "Database | _ schema | mysql" `

MysqlDump= "mysqldump-u$MysqlUser-p$PassWord-S $Socket"

# IP= `ifconfig eth0 | awk-F "[:] +" NR==2 {print $4}'`

BackupDir=/opt/mysqlbak/fenku

LogFile=/opt/mysqlbak/fenku/bak.log

Begin= `date + "Y% m / d% H:%M:%S" `

[- d $BackupDir] | | mkdir-p$BackupDir

For dbname in $Database

Do

$MysqlDump-events-set-gtid-purged=OFF-B $dbname | gzip > / $BackupDir/$ {dbname} _ $(date +% F) _ bak.sql.gz

Done

Last= `date + "Y% m / d% H:%M:%S" `

Echo start: $Begin end: $Last $GZDumpFile succ > > $LogFile

Sleep 1

Find / opt/mysqlbak/fenku-name "* .gz"-mtime + 3-exec rm-rf {}\

Reduction

Single reduction

Restore a single table in mysqldump backup

Mysql-uroot-pMANAGER erp-one-database file.sql

Backup list structure only

Mysqldump-- opt-d database name-u root-p > xxx.sql

Export data base to dbname a table (test) structure and table data (without-d)

Mysqldump-uroot-pdbpasswd dbname test > db.sql

Export the entire database structure (without data)

Mysqldump-h localhost-uroot-p123456-d database > dump.sql

Export a single data table structure (without data)

Mysqldump-h localhost-uroot-p123456-d database table > dump.sql

Considerations for mysqldump

Reference http://huaxin.blog.51cto.com/903026/1846224

Mysqldump-uroot-p123456 xxx > / opt/xxx.sql # backup database xxx

Egrep-v "# |\ * |-- | ^ $" / opt/xxx.sql

Mysqldump-uroot-p123456 xxx-- default-character-set=latin1 > / opt/xxx1.sql

Egrep-v "# |\ * |-- | ^ $" / opt/xxx1.sql

Mysqldump-uroot-p123456-B xxx-- default-character-set=latin1 > / opt/xxx1_B.sql

The difference between adding-B option and adding-B option in diff xxx1.sql xxx1_B.sql comparison

Description: the function of adding-B parameter is to add commands to create and connect to the database when exporting the database, that is, the following two statements

CREATE DATABASE / *! 32312 IF NOT EXISTS*/ `xxx` / *! 40100 DEFAULT CHARACTER SET latin1 * /

Summary:

1.-B parameter is used to export data.

2. Compress the backed-up data with gzip

How mysqldump works

The process of backing up data using the mysqldump command is actually outputting the data from the mysql library in the form of logical sql statements.

Cat mysql.sh # script for backing up multiple databases of a database

#! / bin/bash

For dbname in `mysql-uroot-p123456-e "show databases;" | grep-Evi "database | info | perf" `

Do

Mysqldump-uroot-p123456-- events-B ${dbname} | gzip > / opt/$ {dbname} .sql.gz

Done

Back up a single table

Mysqldump-uroot-p123456 martin student > one.sql

Back up multiple tables

Mysqldump-uroot-p123456 martin student student1 > two.sql

Mysqldump-uroot-p123456-d martin student1

The structure of backing up only the student1 table martin represents the database

Mysqldump-uroot-p123456-A-B-- events | gzip > / opt/all.sql.gz-A represents all databases

Mysqldump-uroot-p123456-A-B-F-- events | gzip > / opt/all.sql.gz-F refreshes bin-log

Mysqldump-uroot-p123456-- master-data=1-- compact martin #-- master-data=1 this parameter will find the bin-log location

Mysqldump-uroot-p123456-- master-data=2-- compact martin #-- master-data=2 this parameter looks for the bin-log location, but the statement is commented and is not actually executed

Description of key parameters of mysqldump

1.-B specifies multiple libraries, which will add database building statements and use statements.

2.-- compact removes comments, which is suitable for debugging and output production environment

3.-A back up all libraries

4.-F refresh binlog log

5.-- master-data=1 adds the binglog log file name and the corresponding location point

6.-x lock table

7.-l read-only lock table

8.-d backup list structure only

9.-t only back up data

10.-- single-transaction is suitable for innodb transactional database backup

-- master-data [= #]

If equal to 1, will print it as a CHANGE MASTER command; if equal to 2, that command will

Be prefixed with a comment symbol

This parameter runs-- lock-all-tables, and writes the binlog and postion information of master to the header of the SQL file, unless combined with-- single-transaction (but this does not mean that the table will not be locked at all, and a temporary global read lock will be added during execution).

Production scene myisam backup:

Mysqldump-uroot-p123456-A-B-master-data=1-x-- events | gzip > / opt/all.sql.gz

Production scene innodb backup:

Mysqldump-uroot-p123456-A-B-master-data=1-- events-- single-transaction | gzip > / opt/all.sql.gz

System ls / opt

Rh xxx1_B.sql xxx1_B.sql.gz xxx1.sql xxx.sql

Source / opt/xxx1_B.sql

Mysql5.7 mysqldump parameter-- all-databases-- lock-all-tables-- routines-- triggers-- events-- master-data=2-- flush-logs-- socket=/opt/3306/mysql.sock-- set-gtid-purged=OFF

Table locking problems caused by mysqldump overlay backup 2013

Solution:

1. If you only need file backups and do not need to build slave libraries often, you can remove-- master-data.

two。 If you have a large amount of data and are in great need of master information for or backups, you can adjust the backup cycle to avoid overlap between the two backups.

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.