понедельник, 23 сентября 2019 г.

MariaDB create master slave replication

First of all, you should check mariadb server config file(usually it /etc/my.cnf.d/server.cnf) on master and slave server. There are must present on the master:
[mariadb]
binlog_format=MIXED
log-bin
server_id=1
and on the slave:
[mariadb]
binlog_format=MIXED
log-bin
server_id=2
Also, you should create user for replication and grant privileges to him:
create user 'replication_user'@'$SLAVE_IP' identified by '$PASSWORD';
grant replication slave on *.* to 'replication_user'@'$SLAVE_IP';
flush privileges;

Then you should login to the master database and lock tables to read status, get master log file and position and create databases dump:
FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.08 sec)
SHOW MASTER STATUS;
+-------------------+-----------+--------------+------------------+
| File              | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+-----------+--------------+------------------+
| master-bin.003437 | 852552729 |              |                  |
+-------------------+-----------+--------------+------------------+
in another screen create database dump:
mysqldump -u root -p -A | gzip > all_db_dump.sql.gz 

or you can use without lock tables:
mysqldump -u root -p -v --insert-ignore --skip-lock-tables --single-transaction=TRUE -A | gzip >  all_db_dump,sql.gz

after dump created, unlock tables:
UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

On the slave server drop and create empty users' databases and import data :
gunzip < all_db_dump.sql.gz | mysql -u root -p 
Then stop slave:
STOP SLAVE;
and create replication on the slave server :
CHANGE MASTER TO 
MASTER_HOST='$MASTER_IP', 
MASTER_USER='replication_user', 
MASTER_PASSWORD='$PASSWORD', 
MASTER_PORT=3306, 
MASTER_LOG_FILE='master-bin.003437', 
MASTER_LOG_POS=852552729, 
MASTER_CONNECT_RETRY=10;

and start slave:
START SLAVE;

After that you can check slave status:
SHOW SLAVE STATUS\G