Home >Database >Mysql Tutorial >自制mysql主从复制(实时)软件实现_MySQL
以下是主要的java实现代码:具体设计参考
自制mysql主从复制(实时)软件——实现 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_t_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<String>list = MySql.executeQuery(sql_s_t1,1);// 1 代表链接数据库datacenterb int listsize = list.size(); String table = null; String sql_create[] = new String[2]; for (int i = 0; i < listsize; i++) {//get p2.table name table = list.get(i); /* * judge the create table languages */ sql_create = sqlCreate(table); try {//执行建表(正常建表) int rs = new MySql().update(sql_create[1],3); if(rs!=0){ Report_File.operate_log("2013create table success ,From: "+table); }else{ Report_File.operate_log("2013create table error ,From: "+table); } } catch (Exception e) { Report_File.operate_log("The table doesn't exist in database (datacenterb)"); }//end try //建完表后决定是否需要修改 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 method public void alter_table(String sql_alter,String table) { //修改表 try { int rs_alt = new MySql().update(sql_alter,3); if (rs_alt!=0) { Report_File.operate_log("2013alter table success ,From: "+table); }else{ Report_File.operate_log("2013alter 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<Vector<String>> sql_create_list = MySql.executeQuery(sql_s_t_schema, 1, "string"); int sql_create_listsize = sql_create_list.size(); String sql_create[] = new String[2];//define dyadic array for (int k = 0; k < sql_create_listsize; k++) {//get table name and table schema Vector<String> 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_language sql_create_p = table_row.get(l); try { //solve the sql_language for table_name int pk =sql_create_p.indexOf("PRIMARY KEY"); int begin =sql_create_p.lastIndexOf("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_increment sql_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 exception System.out.println("replace the create table languages error"+e.toString()); } }//end for l }//end for k return 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");//格式化时间为text ArrayList<String>list = MySql.executeQuery(sql_s_t,1);// datacenterb 中查询表 int listsize = list.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<Vector<String>>listdata = MySql.executeQuery(sql_select, 1,"select");//在迁移源头表中查找数据 try { int listdatasize = listdata.size(); if (listdatasize==0) {//保存没数据的表 Report_File.getNoData_Save(table, sp.format(new Date())); }else { StringBuilder sql_insert_p = new StringBuilder();//依然是StringBulider 方式处理 for (int j = 0; j < listdatasize; j++) { Vector<String> row = listdata.get(j); sql_insert_p.append(this.getInsert_SQL(row));//this private// if (j%1000==0||j==listsize-1) {//分批次提交效果 if (j==20000||j==35000||j==55000||j==70000||j==listdatasize-1) {//分批次提交效果 sql_insert_p.delete((sql_insert_p.length()-8), sql_insert_p.length());//去掉尾部的" ,(null,' " String sql_insert = sql_insert_p.toString(); this.insertTable(sql_insert, table);//执行插入数据 sql_insert_p.delete(0, sql_insert_p.length());// 清空StringBulider为空 }//end if }//end for j }//end else }catch(Exception e) { e.printStackTrace(); Report_File.getError_save(table, sp.format(new Date()));//保存操作中出错表 }//end try }//end for i }//end method private StringBuilder getInsert_SQL(Vector<String> row){//private 构造insert 表的语句 int size = row.size(); // 获取集合大小 StringBuilder sql_insert_data = new StringBuilder();//StringBuilder 处理字符串拼接过程 for(int i = 1; i < size; i++) { // 写入每一行 if (row.get(i)==null) {//判定数据是否为空 sql_insert_data.setLength(0);//丢掉数据 }else { sql_insert_data.append(row.get(i)); if (i==(size-1)) { sql_insert_data.append("'),(null,'");//最后一个 }else { sql_insert_data.append("','");}//不是最后一个}//丢掉数据 }//end else }// end for return sql_insert_data; } /* * 有id auto_increament * 一种是:insert into tablename values (null,'',''); * 另外一种是:insert into tablename (item1,item2)values ('',''); * 一一对应的效果 */ private void insertTable(String sql,String table) {//执行插入操作 // int length = table.length();//合并年表需要处理插入的表名称// 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); } }}