Neuron Powered Motorization

    mysql-replication-guard

    1.0.0 • Public • Published

    MySQL replication guard

    This tool can be used to verify the integrity of a MySQL replication. There is a command to set up the replication. After the replication is set up, it can be verified that the content of the tables are equivalent on both sides.

    Installation

    npm install -g mysql-replication-guard

    or with yarn:

    yarn global add mysql-replication-guard

    Config file

    A config file must be created. It can look like that for instance:

    {
      "master": {
        "host": "master-host",
        "user": "replication_guard",
        "password": "<some password>"
      },
      "slave": {
        "host": "slave-host",
        "user": "replication_guard",
        "password": "<some password>"
      },
      "slaveMasterConnection": {
        "host": "master-host",
        "user": "slave_user",
        "password": "<some password>"
      },
      "databaseName": "<db name>",
      "tables": "all",
      "replicationChannel": "production",
      "mysqlTool": "/usr/bin/mysql",
      "dumpTool": "/usr/bin/mysqldump",
      "mail": {
        "smtp": {
          "host": "smtp.example.com",
          "auth": {
            "user": "system@example.com",
            "pass": "<some password>"
          },
          "port": 587
        },
        "to": {
          "address": "admin@example.com",
          "name": "Admin"
        },
        "from": {
          "address": "system@example.com",
          "name": "MySQL replication guard"
        },
        "subject": "MySQL replication guard event"
      }
    }

    Details about the schema of the config file can be found in dist/config/schema.json.

    Mail

    A mail property can be provided optionally. When provided, an E-Mail will be send with the given configuration when an error occurs, or a data inconsistency was detected (even if it could be fixed).

    The property can be omitted, in that case no E-Mails will be sent at all.

    MySQL Permission

    The script needs the following permissions to the database:

    Master

    CREATE USER replication_guard@localhost IDENTIFIED BY '<some password>';
    GRANT SELECT, LOCK TABLES ON database.* TO replication_guard@localhost;
    GRANT SUPER, RELOAD ON *.* TO replication_guard@localhost;

    Slave

    CREATE USER replication_guard@localhost IDENTIFIED BY '<some password>';
    GRANT RELOAD, SUPER ON *.* TO replication_guard@localhost;
    GRANT SELECT, INSERT, DROP, CREATE, ALTER, REFERENCES, LOCK TABLES ON database.* TO replication_guard@localhost;

    Usage

    There are 3 command which can be used:

    Setup

    mysql-replication-guard --config config.json setup

    This command will set up the replication. To do so, the following commands will be executed:

    /* MASTER */ RESET MASTER;
    /* SLAVE */  STOP SLAVE FOR CHANNEL ?;
    /* SLAVE */  RESET SLAVE FOR CHANNEL ?;
    
    # All tables will be copied from master to slave (might override existing data in the slave)
    
    /* SLAVE */  CHANGE MASTER TO /* data from slaveMasterConnection */ FOR CHANNEL ?; 
    /* SLAVE */  START SLAVE FOR CHANNEL ?;

    When the database is copied from the master to slave, all tables are copied, not only the tables which are configured.

    Verify

    mysql-replication-guard --config config.json verify

    This command will create checksums over all configured tables in both databases. If they are different, a dump will be created on the master for the broken tables and transferred to the slave. This should normally fix the issue. Nevertheless, the script verifies all tables again after fixing, and reports errors when there are still tables with different checksums.

    It is intended that this command is executed every once in a while. This could be done using a crontab. To install a crontab which verified the database every day at 3am you could use this configuration:

    0 3 * * * /usr/bin/mysql-replication-guard -c /root/mysql-replication-guard/config.json verify &> /root/mysql-replication-guard/guard.log
    

    Notify

    mysql-replication-guard --config config.json notify "Something happened"

    This command will simply send an E-Mail with a custom text. It is just for other services to notify about something happened. This command will only work, if a mail configuration is given. Otherwise, it will print an error and abort with a non-zero exit code.

    License

    Just use it, I do not care. (MIT)

    Install

    npm i mysql-replication-guard

    DownloadsWeekly Downloads

    0

    Version

    1.0.0

    License

    MIT

    Unpacked Size

    99.2 kB

    Total Files

    23

    Last publish

    Collaborators

    • mxmlashlah