Self-made mysql master-slave replication (real-time) software implementation _ MySQL

Self-made mysql master-slave replication (real-time) software-implementation of the following is the main java implementation code: Specific Design Reference

Self-made mysql master-slave replication (real-time) software -- implement http://my.oschina.net/u/1462678/blog/227152

1. create table

Package migrate; import java. util. arrayList; import java. util. vector; import mysql. mySql; public class Create_Table {static private String SQL _s_t1 = "select tablename from datacenterb. v_tables_2013 where tablename <> 'V _ tables_2013 '; "; static private String SQL _s_t_schema_p =" show create table datacenterb. "; static private String SQL _alt_p33 =" MODIFY COLUMN 'id' INT (10) UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY ('id ');"; static private String SQL _alt_t_p22 = "MODIFY COLUMN 'id' INT (10) UNSIGNED NOT NULL AUTO_INCREMENT;"; public void create_table () {ArrayList
 
  
List = MySql.exe cuteQuery (SQL _s_t1, 1); // 1 indicates the connected database datacenterbint listsize = list. size (); String table = null; String SQL _create [] = new String [2]; for (int I = 0; I <listsize; I ++) {// get p2.table nametable = list. get (I);/** judge the create table ages */SQL _create = sqlCreate (table); try {// execute table creation (normal table creation) int rs = new MySql (). update (SQL _create [1], 3); if (rs! = 0) {Report_File.operate_log ("2013 create table success, From:" + table);} else {Report_File.operate_log ("2013 create table error, From:" + table );}} catch (Exception e) {Report_File.operate_log ("The table doesn't exist in database (datacenterb )");} // end try // after the table is created, determine whether to modify if (SQL _create [0]. equals ("22") {String SQL _alter = "alter table datacenter2013." + table + SQL _alt_t_p22; alter_table (SQL _alter, table);} else If (SQL _create [0]. equals ("33") {String SQL _alter = "alter table datacenter2013." + table + SQL _alt_t_p33; alter_table (SQL _alter, table );} else {// do nothing} // end if else for judge} // end for I} // end methodpublic void alter_table (String SQL _alter, String table) {// modify table try {int rs_alt = new MySql (). update (SQL _alter, 3); if (rs_alt! = 0) {Report_File.operate_log ("2013 alter table success, From:" + table);} else {Report_File.operate_log ("2013 alter table error, From:" + table );}} catch (Exception e) {Report_File.operate_log ("Alter the table error (maybe it is from operate error)") ;}} public String [] sqlCreate (String table) {String SQL _s_t_schema = SQL _s_t_schema_p + table; ArrayList
  
   
> SQL _create_list = MySql.exe cuteQuery (SQL _s_t_schema, 1, "string"); int SQL _create_listsize = SQL _create_list.size (); String SQL _create [] = new String [2]; // define dyadic arrayfor (int k = 0; k <SQL _create_listsize; k ++) {// get table name and table schema Vector
   
    
Table_row = SQL _create_list.get (k); String SQL _create_p = null; for (int l = 1; l <table_row.size (); l ++) {// get table schema SQL _1_agesql_create_p = table_row.get (l); try {// solve the SQL _language for table_nameint pk = SQL _create_p.indexOf ("PRIMARY KEY "); int begin = require ("AUTO_INCREMENT ="); if (begin> 0) {// string replace auto_increment = number to auto_increment = 1; int end = SQL _create_p.lastIndexOf ("DEFAULT "); SQL _create [0] = "11"; // has been replaced SQL _create [1] = SQL _create_p.replace (SQL _create_p.subSequence (begin, end), "AUTO_INCREMENT = 1 ");} else if (pk> 0) {// has primary key but no auto_incrementsql_create [0] = "22"; SQL _create [1] = SQL _create_p ;} else {// no auto_increment = number, we need to add it to the table; SQL _create [0] = "33 "; // has not been replaced SQL _create [1] = SQL _create_p;} catch (Exception e) {// TODO: handle exceptionSystem. out. println ("replace the create table ages error" + e. toString () ;}// end for l} // end for kreturn SQL _create ;}}
   
  
 

2. insert table

Package migrate; import java. text. simpleDateFormat; import java. util. arrayList; import java. util. date; import java. util. vector; import mysql. mySql; public class Insert_Table {static private String SQL _s_t = "select tablename from datacenterb. v_tables_2013 where tablename <> 'V _ tables_2013 '; "; static private String SQL _s_td_p =" select SQL _NO_CACHE * from datacenterb. "; public void insertExecute () {SimpleDateFormat sp = new SimpleDateFormat (" yyyy-MM-dd "); // The formatting time is textArrayList
 
  
List = MySql.exe cuteQuery (SQL _s_t, 1); // query table int listsize = list in datacenterb. size (); String table = null; for (int I = 0; I <listsize; I ++) {table = list. get (I); String SQL _select = SQL _s_td_p + table + "group by dnt order by null;"; ArrayList
  
   
> Listdata = MySql.exe cuteQuery (SQL _select, 1, "select"); // search for data in the migration source Table try {int listdatasize = listdata. size (); if (listdatasize = 0) {// Save the table Report_File.getNoData_Save (table, sp. format (new Date ();} else {StringBuilder SQL _insert_p = new StringBuilder (); // for (int j = 0; j <listdatasize; j ++) {Vector
   
    
Row = listdata. get (j); SQL _insert_p.append (this. getInsert_ SQL (row); // this private // if (j % 1000 = 0 | j = listsize-1) {// batch submission results if (j = 20000 | j = 35000 | j = 55000 | j = 70000 | j = listdatasize-1) {// batch submission results: SQL _insert_p.delete (SQL _insert_p.length ()-8), SQL _insert_p.length (); // remove ", (null, '"String SQL _insert = SQL _insert_p.toString (); this. insertTable (SQL _insert, table); // execute the insert data SQL _insert_p.delete (0, SQL _insert_p.length ()); // clear StringBulider empty} // end if} // end for j} // end else} catch (Exception e) {e. printStackTrace (); Report_File.getError_save (table, sp. format (new Date (); // error table saved} // end try} // end for I} // end methodprivate StringBuilder getInsert_ SQL (Vector
    
     
Row) {// private statement used to construct the insert table int size = row. size (); // Obtain the set size StringBuilder SQL _insert_data = new StringBuilder (); // StringBuilder processes the string concatenation process for (int I = 1; I <size; I ++) {// write each row if (row. get (I) = null) {// Determine whether the data is empty SQL _insert_data.setLength (0); // discard the data} else {SQL _insert_data.append (row. get (I); if (I = (size-1) {SQL _insert_data.append ("'), (null ,'"); // Last} else {SQL _insert_data.append ("','");} // Not the Last} // lost Data} // end else} // end forreturn SQL _insert_data;}/** id auto_increament *: insert into tablename values (null ,'',''); * Another type is: insert into tablename (item1, item2) values ('',''); * one-to-one matching effect */private void insertTable (String SQL, String table) {// execute the insert operation // int length = table. length (); // name of the table to be inserted when the year table is merged // String insertTable = table. substring (0, length-2); String insert_ SQL = "insert into" + table + "values (Null, '"+ SQL; int rs = new MySql (). update (insert_ SQL, 3); if (rs! = 0) {Report_File.operate_log ("insert data success, From:" + table);} else {Report_File.operate_log ("insert data error, From:" + table );}}}