MySQL master-slave replication and read/write separation

MySQL master-slave replication and read-write separation pre-test environment preparation

• Linux two units
• MySQL database one master one from

Start building MySQL master server installation NTP

[[email protected] ~] yum install ntp -y

Modifying the NTP master configuration file

# 在配置文件里面添加server 127.127.100.0                    //本地是时钟源//fudge 127.127.100.0 stratum 8          //设置时间层级为8（限制在15内）//#开启主服务器的ntp

Installing Ntpdate from the server

Configure MySQL master server settings

#修改主配置文件vim /etc/my.cnfserver-id       = 11               log-bin=master-bin                         //主服务器日志文件//log-slave-updates=true                     //从服务器更新二进制日志//mysql -u root -p                           //进入数据库GRANT REPLICATION SLAVE ON *.* TO ‘myslave‘@‘192.168.32.%‘ IDENTIFIED BY ‘123456‘;#允许192.168.32.0网段的主机以myslave为用户名，密码123456，访问数据库的任何库和任何表FLUSH PRIVILEGES;#刷新数据库mysql> show master status;  #查看+-------------------+----------+--------------+------------------+-------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------+----------+--------------+------------------+-------------------+| master-bin.000001 |      603 |              |                  |                   |+-------------------+----------+--------------+------------------+-------------------+1 row in set (0.01 sec)

From server settings

server-id       = 22   #server-id 要和主服务器不一样是myql的身份身份标识是唯一的relay-log=relay-log-bin                         //从主服务器上同步日志文件记录到本地//relay-log-index=slave-relay-bin.index           //定义relay-log的位置和名称//#进入mysql里面指定主服务器IP地址、用哪个用户登录、登陆密码、二进制文件名、偏移量（注：偏移量要随时看因为他会随时更新）change master to master_host=‘192.168.100.101‘,master_user=‘myslave‘,master_password=‘123456‘,master_log_file=‘master-bin.000002‘,master_log_pos=339;#开启从服务器同步start slave;show slave status\G;             //查看状态//下面两个状态要显示开启如果是no的话表名主从复制没开启成功就要检查了             Slave_IO_Running: Yes            Slave_SQL_Running: Yes

Verifying master-Slave synchronization

主服务器上 create database db_test;去从服务器上 show databases;

Above is the master-slave synchronization, the following installed amoeba is read and write separate dispatch server configuration Amoeba Server

#安装java环境./jdk-6u14-linux-x64.bin 运行 中间有一个yes or no 的交互输入yes#修改环境变量，在/etc/profile里面在最后一行添加export JAVA_HOME=/usr/local/jdk1.6export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/libexport PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/binexport AMOEBA_HOME=/usr/local/amoebaexport PATH=$PATH:$AMOEBA_HOME/bin#用source 运行profile[[email protected] local] source /etc/profile

Install amoeba above do two MySQL database to do the master-slave copy to do read-write separation and add a MySQL node server to do from

#先要有amoeba的源代码包解压到/usr/local里面创建一个amoeba的文件然后解压进去tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/chmod -R 755 /usr/local/amoeba/ #给amoeba文件夹755的执行权限/usr/local/amoeba/bin/amoeba  #输入这句话显示下列内容表名安装成功显示amoeba start|stop说明安装成功#在三台mysql上添加权限开放给amoeba访问grant all on *.* to [email protected]‘192.168.100.%‘ identified by ‘123.com‘;

Back to Amoeba server Cd/usr/local/amoebavim Conf/amoeba.xml

---30 rows-<property name= "user" >amoeba</property>----32 lines---------<property name= "Password" > 123456</property>---117-remove Comment-<property name= "Defaultpool" >master</property> <property name= " Writepool ">master</property> <property name=" Readpool ">slaves</property>vi conf/ dbservers.xml--26-29--Remove Annotations--<property name= "user" >test</property> <property name= "Password" > 123.com</property>-----42-Primary Server address---<dbserver name= "master" parent= "Abstractserver" > <property name= "IpAddress" >192.168.100.101</property>--52-from the server host name-<dbserver name= "slave1" parent= "Abstractserver" >--55-from server address-<property name= "ipAddress" >192.168.100.102</property> <dbserver name= "Slaves" Virtual= "true" > <poolconfig class= "com.meidusa.amoeba.server.MultipleServerPool" >--End--<property Name = "Poolnames" >slave1,slave2</property> </poolconfig>/usr/local/amoeba/bin/amoeba start& netstat -ANPT | grep java

A database with a test in line 23

 23                         <property name="schema">mysql</property>  虽然能登录主服务器的数据库但是不能对其进行操作 里面原来是test但是主服务器上没有test这个数库所 以会报错我们要把test改为主服务器里面已经存在的 数据库才能对它进行相应操作

Want to experience how to read and write the separation down from the server slave separate operation view

---在两台从上-----stop slave;----在主服务器上---内容不会同步insert into zang values(‘1‘,‘zhang‘,‘this_is_master‘);----从服务器1----use benet;insert into zang values(‘2‘,‘zhang‘,‘this_is_slave1‘);----从服务器2----use benet;insert into zang values(‘3‘,‘zhang‘,‘this_is_slave2‘);------在客户端上测试----第一次会向从服务器1读数据-第二次会各从2读取select * from zang;-------------在通过客户端连接数据库后写入的数据只有主会记录，然后同步给从-从服务器不会记录，从而实现了读写分离。----insert into zang values(‘5‘,‘zhang‘,‘write_test‘);-----看不到新插入的数据--因为同步没有开启-----只有主服务器上有数据。select * from zang;

MySQL master-slave replication and read/write separation