Score:0

Server

Replicate mysql database when target server is MariaDB 10.5 (where mysql.user is a view)

elyograg

4/17/24, 4:54 PM

I have a sync script that does a mysqldump on the mysql database on a source server, then imports that dump on the target server, followed by privelege flush. Systems are CentOS 7.

In MariaDB 10.5, mysql.user is no longer a real table, it's a view. The source system is running MariaDB 10.2, where mysql.user is a real table.

When the sync runs, it breaks mysql. To fix it I have to kill mariadb, start it with --skip-grant-tables, run mysql_upgrade --force, kill it again, and restart the service.

It's the user table I care about replicating, but we found it easier to just do the whole mysql database.

How can I manage to replicate the mysql database when mysql.user on the target server is not a real table?

0 + 12

mysql

centos

mariadb

elyograg

4/17/24, 4:59 PM

Hmm. Maybe it's not fully fixed. The mysql_upgrade works while --skip-grant-tables is active, but running it after restarting the service shows a permission denied error. I AM providing a password when I try it again.

Rick James

4/17/24, 9:45 PM

"When the sync runs" -- Which 'sync'?

elyograg

4/18/24, 10:14 PM

The sync script that we wrote. It does a lot more than just dump and import databases. There is no mysql replication, this is a script that replicates the data for our own software, the related databases (with their permissions via the mysql database), and LDAP. The idea is that the target server is there for disaster recovery in the event the primary server suffers catastrophoc failure.

elyograg

4/18/24, 10:17 PM

The source server we call "prod a". We have a "prod b" in the same rack that syncs from prod a using this script. And we have a "dr prod" in a completely different state which syncs from prod b using the same script with a different config.

Rick James

4/19/24, 12:11 AM

Replication? DRBD? More details, please. "A script for syncing" sounds "wrong".

elyograg

4/19/24, 7:46 PM

The sync script copies the LDAP database from the source to the target. It dumps the mysql database on the source MariaDB, copies it to the target, imports it, and then does the same for a configured list of other databases that is usually just one DB. It shuts down the custom software on the destination, quiesces the custom software on the source, creates an LVM snapshot on the source, and uses rsync to synchronize the custom data, which for these servers is over 20TB, but the day to day deltas are a lot less than that, and rsync is good at minimizing data transfer.

elyograg

4/19/24, 7:47 PM

The import of the mysql database breaks MariaDB 10.5, because mysql.user is no longer a real table. So I need to know how to sync DB permissions when the target server is 10.5.

Rick James

4/20/24, 5:26 AM

Avoid dumping and importing any tables in `mysql` and `information_schema`.

elyograg

4/20/24, 8:00 PM

OK, so if I can't do it that way, how do you suggest dealing with a situation where somebody comes to me and says "copy X Y and Z databases, as well as the users that access those databases and their permissions, to this other server here." I want to do it with a script, not manually in the mysql client.

elyograg

4/20/24, 8:03 PM

Up until 10.5, we did this by importing mysql, X, Y, and Z into the target server. Now I can't do that for mysql, so if a password gets changed on the source server, it will not be reflected on the target.

elyograg

4/20/24, 8:16 PM

I got a deeper look. The user view in mysql references other tables in mysql, the first one being global_priv. Looks like if the source server is also 10.5, the current method might work, because it will construct the mysql database "correctly" -- tables like global_priv will exist and the dump will create user as a view, not a table. So I just need to find a way around the corner case where the source is running an earlier version of mariadb. The sync script currently does not stop any services on the source server and I'd like to keep it that way.

danblack

5/1/24, 10:55 PM

`mariadb-dump` has a [--system=users](https://mariadb.com/kb/en/mariadb-dumpmysqldump/) which may assist with this. There's still a couple of bugs with this but there's probably enough SQL syntax to be portable.

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: Replicate mysql database when target server is MariaDB 10.5 (where mysql.user is a view)

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.