Trying to simplify SQL db for the new forum (export/import)

ilter

Member
Hi all,

I am moving my forum to a new domain, and I have mySQL database on my local computer.
The new host is ready with xenForo 2.8 installed and working properly with an empty db.
(cPanel and phpMyAdmin is available through my account at my host)

I uploaded my old db via ftp on to my server, but it is 289 MB in size,
and phpMyadmin only allows databases smaller than 50 MBs.
That's why I decided to make the old db smaller and leaner.
I only need my users back (without their past privilages) and old threads (to be archived, but searchable).

I am on a mac and installed MySQL and MySQL Workbench app.
I imported my old dB and am able to see the tables.

My plan is to locally export the tables in 3 or 4 (maybe more?) pieces and import them using phpMyAdmin on the server (cPanel availabe)

What would be your approach to make the new forum work?
As I said, I am totally new to this and I can't hire a developer at this stage to execute these tasks, yet.

I (and probably my users) don't care about avatars etc. I just need the threads, email addresses (to reconnect with the past users).

Thank you in advance, for any help. Appreciated.
 

Brogan

XenForo moderator
Staff member
You can't selectively import some tables without understanding the relation between them.

You should be able to export and import the whole DB using PMA by doing a few tables at a time and zipping, to keep the size under 50MB.

You can reduce the overall size by truncating the search index table - that can be rebuilt once the site is running again.
 

ilter

Member
Thank you for the reply, Brogan.

What is PMA? Is that phpMyAdmin?

I don't have the database on web in database/table form. That's why I can not 'export' in any other way.
 

dknife

Well-known member
Is it 289Mb in gzip/zip or in raw SQL? PMA allows you to export/import in compressed formats which can be significant difference in sizes.
 

ShikiSuen

Well-known member
Drop the template edit history table (but backup its structure first) and then import the structure back.

If template table is also that fat, too, then do the same thing to the template table after performing XML backup of your templates.
After recovering your data, use commandline tools to rebuild your master data.
 

ShikiSuen

Well-known member
Is it 289Mb in gzip/zip or in raw SQL? PMA allows you to export/import in compressed formats which can be significant difference in sizes.
That is likely to be in zip unless his forum is really small.

Web-based solutions like PHPMyAdmin are too weak.
If letting PHPMyAdmin to handle the import of XenForo database, your server is likely to just kill your PHP-fpm process.

If your target VPS has less than 2GB RAM then you may have NO resort to import your XenForo 2 database to it (too big).
Even if your RAM is sufficient, PHPMyAdmin can cause issues (too much limitations).
If MySQL (not MariaDB, etc.), you can use MySQL Workbench which is a cross-platform MySQL remote client software (free).
// MariaDB has similar free alternatives but it seems that only Windows version is available (compatible with CrossOver on macOS Catalina).

Still, I recommend you to do SQL database import via MySQL / MariaDB commandline (using SSH, or local terminal if you have physic access).
 

ShikiSuen

Well-known member
Oh I just noticed that the OP only has cPanel and PHPMyAdmin access.
Looks like your only approach is to use MySQL Workbench (or similar if your server uses non-oracle MySQL distros like MariaDB, etc.)
In this case you may have to manually fragmentize your MySQL database file into small parts.
 
Top