Does executing mysql dump files trigger replication?

All we need is an easy explanation of the problem, so here it is.

I have two servers A and B that have a source/replica replication set up. I need to bring a new database over from another server, say server C. If I create a dump file of it, and then execute the dump file on server A, will all of those same statements be ran on server B as well?

I would assume that since the dump file is just a bunch of SQL statements, these commands would also be run on my slave, but want to be sure so that I do not break the replication process.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I’m just quoting from the MySQL (8.0) documentation here, so you might want to wait until somebody with a better understanding comes along. That said, here goes…

MySQL Reference Material

There is a section in the MySQL documentation dedicated to 17.5.1 Replication Features and Issues. It contains various sections regarding what is replicated. Two of the interesting sections are:

17.5.1.6 Replication of CREATE … IF NOT EXISTS Statements (MySQL Docs)

The above documentation states:

MySQL applies these rules when various CREATE … IF NOT EXISTS statements are replicated:

  • Every CREATE DATABASE IF NOT EXISTS statement is replicated, whether or not the database already exists on the source.
  • Similarly, every CREATE TABLE IF NOT EXISTS statement without a SELECT is replicated, whether or not the table already exists on the source. This includes CREATE TABLE IF NOT EXISTS … LIKE. Replication of CREATE TABLE IF NOT EXISTS … SELECT follows somewhat different rules; see Section 17.5.1.7, “Replication of CREATE TABLE … SELECT Statements”, for more information.
  • CREATE EVENT IF NOT EXISTS is always replicated, whether or not the event named in the statement already exists on the source.
  • CREATE USER is written to the binary log only if successful. If the statement includes IF NOT EXISTS, it is considered successful, and is logged as long as at least one user named in the statement is created; in such cases, the statement is logged as written; this includes references to existing users that were not created. See CREATE USER Binary Logging, for more information.
  • (MySQL 8.0.29 and later:) CREATE PROCEDURE IF NOT EXISTS, CREATE FUNCTION IF NOT EXISTS, or CREATE TRIGGER IF NOT EXISTS, if successful, is written in its entirety to the binary log (including the IF NOT EXISTS clause), whether or not the statement raised a warning because the object (procedure, function, or trigger) already existed.

17.5.1.7 Replication of CREATE TABLE … SELECT Statements (MySQL Docs)

The above section states that:

MySQL applies these rules when CREATE TABLE … SELECT statements are replicated:

  • CREATE TABLE … SELECT always performs an implicit commit (Section 13.3.3, “Statements That Cause an Implicit Commit”).

    • If the destination table does not exist, logging occurs as follows. It does not matter whether IF NOT EXISTS is present.
    • STATEMENT or MIXED format: The statement is logged as written.
    • ROW format: The statement is logged as a CREATE TABLE statement followed by a series of insert-row events.
      Prior to MySQL 8.0.21, the statement is logged as two transactions. As of MySQL 8.0.21, on storage engines that support atomic DDL, it is logged as one transaction. For more information, see Section 13.1.1, “Atomic Data Definition Statement Support”.
  • If the CREATE TABLE … SELECT statement fails, nothing is logged. This includes the case that the destination table exists and IF NOT EXISTS is not given.

  • If the destination table exists and IF NOT EXISTS is given, MySQL 8.0 ignores the statement completely; nothing is inserted or logged.
    MySQL 8.0 does not allow a CREATE TABLE … SELECT statement to make any changes in tables other than the table that is created by the statement.

Please also note the change in wording regarding replication. MySQL has switched to the non-racist source and replica(s) terms.

Additional Information Based on Comment Regarding MariaDB

The replication of DML and DDL is replicated in a MariaDB too. The following document contains the relevant portion of information.

Replication Overview (MariaDB Documentation)

The main mechanism used in replication is the binary log. If binary logging is enabled, all updates to the database (data manipulation and data definition) are written into the binary log as binlog events. > Replicas read the binary log from each primary in order to access the data to replicate. A relay log is created on the replica, using the same format as the binary log, and this is used to perform the replication. Old relay log files are removed when no longer needed.

Answering Your Question

Does executing mysql dump files trigger replication?

If your dump file contains a/the corresponding statement/s, then it/they will be executed and then replicated from the source(MySQL) / primary (MariaDB) to the replica, assuming you are using a current version of MySQL/MariaDB.

There are some limitations based on the versions of MySQL and MariaDB and their compatibility. So if you are replicating between these systems, then take a look at Replication Compatibility (MariaDB Docs).

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply