User Comments

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.

Instead of using --regexp and locking bunch of databases I rahter use this perl script which copies one database after another.

#!/usr/bin/perl

opendir (D, "/var/lib/mysql");
@f = readdir (D);
closedir (D);
foreach $file (@f)
{
$filename = "/var/lib/mysql/" . $file;
if (-d $filename && $file ne '.' && $file ne '..')
{
system "/usr/bin/mysqlhotcopy --allowold -u root $file /backup/mysql/";
}
}

That way only tables in one database are locked when copying files. If you have some very large database and other smaller all are locked because it takes a long time to copy the big one although smaller could be copied instantly.

-bash version of Marko Hrastovec script-

#!/bin/bash
BACKUP_DIR=/backup
for i in `/usr/bin/find /var/lib/mysql/* -type d -printf "%f\n"`;do /usr/bin/mysqlhotcopy --allowold -u root $i $BACKUP_DIR; done

When using the --noindices option, or when taking a binary backup of a MyISAM table without the MYI index files, the myisamchk -rq option didn't work for me! It seems that you need to use the inbuilt REPAIR TABLE command with the USE_FRM switch to rebuild the index file in version 4.0.2+. This caught me out a little until I found:

http://groups.google.co.uk/group/mailing.database.myodbc/browse_frm/thread/6309388ee0b0b295/45c8077d82d240e1?hl=en&lnk=st&q=mysql+backup+noindices#45c8077d82d240e1

The manual page is at

http://dev.mysql.com/doc/refman/4.1/en/repair-table.html

I had troubles with Hotcopy backup script with a error like this DBD::mysql::db do failed: File '_path_to_file_' not found (Errcode: 24) at /usr/bin/mysqlhotcopy line 466. The solution was to increase the open_file_limit in the my.cnf and restart mysql service.

-bash version of Fili with password


BACKUP_DIR=/backup

stty -echo
read -p "Enter your password for root: " pass
stty echo

for i in `/usr/bin/find /var/lib/mysql/* -type d -printf "%f\n"`;do /usr/bin/mysqlhotcopy --allowold -u root -p $pass $i $BACKUP_DIR; done

Add your own comment.