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:    

WordPress database error COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4' for query \nCREATE TABLE...

Terdapat pesan kesalahan yang cukup membingungkan ketika saya mencoba membuat situs baru dalam instalasi Wordpress Multisite (WPMU) versi 4.2.2. Gejalanya, pada saat klik 'Create' setelah semua data diisi, tampilan pada browser langsung kosong.

Penelusuran pada log server Apache, terdapat pesan kesalahan sbb:

 [Tue Jul 21 16:17:40 2015] [error] [client 127.0.0.1] WordPress database error COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4' for query \nCREATE TABLE wp_7_options (\n option_id bigint(20) unsigned NOT NULL auto_increment,\n option_name varchar(64) NOT NULL default '',\n option_value longtext NOT NULL,\n autoload varchar(20) NOT NULL default 'yes',\n PRIMARY KEY (option_id),\n UNIQUE KEY option_name (option_name)\n) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8_general_ci made by wpmu_create_blog, install_blog, make_db_current_silent, dbDelta, referer: http://rajah.localdomain/wp-admin/network/site-new.php  
 [Tue Jul 21 16:17:40 2015] [error] [client 127.0.0.1] WordPress database error COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4' for query \nCREATE TABLE wp_7_postmeta (\n meta_id bigint(20) unsigned NOT NULL auto_increment,\n post_id bigint(20) unsigned NOT NULL default '0',\n meta_key varchar(255) default NULL,\n meta_value longtext,\n PRIMARY KEY (meta_id),\n KEY post_id (post_id),\n KEY meta_key (meta_key(191))\n) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8_general_ci made by wpmu_create_blog, install_blog, make_db_current_silent, dbDelta, referer: http://rajah.localdomain/wp-admin/network/site-new.php  
 [Tue Jul 21 16:17:40 2015] [error] [client 127.0.0.1] WordPress database error COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4' for query \nCREATE TABLE wp_7_posts (\n ID bigint(20) unsigned NOT NULL auto_increment,\n post_author bigint(20) unsigned NOT NULL default '0',\n post_date datetime NOT NULL default '0000-00-00 00:00:00',\n post_date_gmt datetime NOT NULL default '0000-00-00 00:00:00',\n post_content longtext NOT NULL,\n post_title text NOT NULL,\n post_excerpt text NOT NULL,\n post_status varchar(20) NOT NULL default 'publish',\n comment_status varchar(20) NOT NULL default 'open',\n ping_status varchar(20) NOT NULL default 'open',\n post_password varchar(20) NOT NULL default '',\n post_name varchar(200) NOT NULL default '',\n to_ping text NOT NULL,\n pinged text NOT NULL,\n post_modified datetime NOT NULL default '0000-00-00 00:00:00',\n post_modified_gmt datetime NOT NULL default '0000-00-00 00:00:00',\n post_content_filtered longtext NOT NULL,\n post_parent bigint(20) unsigned NOT NULL default '0',\n guid varchar(255) NOT NULL default '',\n menu_order int(11) NOT NULL default '0',\n post_type varchar(20) NOT NULL default 'post',\n post_mime_type varchar(100) NOT NULL default '',\n comment_count bigint(20) NOT NULL default '0',\n PRIMARY KEY (ID),\n KEY post_name (post_name(191)),\n KEY type_status_date (post_type,post_status,post_date,ID),\n KEY post_parent (post_parent),\n KEY post_author (post_author)\n) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8_general_ci made by wpmu_create_blog, install_blog, make_db_current_silent, dbDelta, referer: http://rajah.localdomain/wp-admin/network/site-new.php  

Pesan error tersebut berarti ada ketidaksesuaian antara CHARACTER SET dan COLLATION yang digunakan pada saat tabel untuk menyimpan situs baru dibuat.

Untuk memperbaikinya, ubah isi dari berkas wp-config.php. Cari kata 'DB_COLLATE', dan tambahkan karakter merah, sehingga menjadi seperti baris di bawah ini.

 /** The Database Collate type. Don't change this if in doubt. */  
 define('DB_COLLATE', 'utf8mb4_general_ci');  

Setelah itu coba kembali untuk membuat situs baru.