Create a copy of a large Database to use in a test forum ?

[webbouk]

I'm trying to import a copy of our forum database onto the server to use with a test forum prior to upgrading to 2.3, however the exported database SQL file is 3.6GB in size, or if compressed with gzip it is just under 800mb - both fail to import due to server restraints.
I've tried using the 'Copy database' withon phpMyAdmin and eventually time out.
I've increased the max file size and max upload size to accomodate the zipped file but still run into connection errors.

Using 'BigDump MySql Importer' I can do a partial upload but then hit the following error:
MySQL: Incorrect string value: '\xF0\x9F\x98\xB4 on some tables which stops the import.

Has anyone any ideas how to get around this so that I can import the database

WHM with cPanel, phpMyAdmin, php8.2. MySql 8.0.

 

[MentaL]

I'm trying to import a copy of our forum database onto the server to use with a test forum prior to upgrading to 2.3, however the exported database SQL file is 3.6GB in size, or if compressed with gzip it is just under 800mb - both fail to import due to server restraints.
I've tried using the 'Copy database' withon phpMyAdmin and eventually time out.
I've increased the max file size and max upload size to accomodate the zipped file but still run into connection errors.

Using 'BigDump MySql Importer' I can do a partial upload but then hit the following error:
MySQL: Incorrect string value: '\xF0\x9F\x98\xB4 on some tables which stops the import.

Has anyone any ideas how to get around this so that I can import the database

WHM with cPanel, phpMyAdmin, php8.2. MySql 8.0.

Post in thread 'Public service announcement: Check your MySQL backup!'

Sep 26, 2018

Actually to be on safe side you'd want to do mysql import with

mysql --default-character-set=utf8mb4

as your may have uf8mb4 mysql table and field character set/collations but your mysql client connection my not be utf8mb4 based and mysql server default client might be a different character set and collation from utf8mb4 i.e. utf8

So, for backup database, this should be it?

/usr/bin/mysqldump --opt xenforo --default-character-set=utf8mb4 > /home/nginx/domains/example.com/backup/ssh_database/ssh_backup.sql

But regarding your import setting, would it be like...

• Sunka

 

[Old Nick]

Has anyone any ideas how to get around this so that I can import the database

Export database in parts?

 

[webbouk]

Somehow the upload has worked.
I initially raised the max file size and max upload size to accommodate the zipped file via Tweaking PHP within WHM, which resulted in connection errors. However, increasing the same within the cPanel account allowed the import within phpMyAdmin

 

[MentaL]

Somehow the upload has worked.
I initially raised the max file size and max upload size to accommodate the zipped file via Tweaking PHP within WHM, which resulted in connection errors. However, increasing the same within the cPanel account allowed the import within phpMyAdmin

Hopefully you used the utf8 string on dump.

 

[duderuud]

Just be sure the dump has the UTF8MB4 collation.

 

[Robert9]

Myoos dumper
Navicat or some of the same type like Heidi SQL, DBWeaver and some more.

 

[Abu Abdullah]

To begin, please log in via SSH or access the terminal on WHM to navigate to your website directory. Once there, execute the following command:

mysqldump -u myusername -p mydatabase > mydatabase_backup.sql

*change dbuser and db name according to your existing database

After a few moments, the file named mydatabase_backup.sql will be created, the duration of which may vary based on the database size.

Next, please move or copy this file to the directory of your new test domain. After navigating to that directory, run the following command:

mysql -u newDBuser -p newDBname < mydatabase_backup.sql

*change the dbuser and db name according to yours newly created database for test site.

Hope this helps.