mysql集群优化_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 变化