Mysql
January 6th, 2009
Dump
Buat keluarin data satu database
mysqldump database > namafile.sql
Buat keluarin semuanya
mysqldump -A > namafile.sql
Buat import
mysql < nama.sql
Log Rotate
/var/backups/mysql/dump.sql {
daily
rotate 14
missingok
compress
postrotate
/usr/bin/mysqldump --defaults-extra-file=/.../backup-credentials.cnf --opt --flush-logs --all-databases > /var/backups/mysql/dump.sql
endscript
}/dumps/mybase.sql.gz {
rotate 60
daily
nocompress
nocopytruncate
postrotate
HOME=/root mysqldump --opt mybase | gzip > /dumps/mybase.sql.gz
endscript
}Setup:
- Create your ~/.my.cnf for password-less database access
- Place the logrotate configuration file above in the /etc/logrotate.d/ directory
- Bootstrap the first dump:
- mkdir -m 700 /dumps
- touch /dumps/mybase.sql.gz
- logrotate -f /etc/logrotate.d/mysql-dumps
- Check the dump using zcat /dumps/mybase.sql.gz.
Cron Pake Perl
#!/usr/bin/perl ($Second, $Minute, $Hour, $Day, $Month, $Year, $WeekDay, $DayOfYear, $IsDST) = localtime(time) ; $Year += 1900 ; $Month += 1; $dt = sprintf("%04d%02d%02d", $Year, $Month, $Day, ) ; exec "/usr/local/bin/mysqldump --opt -hHOSTNAME -uUSERID -pPASSWORD DATABASE_NAME |gzip > PATHNAME/$dt.gz";
Cron Doang
/usr/local/bin/mysqldump -hDBXX.PAIR.COM -uDB_USERNAME -pDB_PASSWORD USERNAME_DATABASENAME > /usr/home/USERNAME/backup/DATABASENAME.`/bin/date +%Y%m%d`
mysqldump –opt -h 192.168.2.105 -u john -p’****’ mybase | gzip > mybase-`date +%Y%m%d`.sql.gz
