Perform Basic MySQL Database Operations

Assuming you are a superuser, create a new database:

[code]mysqladmin -p create db_name[/code]

Import data:
[code]mysql -p db_name < db_name.sql[/code]

Dump the db:

[code]mysqldump -p –opt db_name > db_name.sql[/code]

Backup!

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

[code]
#!/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
[/code]

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

[code]
0 23 * * * /backup/db_backup.sh >> /var/log/db_backup.log
[/code]

Alternately you can backup all databases with one command:

[code]
mysqldump –all-databases -p > mysqlbackup.sql
[/code]

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.

 

About Benjamin Perove

Ben has been associated with a broad spectrum of technologies starting from an early age, and he's contributed to the success of many businesses and enterprises since 2001. Most of his time is spent building cool stuff. When he's not working, he enjoys reading, playing acoustic guitar, and being with friends. He currently resides in Chiang Mai, Thailand.

2 Comments so far

  1. Learn MySQL @ November 11th, 2008

    Learn MySQL…

    It is very simple to use this function through the SQL tab in phpMyAdmin (or any other direct interface with mysql). Say you want to change the name of the table from \”tbl_ oldtable\” to \”tbl_ newtable\” then this would be the code:…

Leave a reply

Add this site to your Firefox Search Bar

Twitter Activity

Recent Entries

Topics

Archives

Got WordPress Security?

You will definitely wish you had downloaded my top 5 recommendations when you're cleaning out malicious Javascript from deep within you WordPress site. Enter your email and get the PDF right now, before it's too late.