Jump to content

Mysql Database Backup


Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by Tryskell
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by thanosdk
Link to comment
Share on other sites

  • 2 years later...

Please sign in to comment

You will be able to leave a comment after signing in



Sign In Now


  • Posts

    • Chapter III is now LIVE!   -Max level is increased to 85 -Max level on subclass increased to 85 -Maximum attribute allowed in weapons is increased to 150 -Maximum attribute allowed in armors is increased to 60 -Ant Queen, Core, Orfen increased to level 83, same chance on Epic Jewel, addition of Eas/Ews/Beas/Bews drop. -Addition of Base Tower, Advance Pedestal & Tower of Naia Elpy teleport into Deltuva.   https://remastered.playinera.com/ https://discord.com/invite/WD8fWf5
    • Auto macro is already in the files. It's more important to focus on fixing the critical issues, like melee range critical skills on hero weapons.. These are the most important things for the server at the moment. You'll need more than 3 months and 2300 server restarts to address them all. ps I can sell you the fixes for a good price 🙂 Remember like before?   
    • Hello, could anyone correct the problem?
    • DISCORD : utchiha_market telegram  https://t.me/utchiha_market SELLIX STORE : https://utchiha-market.mysellix.io/ Join our server for more products : https://discord.gg/hoodservices
    • Grand Opening May 17th   Rates EXP/SP x50 Adena x50 Spoil x5 Seal Stones x5 Quest x5   Augmentation Skill and Glow chance are retail. Active and passive skills has the same status. Active skills increased time to 20 minutes.   Buffs Time 120 minutes. 24 slots, +4 divine inspiration. (need learn) Pets does not lose buffs with noblesse.   Clan & Alliance Clan penalties disabled. Alliance only 1 clan - for large crest. Max. 18 members in Raidboss and Siege zones.   Castle Sieges Sieges every 2 weeks. Siege duration 60 minutes. Only 1 castle available for sieges.   Enchant Weapons from +0 to +3: 100% from +3 to +16: 70% (decreasing 4% each level) Armors from +0 to +3: 100% from +3 to +10: 50% (decreasing 4% each level) Blessed enchant on failure returns the item +3.   Events Capture the Flag - Every day at 12:30 and 18:30. Crazy Rates - On weekends, full time. Death Match - Every day at 14:30 and 20:30. Kill the Boss - Every day at 21:30. Party Farm - On weekends at 16:00. Team vs Team - Every day at 16:30 and 22:30. Treasure Hunt - On Sundays, from 14:00 to 14:30.   Olympiads Ends on Mondays. Battle period: 20:00-00:00. Start battles: 5 registered players. No class-based battles. Tie does not lose points. Hero skills are retail.   Special NPCS Buffer: All buffs, including schemes. Class Master: 1st, 2nd and 3rd class free. Exchange Manager: Exclusive exchange system. General Manager: Shop, donation, services, functions, server infos... Global Gatekeeper: Teleport to all kingdoms and areas, free. Siege Manager: Contains info for all castles. Wedding Manager: Formal wear and adena required to get married.   Soul Crystals Stages from 7 to 12 selling at luxury shop by cristals. (retail) Stage 13 Anakazel and Ember: chance 15%. Antharas, Frintezza and Valakas: chance 100%.     Subclass & Noblesse Subclass free. (no quest) Add subclass at all Village Master. Max. 5 subclasses. Noblesse quest. (retail)   Voice Commands .menu - main features including auto farm & potion. .help - contains all available commands.     Additional Features Auto loot. Auto learn skills. Inventory slots 150. Seven Signs open 24/7. Shout/Trade chat delay 10s. Hero chat delay 30s. Chat filter - for illegal words. Offline shop. Shift + click on monsters to see the droplist. Spawn protection by 30 seconds. Max. 3 sessions per pc. Automatic restart at 07:00.   Raid Bosses Common Raids: 18h + 6h random. Barakiel: 6h. (no random) Tyrannosaurus: 5 minutes. Raids listed on the site/npc have improved stats and drops.   Sailren: Monday at 21:00. (individual teleport and locked after boss spawn) Queen Ant: Monday at 22:00.   Core: Tuesday at 21:00. Orfen: Tuesday at 22:00.   Dr. Chaos: Wednesday at 21:00. Zaken: Wednesday at 22:00. (door opens 00:00 game time and when boss is spawned)   Andreas van Halter: Thursday at 21:00. Frintezza: Thursday at 21:00. (random +/- 15 minutes. Need CC, min.2/max.5 parties)   Anais: Friday at 21:00. Baium: Friday at 22:00. (random +/- 15 minutes)   Antharas: Saturday at 21:00.   Valakas: Sunday at 21:00. All Epics are in PvP area and will be dead at server launch.   Changes Bishop level 40 has Noblesse Blessing skill. All debuffs with time greater than 30s have been reduced to 30s. All cancel skills remove buffs for 10 seconds. Cancellation increased reuse time x5 and chance to 100. Deflect Arrow increased power from 40 to 60. Mirage reduced time from 60s to 30s. Dodge increased the time from 5s to 15s, reuse time x2. Counterattack increased the time from 5s to 15s, reuse time x2. Touch of Death increased chance from 80 to 100. Arrest range increased from 150 to 600. (like shackle) Stun/Shock effect time reduced to 5 seconds. FOI works like in GF. (removed on any action except movement) Major Arcana Set 15% cast speed, 2% m.atk. instead of 17% m.atk. Imperial Staff adds Acumen instead of Empower. Removed fear skills from Antharas and Valakas. Removed teleport skills from Zaken.
  • Topics

×
×
  • Create New...