Database

[papuna]

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]

mysql databce?

for mysql use navicat

[papuna]

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

[xAddytzu]

What are you talking about ? Auto-Backup ?

[papuna]

yes yes

 

addytzu have you skype ???

[xAddytzu]

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]

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

[xAddytzu]

I offer support only in forum :)

[papuna]

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]

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]

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 :)