Mysql replication in centos7

We are going to sync all mysql database in centos7 and exclude some database. We only need mention which
database we do not want to sync, by default all other databases will be on sync.

In master server

Backup your current mysql config file and insert following under [mysqld] section

# cp -p /etc/my.cnf /etc/my.cnf_ORG
# vi /etc/my.cnf
[mysqld]
server_id=1001
relay-log=mysql-relay-bin
relay-log-index=mysql-relay-bin.index
log-bin=mysql-bin

binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

Restart mysql

# systemctl restart mysql

Again, login to mysql

# mysql -u root -p
mysql>​ GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' IDENTIFIED BY ’password’;
mysql> ​FLUSH PRIVILEGES; 
mysql>​ FLUSH TABLES WITH READ LOCK;
mysql>​ SHOW MASTER STATUS;
+------------------+----------+--------------+---------------------------------------------------------------------------------------------+-------------------+
| File
| Position | Binlog_Do_DB | Binlog_Ignore_DB
| Executed_Gtid_Set |
+------------------+----------+--------------+---------------------------------------------------------------------------------------------+-------------------+
| mysql-bin.000002 | 401 |
| information_schema,performance_schema |
|
+------------------+----------+--------------+---------------------------------------------------------------------------------------------+-------------------+
1 row in set (0.00 sec)
mysql>​ exit;

We will need filename and position later so write it down
File​ : ​ mysql-bin.000002
Position​ : ​ 401

Dump all databases

# mysqlsump -uroot -p --all-databases > all-databases.sql
# scp all-databases.sql [email protected]:/

You can unlock tables and quit

In slave server

Similar as in master server, take a backup copy of mysql configuration first and paste following
in [mysqld] section

# cp -p /etc/my.cnf /etc/my.cnf_ORG
# vi /etc/my.cnf
[mysqld]
server-id=1009
relay-log=mysql-relay-bin
log-bin=mysql-bin
read_only=1

Restart mysql

# systemctl restart mysqld

Import all-databases

# mysql -uroot -p < /all-databases.sql

Login to mysql

mysql>​ CHANGE MASTER TO
->​ MASTER_HOST='master-ip',
->​ MASTER_USER=''slaveuser'',
->​ MASTER_PASSWORD='password',
->​ MASTER_LOG_FILE='mysql-bin.000002',
->​ MASTER_LOG_POS=401;
mysql>​ slave start;
mysql>​ show slave status\G;

Note following:
Slave_IO_Running​ : ​ Yes
Slave_SQL_Running​ : ​ Yes

If ​ ‘Slave_IO_State​‘ ​ reports ‘Waiting for master to send event’ then replication is successful.

You can now exit mysql

mysql> ​ exit;

Troubleshooting

If there is an issue in connecting, you can try starting slave with a command to skip over it:

mysql>​ SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql>​ SLAVE START;

Leave a Reply

Your email address will not be published. Required fields are marked *