MySQL主从复制实战

MYSQL主从复制环境构建至少需2台服务器,可以配置1主多从,多主多从,如下为1主1从,MYSQL主从复制架构实战步骤:

  • 系统环境准备

Master:192.168.111.128

Slave: 192.168.111.129

  • Master安装及配置

Master端使用源码安装MySQL-5-5版本软件后,在/etc/my.cnf 配置文件[mysqld]段中加入如下代码,然后重启MYSQL服务即可。如果在安装时cp my-large.cnf /etc/my.cnf,则无需添加如下代码:

server-id = 1

log-bin = mysql-bin

Master端/etc/my.cnf完整配置代码如下:

[client]

port        = 3306

socket    = /tmp/mysql.sock

[mysqld]

port        = 3306

socket    = /tmp/mysql.sock

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

thread_concurrency = 8

log-bin=mysql-bin

binlog_format=mixed

server-id    = 1

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 128M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

Master数据库服务器命令行中 创建tongbu用户及密码并设置权限,执行如下命令,查看bin-log文件及position点,如图11-13所示:

grant replication slave on *.* to ‘tongbu’@’%’ identified by ‘123456’;

show master status;

图11-13 MYSQL Master授权用户

  • Slave安装及配置

Slave端使用源码安装MySQL-5-5版本软件后,在/etc/my.cnf 配置文件[mysqld]段中加入如下代码,然后重启MYSQL服务即可。如果在安装时cp my-large.cnf /etc/my.cnf,则需修改server-id,MASTER与Slave端server-id不能一样,Slave端也无需开启bin-log功能:

server-id = 2

Slave端/etc/my.cnf完整配置代码如下:

[client]

port        = 3306

socket    = /tmp/mysql.sock

[mysqld]

port        = 3306

socket    = /tmp/mysql.sock

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

thread_concurrency = 8

server-id    = 2

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 128M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

Slave指定Master IP、用户名、密码、bin-log文件名(mysql-bin.000028)及position(257),代码如下:

change master to

master_host=’192.168.1.115′,master_user=’tongbu’,master_password=’123456′,master_log_file=’mysql-bin.000001′,master_log_pos=297;

在slave启动slave start,并执行show slave status\G查看Mysql主从状态:

slave start;

show slave status\G

查看Slave端IO线程、SQL线程状态均为YES,代表Slave已正常连接Master实现同步:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

执行Show slave status\G,常见参数含义解析:

Slave_IO_State                     I/O线程连接Master状态;

Master_User                         用于连接Master的用户;

Master_Port                         Master端监听端口;

Connect_Retry                     主从连接失败,重试时间间隔;

Master_Log_File                    I/O线程读取的Master二进制日志文件的名称。

Read_Master_Log_Pos              I/O线程已读取的Master二进制日志文件的位置;

Relay_Log_File                    SQL线程读取和执行的中继日志文件的名称。

Relay_Log_Pos                    SQL线程已读取和执行的中继日志文件的位置;

Relay_Master_Log_File             SQL线程执行的Master二进制日志文件的名称;

Slave_IO_Running                 I/O线程是否被启动并成功地连接到主服务器上;

Slave_SQL_Running                SQL线程是否被启动;

Replicate_Do_DB                    指定的同步的数据库列表;

Skip_Counter                     SQL_SLAVE_SKIP_COUNTER设置的值;

Seconds_Behind_Master             Slave端SQL线程和I/O线程之间的时间差距,单位为秒,常被用于主从延迟检查方法之一。

在Master端创建mysql_db_test数据库和t0表,如图11-14所示,命令如下:

create database mysql_ab_test charset=utf8;

show databases;

use mysql_ab_test;

create table t0 (id varchar(20),name varchar(20));

show tables;

图11-14 MYSQL master创建数据库和表

Slave服务器查看是否有mysql_ab_test数据库和t0的表,如果存在则代表Slave从Master复制数据成功,证明MYSQL主从配置至此已经配置成功,如图11-15所示:

图11-15 MYSQL Slave自动同步数据

在Master服务器的t0表插入两条数据,在slave查看是否已同步,master上执行如下图11-16所示:

insert into t0 values (“001″,”wugk1”);

insert into t0 values (“002″,”wugk2”);

select * from t0;

图11-16 MYSQL Master insert数据

Slave端执行查询命令,如图11-17所示,表示在Master插入的数据已经同步到Slave端:

图11-17 MYSQL Slave数据已同步