Moving from MySQL 5.5 to 5.6. Step 1: Backup

Posted by in MySQL

Today I start a serie of post dedicated to #MySQL database, one of the most used databases on Internet sites. Apart from this, I must say that I found less information than I imagine for moving from MySQL 5.0, 5.1 or 5.5 to versin 5.6 on Windows. So, I will tell you my way!

First, backup your databases

The very first important task is to create a backup of all our databases.

VERY IMPORTANT!! Because updating from MySQL 5.x to 5.6 is an important process, I encourage you yo create also a backup of mysql database (a database used by MySQL) and it includes several important things, like permissions, …

there is too much to talk about backups, because every engine has its technique, although some of them can be shared by several engines. I am writing here about the way I use to do it, using MySQLdump. My default engine is InnoDB.

Using MySQL WorkBench

Maybe I should invert more time with this graphical admin tool, the “oficial” from ORACLE to work with MySQL, because for importat task, MySQL Workbench get very good results. Let’s access to the server, and on the left zone, click on Data Export.

exportacion MySQL WorkBench

exportacion MySQL WorkBench

The Export Data tool is what I normally use to export all databases, and selecting all of them that you are interested in, select a directory, and execute the process. Easy!

In my opinion, I was surprised by the good performance when dealing with big databases (several Gigas), but I must admit that for bigger databases (with more that one Teras) I bet there is better options, but until now, I haven’t had to solve this problem (maybe … in the future?). You can configure several export options like including triggers, with data (or only structure), …

The performance is amaizing (due to mysqldump), and specially when every table is stored in one file (a good advice for big databases).

MySQLdump from MS-DOS

Another option is to use mysqldump.exe. It is recommended when you feel confortable with mysqldump and you like MS-DOS. For beginners this is rthe worst option, because writing long sentences several times is not a funny task, although you can write on .BAT files, and you’ll have the same result.

One of the disadvantages of this options is where is the path to mysqldump.exe,and of course, the lack of information during the process. I rare use this option, mainly because there is better options!

Export databases using heidiSQL

As a graphical admin tool, heidiSQL has a form to export databases. This is the option I use the most when I only need to export a single database. From Tools / Export Database as SQL:

Exportacion desde HeidiSQL

Export databases on HeidiSQL

Exportacion desde HeidiSQL 2

Export on HeidiSQL 2

Just select the options you need, like Drop, Creates, … , output format, just write or select the name of the file to create and … click on Exporta. This option is very handy and useful for beginners, because is very visual. The price you have to pay is that you can’t know the sentence used although you can see how the process is going (there is a zone on heidiSQL where you can see the sentence sent to the database).

Exportacion heidiSQL

Exporting databases on heidiSQL

Exportacion heidiSQL

Exporting on heidiSQL

Of course, the options I introduce here are very useful for punctual backups, but there is several tools for creating regular backups.

I hope I help you! and have a nice day!