MySQL Replication Master/Slave

I'm sorry I haven't finished the Post before now, but  I've been snowed under with work :) 

Let's get the ball rolling, shall we ?

Since 2010 I've worked with MySQL replication and frankly speaking today I'm still sure and convinced that this is a really handy and smart feature.

Let's go over MySQL replication together, shall we ?

It is made up by at least 2 database server, one Master and one Slave and every DML plus DDL SQL command executed on the master node is replicated in asynchronous mode on the Slave.

It is possible using this features to :

1) Use the Slave likes an "online backup" database and use it just in case of failure of the Master node
    
Be careful ! As I said before replication is made from an asynchronous algorithm and in case the Master fails his current data doesn't get to Slave.

2) Use the Master node only for DML SQL command, like for example INSERT and instead execute all the queries on the slave node.

According to MySQL Reference Books it is possible to increase the cluster with lots of slave nodes which end up creating a scale-out solution.

You are looking forward to hints, aren't you ?

Let's get down to MySQL Replication configuration, shall we ?

First of all, as I said before you need at least 2 MySQL database servers, already configured and started.

Would it help if I wrote the link of a previous Post to follow if you need to install 2 MySQL server from scratch ?   It may..... :)

http://alessiodba.blogspot.it/2013/11/mysql-56-e-oracle-linux-64.html

The installation process of the previous blog have to end up adding the following replication configuration parameter to the /etc/my.cnf file.


 mysqlvm01master :

 server-id = 1
 expire_logs_days        = 7
 max_binlog_size         = 512M
 log-slave-updates       = 1
 skip-slave-start

mysqlvm02slave01 :
server-id       = 2
max_binlog_size  = 1024M
expire_logs_days  = 7
log-slave-updates = 1
skip-slave-start


3) Add a new user on the Master and Slave:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'x.x.x.106' IDENTIFIED BY 'xxxxx';

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'x.x.x.105' IDENTIFIED BY 'xxxxx';



This new user is going to be used to establish a connection between the master plus the Slave node.


4) Restart the Master and Slave nodes look for bin-log files:


[mysql@mysqlvm01master]$ ls -l
        drwxr-xr-x. 2 mysql mysql   6 Sep 30 15:32 binlogs
        -rw-rw----. 1 mysql mysql 120 Oct  6 12:00 binlogs.000001
        -rw-rw----. 1 mysql mysql  33 Oct  6 12:00 binlogs.index
         drwxr-xr-x. 2 mysql mysql  78 Oct  3 12:27 innologs



[mysql@mysqlvm02slave]$ ls -l
    drwxr-xr-x. 2 mysql mysql   6 Oct  2 12:29 binlogs
    -rw-rw----. 1 mysql mysql 120 Oct  6 12:06 binlogs.000001
    -rw-rw----. 1 mysql mysql  33 Oct  6 12:06 binlogs.index
    drwxr-xr-x. 2 mysql mysql  78 Oct  6 11:33 innologs   


5) On the Master node determine the current binary log file name and position :

mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| binlogs.000001 |      120 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+


6) On the Slave execute the following command :

mysql> change master to MASTER_HOST='mysqlvm01master',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='xxxxxx',
    -> MASTER_LOG_FILE='binlogs.000001',
    -> MASTER_LOG_POS=120;


7) Start the slave process and check the status :

mysql> start slave;
mysql> show slave status\G

That's all,our Master / Slave replication installation is now ready to work !

I could say lots of other stuff about this features but for now that's all !






See you soon 

Bye