Category Archives: Database

A Backup Script For WordPress

Leave a reply

I’ve written a backup script in order to get my WordPress blog backup automatically. The script is only usable on a Linux/Unix box, since it uses default GNU tools.

The script connects to the server via ssh, copies a folder to a location, dumps a database to the same place with the copied folder, creates a tar.gz out of it, then gets the new file via ftp to a prefered location.

The important thing here is that, you should add your ssh public key to the server so that ssh will connect automatically. I also use .my.cnf files to login mysql without specifying password, so you’d better do that. I’ve talked about it in an earlier post here.

Keep in mind that you need an ftp client to connect. If you don’t have it, install it using yum, apt or whatever.

#!/bin/bash

###  START OF EDIT THESE ###
############################
HOST='192.168.1.1' # ip address of your server
SSHUser='root' # user to connect as ssh
FTPUser='myfunkyftpusername' # user to connect as ftp
FTPPass='mysupersecretFTPpassword!' # ftp connection password
MYSQLUser='root' # # user to connect as MySQL

SSHPort=22 # change if different
FTPPort=21 # change if different
DB=wordpress # which database to backup?
DIRECTORY='/home/eaydin/public_html/wp-content' # directory to back up - server side
DIRWRITE='/home/eaydin/' # move the backup here on the server.
DROPBOX='/home/eaydin/Dropbox' # local file path to backup - host side. use your Dropbox folder?
FILENAME='wp-backup' # Filename to use for backups

### END OF EDIT THESE ###
#########################
DIRWRITE=${DIRWRITE%/} # remove trailing / from dir name.
FILENAME=${FILENAME%/} # remove trailing / from filename in case the user types it.
DATE=`eval date +%d%m%Y"-"%H%M` # create date format. (created on the host side, not server. depends on the host time setings.)
FILETAR=$FILENAME-$DATE.tar.gz # name of the tar.gz file (not path!)

ssh -t $SSHUser@$HOST -p $SSHPort "\
cp -R $DIRECTORY $DIRWRITE/$FILENAME-$DATE ;\
mysqldump --add-drop-table -u $MYSQLUser $DB > $DIRWRITE/$FILENAME-$DATE/wordpress.sql ;\
tar -cvzf $DIRWRITE/$FILETAR $DIRWRITE/$FILENAME-$DATE ;\
chown $FTPUser:$FTPser $DIRWRITE/$FILETAR ;\
rm -rf $DIRWRITE/$FILENAME-$DATE
"
ftp -n $HOST $FTPPort <<END_SCRIPT
quote USER $FTPUser
quote PASS $FTPPass
lcd $DROPBOX
cd $DIRWRITE
binary
get $FILETAR
quit
END_SCRIPT
exit 0

The lines between 5 and 17 are the ones you should edit, they’re all self explained in the comments.

It’s a good idea to add the script to your crontab.
In order to do it, especially on Ubuntu systems, just add your current PATH value right below the /bin/sh line. Like this,

eaydin@eaVT:~$ echo $PATH
/usr/lib/lightdm/lightdm:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games

So you should add this line at the top of the script,

#!/bin/env bash
PATH=/usr/lib/lightdm/lightdm:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games

Or, just run the script whenever you want. I usually set the download path (defined as the $DROPBOX variable on line 16) to my Dropbox folder, this way my backups get automatically synced on the Dropbox server.

Importing Large MySQL Files With phpMyAdmin

Leave a reply

It may not be possible to import large SQL files using phpMyAdmin due to it’s uploading limits.
Sometimes this is related to your php.ini settings, but no always.

You can always use the old reliable method of importing sql files from the command line. Or upload to file to server and tell phpMyAdmin to look for that file specifically.

MySQL Command Line Option

Of course, we need to upload the file first. Below, the 1st line is to achieve this. After that we connect to our server via ssh, and then (on the 2nd line) import the sql file.

scp the_sql_file.sql root@myserver.com:/path/to/upload
mysql -u username -p -h localhost DATABASE-NAME < the_sql_file.sql

Here, the MySQL connection is established using a username and password. If you simple create a .my.cnf file in order to get automatic connections, you won’t need all these. Simply create a file to /root/.my.cnf (or any other user directory you want to)

[client]
user=root
pass=mysql_root_password

From now on, you don’t need to use the -u and -p arguments when using mysql, if this file exists at the user’s home directory you’re logged on as, than it will automatically pass them. It may seem insecure, but the /root folder is only accessed by the root anyway, even though it is not a good idea to keep passwords clear text, if your /root folder is compromised, you’re in big trouble anyway. And keep in mind that we usually have to keep MySQL passwords a cleartexts in scripts all the time.

Using phpMyAdmin to Import the SQL File

You can set a default folder for phpMyAdmin to check for uploaded files. This way, if you place a file into this folder, you can easily choose to import it. Find where your config.inc.php file is. If you don’t know where, try the locate command (if it is installed). Open the file and find the $cfg[‘UploadDir’] section. Update it as below

$cfg['UploadDir'] = 'imports';

Now, phpMyAdmin will check the “imports” folder directly. So we need a folder like that. On your terminal, create the folder.

mkdir /path/to/phpmyadmin/imports

Now upload your sql files here.

scp /path/to/the_sql_file.sql root@myserver.com:/path/to/phpmyadmin/imports

Change the owner and group of the imports folder. First check who owns the phpmyadmin folder. Let’s say it is called webapp,

chown -R webapp:webapp /path/to/phpmyadmin/imports

Ok. Now use your browser and access phpmyadmin, at the import db seciton you’ll see a dropdown menu that wasn’t there before. There you’ll see the files inside the “imports” folder. Even though they are larger than the phpmyadmin upload limits, they’ll get imported.