MySQL - Master slave replication

 MySQL Master slave replication
------------------------------



DB Clienet
- Master (Read+Write)
- Slave (Read)
- slave (Read)

for more performance, you can add more servers

192.168.56.5    master.expanor.local - master  
192.168.56.6    worker1.expanor.local - slave

Install DB server on master

# apt-get install -y master-server

prompt you for root password, just leave blank or enter your own.

2. Edit the config file
# vi /etc/mysql/my.cnf
search bind-address

and modify the bind address as

bind-address = 192.168.56.5 # IP address of the server


search for server-id and uncomment it
server-id = 1

also uncomment log_bin to enable binary login
log_bin  =  /var/log/mysql/mysql-bin.log

save and exit

# restart the sql service
# service mysql restart

Now, login. since we don't have pw, just press enter, you should be in
# mysql -uroot

create a replication,
mysql> create user 'repl'@'%' identified by 'mypassword';
mysql> grant replication slave on *.* to 'repl'@'%';

Create test database

mysql> create database students;
> create table students.names(name  varchar(20));
> insert into students.names values ('John');
> select * from student.names;
> exit

Now, create a dump of daabase (snapshot) --master-data option will
call mysql to write up binary log.
# mysqldump -uroot --all-databases --master-data > masterdump.sql
# grep change *sql | head -1

You can see, binary log file is recorded.

Now, copy this dump to another server.

# scp masterdump.sql 192.168.56.6    

Now, on your worker node1
Install mysql
# apt-get install mysql-server -y # leave pw blank

Edit the config file
# vi /etc/mysql/my.cnf

Now, set the bind address to server ip address
bind-address = 192.168.56.5

server-id = 2 # increment by 1 so value is 2, this need to be unique and positive integer.

save and exit

restart mysql
# service mysql restart

Login
# mysql -uroot
Now, let this slave information about master

> CHANGE MASTER  TO
  -> MASTER_HOST='192.168.56.5',
  -> MASTER_USER='repl',
  -> MASTER_PASSWORD='mypassword';
> exit


Now, restore data from master server
# mysql -uroot < masterdump.sql

# mysql -uroot

Now start the slave
> start slave;
> show slave status\G;


You will see
slave status - running
master log file
master log position

Now, go back to master server and add a new row
> mysql -uroot
> insert into student.names values('Bill');

querry the entry
> select * from student.names;

you can see this info on master.

Now, go to slave and see

on slave server
> select * student.names;

you see it now.

so replication is working fine.


see if slave host is running
> show slave hosts;

you see the result

Adding another slaves
- Create a snapshot from the master server
- set the server_id on the new slave (increment by 1)
- Tell the slave what user, pw and host to use fro master server
- Import the snapshot
- Start the slave