zeldor.biz

Linux, programming and more

Copyright © 2023
Log in

MySQL Dump

October 11, 2011 by Igor Drobot Leave a Comment


Collection of some MySQL commands, very useful to create and restore MySQL-Backups.

Dump only some tables of a database:

1
mysqldump -u root -p db1 wrp_comments wrp_posts > dump.sql

mysqldump -u root -p db1 wrp_comments wrp_posts > dump.sql

Create dump:

1
mysqldump -uUSERNAME -p DBNAME > DBNAME.sql

mysqldump -uUSERNAME -p DBNAME > DBNAME.sql

Restore/Insert dump:

1
mysql -D DBNAME -p < DBNAME.sql

mysql -D DBNAME -p < DBNAME.sql

A tiny backup script for your cron to dump all databases:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
!/bin/bash
 
DUMPDIR="/var/backups/mysqldumps/"
MUSER="root"
MPASS="pasword"
DBLIST="
db01
db02
db03
anotherdb
"
 
# Remove old dumps
rm -f $DUMPDIR*.sql
 
# Optimize db's
for database in $DBLIST; do
    mysql --user=$MUSER --password=$MPASS $database -e 'show tables' | awk '{print "optimize table " $1 ";"}' | mysql --user=$MUSER --password=$MPASS $database >/dev/null
done
 
# Create dump's
for database in $DBLIST; do
    mysqldump --user=$MUSER --password=$MPASS $database >$DUMPDIR$database.sql
done

!/bin/bash DUMPDIR="/var/backups/mysqldumps/" MUSER="root" MPASS="pasword" DBLIST=" db01 db02 db03 anotherdb " # Remove old dumps rm -f $DUMPDIR*.sql # Optimize db's for database in $DBLIST; do mysql --user=$MUSER --password=$MPASS $database -e 'show tables' | awk '{print "optimize table " $1 ";"}' | mysql --user=$MUSER --password=$MPASS $database >/dev/null done # Create dump's for database in $DBLIST; do mysqldump --user=$MUSER --password=$MPASS $database >$DUMPDIR$database.sql done

For very large databases you can use this previous written post: Dump locked Tables

Filed Under: Debian, Linux, MySQL Tagged With: MySQL, MySQL Backup, MYSQL Dump, mysqldump, mysqldumper

Categories

Archives

Tags

apache2 Apple arduino ARM Automation backup bash Cisco Cluster Corosync Database Debian Debian squeeze DIY DNS Fedora FTP Fun Icinga Ipv6 KVM Linux LVM MAC OS X Monitoring MySQL Nagios Nginx openSUSE OpenVPN PHP Proxy Python python3 qemu RAID rsync Samba security ssh Ubuntu virtualization Windows Windows 7 Wordpress

Leave a Reply

Your email address will not be published. Required fields are marked *