Backing up a 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 backup:

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 Database backup shell script:
# mkdir/backups/db_backup/

# vi/backups/mysqlbackup.sh

#!/bin/bash  
Export path1=/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 $path 1/&N Bsp  
mkdir $date 1    
user= "root"    
password= "redhat123"    
outputdir= "$path 1/$date 1"    
mysqldump= "/usr/bin/mysqldump"    
mysql= "/usr/bin/mysql"   &NBSP,
host= "localhost"    ,
databases= ' $MYSQL--user= $USER--password= $PASSWORD--host= $HOST \    
-E "SHOW DATABASES;" | tr-d "|" | grep-v Database   &NBSP;
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 would run every night at a.m.
#crontab-E
0 0 * * */backups/mysqlbackup.sh >/dev/null

This article is from the SQL Server deep Dive blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1924916

Backing up a database using mysqldump