Discussion Mysql Database Backup

[Ephestion]

I know how to do basic MySQL backups and have some scripts that I have used in the past. But I am having trouble with an old machine that I decided to setup as a server. None of the modern software I used to use works with the XP 32bit system. I hav elost my license for Navicat so I have been trying to find a way with MySQL GUI tools (the older versions of MySQL Workbench). I have tried to type out the manual commands for - mysqld but I am not getting expected results.

 

So I am in a bit of a situation because I want to format the HDD and take off Win 32 and put on a system that can operate with more than 4GB RAM and run overall better. Before I can do that I need an effective way to backup the entire L2GLS, L2JGS, L2GCS  databases and make sure that I can dump each table in a separate file so I can then put them back into their respective folders when I want to run the default database installation file.

 

The Tables must be separate SQL files for two main reasons, the PC doesn't have the computing power or memory to process a full database dump of the Game Server and secondly as I mentioned above, it makes it easier to install the backed up files on the new Operating System. Please provide authentic solutions as I have read many articles but non clearly stipulate how to backup a database table by table for each database.

[Tryskell]

What's the problem with mysqldump ? And be serious, if your computer can handle a MySQL server, it got the power to backup it... Simply crashes all third-party softwares (antivirus, etc) to faster the operation and avoid external read/write accesses.

 

http://webcheatsheet.com/sql/mysql_backup_restore.php

https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

 

Once you got your backup, nothing stops you to open it and query it wherever you want.

[Ephestion]

My game server has a 5 min approx. I had to even increase the timeout parameters for many things including the java timeout parameters. Also it has only 4GB Ram and Celeron CPU but win xp is seeing 3.4GB RAM. It's slow and when I used regular SQL dump it puts everything in one file (eventually). But when so much data is in one file it causes it load into memory and that is when the PC hangs. I can't import large SQL's on this system. Which is why I need to find a thinner system that can optimise more memory.  Those links don't help me:

 

shell> mysqldump [options] db_name [tbl_name ...] Need to manually know the name of every table and run it individually for each table
shell> mysqldump [options] --databases db_name ... dumps entire database in one SQL
shell> mysqldump [options] --all-databases ..... dumps each database into a single SQL

 

is there are command for example C:\mysql\bin mysqld uname psswrd dbase "tables" and auto dump for each table in database?

[Tryskell]

http://dba.stackexchange.com/questions/9306/how-do-you-mysqldump-specific-tables ?

 

It's not like all tables are useful to keep, until you use prehistoric pack and got everything on sql instead of xml, and you edited every single table yourself. Still, 4g is far enough (even a Vista uses something like 1.5g, which leave you with 2.2g left), so I don't know how you handle or admin your computer, but it's plain wrong.

 

PS : http://stackoverflow.com/questions/3669121/dump-all-mysql-tables-into-separate-files-automagically

Edited February 16, 2017 by Tryskell

[vampir]

On Tales when database is very big, backup might take several minutes. Server is managing automatic backup during middle of the night(when amount of players is low) and during server restart.

Code: http://pastebin.com/ZE72CB1F

It surely will give errors at first, ConfigHolder will be the reason.

[Ephestion]

Well I exported the entire database as an sql which I could not read back into the server if i needed to restore the database. So I was lucky that the new OS and the installation accepted the backup SQL's. Everything is running better except some script issues. But my initial question remains unanswered and from a technical point of view would like to know if the only way is to develop some kind of custom script to perform the backup.

[thanosdk]

Well I exported the entire database as an sql which I could not read back into the server if i needed to restore the database. So I was lucky that the new OS and the installation accepted the backup SQL's. Everything is running better except some script issues. But my initial question remains unanswered and from a technical point of view would like to know if the only way is to develop some kind of custom script to perform the backup.

This is just an example of a .bat script, you have to change your mysql folder and login/gameserver databases names and path, then add a task in Windows task scheduler at whatever interval suit you and it will do the backup automatically.

@echo off
Set FDate=%Date:~-10,10%
Set Fdate=%FDate:/=-%
MD C:\backup\%FDate%
MD C:\backup\%FDate%\yourlogindb
MD C:\backup\%FDate%\yourgameserverdb

copy /y C:\mysql\DATA\yourlogindb\* C:\backup\%FDate%\logindb
copy /y C:\mysql\DATA\yourgameserverdb\* C:\backup\%FDate%\gameserverdb

Edited February 19, 2017 by thanosdk

[karizma]

Important information that helped me.