mysql负载均衡备份_MySQL + Amoeba 负载均衡、主从备份方案

1.  基本环境

4台内网虚拟机的操作系统都是ubuntu-14.04.4 64位

IP为：192.168.169.11、192.168.169.12、192.168.169.13、192.168.169.14

MySQL 4台2读2写，2写互为主从，2组1读1写为主从备份，Amoeaba安装在任一机器上，物理分离更好

2. 安装mysql

#apt-get update

#apt-get install mysql-server (root/admin)

== 已开机自启动

#mysql -V

#mysql -u root -p

#mysql -h 192.168.169.11 -u root -p #远程登录

mysql> show databases;

mysql> show global variables like 'port'; 查看端口号

mysql> show processlist;

== 设置允许远程登录，共2步

== 第一步：

#vim /etc/mysql/my.cnf

== 注释掉下面这行

#bind-address = 127.0.0.1

== 第二步：

mysql> use mysql

mysql> select host,user,password from user;

mysql> update user set host='%' where user='root' and host='localhost';

mysql> flush privileges;

mysql> exit

3. MySQL 主从同步

== 主 192.168.169.11 & 192.168.169.12 (2写)

mysql> grant REPLICATION SLAVE ON *.* TO 'repl'@'192.168.169.%' IDENTIFIED BY 'admin';

== [mysqld]下面增加下面几行代码

#vim /etc/mysql/my.cnf

server-id = 11

log_bin = /var/log/mysql/master-bin

log_bin_index = master-bin.index

log_slave_updates = 1 //很重要，从前一台机器上同步过来的数据才能同步到下一台机器，当有2台写数据库时，需此设置

relay_log = /var/log/mysql/slave-relay-bin.index  //relay_log其实是从的一端设置，因为11/12互为主从，所以也要设置

relay_log_index = slave-relay-bin.index

general_log = 1

general_log_file = /var/log/mysql/mysql.log   //打开普通log，这样负载均衡设置完后，可以看到插入查询发生在具体哪个MySQL上

#reboot

==reboot后，自动开启master

mysql> show master status;

== 同步时，暂时锁住表，解锁

mysql> flush tables with read lock;

mysql> unlock tables;

== 从 192.168.169.13 & 192.168.169.14 (2读)

== [mysqld]下面增加下面几行代码

#vim /etc/mysql/my.cnf

server-id = 13

relay_log = /var/log/mysql/slave-relay-bin.index

relay_log_index = slave-relay-bin.index

general_log = 1

general_log_file = /var/log/mysql/mysql.log

#reboot

== reboot后，自动开启slave

mysql> change master to

master_host='192.168.169.11',      //11、12互为主从，11、13主从，12、14主从，从的一端都要配一下

master_port=3306,

master_user='repl',

master_password='admin',

master_log_file='master-bin.000003', //show master status; 的File值

master_log_pos=0; //show master status; 的Position值

== 启动Slave同步进程

mysql> start slave;

mysql> stop slave;

mysql> show slave status\G

== 若无法连接master

mysql> reset slave;

mysql> change master to ...... //上面的change命令再执行一遍

//检查下吧，插入一条记录到11，看看12/13/14有同步么？  11->12, 11->13, 11>12->14,插入一条到12，也应该一样

4. 安装Amoeba

== 所有mysql都新增用户

mysql> grant all ON *.* TO 'amoeba'@'192.168.169.%' IDENTIFIED BY 'admin';

#tar -zxvf amoeba-mysql-binary-2.2.0.tar.gz –C /usr/local/servers/amoeba-mysql-binary-2.2.0

#vim ./conf/dbServers.xml

${defaultManager}

64

128

3306

ssh

amoeba

admin

192.168.169.11

192.168.169.12

192.168.169.13

192.168.169.14

1

master1,master2   //设成这样master1,master1,master2，表示2:1

1

slave1,slave2

#vim ./conf/amoeba.xml

amoeba_user

admin

${amoeba.home}/conf/rule.xml

${amoeba.home}/conf/functionMap.xml

1500

masters

masters

slaves

true

#/usr/local/servers/amoeba-mysql-binary-2.2.0/bin/amoeba start &

#mysql -u amoeba_user -p -h 192.168.169.14 -P8066

== 启动报错下列错误的话，修改amoeba & amoeba.bat

The stack size specified is too small, Specify at least 228k

Error: Could not create the Java Virtual Machine.

#vim amoeba

#vim amoeba.bat

DEFAULT_OPTS="-server -Xms1024m -Xmx1024m -Xss256k"

5. 应用程序

Java应用程序只要连 jdbc:mysql://192.168.169.14:8066/ssh?useUnicode=true&characterEncoding=utf-8

备注：存在Amoeba单点，需要进一步热备Amoeba，完善方案

其实最简单的方法是Amoeba可以配2个，应用程序端弄2个数据源