mysql最佳化(3) 叢集配置

標籤：

兩台伺服器 192.168.187.131 192.168.187.132

1.主從配置 131為主 132為從

在131下

vim /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0#######################server-idserver-id = 131 伺服器id#binary loglog-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.logpid-file=/var/run/mysqld/mysqld.pidservice mysqld restart

在132下

vim /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0#################server-id=132  #relay logrelay-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.logpid-file=/var/run/mysqld/mysqld.pidservice mysqld restart 

開放131mysql許可權

grant replication client,replication slave on *.* to ‘repl‘@‘%‘ identified by ‘repl‘;show master status;  //查看主伺服器資訊

132下

change master tomaster_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=1show 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.gztar zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit.tar.gzcd 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 變化

mysql最佳化(3) 叢集配置