Setting up Ubuntu 12.04 LTS with apache, php, mysql, phpmyadmin, webmin from scratch


(NB This guide is subject to change and will evolve with every bit of spare time I have to improve it. Please also note, this is what I did and is not necessarily what you should do. If I have made any errors or something similar please let me know.)

Setting up a Ubuntu 12.04 server via SSH (PUTTY)
General notes for running ubuntu at command line.
-Commands are case sensitive, adduser is not the same as addUser
-‘vi’ editor you cannot use the num pad
-In VIM you need to press i to enter edit mode.
-Finding files in com: find / -name (file you are looking for)

IMPORTANT – make a note of all the passwords you choose in a file somewhere on your local machine, and keep it safe!



Index (the following used to link, but for some reason they broke so are now for reference):
Step 0 – Install Ubuntu 12.04
Step 1 – User security, changing the root password
Step 2 – Updating and upgrade
Step 3 – Installing VIM
Step 4 – MySQL install
Step 5 – Apache2 install
Step 6 – PHP5 install
Step 7 – Testing the PHP5 install
Step 8 – PHP mods
Step 9 – phpMyAdmin install
Step 10 – Configure .htaccess
Step 11 – Log program install
Step 12 – Webmin install
Step 13 – Hosts file correction
Step 14 – SSMTP email forwarding
Step 15 – Adding a new username
Step 16 – Adding SUDO to new username
Step 17 – SSH security
Step 18 – Adding user specifically for sftp
Step 19 – Importing a premade database
Step 20 – php.ini work
Step 21 – Key authentications
Step 22 – Firewalls with IP-tables with Fail2Ban, important!
Step 23 – APC install
Step 24 – Set Cron going to backup your MySQL database


Step0 – Install Ubuntu 11.04 onto your server from a windows local machine (back to index)
This guide is assuming that you are on a server, in which the ubuntu os is installed automatically. However if you are installing it your self from cd, then simply follow the on screen instructions the cd will provide.

(Complete step 2 before completing this step)

Once installed, if SSH is not on your server then install it now:

apt-get install ssh openssh-server





If not already done, make sure that the ‘a-record’ of the domain name you own is pointing to the ip address of your server (contact your domain name provider if you don’t know how to).

You will also need to download PuTTY. With putty, you will be able to access your server at command line remotely, ie from your home PC. It is like a virtual window onto your server, once you download it, there is no install required, it is a stand alone .exe file.


Step1 – User Security, changing the root password (back to index)
The default username is ‘root’ (if you installed yourself then it is what ever you named it). This user is the administrator user, the one who has all rights to everything on the server.
The password for the ‘root’ user needs changing first from the auto-generated one to one of your choice (again if you installed the os yourself, then this isn’t needed as no-one else can possibly know your password, however read through all of Step1 to get some background knowledge).

1 – First log into the server via PUTTY or any other SSH client with the IP address provided by the hosting company (enter the IP address in the IP address box, and port 22 in the port number box). Ensure that the SSH radio button is selected and click ‘Open’ at the bottom.
2 – Once a connection is established your server should be asking for a username. Enter ‘root’ as the username (the screen will look like “login as: “)
3 – The server will then ask for the password. Enter the password provided by the host and press enter.
4 – If you entered your password and username correctly you should now be viewing the command line of the server (a GUI interface is available with something like ‘Webmin’ or ‘CPanel’ but should be installed later).
5 – Now logged in, change the root password, type:

passwd

then press enter, the command line will then ask for a new password. The command ‘passwd‘ is a function that will change the password of the user you are currently logged in as. After entering the password press enter and you will be prompted to re-enter the new password, do so and press enter. The ‘root’ password has now been changed, be sure to note it down somewhere!


Step2 – Updates on a fresh install (back to index)
The server is essentially a computer. It requires a program to allow users to view html files on it, a program to read and use php scripts…etc. Linux works in a slightly different way to windows, it has a list of available files and programs it can install directly from the net, it is therefore essential that after a fresh install of ubuntu 11.04 to update this list to allow you to download and install the correct programs. In Step2 you updated the list of locations, now you need to update and upgrade the system.

1 – Still logged in as root run the following command:

apt-get update

2 – After the server has finished updating, you need to upgrade, run:

apt-get upgrade




Step3 – installing VIM editor(back to index)

nano is your best friend editor. 

Step4 – MySql install (back to index)

1 – Run:

apt-get install mysql-server mysql-client

2 – After running apt-get to install mysql-server and mysql-client you will be asked if you wish to continue, press y and then enter to continue.

3 – You will then need to provide a password for the MySQL root user. Create a long and secure password in the file you are storing all your passwords in, then enter it into the box which has popped up. After entering the password once, it will then ask you to re-enter the password.

4- Watch the screen flicker as the server does the rest of the work. It may pause and take a few mins sometimes, don’t press anything.

5 – After the installation has finished, the bottom line reading ‘root@yourservername’ with the cursor solid green) you will


Step5 – Apache2 (back to index)
Installing Apache2, this is what enables the server to send out html pages to people trying to access your site:

1 – Still logged in as root, run the following command:

apt-get install apache2



2 – after it has finished, direct your browser to your domain name and you should see the Apache2 placeholder page (It works!). If you don’t have a domain sorted yet, just point your browser to the ip address of your server.

Step6 – PHP5 install (back to index)
1 – To install the PHP program onto your server

apt-get install php5 libapache2-mod-php5


After it has installed you will need to restart the program Apache2 on the server to make apache aware of the new PHP program it can use, to do this type:

/etc/init.d/apache2 restart



Step7 – Basic programs, testing the PHP5 install(back to index)
To check that the PHP program is installed correctly on the server, create a simple php script and see what it looks like from you browser. A good one to write would include the phpinfo() function as this will also illustrate what settings are set at the same time. We will create the file in the VIM editor from PUTTY.

1 – var/www is the public file where all internet access is default directed to, so this is where we will be creating the file. To create the file enter the following (as the file does not already exist it will create it for you):

vi /var/www/info.php



2 – Once in the new file, press ‘i’ to enter edit mode.

3 – Create a php file as normal with the basic phpinfo() function included.

4 – Now save the file and exit by pressing ‘Esc’ to exit insert mode, type ‘:wq’ to write the changes to disc and quit the program.

5 – Now check that all is working by directing you browser to http://whatevertheipaddressis/info.php You should see the standard php info page displaying all the settings of your fresh php install.


Step8 – Basic programs, adding the mysql support for php5 and a few others(back to index)
To get MySQL support in PHP, we can install the php5-mysql package. It’s a good idea to install some other PHP5 modules as well as you might need them for your applications. You can search for available PHP5 modules like this:

apt-cache search php5

This will display a list of the available php modules you can add. If this is the first time you have set up your own server then the chances are you might not know what a lot of the modules are. You can check by using the php manual page.

1 – Without getting too bogged down in which mods to install, here is a pretty full list which should cover most of your needs. Enter the following command:

apt-get install php5-mysql php5-curl php5-gd php5-idn php-pear php5-imagick php5-imap php5-mcrypt php5-memcache php5-ming php5-ps php5-pspell php5-recode php5-snmp php5-sqlite php5-tidy php5-xmlrpc php5-xsl

2 – Again restart the Apache program to make it aware of the updates you have just installed to PHP5:

/etc/init.d/apache2 restart


3 – An optional extra which you may be using might require zlib. This requires quite a lot of space compared to the previous list so only add if you need it:

apt-get install zlib*


4 – Again restart the Apache program to make it aware of the updates you have just installed to PHP5

/etc/init.d/apache2 restart


5 – Now you can check that all the new modules you added are installed and working by viewing the php file you created earlier. Scroll down to the modules section, you should now find lots of new modules there, including the MySQL module.


Step9 – Basic programs, phpMyAdmin(back to index)
The last basic program to install is phpMyAdmin, the easy to use database management setup for managing your databases from a web browser instead of at the command line.

1 – Run:

apt-get install phpmyadmin

2 – You will see the following questions:
a) Web server to reconfigure automatically. Choose – apache2
b) Configure database for phpmyadmin with dbconfig-common? Choose Yes
c) You will then be asked for the MySQL password you choose previously.
d) You will also then be asked to create another password, this is for the ‘root’ login for phpMyAdmin. Note this down! It should be different to the root password for mysql.

3 – After installed restart Apache2

/etc/init.d/apache2 restart


4 – To make phpMyAdmin work, the ‘apache2.conf’ file needs to allow phpMyAdmin to run. This is done by editing the apache2.conf file:

vi /etc/apache2/apache2.conf


5 – Add the following line to the end (case sensitive, and remember to press i for edit mode),

Include /etc/phpmyadmin/apache.conf

6. Press ‘Esc’ then write and quit (‘:wq’)

7. Restart apache,

/etc/init.d/apache2 restart


8 – You should now be able to login to phpmyadmin, with root and the mysql password you have stored somewhere:
http://www.yourdomain.com/phpmyadmin/

9 – You will notice that the link to the login of phpmyadmin is pretty basic. You should hide the default url link for security, and change it to some obscure alternative:

vi /etc/phpmyadmin/apache.conf

10 – Change the following line:

Alias /phpmyadmin /usr/share/phpmyadmin

To something like (where : ‘secreturllogin’ is something secret of your choice like ‘osdvkjb765vlkj‘)

Alias /secreturllogin /usr/share/phpmyadmin

11 – Save and exit the file (:wq). You should now be able to access the phpMyAdmin login page from http://www.yourdomain.com/secreturllogin/


Step 10 – Apache configuration, .htaccess(back to index)
The default install configure of Apache is to not allow .htaccess files within any of the web directories to override the default value found in the apache2.conf file. This is usually not desirable, and can be changed
(https://help.ubuntu.com/community/EnablingUseOfApacheHtaccessFiles).

1- you need to edit /etc/apache2/sites-available/default:

vi /etc/apache2/sites-available/default


2 – Look for a section that looks like this:

Options Indexes FollowSymLinks MultiViews
AllowOverride None
Order allow,deny
allow from all
# Uncomment this directive is you want to see apache2’s
# default start page (in /apache2-default) when you go to /
#RedirectMatch ^/$ /apache2-default/


Where it says AllowOverride None, change the None to All, meaning the line should read:

AllowOverride All


3 – All the .htaccess files within any of the web directories should now work fine as they can override the default settings.

(although they say that the htaccess is generally for webhosts, and that if one has root access to the server one should edit file permissions instead, this can lead to complications when certain directories are required access but others are not, therefore .htaccess I feel is not a bad thing, and is quick and secure)

Step 11 – Install log programs(back to index)
This will install a program that creates logs of errors… etc:
1 – apt-get install sysklogd

Step 12 – Optional: Install webmin, a gui interface to the server(back to index)
You may or may not be happy to know that webmin got a whole load easier to configure
1 – Get all the rel. programs

apt-get install perl libnet-ssleay-perl openssl libauthen-pam-perl libpam-runtime libio-pty-perl apt-show-versions python

2 – Now you need to dpwnload webmin deb package using the following command

wget http://prdownloads.sourceforge.net/webadmin/webmin_1.670_all.deb

3 –  Install downloaded package using the following command

 dpkg -i webmin_1.670_all.deb

4- Similar to phpmyadmin, to increase security, change the default port from 10000. Change the top port number. Antyhing under 1000 is good. Change the port number on the first line and save the file.

vi /etc/webmin/miniserv.conf

5 – Changing the port number usually needs a server restart.

reboot

6 – After restarting:

/etc/webmin/stop 

/etc/webmin/start 

You should now be able to access the webmin interface with ‘:784’  (where 784 is the port number you picked)

Step 13 – Correct the host file(back to index)
1 – Make sure the host file contains the correct information:

vi /etc/hosts

2 – It should look like:

127.0.0.1 localhost localhost.localdomain
31.123.567.999 server01.mydomain.com server01

(where localdomain = your domain name)


Step 14 – SSMTP an email forwarding program(back to index)

1 – install ssmtp:

apt-get install ssmtp

2 – configure the following file:

/etc/ssmtp/ssmtp.conf

a) comment out all existing running commands
b) add the following lines:

root=master@mydomain.com
mailhub=server01.mypophost.co.uk:25 <<(default No. is 25) rewriteDomain=mydomain.com
hostname=server01.mydomain.com
UseSTARTTLS=YES
AuthUser=email login provided by email host
AuthPass=*******
FromLineOverride=yes


Their meanings:

root=postmasters email address
mailhub=the address of the server where the pop box is
rewriteDomain=the domain of your site
hostname=your server name and domain name
UseSTARTTLS=YES
AuthUser=the email username (the name you use for outlook)
AuthPass=*******
FromLineOverride=yes


NB: port 25 may be blocked, you may require a different port, contact your email host.
NB Some smtp servers need to get the php.ini to point to a specific location on the server to enable php mail() to work: ssmtp does not. Leave it empty.

3 – Now test that ssmtp is working properly by sending a mail to your email address (eg, you@hotmail.com)

ssmtp you@hotmail.com

After pressing enter the cursor will jump down a line waiting for some more instructions, here you can enter more text for the email but for now, just press Ctrl + D to send the empty email.

You should now receive an email from your server as root.


Step15 – Adding a new username to the server(back to index)
With all the programs installed it is now time to add some security to the server. In a similar way to windows requesting that you add another user and not use the administrator account for logging in, Ubuntu does the same. The idea is a hacker would go straight to the ‘root’ username, to get into the system they need to get correct the username and the password. What we are going to do here is add another user with admin rights then prevent access to the system with the username ‘root’. That way a hacker would need to know both the unique username and its password.

1 – Still logged in as ‘root’ we are going to use the command ‘adduser’, and we are going to add a username ‘john’. Enter the following command:

adduser john

2 – You will then be prompted for a password, which you will have to type in twice

3 – You will then be prompted for the users details, like name, address…etc (These can be left empty if you wish)

4 – The last prompt will be ‘Is this information correct? [Y/n]. If it is press Y and enter.

Step16 – Adding ‘sudo’ rights to the new username(back to index)
After Step15 you should now have have a new user on the server named ‘john’. We are now going to give ‘john’ the ability to do everything that the root user can.

When logged in as root you have the ability to change anything on your server, when logged in as anyone else you do not. However you can allow users to use a function called ‘sudo’ which will allow that user to do any action the root user can do. Which users can and cannot use sudo is controlled in a file called ‘visudo’.

1 – Still logged in as ‘root’, type:

visudo

2 – Move the cursor to the end of the file with the arror keys and enter the following line, save and exit:

john ALL=(ALL:ALL) ALL

3– the user ‘john’ now has the ability to use sudo. We will cover how exactly to use sudo later.


Step17 – User Security, preventing root login and changing the SSH port(back to index)
Now that the user ‘john’ can use sudo, it is safer to prevent the root user from logging in.
The default SSH port number is 22. For security, it is best to change it to a different number.

Preventing root access and changing the port is set within a file called ‘ssh_config’ and is found in the following ‘/etc/ssh/’, run the following command:

vi /etc/ssh/sshd_config

NB this is a configuration file of the ubuntu server, be carefull.


2 – When you login to the server via putty, you are using a SSH connection, through a port number. The file you have open determines which port number to allow SSH connections through. The default is port 22. For security, change this to any integer between 1025 and 65536.

3 – Now set the root access to none. Move the cursor down the where it says ‘PermitRootLogin’, where it now says yes, change this to ‘no’.

4 – Now with the port number changed to 22, and the root access denied, before saving you need to allow other users access to the server via an ssh connection: move the cursor to the end of the file press enter for a new line and type:

AllowUsers john

(to add any other users here simply seperate each username with a space)

6 – Save and quit the file, and make a note of the port number you altered.

7 – The last step to enforce the changes is to restart the system. Still logged in as root type:

reboot

The SSH connection will now be terminated. Close the putty program.

8 – Now log back in via PUTTY but change the port number to whichever you changed it to in the sshd_config file.

11 – Try loggin in via root, nothing should happen.

12 – Restart PUTTY, and login as ‘john’ and you should be logged in.

As ‘john’ has sudo abilities, ‘john’ can do anything the root user could do, for example:

apt-get update

First try running the update command just logged in as plain ‘john’. You should see a ‘permission denied’ message. To use the sudo command, all you have to do is prefix any command with ‘sudo’:

sudo apt-get update

Now the command should run fine with no problems.

There may be times in the future where you wish to do lots of actions with user ‘john’ requiring sudo, instead of typing sudo before each command you can enter a sort of constant ‘sudo’ state by running:

sudo su

After entering just ‘sudo su’ you will need to enter your password, and then will have a constant ‘sudo’ state and will not have to prefix any commands with sudo.

Step 18 – Adding a user with no root access but designed for only SSH connections(back to index)
To use a standard ftp program to use ftp to upload files to your new server is one option… however it requires a whole new program installing (vsftp). Filezilla for example can just as easily use sftp which doesn’t require any further programs as the ssh is already there.

Problem is, you don’t really want someone being able to add files to your server with filezilla on an account that has root access to your server. That could be a disaster if they decided to do something they weren’t meant to. So, create one more account allowed to connect via ssh but without sudo ability.

1 – Add a new user like ‘sftpUser’

adduser stfpUser


2 – add this new user to the sshd_config file as previously done with ‘john’

3 – Now, this user needs to be able to write to the var/www dir where your site will be stored. The easiest way to do this is through webmin.
a) Go into webmin with the ‘john’ username.
b) Navigate your way to ‘others’ then ‘filemanager’.
c) Once file manager is loaded in you browser, get to the dir /var/www.
d) In the right hand window single click on the ‘www’ dir to highlight it, then click the ‘info’ button at the top.
e) From here you can change all the chmods and groups… etc. change the user and the group to be ‘sftpUser’.
For the www directory, set the owner to the sftpuser and the group to www-data. www-data is typically the name of the apache user which will also be the php user. Doing this will allow you php application to write files to the server which is essential for photo uploads… etc etc.
Set the permissions to 0775. This will allow the owner and group to write to the directory bu not allow the general public to.

4 – Now you will be able to use filezilla to connect to the www dir with the sftpUser you just created. However this must be done with an sftp connection:
a) click file > sitemanager
b) then click new site.
c) give your site a name (this is just a ref name for within filezilla)
d) Type in the ip address of the server in host
e) enter the ssh port number in port (remember, you have changed this to something else)
f) in ‘Protocol’ select the sftp option
g) in the ‘Logon Type’ select ‘Ask for password’
h) In the user, enter the sftpUser
I) Click ‘Connect’, then you will be asked for the password, enter it: JOB DONE. You are now connected with Filezilla to your server over an sftp connection, you can now upload you site.


Step 19 – You may have a db backed up you wish to install onto the server(back to index)
If you have previously backed up you entire database to a .sql file, the best way to upload this to your new server is to add the db via the command line, especially as the max upload limit can be quite small and tricky to change. First you will need to sftp the .sql file onto your server somewhere, then use mysql at command line to read the .sql file and import the db from it.

1 – With a user setup with access to the var/www directory. Connect to the server via filezilla on an sftp connection. Transfer the sql backup file to a location you can remember and that the sftpUser has access to obviously.

2 – Connect to the server via PuTTY and run the following mysql command:

mysql -u username -p < location/the.sqlfile

(the username (root) and password are the mysql credentials, not you server credentials. The file location is where ever you placed the .sql file, eg : /var/www/backup.sql
You will then be asked for the password. Again this is the mysql root password.
This line will not only re create all the tables, but will even create the database too (assuming that the backup you created on your other server was of the whole db and not just a group of tables)

3 – The best way to test the database is to see if your php scripts are working. Once you have uoploaded your php scripts to the var/www folder you will then need to create any additional usernames to the database that the scripts are programed to use, the easiest way to do this is from within phpMyadmin.


Step 20 – php.ini configurations(back to index)
If you need to make changes to the php.ini, there are two files that can be changed:
1) apache2/php.ini
2) cli/php.ini cli is command line interface.

So the cli/php.ini should only be in effect when running a php script from the “command line”.
The apache/php.ini is the one you want for php web pages.


Step 21 – Key authentication(back to index)
http://www.rackspace.com/knowledge_center/index.php/Basic_Cloud_Server_Security

One effective way of securing SSH access to your cloud server is to use a public/private key. This means that a ‘public’ key is placed on the server and the ‘private’ key is on your local workstation. This makes it impossible for someone to log in using just a password – they must have the private key. This consists of 3 basic steps: create the key on your local workstation, copy the public key to the Cloud Server, and set the correct permissions for the key.
The first step is to create a folder to hold your keys. On your LOCAL workstation:
mkdir ~/.ssh
That’s assuming you use Linux or a Mac and the folder does not exist. Follow the link to read a detailed article for key generation using Putty for Windows.
To create the ssh keys, on your local workstation enter:
ssh-keygen -t rsa
If you do not want a passphrase then just press enter when prompted.
That created two files in the .ssh directory: id_rsa and id_rsa.pub. The pub file holds the public key. This is the file that is placed on the Cloud Server.
The other file is your private key. Never show, give away or keep this file on a public computer.


Now we need to get the public key file onto the Cloud Server.
We’ll use the ‘scp’ (secure copy) command for this as it is an easy and secure means of transferring files.
Still on your local workstation enter this command:
scp ~/.ssh/id_rsa.pub demo@123.45.67.890:/home/demo/
When prompted, enter the demo user password.
Change the IP address to your cloud server and the location to your admin user’s home directory (remember the admin user in this example is called demo).


OK, so now we’ve created the public/private keys and we’ve copied the public key onto the Cloud Server.
Now we need to sort out a few permissions for the ssh key.
On your Cloud Server, create a directory called .ssh in the ‘demo’ user’s home folder and move the pub key into it.
mkdir /home/demo/.ssh
mv /home/demo/id_rsa.pub /home/demo/.ssh/authorized_keys
Now we can set the correct permissions on the key:
chown -R demo:demo /home/demo/.ssh
chmod 700 /home/demo/.ssh
chmod 600 /home/demo/.ssh/authorized_keys
Again, change the ‘demo’ user and group to your admin user and group.
It may seem a long set of steps but once you have done it once you can see the order of things: create the key on your local workstation, copy the public key to the Cloud Server, and set the correct permissions for the key.


Because keeping the SSH service on the default port of 22 makes it an easier target, we’ll change the default SSH configuration to make it more secure:
nano /etc/ssh/sshd_config
The main things to change, check, and add are:
Port 22 <— change to a port of your choosing
Protocol 2
PermitRootLogin no
PasswordAuthentication no
UseDNS no
AllowUsers demo
The settings are fairly self explanatory but the main thing is to move the server from the default port of 22 to one of your choosing, turn off root logins, and define which users can log in.
NOTE: the port number can readily be any integer between 1025 and 65536 (inclusive), but should be noted for reference later when any additional listening processes are setup, as it will be important to avoid conflicts.
PasswordAuthentication has been turned off as we setup the public/private key earlier. If you intend to access your Cloud Server from different computers, you may want leave PasswordAuthentication set to yes. Only use the private key if the local computer is secure (i.e. don’t put the private key on a work computer).
Note that we haven’t enabled the new settings – we will restart SSH in a moment but first we need to.


Step 22 – IP-Tables (back to index)
So the concept here is simple, ban the bad guys from accessing your server. Built into the Linux kernal is something called IP Tables, they are quite complex. Essentially they do things with the incoming and outgoing data to and from the server.

Managing a servers iptables manually is a little bonkers, there are millions of ip addresses and millions of hackers.. so.. what do you do?

There is a nice program named ‘fail2ban’. Install this program and it will automatically keep your iptables up to date with the ban list. Essentially fail2ban will ban ip’s from accessing your server after a number of failed login attempts. EG some wise guy decides to brute force attack your ssh port (because he somehow figured out the port you changed it to).. but after 3 failed attempts fail2ban records the ip address in the ip tables and banthe user from trying to login to that port for 600 seconds.

The number of failed attempts are set in the config file as are the ignore ips and lock out times.

So to install enter the following:

To edit the conf file, it live here:

In the conf file you will see that the base values you might be interested in are at the top and look like this:

[DEFAULT]

# “ignoreip” can be an IP address, a CIDR mask or a DNS host
ignoreip = 127.0.0.1/8
bantime  = 600
maxretry = 3

 If you change any settings make sure you restart the fail2ban:

/etc/init.d/fail2ban restart



Step 23 – APC Install (back to index)

APC (Alternative PHP Cache). APC is a free and open framework which caches and optimizes the intermediate code generated when interpreting PHP scripts. This improves performance since the PHP scripts don’t have to be interpreted every single time they are run. You can think of APC as compiling your PHP scripts into small executable.

If you intend to use any frameworks such as symfony this is highly recommended. Simply run:

apt-get install php-apc





Step 24 – Set Cron going to backup your MySQL database(back to index)

Creating an auto backup of your mysql database is important. There are a few premade scripts out there that you can download and configure to do this for you, however I found it better (after trying out a few availabilities) to simply write one from scratch… it’s pretty simple.

This method uses a php script, a table in a mysql database and crontab (a deamon tool for automatically running a script on your linux server).

You most likely will not want to create and indefinite number of database backups. This method creates one backup a day for seven days, on the eighth day the system will overwrite the oldest, and on the ninth overwrite the oldest again… so on and so on.

The MySQL table:
You can if you want figure write the table out by hand (however I cannot for the life of me think of a single reason to do this when phpMyAdmin is available). The table can look like this (you may also want to add this table to the database you are backing up, so if something does go wrong in the future you will have a record of when all the backups were taken)

Field 1
name – ID
type – INT
AutoIncrement
Primary

Field 2
name – name
type – TINYINT

Field 3
name – date
type – DATE



The PHP script. Place this php script out of the public root. Maybe something like /var/www_private/db_backup/

<?php

//setting the login details for the server
$db_username = “username”;//ensure this username has the privileges to mysql dump and insert
$db_password = “password”;
$db_hostname = ‘localhost’;
$db_database = ‘database_to_backup’;

//first log into the database
$db_server = mysql_connect($db_hostname, $db_username, $db_password);

//selecting databse
mysql_select_db($db_database, $db_server);

//now get the latest entry in the table ‘database_backup_info’
$query_latest_entry = mysql_query(“SELECT * FROM database_backup_info ORDER BY ID DESC LIMIT 1 “);

//if there are results in the table
if(mysql_num_rows($query_latest_entry)>0)
{
//returning the row
$query_latest_entry_row = mysql_fetch_assoc($query_latest_entry);

//now determine the new file number based on the last entry
if($query_latest_entry_row[‘name’] + 1 <= 7)
{
//the new name is the last plus 1
$new_name = $query_latest_entry_row[‘name’] + 1;
}
else
{
//the new name is just 1
$new_name = 1;
}

//insert the entry into the db
$query_insert_new = mysql_query(“INSERT INTO database_backup_info (ID, name, date)VALUES (NULL,’$new_name’,CURDATE())”);
}
//if no results then this will be the first entry
else{
//setting the first name to 1
$new_name = 1;

//insert the entry into the db
$query_insert_new = mysql_query(“INSERT INTO database_backup_info (ID, name, date) VALUES(NULL,’$new_name’,CURDATE())”);
}

//setting the file and path for the database backup
$db_backupFile= dirname(__FILE__).’/sql_files/’.$new_name.’.sql’;

//the command to dump the database
$command = “mysqldump -u$db_username -p$db_password -h$db_hostname $db_database > $db_backupFile”;

//running the command in shell
system($command, $result);

?>




The next bit is to get the php script to run by automatically at set times… crontab. Crontab is pretty straight forward once you know how (which is always the case I suppose).
First, log into pUTTY with sudo su.

Crontab has a list of schedules which you can add to. To access the list enter the following into the cli (command line):

crontab -e



Now unless you have used crontab before on your server you will be asked which editor to use. Pick one and proceed (vim or nano, 1,3,3…)

Now to get your script to run once a day simply enter:

@daily /usr/bin/php5 /var/www_private/db_backup/backup_generator.php



There are two sections to this: The first section tells cron to use php5, the second says which file to use.

Of course you can also use Webmin to do this too. Just go to ‘system > Scheduled Cron Jobs’ then click ‘Create a new scheduled cron job’.
1 – Pick a user to run the job as.
2 – Enter the command into the box labelled ‘Command’:

/usr/bin/php5  /var/www_private/db_backup/backup_generator.php     

3 – Select ‘Daily(at midnight)’ in the simple schedual.
4 – click create.
5 – Check the cron is working by going back into the job, and clicking ‘run now’ at the bottom. You should now have an entry in your info table you created earlier. And a .sql file in the directory sql in the directory your php script is sitting in.

The backups can get more complex if your database is huuuge… for example the php.ini might have the max run time of a script set to a shorter amount of time it takes for the system to make a back up of your database.

You may find that you need to split your database into 2parts.

You may wish to compress your backups…abase