Setting up MySQL master to slave replication

First you need to configure MySQL to run on a reachable socket both on the master and slave, something else then localhost. Assign each server with an unique server id. Make a snapshot of the current database. Import that into the slave system. Create a user on the master that is allow to replicate. Set up the master configuration on the slave.

Set up MySQL to listen on a reachable socket and assign an unique id to master/slave.

My master system is running ubuntu 18.04 lts and is running MySQL v5.7. Configuration files are under.

/etc/mysql/

Set server-id to something unique for each server. For example set master server-id to 1 and set server-id for slave to 2. Configuration for master.

[mysqld]
# listen on any interface
bind-address=0.0.0.0
# set server-id master
server-id=1

My slave runs FreeBSD v12.1 with MySQL v80 installed. Configuration files are under.

/usr/local/etc/mysql

Configuration for the slave server.

note: Make sure skip-networking is commented out, from both master and slave.

[mysqld]
# listen on any interface
bind-address=0.0.0.0
# set server-id master
server-id=2

Restart both servers

Dump the to be replicated database from the master server and create replication user.

To connect to the mysql server.

mysql -u root -p

Run following commands on the MySQL shell

CREATE USER replica IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'hostname/ip' IDENTIFIED BY 'password';
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
QUIT;

Show master status will show a table with File and Postion. Both values are required to make this work. Make sure to copy them somewhere. Once logged out run.

 mysqldump -u root -p --master-data --datbases exampledb > exampledb.sql

After that's done log back into MySQL

mysql -u root -p

Run following commands on the MySQL shell

UNLOCK TABLES;
QUIT;

Setting up slave to connect to the master

Connect on the slave server to MySQL

mysql -u root -p

STOP SLAVE;
CREATE DATABASE exampledb;
QUIT;

Import the master data

mysql -u root -p exampledb <exampledb.sql

Connect on the slave server to MySQL

mysql -u root -p

In order to sync the slave database to the master database we need to create it first and finally change the master configuration

STOP SLAVE;

CHANGE MASTER TO 
MASTER_HOST='master ip', 
MASTER_USER='replica', 
MASTER_PASSWORD='password', 
MASTER_LOG_FILE='mysql-bin.001', 
MASTER_LOG_POS=100; 

START SLAVE;
QUIT;

The master log file you got from running show master status previously. These values need to be set to what they where when you ran that command.

Note: !! First stop slave then create configure master then start slave !!