MYSQL 读写分离及分表

MYSQL 读写分离及分表

目标：

读写分离

master+2×slave

 

 

配置master服务器

1 创建replication帐号

create user cp_acct;

GRANT REPLICATION SLAVE ON *.* TO 'cp_acct'@'192.168.0.%' IDENTIFIED BY '123456';

 

2 查找mysql配置文件位置，并修改

mysql --help | grep my.cnf

1 server-id=1   //给数据库服务的唯一标识，一般为大家设置服务器Ip的末尾号

2 log-bin=master-bin

3 log-bin-index=master-bin.index

innodb_flush_log_at_trx_commit=1

sync_binlog=1

 

show master status;

 

 配置slave服务器

1 [mysqld]

2 server-id=2

3 relay-log-index=slave-relay-bin.index

 

4 relay-log=slave-relay-bin

 

连接master：

master-bin.000001

change master to master_host='192.168.0.107',

master_port=3306,

master_user='cp_acct',

master_password='123456', 

master_log_file='master-bin.000001',

master_log_pos=0;

 

slave start;

show slave status;

 

（2）连接Master

change master to master_host='192.168.0.104', //Master 服务器Ip
master_port=3306,
master_user='repl',
master_password='mysql', 
master_log_file='master-bin.000001',//Master服务器产生的日志
master_log_pos=0;

（3）启动Slave

start slave;

 

----------

server-id=2

relay-log-index=slave-relay-bin.index

relay-log=slave-relay-bin

expire_logs_days        = 10

max_binlog_size         = 100M

 

# Replication slave 

master-host="192.168.0.107"

master-user=cp_acct

master-password=123456

master-port=3306

master-connect-retry=60

replicate-do-db=usp 

replicate-Ignore-db=information_schema

----------------

 

 查看状态 及调试 

 

1,查看master的状态 

SHOW MASTER STATUS; 

Position 不应为0 

2,查看slave的状态 

show slave status; 

Slave_IO_Running | Slave_SQL_Running 这两个字段 应为 YES|YES. 

show processlist; 

会有两条记录与同步有关 state为 Has read all relay log; waiting for the slave I/O thread to update it 

和s Waiting for master to send event . 

3,错误日志 

MySQL安装目录dataHostname.err 

 

主服务器上的相关命令： 

show master status 

show slave hosts 

show {master|binary} logs 

show binlog events 

purge {master|binary} logs to ’log_name’ 

purge {master|binary} logs before ’date’ 

reset master(老版本flush master) 

set sql_log_bin={0|1} 

----------------------------------------------------------------------------------- 

从服务器上的相关命令: 

slave start 

slave stop 

SLAVE STOP IO_THREAD //此线程把master段的日志写到本地 

SLAVE start IO_THREAD 

SLAVE STOP SQL_THREAD //此线程把写到本地的日志应用于数据库 

SLAVE start SQL_THREAD 

reset slave 

SET GLOBAL SQL_SLAVE_SKIP_COUNTER 

load data from master 

show slave status(SUPER,REPLICATION CLIENT) 

CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //动态改变master信息 

PURGE MASTER [before ’date’] 删除master端已同步过的日志