自制mysql主从复制（实时）软件实现_MySQL

自制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() {		ArrayListlist = 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> 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 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				ArrayListlist = 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>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 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 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);		}			}}