Assuming you are a superuser, create a new database:
1 |
mysqladmin -p create db_name |
Import data:
1 |
mysql -p db_name < db_name.sql |
Dump the db:
1 |
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):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
#!/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):
1 |
0 23 * * * /backup/db_backup.sh >> /var/log/db_backup.log |
Alternately you can backup all databases with one command:
1 |
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.
There are no comments