Please do NOT post requests for help here. Use the Help forum for that.
  • Page:
  • 1

TOPIC:

Ways to reduce size of the MySQL database 1 month 5 days ago #1

  • RBaker
  • RBaker's Avatar Topic Author
  • Offline
  • New Member
  • New Member
  • Posts: 27
I am not the admin of the server, it is on shared hosting.

Should I just delete the MySQL database and reload the current GEDCOM into a fresh MySQL database?

Please Log in or Create an account to join the conversation.

Last edit: by RBaker.

Ways to reduce size of the MySQL database 1 month 4 days ago #2

Why do you want to do that?
You lose e.g. the settings, all user accounts, the change log (Control panel/Manage family trees/Changes log) - I did this 2015 and lost 9 years of Change log.

As far as I can see, all gedcom related tables are deleted when you re-import it.
Frank (Warius)
wbt.warius.info with Webtrees 2.1.15 on Windows Server 2022, IIS10, PHP 8.1 latest, MYSQL 8.0 latest

Please Log in or Create an account to join the conversation.

Last edit: by Warius.

Ways to reduce size of the MySQL database 1 month 4 days ago #3

  • fisharebest
  • fisharebest's Avatar
  • Offline
  • Administrator
  • Administrator
  • Posts: 16886
You can delete old logs and change history in the control panel.

This marks the space as "unused" within MySQL, but does not return it to the operating system.

To do that, there are two choices. I prefer option 2, but it requires you server is configured with "file per table" storage.

1) export database to .SQL file. drop/recreate database, import SQL file

2) for each table, run the SQL statement: ALTER TABLE wt_xxx ENGINE=InnoDB;
Greg Roach - This email address is being protected from spambots. You need JavaScript enabled to view it. - @fisharebest@phpc.social - fisharebest.webtrees.net

Please Log in or Create an account to join the conversation.

Ways to reduce size of the MySQL database 1 month 4 days ago #4

  • RBaker
  • RBaker's Avatar Topic Author
  • Offline
  • New Member
  • New Member
  • Posts: 27


To do that, there are two choices. I prefer option 2, but it requires you server is configured with "file per table" storage.

1) export database to .SQL file. drop/recreate database, import SQL file

2) for each table, run the SQL statement: ALTER TABLE wt_xxx ENGINE=InnoDB;

I will try option #1. I did not know importing an .SQL database file to a new, empty database will clean out the old, deleted, non-used database data. Perfect.

This install is on shared hosting at Crazy hosting, they do not allow me to anything except create / delete / export / import the SQL database.

Thank You

Please Log in or Create an account to join the conversation.

Last edit: by RBaker.

Ways to reduce size of the MySQL database 1 week 6 days ago #5

  • RBaker
  • RBaker's Avatar Topic Author
  • Offline
  • New Member
  • New Member
  • Posts: 27


To do that, there are two choices. I prefer option 2, but it requires you server is configured with "file per table" storage.

1) export database to .SQL file. drop/recreate database, import SQL file

2) for each table, run the SQL statement: ALTER TABLE wt_xxx ENGINE=InnoDB;

I had time tonite to play with the MySQL database to see which size reduction mechanism would work for me. Starting with an MYSQL database that was 158MB, I have been able to reduce the MySQL database size to 46MB. My maximum size limit on the server was supposed to be 128MB, thank goodness something is/was misconfigured or flexible on their server. Perhaps there is a built-in safety factor that saved me :)

For whatever reasons, trying the two methods Greg mentioned (and I would have preferred to use), I could not produce any affect on the size of the database using either the tools in cpanel or PhpMyAdmin. The server is probably not configured properly for method #2 even though getting a green check mark in PhpMyAdmin when executed on each table. Trying method #1 two different ways failed to reduce the database size

Sally forth!
I ended up exporting the GEDCOM file from webtrees and then importing that GEDCOM into a fresh MySQL database. As mentioned by forum member Warius I did lose login and account credentials for all (7) members, plus all webtrees settings that I had changed, and HTML Blocks info, FAQ, Email settings, page block changes, etc. so it is important to back up that information to reset the webtrees to how you had it to keep what you have changed. If you choose this method then take the time to copy the settings, you will be glad you did! If you are as I am there will be a lot of different settings to copy.
Individual media permissions for documents. photo's, etc. are retained in the GEDCOM. System-wide media permissions are not.

All in all everything went smoothly, what took longest was resetting all the changes I had copied.

Please Log in or Create an account to join the conversation.

Last edit: by RBaker.
  • Page:
  • 1
Powered by Kunena Forum