MySQL Master Slave replication setup

MySQL replication setup

MASTER - SLAVE

First we need to setup the MASTER

###############################################################

###############################################################

###############################################################

Edit the [mysqld] section of the my.cnf file, in this case, this was /etc/my.cnf.d/server.cnf

[mysqld]

tmpdir=/tmp/ramdisk

server-id=0

log_bin=mysql-bin

log_error=mysql-bin.err

binlog_do_db=test

feedback = on

Fig.1

tmpdir was used previously (not as part of this procedure) to add a ramdisk for temp tables, more info can be found here

The server-id must be different to what we use on the SLAVE

log_bin and log_error can be seen in Fig.2 below. Note in this instance, I did not use the full path to these files.

The last entry, binlog_do_db includes the database we are interested in replicating, in this case, just test.

Below we can see the location of the log_bin and log_error files;

root@orion:/etc/my.cnf.d# dir /var/lib/mysql

TM351   ib_logfile1     mir_clone       mysql-bin.000002    printers test

aria_log.00000001  ib_logfile1_BAC  multi-master.info  mysql-bin.err   printersBAC

aria_log_control   ibdata1     myrecipes       mysql-bin.index   printersBAC~

ib_logfile0   ibdata1_BAC     mysql       orion.err   spares

ib_logfile0_BAC    mir     mysql-bin.000001   performance_schema  spares.sql

Fig.2

At this point, we can restart mysql with either;

/etc/rc.d/rc.mysql stop/start or service mysql stop/start

The former for my Slackware server and the latter, the Debian development box.

For the next stage, I used phpmyadmin > add master where we carry out the steps above and then return to the phpmyadmin page and hit GO but believe we could do this manually as below within mysql;

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

Where slave_user is the a username on the MASTER that exists and will be granted REPLICATION privileges.

We can then FLUSH PRIVILEGES;

and SHOW MASTER STATUS;

Fig.3

Note the values, we will need them later...

We now need to backup the database so that we can import it into the SLAVE later, we can do this as follows from within mysql;

USE test;

FLUSH TABLES WITH READ LOCK;

QUIT;

mysqldump -u root -p --opt test > test.sql

Log back in to mysql and do;

USE test;

UNLOCK TABLES;

QUIT;

Now we need to setup the SLAVE

###############################################################

###############################################################

###############################################################

We need to login to the other server and also mysql.

CREATE DATABASE test;

QUIT;

We then need to import the database that we just exported, this can be done by;

mysql -u root -p test < /path/to/test.sql

We now need to edit the configuration files, similarly as we done for the MASTER 

In this case, I modified the [mysqld] section in /etc/mysql/mariadb.conf.d/50-server.cnf

The server-id should be different from the MASTER

The relay-log, log-bin and binlog_do_db should be changed as above.

NOTE: unsure if the full paths were required here or not, recalling that I didn't use them when setting up the MASTER.

We should now restart the server (command for this already noted above)

Log back into mysql and enter;

CHANGE MASTER TO MASTER_HOST='192.168.254.198',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=  690;

Where MASTER_HOST is the IP address of the MASTER

Where slave_user is a username on the MASTER that has REPLICATION privileges.

Where MASTER_PASSWORD is the password for the slave_user on the MASTER 

MASTER_LOG_FILE and MASTER_LOG_POS are values from above, when we ran SHOW MASTER STATUS (Fig.3)

We now want to START SLAVE;

We can see the SLAVE STATUS by SHOW SLAVE STATUS\G;

If there is an error, we can apparently try the below;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START; 

We can see both the MASTER and SLAVE STATUS from phpmyadmin under REPLICATION as below;

On the above, note the File, Position, Binlog_Do_DB and Server ID and also how they are common to the below;

I believe the Position on the MASTER should match the Exec_Master_Log_Pos on the SALVE

Finally, to test, we can make a change to a table, or value on the MASTER and see it replicated across to the SLAVE

If the database structure is in place on the SLAVE, then it seems when connected, the MASTER will be replicated ok.

TBC...

---

MASTER - MASTER

See here