Database

papuna · June 13, 2010

hello maxcheaters community

Players and developers i want to ask you about database save.

is any program which can keep my database? for example in every 5 or 10 min?

sorry if there is topic like this, i can not find it :-* :-*

eX · June 13, 2010

mysql databce?

for mysql use navicat

papuna · June 13, 2010

yes i know and is there any way to keep my database for example after rewriting windows etc?

xAddytzu · June 13, 2010

What are you talking about ? Auto-Backup ?

papuna · June 13, 2010

yes yes

addytzu have you skype ???

xAddytzu · June 13, 2010

yes yes

addytzu have you skype ???

Yes, I have

You could to make a task, this is only for shutdown/restart

Index: l2jfree-core/config/options.properties
===================================================================
--- l2jfree-core/config/options.properties	(revision 8052)
+++ l2jfree-core/config/options.properties	(working copy)
@@ -400,3 +400,6 @@

# Whether to optimize tables each time server is launched
OptimizeDatabaseTables = True
+
+# Whether to dump tables each time server is launched and shutdown
+DumpDatabaseTables = True
\ No newline at end of file
Index: l2jfree-core/src/main/java/com/l2jfree/Config.java
===================================================================
--- l2jfree-core/src/main/java/com/l2jfree/Config.java	(revision 8052)
+++ l2jfree-core/src/main/java/com/l2jfree/Config.java	(working copy)
@@ -1254,6 +1254,7 @@
	public static int				RETARGET_BLOCKING_PERIOD;

	public static boolean			OPTIMIZE_DATABASE;
+    public static boolean			DUMP_DATABASE;

	// *******************************************************************************************
	private static final class OptionsConfig extends ConfigLoader
@@ -1465,6 +1466,7 @@
		    RETARGET_BLOCKING_PERIOD = Integer.parseInt(optionsSettings.getProperty("CannotRetargetFor", "400"));

		    OPTIMIZE_DATABASE = Boolean.parseBoolean(optionsSettings.getProperty("OptimizeDatabaseTables", "True"));
+            DUMP_DATABASE = Boolean.parseBoolean(optionsSettings.getProperty("DumpDatabaseTables", "True"));
		}
	}

Index: l2jfree-core/src/main/java/com/l2jfree/gameserver/GameServer.java
===================================================================
--- l2jfree-core/src/main/java/com/l2jfree/gameserver/GameServer.java	(revision 8052)
+++ l2jfree-core/src/main/java/com/l2jfree/gameserver/GameServer.java	(working copy)
@@ -20,6 +20,7 @@
import java.util.HashSet;
import java.util.Set;

+import com.l2jfree.gameserver.util.TableDumper;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

@@ -190,6 +191,8 @@
		_log.info("IdFactory: Free ObjectID's remaining: " + IdFactory.getInstance().size());
		if (Config.OPTIMIZE_DATABASE)
			TableOptimizer.optimize();
+        if (Config.DUMP_DATABASE)
+            TableDumper.saveDB();
		Class.forName(RunnableStatsManager.class.getName());
		ThreadPoolManager.getInstance();
		if (Config.DEADLOCKCHECK_INTERVAL > 0)
Index: l2jfree-core/src/main/java/com/l2jfree/gameserver/Shutdown.java
===================================================================
--- l2jfree-core/src/main/java/com/l2jfree/gameserver/Shutdown.java	(revision 8052)
+++ l2jfree-core/src/main/java/com/l2jfree/gameserver/Shutdown.java	(working copy)
@@ -14,6 +14,7 @@
 */
package com.l2jfree.gameserver;

+import com.l2jfree.gameserver.util.TableDumper;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

@@ -220,7 +221,10 @@

		SQLQueue.getInstance().run();
		System.out.println("Data saved. All players disconnected, " + _mode.getText() + ".");
-		
+
+        if (Config.DUMP_DATABASE)
+            TableDumper.saveDB();
+
		try
		{
			Thread.sleep(1000);
Index: l2jfree-core/src/main/java/com/l2jfree/gameserver/util/TableDumper.java
===================================================================
--- l2jfree-core/src/main/java/com/l2jfree/gameserver/util/TableDumper.java	(revision 0)
+++ l2jfree-core/src/main/java/com/l2jfree/gameserver/util/TableDumper.java	(revision 0)
@@ -0,0 +1,129 @@
+/*
+ * This program is free software: you can redistribute it and/or modify it under
+ * the terms of the GNU General Public License as published by the Free Software
+ * Foundation, either version 3 of the License, or (at your option) any later
+ * version.
+ * 
+ * This program is distributed in the hope that it will be useful, but WITHOUT
+ * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
+ * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
+ * details.
+ * 
+ * You should have received a copy of the GNU General Public License along with
+ * this program. If not, see <http://www.gnu.org/licenses/>.
+ */
+package com.l2jfree.gameserver.util;
+
+import java.io.File;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.Statement;
+import java.util.ArrayList;
+
+import com.l2jfree.Config;
+import org.apache.commons.lang.StringUtils;
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+
+import com.l2jfree.L2DatabaseFactory;
+
+public final class TableDumper
+{
+	private static final Log _log = LogFactory.getLog(TableDumper.class);
+	
+	public static void saveDB()
+	{
+		Connection con = null;
+		try
+		{
+			con = L2DatabaseFactory.getInstance().getConnection();
+			Statement st = con.createStatement();
+			
+			final ArrayList<String> tables = new ArrayList<String>();
+			{
+				ResultSet rs = st.executeQuery("SHOW FULL TABLES");
+				while (rs.next())
+				{
+					String tableType = rs.getString(2/*"Table_type"*/);
+					
+					if (tableType.equals("VIEW"))
+						continue;
+					
+					tables.add(rs.getString(1));
+				}
+				rs.close();
+			}
+			
+			{
+				ResultSet rs = st.executeQuery("CHECK TABLE " + StringUtils.join(tables, ","));
+				while (rs.next())
+				{
+					String table = rs.getString("Table");
+					String msgType = rs.getString("Msg_type");
+					String msgText = rs.getString("Msg_text");
+					
+					if (msgType.equals("status"))
+						if (msgText.equals("OK"))
+							continue;
+					
+					_log.warn("TableSaver: CHECK TABLE " + table + ": " + msgType + " -> " + msgText);
+				}
+				rs.close();
+				
+				_log.info("TableSaver: Database tables have been checked.");
+			}
+			
+			{
+				ResultSet rs = st.executeQuery("ANALYZE TABLE " + StringUtils.join(tables, ","));
+				while (rs.next())
+				{
+					String table = rs.getString("Table");
+					String msgType = rs.getString("Msg_type");
+					String msgText = rs.getString("Msg_text");
+					
+					if (msgType.equals("status"))
+						if (msgText.equals("OK") || msgText.equals("Table is already up to date"))
+							continue;
+					
+					_log.warn("TableSaver: ANALYZE TABLE " + table + ": " + msgType + " -> " + msgText);
+				}
+				rs.close();
+				
+				_log.info("TableSaver: Database tables have been analyzed.");
+			}
+			
+			{
+                File DirDump = new File(Config.DATAPACK_ROOT, "dump/");
+				ResultSet rs = st.executeQuery("SELECT * FROM " + StringUtils.join(tables, ",") + " INTO DUMPFILE '" + DirDump + "'");
+				while (rs.next())
+				{
+					String table = rs.getString("Table");
+					String msgType = rs.getString("Msg_type");
+					String msgText = rs.getString("Msg_text");
+					
+					if (msgType.equals("status"))
+						if (msgText.equals("OK") || msgText.equals("Table is already up to date"))
+							continue;
+					
+					if (msgType.equals("note"))
+						if (msgText.equals("Table does not support dump, doing recreate + analyze instead"))
+							continue;
+					
+					_log.warn("TableSaver: Create dump " + table + ": " + msgType + " -> " + msgText);
+				}
+				rs.close();
+				
+				_log.info("TableSaver: Database tables have been dumped.");
+			}
+			st.close();
+		}
+		catch (Exception e)
+		{
+			_log.warn("TableSaver: Cannot dump database tables!", e);
+		}
+		finally
+		{
+			L2DatabaseFactory.close(con);
+		}
+	}
+}

papuna · June 13, 2010

pls send me your skype in PM it's too hard to post in every 5 sec

xAddytzu · June 13, 2010

I offer support only in forum :)

papuna · June 13, 2010

so you can not? :D

ok, i know that there is a program with save database for example in 5-10 min but i dont know what program is it.

xAddytzu · June 13, 2010

Yes, I have

You could to make a task, this is only for shutdown/restart

Index: l2jfree-core/config/options.properties
===================================================================
--- l2jfree-core/config/options.properties	(revision 8052)
+++ l2jfree-core/config/options.properties	(working copy)
@@ -400,3 +400,6 @@

# Whether to optimize tables each time server is launched
OptimizeDatabaseTables = True
+
+# Whether to dump tables each time server is launched and shutdown
+DumpDatabaseTables = True
\ No newline at end of file
Index: l2jfree-core/src/main/java/com/l2jfree/Config.java
===================================================================
--- l2jfree-core/src/main/java/com/l2jfree/Config.java	(revision 8052)
+++ l2jfree-core/src/main/java/com/l2jfree/Config.java	(working copy)
@@ -1254,6 +1254,7 @@
	public static int				RETARGET_BLOCKING_PERIOD;

	public static boolean			OPTIMIZE_DATABASE;
+    public static boolean			DUMP_DATABASE;

	// *******************************************************************************************
	private static final class OptionsConfig extends ConfigLoader
@@ -1465,6 +1466,7 @@
		    RETARGET_BLOCKING_PERIOD = Integer.parseInt(optionsSettings.getProperty("CannotRetargetFor", "400"));

		    OPTIMIZE_DATABASE = Boolean.parseBoolean(optionsSettings.getProperty("OptimizeDatabaseTables", "True"));
+            DUMP_DATABASE = Boolean.parseBoolean(optionsSettings.getProperty("DumpDatabaseTables", "True"));
		}
	}

Index: l2jfree-core/src/main/java/com/l2jfree/gameserver/GameServer.java
===================================================================
--- l2jfree-core/src/main/java/com/l2jfree/gameserver/GameServer.java	(revision 8052)
+++ l2jfree-core/src/main/java/com/l2jfree/gameserver/GameServer.java	(working copy)
@@ -20,6 +20,7 @@
import java.util.HashSet;
import java.util.Set;

+import com.l2jfree.gameserver.util.TableDumper;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

@@ -190,6 +191,8 @@
		_log.info("IdFactory: Free ObjectID's remaining: " + IdFactory.getInstance().size());
		if (Config.OPTIMIZE_DATABASE)
			TableOptimizer.optimize();
+        if (Config.DUMP_DATABASE)
+            TableDumper.saveDB();
		Class.forName(RunnableStatsManager.class.getName());
		ThreadPoolManager.getInstance();
		if (Config.DEADLOCKCHECK_INTERVAL > 0)
Index: l2jfree-core/src/main/java/com/l2jfree/gameserver/Shutdown.java
===================================================================
--- l2jfree-core/src/main/java/com/l2jfree/gameserver/Shutdown.java	(revision 8052)
+++ l2jfree-core/src/main/java/com/l2jfree/gameserver/Shutdown.java	(working copy)
@@ -14,6 +14,7 @@
  */
package com.l2jfree.gameserver;

+import com.l2jfree.gameserver.util.TableDumper;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

@@ -220,7 +221,10 @@

		SQLQueue.getInstance().run();
		System.out.println("Data saved. All players disconnected, " + _mode.getText() + ".");
-		
+
+        if (Config.DUMP_DATABASE)
+            TableDumper.saveDB();
+
		try
		{
			Thread.sleep(1000);
Index: l2jfree-core/src/main/java/com/l2jfree/gameserver/util/TableDumper.java
===================================================================
--- l2jfree-core/src/main/java/com/l2jfree/gameserver/util/TableDumper.java	(revision 0)
+++ l2jfree-core/src/main/java/com/l2jfree/gameserver/util/TableDumper.java	(revision 0)
@@ -0,0 +1,129 @@
+/*
+ * This program is free software: you can redistribute it and/or modify it under
+ * the terms of the GNU General Public License as published by the Free Software
+ * Foundation, either version 3 of the License, or (at your option) any later
+ * version.
+ * 
+ * This program is distributed in the hope that it will be useful, but WITHOUT
+ * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
+ * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
+ * details.
+ * 
+ * You should have received a copy of the GNU General Public License along with
+ * this program. If not, see <http://www.gnu.org/licenses/>.
+ */
+package com.l2jfree.gameserver.util;
+
+import java.io.File;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.Statement;
+import java.util.ArrayList;
+
+import com.l2jfree.Config;
+import org.apache.commons.lang.StringUtils;
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+
+import com.l2jfree.L2DatabaseFactory;
+
+public final class TableDumper
+{
+	private static final Log _log = LogFactory.getLog(TableDumper.class);
+	
+	public static void saveDB()
+	{
+		Connection con = null;
+		try
+		{
+			con = L2DatabaseFactory.getInstance().getConnection();
+			Statement st = con.createStatement();
+			
+			final ArrayList<String> tables = new ArrayList<String>();
+			{
+				ResultSet rs = st.executeQuery("SHOW FULL TABLES");
+				while (rs.next())
+				{
+					String tableType = rs.getString(2/*"Table_type"*/);
+					
+					if (tableType.equals("VIEW"))
+						continue;
+					
+					tables.add(rs.getString(1));
+				}
+				rs.close();
+			}
+			
+			{
+				ResultSet rs = st.executeQuery("CHECK TABLE " + StringUtils.join(tables, ","));
+				while (rs.next())
+				{
+					String table = rs.getString("Table");
+					String msgType = rs.getString("Msg_type");
+					String msgText = rs.getString("Msg_text");
+					
+					if (msgType.equals("status"))
+						if (msgText.equals("OK"))
+							continue;
+					
+					_log.warn("TableSaver: CHECK TABLE " + table + ": " + msgType + " -> " + msgText);
+				}
+				rs.close();
+				
+				_log.info("TableSaver: Database tables have been checked.");
+			}
+			
+			{
+				ResultSet rs = st.executeQuery("ANALYZE TABLE " + StringUtils.join(tables, ","));
+				while (rs.next())
+				{
+					String table = rs.getString("Table");
+					String msgType = rs.getString("Msg_type");
+					String msgText = rs.getString("Msg_text");
+					
+					if (msgType.equals("status"))
+						if (msgText.equals("OK") || msgText.equals("Table is already up to date"))
+							continue;
+					
+					_log.warn("TableSaver: ANALYZE TABLE " + table + ": " + msgType + " -> " + msgText);
+				}
+				rs.close();
+				
+				_log.info("TableSaver: Database tables have been analyzed.");
+			}
+			
+			{
+                File DirDump = new File(Config.DATAPACK_ROOT, "dump/");
+				ResultSet rs = st.executeQuery("SELECT * FROM " + StringUtils.join(tables, ",") + " INTO DUMPFILE '" + DirDump + "'");
+				while (rs.next())
+				{
+					String table = rs.getString("Table");
+					String msgType = rs.getString("Msg_type");
+					String msgText = rs.getString("Msg_text");
+					
+					if (msgType.equals("status"))
+						if (msgText.equals("OK") || msgText.equals("Table is already up to date"))
+							continue;
+					
+					if (msgType.equals("note"))
+						if (msgText.equals("Table does not support dump, doing recreate + analyze instead"))
+							continue;
+					
+					_log.warn("TableSaver: Create dump " + table + ": " + msgType + " -> " + msgText);
+				}
+				rs.close();
+				
+				_log.info("TableSaver: Database tables have been dumped.");
+			}
+			st.close();
+		}
+		catch (Exception e)
+		{
+			_log.warn("TableSaver: Cannot dump database tables!", e);
+		}
+		finally
+		{
+			L2DatabaseFactory.close(con);
+		}
+	}
+}

mogo · June 13, 2010

mysqldump -u USERNAME -pPASSWORD DATABASE > backup.sql

Combine that with crontab/scheduled tasks, filenames including the date and you have a perfect auto-backup system :)

Database

Question

papuna

10 answers to this question

Recommended Posts

eX

papuna

xAddytzu

papuna

xAddytzu

papuna

xAddytzu

papuna

xAddytzu

mogo

Create an account or sign in to comment

Create an account

Sign in

Posts

Topics

Browse

Activity

Store