MariaDB - MySQL - ALL-DB Import - Table 'user' already exists

Question

I am attempting to migrate from local MariaDB to Docker version which should in essence be as simple as migrating to a new SQL Server. I have setup the Docker container fine via but can't seem to import my "all-databases" dump.

This is what I get:

mysql -u root -p < mariadb_alldb_*.sql
Enter password:
ERROR 1050 (42S01) at line 8022: Table 'user' already exists

Dump generated via :

mysqldump -u root -p --all-databases --skip-lock-tables > mariadb_alldb_"$(date '+%F')".sql

Update: This is run on a fresh docker container each time and I have created backups in a directory that I am importing from.

ls * | grep mariadb_alldb_
mariadb_alldb_2020年05月04日.sql

Update2: Perhaps it's related to my docker setup?

Here is my docker cmd:

docker stop mariadb && docker rm mariadb
docker run -d --name="mariadb" \
-p 3306:3306 \
-e TZ="America/Whitehorse" \
-v "/opt/mariadb/conf/conf.d":"/etc/mysql/conf.d" \
-v "/opt/mariadb/backups":"/mnt/" \
--mount type=volume,dst=/var/run/mysqld,volume-driver=local,volume-opt=type=none,volume-opt=o=bind,volume-opt=device=/var/run/mysqld \
mariadb:latest

I am doing this import from the local machine, I have reproduced results from inside the container.

• dbdemon: Are you importing multiple backup sql files of the same databases? I ask because I see a wildcard in the import command. You probably only want the latest one, right?
• FreeSoftwareServers: PWD only has the one file that matches. @dbdemon
• dbdemon: Which versions of MariaDB are you using in the source and target?

Answer 1

As someone already mentioned, this has to do with the mysql.user table being changed to a view in 10.4. The problem and solution is documented on the MariaDB website in MDEV-22127.

The solution is to simply add the following two lines to the top of your all-dbs.sql dump file:

DROP TABLE IF EXISTS `mysql`.`global_priv`;
DROP VIEW IF EXISTS `mysql`.`user`;

• nhck: Thank you - saved my heart. Note to future users: You can also do this on the mysql-commandline before importing.
• 00-BBB: @nhck please elaborate? How to 'do this on the mysql-commandline before importing.'? Trying to open 20GB sql dump file to add text at the top :/ Would like to know how to do this via command line. Do not want to do the whole SQL dump all over again. Cheers
• nhck: @00-BBB you can just login using your command line client (e.g. mysql -u root -p) - and then execute the DROP Table and drop view there.
• Michael Altfield: @foobrew You got a good command to add those two lines onto a file that's hundreds of gigabytes large? Might be useful to add to the answer..

Answer 2

I've encountered a similar problem, and the issue seems to have to do with the MariaDB version. Namely, that as of MariaDB 10.4, the mysql.user table has been replaced with a view, while the real data is in the new mysql.global_priv table. This causes the DROP TABLE to fail, which, in turn, causes the CREATE TABLE to fail.

Adding --ignore-table=mysql.user to the dump results in a file that works perfectly, other than not transferring the users.

Unfortunately, the structures of mysql.user and mysql.global_priv are different enough, that if the user count is small and the permissions aren't too complex, it's better to just manually recreate the users and privileges.

Answer 3

Start using docker image mariadb:10.3, do the import there and then perform the data upgrade to a newer image. This will keep your permissions.

Answer 4

The solution was two fold. I used a script to dump "all-databases" except schema and to dump the users.

The issue, just FYI was the mysql.user table and something to do with locks...

"All-DB Dump" --> https://dba.stackexchange.com/a/69667/113202

cat << 'EOF' >mysql_all_db_dump.sh
#!/bin/bash
#https://dba.stackexchange.com/a/69667/113202
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"
DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}
DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done
MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > all-dbs.sql
EOF
chmod +x mysql_all_db_dump.sh
./mysql_all_db_dump.sh

Import:

mysql -u root -p < all-dbs.sql

Users Dump:--> https://serverfault.com/questions/8860/how-can-i-export-the-privileges-from-mysql-and-then-import-to-a-new-server/399875#399875

MYSQL_CONN="-uroot -ppassword"
mysql ${MYSQL_CONN} --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

Import:

mysql -uroot -p -A < MySQLUserGrants.sql

Now onto my next issue, all the users hostname = localhost -->

Copy MySQL User but change HostName

• dbdemon: With MariaDB 10.3+ the mysqldump program has an option to leave out a database from the dump file: "--ignore-database=dbname1" (use it multiple times to specify multiple databases). That said, you shouldn't normally have to do any of this AFAIK.