Perform Basic MySQL Database Operations

Assuming you are a superuser, create a new database:

mysqladmin -p create db_name

Import data:

mysql -p db_name < db_name.sql

Dump the db:

mysqldump -p --single-transaction db_name > db_name.sql

Backup!

Here’s a script called db_backup (make the file executable with chmod a+x filename):

#!/bin/bash

# vars
path=/backup
suffix=$(date +%m%d%Y)
filename=db_backup_$suffix.tar.gz
db_user=root
db_pass=

# dumb the dbs
mysqldump -u$db_user -p$db_pass --opt asterisk > $path/asterisk.sql
mysqldump -u$db_user -p$db_pass --opt gallery2 > $path/gallery2.sql
mysqldump -u$db_user -p$db_pass --opt wordpress > $path/wordpress.sql

# create an archive, cleanup
tar -P -zcf $path/$filename $path/*.sql
rm -f $path/*.sql

exit 0

Edit the crontab with crontab -e and add the following line (which runs every day at 11 pm and logs to the file specified):

0 23 * * * /backup/db_backup.sh >> /var/log/db_backup.log

Alternately you can backup all databases with one command:

mysqldump -p --all-databases --single-transaction > mysqlbackup.sql

Or backup the database files directly from /var/lib/mysql/. You don’t have to be a guru to administer your databases (though some knowledge of relational databases & SQL definitely helps). PHPMyAdmin is a handy tool that you should setup if you haven’t already.