Script Backup untuk Wordpress Apache dan MySQL

Berikut ini adalah script yang bisa digunakan untuk melakukan backup dari Apache, Wordpress dan MySQL. Backup akan dilakukan di penyimpanan lokal, lalu dipindahkan ke Amazon S3 sebagai media backup.

#!/bin/sh  
  
service apache2 stop  
  
mysqldump -u <mysql_user> -p<mysql_pass> <mysql_dbname> | gzip -9 > mysqlbackup-`date +%Y%m%d`.sql.gz  
chown ubuntu:ubuntu mysqlbackup-`date +%Y%m%d`.sql.gz  
s3cmd put mysqlbackup-`date +%Y%m%d`.sql.gz s3://<s3_bucket_name>/`date +%Y%m`/  
  
tar -zcvf wordpress-`date +%Y%m%d`.tar.gz /var/www  
chown ubuntu:ubuntu wordpress-`date +%Y%m%d`.tar.gz  
s3cmd put wordpress-`date +%Y%m%d`.tar.gz s3://<s3_bucket_name>/`date +%Y%m`/  
 
tar -zcvf apache2config-`date +%Y%m%d`.tar.gz /etc/apache2  
chown ubuntu:ubuntu apache2config-`date +%Y%m%d`.tar.gz  
s3cmd put apache2config-`date +%Y%m%d`.tar.gz s3://<s3_bucket_name>/`date +%Y%m`/  
 
service apache2 start  

Hasil backup juga menyimpan Private Key dari Apache HTTPS. Oleh karena itu disarankan untuk melakukan enkripsi menggunakan GPG. Script di bawah ini adalah modifikasi script di atas dengan menambahkan enkripsi GPG

1:  #!/bin/sh  
2:    
3:  service apache2 stop  
4:    
5:  mysqldump -u <mysql_user> -p<mysql_pass> <mysql_dbname> | gzip -9 > mysqlbackup-`date +%Y%m%d`.sql.gz  
6:  gzip -9 > mysqlbackup-`date +%Y%m%d`.sql.gz  
7:  chown ubuntu:ubuntu mysqlbackup-`date +%Y%m%d`.sql.gz  
8:  gpg --output mysqlbackup-`date +%Y%m%d`.sql.gz.gpg --encrypt --recipient <gpg_keyname> mysqlbackup-`date +%Y%m%d`.sql.gz  
9:  s3cmd put mysqlbackup-`date +%Y%m%d`.sql.gz.gpg s3://<s3_bucket_name>/`date +%Y%m`/  
10:    
11:  tar -zcvf wordpress-`date +%Y%m%d`.tar.gz /var/www  
12:  chown ubuntu:ubuntu wordpress-`date +%Y%m%d`.tar.gz  
13:  gpg --output wordpress-`date +%Y%m%d`.tar.gz.gpg --encrypt --recipient <gpg_keyname> wordpress-`date +%Y%m%d`.tar.gz  
14:  s3cmd put wordpress-`date +%Y%m%d`.tar.gz.gpg s3://<s3_bucket_name>/`date +%Y%m`/  
15:    
16:  tar -zcvf apache2config-`date +%Y%m%d`.tar.gz /etc/apache2  
17:  chown ubuntu:ubuntu apache2config-`date +%Y%m%d`.tar.gz  
18:  gpg --output apache2config-`date +%Y%m%d`.tar.gz.gpg --encrypt --recipient <gpg_keyname> apache2config`date +%Y%m%d`.tar.gz  
19:  s3cmd put apache2config-`date +%Y%m%d`.tar.gz.gpg s3://<s3_bucket_name>/`date +%Y%m`/  
20:    
21:  service apache2 start  
22:    

Cara Mematikan InnoDB di MySQL

Buka berkas /etc/mysql/my.cnf, lalu tambahkan baris dibawah ini

 [mysqld]  
 innodb=OFF  
 default_storage_engine=MyISAM  
 loose-innodb-trx=0   
 loose-innodb-locks=0   
 loose-innodb-lock-waits=0   
 loose-innodb-cmp=0   
 loose-innodb-cmp-per-index=0  
 loose-innodb-cmp-per-index-reset=0  
 loose-innodb-cmp-reset=0   
 loose-innodb-cmpmem=0   
 loose-innodb-cmpmem-reset=0   
 loose-innodb-buffer-page=0   
 loose-innodb-buffer-page-lru=0   
 loose-innodb-buffer-pool-stats=0   
 loose-innodb-metrics=0   
 loose-innodb-ft-default-stopword=0   
 loose-innodb-ft-inserted=0   
 loose-innodb-ft-deleted=0   
 loose-innodb-ft-being-deleted=0   
 loose-innodb-ft-config=0   
 loose-innodb-ft-index-cache=0   
 loose-innodb-ft-index-table=0   
 loose-innodb-sys-tables=0   
 loose-innodb-sys-tablestats=0   
 loose-innodb-sys-indexes=0   
 loose-innodb-sys-columns=0   
 loose-innodb-sys-fields=0   
 loose-innodb-sys-foreign=0   
 loose-innodb-sys-foreign-cols=0  

Pastikan hasilnya dengan menjalankan perintah di bawah. Baris yang berisi InnoDB akan terlihat seperti hasil di bawah ini:

 mysql -u root -p  
 show engines;  
 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+  
 | Engine       | Support | Comment                            | Transactions | XA  | Savepoints |  
 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+  
 | PERFORMANCE_SCHEMA | YES   | Performance Schema                       | NO      | NO  | NO     |  
 | MRG_MYISAM     | YES   | Collection of identical MyISAM tables             | NO      | NO  | NO     |  
 | MEMORY       | YES   | Hash based, stored in memory, useful for temporary tables   | NO      | NO  | NO     |  
 | BLACKHOLE     | YES   | /dev/null storage engine (anything you write to it disappears) | NO      | NO  | NO     |  
 | MyISAM       | DEFAULT | MyISAM storage engine                     | NO      | NO  | NO     |  
 | CSV        | YES   | CSV storage engine                       | NO      | NO  | NO     |  
 | ARCHIVE      | YES   | Archive storage engine                     | NO      | NO  | NO     |  
 | FEDERATED     | NO   | Federated MySQL storage engine                 | NULL     | NULL | NULL    |  
 | InnoDB       | NO   | Supports transactions, row-level locking, and foreign keys   | NULL     | NULL | NULL    |  
 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+  

Referensi: https://dev.mysql.com/doc/refman/5.6/en/innodb-turning-off.html