3

I'm trying to import an older (~version 5) server into a MariaDB docker container.

I created a dump using the mysqldump command (with --all-databases), and when I try to import it I get the following error:

ERROR 1050 (42S01) at line 2071: Table 'user' already exists

I'm trying to import into a brand new container, using the standard command:

 mysql -uroot -p  < [sqldump].sql

I'm a novice when it comes to SQL and was wondering if I'm missing something obvious, or if there is some real incompatibility.

I'd really appreciate your advice.

Thanks!

dkd6
  • 155
  • 1
  • 9

2 Answers2

2

It sounds like your dump script wants to overwrite the system "mysql" database that contains users and privilege information. If that's really what you want to do, you can run mysqldump again with the --add-drop-database option, then FLUSH PRIVILEGES after import. I wouldn't recommend that though, especially if the databases are running different versions.

A better option would be to explicitly choose which databases you want to export by passing their names to the --databases argument, excluding DBs like "mysql", "information_schema" and "performance_schema". You would then grant privileges to the newly imported DBs as needed.

Another quick option would be to edit your existing dump file in a text editor and just take out the part that wants to create the "mysql" tables (including "user" and others).

Google Fail
  • 46
  • 5
  • It sounds like the dump is not the same version as the target system? In that case there may be modifications to the `mysql` table schemas that would lead to trouble. So, do not reload `mysql` or `information_schema` or `performance_schema` or `sys`. – Rick James Dec 25 '20 at 18:21
  • THANK YOU, @Google Fail! I was attempting to "restore" a complete database dump from `mysqld Ver 5.7.33-0ubuntu0.16.04.1` to `mariadb Ver 15.1 Distrib 10.5.9-MariaDB` and am glad I heeded your *A better option would be...* advice. Otherwise I would have "restored" user and several other tables in forms not compatible with my target database engine. – CODE-REaD Apr 02 '21 at 16:50
  • You’re welcome! @CODE-REaD – Google Fail Apr 02 '21 at 16:56
0

The issue has nothing to do with Docker and probably doesn't belong under serverfault. The answer is here: https://dba.stackexchange.com/a/285253/93071

foobrew
  • 146
  • 2