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
Code language: JavaScript (javascript)
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;
Code language: JavaScript (javascript)
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
Code language: JavaScript (javascript)
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';
Code language: JavaScript (javascript)
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;
Code language: PHP (php)