User Comments

I couldn't get mysqlhotcopy to work with the privileges the documentation say are required (Select_priv,Reload_priv). I was able to strace the thread and see mysql was returning "access denied" because mysqlhotcopy was trying to do a LOCK query.

If you are getting "access denied", try giving the appropriate user "Lock_tables_priv" and do a "FLUSH PRIVILEGES".

(mysqlhotcopy v1.18)

mysqlhotcopy needs to be run as root or a user that can access the mysql files hence killing it for any web based play/backup. interestingly enough mysqldump does accept the httpd user access as long as you have the correct mysql user/passwds

also understand that mysqlhotcopy creates a clean (flushed/locked) replica of the data files as is in your mysql data directory. it does not create a sql dump file as does mysqldump.

I've written some basic Java code that does something similar to the perl script, except with a lot less options. Maybe MySql could add it to the JDBC driver code or other, so that future downloads have this functionality, which would allow others to use it in Windows or UNIX without the need of PERL.

Here it is, use at own risk.

package au.com.infomedix.utility;

import java.io.*;
import java.sql.*;
import java.util.Calendar;
import org.apache.commons.io.FileUtils;

/**
*
* A java representation of the perl mysqlhotcopy script
* Ref: http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html
* <p>
* Some statistics for 28 files totaling 640MB:<br>
* <br>Windows Native: 104375 msecs
* <br>UNIX Native: 95520 msecs
* <br>
* <br>UNIX commons-io: 94657 msecs (1.5 mins)
* <br>WINDOWS commons-io: 96360 msecs (1.5 mins)
* <br>
*
* @TODO: Add a debug/verbose flag
* @TODO: Add more options
*
* @author Andrew Bruno
*
*/
public class MySqlHotCopy
{

private String username = null;

private String password = null;

/*
* Should always be localhost as files are assumed to be on the same server
* that mysql is running on. On Unix use 127.0.0.1 and not localhost or else
* you'll get Socket Exceptions
*/
private final String host = "127.0.0.1";

private String database = null;

private String url = null;

private String dirSourceIndex = null;

private String dirBackup = null;

private String copymode = null;

private static boolean error = false;

public MySqlHotCopy()
{
super();
}

public MySqlHotCopy( String args[] )
{
super();
username = args[0];
password = args[1];
database = args[2];
dirSourceIndex = args[3];
dirBackup = args[4];
copymode = args[5];
url = "jdbc:mysql://" + host + "/" + database;
}

/**
* @param args
*/
public static void main(String[] args)
{
if (args.length <= 0)
{
showUsageAndExit("Parameters missing", args);
}
else
{
if (args.length != 6)
{
showUsageAndExit("6 Parameters required", args);
}
else
{
MySqlHotCopy mySqlHotCopy = new MySqlHotCopy(args);

Connection conn = null;

try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection(mySqlHotCopy.url, mySqlHotCopy.username, mySqlHotCopy.password);
System.out.println("Database connection established");

Statement s = conn.createStatement();

s.executeQuery("SHOW TABLES");
ResultSet rs = s.getResultSet();

String lockSqlCommand = "LOCK TABLES ";
String flushSqlCommand = "FLUSH TABLES ";

while (rs.next())
{
String tableName = (String) rs.getObject(1);
lockSqlCommand = lockSqlCommand + tableName + " READ";
flushSqlCommand = flushSqlCommand + tableName;

if (rs.isLast())
{
lockSqlCommand = lockSqlCommand + ";";
flushSqlCommand = flushSqlCommand + ";";
}
else
{
lockSqlCommand = lockSqlCommand + ", ";
flushSqlCommand = flushSqlCommand + ", ";
}
}
rs.close();

System.out.println("Lock Sql Command is " + lockSqlCommand);
System.out.println("Flush Sql Command is " + flushSqlCommand);

s.executeUpdate(lockSqlCommand);
s.executeUpdate(flushSqlCommand);
s.executeUpdate("FLUSH LOGS");
// s.executeUpdate("RESET MASTER");
// s.executeUpdate("RESET SLAVE");

long time = Calendar.getInstance().getTimeInMillis();

if (mySqlHotCopy.copymode.equals("nativedos"))
{
System.out.println("Using Native Dos mode to copy files");

String copyCommand = "cmd /c COPY /Y \"" + mySqlHotCopy.dirSourceIndex + "\" \"" + mySqlHotCopy.dirBackup + "\"";

System.out.println("DOS Copy Command = '" + copyCommand + "'");
Process process = Runtime.getRuntime().exec(copyCommand);

DataInputStream p_in = new DataInputStream(process.getInputStream());
BufferedReader d = new BufferedReader(new InputStreamReader(p_in));

String p_str;
while ((p_str = d.readLine()) != null)
{

System.out.println(p_str);
}

if (process.exitValue() != 0)
{
System.out.println("Dos copy process exited with an error value of " + process.exitValue());
}
}
else if (mySqlHotCopy.copymode.equals("nativeunix"))
{

final String copyCommand = "/bin/cp -v " + mySqlHotCopy.dirSourceIndex + "*" + " " + mySqlHotCopy.dirBackup;

System.out.println("Using Native Unix mode to copy files");
String[] cmd = { "/bin/sh", "-c", copyCommand };

System.out.println("UNIX Copy Command = '" + copyCommand + "'");

// See
// http://www.mountainstorm.com/publications/javazine.html
Process process = Runtime.getRuntime().exec(cmd, null, null);

DataInputStream p_in = new DataInputStream(process.getInputStream());
BufferedReader d_in = new BufferedReader(new InputStreamReader(p_in));

String p_str;
while ((p_str = d_in.readLine()) != null)
{
System.out.println(p_str);
}

DataInputStream p_err = new DataInputStream(process.getErrorStream());
BufferedReader d_err = new BufferedReader(new InputStreamReader(p_err));

String p_err_str;
while ((p_err_str = d_err.readLine()) != null)
{
System.out.println(p_err_str);
}

// OutputStreamWriter osWriter = new
// OutputStreamWriter(process.getOutputStream());
// PrintWriter out = new PrintWriter(osWriter);

// if ((process != null) && (process.exitValue() != 0))
// {
// System.out.println("UNIX copy process exited with an
// error value of " + process.exitValue());
// }

}
else if (mySqlHotCopy.copymode.equals("commonsiojava"))
{
System.out.println("Using Commons IO mode to copy files");

FileUtils.copyDirectory(new File(mySqlHotCopy.dirSourceIndex), new File(mySqlHotCopy.dirBackup));

}
else
{
showUsageAndExit("copymode " + mySqlHotCopy.copymode + " not supported", args);
}

time = Calendar.getInstance().getTimeInMillis() - time;

System.out.println("Copying of files took " + time + " milleseconds");

/* I dont think this is really needed */
s.executeUpdate("UNLOCK TABLES");

s.close();

}
catch (Exception e)
{
System.err.println("Exception caught: " + e.getMessage());
e.printStackTrace();
error = true;
}
finally
{
if (conn != null)
{
try
{
conn.close();
// System.out.println("Database connection terminated");
}
catch (Exception e)
{ /* ignore close errors */
}
}
}

if (error)
System.exit(1);
else
System.exit(0);
}

}

}

private static void showUsageAndExit(String errorString, String[] args)
{
System.err.println();

if ((errorString != null) && (errorString.length() != 0))
{
System.err.println("Error Message: " + errorString);
}

if (args.length != 0)
{
System.err.print(MySqlHotCopy.class.getName() + " called with parameters ");
for (int i = 0; i < args.length; i++)
{
String string = args[i];
System.err.print(string + " ");
}
System.err.println();
}

System.out.println("Usage: au.com.infomedix.udr.utility.MySqlHotCopy username password database dbindexdir dbbackupindexdir <copymode>");
System.out.println("where <copymode> is one of: ");
System.out.println("\t nativedos");
System.out.println("\t nativeunix");
System.out.println("\t commonsiojava");

System.exit(1);

}

}

Hi andrew, First I would like to thank you for the code. But I dont understand the use of SourceIndexDir when you are asking for the username, password for the database as you can directly copy from one directory to another!!! I think, its a better option to create queries and find out the result sets and save the result sets as a back up, not copying the directories directly. Sorry if I am missing something over here.

Hi Tasin, i believe the username and password is neeeded to lock the database tables when performing a backup. Copying the index files is similiar to creating queries but with lesser effort. Cheers!

Guys, if you want to simply backup queries, then use mysqldump.

On the other hand, if there is a way to find the directory location of where the index files to the MYISAM are via an SQL query, then you could alter code, and remove the sourceIndexDir field. This would add safely, and make the code smarter.

Mysqlhotcopy does not copy all of the directories from a raid set when the number of tables is greater than ten. The raid directories are numbered using hexadecimals but mysqlhotcopy only copies directories numbered with decimals. This was found on a Linux system. Raid directories may be numbered diferently on other systems like Windows.

A bug report with a potential fix has been submitted.

While trying to copy all my databases to a new machine using mysqlhotcopy, I couldn't find anyway to do this in a single command, with or without the --regexp option. If it can be done, would someone please describe how.

This is how I finally did it in one line in bash:
cd /tmp/mysqlhotcopies/ && mysqlhotcopy --flushlog --regexp '.*' . && for d in *; do { mysqlhotcopy --flushlog --addtodest $d /tmp/mysqlhotcopies; } done

Suggestions welcomed.

-Kevin

When dumping too many databases alltogether, mysqlhotcopy may fail with
DBD::mysql::db do failed: Can't find file: '...'
To avoid this you should dump the databases separately, so instead of doing
mysqlhotcopy --regexp='.+'.'.+' <dumpdir>
do it in two or more steps, for instance
mysqlhotcopy --regexp='^[a-m].+'.'.+' <dumpdir>
mysqlhotcopy --regexp='^[n-z].+'.'.+' <dumpdir>

When dumping a db with many tables, a similar problem described above by Jacob Rief also occurs.

DBD::mysql::db do failed: File '_path_to_file_' not found (Errcode: 24) at /usr/bin/mysqlhotcopy line 466.

A similar work around using regexp to split up the number of tables to dump should work.

If there is a --all-databases option for this utility would be a more elegant solution.

Add your own comment.