Cannot upload "to big" mysql file?

[viking60]

Often when we want to backup Mysql files we need to adjust the file sizes the server accepts for upload.
So in /etc/php/php.ini we have some max sizes to adjust
Like:
upload_max_filesize, memory_limit and post_max_size

But still I I get protests like
Got a packet bigger than 'max_allowed_packet' bytes
And that is not to be found in php.ini

For that we need to find my.cnf
in my case it is in /etc/mysql/my.cnf
And in that file you will find

Code: Select all

max_allowed_packet = 10M

increase this value and restart mysqld:

Code: Select all

sudo systemctl restart mysqld

This goes for MariaDB too.

if you have altered something in /etc/php/php.ini you may want to restart the server with

Code: Select all

sudo systemctl restart httpd

After this you should be able to upload your big mysql database.

[viking60]

I have found out as config and software changes that the above method is unpractical. So I use the CLI mysql aproach to import big databases now.

I download the databases from some site with phpmyadmin or adminer. Here you set if the download should create the database etc.
(There are no restriction on size when exporting a database - the problems occurs when you try to import them.)

That gives me "mydbase.sql" in my download section.

If I have chosen to let this sql create the database (during export) then I import it with this command
In my download directory:

Code: Select all

mysql -u root -p  < mydbase.sql

If I have not chosen the sql file to create the database then I need to create the database in phpmyadmin or adminer (or in the cli) first. Let us say I named it "mydatabase".

That would result in this command:

Code: Select all

mysql -u root -p mydatabase < mydbase.sql

No complaints about the size etc etc.

In the examples above my mysql user is root - it could be something else in your case. (root in mysql is not the same as Linux root confusing but true )It is like David Cameron and Cameron Diaz - they are not related - they just share the same name

Phpmyadmin and Adminer are still fine for handling databases - but not in importing big databases - in fact they suck at it They are not the only guilty party since they depend on apache and php but the result is a big headache when you try to import databases; nevertheless.

MariaDB and Mysql are the specialists there so I use them for that.

There are alternatives like Bigdump - but I have not tested it..because I don't need it.