MySQL master-slave replication and read/write separation

Host	Operating System	IP Address	main software
Master	Centos7.3x64	192.168.200.132	MySQL 5.5.24
Slave1	Centos7.3x64	192.168.200.130	MySQL 5.5.24
Slave2	Centos7.3x64	192.168.200.128	MySQL 5.5.24
Amoeba	Centos7.3x64	192.168.200.133	Amoeba,jdk
Client	Centos7.3x64	192.168.200.129	MySQL 5.5.24

Objective:

• Types of replication supported by MySQL
• Copy-based statement. Execute the same statement from the server. MySQL is used by default.
• Row-based replication. Copy the changed content to the past, not the server in the execution of a.
• Mixed type replication. By default, statement-based replication is used, and once a statement-based replication is found to be accurate, row-based replication

Read/write Separation:

1. In simple terms, read-write separation is written on the primary server and is read only from the server. The rationale is to have the primary database process transactional queries and to process select queries from the database. Data a copy is used to synchronize the changes caused by the things query to the amoeba from the database in the cluster.

   Amoeba (amoeba) project, the Open source framework launched a amoeba for MySQL software in 2008. This software is dedicated to MySQL's distributed database front-end agent layer, which is mainly used as the SQL routing function when the application layer accesses MySQL, focusing on the development of the Distributed Database Proxy layer (DB proxy), which is located between the client, DBServer (s), Transparent to the client. With load balancing, high availability, SQL filtering, read and write separation, routable related to the target database, multiple database merge results can be requested concurrently.

   The only thing that's going on in the experiment is shutting down the firewall at the very beginning. Don't cause anything to be configured because the fire does not shut down and cause an error

systemctl stop firewalld.servicesetenforce 0

Master service MySQL configuration sync time

yum install ntp -y  // 时间同步 在centos7.3 中默认是安装好的(不是最小化安装)vim /etc/ntp.conf  //修改其主配置文件  因为我的网段全是200 段的server 127.127.200.0                    //本地是时钟源// fudge 127.127.200.0 stratum 8          //设置时间层级为8（限制在15内）//启动服务systemctl start ntpdate.service

Same operation from the server can be done at the same time two

systemctl start ntpd.service //开启服务/usr/sbin/ntpdate 192.168.200.132  //同步主服务器的时间当提示the NTP socket is in use, exiting  //说明同步好了可以使用

Master, Slave1, Slave2 respectively install MySQL database installation MySQL5.5.24 If the conditions allow three simultaneous operation

Yum install gcc gcc-c++ make cmake ncurses-devel bisonlibaio-devel-y//install required environment tar ZXVF mysql-5.5.24.tar.gz-c/opt/// . mysql package extracted to/opt directory useradd-s/sbin/nologin mysql//create Admin user mkdir/usr/local/mysql//Create storage directory go to MySQL extract Directory # config Mysqlcmake- Dcmake_install_prefix=/usr/local/mysql-dmysql_unix_addr=/home/mysql/mysql.sock-ddefault_charset=utf8-ddefault_ Collation=utf8_general_ci-dwith_extra_charsets=all-dwith_myisam_storage_engine=1-dwith_innobase_storage_engine =1-dwith_memory_storage_engine=1-dwith_readline=1-denabled_local_infile=1-dmysql_datadir=/home/mysql-dmysql_ User=mysql-dmysql_tcp_port=3306make & make Install//perform installation chown-r Mysql.mysql/usr/local/mysql//modify MySQL directory belong to the main group Expo RT path= $PATH:/usr/local/mysql/bin///Refresh environment variable CP support-files/my-medium.cnf/etc/my.cnf//copy mysql default configuration file and Startup script CP support-files/mysql.server/etc/init.d/mysqld//copy mysql default configuration file and startup script/usr/local/mysql/scripts/mysql_install_ DB \/Switch to MySQL extract directory//Initialize database--user=mysql--ldata=/var/lib/MySQL--basedir=/usr/local/mysql--datadir=/home/mysqlln-s/var/lib/mysql/mysql.sock/home/mysql/mysql.sock/* Straight Install the MySQL installation path and data directory path in the soft connection */vim/etc/init.d/mysqld//mysqld Basedir=/usr/local/mysqldatadir=/home/mysqlservice mysqld start mysqladmin-u root password ' abc123 '//set MySQL login password mysq-u root-p//Can Login

Start configuring master-slave MySQL

主MySQL 主配置文件vim /etc/my.cnfserver-id       = 11                        //主个从的区别总得有一个做老大吧log-bin=master-bin                         //主服务器日志文件//log-slave-updates=true                     //从服务器更新二进制日志//service mysqld restart从MySQL 主配文件  两个从MySQL一样的配置vim /etc/my.cnf  注意这是在从服务器上配置server-id       = 22                    //id这个两个从的服务器都一样relay-log=relay-log-bin                         //从主服务器上同步日志文件记录到本地//relay-log-index=slave-relay-bin.index           //定义relay-log的位置和名称//到此主从同步读写分离配置文件就到此接数

To create a user from a server synchronization for slaves

GRANT REPLICATION SLAVE ON *.* TO ‘myslave‘@‘192.168.200.%‘ IDENTIFIED BY ‘123456‘;给与从服务器权限 对方的名字为myslave 访问的网段200端 密码 123456FLUSH PRIVILEGES;  //立即生效show master status;  //查看状态以上就是主服务器的同步配置下面是从服务器同步配置当然实在MySQL模式下change master to master_host=‘192.168.200.132‘,master_user=‘myslave‘,master_password=‘123456‘,master_log_file=‘master-bin.000004‘,master_log_pos=339; //添加向主服务器同步数据命令start slave;  //开启slave从服务器show slave status\G;  //查看状态

Verifying master-Slave synchronization

Start configuring Amoeba server also known as Amoeba

Copy the JDK package and install the CP jdk-6u14-linux-x64.bin/usr/local/cd/usr/local///Do not forget to switch directories. /jdk-6u14-linux-x64.bin # Yes press ENTER to modify the JDK directory name MV jdk1.6.0_14//usr/local/jdk1.6 add jdk, JRE, amoeba environment variable vim/etc/p Rofile//End Add 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/binsource/etc/profile//Refresh Environment Change Mkdir/usr/local/amoeba//Create amoeba directory tar ZXVF Amoeba-mysql-binary-2.2.0.tar.gz-c/usr/local/amoeba///Unzip amoeba package to/usr/local/amoeba directory Chmod-r 755/usr/local/ amoeba///Modify Amoebab Directory Permissions/usr/local/amoeba/bin/amoeba//Verify amoeba installed successfully display Amoeba Start|stop instructions for successful installation +-------- Add permissions on three MySQL open to amoeba access-----------------------+ |grant all on * * to [email protected] ' 192.168.200.% ' Identified by ' 123.com '; |+------------------------------------------------------------------+vim conf/amoeba.xml/ /configuration file---30 rows--<property name= "user" >amoeba</property> #访问amoeba的用户名----32 lines---------<property name= "password" >123456</property> # Password---117-remove comment-<property name= "Defaultpool" >master</property> #默认为主服务器 <property name= "Writepool" >master</property> #写入为主服务器 <property name= "Readpool" >slaves</property> #读取为slaves池, existing slave1, Slave2 Server

Modify the Dbservers.xml configuration file or do action on the amoeba

Vim conf/dbservers.xml3 <dbserver name= "master" parent= "Abstractserver" > #name为master44 <factoryConfig> <!--mysql IP-->46 <property name= "ipAddress" >192.168.200.132</property> #主服务器IP地址47 </ factoryconfig>48 </dbserver>4950 <dbserver name= "slave1" parent= "Abstractserver" > #name为slave151 < factoryconfig>52 <!--mysql IP-->53 <property name= "ipAddress" >192.168.200.128</property> # From server 1 IP address </factoryconfig>55 </dbserver>5657 <dbserver name= "slave2" parent= "Abstractserver" > # Name is slave258 <factoryconfig>59 <!--mysql IP-->60 <property name= "IpAddress" >192.168.200.130</ property> #从服务器2 IP address </factoryconfig>62 </dbserver>6364 <dbserver name= "Slaves" virtual= "true" > #name为slaves65 <poolconfig class= "Com.meidusa.amoeba.server.MultipleServerPool" >66 <!--Load balancing Strategy:1=roundrobin, 2=weightbased, 3=ha-->67 <property name= "LoadbaLance ">1</property> #默认为轮询方式6869 <!--separated by Commas,such As:server1,server2,server1-->70 < Property Name= "Poolnames" >slave1,slave2</property> #轮询顺序为: slave1, slave271 </poolconfig>72 </ Dbserver>/usr/local/amoeba/bin/amoeba start&//start Service NETSTAT-ANPT | grep java//view Java port

Client

这个就简单了yum install mysql -y    //直接用yum 装mysql -u amoeba -p123456 -h 192.168.200.133 -P8066   //访问的IP是变形虫的IP地址的

Verifying read-Write separation

stop slave;   //关闭同步 在主服务器上创建一个表格create table zhang (id int(10),name varchar(10),address varchar(20));则在客户端可以查看的到在Slave1，Slave2服务器上则看不到反之当在从服务器本身上写入数据则客户端显示出来，主服务器显示不出来

MySQL master-slave replication and read/write separation