Using PHP to backup an entire MySQL server

This post can be used as a free way to use PHP to backup your MySQL server. The code below comes from a script I developed so that we could back up the server with the execution of a single PHP page. Due to the way the script works, it passes off most of the heavy lifting to the system using exec() function so that it doesn't have to run through the webserver. It does this by using the mysqldump client program that is installed on the MySQL server - The PHP script is just used as a trigger. This is especially helpful if a non-technical person needs to run a database backup, yet doesn't know how to use console commands.

A few notes:

• Each database is backed up in to it's own file and zipped up using gzip - make sure you have it installed. If not, you can always remove the zip command. 
• The database script also adds the date to the file name
• If you do not want to backup the entire database, just alter the database_list array with the names of the databases you want to backup, i.e.

$database_list = array('database_1', 'database_2');

Usage:

To use the script, all you have to do is modify the connection variables at the top and make sure your dump folder has the correct permissions.

1. I List databases using a little bit of PHP and add them to an array
2. Loop through the databases

The script:

set_time_limit(0);

$username = 'username';

$password = 'password';

$host = 'localhost';

/**

* Put your path here - Can be any valid path, but remember that you must have write permission on the folder

*/

$dump_path = './dumps/';

$dbc = mysqli_connect($host,$username,$password);

$result = mysqli_query($dbc,'SHOW DATABASES'); 

$database_list = array();

while($row = mysqli_fetch_array($result)) {

   $database_list[] = $row['Database'];

}

foreach ($database_list as $database) {

    $dump_name = $database.'_'.date('d-m-y');

    $dump_string = 'mysqldump --host '.$host.' --user='.$username.' --password='.$password.' '.$database.' > '.$dump_path.$dump_name.'.sql';

    exec($dump_string);

    $zip_cmd = 'gzip '.$dump_path.$dump_name.'.sql';

    exec ($zip_cmd );

}

mysqli_close($dbc);

Download
If you would like to download the script, you may get it here

Expansion
Another one of the benefits of using the exec() function is that it can easily adapted to perform scheduled, reliable backups using a CRON job. This is one of the reasons I used the date() function to append the backup name, so that you could have more than copy of the database in the same directory. One further modification to the script you could perform is to sub categorise your database backups so that they are easier to manage.

James

Everyday MySQL console commands and MySQL hints

PHPMyAdmin is a great tool, but due its web based nature it can sometimes fall a little short, especially when dealing with large amounts of data. Don't get me wrong, it's still very useful for browsing large data sets, but if you are looking to do imports, exports, adding indexes etc., then you really need to use the console or some other application.

To be honest, most of the commands here, you will find on many other websites, I just wanted to compile this list as a reference: All the following commands were ran using the console in BASH, but should work on Windows/Mac consoles.

Logging in with a supplied username and using password option:

mysql --user=your_username -p

Logging in with a supplied username and a supplied password:

mysql --user=your_username --password=your_password

Logging in to a different host with a supplied username and password:

mysql --host  host_address --user=your_username --password=your_password

List databases

SHOW DATABASES;

Select a table

USE table_name;

Add an index

When you have a million plus records, this can save so much time in your lookups. I had a table that 1.5 million records, using a single WHERE clause on a non-index field was taking around 1.9 seconds, adding an index reduced the time to 0.0005 seconds:

ALTER TABLE table_name add index (column_name);

Export a database to a file:

mysqldump --user=your_username --password=your_password database_name > name_and_path_of_sql_file_to_create.sql

Will use a modification of the above to show how you can use PHP to backup an entire database in a future post - you can read that here: backup up your MySQL server with PHP

Import a database on to a different host with a supplied username and password:

mysql --host host_address --user=your_username --password=your_password db_name < your_sql_file.sql

Hope this is of use to you.

James

Filtering date data in MySQL using WHERE against a date

In a previous post I described a method of using DATE_FORMAT to sort data and search against it. That post described how you can sort your data and then use a WHERE clause against it. It basically described a 2 step process, this is alternative and does it in 1 step. There are benefits to both methods and I leave it up to you to decide which way to go.

N.B. I am sure this method is on a million different websites, I only include it on my blog so that it has both versions.

The SQL:

SELECT * FROM your_table WHERE your_timestamp > '2010-10-09 00:00:00' AND your_timestamp < '2010-10-09 23:59:59'

The Explanation:

It's quite simple really; MySQL performs a comparison where your value in your DATETIME field is greater than the minimum reference and less than the maximum. In the case above it selects all dates from a specific day (2010-10-09), but in theory you could do it on many different ranges.

I hope this helps.

Search functions against DATETIME in MySQL [using PHP]

When you are using a database for any large scale application, it is always a good idea to make a note of when things happen, i.e. when a page was created or when a page was updated. The easiest way of doing this is to pass NOW() through your SQL to your database to add the date and time to a DATETIME field, whereby you end up with data looking like this in your table:

2010-10-13 18:32:45

2010-10-13 18:28:01

2010-10-13 18:29:17

In its raw form, the SQL for performing this function will look something like this:

SELECT * FROM your_table WHERE DATE_FORMAT(column_name, '%Y-%m-%d')= 'your_date'

The SQL statement works in two stages:

1. MySQL first formats the data into your prescribed format, in my case, this was YEAR (%y), MONTH (%m), DAY (%d).
2. Now that MySQL has sorted the data internally, it performs the comparison part of the statement against your formatted data using the WHERE clause.

Of course, the internal functions that MySQL provides performs the vast majority of the hard work for you. The key to using this solution is to provide your string in the same format as the data is stored. In my case, the string I needed to run a comparison on was a date, the time wasn't a necessary part of the clause, so I provided:

'2010-10-13'

So my SQL looked like a little bit like this:

SELECT * FROM my_dates WHERE DATE_FORMAT(my_date_time, '%Y-%m-%d')= '2010-10-13'
(Notice the hyphen as the separator, this may differ in your case)

%Y = 4 digit numeric YEAR,
%m = 2 digit numeric MONTH,
%d = 2 digit numeric DAY

There are many other options for date formatting, all of which can be found here on the MySQL website:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

I have also written a different method of extracting data using a date, you can find that here.

I hope this helps...

How to make Scheduled MySQL database backups using Windows

Had a bit of a hair raising issue the other morning with database problems. So I decided to write this post: There are a number of other options some paid, some free. But this is a free solution and works using free software that anyone can download. The more commercial software allows you to manage multiple databases easily, but if you need to backup your database on a budget or you only have a small number of databases on the same server then this will suit you fine. When I get round to it, I will write this tutorial for Linux.

Also this article assumes you will have your machine switched on when you set the scheduled backup to run.

The software:

1. Download and install MySQL Administrator Tools from mysql.com: http://dev.mysql.com/downloads/gui-tools/5.0.html

2. When you have installed the program, look in your start menu for the MySQL System Tray Icon - run that (if not already).

3. Right Click on the system tray icon -> Monitor Options -> Launch Monitor After Login

Setting up the MySQL Backup

1. Connect to your database using the supplied information
2. Set up the environment

Tools -> Options -> General Options

Check The Store Passwords Option
Set Password Storage Method = Obscured

Click Apply

This is so that your backup Choices can remember the details

Click Close

3. Add a stored connection. This can be done via the stored save Current connection option, but this normally doesn't work for me as the connection disappears from the folder on restart of the program. So for the sake of this tutorial we will be saving a brand new connection

Tools -> Manage Connections

Click New Connection
Under Connection - Add a name, i.e. the website name
Username: Enter the username for the database connection
Password: Enter the password for the database connection
Hostname: The hostname or IP address of your database

Click Apply and Close the dialog and then close the program. This has to be done as to schedule the backup properly you need to be logged in under a stored connection.

4. Now back at your main screen, click the backup button

5. Click New Project and the screen will become active with your database(s) on the left. - Enter a name in to the Project Name field

6. Click the database(s) you want to add to your backup by clicking on the 'greater than' arrow that points to the right - '>'and this will add the database to the backcup operation as well as all the tables within (default)

7. Once you have done this - click the Schedule Tab at the top

8. Click Schedule This Backup Project and then choose a location where your backups should be stored

9. The filename should be filled in automatically, so choose your own backup Execution options: Time, Frequency - i.e. daily, weekly...

10. Click Save Project

11. Click Execute Backup to test settings

12. Enter your windows password (if required)

Essential tools for PHP web developers

Essential tools for PHP web developers

I hope you enjoyed the book list, here are some of my recommendations for software to use when you develop your applications.

Editing Software
I use an excellent named PHPED IDE from NuSphere. It is very well priced and it has some brilliant features including intellisense and will pick up all classes, objects, methods, properties, variables and functions that are available to the page you work on. It also has support for CSS, Javascript, Perl and Smarty Templates. The installation also comes with a debug software and a few other tricks and whistles. I see it as an essential tool for anyone coding PHP on a PC. Find out more at nusphere.com

Primarlily I am a PC user, whether it be Linux or Windows, I like using PCs and my reasons go beyond this article. However I am not averse to new ideas or opinions and I have heard from many people that there is an editor called BBEdit by barebones sofware for OSX, unfortunately I have never tried it and cannot comment, but if you have the means, then please do so.

Browsers

I have chosen Firefox as an essential tool for a number of reasons. It has great support for CSS, is secure and is updated on a very regular basis. However, these are not the only reasons for me choosing this program. The other reason is that is has support for add-ons (little additions written by 3rd part developers) which I now consider to be essential.

Here are my list Firefox add-ons that I use:

• Firebug - great for looking at problems and properties of the DOM or Javascript. Has an excellent JavaScript console which is brilliant for debugging scripts. Has so many different options that it would be pointless to go through them here. Find out more here
• Web Developer - Adds new menus so that you can view CSS properties, JavaScript, Image Info... Again too much to list, so please go here for more info.
• Live HTTP headers - Allows you to view header information as it happens. Great tool for looking at information sent by the server. Find out more here
  

Internet Explorer - The other browser I must add here is IE. It is not the best browser in the world, but the market place deems it to be essential as it is still the dominant browser on the market. Your client based scripts, HTML and apps could many great things but if they do not work in IE then they are not going to gain popularity. In fairness to Microsoft they have done their best to iron out many of the CSS problems that has caused issues in the past. I tend to develop my apps using FF as the main browser. Once I'm happy with a page or the area I have been working, I always give it a test in IE to make sure it all renders properly.

• If you really do not want to use Internet Explorer, then there is an option for those with a love of Firefox. You can actually download an addon that will simulate IE within FF. To find out more, please go here.

The obvious choices:

You of course must have an installed PHP installed on your system. With most Linux distributions, the packages can be added at installation or later. You will also need MySQL installed as well, again if you do know how to do this or do not feel comfortable adding to a Linux system it can be tricky. In terms of the Operating System, PHP is a very versatile piece of software and can be installed to be used with either Apache in Linux or on Windows or it can be even installed on IIS in Windows. It is your choice to make and should be made according to your ability at using these Operating Systems.

Unfortunately for people who want to use PHP or MySQL in a Linux installation is beyond the scope of this article. However for those who want to use Windows, there is a simple alternative called WAMP and that can be downloaded here. It installs and configures all the things you need and can get you up and running very quickly. The components that can be installed on Windows include Apache Web Server, MySQL and PHP (WAMP) and once added, you do have a fully working web server.

Reference Manuals/Books

• PHP.net - the PHP Manual - complete reference to everything in the PHP Language
• W3 schools - Excellent reference for HTML and JavaScript properties/methods
• JavaScript Bible - great JavaScript reference book
  
• PHP OOP solutions - still a great book (chapter 2 especially) to refer to

Hope these recommendations help and happy coding...

My PHP development Book List (Advanced)

Advance your PHP knowledge...

I hope you all enjoyed my recommendations on PHP books for beginners. I now give you my list for more advanced books that will take you further into the world of PHP programming and development.

1. I touched upon this book in the beginners book list, but feel like it should be mentioned upon more here. Advanced PHP 5 by Larry Ullman would be the first book I would start to read if I was to go in to more Advanced PHP 5 programming and the reasons are as follows. It has great reference chapters on XML, Ajax and OOP - all of which I would recommend you move on to with more detailed books (some of which are listed below). Each chapter is (relatively) light weight and will give you a good grounding in the subject.

2. The next book I would take a look at is PHP Object-Oriented Solutions by David Powers for friends of Ed. This is a brilliant book, I didn't learn OOP from this book, but from looking at it retrospectively I wished I had. It's great book to learn from and (chapter 2 especially) works as a great reference manual should you need it.

You quickly realise when you are building your applications that you often have retype lots and lots of code. OOP is based on code reusability and modularity so that once you have classes in place, the hard work is done. OOP is also good for those who have to keep one eye on the profitability of their business i.e. free lance developers. With OOP in PHP, you can use common classes so that your time can be utilised in a more efficient manner.

I recommend OOP to all people learning PHP, it may take you more time in the early stages when you are designing and building your class, but once the classes are finished you will find your projects running far smoother. Getting one's head around OOP can be tricky so, unless you can do it in another language I would leave this until you really feel comfortable with the language before you proceed.

3. To learn Ajax with PHP I would use Ajax And PHP Building Responsive Web Applications by Cristian Darie et. all. If you managed to master many of the concepts in JavaScript and PHP then this shouldn't be too hard a book to digest. Not only does teach you about Ajax and how to use it with PHP and MySQL, it also gives you real life examples of concepts that can be used in most modern websites.

N.B. I would suggest that you work through at least some of the chapters in the previous book as this Ajax book does use PHP OOP concepts.

Ajax is a brilliant concept and again, it is something that I would recommend all developers to at least try. Not only does it add that all important visual flair to pages, it also has other benefits too. If server loads are an issue, either through the amount of data or instructions they can handle it helps as only the parts of a page that are needed are sent from the server, rather than the server having to send the whole page again.

Dreamweaver - an excellent way to start your web development career

My love/hate relationship with Macromedia/Adobe Dreamweaver

I have since moved on from Dreamweaver but for some jobs it is still a very useful tool. A lot of pro developers hate Dreamweaver and in many cases they have a point. However like many things in life, when people get an idea or an opinion it can be very hard to change. I feel like I am an open minded person that believes in using the right tool for the right job.

Dreamweaver is an excellent program created by a great time of programmers and designers. It has a fantastic interface and it makes site creation available to the masses. When I first started using DW I thought the program was amazing and I was very impressed with how well it created dynamic pages with such a minimum of effort. Also it gives a person a very good look at how server pages work along with some basic interaction with databases and really does ease them in to the whole server side world of the web. It has been created to use many different models including ASP[.net] with MS SQL Server or Access; PHP with MySQL on different servers including IIS and Apache and is therefore a very adaptable piece of software.

Dreamweaver also allowed you to use the WYSIWYG side that allowed you to combine the design and the server side parts all on one page. This was indispensible for a person who had some HTML experience but hardly any server coding experience.

OK, on to the bad points. For people who can code their own apps in a server side language this piece of software will no doubt infuriate them. It writes a lot of code (sometimes needlessley) and creates huge amounts of script to do some very simple operations. This is Dreamweaver's main issue for seasoned developers and like I said it is understandable, but if used sensibly Dreamweaver can still have its place in a developers arsenal.

It is also is quite inflexible with the way creates files in to its structure that it uses for database connections - but this is only a minor issue.

I mentioned on the blog front page that I hope to use this blog to help a business be more profitable. Dreamweaver fits in to this idea perfectly. As a developer, a significant amount of time can be used on the inital stages when design meets the site structure. You can create classes, functions and all manner of time saving techniques to try and make site development more streamlined but if you want to speed up this stage I still stick by Dreamweaver. One of Dreamweaver's finest tools is its ability to help you create CSS and how it can fit in to your site development. CSS in Dreamweaver will allow you to (when your elements are in place) to add an atribute and it will allow you to see immediately how it affects the site's layout rather than loading and refreshing in a browser. If you are creating a site that uses a non-standard layout, this is a fantastic way to do things and will no doubt help you at many stages in the design/structure. Of course you need a fairly good understanding of CSS, but once you have that - page layout becomes a whole lot easier.

PHP - A New Start in my development

Using PHP to develop websites

So here I was, a brand new book (PHP 4/MySQL by Larry Ullman for Peachit Press) in front that promised me how to learn PHP and create dynamic websites. I had, of course already created dynamic website using classic ASP but I wanted to use PHP and use it well. Many of the concepts mentioned early on in the book just reiterated how to do things that were already common to most programming languages. The common if, else, echo and so on and so forth and these chapters were a breeze and I sailed through the first few chapters in a matter of hours.

However when I started to meet new ideas and concepts I was starting to slow down as the new information had to absorbed and understood for future use. Things like arrays and functions were, though not ultra complicated were new to me as I had learnt ASP in such a disjointed way that this new approach was very heavy going.

With functions in PHP there were new ideas and concepts that had to be understood - like variables within a functions, parameters and the idea of whether or not it is even worth writing the function in the first place! When it came to arrays and using them in PHP, this was such a refreshing change. I had used arrays in ASP but they were, I felt a little clunky, with PHP they feel flexible and you can manipulate them in so many ways (thanks to PHP's inbuilt functions).

I have to say Larry Ullman is an excellent teacher and his books are very, very good and without him I think learning PHP would have been a lot more difficult, so thanks go out to him. I have bought many of his books and it's a testament to his skill at writing technical text books that I keep buying updated versions as there are so many useful bits of information in them.