MARIADB MASTER SLAVE
Install on both master and slave
yum install mariadb-server mariadb -y
systemctl enable mariadb
systemctl start mariadb.service
mysql_secure_installation
Master
Add below lines on the mysql
vi /etc/my.cnf
[server]
# add follows in [server] section : get binary logs
log-bin=mysql-bin
# define uniq server ID
server-id=101
Restart the mariadb service
systemctl restart mariadb.service
mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.8-MariaDB-log MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
# create user (set any password for ‘password’ section)
MariaDB [(none)]> grant replication slave on *.* to replication@’%’ identified by ‘P@assword’;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit
Bye
Slave
Add below lines on the mysql on the slave server
vi /etc/my.cnf
slave node
[server]
# add follows in [server] section : get binary logs
log-bin=mysql-bin
# define server ID (different one from Master Host)
server-id=102
# read only
read_only=1
# define own hostname
report-host=slaveserver
Restart the mariadb service
systemctl restart mariadb.service
Get Dump-Data on Master Host server
mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.2.8-MariaDB-log MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
# lock all tables
MMariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 541 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
mysqldump -u root -p –all-databases –lock-all-tables –events > mysql_dump.sql
unlock the tables on the master server
Enter password:
# back to the remained window and unlock
MariaDB [(none)]> unlock tables;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit
Bye
scp mysql_dump.sql dev01@ec2-13-127-218-218.ap-south-1.compute.amazonaws.com:/tmp/
Go to the Slave Host.
import dump from Master Host
[root@ip-172-31-25-39 ~]# mysql -u root -p < /tmp/mysql_dump.sql
Enter password:
Configure replication settings on Slave Host. It’s OK all, make sure the settings work normally to create databases on Master Host.
# import dump from Master Host
mysql -u root -p
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;
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
# show status
MariaDB [(none)]> show slave status\G
asterA has the following error in show slave status:
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’
Solution:
Slave: stop slave;
Master: flush logs
Master: show master status; — take note of the master log file and master log position
Slave: CHANGE MASTER TO MASTER_LOG_FILE=’log-bin.00000X?, MASTER_LOG_POS=106;
Slave: start slave;
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.01 sec)
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;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G ;
show binary logs ;
>for f in $(cat mysqld-bin.index); do test -f $f || echo "Not found $f" ; done;
Recent Comments