MySQL optimization (3) cluster configuration

Two servers 192.168.187.131 192.168.187.132

1. master-Slave Configuration 131 main 132 is from

Under 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 Server id# Binary Loglog-bin = mysql-bin#statement row mixed  log format 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

Under 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

Open 131mysql Permissions

Grant replication client,replication Slave on * * to ' repl ' @ ' percent ' identified by ' repl '; show master status;  View Primary Server information

132 Down

Change Master Tomaster_host = ' 192.168.187.131 ', master_log_file = ' mysql-bin.000006 ',  //According to the master server to be changed master_log_pos= 263,master_user= ' Repl ', master_password= ' repl ';

Slave start;
show slave status;  If display waiting indicates success

Next in 131 SQL operation, 132 will have a corresponding display

2. Primary master replication is two primary servers

Implementation is also very simple, that is, each other as the main server, their own from the server can

In 131 (original primary server) configuration Plus

Relay-log=mysql-relay

In 132 (original slave server) configuration plus

Log-bin=mysql-bin

Binlog-format=mixed

Then, as described above, you can synchronize both master and slave

Then test the =_=.

Workaround for primary key conflict for primary master replication:

Enter in MySQL on both sides

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;  

This method is only suitable for two servers =_=

3. Passive primary master replication (one read and one write)

On the MySQL configuration of the read-only server, add

Read-only=1show variables like '%read% ';//See if Read-only

4.mysql-proxy for load balancing and read-write separation

Download Mysql-proxy and use

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  load balancer. 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   read/write separation

You can connect a virtual machine to test under Windows

5.partition Partitioning

CREATE TABLE topic (    tid int primary key auto_increment,    title char (a) NOT null default '    ) Engine MyISAM CharSet UTF8-    partition by Range (TID) (+ partition    t0 values less than (ten),    part ition T1 values less than (a),     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),      par Tition xs  values in (3), and       partition GX  values in (4)      ;

Can be tested and observed after the table is built

Ll/var/lib/mysql/test Change

MySQL optimization (3) cluster configuration