Browsed by
Category: MySQL

Restore MySQL database from dump file

Restore MySQL database from dump file

In a previous post I described how you can backup your MySQL database(s). This article tells you how to restore a database using the dump file created by the backup command.

Just start a command box and execute the following command, enter the password for the given user and wait for the command to complete.

[text]
mysql -u username -p database < dumpfile.sql
[/text]

Substitute ‘username’, ‘database’ and ‘dumpfile.sql’ with your own data.

Look at the MySQL reference manual for all the possible options for the mysql command.

Scheduled backup of multiple MySQL databases

Scheduled backup of multiple MySQL databases

When you have one or more MySQL databases you probably want to create a backup at a regular basis. This article shows you how this can be done by using a simple DOS-script.

First thing to do is to create a text file with the names of your databases you want to backup in it. In this example I named the file ‘mysql_databases.txt‘:

[text]
database1
database2
database3
[/text]

Next thing to do is creating the DOS-script. The highlighted lines show which lines to change so it matches your own situation. The ‘d:\scripts‘ directory is where the script is stored and where other usefull tools are stored. More on that later on.

[text highlight=”4-9,11,25″]
@echo off
title Dump MySql databases

SET BUPDIR=d:\backup\mysql_dbexport
SET LOGFILE=dump_mysql_dbs.log
SET DBSLIST=mysql_databases.txt
SET USER=user
SET PWD=password
SET MAILTO=your@email.com

cd /d d:\scripts

echo ———————  > %LOGFILE%
echo MySql database backup >> %LOGFILE%
echo ——————— >> %LOGFILE%

REM Make backup of database given mysql_databases.txt file
for /F %%d IN (%DBSLIST%) DO (
echo %date% %time% >> %LOGFILE%
echo Dumping database %%d
echo Dumping database %%d >> %LOGFILE%
mysqldump –user=%USER% –password=%PWD% –result-file=%BUPDIR%\%%d.sql %%d >> %LOGFILE%
echo ——————— >> %LOGFILE%)

REM Insert code for further processing here

echo %date% %time% >> %LOGFILE%
echo ——————— >> %LOGFILE%
echo         D O N E       >> %LOGFILE%
echo ——————— >> %LOGFILE%

rem pause
[/text]

Now you have a script which can be scheduled so you will always have a backup of your database(s).

If you also want a copy of the backup being sent to you by e-mail, you can extent the script with the following code. Just put the following code in place of the ‘REM Insert code for further processing here’ line.

[text]
REM Zip sql-dumps
7-zip\7za a %BUPDIR%\mysql_dbexport.zip %BUPDIR%\*.sql

REM Mail the log- and zip-file
mailto -a %MAILTO% -s Backup done (databases) #dt -bf %LOGFILE% -f %BUPDIR%\mysql_dbexport.zip
[/text]

Remark: The above script is using 7-zip as a command-line compressing tool. The mailto command is a home made tool, but I’m sure the internet will provide you with a mailto tool that also does the trick.