mysql优化(3) 集群配置

两台服务器 192.168.187.131 192.168.187.132

1.主从配置 131为主 132为从

在131下

vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
######################
#server-id
server-id = 131 服务器id
#binary log
log-bin = mysql-bin
#statement row mixed  日志格式
binlog-format = mixed
binlog-dp-db=test #指定数据库
binlog-ignore-db=mysql  #防止同步mysql
######################

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

service mysqld restart

在132下

vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#################
server-id=132  
#relay log
relay-log=mysql-relay

master-port=3306
master-connect-retry=60
replicate-ignore-db=mysql
replicate-do-db=test
####################
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

service mysqld restart 

开放131mysql权限

grant replication client,replication slave on *.* to 'repl'@'%' identified by 'repl';

show master status;  //查看主服务器信息

132下

change master to
master_host = '192.168.187.131',
master_log_file = 'mysql-bin.000006',  //根据主服务器来改
master_log_pos=263,
master_user='repl',
master_password='repl';

slave start;
show slave status;  //若显示waiting表示成功

接下来在131下进行sql操作,132就会有相应显示

 

2.主主复制 即两台都为主服务器

实现也很简单，就是各自视对方为主服务器，自己为从服务器即可

在131（原来主服务器）配置加上

relay-log=mysql-relay

在132（原来从服务器）配置加上

log-bin=mysql-bin

binlog-format=mixed

然后像上面描述一样将两边主从同步即可

然后测试 =_=

 

关于主主复制的主键冲突的解决方法：

在两边的mysql中输入

set session auto_increment_increment=2;  
set session auto_increment_offset=1;    
set global auto_increment_increment=2;  
set global auto_increment_offset=1;  

set session auto_increment_increment=2;  
set session auto_increment_offset=2;    
set global auto_increment_increment=2;  
set global auto_increment_offset=2; 

 此方法只适合两个服务器=_=

 

3.被动主主复制（一读一写）

在只读服务器的mysql配置上加上

read-only=1

show variables like '%read%';//查看是否为只读

 

4.mysql-proxy实现负载均衡和读写分离

下载mysql-proxy并使用

wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit.tar.gz
tar zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit.tar.gz
cd mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit
./bin/mysql-proxy -P 4040 --proxy-backend-addresses=192.168.187.131:3306 --proxy-backend-addresses=192.168.187.132:3306  负载均衡

./bin/mysql-proxy -b 192.168.187.131:3306 -r 192.168.187.132 -s /usr/local/src/mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit/share/doc/mysql-proxy/rw-splitting.lua   读写分离

可在windows下连接虚拟机进行测试

 

5.partition分区

create table topic(
    -> tid int primary key auto_increment,
    -> title char(20) not null default ''
    -> )engine myisam charset utf8
    -> partition by range(tid) (
    -> partition t0 values less than(10),
    -> partition t1 values less than(20), 
    -> partition t2 values less than(MAXVALUE)
    -> );

create table user(
    -> uid int,
    -> uname char(6),
    -> aid int
    -> )engine myisam charset utf8
    -> 
    -> partition by list(aid) (
    -> partition bj  values in (1),
    -> partition hb values in (2),  
    -> partition xs  values in (3),   
    -> partition gx  values in (4)  
    -> );

 建表以后可以测试并观察

ll /var/lib/mysql/test 变化