wiki

This page explain howto rebuild a replication from mydumper / myloader

apt-get install mydumper

You need at least version : 0.6.1-1

host : dumper

mkdir /dumppath/sql_to_dump

Create a mydumper user if not exist

CREATE USER 'mydumper'@'dumper_host' IDENTIFIED BY '*****';
GRANT ALL PRIVILEGES ON *.* TO 'mydumper'@'dumper_host';

try to connect to the server to dump with classical mysql client

mysql -umydumper -p******* -hserver_to_dump

then open a screen

screen -S dump_date

mydumper --user=mydumper --password={$password} -regex '^(?!(mysql|test))' --outputdir=/dumppath/{$output_dir}/ --host={$host_to_backup} --port=3306 --threads=15 --compress --kill-long-queries --less-locking --verbose=3 --triggers --routines

At this stage you'll get a dump without the mysql database, so you need to recreate the user on the futur slave

rsync locally the dump (get the dump locally on the host you need to restore)

exemple :

rsync -avz /dump/sql02/ root@100.121.141.161:/mysqlbinlog/sql02/

then in mysql be sure you have all you need

stop slave;
reset slave;

do a dump on the local user, you need to have the replication user, the root user, the sames users as the master !

#!/bin/bash
mysqldump \
--triggers --routines --hex-blob --add-locks --add-drop-table \
--databases mysql user \
> /home/noc/user.sql

Import the mydumper dump (without the mysql database) within the datadir of the running futur slave mysql :

myloader -o -h {$host} -t {$threads} -u {$user} -p {$secret} -v 3 -d {$dump_path}

Exemple to reimport localy the dump :

myloader -o -h localhost -t 8 -u root -p ***** -v 3 -d /mysqlbinlog/sql02

then re-import the dump of the users :

mysql mysql < user.sql

double check :

mysql
use mysql;
select * from user;
flush privileges;

cd to the dump folder from whom the dump was loaded .

Check the metadata file for the position of the replication at the dump moment .

cat metadata

You'll get something like that :

Started dump at: 2018-06-14 10:03:42
SHOW MASTER STATUS:
        Log: mysql-bin.000161
        Pos: 274618698
        GTID:0-1-59577040

SHOW SLAVE STATUS:
        Connection name: 
        Host: prod-sql-01
        Log: mysql-bin.000048
        Pos: 213917058
        GTID:0-1-59577040

Finished dump at: 2018-06-14 10:18:59

connect on to the slave Server

mysql

stop slave;

reset slave;

CHANGE MASTER TO   MASTER_HOST='prod-sql-02',   MASTER_USER='replication', MASTER_PASSWORD='Password',   MASTER_PORT=3306,   MASTER_LOG_FILE='${logbin file found in metadata}',   MASTER_LOG_POS=${position found in metadata},   MASTER_CONNECT_RETRY=10;

exemple from the metadata exemple gave just before :

CHANGE MASTER TO   MASTER_HOST='prod-sql-02',   MASTER_USER='replication', MASTER_PASSWORD='Password',   MASTER_PORT=3306,   MASTER_LOG_FILE='mysql-bin.000161',   MASTER_LOG_POS=274618698,   MASTER_CONNECT_RETRY=10;

start slave;

 show slave status\G 

If no error and if everything fine :

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: prod-sql-02
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000161
          Read_Master_Log_Pos: 446435154
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 94494399
        Relay_Master_Log_File: mysql-bin.000161
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 446435154
              Relay_Log_Space: 94494705
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)

MariaDB [(none)]> quit