Wednesday, April 22, 2015

Mysql Basic Two server replication(ubuntu14.04 trusty)


#This short article shows basic mysql server replication on ubuntu14.04's
package mysql-server-5.5 version.
# Both servers need to “apt-get install mysql-server-5.5” command.
#Here master server private ip is 10.0.0.17 and slave server is 10.0.0.10.
#This is very basic when both servers have no data. Just start from very fresh installation.

#To communicate between two servers, you should edit Bind-address(default is 127.0.0.1)

root@master:~# vi /etc/mysql/my.cnf

Bind-address = 10.0.0.17

#Then restart mysql server
root@master:~# service mysql restart
mysql stop/waiting
mysql start/running, process 7107

#On master server, REPLICATION GRANT ...execute like belows.
root@master:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.5.43-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
-> TO repl@'10.0.0.10' IDENTIFIED BY 'pass';
Query OK, 0 rows affected (0.00 sec)
#Well ok.

# Also edit server-id and log_bin option like belows.
root@master:~# vi /etc/mysql/my.cnf

# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
# I used ip(private)'s last two digit 17(10.0.0.17)
server-id = 17
log_bin = /var/log/mysql/mysql-bin.log


#Should restart
root@master:~# service mysql restart
mysql stop/waiting
mysql start/running, process 7291
#Master status check.
root@master:~# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.5.43-0ubuntu0.14.04.1-log (Ubuntu)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

#On slave, also,should edit my.cnf file and restart.
root@slave:/var/log/mysql# vi /etc/mysql/my.cnf
bind-address = 10.0.0.10
#omitting#...
server-id = 10
log_bin = /var/log/mysql/mysql-bin.log
relay_log = /var/log/mysql/mysql-relay-bin
log_slave_updates = 1
read_only = 1

#Should restart
root@slave:/var/log/mysql# service mysql restart
mysql stop/waiting
mysql start/running, process 22074

#From slave to master, let the slave know where master is.
root@slave:/var/log/mysql# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.5.43-0ubuntu0.14.04.1-log (Ubuntu)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.17',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='pass',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=0;
Query OK, 0 rows affected (0.02 sec)

#Current status showing.See “Slave_IO_Running: No Slave_SQL_Running: No”
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.17
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
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: 4
Relay_Log_Space: 107
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)

#Let's start slave;
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
#Now Slave_IO_Running and Slave_SQL_Running is Yes.
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.17
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000001
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: 107
Relay_Log_Space: 409
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 17
1 row in set (0.00 sec)


#Master
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 37
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
*************************** 2. row ***************************
Id: 38
User: repl
Host: 10.0.0.10:43292
db: NULL
Command: Binlog Dump
Time: 107
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
2 rows in set (0.00 sec)
mysql> SYSTEM ifconfig eth1
eth1 Link encap:Ethernet HWaddr 08:00:27:e0:fd:90
inet addr:10.0.0.17 Bcast:10.0.0.255 Mask:255.255.255.0
inet6 addr: fe80::a00:27ff:fee0:fd90/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:23 errors:0 dropped:0 overruns:0 frame:0
TX packets:25 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:1697 (1.6 KB) TX bytes:2465 (2.4 KB)

#Slave check.
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 36
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
*************************** 2. row ***************************
Id: 37
User: system user
Host:
db: NULL
Command: Connect
Time: 84
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 38
User: system user
Host:
db: NULL
Command: Connect
Time: 84
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3 rows in set (0.00 sec)

mysql> system ifconfig eth1
eth1 Link encap:Ethernet HWaddr 08:00:27:98:c4:ee
inet addr:10.0.0.10 Bcast:10.0.0.255 Mask:255.255.255.0
inet6 addr: fe80::a00:27ff:fe98:c4ee/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:66 errors:0 dropped:0 overruns:0 frame:0
TX packets:66 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:6342 (6.3 KB) TX bytes:5631 (5.6 KB)

#Now set up is completed. Let's check.

#On Master
mysql> create database replica_test;
Query OK, 1 row affected (0.00 sec)
mysql> use replica_test;
Database changed

mysql> CREATE TABLE test (
-> id varchar(28),
-> name varchar(128)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test values('test','testname');
Query OK, 1 row affected (0.00 sec)

#On slave, you should see replica_test database.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| replica_test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use replica_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> desc test;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | varchar(28) | YES | | NULL | |
| name | varchar(128) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from test;
+------+----------+
| id | name |
+------+----------+
| test | testname |
+------+----------+
1 row in set (0.02 sec)

#Voila!..it is very successful.

#Tip: If you want to disable slave, after chaning my.cnf to original value “stop slave; reset slave; service mysql restart” command will be helpful.


No comments:

Post a Comment