主从复制

在主从复制的基础上可以完成一主多从
分别修改主与从服务器配置文件
找到主数据库的配置文件my.cnf，在/etc/my.cnf,在[mysqld]部分插入如下两行：

#主服务器
[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id
#从服务器
[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=2 #设置server-id

进入主服务器创建并授权：

MariaDB [(none)]>grant replication slave on *.* to slave@'10.0.0.91' identified by '123456';
MariaDB [(none)]> flush privileges;

查看主服务器master status并记住信息：

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      475 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

从库写入：

MariaDB [(none)]> change master to master_user='slave',
    -> master_password='123456',
    -> master_host='10.0.0.81',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=394;

开启slave

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.81
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 394
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes #此处为yes表示成功
            Slave_SQL_Running: Yes #此处为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: 394
              Relay_Log_Space: 825
              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
1 row in set (0.00 sec)

ERROR: No query specified

测试:
在主服务器内创建库，可以看到从库也已经创建了

主主复制

为了分摊写压力，开启主主复制
在原主从基础上做主主
分别修改主从服务器配置文件

#主服务器内
[mysqld]
log-bin=mysql-bin
server-id=1
replicate-do-db=test #提高效率将不必要的资源不写入二进制文件
bilog-ignore-db=mysql  #提高效率将不必要的资源不写入二进制文件
bilog-ignore-db=information_schema #提高效率将不必要的资源不写入二进制文件
auto-increment-increment=2 #防止主键冲突
auto-increment-offset=1 #防止主键冲突
#从服务器内
[mysqld]
log-bin=mysql-bin
server-id=2
replicate-do-db=test #提高效率将不必要的资源不写入二进制文件
bilog-ignore-db=mysql  #提高效率将不必要的资源不写入二进制文件
bilog-ignore-db=information_schema #提高效率将不必要的资源不写入二进制文件
auto-increment-increment=2 #防止主键冲突
auto-increment-offset=1 #防止主键冲突

授权：
刚刚为了做主从，已经在主服务器内做好了授权，现在在从内也做相同操作：

MariaDB [(none)]>grant replication slave on *.* to slave@'10.0.0.81' identified by '123456';
MariaDB [(none)]> flush privileges;

查看从服务器master status并记住信息：

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      394 |              | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

主库写入：

MariaDB [(none)]> change master to master_user='slave',
    -> master_password='123456',
    -> master_host='10.0.0.81',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=394;

开启slave

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.81
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 394
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes #此处为yes表示成功
            Slave_SQL_Running: Yes #此处为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: 394
              Relay_Log_Space: 825
              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
1 row in set (0.00 sec)

ERROR: No query specified

测试:
此时已完成互为主从配置，可分别在俩台服务器内操作进行验证

多主一从

沿用上面俩台主从服务器，分别关闭slave，升级为俩台主服务器，添加一台准备作为从服务器使用
主服务器开启bin-log日志并做好配置，此处略！

配置从服务器配置文件：

vim /etc/my.cnf

[mysqld_multi]
mysqld=/usr/bin/mysqld_safe
mysqladmin=/usr/bin/mysqladmin
log=/tmp/multi.log


[mysqld01]
port=3306
datadir=/var/lib/mysqla/
pid-file=/var/lib/mysqla/mysqld.pid
socket=/var/lib/mysqla/mysql.sock
user=mysql
server-id=20

[mysqld02]
port=3307
datadir=/var/lib/mysqlb/
pid-file=/var/lib/mysqlb/mysqld.pid
socket=/var/lib/mysqlb/mysql.sock
user=mysql
server-id=20

分别对不同的用户目录进行初始化并授权

[root@mysql1 ~]# mysql_install_db  --datadir=/var/lib/mysqla --user=mysql
[root@mysql2 ~]# mysql_install_db  --datadir=/var/lib/mysqlb --user=mysql
[root@mysql1 ~]# chown -R mysql /var/lib/mysqla/
[root@mysql2 ~]# chown -R mysql /var/lib/mysqlb/

如果出现报错：

[root@mysql ~]# mysql_install_db  --datadir=/var/lib/mysqla --user=mysql
Neither host 'mysql' nor 'localhost' could be looked up with
'/usr/libexec/resolveip'
Please configure the 'hostname' command to return a correct
hostname.
If you want to solve this at a later stage, restart this script
with the --force option

The latest information about mysql_install_db is available at
https://mariadb.com/kb/en/installing-system-tables-mysql_install_db

可尝试使用的解决办法

经测试发现无法实现初始化，原因可能是出现在它是二进制源码包安装，我这采用的是rpm安装，在"resolveip"的路径上面会有很大的出入，所以我通过"which resolveip" 查看路径，将resolveip这个文件复制到/usr/libexec/下
分别启动进程，并查看进程

[root@mysql1 ~]# mysqld_multi --defaults-file=/etc/my.cnf start 05
[root@mysql2 ~]# mysqld_multi --defaults-file=/etc/my.cnf start 06
[root@centos ~]# netstat -tunlp|grep mysqld
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      1864/mysqld         
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      3563/mysqld 

分别登录子进程进行配置

mysql -uroot -P3306 -S /var/lib/mysqla/mysql.sock


MariaDB [(none)]> change master to master_user='slave',
    -> master_password='123456',
    -> master_host='10.0.0.81', #其中一台主服务器IP
    -> master_log_file='mysql-bin.000004',
    -> master_log_pos=394;


mysql -uroot -P3307 -S /var/lib/mysqla/mysql.sock

MariaDB [(none)]> change master to master_user='slave',
    -> master_password='123456',
    -> master_host='10.0.0.91', #其中一台主服务器IP
    -> master_log_file='mysql-bin.000004',
    -> master_log_pos=622;

读写分离

三台机器的IP地址

主机名	IP	身份
mysql	10.0.0.51	调度服务器
mysql1	10.0.0.81	主服务器
mysql2	10.0.0.91	从服务器

先配置好主从服务器
安装好ameoba
yum install -y gcc*
1、安装java环境

jdk下载地址：
链接：https://pan.baidu.com/s/1D8zMBli0cV8h8MD43YNTlA
提取码：8tem

[root@mysql ~]# rpm -ivh jdk-8u102-linux-x64.rpm 
Preparing...                          ################################# [100%]
Updating / installing...
   1:jdk1.8.0_102-2000:1.8.0_102-fcs  ################################# [100%]
Unpacking JAR files...
    tools.jar...
    plugin.jar...
    javaws.jar...
    deploy.jar...
    rt.jar...
    jsse.jar...
    charsets.jar...
    localedata.jar...

[root@mysql ~]# vim /etc/profile
末行插入如下内容：
#set java environment
JAVA_HOME=/usr/java/jdk1.8.0_102
JRE_HOME=/usr/java/jdk1.8.0_102/jre
CLASS_PATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JRE_HOME/lib
PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin
export JAVA_HOME JRE_HOME CLASS_PATH PATH

环境变量添加后记得更新
source /etc/profile
测试是否安装成功

[root@mysql ~]# java -version
java version "1.8.0_102"
Java(TM) SE Runtime Environment (build 1.8.0_102-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.102-b14, mixed mode)

2、安装Amoeba

ameoba下载地址：
链接: https://pan.baidu.com/s/1SrAb676j38ChpIq80YsvEQ 提取码: 7mie
直接解压

[root@mysql ~]# tar xvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
chmod +x bin*
[root@mysql ~]# cd /usr/local/amoeba/
[root@mysql ~]# chmod +x bin*
[root@mysql amoeba]# ll
total 48
drwxr-xr-x 2 root root    63 Apr 14 22:29 benchmark
drwxr-xr-x 2 root root   131 Feb 29  2012 bin
-rw-r--r-- 1 root root  3976 Aug 29  2012 changelogs.txt
drwxr-xr-x 2 root root   243 Apr 14 22:29 conf
drwxr-xr-x 3 root root  4096 Apr 14 22:29 lib
-rw-r--r-- 1 root root 34520 Aug 29  2012 LICENSE.txt
-rw-r--r-- 1 root root  2031 Aug 29  2012 README.html

3、配置Amoeba

配置amoeba本机server,主要修改的有以下内容：
<Server>
IP:
Port:
User:
Password:
</Server>

vim conf/amoeba.xml

<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">
<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">

        <proxy>

                <!-- service class must implements com.meidusa.amoeba.service.Service -->
                <service name="Amoeba for Mysql" class="com.meidusa.amoeba.net.ServerableConnectionManager">
                        <!-- port -->
                        <property name="port">8066</property>  #默认端口8066

                        <!-- bind ipAddress -->
                 
                        <property name="ipAddress">10.0.0.51</property>#修改为ameoba安装的IP
           <property name="manager">${clientConnectioneManager}</property>

                        <property name="connectionFactory">
                                <bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">
                                        <property name="sendBufferSize">128</property>
                                        <property name="receiveBufferSize">64</property>
                                </bean>
                        </property>

                        <property name="authenticator">
                                <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">

                                        <property name="user">ameoba</property>#登录ameoba的用户名

                                        <property name="password">123456</property>#登录ameoba的密码

                                        <property name="filter">
                                                <bean class="com.meidusa.amoeba.server.IPAccessController">
                                                        <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
                                                </bean>
                                        </property>
                                </bean>
                        </property>

                </service>
.....
        <!-- default using file loader -->
        <dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader">
                <property name="configFile">${amoeba.home}/conf/dbServers.xml</property>
        </dbServerLoader>

        <queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
                <property name="ruleLoader">
                        <bean class="com.meidusa.amoeba.route.TableRuleFileLoader">
                                <property name="ruleFile">${amoeba.home}/conf/rule.xml</property>
                                <property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>
                        </bean>
                </property>
                <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
                <property name="LRUMapSize">1500</property>
                <property name="defaultPool">writedb</property>#设置amoeba默认的池，这里设置为writedb

                <property name="writePool">writedb</property>#取消注释后定义好写的DB
                <property name="readPool">slave</property>#取消注释后定义好读的DB
                <property name="needParse">true</property>
        </queryRouter>

Amoeba的配置文件在本环境下位于/usr/local/amoeba/conf目录下。配置文件比较多，但是仅仅使用读写分离功能，只需配置两个文件即可，分别是dbServers.xml和amoeba.xml，如果需要配置ip访问控制，还需要修改access_list.conf文件，下面首先介绍dbServers.xml

主要修改的有以下内容：

<dbServerlist>
<dbServer name=serverN>
<dbserver> x3 #主从服务器的数量
<pool>
serverN #写入你要设置的服务器具体用来写还是读
</pool>
</dbServerlist>

 <dbServer name="abstractServer" abstractive="true">
                <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
                        <property name="manager">${defaultManager}</property>
                        <property name="sendBufferSize">64</property>
                        <property name="receiveBufferSize">128</property>
                        <property name="port">3306</property> #设置使用的端口
                        <property name="schema">testdb</property>  #设置要连接的库名
                        <property name="user">test</property> #设置要连接的账户名
                        <property name="password">123456</property> # 设置连接使用的密码
                </factoryConfig>

<poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
                        <property name="maxActive">500</property> #最大连接数，默认500
                        <property name="maxIdle">500</property> #最大空闲连接数
                        <property name="minIdle">10</property> #最新空闲连接数
                        <property name="minEvictableIdleTimeMillis">600000</property>
                        <property name="timeBetweenEvictionRunsMillis">600000</property>
                        <property name="testOnBorrow">true</property>
                        <property name="testOnReturn">true</property>
                        <property name="testWhileIdle">true</property>
                </poolConfig>
 </dbServer>

        <dbServer name="writedb"  parent="abstractServer">#设置一个后端可写的库，这里定义为writedb
                <factoryConfig>
                        <property name="ipAddress">10.0.0.81</property> #写入准备当写库的IP
                </factoryConfig>
        </dbServer>
 <dbServer name="slave"  parent="abstractServer"> #设置后端可读的库
                <factoryConfig>
                        <property name="ipAddress">10.0.0.91</property>#设置后端可读的库IP
                </factoryConfig>
        </dbServer>
<dbServer name="myslave" 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">writedb,slave</property>#默认读的库为writedb,slave
                </poolConfig>
        </dbServer>

在主服务器上创建数据库testdb

分别在masterdb和slavedb上为amoedb授权

GRANT ALL ON testdb.* TO 'test1'@'10.0.0.51' IDENTIFIED BY '123456';

[root@mysql amoeba]# vim bin/amoeba
[root@mysql amoeba]# pwd
/usr/local/amoeba

默认的128修改为256K

DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k"

可在ameoba上兼职mariadb客户端进行连接测试

[root@mysql amoeba]# mysql -utest1 -p123456 -h 10.0.0.81
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
| testdb |
+--------------------+
3 rows in set (0.00 sec)
可以连接表示没有问题

[root@bogon amoeba]# /usr/local/amoeba/bin/launcher