How to create MySQL replication server

👤 admin

🔴 Admin

✍️ The most important thing in the world is to not be alone.

⏳ Last active: 15 Apr 2025 at 16:01

📅 Created: 14 Feb 2021 at 20:07

👀 Viewed: 38 times

✉️ Send Email

10.20.6.29 - Master Database
10.20.6.30 - Slave Database

pico /etc/mysql/mysql.conf.d/mysqld.cnf

chnage the bind address to your server real ip address 10.20.6.29

bind-address            = 127.0.0.1

uncomment

server-id               = 1

log_bin                 = /var/log/mysql/mysql-bin.log

at the bottom of the file add your databases

binlog_do_db            = newdatabase

binlog_do_db            = newdatabase2

sudo service mysql restart

now log in with root on the mysql server via terminal

mysql -u root -p

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

FLUSH PRIVILEGES;

copy the databases to the slave server with phpmyadmin
in 10.20.6.30 a.k. the slave

pico /etc/mysql/mysql.conf.d/mysqld.cnf

server-id               = 2

relay-log               = /var/log/mysql/mysql-relay-bin.log

log_bin                 = /var/log/mysql/mysql-bin.log

binlog_do_db            = newdatabase

binlog_do_db            = newdatabase2

sudo service mysql restart

CHANGE MASTER TO MASTER_HOST='10.20.6.29',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001';

START SLAVE;

SHOW SLAVE STATUS\G

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

SLAVE STOP;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

SLAVE START;