0

I need some details on importing and exporting a MySQL 8 database.

  1. If the entire database is backed up in one dump, is it possible to restore a single table, or should the individual tables be exported?

  2. When setting FOREIGN_KEY_CHECKS, UNIQUE_CHECKS, and AUTOCOMMIT to 0, is this done to the database that you are importing to from MySQL? If so, it seems to have no effect for me. If not, please provide the details on how to do these settings.

  3. Should the settings in 2.) be done to the database prior to exporting in order to get faster importing?

This 12 hour import of one table with 5M records and several indexes is for the birds.

AmigoJack
  • 5,234
  • 1
  • 15
  • 31
Jim Sawyer
  • 21
  • 2

1 Answers1

0
  1. Since the dump is one large text file consisting of almost only SQL commands you can also delete portions of it, especially the CREATE TABLE and INSERT INTO ones that you wish to skip. Exporting only portions of a database has the same effect. Sometimes you don't have the choice (either no text editor available dealing with large files, or no source database to export available).
  2. Those are to increase performance when writing (importing), whereas on reading (exporting) they have no effect and would not make sense (you don't check constaints when reading). They are in the dump, because you always plan to import it again - it's not a log of the export, but merely wants to be user friendly so you don't have to do it. The manual:
  1. No. Export and import are unbound to each other - you don't want to disable any constraints or checks when just reading (exporting) data and you also would not have any benefit from doing so.

What you haven't stated is how you import and with which program: via console and the "mysql" client (i.e. mysql < data.sql)? Have you checked the operating system's performance during the import to see a bottleneck (i.e. high disk usage, much swapping, no more RAM left, CPU usage...)?

AmigoJack
  • 5,234
  • 1
  • 15
  • 31
  • I have used exported mostly from WorkBench but have done a bit from the command line. I import from the command line. Do I do the foreign_key_checks, unique_checks, and autocommit from MySQL or from the /Bin directory? Because I can't make them have an effect. It seems to me that I would have to set these values to 0 after importing to use the database. But this has not been my experience. The database works fine including all the indexes, indicating I never had them turned off in the first place it seems to me. – Jim Sawyer Jun 01 '21 at 20:23
  • Read the manual from the start: "Server System Variables" are MySQL scope, not operating system scope. You set them in the SQL prompt, not in the shell. The [`SET` syntax](https://dev.mysql.com/doc/refman/8.0/en/set-variable.html) is MySQL specific and not meant for Unix - your dump file entirely consists of SQL and not one bit of shell commands. Directories are irrelevant, because the shell is irrelevant. – AmigoJack Jun 01 '21 at 21:07