Mysql Scalability(1)Master - Slave

Mysql Scalability(1)Master - Slave

1. Master - Slave Concept
Steps During Replica
     #1 Master update the binary log events
     #2 Slave copy Master binary log events to relay log
     #3 Slave execute the relay log

I will have 1 master ubuntu-master, 1 slave ubuntu-client1.
2. Install Mysql 
>sudo apt-get install software-properties-common
>sudo add-apt-repository ppa:ondrej/mysql-5.6
>sudo apt-get update
>sudo apt-get install mysql-server

3. Change the Configuration
>cd /etc/mysql
>sudo vi my.cnf

For Master
#Comments the line bind-address
#bind-address       = 127.0.0.1
#Configure the the server-id and log file directory
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log 

For Slave
server-id               = 11
#this is optional, if the slave is not master of other slaves, we do not need to open log_bin
log_bin                 = /var/log/mysql/mysql-bin.log
#bind-address       = 127.0.0.1

Restart the Mysql Server 
>sudo /etc/init.d/mysql restart

Start to Run
Login on to master as root
>use mysql;
>grant replication slave on *.* to 'masterslave'@'ubuntu-client1' identified by ‘111111';
>show master status;
+------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 |      561 |              |                  |                   | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

The number 561 and mysql-bin.000001 are important and it will be used later.

Login on to slave as root
>use mysql;
>change master to master_host='ubuntu-master', master_user='masterslave', master_password='kaishi', master_log_file='mysql-bin.000001', master_log_pos=561;
>start slave;
>show slave status\G;

Here is the status
              Master_Log_File: mysql-bin.000002          Read_Master_Log_Pos: 120               Relay_Log_File: mysqld-relay-bin.000005                Relay_Log_Pos: 283        Relay_Master_Log_File: mysql-bin.000002             Slave_IO_Running: Yes            Slave_SQL_Running: Yes

The most import part is Slave_IO_Running:Yes, Slave_SQL_Running:Yes. Ok, that means the master slave is running fine.

4. Verify the Installation
Create schema on Master
>create database sillycat;
>use sillycat;

Create table and Insert Data
>create table branduser(id int primary key, username char(255), age int);
>insert into branduser(id, username, age) values ( 1, "sillycat", 32);

Logon on to the slave mysql, My data is already there
>use sillycat;
>select * from branduser;


References:
http://xuwensong.elastos.org/2014/01/07/ubuntu-%E4%B8%8B-mysql-%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6%E5%8F%8Amysql-proxy-%E8%AF%BB%E5%86%99%E5%88%86%E7%A6%BB/
http://blog.csdn.net/hguisu/article/details/7325124
http://heylinux.com/archives/1004.html

ubuntu set up
http://sillycat.iteye.com/blog/2090147