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