asked on

BACKUP of mysql database from mysql server - using Coldfusion

I have a database called office on mysqlserver. I need to have a user friendly backup facility - ie the user can click a link and the backup will be created - ie office.sql and inserted into a folder.

C:\DUMPS>"c:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" mysqldump -u root -p office < C:\dumps\office.sql

To start, I have been trying the above cmd approach - which is trying to create a backup called office.sql in a folder dumps - using the database office which is mounted on the mysql server. I have not succeeded with this - get error that the 'file cannot be found' ?

However, assuming that this bug can be cured, I would then like to trigger this command line from within Coldfusion. So the coldfusion page would have a link called BACKUP. On clicking the backup is created - overwriting any exisiting backup - and a confirmation message provided confirming that BACKUP has been created.

Thanks for your help

james

_agx_

File not found usually means the path to the exe is incorrect. Verify it with the dir command or windows.

I haven't used the command line tool in a while, but the command should invoke mysqldump.exe (not mysql.exe). Also, the syntax is more like below. There's no space in between the parameters (-u and -p ) and the values, and the output is sent to a file, ie ">"

c:\dumps>"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe" -uroot -pThePassword TheDatabaseName > C:\dumps\office.sql

Open in new window

That should work, but will display a warning: [Warning] Using a password on the command line interface can be insecure.. The docs suggest storing passwords in ./~my.conf instead.

To invoke a command from CF, you can use CFExecute. However, get things working from the command line first. Then worry about plugging it into CF.

jameskane

ASKER

Thanks _agx_

The command works and I am able to generate the backup

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe" -uroot -pBuddyholly_69 office > C:\mysql_backups\office.sql

I have tried to execute it with a cmf page using cfexecute. The code is below

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>BACK UP GENERATOR</title>
</head>

<body>

ITS STARTED

<cfexecute name = "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe"
arguments = "-uroot -pBuddyholly_69 office" 
outputFile = "C:\mysql_backups\office_cf.sql"
timeout = "10"> 
</cfexecute>

ITS DONE
</body>
</html>

Open in new window

Unfortunately this generated the following error

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jameskane

ASKER

No luck I'm afraid - get the error message (only showing partial) as per attached image. I rechecked the text on the command line and it works. See copy of command line below.

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe" -uroot -pBuddyholly_69 office > C:\mysql_backups\office_cf.sql

Here is the code of the page I ran

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>BACK UP GENERATOR</title>
</head>

<body>

ITS STARTED

<!---<cfexecute name = "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe"
arguments = "-uroot -pBuddyholly_69 office" 
outputFile = "C:\mysql_backups\office_cf.sql"
timeout = "10"> 
</cfexecute>--->

<cftry>  
   <!--- Increase timeout to account for db size. Larger db's require more time for backup --->
   <cfexecute name=' "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe" '
      arguments=" -uroot -pBuddyholly_69 office"
      errorVariable="errorMessage"
      outputFile="C:\mysql_backups\office_cf.sql"
      timeout="120" />  
	
      errorMessage = <cfoutput>#errorMessage#</cfoutput><br>
      Output File: <cfdump var="#GetFileInfo('C:\mysql_backups\office_cf.sql')#">
	
      <cfcatch>  
         FAILED: <cfdump var="#cfcatch#">  
      </cfcatch> 
</cftry> 

ITS DONE
</body>
</html>

Open in new window

_agx_

Not sure that's caused by CF. It can often mean the windows CLASSPATH is too long.

Can you run any commands from cfexecute, like a simple echo?

<cftry>  
   <!--- Increase timeout to account for db size. Larger db's require more time for backup --->
   <cfexecute name=' "C:\windows\system32\cmd.exe" '
      arguments="/c echo This worked "
      errorVariable="errorMessage"
      variable="result"
      timeout="200" />  
	
	  <cfoutput>
      ErrorMessage = #errorMessage#<br>
      Result = #Result#<br>
	  </cfoutput>
	
      <cfcatch>  
         FAILED: <cfdump var="#cfcatch#">  
      </cfcatch> 
</cftry>

Open in new window

SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jameskane

ASKER

Great _agx_ taking out the " "" did the trick !!
Below is the revised code and also attached an image with the output resulting from running the revised code (below)

Many thanks again !

James

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<!---<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />--->
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>BACK UP GENERATOR</title>
</head>

<body>


<cftry>  
   <!--- Increase timeout to account for db size. Larger db's require more time for backup --->
   <cfexecute name='C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe'
      arguments=" -uroot -pBuddyholly_69 office"
      errorVariable="errorMessage"
      outputFile="C:\mysql_backups\office_cf.sql"
      timeout="120" />  
	
      <!---errorMessage = <cfoutput>#errorMessage#</cfoutput><br>--->
      <cfdump var="#GetFileInfo('C:\mysql_backups\office_cf.sql')#">
      Votre sauvegarde a reussi
	
      <cfcatch type="any">  
         Votre sauvegarde a echoue: <cfdump var="#cfcatch#">  
      </cfcatch> 
</cftry> 


</body>
</html>

Open in new window

jameskane

ASKER

Fantastic as usual !!!!

_agx_

You're welcome. You can use the same approach for the restore as well. Just change the command and arguments.

As an aside, automated backup/restores can - and sometimes do - go wrong sometimes. So have a backup plan (no pun intended ;-) or always keep a good copy in case something goes wrong.