mysql - Copying large database is slow. How can I speed up the process? -

i copying database java db mysql. tables set correctly in mysql, , wrote method copying database.

there no errors in method, works on tables limited in size. slow large tables. took 5 min 29 sec copy 7937 records! since 1 table has 2.3 million records have wait more 26 hours...

i ran in netbeans , output here: (batch size = 2000)

 run: ------    copying table currency ------  26 rows needs copied executed batch currency, inserted last 26 elements ------    finished copying table currency ------   ------    copying table exchange ------  31 rows needs copied executed batch exchange, inserted last 31 elements ------    finished copying table exchange ------   ------    copying table markets ------  82 rows needs copied executed batch markets, inserted last 82 elements ------    finished copying table markets ------   ------    copying table trade ------  2303371 rows needs copied executed batch trade, inserted 2000 elements     have copied total of 2000/2303371 rows, 0% executed batch trade, inserted 2000 elements     have copied total of 4000/2303371 rows, 0% executed batch trade, inserted 2000 elements     have copied total of 6000/2303371 rows, 0% build stopped (total time: 5 minutes 29 seconds) 

so stopped execution when saw take longer time anticipated.

  /**      *       * @param javadb connection source database      * @param mysql connection target database      * @param orderedtables table names in order copied (so no foreign key constraints error stuff happens)      */     public static void copyfromjavadbtomysql(connection javadb, connection mysql, string[] orderedtables, int batchsize){         statement javadb_stmnt = null;         preparedstatement mysql_stmnt = null;
         try {            (int = 0; < orderedtables.length; i++){                 //print out information on table isbeing copied , how many rows table contains.                 system.out.println("------    copying table " + orderedtables[i] + " ------ ");                 int numberrows = database.executecountquery(orderedtables[i], null); //a helper method getting rows in table                 system.out.println(numberrows + " rows needs copied");
                 int totalnumbercopied = 0;
              //get resultset source table             string selectquery = "select * " + orderedtables[i];             javadb_stmnt = javadb.createstatement();             resultset rs_select = javadb_stmnt.executequery(selectquery);              //creating insert query target table. query 'insert <tablename> values (?, ?, ..., ?)'             int colcount = rs_select.getmetadata().getcolumncount();             string insert_query = "insert " + orderedtables[i] + "( ";             string valuessubstring = "values (";             (int j = 0; j < colcount-1; j++){                 insert_query += rs_select.getmetadata().getcolumnname(j+1) + ", ";                 valuessubstring += "?, ";             }             insert_query += rs_select.getmetadata().getcolumnname(colcount) + ") ";             insert_query += valuessubstring + "?)";              //use prepared statement batches             mysql_stmnt = mysql.preparestatement(insert_query);                         int counter = 0;              //iterate on resultset source table             while (rs_select.next()){                 //add batch insert statement current row in result set                 (int j = 1; j <= colcount; j++){                     object obj = rs_select.getobject(j);                     mysql_stmnt.setobject(j, obj);                 }                 mysql_stmnt.addbatch();                    //if have added enough inserts, execute batch insert                 if (++counter == batchsize){                     int[] arr = mysql_stmnt.executebatch();                     //print out progressinformation                     totalnumbercopied += arr.length;                                             double progress = (1.0*totalnumbercopied) / (1.0*numberrows) * 100;                     decimalformat df = new decimalformat("#");                     system.out.print("executed batch "+orderedtables[i]+", inserted " + arr.length + " elements");                     system.out.println("\t have copied total of " + totalnumbercopied + "/" + numberrows + " rows, " + df.format(progress) + "%");                     //reset counter batch size                     counter = 0;                 }                             }             //insert last elements             int[] arr = mysql_stmnt.executebatch();             system.out.println("executed batch "+orderedtables[i]+", inserted last " + arr.length + " elements");             system.out.println("------    finished copying table " + orderedtables[i] + " ------ \n");         }                 } catch (sqlexception ex) {         logger.getlogger(databasecopier.class.getname()).log(level.severe, null, ex);     } {         try {             javadb.close();             mysql.close();         } catch (sqlexception ex) {             logger.getlogger(databasecopier.class.getname()).log(level.severe, null, ex);         }     } 

}