Mysql backup/restore

Просто положу здесь.

Use of mysqldump for taking backup of one table

In this section you will see the example of mysqldump function which takes the backup of only one table.

The mysqldump utility provides many options to take the backup of data as per user requirement. You can specify the name of table be backup in while executing the mysqldump utility.

Let's assume we have to take the backup of table1 from the mydatabase, then you can issue the following command on the console:

mysqldump -u -p mydatabase table1 > table1.sql

To restore the backup you can use the following command:

mysql -u -p mydatabase < table1.sql The mysqldump is very handy tool, it can be easily used to take the backup of only one table from the database. --- Dump ALL MySQL Databases mysqldump --user=XXXXXXXX --password=XXXXXXX -A > /PATH/TO/DUMPFILE.SQL

Dump Individual or Multiple MySQL Databases
mysqldump --user=XXXXXXXX --password=XXXXXXX --databases DB_NAME1 DB_NAME2 DB_NAME3 > /PATH/TO/DUMPFILE.SQL

Dump only certain tables from a MySQL Database
mysqldump --user=XXXXXXXX --password=XXXXXXXX --databases DB_NAME --tables TABLE_NAME > /PATH/TO/DUMPFILE.SQL

Use the following procedure to reload the contents of a database:
1. Unzip the backup file you wish to use.
2. Open it up and pull out only the information that you will need.
3. Save this text file.
4. Use the following command to feed back in the contents of a text file:
mysql --verbose --user=XXXXXXXX --password=XXXXXXXX DB_NAME < /PATH/TO/DUMPFILE.SQL

Cacti backup script

Много времени прошло с момента поднятия cacti на серваке. Написал скрипт для бэкапа, который рекурсивно копирует папку cacti со всеми скриптами и файлами графиков, сливает mysql базу и всё это заворачивает в tar и gzip. Можт кому будет интересно.

Собственно листинг:

#/usr/bin/bash

TMPDIR=/space/cacti_bkp
CACTIDIR=/usr/local/apache2/htdocs/cacti
BKPDIR=/space/
BKP=cacti_bkp

if test -d $TMPDIR
then rm -r $TMPDIR; mkdir $TMPDIR
else mkdir $TMPDIR
fi

echo "Copying files.."
(cp -R $CACTIDIR $TMPDIR)
wait

echo "Making SQL dump.."
/usr/local/mysql/bin/mysqldump -ucactiuser -pcactiuser --opt cacti > $TMPDIR/cacti_base.sql

echo "TARing files.."
tar cf $BKPDIR/$BKP.tar $TMPDIR

rm -r $TMPDIR
echo "GZIPing files.."

gzip -9 $BKPDIR/$BKP.tar
chown cacti:cacti $BKPDIR/$BKP.tar.gz
mv $BKPDIR/$BKP.tar.gz $BKPDIR/$BKP`date '+_%Y%m%d_%H%M%S'`.tar.gz

Результат работы скрипта:

-rw-r--r-- 1 cacti cacti 178235269 Jan 10 08:17 cacti_bkp_20100110_082232.tar.gz
-rw-r--r-- 1 cacti cacti 178720071 Jan 13 07:46 cacti_bkp_20100113_075147.tar.gz


В cron:
15 8 * * 0 sh /space/cacti_bkp.sh 1>/dev/null &
20 8 * * * find /space/*.gz -mtime 28 -exec rm {} \;

Первая строка собственно бэкап, вторая ищет старые бэкапы и трёт их.