Home >Database >Mysql Tutorial >自制mysql主从复制(实时)软件实现_MySQL

自制mysql主从复制(实时)软件实现_MySQL

WBOY
WBOYOriginal
2016-06-01 13:10:371133browse

以下是主要的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);		}			}}


Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn