Build MySQL master-slave copy and read/write separation

Build MySQL master-slave copy and read/write separation Case Overview:

在实际环境中 ，如果对数据库的读和写都在同一个数据库服务中操作 ，无论实在安全性、高可用性，还是高并发等各个方面都是完全不能满足实际需求的 ，因此 ，一般来说都只通过主从复制的方式来同步数据 ，在通过读写分离来提升数据库的并发负载能力 ，这样的方案来进行部署与实施 。

Environment Extension Map:

The circumstances of the case:

Host	Operating System	IP Address	main software
Primary server	CentOS 7.3 x86_64	192.168.217.130	Ntp
From server 1	CentOS 7.3 x86_64	192.168.217.131	Ntpdate
From server 2	CentOS 7.3 x86_64	192.168.217.132	Ntpdate
Amoeda	CentOS 7.3 x86_64	192.168.217.129	Jdk-6u14-linux-x64.bin amoeba-mysql-binary-2.2.0.tar.gz
Client	CentOS 7.3 x86_64	192.168.217.128	Mysql

Case implementation: Configure the primary server:

2. Set up the time synchronization service on the primary server to install NTP:

   yum install ntp -y

4. To configure NTP:

   vim /etc/ntp.conf                             #编辑配置文件 添加server 127.127.217.0                     #设置本地是时钟源fudge 127.127.217.0 stratum 8      #设置时间层级为8（限制在15内）#127.127  ntp时间服务器的保留ip地址，作用是使用本机作为客户端的时间服务器

6. Open service:

   systemctl start ntpd.service systemctl stop firewalld.service setenforce 0

8. To configure the MySQL configuration file:

   vim /etc/my.cnf        #在配置文件添加[mysqld]                     server-id = 11                    #修改   ID 主从不一样log-bin=master-bin            #开启二进制日志log-slave-updates=true     #开启从服务器同步主服务器二进制日志

10. Restart the service and enter the database to authorize from the server:

    mysql -u root  -pmysql>GRANT REPLICATION SLAVE ON *.* TO ‘myslave‘@‘192.168.217.%‘ IDENTIFIED BY ‘123456‘;#允许从服务器使用 myslave账户 登录数据库 mysql> FLUSH PRIVILEGES;    #刷新权限mysql> show master status;       #查看主服务器状态+-------------------+----------+--------------+------------------+-------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------+----------+--------------+------------------+-------------------+| master-bin.000001 |      604 |              |                  |                   |+-------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

    Remember the binary log file name and offset of the primary server.

To configure the slave server:

2. Install ntpdate sync Time:

   yum install ntpdate -y      #从服务器上安装的 [[email protected] ~]# systemctl start ntpdate.service                   #开启服务[[email protected] ~]# /usr/sbin/ntpdate 192.168.217.130            #时间同步6 Jul 09:45:44 ntpdate[2941]: adjust time server 192.168.217.130 offset -0.233224 sec   #同步成功systemctl stop firewalld.service setenforce 0

4. To configure the MySQL configuration file:

   vim /etc/my.cnf[mysqld]    server-id = 12      # id 不一样  别一台从服务器同样配置 id 不同即可relay-log=relay-log-bin      #开启中继日志relay-log-index=slvae-relay-bin.index      #指定位置和名称

6. Restart the service and go to MySQL:

   mysql -u root -pmysql> change master to master_host=‘192.168.217.130‘,master_user=‘myslave‘,master_password=‘123456‘,master_log_file=‘master-bin.000001‘,master_log_pos=604;   #  主服务器地址、授权用户、二进制日志名称、偏移量 。mysql> start slave;   # 开启从服务器mysql> show slave status\G;                 #查看从服务器状态*************************** 1. row ***************************           Slave_IO_State: Waiting for master to send event              Master_Host: 192.168.217.130              Master_User: myslave              Master_Port: 3306            Connect_Retry: 60          Master_Log_File: master-bin.000001      Read_Master_Log_Pos: 604           Relay_Log_File: relay-log-bin.000002            Relay_Log_Pos: 321    Relay_Master_Log_File: master-bin.000001         Slave_IO_Running: Yes                # 两个为yes 表示同步中        Slave_SQL_Running: Yes              

To configure the Amoeda proxy server:

2. To configure a Java environment variable:

   cp jdk-6u14-linux-x64.bin /usr/local/cd /usr/local/./jdk-6u14-linux-x64.bin     #运行jdk#之后输入yes 或者 按回车键即可mv jdk1.6.0_14/ /usr/local/jdk1.6     #重命名

   vi /etc/profile     # 添加export JAVA_HOME=/usr/local/jdk1.6      #java家目录export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib     #指定运行库export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin     #识别java命令export AMOEBA_HOME=/usr/local/amoeba     #指定amoeba家目录export PATH=$PATH:$AMOEBA_HOME/bin     # amoeba命令

   source /etc/profile     #刷新环境变量

4. Configure Amoeba Software:

   mkdir /usr/local/amoebatar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/chmod -R 755 /usr/local/amoeba//usr/local/amoeba/bin/amoebaamoeba start|stop     #出现 amoeba start|stop 说明 amoeba 安装成功

6. Configuring amoeba Read-Write separation:

   mysql> grant all on *.* to ‘test‘@‘192.168.217.%‘ identified by ‘abcabc‘;#在三台mysql上添加权限开放给amoeba访问

   cd /usr/local/amoebavim conf/amoeba.xml     #编辑 amoeba 配置文件

   30<property name="user">amoeba</property>   #第30、32行 用户名 密码  这是客户端访问 amoeba 所需要的账户密码31 32 <property name="password">123456</property>

   # 把注释去掉 开启服务115<property name="defaultPool">master</property>     #修改 默认池116<property name="writePool">master</property>     #修改 写入池   写操作写 mysql 主服务器117<property name="readPool">slaver</property>     #修改 读入池   读操作读 mysql 从服务器

7. To configure the Dbservers.xml configuration file:

     vim conf/dbservers.xml <property name= "schema" >test</property> #把这句话 Note Either create a test database on the MySQL master server, or change the test to a database name that already exists on the MySQL master server <property name= "user" >test</ property> #允许amoeba access the database account password 26 # Remove the comment open service <property name= "Password" >ABCABC</PROPERTY>  

   <dbserver name= "Master" parent= "Abstractserver" > #修改 MySQL master server name <factorycon fig> <!--mysql IP-->45 <property name= "ipAddress" >192.     168.217.130</property> #修改 Primary server IP address <dbserver name= "slave1" parent= "Abstractserver" >                         #修改 MySQL from server name <factoryconfig>51 <!--mysql IP-->52 <property name= "ipAddress" >192.168.217.131</property> #修改 from server IP address &lt ;/factoryconfig>54 </dbserver>55 #添加 slave2 <dbserver name= "slave2" parent= "Abstractserver                         "> #MySQL from server name <factoryconfig>58 <!--MySQL IP-->59 <property name= "ipAddress" >192.168.217.132</property> # Slave Server IP address 60 & lt;/factoryconfig>61        </dbserver>62 <dbserver name= "Slaves" virtual= "true" > #定义 slaves Pool <p Oolconfig class= "Com.meidusa.amoeba.server.MultipleServerPool" >64 <!--Load balancing Strate Gy:1=roundrobin, 2=weightbased, 3=ha-->65 <property name= "LoadBalance" >1</property&                         Gt;66 <!--separated by Commas,such As:server1,server2,server1-->68                 <property name= "Poolnames" >slave1,slave2</property> #修改 slaves Pool contains slave1, slave269 </poolConfig>

9. To start the Amoeba service:

   /usr/local/amoeba/bin/amoeba start &     #后台启动netstat -anpt | grep java     #查看8066端口systemctl stop firewalld.service setenforce 0

   To configure the client:
11. Install MySQL on the client:

    yum install -y mysqlmysql -u amoeba -p123456 -h 192.168.217.129 -P8066     #链接 amoeba 代理服务   ip地址是 amoeba 地址

Read-Write Separation test:

1. Create a table on the client to see if the master-slave server is created.
2. In a MySQL from the server table insert information, the client to see the information should be a bit of information, no information. Because the read MySQL from the server is read in turn.

   PS: The database authorized to amoeba access to the rights account, password and amoeba to the client grant rights account, password do not mistake.

Build MySQL master-slave copy and read/write separation