复制MySQL实时_自制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() {ArrayListlist = MySql.executeQuery(sql_s_t1,1);// 1 代表链接数据库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 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 methodpublic 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 arrayfor (int k = 0; k < sql_create_listsize; k++) {//get table name and table schema Vectortable_row = sql_create_list.get(k);String sql_create_p = null;for (int l = 1; l< table_row.size(); l++) {//get table schema sql_languagesql_create_p = table_row.get(l);try {//solve the sql_language for table_nameint 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_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 languages 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");//格式化时间为textArrayListlist = 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++) {Vectorrow = 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 methodprivate StringBuilder getInsert_SQL(Vectorrow){//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 forreturn 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);}}}

相关标签：

本文原创发布php中文网，转载请注明出处，感谢您的尊重！