1

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.

3
  • 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?
    – dbdemon
    May 4 '20 at 19:42
  • PWD only has the one file that matches. @dbdemon May 4 '20 at 19:44
  • Which versions of MariaDB are you using in the source and target?
    – dbdemon
    May 15 '20 at 20:49
4

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`;
1
  • Thank you - saved my heart. Note to future users: You can also do this on the mysql-commandline before importing.
    – nhck
    Mar 25 at 12:34
1

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.

0

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

1
  • 1
    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.
    – dbdemon
    May 15 '20 at 20:47
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.