#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