How to quickly upload large dump to MySQL?

Started by Franklin, Mar 02, 2023, 12:27 AM

Previous topic - Next topic

FranklinTopic starter

Is there a comparable feature in MySQL to PostgreSQL's binary dump that allows for pouring into multiple streams? I ask because there is a sizable dump in MySQL that needs to be filled once a week using the command mysql dbname < dbname.sql, and it typically lasts for several weeks.

On a related note, does anyone know if there are any additional convenient backup options available for MySQL?
  •  

Tatwa Technologies

To optimize MYISAM, it is advisable to enlarge the key_buffer_size. When dealing with InnoDB, a helpful approach is to enclose the dump with the SET FOREIGN_KEY_CHECKS=0 command at the start and SET FOREIGN_KEY_CHECKS=1 command at the conclusion.

For additional information on enhancing bulk data loading in InnoDB, consider referring to the following resource: https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-bulk-data-loading.html.
  •  

VivianStevenson


SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

In order to optimize InnoDB bulk data loading, it is recommended to temporarily disable autocommit, unique checks, and foreign key checks. This can be done by setting the corresponding variables to 0. Once the bulk data loading is completed, these checks can be re-enabled by setting them back to 1 and performing a commit. More information on this topic can be found in the MySQL documentation: https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-bulk-data-loading.html
  •  

bewennick

In my experience, I have only encountered recovery from a cold backup, which essentially involves copying files. However, to carry out this process, it is crucial to first backup the database files. This is typically done on a replica that is temporarily stopped in order to avoid any downtime on the master server.

Traditional backup and recovery methods tend to be less effective when dealing with databases that are several hundred gigabytes in size.

When it comes to managing large databases, alternative approaches such as incremental backups, replication, or even cloud-based solutions can prove to be more efficient and reliable. These methods offer faster backup and recovery times, as well as improved scalability and flexibility. Additionally, implementing specialized tools and technologies specific to database management can further enhance the overall backup and recovery process.
  •