Ask a Question

It takes a long time to fill a dump of the base MYSQL. Why is it taking so long to dump the MYSQL database?

+3 votes
by
Hi all, I have a mysql database of 670 mb. Installed on Ubuntu 18. There was a move to a new server and the dump took a long time to load. Here is the console view https://cloud.mail.ru/public/3hPj/3neoXmPjV

Running the commandMysql -v -u root -p main_data < /tmp/dump....sql

What could be wrong? How to speed it up?
by
Try adding such keys to the mysql command...

--add-locks is an option that adds LOCK TABLES before execution and UNLOCK TABLE after each table dump. It is used to speed up MySQL access;

The --quick and --opt options are recommended if the MySQL database is too large to fit entirely in memory.

3 Answers

0 votes
by
 
Best answer
Do not create indexes before adding data.
A typical database dump consists of two sections:
1. database structure
2. database data
The database structure creates tables: their fields and indices.
You need to cut the code of these indexes and add the creation of the indexes to the end of the dump.
Then the speed of importing the dump will increase by 50 times.
The speed is also affected by the type of tables - myisam is faster than innodb.
0 votes
by
It is faster to move the database directory with rsinkom or mariabackup (if you have mariadb)
0 votes
by
670 mb shouldn't fill up fast =)
by
N No. The size was the same) Where to look config? Here etcmysqlmysql.conf.dmysqld.cnf? What kind of parameters should be? Maybe it affects it:

key_buffer_size		= 16M
max_allowed_packet	= 16M
query_cache_limit	= 1M
query_cache_size        = 16M
by
Dos So as not to describe here, for example, here is an article on how to configure MySQL to use:
https://ruhighload.com/%D0%9E%D0%BF%D1%82%D0%B8%D0...

And here you can find ready (approximate) configurations for a particular amount of memory on the server:
https://ruhighload.com/mycnfexample?ram=1
by
Check the configuration of MySQL itself...how much is allocated to it...as an option...
by
Dos Maybe the DB used to be smaller? :)
by
Yes, but it was faster before.
by
Maybe the server is not that fast, maybe it is busy with something. You have to watch.
...