10 Ways to Automatically & Manually Backup MySQL Database

MySQL is one of the most popular open source database management system for the development of interactive Websites.




If your site stores its sensitive data in a MySQL database, you will most definitely want to backup that information so that it can be restored in case of any disaster (we all have been there).



There are several ways to backup MySQL data. In this article we’ll look at how to backup your databases using different methods, we will also learn how to achieve an automatic backup solution to make the process easier. Starting with the mysqldump utility that comes with MySQL, we will review several examples using mysqldump, including the backup of your database to a file, another server, and even a compressed gzip file and send it to your email.





1. Automatically backup mysql database to Amazon S3







Many of users use Amazon S3 to backup their mysql databases. Here is an automated script which does this task of taking the backup of a mysql database and then moving it to the Amazon S3.



2. How to Backup MySQL Database automatically (for Linux users)



view plaincopy to clipboardprint?

15 2 * * * root mysqldump -u root -pPASSWORD --all-databases
gzip > /mnt/disk2/database_`data ' %m-%d-%Y'`.sql.gz



15 2 * * * root mysqldump -u root -pPASSWORD --all-databases
gzip > /mnt/disk2/database_`data ' %m-%d-%Y'`.sql.gz

This post will show you how to backup MySQL Database automatically if you are a linux user. You can use cron to backup your MySQL database automatically.”cron” is a time-based scheduling utility in Unix/Linux

operating system.



3. Backup your MySQL databases automatically with AutoMySQLBackup



AutoMySQLBackup has some great features to: backup a single database, multiple databases, or all the databases on the server; each database is saved in a separate file that can be compressed (with gzip or bzip2); it will rotate the backups and not keep them filling your hard drive (as normal in the daily backup you will have only the last 7 days of backups, the weekly if enabled will have one for each week, etc.).



4. Backing Up With MySQLDump



mysqldump ---user [user name] ---password=[password]

[database name] > [dump file]



mysqldump ---user [user name] ---password=[password]

[database name] > [dump file]

In this article we’ll look at how to backup our databases using the mysqldump utility that comes with MySQL. Several examples will be reviewed using mysqldump, including the backup of your database to a file,

another server, and even a compressed gzip file.



5. Backup Your Database into an XML File Using PHP



mysqldump ---user [user name] ---password=[password]

[database name] > [dump file]



mysqldump ---user [user name] ---password=[password]

[database name] > [dump file]

Here’s a PHP snippet that outputs your database as XML. XML isn’t the easiest format to restore a table but it can be easier to read.



6. How to – Using PHP To Backup MySQL Database



Execute a database backup query from PHP file. Below is an example of using SELECT INTO OUTFILE query for creating table backup:



view plaincopy to clipboardprint?



include 'config.php';

include 'opendb.php';



$tableName = 'mypet';

$backupFile = 'backup/mypet.sql';

$query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";

$result = mysql_query($query);



include 'closedb.php';

?>





include 'config.php';

include 'opendb.php';



$tableName = 'mypet';

$backupFile = 'backup/mypet.sql';

$query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";

$result = mysql_query($query);



include 'closedb.php';

?>

To restore the backup you just need to run LOAD DATA INFILE query like this :



view plaincopy to clipboardprint?



include 'config.php';

include 'opendb.php';



$tableName = 'mypet';

$backupFile = 'mypet.sql';

$query = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";

$result = mysql_query($query);



include 'closedb.php';

?>





include 'config.php';

include 'opendb.php';



$tableName = 'mypet';

$backupFile = 'mypet.sql';

$query = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";

$result = mysql_query($query);



include 'closedb.php';

?>

7. Backup MySQL Database Via SSH



A simple solution to backup your large MySQL databases through SSH. You will need to enable shell access inside your Plesk control panel and use a utility such as PuTTY to log into your server via SSH.



8. How to e-mail yourself an automatic backup of your MySQL database table with PHP



This script will send an e-mail to you with an .sql file attached, thus enabling you to back up specific tables easily. You could even set up an e-mail account just to receive these backups…



9. Ubuntu Linux Backup MySQL server Shell Script



If you have a dedicated VPS server running Ubuntu Linux. Here is how to backup all your mysql server databases to your ftp server



10. How to backup MySQL databases, web server files to a FTP server automatically



This is a simple backup solution for people who run their own web server and MySQL server on a dedicated box or VPS. The main advantage of using FTP or NAS backup is a protection from data loss.First you will need to backup each database with mysqldump command, Automating tasks of backup with tar, Setup a cron job and generate FTP backup script.



$ mysqldump -u root -h localhost -pmypassword faqs
gzip -9 > faqs-db.sql.gz

The first-ever web browser for India

The world's only antivirus browser

Open Source Connection Manager-Easy To manage Servers

Administering Your Network with Terminals: An Open Source Connection Manager

Terminals 1.7e is all about storing multiple remote connections of varying types and arranging them by tags or in groups and then allowing you to mange numerous simultaneous open sessions via an interface similar to tabbed web browsers. However, you have the option of having any connection that you create open in either Terminal’s tab system or within a separate window. Here’s what the basic interface looks like (remember, you can click the images for a full-sized view):

Terminals is capable of creating connections using RDP, VNC, VMRC, Telnet, SSH1, SSH2, ICA Citrix, RAS and HTTP sessions. When manually creating a connection you are given the choice of the aforementioned protocols and, depending on the protocol that you choose, you will have a different set of options to choose from. All options should be familiar to anyone who uses that protocol frequently, e.g. toggling themes, menu animations and redirecting sound for RDP connections.
Connections can also be automatically discovered using Active Directory or by scanning a range of IP addresses for RDP, VNC, VMRC, Telnet and SSH accepting incoming connections. You can also import RDP, vRD or MuRD files.
Aside from the typical remote GUI connections to an operating system, HTTP connections can be made which essentially means that Terminals spins up Internet Explorer as a rendering engine within one of Terminal’s own tabs. This allows you to make connections to web sites or web based administration pages. Notice that picture 1 displays Terminal’s home page in the active tab.
You can keep your connections organized by tagging them. Those tags are used to create a hierarchy of folders located in a favorites fly-out window on the left of the screen. There is also a history tab in the favorites fly-out window that shows what you connected to in the last day, weeks and months.

I’m sure every administrator has a group of servers or administration pages that they log into frequently and the creators of Terminals made a grouping feature that allows you to open multiple connections at once. For example, if the first thing in the morning that you want to do is check out 4 Windows based web servers, 1 GroundWork dashboard page, 2 telnet sessions to routers and throw in some VNC connections for fun. That’s easy! Just create a group and name it whatever you’d like and then add the relevant connections that are in your favorites list. From then on, if you select that group name in the “Groups” menu all connections in that group will start up in their own tabs simultaneously.

After adding dozens or even hundreds of servers, you will want to take care that you don’t lose all of those configured connections. You can simply copy the terminals.config file in the Terminals application folder or you can backup your configuration file to Amazon’s S3 cloud storage system from within Terminals.

Another one of the useful features of Terminals is the capture manager. Capture manager gives you the ability to take a screenshot of the active remote connection and then store and manage those screenshots in a hierarchy of folders on the local machine. By clicking the “Capture Terminal Screen” button in the shortcuts menu bar, a screenshot of the currently active remote device is immediately taken . Within the capture manager tab you can arrange the photos, view them by thumbnails and annotate them.

Terminals also has the ability to connect to Flickr and upload capture manager shots to your Flickr account. I’ll bet you didn’t see that coming!
The main application window uses toolbars reminiscent of Office 2003 to organize shortcuts to many common administrative control panels and MMCs. Some of the utilities available on the toolbars include the Local Group Policy editor, device manager, certificate manager and even the power configuration control panel.

You will probably find most of those utilities to be of little use to you in the course of your workday, so you can remove the ones you don’t use and create custom shortcuts. Simply supply the executable path, optional arguments, the working folder and you’ll have your very own shortcut.
If you need more space to view your remote connections, just press F11 for full screen mode. Terminals will remove its menu and toolbars and cover up everything on the screen (including the taskbar) to give you maximum space. Pressing F11 again will bring it all back.
A plethora of network related tools are available from the Networking Tools feature which you can open from the Tools menu. Networking Tools opens a separate tab with multiple sub-tabs that offer you tools like ping, trace route, WMI query, open connections, a list of network interfaces, whois lookup (yes, whois lookup from within the app), DNS lookup, scanning for shares locally or remotely, listing all running services, checking proper time from a time server and even a packet sniffer!

Many of those tools can be run to query a remote system such as the WMI Query tool, connections tab (shows open connections on a system), shares and services. Note well that terminals must be run as an administrator for many of these Networking Tools components to work properly.
For all of this consolidating and time saving coolness, I must point out some of the rough edges to this program. I don’t want anyone to have any illusions about this product. It certainly doesn’t have the polish of a commercial product or the support. It is an open source product with very few people giving their precious spare time to work on it.
Some of the minor shortcomings include:

• The picture manager tab is not named. Instead it is a barely visible stub of a tab on your tabs list. In fact, if you look at the above screenshot of the Network Tools feature, you will see a stub of a tab just to the left of the Network Tools tab. Yep, that's the picture manager tab.
• Groups don’t seem to support the addition of other connections once the group has been made.
• Renaming a connection really just makes a new connection with the same settings and the new name. The old connection with the old name is left behind forcing you to manually delete it.
• VNC support is through the open source VNCSharp .NET VNC Client library and only versions 3.3, 3.7, and 3.8 of the RFB Protocol are supported. Some people have reported good results with VNC connections and other people have had bad experiences.
• The program must be run as an administrator to be able to use most of Network Tools features such as ping and the packet sniffer. In fact, if you attempt to run the packet sniffer with a regular user account, the program will simply crash.
• The new version of remote desktop included in Windows 7 causes a tiny user interface glitch in Terminals. The remote desktop banner at the top of the screen is not neatly contained within Terminal’s application window as normal, but rather superimposed over the Terminals window at the very top of the screen. Compare the two images below. Windows 7 and the UI issue with Terminals is on top and XP with the more well behaved remote dekstop UI is on the bottom

Concerning some of the more serious faults with Terminals:

• The SSH and Telnet functionality has some serious limitations. Users have complained about not being able to enter brackets or the pipe character (SSH without the pipe character? Say what?). Terminals does not support the use of an external helper application for SSH so you can’t substitute the program's built-in SSH support with something else like PuTTY.exe. Some have complained about the lack of “command recall” ability to allow you to quickly re-enter commands that you have recently used. Some other quirks exist as well such as the command prompt for the remote system being always at the top of command output rather than the output scrolling by and the prompt being at the end of the output.
• There are spurious exceptions involving errors with the “DataGridView” which is usually followed by the need to completely close the program and restart. Happily, this error usually only happens when heavily using certain tabs of the Networking Tools features such as going from the connections tab to the interfaces tab. While in theory it is a nice set of features, I found that I rarely used the Networking Tools tab and concentrated mostly on RDP and HTTP connections and thus didn’t encounter DataGridView errors until I started experimenting in preparation for this article. Your mileage may vary.

With those shortcomings in mind, I invite you to try Terminals for yourself and even lend a hand in its development if you are able. The program has saved me countless hours (and some money that I didn’t have to spend on a commercial product) and made my day of remote administration much easier than it would otherwise be. And best of all… it works on Windows 7.