Linux technical support - [email protected]


mariadb 5.5 replication master slave all databases on CentOS 7.1

Before master slave replication you should install and start mariadb on eath host

[root@test001 ~]# yum -y install mariadb-server 
[root@test001 ~]# systemctl start mariadb
[root@test001 ~]# systemctl enable mariadb

[root@test002 ~]# yum -y install mariadb-server 
[root@test002 ~]# systemctl start mariadb
[root@test002 ~]# systemctl enable mariadb

On master replication host, create replication user

mysql -u root
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

On master replication host, enable bin log

[root@test001 ~]# cat /etc/my.cnf | grep -v "^$\|^#"

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
server-id = 1
expire_logs_days = 14
log_bin = /var/log/mariadb/mysql-bin.log
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = test
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d

Restart master replication host

service mariadb restart

Check filename and position on master replication host

mysql -u root 
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+--------------------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+--------------------------+------------------+
| mysql-bin.000010 |       245 |              | mysql            |
+------------------+--------------------------+------------------+
1 row in set (0.00 sec)

copy db if exist from master to slave with mysqldump
DO NOT QUIT MYSQL CONSOLE, IT WILL DESTROY LOCK

UNLOCK TABLES;

On slave replication host

[root@test002 ~]# cat /etc/my.cnf | grep -v "^$\|^#"
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
server-id = 2
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d

load mysqldump if exist

service mariadb restart
mysql -u root 
mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST='10.88.1.2', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=245;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>