{"id":7284,"date":"2018-03-22T00:33:22","date_gmt":"2018-03-21T16:33:22","guid":{"rendered":"http:\/\/rmohan.com\/?p=7284"},"modified":"2018-03-22T00:33:58","modified_gmt":"2018-03-21T16:33:58","slug":"mariadb-master-slave","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=7284","title":{"rendered":"MARIADB MASTER SLAVE"},"content":{"rendered":"

MARIADB MASTER SLAVE<\/strong><\/p>\n

Install on both master and slave<\/p>\n

yum install mariadb-server mariadb -y<\/p>\n

systemctl enable mariadb<\/p>\n

systemctl start mariadb.service<\/p>\n

mysql_secure_installation<\/p>\n

Master<\/p>\n

Add below lines on the mysql<\/p>\n

vi \/etc\/my.cnf<\/p>\n

[server]
\n# add follows in [server] section : get binary logs
\nlog-bin=mysql-bin
\n# define uniq server ID
\nserver-id=101<\/p>\n

Restart the mariadb service<\/p>\n

systemctl restart mariadb.service<\/p>\n

mysql -u root -p<\/p>\n

Enter password:<\/p>\n

Welcome to the MariaDB monitor. Commands end with ; or \\g.
\nYour MariaDB connection id is 9
\nServer version: 10.2.8-MariaDB-log MariaDB Server<\/p>\n

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.<\/p>\n

Type ‘help;’ or ‘\\h’ for help. Type ‘\\c’ to clear the current input statement.<\/p>\n

# create user (set any password for ‘password’ section)<\/p>\n

MariaDB [(none)]> grant replication slave on *.* to replication@’%’ identified by ‘P@assword’;<\/p>\n

Query OK, 0 rows affected (0.00 sec)
\nMariaDB [(none)]> flush privileges;<\/p>\n

Query OK, 0 rows affected (0.00 sec)
\nMariaDB [(none)]> exit<\/p>\n

Bye<\/p>\n

Slave<\/p>\n

Add below lines on the mysql on the slave server<\/p>\n

vi \/etc\/my.cnf<\/p>\n

slave node
\n[server]
\n# add follows in [server] section : get binary logs<\/p>\n

log-bin=mysql-bin
\n# define server ID (different one from Master Host)<\/p>\n

server-id=102
\n# read only<\/p>\n

read_only=1
\n# define own hostname
\nreport-host=slaveserver<\/p>\n

Restart the mariadb service
\nsystemctl restart mariadb.service<\/p>\n

Get Dump-Data on Master Host server<\/p>\n

mysql -u root -p<\/p>\n

Enter password:<\/p>\n

Welcome to the MariaDB monitor. Commands end with ; or \\g.
\nYour MariaDB connection id is 10
\nServer version: 10.2.8-MariaDB-log MariaDB Server<\/p>\n

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.<\/p>\n

Type ‘help;’ or ‘\\h’ for help. Type ‘\\c’ to clear the current input statement.<\/p>\n

# lock all tables<\/p>\n

MMariaDB [(none)]> flush tables with read lock;
\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n

MariaDB [(none)]> show master status;
\n+——————+———-+————–+——————+
\n| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
\n+——————+———-+————–+——————+
\n| mysql-bin.000001 | 541 | | |
\n+——————+———-+————–+——————+
\n1 row in set (0.00 sec)<\/p>\n

mysqldump -u root -p –all-databases –lock-all-tables –events > mysql_dump.sql<\/p>\n

unlock the tables on the master server<\/p>\n

Enter password:
\n# back to the remained window and unlock<\/p>\n

MariaDB [(none)]> unlock tables;<\/p>\n

Query OK, 0 rows affected (0.00 sec)
\nMariaDB [(none)]> exit<\/p>\n

Bye<\/p>\n

scp mysql_dump.sql dev01@ec2-13-127-218-218.ap-south-1.compute.amazonaws.com:\/tmp\/<\/p>\n

Go to the Slave Host.<\/p>\n

import dump from Master Host<\/p>\n

[root@ip-172-31-25-39 ~]# mysql -u root -p < \/tmp\/mysql_dump.sql
\nEnter password:<\/p>\n

Configure replication settings on Slave Host. It’s OK all, make sure the settings work normally to create databases on Master Host.
\n# import dump from Master Host<\/p>\n

mysql -u root -p<\/p>\n

CHANGE MASTER TO MASTER_HOST=’13.127.203.148′, MASTER_USER=’replication’, MASTER_PASSWORD=’P@assword’, MASTER_LOG_FILE=’| mysql-bin.000001′, MASTER_LOG_POS=541;<\/p>\n

MariaDB [(none)]> start slave;<\/p>\n

Query OK, 0 rows affected (0.00 sec)
\n# show status<\/p>\n

MariaDB [(none)]> show slave status\\G<\/p>\n

asterA has the following error in show slave status:<\/p>\n

Last_IO_Errno: 1236
\nLast_IO_Error: Got fatal error 1236 from master when reading data from binary log: \u2018Could not find first log file name in binary log index file\u2019<\/p>\n

Solution:<\/p>\n

Slave: stop slave;<\/p>\n

Master: flush logs<\/p>\n

Master: show master status; \u2014 take note of the master log file and master log position<\/p>\n

Slave: CHANGE MASTER TO MASTER_LOG_FILE=\u2019log-bin.00000X?, MASTER_LOG_POS=106;<\/p>\n

Slave: start slave;<\/p>\n

MariaDB [(none)]> stop slave;
\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n

MariaDB [(none)]> reset slave;
\nQuery OK, 0 rows affected (0.01 sec)<\/p>\n

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST=’13.127.203.148′, MASTER_USER=’replication’, MASTER_PASSWORD=’P@assword’, MASTER_LOG_FILE=’| mysql-bin.000005′, MASTER_LOG_POS=245;
\nQuery OK, 0 rows affected (0.01 sec)<\/p>\n

MariaDB [(none)]> start slave;
\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n

MariaDB [(none)]> show slave status\\G ;<\/p>\n

 <\/p>\n

show binary logs ;<\/pre>\n
>for f in $(cat mysqld-bin.index); do test -f $f || echo \"Not found $f\" ; done;<\/pre>\n","protected":false},"excerpt":{"rendered":"
MARIADB MASTER SLAVE<\/p>\n
Install on both master and slave<\/p>\n
yum install mariadb-server mariadb -y<\/p>\n
systemctl enable mariadb<\/p>\n
systemctl start mariadb.service<\/p>\n
mysql_secure_installation<\/p>\n
Master<\/p>\n
Add below lines on the mysql<\/p>\n
vi \/etc\/my.cnf<\/p>\n
[server] # add follows in [server] section : get binary logs log-bin=mysql-bin # define uniq server ID server-id=101<\/p>\n
Restart the mariadb service<\/p>\n
systemctl restart mariadb.service<\/p>\n
 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[72],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7284"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7284"}],"version-history":[{"count":3,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7284\/revisions"}],"predecessor-version":[{"id":7287,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7284\/revisions\/7287"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7284"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7284"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7284"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}