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 &gt; /var/backups/mysql/dump.sql
endscript
}
/dumps/mybase.sql.gz {
        rotate 60
        daily
        nocompress
        nocopytruncate
        postrotate
          HOME=/root mysqldump --opt mybase | gzip &gt; /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 &gt; 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

Share
  1. No comments yet.
  1. No trackbacks yet.

Switch to our mobile site