MySQL synchronization of master and slave
softwore information：
os type： CentOS 6.3
version：mysql-5.5.28
master ip: 192.168.137.27
slave ip 192.168.137.28
How to deploy mysql,you can refer to https://www.roamway.com/55.html
Edit configuartion file.
vim /etc/my.cnf
Warning：we only demo the basic fouction simply.You may need to add other extra feature
when this cluster be used to production.
[mysqld] part of mster configuration
[mysqld]
socket=/usr/local/mysql/data/mysql.sock
default-storage-engine=MyISAM
server-id=27
log-bin=mysqlmaster-bin.log
sync_binlog=1
lower_case_table_names=1
log_bin_trust_function_creators=1
[mysqld] part of slave configuration
[mysqld]
socket=/usr/local/mysql/data/mysql.sock
default-storage-engine=MyISAM
server-id=28
log-bin=mysqlslave-bin.log
sync_binlog=1
l0wer_case_table_names=1
l0g_bin_trust_function_creators=1
Edit startup script of master and slave
vim /etc/init.d/mysqld
modify the value of “basedir=” and “datadir=”
mysql initialization
/usr/local/mysql/scripts/mysql_install_db \
–basedIr=/usr/local/mysql \
–datadir=/usr/local/mysql/data \
–user=mysql
Start master and slave, logining master and creating account which is used to synchronization.
[root@mysqlmaster ~]#/usr/local/mysql/bin mysql -uroot -p
mysql> GRANT REPLICATON SLAVE ON *.* TO ‘slave’@’192.168.137.28’ IDENTIFIED BY ‘123456’;
Query OK, 0 rows affected (0.34sec)
Check status of master and note down File field and the value of Position,
The information will be used when configure this cluster.
mysql> show master status;
+————————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+————————+———-+————–+——————+——————-+
| mysqlmaster-bin.000004 | 963| | | |
+————————+———-+————–+——————+——————-+
1 row in set (0.00 sec)
startup slave and login it ,execute the following command.
change master to master_host=’192.168.137.27′,master_user=’slave’,master_password=’123456′,
master_log_file=’mysqlmaster-bin.00004′,master_log_pos=963;
And the value of master_log-bin and master_log_pos which were queried from master.
start duplicate thread in slave.
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
check status of slave
mysql> show slave status
If both the status of Slave_IO_Running and Slave_SQL_Running are yes，It indicates that the
synchronization from master to slave is successfull, as shown in the following figure.
Confirm whether the synchronization machenism works or not .
master operation:
use roamway;
show tables;
create table test2(id int primary key auto_increment,name varchar(20))auto_increment=1;
insert into test2 values(1,’roamway同步测试’);
show tables;
select * from test2;
slave operation:
use roamway;
show tables;
select * from test2;
It means that data synchronization from master to slave takes effect