How to back up a database using mysqldump

This article mainly introduces how to back up the database using mysqldump, the contents of the articles are carefully selected and edited by the author, with a certain pertinence, the reference significance for you is still relatively great, the following with the author to understand how to backup the database using mysqldump.

MySQL single database backup:

Mysqldump-force-opt-single-transaction-lock-tables=false-skip-events-user=root-password=xxxx-routines-databases testdb > singledb.sql

MySQL all database backups:

Mysqldump-force-opt-single-transaction-lock-tables=false-skip-events-user=root-password=xxxx-routines-all-databases > all_databases.sql

MySQL database restore:

Mysql-u root-predhat dbname

< /mnt/dbname.sql MySQL数据库备份的Shell脚本： # mkdir /backups/db_backup/ # vi /backups/mysqlbackup.sh #!/bin/bash export path2=/backups/db_backup date1=`date +%y%m%d_%H%M%S` /usr/bin/find /backups/db_backup/* -type d -mtime +30 -exec rm -r {} \; 2>

/ dev/null

Cd $path2/

Mkdir $date1

USER= "root"

PASSWORD= "redhat123"

OUTPUTDIR= "$path2/$date1"

MYSQLDUMP= "/ usr/bin/mysqldump"

MYSQL= "/ usr/bin/mysql"

HOST= "localhost"

Databases= `$ MYSQL-user=$USER-password=$PASSWORD-host=$HOST\

-e "SHOW DATABASES;" | tr-d "|" | grep-v Database`

Echo "`for db in $databases; do

Echo $db

If ["$db" = "performance_schema"]; then

$MYSQLDUMP-force-opt-single-transaction-lock-tables=false-skip-events-user=$USER-password=$PASSWORD-host=$HOST-routines\

-- databases $db | gzip > "$OUTPUTDIR/$db.gz"

Else

$MYSQLDUMP-force-opt-single-transaction-lock-tables=false-events-user=$USER-password=$PASSWORD-host=$HOST-routines\

-- databases $db | gzip > "$OUTPUTDIR/$db.gz"

Fi

Done `"

: wq

Configure scheduling in crontab:

# The script will run every night at 12 A.M

# crontab-e

0 * / backups/mysqlbackup.sh > / dev/null

After reading the above about how to back up the database using mysqldump, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.