Backup and Recovery MySQL dengan Zmanda Recovery Manager (ZRM) for MySQL

Untuk melakukan Backup dan Restore database MySQL dengan tools ZRM for MySQL ini perlu dilakukan beberapa perubahan konfigurasi sesuai kebutuhan backup:
Buka dan edit file konfigurasi mysql-zrm.cnf

backup-level=0
retention-policy=”10D”
replication=1
compress=”1″
compress-plugin=/usr/bin/gzip
user=”root”
password=”******247″
socket=”/var/lib/mysql/mysql.sock”
single-transaction=0

david# vi /etc/mysql-zrm/mysql-zrm.conf

Untuk melakukan testing backup ini saya menggunakan database world dan table City1, jalankan backup dengan query mysql-zrm-scheduler

david# mysql-zrm-scheduler –now –backup-set dailyrun

schedule:INFO: ZRM for MySQL Community Edition – version 2.1
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
backup:INFO: ZRM for MySQL Community Edition – version 2.1
dailyrun:backup:INFO: START OF BACKUP
dailyrun:backup:INFO: PHASE START: Initialization
dailyrun:backup:WARNING: The lvm-snapshot option is deprecated. Use snapshot-size instead
dailyrun:backup:INFO: backup-set=dailyrun
dailyrun:backup:INFO: backup-date=20090701123455
dailyrun:backup:INFO: mysql-server-os=Linux/Unix
dailyrun:backup:INFO: backup-type=regular
dailyrun:backup:INFO: host=localhost
dailyrun:backup:INFO: backup-date-epoch=1246426495
dailyrun:backup:INFO: retention-policy=10D
dailyrun:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition – version 2.1
dailyrun:backup:INFO: mysql-version=5.0.83-community-log
dailyrun:backup:INFO: backup-directory=/var/lib/mysql-zrm/dailyrun/20090701123455
dailyrun:backup:INFO: backup-level=0
dailyrun:backup:INFO: backup-mode=raw
dailyrun:backup:INFO: PHASE END: Initialization
dailyrun:backup:INFO: PHASE START: Running pre backup plugin
dailyrun:backup:INFO: PHASE END: Running pre backup plugin
dailyrun:backup:INFO: PHASE START: Flushing logs
dailyrun:backup:INFO: PHASE END: Flushing logs
dailyrun:backup:INFO: PHASE START: Creating snapshot based backup
dailyrun:backup:INFO: Unable to use snapshot for tables in world
dailyrun:backup:INFO: PHASE END: Creating snapshot based backup
dailyrun:backup:INFO: PHASE START: Creating raw backup
dailyrun:backup:INFO: raw-tables=City1
dailyrun:backup:INFO: raw-databases=world
dailyrun:backup:INFO: PHASE END: Creating raw backup
dailyrun:backup:INFO: PHASE START: Calculating backup size & checksums
dailyrun:backup:INFO: next-binlog=mysql-bin.008133
dailyrun:backup:INFO: last-backup=/var/lib/mysql-zrm/dailyrun/20090701122525
dailyrun:backup:INFO: backup-size=0.28 MB
dailyrun:backup:INFO: PHASE END: Calculating backup size & checksums
dailyrun:backup:INFO: PHASE START: Compression/Encryption
dailyrun:backup:INFO: compress=/usr/bin/gzip
dailyrun:backup:INFO: backup-size-compressed=0.07 MB
dailyrun:backup:INFO: PHASE END: Compression/Encryption
dailyrun:backup:INFO: read-locks-time=00:00:00
dailyrun:backup:INFO: flush-logs-time=00:00:01
dailyrun:backup:INFO: compress-encrypt-time=00:00:00
dailyrun:backup:INFO: backup-time=00:00:01
dailyrun:backup:INFO: backup-status=Backup succeeded
dailyrun:backup:INFO: Backup succeeded
dailyrun:backup:INFO: PHASE START: Running post backup plugin
dailyrun:backup:INFO: PHASE END: Running post backup plugin
dailyrun:backup:INFO: PHASE START: Mailing backup report
dailyrun:backup:INFO: PHASE END: Mailing backup report
dailyrun:backup:INFO: PHASE START: Cleanup
dailyrun:backup:INFO: PHASE END: Cleanup
dailyrun:backup:INFO: END OF BACKUP
/usr/bin/mysql-zrm started successfully

Hapus table City1 yang sudah dibackup pada database world

david# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.83-community-log MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> use world
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+—————–+
| Tables_in_world |
+—————–+
| City            |
| City1           |
| Country         |
| CountryLanguage |
+—————–+
4 rows in set (0.00 sec)

mysql> drop table City1;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+—————–+
| Tables_in_world |
+—————–+
| City            |
| Country         |
| CountryLanguage |
+—————–+
3 rows in set (0.00 sec)

Dan sekarang lakukan restore dengan query mysql-zrm-restore

david# mysql-zrm-restore –backup-set dailyrun –source-directory /var/lib/mysql-zrm/dailyrun/20090701123455

restore:INFO: ZRM for MySQL Community Edition – version 2.1
dailyrun:restore:WARNING: The lvm-snapshot option is deprecated. Use snapshot-size instead
dailyrun:restore:INFO: Restored database from raw backup: world
dailyrun:restore:INFO: Restore done in 1 seconds.
MySQL server has been shutdown. Please restart after verification.

mysql> show tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)
ERROR:
Can’t connect to the server

Restart service MySQL dan check kembali table City1 apakah sudah tersedia

david# service mysql start
Starting MySQL.                                            [  OK  ]

david# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.83-community-log MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> use world
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+—————–+
| Tables_in_world |
+—————–+
| City            |
| City1           |
| Country         |
| CountryLanguage |
+—————–+
4 rows in set (0.01 sec)

mysql> \q

david# date
Wed Jul  1 14:04:44 WIT 2009

yapppppp………….. table City1 sudah tersedia

semoga bermanfaat…!

Advertisements

About davidand

Hi, welcome to my blogs. My name is David Andriansyah. I am currently working as an independent Cognos Business Intelligence consultant, and I also as Freelance Web Developer. View all posts by davidand

3 responses to “Backup and Recovery MySQL dengan Zmanda Recovery Manager (ZRM) for MySQL

  • testmuku

    [root@hostdb ~]# mysql-zrm –action restore –backup-set DailyBackup1 –source-directory /var/lib/mysql-zrm/DailyBackupDB1/20090803114518/
    restore:INFO: ZRM for MySQL Community Edition – version 2.1
    DailyBackup1:restore:ERROR: Output of command: ‘mysqladmin variables’ is {
    mysqladmin: connect to server at ‘localhost’ failed
    error: ‘Access denied for user ‘root’@’localhost’ (using password: NO)’
    }
    DailyBackup1:restore:ERROR: Cannot connect to mysql server!

    What is the problem????

  • davidaank

    mysqladmin Cannot connect to mysql server
    check the root user password is correct

  • Panglima

    terima kasih mas David untuk tutorialnya,
    klo ukuran file backupannya lebih kecil dari mysqldump gak ya?

    thanx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: