How to backup remote MySQL database on your Mac using Sequel Pro

Sequel Pro is a nice freeware tool for MySQL administrators and web developers. It aims as a desktop manager for your local or remote MySQL installation. It has numerous features including backup and restore of your MySQL tables and data.

As a web developer I often work with phpMyAdmin, browser based web application to manage MySQL database. But its installation is not as easy as we wish; moreover the phpMyAdmin is not present on all websites or servers of my customers.

If you need to manage the remote database, you can try Adminer, another free web based MySQL manager. It has the same functionality as phpMyAdmin and its installation is very fast – it is only one file including everything. So you can copy it to the website and load in your browser.

But not for all the time you can deal or would like to deal with web based projects. I started using the desktop-based database managers the last year and after a few months I see the advantages. If you will choose the right software, you will have maximum comfort for your queries, browsing tables’ structure and data. And you will have an option to backup the remote database to your computer.

That’s the case of the freeware tool called Sequel Pro. It is a nice MySQL manager for Mac OS X users. It can connect to any remote MySQL database if this has remote access for your IP enabled.

How to browse MySQL database using Sequel Pro on your Mac OS

At first you have to connect. You can use standard connection using database host, port, username and password with SSL option. You can also use a Socket or SSH, so you have three powerful combinations. Prepared connections can be stored as favourites for the next time.

After you are connected you will see all tables in selected MySQL table on the left pane. Clicking the table you can see its basic information below.

Sequel Pro: MySQL manager running on Mac OS X

When the MySQL table is selected, click one of the buttons at the top toolbar. The Structure button will show you up the properties of the table. Clicking the Content you can browse the data within the selected table.

You can also run your custom queries. There is one feature why you will love the Sequel Pro. The query editor has intelligent help – it suggests the autocompletion of the tables’ names, columns’ names or the keywords. It is very useful when building up large SELECT queries.

How to backup and restore MySQL on your Mac OS X

Sequel Pro has also backup features. Using it you can backup one or selected tables or the whole remote MySQL database on your Mac. If you want to backup a specific table, select it in the right pane. If you want to backup the whole MySQL database you don’t need to select a specific table.

Now, go to the File > Export menu. You will see a new window where you can confirm your selection (select another tables or all tables). There are three checkboxes for each table. S means you would like to export table structure to the backup file, C stands for content and D is for drop table syntax in the dump.

There are also a few options for the backup itself. To create a complete backup of your MySQL database be sure you have selected Structure, Auto increment values, DROP TABLE syntax and Content. You can set the INSERT block size to be sure there will be no timeout when restoring the database from this dump.

The Advanced options give you the possibility to compress the backup. By the way, check the buttons at the top. Beside the SQL export you can backup your MySQL database to the CVS, XML a Dot files too. I prefer the standard SQL.

Sequel Pro and MySQL backup

When you are done with the settings, click the Export button. You will see a progress bar informing you about downloading the data.

The restoration of your MySQL backup is as easy as going to the File > Import menu and selecting the created backup file.

Where to download Sequel Pro for the Mac

Sequel Pro is a free application. It is an open source, which means you can also download it source code. If you like it, you can donate it on its homepage. Sequel Pro runs on Mac OS X 10.5 Leopard or newer and is fully compatible with Lion till this date.

Point your browser to the to see more information about this great app.