Category Archives: MySQL

VirtualMin backup fails

virtualminHaving problems backing up your virtual hosts with virtualmin after the upgraded to 4.13 (available around january the seventh 2015)?

The source of the problem is that mysqldumps are now executed under the domain account (for security reasons). As is obvious this does not always work correct. For this moment there is a quick fix thanks to the great support folks at virtualmin.com.

Check here for the quickfix (the restart of the virtualmin server is mandatory :-)).

Share

MySQL automated database backup

This article describes a way to setup a regular backup for your MySQL databases. This method uses the crontab to schedule backup jobs (you could also use the logrotate method).

The database backups are stored in separate files. Once database backup is completed the file is zipped and password encrypted with openssl.

First create a .my.cnf file in your home directory with the following contents:

[client]
user=backup
password=1234567890

Make sure the file is only readable by your self:

chown 600 ~/.my.cnf

Next create a SQL user as shown below:

mysql> grant lock tables,select,reload,show databases,event on *.* to 'backup'@'localhost' identified by '1234567890';
mysql> flush privileges;

Create a folder in for example /var/backups/ and name it mysql

mkdir /var/backups/mysql

Create a script called backup_mysql_dbs.sh at a suitable location, for example your $HOME/cron/scripts folder (make it NOT world readable / writeable):

#!/bin/bash
BACKUPDEST=/var/backups/mysql

if [ ! -d "$BACKUPDEST" ]; then
echo "$BACKUPDEST does not exist, exiting"
exit 1
fi

# Remove backup files older dan 7 days
/usr/bin/find $BACKUPDEST/*.sql.gz.enc -type f -mtime +5 -exec rm {} \;

# Backup all mysql databases to separate files. The files are gzipped and encoded with a password.
umask 077
/usr/bin/mysql -Ne "show databases" | grep -v schema |
while read db; do
CDATE=`date '+%Y-%m-%d-%H%M%S'`
/usr/bin/mysqldump --skip-events --events $db | /bin/gzip | /usr/bin/openssl aes-256-cbc -salt -k $CDATE > $BACKUPDEST/$db.$CDATE.sql.gz.enc;
done

Now it is time to add a cron.d file; go to /etc/cron.d and add a crontab file, e.g. backup_mysql, with the following contents:

# /etc/cron.d/anacron: crontab entries for the anacron package
MAILTO=user@host.ext
M H * * * root /var/www/backup_mysql_dbs.sh

Replace the M and H with minute and hour you want the backup to be performed.

That’s it; your mysql databases are backedup at the sepcified interval.

 

Share

Ubuntu Apache + MySQL + WordPress

Install devenv with Ubuntu + Apache + MySQL

Create a new virtual machine and mount your Ubuntu ISO as CDROM drive. Startup the new virtual machine and install Ubuntu with default options.

After Ubuntu installation is complete be sure to add the guest additions to your system (keep your original configuration when asked):

$>apt-get install virtualbox-guest-x11

Ubuntu windows appearing slow? See this url: http://askubuntu.com/questions/207813/why-does-an-ubuntu-12-10-guest-in-virtualbox-run-very-very-slowly/214968#214968

Upgrade and update your system to make sure you have the latest and the greatest software:

$>apt-get update
$>apt-get upgrade

Mandatory components for your development environment

$>apt-get install apache2
$>apt-get install mysql-server libapache2-mod-auth-mysql php5-mysql
# Enter root password and press OK (twice)
# Install MySQL system tables:
$>mysql_install_db
# Secure your MySQL installation
$>/usr/bin/mysql_secure_installation
# Enter the root password choosen above
# Choose n (already have a password)
# Enter four times (everything default)
$>apt-get install php5 php5-xdebug libapache2-mod-php5 php5-mcrypt php5-cli php5-curl php5-gd
# Install your favorite editor for quick edits....
$>apt-get install vim

Optional (but useful tools)

WebMin server administration

apt-get install perl libnet-ssleay-perl openssl libauthen-pam-perl libpam-runtime libio-pty-perl apt-show-versions python
wget http://prdownloads.sourceforge.net/webadmin/webmin_1.610_all.deb
dpkg --install webmin_1.610_all.deb

When you want to mount an external CIFS filesystem (for example your NAS) install the CIFS utility package.

$>apt-get install cifs-utils

Mount a cifs remote file system:

$>mkdir /mnt/share
$>mount.cifs //SERVER/share /mnt/share -o user=USER,uid=1000,gid=1000
# Enter your password
# Install netbeans
$>apt-get install netbeans
# Install chromium-browser
Install chrome browser
$>apt-get install chromium-browser
# Install the Gimp
$>sudo apt-get install gimp
# Install VIM
$>sudo apt-get install vim

Setup WordPress installatie

For pretty URL’s to work make sure the rewrite module is enabled in Apache. You can do this with the WebMin tool; in Webmin goto “Servers”, “Apache Webserver”, select the “Global configuration” tab, select “Configure Apache modules”, check the “Rewrite” module.

Changes take effect immediately.

$>cd /var/www/
$>mkdir demo
$>cd demo
$>wget http://wordpress.org/latest.tar.gz
$>tar -xzvf latest.tar.gz
$>mv wordpress www
$>rm latest.tar.gz
$>cd /var/www
$>chown -R www-data:www-data .

Setup the MySQL database

$>mysql -uroot -pXXXXXXX
mysql>create database wp_demo;
mysql>create user 'wp_demo'@'localhost' IDENTIFIED BY 'XXXXXXXXXX';
mysql> grant create,drop,select,insert,update,delete on wp_demo.* to 'wp_demo'@'localhost';
Share