MySQL – How to Backup MySQL Server on Windows

backupMySQLwindows

I was wondering if there was any way of making a backup of an entire MySQL Server at regular intervals to a specific location. (If the version is required I can get it easily).

If there is, I must have a way of restoring just a single database from that server backup easily as though I have backed them up individually.

The method used will just be a pure backup and not a difference so no need to worry about complications.

UPDATE: I am using Windows Server 2008 to host the MySQL Server

Best Answer

PLEASE SEE MY WINDOWS ANSWER DOWN BELOW BY THE UPDATE 2012-05-22 12:42 EDT

You need to make backups of each database separately

First, go get all your databases

MYSQL_CONN="-uusername -ppassword"
SQLSTMT="SELECT DISTINCT table_schema FROM information_schema.tables"
SQLSTMT="${SQLSTMT} WHERE table_schema NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > /tmp/ListOfDatabases.txt

BACKUP ALGORITHM #1

If you have a few databases to backup, you can dump all the databases is parallel:

MYSQL_CONN="-uusername -ppassword"
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
for DB in `cat /tmp/ListOfDatabases.txt`
do
    BACKUP_GZIP=${DB}.sql.gz
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB} | gzip > ${BACKUP_GZIP} &
done
wait

BACKUP ALGORITHM #2

If there are too many databases to launch at the same time, dump 10 at a time:

MYSQL_CONN="-uusername -ppassword"
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
COMMIT_COUNT=0
COMMIT_LIMIT=10
for DB in `cat /tmp/ListOfDatabases.txt`
do
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB} | gzip > ${DB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

BACKUP ALGORITHM #3

If there are simply way too many databases or the some of the databases are simply too large for frequent backups, you may want to think about making backups like this:

On the 1st of the month, dump the Separate Databases Using BACKUP ALGORITHM #2
Every hour, make copies to the binary logs (forming a binary log archive)

That way, restoration of a database can be done in a point-in-time fashion.

For example, to get database mydb as for 2012-05-20 09:00:00 you do this:

Restore Database Backup from 2012-05-01 00:00:00
Replay all archived binary logs using the mysqlbinlog program
- use option --start-datetime="2012-05-01 00:00:00"
- use option --stop-datetime="2012-05-20 09:00:00"
- use option --database=mydb

CONCLUSION

There may be other considerations to these scripts you may want to factor in. You may also want to use Open Source/Commerical programs to get the backups in the way most comfortable for you to use.

I would further recommend using MySQL Replication. That way these scripts can be executed on the Slave in this manner (as recommended in MySQL 5.0 Certification Study Guide, Page 462 Section 32.6 under the Heading "Replication as an Aid to Backup"):

STOP SLAVE SQL_THREAD;
Run the Backup
START SLAVE SQL_THREAD;

This would also keep the backups from interefering with a production Master.

UPDATE 2012-05-22 12:42 EDT

Oh, you are running Windows 2008? That is a whole different ballgame.

I actually wrote a post in StackOverflow on dumping separate databases in Windows back in March of 2012.

I will post my answer from StackOverflow right now...

Have the information_schema database construct a DOS Batch File to perform the mysqldumps in parallel

set MYSQLUSER=root
set MYSQLPASS=1234
set BATCHFILE=S:\Backup\MySQL\Batch_mysqldump.bat 
set DUMPPATH=S:\Backup\MySQL
echo @echo off > %BATCHFILE% 
echo cd %DUMPPATH% >> %BATCHFILE% 
mysql -u%MYSQLUSER% -p%MYSQLPASS% -AN -e"SELECT CONCAT('start mysqldump -u%MYSQLUSER% -p%MYSQLPASS% --routines --triggers ',schema_name,' > ',schema_name,'.sql') FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','performance_schema')" >> %BATCHFILE% 
type %BATCHFILE%

Just run like any DOS Batch File

Make sure you have the correct username and password to connect to mysql

I just tried it out to make sure

C:\>set MYSQLUSER=lwdba

C:\>set MYSQLPASS=<hidden>

C:\>set BATCHFILE=C:\LWDBA\Batch_mysqldump.bat

C:\>set DUMPPATH=C:\LWDBA

C:\>echo @echo off > %BATCHFILE%

C:\>echo cd %DUMPPATH% >> %BATCHFILE%

C:\>mysql -u%MYSQLUSER% -p%MYSQLPASS% -AN -Bse"SELECT CONCAT('start mysqldump -u%MYSQLUSER% -p%MYSQLPASS% --routines --triggers ',schema_nam
e,' > ',schema_name,'.sql') FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','performance_schema')" >> %BATCH
FILE%

C:\>type %BATCHFILE%
@echo off
cd C:\LWDBA
start mysqldump -ulwdba -phidden  --routines --triggers a1ex07 > a1ex07.sql
start mysqldump -ulwdba -phidden  --routines --triggers annarbor > annarbor.sql
start mysqldump -ulwdba -phidden  --routines --triggers dilyan_kn > dilyan_kn.sql
start mysqldump -ulwdba -phidden  --routines --triggers dtest > dtest.sql
start mysqldump -ulwdba -phidden  --routines --triggers dude > dude.sql
start mysqldump -ulwdba -phidden  --routines --triggers example > example.sql
start mysqldump -ulwdba -phidden  --routines --triggers fed > fed.sql
start mysqldump -ulwdba -phidden  --routines --triggers friends > friends.sql
start mysqldump -ulwdba -phidden  --routines --triggers giannosfor > giannosfor.sql
start mysqldump -ulwdba -phidden  --routines --triggers javier > javier.sql
start mysqldump -ulwdba -phidden  --routines --triggers johnlocke > johnlocke.sql
start mysqldump -ulwdba -phidden  --routines --triggers junk > junk.sql
start mysqldump -ulwdba -phidden  --routines --triggers lovesh > lovesh.sql
start mysqldump -ulwdba -phidden  --routines --triggers mysql > mysql.sql
start mysqldump -ulwdba -phidden  --routines --triggers nwwatson > nwwatson.sql
start mysqldump -ulwdba -phidden  --routines --triggers part > part.sql
start mysqldump -ulwdba -phidden  --routines --triggers preeti > preeti.sql
start mysqldump -ulwdba -phidden  --routines --triggers prefixdb > prefixdb.sql
start mysqldump -ulwdba -phidden  --routines --triggers replagdb > replagdb.sql
start mysqldump -ulwdba -phidden  --routines --triggers rollup_test > rollup_test.sql
start mysqldump -ulwdba -phidden  --routines --triggers sample > sample.sql
start mysqldump -ulwdba -phidden  --routines --triggers stuff > stuff.sql
start mysqldump -ulwdba -phidden  --routines --triggers table_test > table_test.sql
start mysqldump -ulwdba -phidden  --routines --triggers tagmediatest > tagmediatest.sql
start mysqldump -ulwdba -phidden  --routines --triggers targetdb > targetdb.sql
start mysqldump -ulwdba -phidden  --routines --triggers test > test.sql
start mysqldump -ulwdba -phidden  --routines --triggers test_mysqldb > test_mysqldb.sql
start mysqldump -ulwdba -phidden  --routines --triggers tostinni > tostinni.sql
start mysqldump -ulwdba -phidden  --routines --triggers user1267617 > user1267617.sql
start mysqldump -ulwdba -phidden  --routines --triggers user391986 > user391986.sql
start mysqldump -ulwdba -phidden  --routines --triggers utility > utility.sql
start mysqldump -ulwdba -phidden  --routines --triggers veto > veto.sql
start mysqldump -ulwdba -phidden  --routines --triggers vito > vito.sql
start mysqldump -ulwdba -phidden  --routines --triggers zipcodes > zipcodes.sql

Give it a Try !!!

EDIT By JakeJ:

When ran, the solution just outputted the data into CMD, then closed it without writing it to any file, however this now saves the data to the .SQL file created.

set MYSQLUSER=root
set MYSQLPASS=pass
set BATCHFILE=C:\Backup\MySQL\Batch_mysqldump.bat 
set DUMPPATH=C:\Backup\MySQL
echo @echo off > %BATCHFILE% 
echo cd %DUMPPATH% >> %BATCHFILE% 
cd C:\Program Files\MySQL\MySQL Server 5.5\bin\
mysql.exe -u%MYSQLUSER% -p%MYSQLPASS% -AN -e"SELECT CONCAT('mysqldump -u%MYSQLUSER% -p%MYSQLPASS% --routines --triggers ',schema_name,' > ',schema_name,'.sql') FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','performance_schema')" >> %BATCHFILE% 
type %BATCHFILE% 
cd C:\Backup\MySQL
Batch_mysqldump.bat

Related Solutions

Sql-server – SQL Server backup, attach to BAK file

First there's no way using the native tools to mount a backup file and read the data in it. That said, what you can do is write your version information into the description of the backup using the DESCRIPTION keyword. Then read this information using the RESTORE DATABASE WITH HEADERONLY to get the information and make sure that they have the correct version.

Mysql – Restoring tab exports to a thesql database with integrity

It doesn't really make sense that you're able to do the backup with the --tab option but not without it, and I think that should be investigated.

Concerns about integrity are generally concerns with the backup, and you should be concerned about the backup, because --single-transaction issues a START TRANSACTION WITH CONSISTENT SNAPSHOT before the backup, ensuring that what gets written to the file represents the state of all the tables at one consistent point in time.

Without this, your "user" table, being alphabetically later than your "history" table could (for example) in the backup file contain a new user but not have caught history of that account's creation, since the backup of "history" finished before the backup of "user" was started.

You say you want to restore within a transaction, but that's not possible (by default) and probably not really what you intend at any rate.

It's not possible, by default, because of the way mysqldump works. Examine a backup file

DROP TABLE IF EXISTS `action`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `action` (
  `id` int(11) NOT NULL ...

Both CREATE TABLE and DROP TABLE cause an implicit commit. This isn't the only concern, because non-conflicting writes to the tables could still be done by other threads.

There are two other ways to prevent changes on a MySQL server:

FLUSH TABLES WITH READ LOCK;

This attempts to close all tables and acquire a global lock that prevents any thread from writing. The problem here again in with the standard behavior of mysqldump, and what it writes to the dump file for execution during the restore:

LOCK TABLES `action` WRITE;
/*!40000 ALTER TABLE `action` DISABLE KEYS */;
/*!40000 ALTER TABLE `action` ENABLE KEYS */;
UNLOCK TABLES;

These individual table locks are not compatible with the global lock:

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.16 sec)

mysql> lock tables t1 write;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

You can disable this behavior with --skip-add-locks but then you wouldn't be able to write data into the tables if you had the global read lock because it prevents you from writing as well as other threads... so that one seems to be a non-starter.

Alternately, you could force the entire server to read_only mode.

FLUSH TABLES WITH READ LOCK; -- force a wait until all ongoing writes have finished
SET GLOBAL READ_ONLY = 1;    -- set entire server to read-only except for users with `SUPER` privilege
UNLOCK TABLES;               -- release the global read lock
--- restore backup
SET GLOBAL READ_ONLY = 0;

If you're concerned with other threads modifying the tables during the restore, but you don't want the entire server to be read-only for the duration of the operation, I only see one really solid option, and that would be to restore all of the tables with an alternate name, and then, in a single SQL statement, rename them all to their correct names.

When multiple tables are renamed in a single statement, the entire statement is atomic.

RENAME TABLE t1 TO t1_old, t1_new to t1;

This acquires a global mutex that prevents t1 from being accessed until all of the renames are complete... but it would require manipulating your dump file to give the tables alternate names, thought it might also work if you restored to an alternate database name and renamed the tables in a fully-qualified form.

Another approach would be to drop and add all of the tables and lock all of them for write, before inserting the data, after removing any LOCK and UNLOCK from the dump file(s). The one additional step you'd need to do, here, would be after acquiring the locks, you'd need to run through every table and delete any data that got inserted by other threads between the time you created the table and acquired the locks, or the restore would fail. SET FOREIGN_KEY_CHECKS = 0 is a session-level setting that would allow your session to freely remove any stray data without regard to foreign key constraints.

The bottom line is that there is not a simple, straightforward way to keep all hands off of the data you are attempting to restore, and if faced with that requirement, I would be inclined to shut down the application server or otherwise prevent access to the server with a mechanism outside of mysql itself, such as IP-level filtering.