数据库表格迁移到NFS的工具

最新推荐文章于 2023-07-27 17:00:56 发布

leinchu

最新推荐文章于 2023-07-27 17:00:56 发布

阅读量155

点赞数

分类专栏： Mysql Java

本文链接：https://blog.csdn.net/leinchu/article/details/113477951

版权

Java 同时被 2 个专栏收录

22 篇文章 1 订阅

订阅专栏

Mysql

9 篇文章 0 订阅

订阅专栏

nfs，说白了就相当于windows的共享文件夹。

为什么要弄这个东西——我们的项目一开始没有去考虑单机不能承载的问题，现在要考虑了，把java改成分布式的了，但问题又来了，单个数据库有无法支撑多台服务器的查询。怎么办？最先想到的把数据表格放在内存映射里面，但实验发现性能提升很少，而且还要冒着丢失数据的风险。后来，想起了nfs这个东西。经过一番实验，发现innodb用nfs性能下降近一半，但myisam几乎没有损失！所以，这个工具应运而生。

我的java没有用springboot，因为我是外行入门，并且向来不喜欢庞大的东西，因为他们效率相对较低。我也没有那么时间去学那些条条款款，去踩坑，自己的弄东西，简单、效率高、够用。所以下面的代码，你要用的话，得自己改改，


import java.io.BufferedReader;
import java.io.File;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

/*有省略不宜公开的东西*/

import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

public class TableMigrateTool {
	static final String html1 = "<!doctype html>\r\n" + 
			"<html>\r\n" + 
			"<head>\r\n" + 
			"<meta charset=\"utf-8\">\r\n" + 
			"<title>数据库表迁移工具</title>\r\n" + 
			"</head>\r\n" + 
			"<style>\r\n" + 
			"div, p, body{\r\n" + 
			"	font-size:12px;\r\n" + 
			"}\r\n" + 
			".gou{\r\n" + 
			"	width: 20px; height:15px;\r\n"
			+ "display: inline-block;\r\n"+
			"	background-position:left top;\r\n" + 
			"	background-repeat:no-repeat;\r\n" + 
			"	background-image: url(\"data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABQAAAAUCAYAAACNiR0NAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJbWFnZVJlYWR5ccllPAAAAyJpVFh0WE1MOmNvbS5hZG9iZS54bXAAAAAAADw/eHBhY2tldCBiZWdpbj0i77u/IiBpZD0iVzVNME1wQ2VoaUh6cmVTek5UY3prYzlkIj8+IDx4OnhtcG1ldGEgeG1sbnM6eD0iYWRvYmU6bnM6bWV0YS8iIHg6eG1wdGs9IkFkb2JlIFhNUCBDb3JlIDUuMy1jMDExIDY2LjE0NTY2MSwgMjAxMi8wMi8wNi0xNDo1NjoyNyAgICAgICAgIj4gPHJkZjpSREYgeG1sbnM6cmRmPSJodHRwOi8vd3d3LnczLm9yZy8xOTk5LzAyLzIyLXJkZi1zeW50YXgtbnMjIj4gPHJkZjpEZXNjcmlwdGlvbiByZGY6YWJvdXQ9IiIgeG1sbnM6eG1wPSJodHRwOi8vbnMuYWRvYmUuY29tL3hhcC8xLjAvIiB4bWxuczp4bXBNTT0iaHR0cDovL25zLmFkb2JlLmNvbS94YXAvMS4wL21tLyIgeG1sbnM6c3RSZWY9Imh0dHA6Ly9ucy5hZG9iZS5jb20veGFwLzEuMC9zVHlwZS9SZXNvdXJjZVJlZiMiIHhtcDpDcmVhdG9yVG9vbD0iQWRvYmUgUGhvdG9zaG9wIENTNiAoV2luZG93cykiIHhtcE1NOkluc3RhbmNlSUQ9InhtcC5paWQ6NUY4RThFRDg2MzhEMTFFQjlBQkJBMDAzMjY0NzE1NDgiIHhtcE1NOkRvY3VtZW50SUQ9InhtcC5kaWQ6NUY4RThFRDk2MzhEMTFFQjlBQkJBMDAzMjY0NzE1NDgiPiA8eG1wTU06RGVyaXZlZEZyb20gc3RSZWY6aW5zdGFuY2VJRD0ieG1wLmlpZDo1RjhFOEVENjYzOEQxMUVCOUFCQkEwMDMyNjQ3MTU0OCIgc3RSZWY6ZG9jdW1lbnRJRD0ieG1wLmRpZDo1RjhFOEVENzYzOEQxMUVCOUFCQkEwMDMyNjQ3MTU0OCIvPiA8L3JkZjpEZXNjcmlwdGlvbj4gPC9yZGY6UkRGPiA8L3g6eG1wbWV0YT4gPD94cGFja2V0IGVuZD0iciI/PoA8KfgAAACnSURBVHjaYvj//z8DCAe03eKEsSnBTAxUBoPfQBZsgn5TuAjpYwPinzDOppxvjGS5EKgRjJENAwJGanqZkeIwBAbHfyhTl+gwJGQYLMxAYQ0NAtJciBR2ZCeb/1DMBjMIm+uINhApGfzElqTwJSsWXGkQZCjIVUiRQNB1WF2I5EWsyYLsrIcUCYy/v///QLThxBZfvpM5wZhQ8cUIIkZW8UV1AwECDABzWKscHVzkGAAAAABJRU5ErkJggg==\");	\r\n" + 
			"}"
			+ "\r\n" + 
			".main{\r\n" + 
			"	float:left;\r\n" + 
			"	width:100%;\r\n" + 
			"	margin:0 auto;\r\n" + 
			"}\r\n" + 
			"\r\n" + 
			".main_form{\r\n" + 
			"	width:1000px;\r\n" + 
			"	margin:0 auto;\r\n" + 
			"	border:1px solid #CCC;\r\n" + 
			"}\r\n" + 
			"\r\n" + 
			".main_title{\r\n" + 
			"	background-color:#06F;\r\n" + 
			"	color:#FFF;\r\n" + 
			"	margin:0;\r\n" + 
			"	padding:0;\r\n" + 
			"	line-height:32px;\r\n" + 
			"	font-size:21px;\r\n" + 
			"	text-align:center;\r\n" + 
			"}\r\n" + 
			"\r\n" + 
			"#table_list{\r\n" + 
			"	min-height:360px;\r\n" + 
			"	overflow-y: auto;\r\n" + 
			"	margin:0 auto;\r\n" + 
			"	width:90%;\r\n" + 
			"}\r\n" + 
			"\r\n" + 
			".table_name{\r\n" + 
			"	width:33%;\r\n" + 
			"	float:left;\r\n" + 
			"	line-height:18px;\r\n" + 
			"}\r\n" + 
			"</style>\r\n" + 
			"<body>\r\n" + 
			"<div class=\"main\">\r\n" + 
			"	<div class=\"main_form\">\r\n" + 
			"    	<div class=\"main_title\">表迁移</div>\r\n" + 
			"        <div style=\"margin:6px;position: relative;\" id=\"dcontent\">\r\n"+
			"        	<div>迁移位置：<input type=\"text\" id=\"dir_name\">&nbsp;&nbsp;确保该位置所有者为mysql:mysql，且有权限（700）。另外此目录不能存储了其它同名的表。</div>\r\n" + 
			"            <div>\r\n" + 
			"            	选择表：\r\n" + 
			"                <div id=\"table_list\">\r\n" + 
			"                	加载中……\r\n" + 
			"                </div>\r\n" + 
			"            </div>\r\n" + 
			"            <div><input type=\"checkbox\" value=\"迁移\" id=\"to_myisam\" checked>转换为MyIsam(MyIsam在NFS下效率无损失，InnoDB损失48%)</div>\r\n" + 
			"            <div><input type=\"button\" value=\"迁移\" onclick=\"mig()\" id=\"sub_btn\"></div>\r\n" + 
			"		 <div id=\"msgs\" style=\"position: absolute;z-index:1;background-color:#fff;width: 100%;display: none;top:0;left:0; overflow-y: auto;\">msg</div>\r\n" + 
			"        </div>\r\n" + 
			"    </div>\r\n" + 
			"</div>\r\n"+ 
			"<script src=\"?act=init\"></script>\r\n" + 
			"</body>\r\n" + 
			"</html>";

	private static void startPage(HttpServletResponse httpServletResponse) {
		WebUtils.outPrint(httpServletResponse, html1);
	}
	private static HashMap<String, String> tbp = new HashMap<>();
	private static void listTables(HttpServletResponse httpServletResponse) {
		Mapers mapper = DbUtil.getMapper(true, "table_id_manger");
		httpServletResponse.addHeader("Content-Type", "text/javascript");
		ArrayList<String> tables = mapper.getStringArray("show tables");
		String out = "var tables = [";
		String con = "";
		tbp.clear();

		for(int i=0;i<tables.size();i++) {
			String tb = tables.get(i);
			Map<String, Object>	 ct = mapper.selectMap("show create table  `"+tb+"`");
			String create_str = (String) ct.get("Create Table");
			String[] cs = create_str.split("ENGINE");
			String p1 = cs[1];
			String[] ps = p1.split("\\/\\*");
			String p = ps[0].replaceAll("\n", "").substring(1);			
			tbp.put(tb, p);
			out += con + "{'name':'"+tb+"', 'p':\""+p+"\", 'f':"+(p.contains("DIRECTORY")?"true":"false")+"}";
			con = ",\n";
		}

		out += "];\nfunction $(id){return document.getElementById(id);}\r\n" + 
				"function showATable(t){"
				+ "	var d = document.createElement(\"div\");\r\n"
				+ "	d.className=\"table_name\";\r\n"
				+ "	$(\"table_list\").appendChild(d);\r\n"
				+ "	var html='';\r\n"
				+ "	if(t.p.indexOf(\"DIRECTORY=\")!=-1){\r\n"
				+ "		d.style.color=\"green\";\r\n"
				+ "		html +='<input type=\"checkbox\" onclick=\"selectTable(this, \\''+t.name+'\\')\"><span class=\"gou\"></span>'+t.name+'';\r\n"
				+ "	} else {"
				+ "		if(t.name.toLocaleLowerCase().indexOf('_mig_bak')!=-1){\r\n"
				+ "			d.style.color=\"orange\";\r\n"
				+ "			html +='<input type=\"checkbox\" onclick=\"selectTable(this, \\''+t.name+'\\')\">&nbsp;'+t.name+'';\r\n"
				+ "		}else{\r\n"
				+ "			html +='<input type=\"checkbox\" onclick=\"selectTable(this, \\''+t.name+'\\')\">&nbsp;'+t.name+'';\r\n"
				+ "		}\r\n"
				+ "}\r\n"
				+ "	d.innerHTML=html;\r\n"
				+ "	d.title=t.p;\r\n"
				+ "}\r\n" + 
				"$(\"table_list\").innerHTML=\"\";\r\n" + 
				"for(var i=0;i<tables.length;i++){\r\n" + 
				"    showATable(tables[i]);\r\n" + 
				"}\r\nvar selectedTbs = {}\r\n" + 
				"function selectTable(obj, n){\r\n" + 
				"    selectedTbs[n] = obj.checked;\r\n" + 
				"}\r\n";

		out += "var selectedTbs = {}\r\n" + 
				"function selectTable(obj, n) {\r\n" + 
				"    selectedTbs[n] = obj.checked;\r\n" + 
				"}\r\n" + 
				"\r\n"
				+ "function cs(url){\r\n" + 
				"    var s = document.createElement('script');\r\n" + 
				"    s.src = url;\r\n" + 
				"    document.body.appendChild(s);\r\n" + 
				"}\r\n" + 
				"function mig() {\r\n" + 
				"    if($('dir_name').value.length<2) {\r\n" + 
				"        $('dir_name').focus();\r\n" + 
				"        return;\r\n" + 
				"    }\r\n" + 
				"\r\n" + 
				"    var table_str = \"\";\r\n" + 
				"    var con = \"\";\r\n" + 
				"    for(var n in selectedTbs) {\r\n" + 
				"        if(selectedTbs[n]) {\r\n" + 
				"            table_str += con + n;\r\n" + 
				"            con = \",\";\r\n" + 
				"        }\r\n" + 
				"    }\r\n" + 
				"\r\n" + 
				"    var s = document.createElement('script');\r\n" + 
				"    s.src = \"?act=mig&dir=\"+$('dir_name').value+\"&to_myisam=\"+($('to_myisam').checked?'yes':'no')+\"&tbs=\"+table_str;\r\n" + 
				"    document.body.appendChild(s);\r\n$('sub_btn').disable=true;\r\n"
				+ "clearMsg();" + 
				"}\r\n";

		out += "function getxy(e){\r\n" + 
				" var a=new Array()\r\n" + 
				" var t=e.offsetTop;\r\n" + 
				" var l=e.offsetLeft;\r\n" + 
				" var w=e.offsetWidth;\r\n" + 
				" var h=e.offsetHeight;\r\n" + 
				" while(e=e.offsetParent){\r\n" + 
				" t+=e.offsetTop;\r\n" + 
				" l+=e.offsetLeft;\r\n" + 
				" }\r\n" + 
				" a[0]=w;a[1]=h;\r\n" + 
				" a[2]=l;a[3]=t;\r\n" + 
				" return a;\r\n" + 
				"}\r\n";

		out += "function addMsg(msg, flag){\r\n"
				+ "if(msg.length<1)return;\r\n" + 
				"    o.innerHTML = o.innerHTML + msg+'';\r\n" + 
				"}\r\n"
				+ "function clearMsg(){\r\n" + 
				"    $('msgs').innerHTML='';\r\n" + 
				"}\r\n" + 
				"function sc(url){\r\n" + 
				"    var s = document.createElement('script');\r\n" + 
				"    s.src = url;\r\n" + 
				"    document.body.appendChild(s);\r\n" + 
				"}\r\n"+
				"var o = $('msgs');\r\n" + 
				"var dc = $('dcontent');\r\n" + 
				"var xy = getxy(dc);\r\n" +
				"o.style.height=(xy[1])+\"px\";\r\n"
				+ "console.log((xy[1])+'px');\r\n" +
				"function closeMsg(){\r\n"
				+ "	$('sub_btn').disable=false;$('msgs').style.display='none';\r\n"
				+ "}\r\n";

		WebUtils.outPrint(httpServletResponse, out);
	}
	private static boolean miging =false;
	private static boolean mig_done = false;
	private static String[] tbs;
	private static String dir;
	private static String deal_msg = "";
	private static String getMigMsg() {
		String msg = deal_msg;
		deal_msg = "";
		return msg;
	}
	private static void quietSql(Mapers mapper, String sql) {
		try {
			mapper.doSQL(sql);
		} catch (Exception e) {
			if(e.getMessage().indexOf("Duplicate")==-1)
				System.out.println("> sql="+sql+", msg="+e.getMessage());
		}
	}
	private static void migTable(HttpServletRequest httpServletRequest , HttpServletResponse httpServletResponse) {
		String out = "";
		dir = httpServletRequest.getParameter("dir");
		String cf1 = httpServletRequest.getParameter("cf1");
		String to_myisam = httpServletRequest.getParameter("to_myisam");
		String tbs_str = httpServletRequest.getParameter("tbs");
		tbs = tbs_str.split(",");
		Integer idx = httpServletRequest.getParameter("idx")!=null?CommonUtils.parseInt(httpServletRequest.getParameter("idx")):null;
		String url = "?act=mig&to_myisam="+to_myisam+"&dir="+dir+(cf1!=null?("&cf1=y"):"")+"&tbs="+tbs_str+(idx!=null?("&idx="+idx):"");
		if(idx==null) {
			Properties props = System.getProperties();
			String os = props.getProperty("os.name");
            System.out.println("操作系统的名称：" + os);
            if(!os.contains("Windows")) {
				File f = new File(dir);
				if(!f.exists()) {
					out = "alert('"+dir+"不存在！');$('sub_btn').disable=false;";
				} else {
					String[] command = { "/bin/sh", "-c", "ls -lth "+dir+" |awk '{print $3,$4}" };
	            	try {
						Process ps = Runtime.getRuntime().exec(command );
						String str;
						String rs = "";  
						BufferedReader buffer = new BufferedReader(new InputStreamReader(ps.getInputStream()));
				        while((str=(buffer.readLine()))!=null) {
				            rs += str;
				        }
				        if(!rs.contains("mysql mysql")) {
				        	out= "if(confirm('"+dir+"不是mysql:mysql用户所有(所有者："+rs+")，是否继续')){\r\n" + 
				        			"    sc('"+url+"&cf1=y');\r\n" + 
				        			"} else $('sub_btn').disable=false;";
				        } else {
				        	out= "addMsg('检查目录："+dir+"', 1); $('msgs').style.display='';sc('"+url+"&idx=0');\r\n";
				        }
					} catch (Exception e) {
						out = "alert('发生错误"+e.getMessage().replaceAll("[\n\r']", "")+"');$('sub_btn').disable=false;";
					}
				}
            } else out= "addMsg('发现系统为："+os+"，推定为在Windows下运行Java迁移Linux的数据库，请自行保证迁移目录合要求。', 1); $('msgs').style.display='';sc('"+url+"&idx=0');\r\n";
		} else {
			if(!miging) {
				miging = true;
				mig_done = false;
				Thread mig_th = new Thread(new Runnable() {
					@Override
					public void run() {
						String sql;
						String otb;
						String ntb;
						Mapers mapper = DbUtil.getMapper(true, "table_id_manger");
						IdAndName ia;
						String table_space = MD5.encrypt(dir);
						
						sql = " create tablespace "+table_space+" add datafile '"+dir+"/"+table_space+".ibd' file_block_size=8192; ";
						try {
							quietSql(mapper, sql);
						} catch(Exception e) {
							deal_msg += "出错："+e.getMessage().replaceAll("[\n\r']", "");
							mig_done = true;
						}
						if(miging) {
							for(int i=0;i<tbs.length;i++) {
								try {
									deal_msg += "<hr>开始处理："+tbs[i]+" ("+(i+1)+"/"+tbs.length+") <br>";
									otb = tbs[i]+"_mig_bak";
									ntb = tbs[i];
	
									Map<String, Object>	 ct = mapper.selectMap("show create table  `"+ntb+"`");
									String create_str = (String) ct.get("Create Table");
									String[] cs = create_str.split("ENGINE");
									String p1 = cs[1];		
									String[] ps = p1.split("\\s", 2);
									String engine = ps[0].replaceAll("\n", "").substring(1);

									sql = "RENAME TABLE `"+ntb+"` TO `"+otb+"`";
									mapper.doSQL(sql);
									deal_msg += sql+"<br/>";
									
									if(engine.toLowerCase().equals("innodb") && (to_myisam==null || !to_myisam.equals("yes"))) {
										sql = cs[0]+ " ENGINE="+engine+" TABLESPACE="+table_space+" "+ps[1];
									} else if(engine.equals("myisam") || (to_myisam!=null && to_myisam.equals("yes"))) {
										sql = cs[0]+ " ENGINE="+engine+"  INDEX DIRECTORY = '"+dir+"'  DATA DIRECTORY = '"+dir+"' "+ps[1];
									} else {
										deal_msg += "暂不支持表"+tbs[i]+"的引擎"+engine+"暂不支持<br/>";
										continue;
									}
										
									mapper.doSQL(sql);
									deal_msg += "新表已创建<br/>";
										
									sql = " count(*) id FROM `"+otb+"`";
									ia = mapper.SelectIdAndName(sql);
									
									deal_msg += "开始迁移数据，共"+ia.getId()+"<br/>";
									
									sql = "INSERT INTO "+ntb+" SELECT * FROM "+otb;
									mapper.doSQL(sql);
									
									deal_msg += ""+tbs[i]+"处理完成<br>";
								} catch(Exception e) {
									deal_msg += "出错："+e.getMessage().replaceAll("[\n\r']", "");
									log("出错："+CommonUtils.getStackTrace(e)+"\nSQL="+sql);
									mig_done = true;
								}
							}
							mig_done = true;				
						}
					}
				});
				mig_th.start();
				out= "addMsg('迁移中开始', 1);sc('"+url+"');";
			} else {
				if(miging) {
					out= "addMsg('"+getMigMsg()+"', 2);\r\n";
					if(mig_done) {
						miging = false;
						out += "$('msgs').innerHTML=$('msgs').innerHTML+'<br><input type=\"button\" onclick=\"closeMsg();\" value=\"关闭\">';";
					} else out += "sc('"+url+"');";
				} else out= "addMsg('流程出错', 0)\r\n";
			} 
		}
		WebUtils.outPrint(httpServletResponse, out);
	}
	private static void log(String msg) {
		CommonUtils.Log(CommonUtils.getNowDateTimeString() + ": " + msg + "\n", "asn.log", true);
	}
	public static void route(HttpServletRequest httpServletRequest , HttpServletResponse httpServletResponse, String data, String requestUri) {
		String act = httpServletRequest.getParameter("act");
		if(act==null) {
			startPage(httpServletResponse);			
		} else if(act.equals("init")) {
			listTables(httpServletResponse);
		} else if(act.equals("mig")) {
			migTable(httpServletRequest, httpServletResponse);
		}
	}
}

leinchu

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库表格迁移到NFS的工具

nfs，说白了就相当于windows的共享文件夹。为什么要弄这个东西——我们的项目一开始没有去考虑单机不能承载的问题，现在要考虑了，把java改成分布式的了，但问题又来了，单个数据库有无法支撑多台服务器的查询。怎么办？最先想到的把数据表格放在内存映射里面，但实验发现性能提升很少，而且还要冒着丢失数据的风险。后来，想起了nfs这个东西。经过一番实验，发现innodb用nfs性能下降近一半，但myisam几乎没有损失！所以，这个工具应运而生。我的java没有用springboot，因为我是外行入门，并且
复制链接

扫一扫