MySQL Clustering - Master / Slave

We will need two Ubuntu Servers.

for example:

hostname: IP
--------------------------------------
ubuntu-one: 10.66.66.36
ubuntu-two: 10.66.66.29

To begin with we'll install and configure MySQL for normal use on each of the boxes.

sudo apt-get install mysql-server --yes

Set a strong MySQL root password and wait for the packages to download and install, then edit /etc/mysql/my.cnf to make MySQL listen on all IP addresses.

bind-address = 0.0.0.0

Now restart MySQL and fire up the MySQL command-line client to check all is good.

sudo /etc/init.d/mysql restart

mysql -u root -p
Enter password: [enter the MySQL root password you chose earlier]
mysql> 

If you got the mysql> prompt then MySQL is running. Try connecting to the other node across the network to see if the firewall is opened and MySQL is listening on the network interface.

From ubuntu-one run:

mysql -h ubuntu-two -u root -p

Enter password: [enter the MySQL root password you chose earlier]
ERROR 1130 (00000): Host 'db-01' is not allowed to connect to this MySQL server

If you got the above error then everything is working fine - MySQL connected and refused to authorise the client.

The first thing we want to do is setup a simple master-slave
replication to see that it's possible to replicate data from one
database host to the other. This requires a binary log so tell MySQL on
ubuntu-one to keep one.

Edit /etc/mysql/my.cnf and set the following values under the replication section:

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = my_application <=== Database to replicate
binlog_ignore_db        = mysql <=== Database to ignore
binlog_ignore_db        = test <=== Database to ignore  On ubuntu-one grant\
                                   replication slave rights to ubuntu-two.

Change some_password to a real, strong password.
Afterwards, make sure you restart MySQL.

mysql -u root -p

Enter password: [enter the MySQL root password you chose earlier]
mysql> grant replication slave on *.* to 'replication'@'ubuntu-two' identified by 'some_password';

sudo /etc/init.d/mysql restart

Jump on to ubuntu-two and set it up to replicate data from ubuntu-one by editing /etc/mysql/my.cnf, again replacing the hostname, username and password with the values for ubuntu-one.

server-id = 2
master-host = ubuntu-one
master-user = replication
master-password = some_password
master-port = 3306

One way replication should now be setup. Restart MySQL and check the
status of the slave on ubuntu-two. If the Slave_IO_State is "Waiting for
master to send event" then you've been successful.

# Run this on ubuntu-two only

sudo /etc/init.d/mysql restart
mysql -u root -p
Enter password: [enter the MySQL root password you chose earlier]
mysql> show slave status ;
*************************** 1. row ***************************
          Slave_IO_State: Waiting for master to send event
             Master_Host: 193.219.108.241
             Master_User: replication
             Master_Port: 3306
           Connect_Retry: 60
         Master_Log_File: mysql-bin.000005
     Read_Master_Log_Pos: 98
          Relay_Log_File: mysqld-relay-bin.000004
           Relay_Log_Pos: 235
   Relay_Master_Log_File: mysql-bin.000005
        Slave_IO_Running: Yes
       Slave_SQL_Running: Yes
         Replicate_Do_DB:
     Replicate_Ignore_DB:
      Replicate_Do_Table:
  Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
              Last_Errno: 0
              Last_Error:
            Skip_Counter: 0
     Exec_Master_Log_Pos: 98
         Relay_Log_Space: 235
         Until_Condition: None
          Until_Log_File:
           Until_Log_Pos: 0
      Master_SSL_Allowed: No
      Master_SSL_CA_File:
      Master_SSL_CA_Path:
         Master_SSL_Cert:
       Master_SSL_Cipher:
          Master_SSL_Key:
   Seconds_Behind_Master: 0