MariaDB Master-Slave replication checklist

This is MariaDB (MySQL) checklist for setting up Master-Slave replication.

Master

  • modify configuration (my.cnf)
    [mariadb]
    log-bin
    server_id=1
    log-basename=master1
    
  • create/grant replication user
    CREATE USER 'replication_user'@'HOST' IDENTIFIED BY 'PASSWORD';
    GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'HOST';
    
  • lock databases and find start position (do not exit mysql client until next step is completed !)
    FLUSH TABLES WITH READ LOCK;
    SHOW MASTER STATUS;
    +--------------------+----------+--------------+------------------+
    | File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +--------------------+----------+--------------+------------------+
    | mariadb-bin.000096 |      568 |              |                  |
    +--------------------+----------+--------------+------------------+
    
  • copy data to slave using mysqldump

Slave

  • modify configuration (my.cnf)
    [mariadb]
    server_id=100
    ## restrict only to this database (specify multiple times for multiple dbs)
    #replicate-do-db=db1
    ## ignore this database
    #replicate_ignore_db=db2
    ## skip CREATE USER errors
    slave_skip_errors=1396
    
  • setup master using position data from master status
    CHANGE MASTER TO
      MASTER_HOST='MASTER-HOST',
      MASTER_USER='replication_user',
      MASTER_PASSWORD='PASSWORD',
      MASTER_PORT=3306,
      MASTER_LOG_FILE='mariadb-bin.000096',
      MASTER_LOG_POS=568,
      MASTER_CONNECT_RETRY=10;
    CHANGE MASTER TO MASTER_USE_GTID = current_pos;
    
  • start slave
    START SLAVE;
    SHOW SLAVE STATUS; --- to check replication status