Ubuntu 16.04 MySQL replication

[sonik]

10.20.6.29 - Master Database
10.20.6.30 - Slave Database

Code: Select all

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

chnage the bind address to your server real ip address 10.20.6.29

Code: Select all

bind-address            = 127.0.0.1

uncomment

Code: Select all

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log

at the bottom of the file add your databases

Code: Select all

binlog_do_db            = newdatabase
binlog_do_db            = newdatabase2

Code: Select all

sudo service mysql restart

now log in with root on the mysql server via terminal

Code: Select all

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

Code: Select all

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

Code: Select all

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: Select all

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: Select all

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:

Code: Select all

SLAVE STOP;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
SLAVE START;