Tag Archives: MYSQL

MySQL Backup

Restore MySQL database from .frm files

Posted on by

So you need to restore a MySQL database from the files which were on a server and don’t have a proper SQL dump which you can just easily import, d’oh!

Below is a brief example from a WAMP server of how this can be done.

Before starting you should stop the WAMP services, or at least restart the services when prompted to start them.

On the old server instance navigate to the MySQL data folder by default this should look something similar to “C:\wamp\bin\mysql\mysql5.1.53\data\” where “mysql5.1.53″ will be the version number of the previously installed MySQL database.

Inside this folder you should see a few files and folders. The folders are the actual MySQL databases, and contain a bunch of .frm files which we will require. You should recognise the folder names as the database names. These folder and all their contents can be copied directly to your MySQL data folder, you can neglect the default databases mysql, performance_schema, test.

If you started the server now you will see the databases are picked up, however the databases will contain none of the tables which were copied across. In order for the contents of the database to be picked up, back in the data folder you should see a file “ibdata1″, this is the data file for tables, copy this directly into the data folder, you should already have a file in your new data folder called “ibdata1″ so you may wish to rename this to “ibdata1.bak” before copying across the “ibdata1″ from the old MySQL data folder.

Once this has been done Restart all the WAMP services. You can use PhpMyAdmin to check if your databases have been successfully restored.

cmd

A simple cmd batch script to backup MYSQL for WAMP servers

Posted on by

Here’s a quick and simple guide to creating a short script to backup your MYSQL databases for your WAMP server.

To do this we will create a .bat file using notepad, or your favourite text editor, you can then even go onto automating the execution of this script using the in-built Windows Task Scheduler. I won’t bother going into detail, if you’ve already setup a WAMP server I assume you’re pretty capable with a computer already and the script is only a basic example for you to tailor and play with, but can be a lifesaver in nasty situations!

Open up notepad and write the script as shown below – changing any options to suit your own circumstances.

echo off
cd C:wampbinmysqlmysql5.1.53bin
cls
mysqldump -u user -p password database1_name > C:sql_dumpsdatabase1_name.sql
echo database1 sql dump created
mysqldump -u root database2_name > C:sql_dumpsdatabase2_name.sql
echo database2 sql dump created
echo ——————————–
echo All SQL Database Backups Completed
echo ——————————–
pause

Save the file as backup.bat. This script will now be executable and can be run to backup and take dumps of your databases.

A further look into what each line of the script is doing…

echo off
Turns off command-echoing
cd C:wampbinmysqlmysql5.1.53bin
Change directory to where your mysqldump.exe executable is installed
cls
Clears the screen. The user doesn’t need to see what we’ve been doing yet.
mysqldump -u user -p password database1_name > C:sql_dumpsdatabase1_name.sql
Creates a dump of the database called database1_name to the location C:sql_dumps as a file named database1_name.sql, using the MYSQL account details of username user and password password.
echo database1 sql dump created
Displays a message to say this step has been completed
mysqldump -u root database2_name > C:sql_dumpsdatabase2_name.sql
Creates a second dump for a second database called database2_name to the location C:sql_dumps as a file named database2_name.sql, using the MYSQL root account.
echo database2 sql dump created
Displays a message to say this step has been completed
echo ——————————–
echo All SQL Database Backups Completed
echo ——————————–
Displays a message to say all steps have been completed
pause
Suspends processing of the batch file displaying “Press any key to continue…”. I have simply added a pause to the end of the script to allow the user to see that the script has run and the messages which have been displayed.

Just a quick note you should notice I change directory to where the mysqldump.exe lives, the path for this maybe slightly different for yourself depending on where you placed your WAMP installation. To prevent you from having to take this step you could add the directory to the Windows Enviroment Variables… Path (Right-Click Computer – Advanced system properties), however this is only an optional extra step.