Is your database starting to get that not-so-fresh feeling? Is your DBErrors.log file larger than your naughty movie collection? Look no further! Use these queries to make your database sparkle brighter than a Twilight cosplay convention.
[ol][li]First, make sure your worldserver.conf file is set to auto-clean the character DB during startup. To do this, browse into your Build\bin\Release folder and open “worldserver.conf”[/li]
[li]Set CleanCharacterDB = 1 (Optional: also set PersistentCharacterCleanFlags = 25 to continue fully cleaning the DB every time the server boots up - see CharacterDBCleanup.txt for more info. You may also need to edit this setting in your
[worldstates](http://www.trinitycore.info/Worldstates_tc2) table if the worldserver doesn’t change it automatically)[/li]
[li]Also set CharDelete.Method = 1, and modify CharDelete.MinLevel and CharDelete.KeepDays however you like (CharDelete.KeepDays will need to be greater than 0 for this cleaning to work)[/li]
[li]Choose one of the queries below:[/li]
[SIZE=12px]>[/SIZE] Clean Auth AND Characters:
- Clean Auth/Characters DB (REMEMBER TO BACKUP!)
- Set date to remove all accounts made BEFORE that time
- Example shown will remove all accounts made before 2010
- Remember to replace “auth” and “characters” with the names of your databases
SET @date := “2010-01-01 00:00:00”; – Set date and time here
SET @timestamp := (SELECT UNIX_TIMESTAMP(@DATE));
last_login < @date;
account_data WHERE NOT EXISTS (SELECT NULL FROM
deleteDate=@timestamp WHERE NOT EXISTS (SELECT NULL FROM
[SIZE=12px]>[/SIZE] Clean Characters Only:
- Clean Characters DB (REMEMBER TO BACKUP!)
- Set time stamp to remove all characters made BEFORE that time
- Use unixtimestamp.com to find an exact Unix time stamp
- Remember to replace “characters” with the name of your database
SET @deletedate := “1293861600”; – Set UNIX time stamp here
deleteDate = @deletedate WHERE
logout_time < @deletedate;
According to src/server/game/Entities/Player/Player.cpp, the character cleaning method will run the following query:
SELECT guid, deleteInfos_Account FROM characters WHERE deleteDate IS NOT NULL AND deleteDate < '%u';
‘%u’ is the Unix timestamp of the current date minus CharDelete.KeepDays. This means that if you run a similar query against your database (using UnixTimestamp.com), you will find all of the characters that will be deleted.
SELECT COUNT(*) FROM characters WHERE deleteDate IS NOT NULL AND deleteDate < 1311051600;
This method of cleaning will effectively remove ALL character data, even data from other tables - character_social, character_pet, guild_eventlog, etc.
Optimize! Depending on the amount of data deleted from your database in the above steps, the optimization alone could reduce the size of your database by half!
mysqlcheck --optimize -u root -p db_name
The above steps could likely take hours to complete if your database is large, so plan ahead and have some scheduled maintenance. Another important note: because of the way the “optimize” function works, it will need at maximum double the disk space of your current largest table (not database - table), so make sure you have plenty of room on your hard drive.
Don’t forget to √ Like This this post if it helps!