本文介绍了一种自制的MySQL主从复制方案，包括创建表、修改表及数据迁移的具体实现。通过Java编程实现了表结构与数据的一致性同步。
以下是主要的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);
}
}
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
