Mysql数据库主从+Amoeba读写分离

时间:2022-09-20 15:29:35

一、Mysql数据库主从配置

1、概要

首先要准备两台服务器,一台主服务器(Master),另一台从服务器(Slave),然后要保证MasterSlave的版本要相同且Master不能高于Slave的版本,一般稳健的做法都是使其版本相同,因为MySQL不同版本之间的binlog(二进制日志)格式可能会不一样,最后会导致同步出现异常。介绍先到这里,以下我们先确认两台服务器的信息:

 

主服务器(Master)
IP192.168.3.11
系统:Linux(Ubuntu 64v10.10)
MySQL版本:5.7.17
需要同步的数据库名:pod1
用于同步的帐号:hyf1
用于同步的密码:mysql

————————————–

从服务器(Slave)
IP192.168.3.38
系统:windows64
MySQL版本:5.7.18
需要同步的数据库名:pod1

先连接数据库

mysql -u root –p 111111mysql -u 用户名 –p 密码)

 

在主库创建同步用户

1 mysql>create user hyf1; //创建新用户

2 //repl用户必须具有REPLICATION SLAVE权限,除此之外没有必要添加不必要的权限,密码为mysql。说明一下192.168.0.%,这个配置是指明repl用户所在服务器,这里%是通配符,表示192.168.0.0-192.168.0.255的Server都可以以repl用户登陆主服务器。当然你也可以指定固定Ip。

3 mysql> GRANT REPLICATION SLAVE ON *.* TO ' hyf1'@'192.168.3.%' IDENTIFIED BY 'mysql';

mysql>flush privileges; // 使修改立即生效

2、修改Master服务器的配置#开启主从复制,主库的配置

#打开my.cnf配置文件

vi /etc/mysql/my.cnf

#[mysqld]下添加以下内容
#注意,这里的MasterSlaveserver-id不能相同

 

 

server-id   = 11

# server-id给数据库服务的唯一标识 设置值为i,建议使用ip尾号(防止值重复)

log-bin= mysql3306-bin

#指定同步的数据库,如果不指定则同步全部数据库

binlog_do_db  = pod1 #如果需要同时同步多个数据库,请另起一行设置#binlog_ignore_db   = mysql 

 

3)查看日志SQL语句中操作

mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysql3306-bin.000006 |1120 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

需要记录下Position(标红的内容),需要在从库中设置同步起始值。

重启MySQL服务

 

3、配置Slave从服务器(windows

1)找到MySQL安装文件夹修改my.ini文件,在[mysqld]下面增加下面几行代码

 

1 [mysqld]

2 server-id=38 

 

#replication-rewrite-db= dbmaster -> dbslave  #主从数据库名不一致时

#master-connect-retry=60 #预设重试间隔秒
#replicate-do-db=test    #告诉slave只做backup数据库的更新

 

重启MySQL服务

3.1执行SQL

CHANGE MASTER TO

 master_host='192.168.3.11', //Master服务器Ip

 master_user='hyf1',

 master_password='mysql',

 master_port=3306,

 master_log_file='mysql3306-bin.000006',//Master服务器查看的Position

 master_log_pos=1120; //Master服务器查看的Position

3.2#启动slave同步

START SLAVE;

#查看同步状态

3.3SHOWSLAVE STATUS;

 

 

Windows启动mysqlnet start mysql

2)如果发现主从复制失败时,可以先关闭从数据库服务器,然后删除从数据库服务器中data目录下relay-log.info,hosname-relay-bin*,master.info等文件,重启从服务器。

相关命令:

linux查看主从服务器的状态

mysql> show processlist;

 

1.Linux下查看mysqlapache是否安装,并卸载。

===================指令 ps -ef|grep mysql得出结果

 

root     17659     1  0  2011 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid   

mysql    17719 17659  0  2011 ?        03:14:57 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock  

usr/bin/mysql 是指:mysql的运行路径

var/lib/mysql 是指:mysql数据库文件的存放路径

usr/lib/mysql 是指:mysql的安装路径

 

2. ===========#重启服务器

/etc/init.d/mysql restart

2.1,mysql 的守护进程是mysqld

如果已经安装则:

[root@localhost ~]# service mysqld start

=================启动 MySQL:  [确定]        

如果没有安装则:

[root@localhost ~]# service mysqld start

mysqld:未被识别的服务

 

3.查找已安装的myslq 版本:

#rpm  -qa | grep  mysql (注意大小写,如果mysql不行就换MySQL

 

显示:

[root@localhost ~]# rpm  -qa | grep  mysql

mysql-5.0.77-4.el5_4.2

mysql-server-5.0.77-4.el5_4.2

php-mysql-5.2.13-1.el5.art

复制代码

在屏幕上将显示已安装的mysql包名如:mysql-5.0.77-4.el5_4.2 ;

 

系统默认会查找/usr/bin下的命令,如果这个命令不在这个目录下,当然会找不到命令,我们需要做的就是映射一个链接到/usr/bin目录下,相当于建立一个链接文件。

# ln -s  /usr/local/mysql/bin/mysql  /usr/bin

 

一、启动方式

1、使用 service启动:service mysqld start
2、使用 mysqld 脚本启动:/etc/inint.d/mysqld start
3、使用 safe_mysqld启动:safe_mysqld&

二、停止

1、使用 service启动:service mysqld stop
2、使用 mysqld 脚本启动:/etc/inint.d/mysqld stop
3mysqladmin shutdown

三、重启
1、使用 service启动:service mysqld restart
2、使用 mysqld 脚本启动:/etc/inint.d/mysqld restart

二、amoeba的安装配置使用

Amoeba框架是居于JDK1.5开发的,采用了JDK1.5的特性,所以还需要安装java环境jdk,建议使用javaSE1.5以上的JDK版本

网址下载amoeba:http://sourceforge.NET/projects/amoeba/files/

我这里下载的是amoeba-mysql-3.0.5-RC-distribution.zip。直接解压即可使用

使用读写分离功能,只需配置两个文件即可,分别是dbServers.xmlamoeba.xml,如果需要配置ip访问控制,还需要修改access_list.conf文件

1. dbServers.xml文件配置

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

 

<!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">

<amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/">

 

<!--

Each dbServer needs to be configured into a Pool,

If you need to configure multiple dbServer with load balancing that can be simplified by the following configuration:

 add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with name factoryConfig

 such as 'multiPool' dbServer   

-->

<dbServer name="abstractServer" abstractive="true">

<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">

<property name="connectionManager">${defaultManager}</property>

<property name="sendBufferSize">64</property>

<property name="receiveBufferSize">128</property>

<!-- mysql port 代理连接数据库所使用的端口号-->

<property name="port">3306</property>

<!-- mysql schema 代理连接数据库所使用的数据库-->

<property name="schema">pod1</property>

<!—以下是数据库主从配置同步使用的账号和密码,分别在masterdb和slavedb上为amoedb授权-->

<!-- mysql user 代理连接数据库使用的用户名-->

<property name="user">root</property>

<!--代理连接数据库使用的密码-->

<property name="password">111111</property>

</factoryConfig>

 

<poolConfig class="com.meidusa.toolkit.common.poolable.PoolableObjectPool">

<property name="maxActive">500</property>#最大连接数

<property name="maxIdle">500</property>#最大空闲连接数

<property name="minIdle">1</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="server1"  parent="abstractServer"><!—name可自定义-->

<factoryConfig>

<!-- mysql ip 数据库服务地址-->

<property name="ipAddress">192.168.3.11</property>

</factoryConfig>

</dbServer>

<dbServer name="server2"  parent="abstractServer">

<factoryConfig>

<!-- mysql ip 数据库服务地址-->

<property name="ipAddress">192.168.3.38</property>

</factoryConfig>

</dbServer>

#下面设置定义一个虚拟的dbserver,实际上相当于一个dbserver组,这里将可读的数据库ip统一放到一个组中,将这个组的名字

<dbServer name="group1" virtual="true">

<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">

<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->

<!-- 负载均衡参数 #选择调度算法,1表示复制均衡,2表示权重,3表示HA, 这里选择1-->

<property name="loadbalance">1</property>

<!-- Separated by commas,such as: server1,server2,server1 -->

<!-- 参与该pool负载均衡的poolName列表以逗号分割 -->

<property name="poolNames">server1,server2</property>

</poolConfig>

</dbServer>

</amoeba:dbServers>

2. 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.mysql.server.MySQLService">

<!-- port -->

<property name="port">8066</property>

<!-- bind ipAddress#下面配置监听的接口,如果不设置,默认监听所以的IP -->

<!--

<property name="ipAddress">127.0.0.1</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="authenticateProvider">

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

<!--#提供客户端连接amoeba时需要使用这里设定的账号 (这里的账号密码和amoeba连接后端数据库服务器的密码无关)-->

<property name="user">test</property>

<property name="password">123456</property>

<property name="filter">

<bean class="com.meidusa.toolkit.net.authenticate.server.IPAccessController">

<property name="ipFile">${amoeba.home}/conf/access_list.conf</property>

</bean>

</property>

</bean>

</property>

</service>

<runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">

<!-- proxy server client process thread size -->

<property name="executeThreadSize">128</property>

<!-- per connection cache prepared statement size  -->

<property name="statementCacheSize">500</property>

<!-- default charset -->

<property name="serverCharset">utf8</property>

<!-- query timeout( default: 60 second , TimeUnit:second) -->

<property name="queryTimeout">60</property>

</runtime>

</proxy>

<!--

Each ConnectionManager will start as thread

manager responsible for the Connection IO read , Death Detection

-->

<connectionManagerList>

<connectionManager name="defaultManager" class="com.meidusa.toolkit.net.MultiConnectionManagerWrapper">

<property name="subManagerClassName">com.meidusa.toolkit.net.AuthingableConnectionManager</property>

</connectionManager>

</connectionManagerList>

<!-- 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">server1</property> #设置amoeba默认的池,这里设置为server1

 

<property name="writePool">server1</property> #这两个选项默认是注销掉的,需要取消注释,这里用来指定前面定义好的俩个读写池

 

<property name="readPool">server2</property>#

<property name="needParse">true</property>

</queryRouter>

</amoeba:configuration>

 

 

 

3. 修改jvm.properties文件JVM_OPTIONS参数。

易报错:

Error: Could not create the Java Virtual Machine.

Error: A fatal exception has occurred. Program will exit.

 

由于stack size太小,导致JVM启动失败解决办法修改jvm.properties文件JVM_OPTIONS参数

改成:JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k -XX:PermSize=16m -XX:MaxPermSize=96m"

原为:JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m"

 

 

 

1. 启动amoeba

Windows下:

dos命令打开安装目录运行launcher.bat文件

 cd D:\TheDevelopmentToolInstallationDirectory\amoeba-mysql-3.0.5-RC\bin

 launcher.bat

 

linux下运行launcher文件

# /usr/local/amoeba/bin/launcher

    查看端口

[root@bogon ~]# netstat -unlpt | grep java

tcp        0      0 :::8066                     :::*                        LISTEN      1602/java    

 

2.测试

远程登陆mysql客户端通过指定amoeba配置文件中指定的用户名、密码、和端口以及amoeba服务器ip地址链接mysql数据库

mysql -uroot -h 192.168.3.11 -P8066 –p

mysql -h192.168.3.11 -uroot -p -P8066

 

 

标签:mysql主从

三、mysql同步原理(linux下

1、mysql主库在事务提交时将数据变更作为时间记录到二进制日志(binary log)中;

2、slave IO线程将master的binary log events读写到它的中继(Relay log);

3、slave SQL进程读取Relay log,将重做记录数据到数据库中。

 

二、主从同步配置

2,1、配置环境

    masterIP:192.168.152.128

    mysql version5.7.17

    本地多实例配置  端口为33073308.

    前期已配置多实例安装,本方案中3307为主mysql

2.2、配置my.cnf文件

配置如下:

[mysqld_multi]

mysqld=/usr/local/mysql/bin/mysqld_safe

mysqladmin=/usr/local/mysql/bin/mysqladmin

log=/data/mysql_multi.log

 

[mysqld3307]

basedir = /usr/local/mysql

datadir = /data/3307

port = 3307

server_id = 3307

log-bin=mysql-bin

binlog_format=mixed

socket = /data/3307/mysql.sock

symbolic-links = 0

character_set_server = utf8

pid_file=/data/3307/mysql.pid

log_error=/data/3307/3307.error

read-only=0

#replicate-do-db=test

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

relay_log=mysql-relay-bin

log-slave-updates=on

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

 

[mysqld3308]

basedir = /usr/local/mysql

datadir = /data/3308

port = 3308

server_id = 3308

log-bin=mysql-bin

binlog_format=mixed

socket = /data/3308/mysql.sock

symbolic-links = 0

character_set_server = utf8

pid_file=/data/3307/mysql.pid

log_error=/data/3308/3308.error

read-only=0

#replicate-do-db=test

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

relay_log=mysql-relay-bin

log-slave-updates=on

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

 

2.3、重启多实例

[root@localhost data]# /etc/init.d/mysqld_multi restart

[root@localhost data]# /etc/init.d/mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is running

MySQL server from group: mysqld3308 is running

[root@localhost data]# 

 

2.4、登录3307端口主mysql,并建立账户并授权slave

[root@localhost data]# mysql -u root -h127.0.0.1 -P3307 -plila3307

mysql> GRANT REPLICATION SLAVE ON *.* to ‘slave‘@‘%‘ identified by ‘lilaslave‘;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

mysql> 

查看master状态信息

mysql> show master status

    -> ;

+------------------+----------+--------------+------------------+-------------------+

| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 |      590 |              |                 |                  |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

 

2.5、从服务器配置

mysql> change master to master_host=‘127.0.0.1‘,master_port=3307,master_user=‘slave‘,master_password=‘lilaslave‘,master_log_file=‘mysql-bin.000002‘,master_log_pos=590;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

 

mysql> start slave; #启动slave

Query OK, 0 rows affected (0.00 sec)

 

mysql> show slave status\G    #查看slave的状态信息

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 127.0.0.1

                  Master_User: slave

                  Master_Port: 3307

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000002

          Read_Master_Log_Pos: 590

               Relay_Log_File: mysql-relay-bin.000002

                Relay_Log_Pos: 320

        Relay_Master_Log_File: mysql-bin.000002

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: 

          Replicate_Ignore_DB: mysql,information_schema

           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: 590

              Relay_Log_Space: 527

              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: 3307

                  Master_UUID: 873a805c-f25c-11e6-a012-000c29bccc66

             Master_Info_File: /data/3308/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           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

         Replicate_Rewrite_DB: 

                 Channel_Name: 

           Master_TLS_Version: 

1 row in set (0.00 sec)

 

2.6、主服务器测试,创建新的数据库和表,

mysql> create database ceshi_db;

Query OK, 1 row affected (0.06 sec)

 

mysql> use ceshi_db;

Database changed

mysql> create table home(id int(10) not null,name char(10));

Query OK, 0 rows affected (0.11 sec)

 

mysql> 

 

2.7、查看slave服务器是否有ceshi_db数据库和home表,及表结构是否相同

mysql> show databases;

+--------------------+

| Database          |

+--------------------+

| information_schema |

| ceshi_db          |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

5 rows in set (0.06 sec)

 

mysql> use ceshi_db;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> show tables;

+--------------------+

| Tables_in_ceshi_db |

+--------------------+

| home              |

+--------------------+

1 row in set (0.00 sec)

 

mysql> desc home;

+-------+----------+------+-----+---------+-------+

| Field | Type     | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| id    | int(10) | NO |   | NULL   |     |

| name  | char(10) | YES |    | NULL   |     |

+-------+----------+------+-----+---------+-------+

2 rows in set (0.03 sec)

 

mysql>