Mysql

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

    Leave a comment