mysql主从复制+读写分离

mysql主从复制+读写分离

Mysql主从复制
环境准备：mysql1.1+mysql1.2（主）+mysql1.3
mysql主服务器：
源码包安装mysql过程省略...
[root@mysql /]# vim /etc/my.cnf 
...............................
server_id = 1                       # 修改，ID 要和从服务器区分开来
log_bin = master-bin             # 修改
log-slave-updates = true         # 添加
[root@mysql /]# systemctl restart mysqld                 # 重启服务使配置生效
[root@mysql /]# mysql -u root -p123.com
mysql> grant replication slave on *.* to 'slave'@'192.168.1.%' identified by '123.com';
mysql> flush privileges;
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      408 |             |                 |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# file列显示日志名，position 列显示偏移量，在后面配置从服务器时需要使用
mysql从服务器1.1：
源码包安装mysql过程略..
[root@mysql /]# vim /etc/my.cnf 
................           
server_id = 2                  # ID 记得要和主服务器区分开来
relay-log = relay-log-bin             # 添加
relay-log-index = slave-relay-bin.index            # 添加
[root@mysql /]# systemctl restart mysqld           # 重启使配置生效
[root@mysql /]# mysql -u root -p123.com
mysql> change master to master_host='192.168.1.2',master_user='slave',master_password='123.com',master_Log_file='master-bin.000001',master_log_pos=408;
# 按主服务器的结果更改上面命令中的mater_log_file和mater_log_pos参数
mysql> start slave;             # 启动同步
mysql> show slave status\G    
显示如下：
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.1.2
                Master_User: slave
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: master-bin.000001
        Read_Master_Log_Pos: 408
              Relay_Log_File: relay-log-bin.000002
              Relay_Log_Pos: 284
      Relay_Master_Log_File: master-bin.000001
            Slave_IO_Running: Yes         # 确保这俩行为yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
          Replicate_Do_Table: 
      Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                  Last_Errno: 0
                  Last_Error: 
                Skip_Counter: 0
        Exec_Master_Log_Pos: 408
            Relay_Log_Space: 455
            Until_Condition: None
              Until_Log_File: 
              Until_Log_Pos: 0
          Master_SSL_Allowed: No
          Master_SSL_CA_File: 
          Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
              Master_SSL_Key: 
      Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
              Last_IO_Errno: 0
              Last_IO_Error: 
              Last_SQL_Errno: 0
              Last_SQL_Error: 
Replicate_Ignore_Server_Ids: 
            Master_Server_Id: 1
                Master_UUID: ecfee380-2a4a-11eb-814f-000c29d2f2f0
            Master_Info_File: /usr/local/mysql/data/master.info
                  SQL_Delay: 0
        SQL_Remaining_Delay: NULL
    Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
          Master_Retry_Count: 86400
                Master_Bind: 
    Last_IO_Error_Timestamp: 
    Last_SQL_Error_Timestamp: 
              Master_SSL_Crl: 
          Master_SSL_Crlpath: 
          Retrieved_Gtid_Set: 
          Executed_Gtid_Set: 
              Auto_Position: 0
1 row in set (0.00 sec)

mysql从服务器1.3配置如上！ 验证效果： 主服务器上创建数据库 从服务器上进行同步 即为成功!
Mysql读写分离
环境准备： 主从复制环境+amoeba1.4+client1.5
主从mysql分别给amoeba授权
mysql> grant all on *.* to amoeba@'192.168.1.%' identified by '123.com';
amoeba配置
[root@localhost media]# ls
amoeba-mysql-binary-2.2.0.tar.gz jdk-6u14-linux-x64.bin
[root@localhost media]# cp * /usr/src/
[root@localhost media]# cd /usr/src/
[root@localhost src]# chmod +x jdk-6u14-linux-x64.bin 
[root@localhost src]# ./jdk-6u14-linux-x64.bin             # 执行之后一直空格键，出现yes后输入yes，回车
..................
Do you agree to the above license terms? [yes or no]
yes
Press Enter to continue.....               # 出现此行回车
Done.                               # 说明成功了
[root@localhost src]# mv jdk1.6.0_14/ /usr/local/jdk1.6
[root@localhost src]# vim /etc/profile #为java添加环境变量
添加如下：
export  JAVA_HOME=/usr/local/jdk1.6
export  CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export  PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/bin
export  AMOEBA_HOME=/usr/local/amoeba
export  PATH=$PATH:$AMOEBA_HOME/bin
[root@localhost src]# source /etc/profile     #刷新环境变量

安装并且配置amoeba
[root@localhost /]# mkdir /usr/local/amoeba
[root@localhost /]# cd /usr/src/
[root@localhost src]# tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@localhost /]# chmod -R 755 /usr/local/amoeba/           # 给amoeba目录权限
[root@localhost /]# /usr/local/amoeba/bin/amoeba
amoeba start|stop                       # 显示此内容说明安装成功了
[root@localhost /]# vim /usr/local/amoeba/conf/amoeba.xml           # 编辑配置文件
................................
<property name="authenticator">
                              <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">

                                      <property name="user">amoeba</property>         # 修改客户端登录的用户和密码（搜索user可到）

                                      <property name="password">123.com</property>

                                      <property name="filter">
                                              <bean class="com.meidusa.amoeba.server.IPAccessController">
.........................................
<property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
              <property name="LRUMapSize">1500</property>
              <property name="defaultPool">master</property>   # 修改为master

              <property name="writePool">master</property>     # 修改为master
              <property name="readPool">slaves</property>         # 修改为slaves
              # 以上两行被注释过，需要将注释去掉
              <property name="needParse">true</property>
      </queryRouter>
编辑dbServers/xml文件
[root@localhost /]# vim /usr/local/amoeba/conf/dbServers.xml 
............................
<!-- mysql user -->
                      <property name="user">amoeba</property>             # 修改为amoeba

                      <property name="password">123.com</property>    # 设置密码，将注释去掉
              </factoryConfig>
..............................
<dbServer name="master"  parent="abstractServer">            # 修改名称
              <factoryConfig>
                      <!-- mysql ip -->
                      <property name="ipAddress">192.168.1.2</property>      # 设置主服务器IP
              </factoryConfig>
      </dbServer>

      <dbServer name="slave1"  parent="abstractServer">
              <factoryConfig>
                      <!-- mysql ip -->
                      <property name="ipAddress">192.168.1.1</property>
              </factoryConfig>
      </dbServer>

      <dbServer name="slave2"  parent="abstractServer">
              <factoryConfig>
                      <!-- mysql ip -->
                      <property name="ipAddress">192.168.1.3</property>
              </factoryConfig>
      </dbServer>
<dbServer name="slaves" virtual="true">
              <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
                      <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
                      <property name="loadbalance">1</property>

                      <!-- Separated by commas,such as: server1,server2,server1 -->
                      <property name="poolNames">slave1,slave2</property>
              </poolConfig>
      </dbServer>
[root@localhost /]# /usr/local/amoeba/bin/amoeba start&             # 启动服务
client客户端：
[root@localhost /]# yum -y install mysql
[root@localhost /]# mysql -u amoeba -p123.com -h 192.168.1.2 -P8066     # 登录访问
验证效果!
主服务器中在test库中创建一个表zzc，同步到各个从服务器上，并且写入一条内容！当前主从服务器均可查询到~~~ 关闭从服务器的slave功能，并且从服务器分别插入表zzc中一条内容查询会出现从服务器中有刚添加的内容而主服务器中没有。 最后使用客户机远程登陆在test库中zzc表里写入一条内容，在关闭slave功能情况下此内容仅客户机与主服务器可以查询重新开启slave功能则从服务器同步成功!这样，就实现了 MySQL 的读写分离，所有写操作全部在 master主服务器上，用来避免数据的不同步，所有读操作都分摊给了从服务器，用来分担服务器的压力。