GUIDE: Backup and restore MySQL database

[johan]

Note: for an updated version of this and other articles, we recommend you to check our knowledge base here

GUIDE: Backup and restore MySQL database

BACKUP

The following command will backup your database.

"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe" --database GpsGateServer --single-transaction -h localhost -ppassword -u root > C:\backups\GpsGateServer.mysqldump

You can then schedule this command to be executed once per day, preferably at night using "Scheduled Task" feature you find in the Windows Control Panel.

It is recommended that you make the backup to a network drive (and not C: as in the sample above). First of all it will increase performance to not write down the backup to the same disk as the original database. And secondly you should have the backup on a separate server, in best case off site.

RESTORE

"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe" --database GpsGateServer -h localhost -ppassword -u root < C:\backups\GpsGateServer.mysqldump

Remarks

Please replace 5.5 with the version of MySQL you have installed. If you installed MySQL as part of the setup Wizard you have 5.5 installed.

Regards,
Johan

GpsGate Support

[mti]

Hi Johan,
if I may, is it correct to say that you can move one installation of GGS to a different server if you:
1. Backup the source database
2. Install GGS in the new server
3. Restore the source database in the new server

The assumption is that the two releases have exactly the same configuration in terms of DLLs, Core patches, etc.

if you are not sure the \bin directory and the \Franson NMEA directory shall be replaced from the source to the new server, is this correct? any step missing?

[teonguyen]

Hi Johan
I can not run mysqldump.exe file,GpsGateServer and MySQL sofware ruing on Win 2003 Server
Can you help me

thanks

[Job Accommodation]

Hi Johan,

When we try to backup the MySQL database, we get this error:

mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE 'map\_meta\_data'': Can't create /write to file 'C:\Windows\TEMP\#sql_a44_0.MYD' (Errcode: 13) (1)

Do you know what is wrong?

Thanks in advance!

Regards

[johan]

When I search for the error message I get many hits. This one for example:
http://dreweyscorner.blogspot.se/2007/0 ... ggers.html

Regards,
Johan

GpsGate Support

[teonguyen]

Hi,
I tried to follow the instruction to do the backup bu can not find the command in the stated directory
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe"

There is no \bin\mysqldump.exe

Same for the restore command: "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe"

Can anyone help and advise, pls.
Thanks

[johan]

teonguyen: Check the install path for your MySQL.

Regards,
Johan

GpsGate Support

[Oslo85_]

Hi

I am moving from a win xp to a win 7 pro system. But I need some help regarding the MySQL backup process.

Is this the correct order?


1. Make sure the the GpsGateServer is updated on old system v.4

2. Run this command on old system "C:\Programfiler\MySQL\MySQL Server 5.5\bin\mysqldump.exe" --database GpsGateServer --single-transaction -h localhost -pXXXXXXXX -u root > C:\backups\GpsGateServer.mysqldump


2. Backup "Deploy" "Franson NMEA Service" "IIS" according to this guide > topic.asp?TOPIC_ID=13322

3. Run GpsGateServer.exe on new server

4. Canceling the wizard when it starts

5. Deleting the following folders (Deploy, Franson NMEA Service, IIS)
- C:\GpsGateInstall\GpsGateServer\Deploy
- C:\GpsGateInstall\GpsGateServer\Franson NMEA Service
- C:\GpsGateInstall\GpsGateServer\IIS

6. Copy my old Deploy, Franson NMEA Service, IIS to the following folder
- C:\GpsGateInstall\GpsGateServer
7. Run C:\GpsGateInstall\Install.exe on your new server.


During install the GpsGateServer is preselected database like in the videos below. However under Choose/Create DSN, IIS express? is showed NOT GpsGateServer.
Ref. 3m.50s into this video > https://www.youtube.com/watch?v=jPQgCJfbLNw

Is this the correct method? and WHEN in the process do I run the RESTORE command:

"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe" --database GpsGateServer -h localhost -ppassword -u root < C:\backups\GpsGateServer.mysqldump

I am not a pro in SQL, so my first thought was that when I run the backup command. a file named GpsGateServer.mysqldump was automaticly created under C:\backups\ but there was no file there.

I tried the process on the new machine (withouth updating the GpsGateServer on the old one) and all went ok to the "Click Next to start NMEA Service" on GpsGateServer login screen, I got this error>


Server Error in '/GpsGateServer' Application.

ERROR [42S02] [MySQL][ODBC 5.1 Driver][mysqld-5.5.20]Table
'gpsgateserver.settings_settings_category' doesn't exist
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.Odbc.OdbcException: ERROR [42S02]
[MySQL][ODBC 5.1 Driver][mysqld-5.5.20]Table
'gpsgateserver.settings_settings_category' doesn't exist

Source Error:


An unhandled exception was generated during the execution of the current web
request. Information regarding the origin and location of the exception can
be identified using the exception stack trace below.

Stack Trace:



[OdbcException (0x80131937): ERROR [42S02] [MySQL][ODBC 5.1
Driver][mysqld-5.5.20]Table 'gpsgateserver.settings_settings_category'
doesn't exist]
Franson.DAO.DAOConnector.RetryConnect(Exception ex, Boolean bThrow) +301
Franson.DAO.DAOBase.AddTable(String strTableName) +290
Franson.Directory.DAO.SettingsDAOSQL..ctor() +46
Franson.Directory.DAO.SiteSettingsDAOSQL..ctor() +15
Franson.Directory.DAO.DirMySQLDAOFactory.GetSiteSettingsDAO() +20
Franson.Directory.SiteSettings..ctor() +28
Franson.Directory.SettingsManager.GetSiteSettings() +146
Franson.Web.UI.PageBase.AddClientContextScript() +100
GpsGate.SiteAdmin.WebUI.Index.Page_Load(Object sender, EventArgs e) +601
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender,
EventArgs e) +51
System.Web.UI.Control.OnLoad(EventArgs e) +92
Franson.Web.UI.PageBase.OnLoad(EventArgs e) +4
System.Web.UI.Control.LoadRecursive() +54
System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +772






All help is much appreciated, Thx!


Jonas

[Oslo85_]

Googlet online to try to understand the process.
According to this article> http://www.thegeekstuff.com/2008/09/bac ... mysqldump/

It says;

How To Backup MySQL database

1. Backup a single database:

This example takes a backup of sugarcrm database and dumps the output to sugarcrm.sql
# mysqldump -u root -ptmppassword sugarcrm > sugarcrm.sql
# mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql



1. Where is the dumpfile saved according to GpsGateServer method?

2. Do I need to restore the dumbfile BEFORE Running the GpsGateServer.exe on NEW server?

I have ben trying to understand this for two days now "Frustrated"[V]


Jonas

[Reglo]

If you don't see the backup file in C:\backups, then the command didn't work properly, and there is no point in doing the rest of the process. Check for typos etc. (speaking from experience...)

Reglo

[Oslo85_]

Reglo; thank you so Much for answering.

My old system have only 2gb of ram. I think that is the cause. I had to make a complete New install.
I lost all the old tracks but thats ok.
Have a Nice Day and thx again!


Jonas

[limyo]

Currently I use "mysqldump.exe" to backup MySql data.
MySQL data on my server too large , nearly 300 GB and backup took around 3 to 4 hours.

I plan to use "Handy Backup" software to backup using incremental method. This way can reduce much time to backup data.

Does any one experience with this Handy backup software? Please share what advantage and disadvantage of this software.

Thanks in advance.

Best regards

Yo Lim

[Oslo85_]

Yo Lim; I think you should try the software. I guess it would be possible to "try before buy"?

I tried to run the following command >

"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe" --database GpsGateServer --single-transaction -h localhost -pXXXXXXX -u root > C:\backups\GpsGateServer.mysqldump

on my new system Win 7 pro, 16gb ram, new xeon cpu. No file was generated under C:\backups\ ....

Jonas

[bozso]

Hi Johan!
I have GGS with 10 license.
Unfortunately, the HDD go bad, very slow and not all programs started under Win7.
MySQL does not start.
How can I backup the database by hand, not with mysqldump?
I am waiting for your answer, it is very important.

[SkyTrack]

I'm also having the same issue when i'm trying to transfer (backup) my database, from my server to my laptop, using this command line:
--database GpsGateServer --single-transaction -h localhost -pXXXXXXX -u root > C:\backups\GpsGateServer.mysqldump
Where you see -pxxxxx, i'm putting -p123456 (for example, let's say the code is 123456)....
But i'm always getting an Error like that:
ERROR 1064 (42000): You have an error in your SQL syntax.....etc...etc...
Can someone please write down the exact command line, with a full example?
Perhaps i must change something??? And what's that?
Regards