2

I have a MySQL server (Serv-B) that acts as a Slave from a Master server (Serv-A). This has been configured and is working perfectly fine (the replication has been checked to work).

Now, I'd like this "slave" server to also become the master to another slave server (Serv-C). For that, I do the same commands as for setting up the initial configuration :

I connect to the MySQL server Serv-B, and call the following commands :

FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;

On another terminal, I run the mysqldump command :

mysqldump -u root mydb > /home/cx42/mydb.sql

But it hangs. It hangs until I either close the MySQL terminal, or stop the "Flush" command with UNLOCK TABLES;

As soon as I unlock the tables, the mysqldump commands finish in a few seconds.

It seems that the FLUSH command is locking my tables in a way that mysqldump can't access them, but I don't know what is causing this.

The Serv-B server (the one I'm calling "FLUSH ..") has the following configuration :

server-id              = 10
log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days       = 7
max_binlog_size        = 100M
binlog_do_db           = mydb
log_slave_updates      = 1
relay-log              = /var/log/mysql/mysql-relay-bin.log

What is wrong ?

Cyril N.
  • 38,875
  • 36
  • 142
  • 243

1 Answers1

1

Yes, it's correct that FLUSH TABLES WITH READ LOCK acquires locks. It should be apparent from the syntax, and also if you were to read the documentation:

  • FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK

    Flushes and acquires read locks for the named tables.

You could release the lock with UNLOCK TABLES, but that means you might not get the right reading from SHOW MASTER STATUS. As soon as you unlock the tables, more updates could be executed, advancing the log position.

The better option is to let mysqldump do the work. If you invoke mysqldump with the --source-data option (or --master-data before version 8.0.26), the output of the dump will include the binary log position, so you don't have to read it in another window.

See https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_source-data

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    I didn't knew about the `--source-data` !! That's PERFECT! – Cyril N. May 11 '22 at 16:34
  • 1
    I would also recommend using the `--single-transaction` option, if your tables are all InnoDB ([which they should be](https://stackoverflow.com/questions/20148/myisam-versus-innodb/17706717#17706717)). – Bill Karwin May 11 '22 at 18:24