Machtl
Posts: 88
Joined: Sat Aug 16, 2014 2:58 pm
Location: Austria

RE: GUIDE: Backup and restore MySQL database

Tue Dec 01, 2015 12:19 am

i use the following in a batch-file to make a dumpfile every 2 hours:
"C:\Programme\MySQL\MySQL Server 5.7\bin\mysqldump.exe" --single-transaction -h localhost -p123456 -u root --compress gpsgateserver > E:\Backup\Server\MySQLData\GpsGateServer_SQLDump.sql

and the following to copy the database directly into another mysql server with ip 192.168.x.x:
"C:\Programme\MySQL\MySQL Server 5.7\bin\mysqladmin.exe" -h 192.168.x.x --force drop gpsgateserver
"C:\Programme\MySQL\MySQL Server 5.7\bin\mysqladmin.exe" -h 192.168.x.x --force create gpsgateserver
"C:\Programme\MySQL\MySQL Server 5.7\bin\mysqldump.exe" --single-transaction -h localhost -p123456 -u root --compress gpsgateserver | "C:\Programme\MySQL\MySQL Server 5.7\bin\mysql.exe" -h 192.168.x.x --compress gpsgateserver

SkyTrack
Posts: 6
Joined: Fri Nov 20, 2015 5:41 pm
Location: Greece

RE: GUIDE: Backup and restore MySQL database

Wed Dec 02, 2015 2:06 pm

Dear Machtl, thank you for your answer. Unfortunately, i'm getting the same error again, in every command line that i'm trying!
I'm going firstly at the MySQL Command Line Client, by entering my access code (example a123f@)... Then, i copy /paste the entire line you have posted above:

"C:\Programme\MySQL\MySQL Server 5.7\bin\mysqldump.exe" --single-transaction -h localhost -pa123f@ -u root --compress gpsgateserver > C:\backups\Server\MySQLData\GpsGateServer_SQLDump.sql

The answer is always the same: ERROR 1064 (42000): ...syntax error....
????

Machtl
Posts: 88
Joined: Sat Aug 16, 2014 2:58 pm
Location: Austria

RE: GUIDE: Backup and restore MySQL database

Wed Dec 02, 2015 2:14 pm

you dont have to enter the mysql command line client. just execute it from the windows command window... inside the mysql command client the syntax is completly different!

SkyTrack
Posts: 6
Joined: Fri Nov 20, 2015 5:41 pm
Location: Greece

RE: GUIDE: Backup and restore MySQL database

Wed Dec 02, 2015 6:18 pm

Believe me, i have try for it a lot of times....
I have put those lines:
"C:\Programme\MySQL\MySQL Server 5.7\bin\mysqldump.exe" --single-transaction -h localhost -p123456 -u root --compress gpsgateserver > E:\Backup\Server\MySQLData\GpsGateServer_SQLDump.sql

But i haven't try this one:
--database GpsGateServer --single-transaction -h localhost -pXXXXXXX -u root > C:\backups\GpsGateServer.mysqldump

I shoul write the 1st or the 2nd???
Because the answer for the 1st, was: The system can't locate the specific disk direction...!

Machtl
Posts: 88
Joined: Sat Aug 16, 2014 2:58 pm
Location: Austria

RE: GUIDE: Backup and restore MySQL database

Wed Dec 02, 2015 7:18 pm

What version of MySQL Server do you have?
You changed the path in the lines above right? Because this executable-path is only right for my machine here...

SkyTrack
Posts: 6
Joined: Fri Nov 20, 2015 5:41 pm
Location: Greece

RE: GUIDE: Backup and restore MySQL database

Mon Dec 07, 2015 11:02 am

Yes Machtl. I've change everything regarding my database details, codes etc... Finaly, i foubnd the problem! I'm using other MySQL release, instead of 5.7!! My database comes from MySQL 5.5!!! That's why i couldn't make any progress!! Realy sorry for that mistake... So, finaly i get my backup. But file that created, was with 0 (zero) data inside! This happend because we haven't collect any info's from GPS trackers yet? Perhaps we must make some test firstly and then we should backup again the database...? I thought that the database is collecting also our customer's details etc! Am i missing something here??
Regards

jasoni
Posts: 3
Joined: Tue Mar 04, 2014 5:12 am
Location: Australia

RE: GUIDE: Backup and restore MySQL database

Thu Feb 04, 2016 7:27 am

Originally posted by Machtl

you dont have to enter the mysql command line client. just execute it from the windows command window... inside the mysql command client the syntax is completly different!


i have had to do the following steps which are missing for newbies like myself that dont understand mysql.

step 1
from a standard command prompt paste the line below make sure the 5.# matches your installation

cd C:\Program Files\MySQL\MySQL Server 5.5\bin

step 2
run the code from the top post inserting your password after the first p in password and making sure the 5.# matches your installation

this should work.

cheers jason

Return to “GpsGate Server - How to setup and use”