MySQL daily backup through manual commands or scripts
MySQL all database dump - localhost:
mysqldump -u root -ppasswd --all-databases > db.sql
-u = username = root
-p = password = ppasswd (don't keep space-bar between -p and the actual password)
--all-databases = all databases backup dump
db.sql = name of database - .sql = extension of database filename
MySQL all database dump - remote host:
mysqldump -u root -ppasswd -h 192.168.1.250 --all-databases > db.sql
-h = remote host IP address
MySQL all database dump - remote host and view progress:
mysqldump -u root -ppasswd -h 192.168.1.250 --verbose --all-databases > db.sql
--verbose = view progress on screen
MySQL all database dump - remote host, view progress and save by date:
mysqldump -u root -ppasswd -h 192.168.1.250 --verbose --all-databases > db_$(date +%Y_%m_%d).sql
> db_$(date +%Y_%m_%d).sql = save database file using current date with .sql extension
MySQL all database dump - remote host, save by date and gzip:
mysqldump -u root -ppasswd -h 192.168.1.250 --all-databases | gzip > $db$(date +%Y_%m_%d).sql.gz
| gzip = pipe to gzip command
> $db$(date +%Y_%m_%d).sql.gz = save database file with current date and .gz extension
MySQL dump all individual databases - localhost, save by date:
mysql -u root -ppasswd -Ne "show databases" | grep -v schema | while read db; do mysqldump --verbose -u root -ppasswd $db > $db.$(date +%Y_%m_%d).sql; done
mysql = command to connect to database
show databases = show list of databases command after connecting inside mysql
grep -v schema | while read db; do mysqldump = get list of db and while read, start dumping individual databases
Note: password added twice in command
MySQL dump all individual databases - localhost, gzip, save by date:
mysql -u root -ppasswd -Ne "show databases" | grep -v schema | while read db; do mysqldump --verbose -u root -ppasswd $db | gzip > $db.$(date +%Y_%m_%d).sql.gz; done
MySQL dump all individual databases - remote host, gzip, save by date:
mysql -h 192.168.1.250 -u root -ppasswd -Ne "show databases" | grep -v schema | while read db; do mysqldump --verbose -h 192.168.1.250 -u root -ppasswd $db | gzip > $db.$(date +%Y_%m_%d).sql.gz; done
MySQL daily dump shell script - remote host:
cd /home
mkdir dbdump
cd /home/dbdump
vi mysqldbdump.sh
mysql -h 192.168.1.250 -u root -ppasswd -Ne "show databases" | grep -v schema | while read db; do mysqldump --verbose -h 192.168.1.250 -u root -ppasswd $db | gzip > $db.$(date +%Y_%m_%d).sql.gz; done
:wq
chmod 777 mysqldbdump.sh
./mysqldbdump.sh
MySQL daily dump cron - remote host:
vi mysqldbdump.sh
mysql -h 192.168.1.250 -u root -ppasswd -Ne "show databases" | grep -v schema | while read db; do mysqldump -h 192.168.1.250 -u root -ppasswd $db | gzip > $db.$(date +%Y_%m_%d).sql.gz; done
:wq
Note: --verbose = removed from script as we want to set this inside cron
crontab -e
MAILTO=youremailaddress@gmail.com
* 8 * * * /home/dbdump/mysqldbdump.sh