Help Creating Auto DB Backup

[FUBAR22]

Hello,

 

Not sure if this is the right place to post, but let me try.

 

I am using a server host company that does not offer automatic backup of the database.  I know nothing about this, and could use someones help to create a simple automatic scheduled backup of my database each hour.

 

Environment:

Host provider - Vert Hosting

I use phpMyAdmin

I have access to all files

 

Can someone provide me instructions and a simple script to backup my database each hour (and possibly delete the oldest after 2 days)?

I do not believe my host allows bat files, so is this something that I can run as a "scheduler" event within php?

 

Anyway....any help would be GREAT!!

 

Thanks

Fubar

[Sandbird]

Do you have Bec Running on the server ?

If you do add this job at the scheduler (it will run every 1h)

<job id="1">
	<time>010000</time>
	<delay>000000</delay>
	<day>1,2,3,4,5,6,7</day>
	<loop>1</loop>
        <cmd>C:\Users\blahblah\Desktop\backup.bat</cmd>
	<cmdtype>1</cmdtype>
</job>
    

Change the job id and path first.

Then make a backup.bat file at that location.

@echo off
FOR /F "tokens=1-4 DELIMS=/ " %%F IN ('date /T') DO (set v_date=%%F%%G%%H) 
FOR /F "tokens=1-4 DELIMS=: " %%F IN ('time /T') DO (set v_time=%%F%%G%%H) 
set fname=database_backup_%v_date%_%v_time%.sql 
echo %v_time% 
echo %fname% 
cd /D "C:\wamp\bin\mysql\mysql5.6.12\bin"
mysqldump -u SQLUSER -p SQLDATABSENAME --password=SQLPASSWORD --result-file="C:\Arma2\Epoch\%fname%"
ping 127.0.0.1 -n 5 >NUL
exit

Change the path where your mysqldump.exe is....

Change your sql user/pass/databasename.

Change the folder where you want the backup to save (c:\arma2\epoch\)

 

 

EDIT: OH crap, i just saw that your host DOESNOT allow .bat files....hmmmm,sorry

What type of OS is it ? Windows ? Do you have access to the desktop ? Or just a simple WebGUI?

[FUBAR22]

Hi Sandbird....I am running it hosted by Vert Hosting.  I have access via FTP, tcAdmin and phpMyAdmin tool for the database.

 

Is there an sql statement I can run to back this up to my own computer?

 

Cheers,

[Sandbird]

Hi Sandbird....I am running it hosted by Vert Hosting.  I have access via FTP, tcAdmin and phpMyAdmin tool for the database.

 

Is there an sql statement I can run to back this up to my own computer?

 

Cheers,

 

Do they give you shell access ?

If yes you can do it with a shell script:  http://stackoverflow.com/questions/9945995/how-can-i-automate-the-exportation-of-a-mysql-database-using-phpmyadmin/11031535#11031535

 

Cant think of another way,....unless you have your PC open all the time, then you can add a windows task event to do it from your pc with mysqldump....but you'll have to install an appache/mysql server on your pc...something like WAMP (www.wampserver.com) so you get the mysqldump.exe

[Turtle]

Do you have Bec Running on the server ?

If you do add this job at the scheduler (it will run every 1h)

<job id="1">
	<time>010000</time>
	<delay>000000</delay>
	<day>1,2,3,4,5,6,7</day>
	<loop>1</loop>
        <cmd>C:\Users\blahblah\Desktop\backup.bat</cmd>
	<cmdtype>1</cmdtype>
</job>
    

Change the job id and path first.

Then make a backup.bat file at that location.

@echo off
FOR /F "tokens=1-4 DELIMS=/ " %%F IN ('date /T') DO (set v_date=%%F%%G%%H) 
FOR /F "tokens=1-4 DELIMS=: " %%F IN ('time /T') DO (set v_time=%%F%%G%%H) 
set fname=database_backup_%v_date%_%v_time%.sql 
echo %v_time% 
echo %fname% 
cd /D "C:\wamp\bin\mysql\mysql5.6.12\bin"
mysqldump -u SQLUSER -p SQLDATABSENAME --password=SQLPASSWORD --result-file="C:\Arma2\Epoch\%fname%"
ping 127.0.0.1 -n 5 >NUL
exit

Change the path where your mysqldump.exe is....

Change your sql user/pass/databasename.

Change the folder where you want the backup to save (c:\arma2\epoch\)

 

 

EDIT: OH crap, i just saw that your host DOESNOT allow .bat files....hmmmm,sorry

What type of OS is it ? Windows ? Do you have access to the desktop ? Or just a simple WebGUI?

question...

In this line:

ping 127.0.0.1 -n 5 >NUL

Do I need to do anything for the ip? Its on a dedi and I get the localhost part but what about a port or anything?

[Sandbird]

@Turtle

uh yeah forget to say whats that...

That means wait for 5 seconds before going to the next line.I use other stuff bellow...and wanted to wait a bit before executing them.

[Turtle]

I'm getting an error saying that "mysqldump is not an external or internal command"

 

@Turtle

uh yeah forget to say whats that...

That means wait for 5 seconds before going to the next line.I use other stuff bellow...and wanted to wait a bit before executing them.

 

[Defent]

That probably means you're not pointing this to the right location:

cd /D "C:\wamp\bin\mysql\mysql5.6.12\bin"

Instead of ping to wait you can also use:

timeout 5 

That's 5 seconds before next line starts.

[Turtle]

That probably means you're not pointing this to the right location:

cd /D "C:\wamp\bin\mysql\mysql5.6.12\bin"

Instead of ping to wait you can also use:

timeout 5 

That's 5 seconds before next line starts.

I have mysql folder but then I don;t have the mysql5.6.12 folder in it

[Defent]

That doesn't matter. Just point the code line towards the bin folder and it will porbably work.

[Turtle]

That probably means you're not pointing this to the right location:

cd /D "C:\wamp\bin\mysql\mysql5.6.12\bin"

Instead of ping to wait you can also use:

timeout 5 

That's 5 seconds before next line starts.

Ahh thanks so much man:) Working Perfectly

[AztecGhost]

https://mysqlbackuponline.com/ was so easy to setup and use.

 

Thanks for the suggestion

[airtonix]

Better way to do it is Host your MYSQL db files "/var/run/mysql" on an filesystem that is Copy On Write (llvm, zfs) then it's a simple matter of periodically asking the filesystem to do a snapshot.

 

http://www.lullabot.com/blog/article/mysql-backups-using-lvm-snapshots

[Just_R]

OR

 

you can get goodsync and just sync down whatever files you need and it has a scheduler.  Much more simple than this.

 

 

Do you have Bec Running on the server ?

If you do add this job at the scheduler (it will run every 1h)

<job id="1">
	<time>010000</time>
	<delay>000000</delay>
	<day>1,2,3,4,5,6,7</day>
	<loop>1</loop>
        <cmd>C:\Users\blahblah\Desktop\backup.bat</cmd>
	<cmdtype>1</cmdtype>
</job>
    

Change the job id and path first.

Then make a backup.bat file at that location.

@echo off
FOR /F "tokens=1-4 DELIMS=/ " %%F IN ('date /T') DO (set v_date=%%F%%G%%H) 
FOR /F "tokens=1-4 DELIMS=: " %%F IN ('time /T') DO (set v_time=%%F%%G%%H) 
set fname=database_backup_%v_date%_%v_time%.sql 
echo %v_time% 
echo %fname% 
cd /D "C:\wamp\bin\mysql\mysql5.6.12\bin"
mysqldump -u SQLUSER -p SQLDATABSENAME --password=SQLPASSWORD --result-file="C:\Arma2\Epoch\%fname%"
ping 127.0.0.1 -n 5 >NUL
exit

Change the path where your mysqldump.exe is....

Change your sql user/pass/databasename.

Change the folder where you want the backup to save (c:\arma2\epoch\)

 

 

EDIT: OH crap, i just saw that your host DOESNOT allow .bat files....hmmmm,sorry

What type of OS is it ? Windows ? Do you have access to the desktop ? Or just a simple WebGUI?