导航：首页 > 数据库 >

MySQL读写分离amoeba&mysql-proxy

发表于：2024-11-20 作者：千家信息网编辑

千家信息网最后更新 2024年11月20日，----主从同步介绍refencen https://www.cnblogs.com/lin3615/p/5684891.html1. 读写分离方式这里介绍两种方式，一种是用mysql-proxy，一

千家信息网最后更新 2024年11月20日MySQL读写分离amoeba&mysql-proxy

----主从同步介绍

refencen https://www.cnblogs.com/lin3615/p/5684891.html

1. 读写分离方式

这里介绍两种方式，一种是用mysql-proxy，一种用Amoeba

amoeba

优点：直接实现读写分离和负载均衡，不用修改代码，有很灵活的数据解决方案

缺点：自己分配账户，和后端数据库权限管理独立，权限处理不够灵活

mysql-proxy

优点：直接实现读写分离和负载均衡，不用修改代码，master和slave用一样的帐号

缺点：字符集问题，lua语言编程，还只是alpha版本，时间消耗有点高

2. 读写分离，延迟是个大问题

在slave服务器上执行 show slave status 查看同步情况

Master_Log_File:slave中的I/O线程当前正在读取的master服务器二进制式日志文件名.

Read_Master_Log_Pos:在当前的 master服务器二进制日志中，slave中的I/O线程已经读取的位置

Relay_Log_File:SQL线程当前正在读取与执行中继日志文件的名称

Relay_Log_Pos:在当前的中继日志中，SQL线程已读取和执行的位置

Relay_Master_Log_File:由SQL线程执行的包含多数近期事件的master二进制日志文件的名称

Slave_IO_Running:I/O线程是否被启动并成功连接到master

Slave_SQL_Running:SQL线程是否被启动

Seconds_Behind_Master:slave服务器SQL线程和从服务器I/O线程之间的差距，单位为秒计

slave同步延迟情况出现：

1.Seconds_Behind_Master不为0，这个值可能会很大

2.Relay_Master_Log_File和Master_Log_File显示bin-log的编号相差很大，说明bin-log在slave上没有及时同步，所以近期执行的 bin-log和当前I/O线程所读的 bin-log相差很大

3.mysql的 slave数据库目录下存在大量的 mysql-relay-log日志，该日志同步完成之后就会被系统自动删除，存在大量日志，说明主从同步延迟很厉害

3. mysql主从同步延迟原理

mysql主从同步原理

主库针对读写操作，顺序写 binlog，从库单线程去主库读"写操作的binlog",从库取到 binlog在本地原样执行(随机写),来保证主从数据逻辑上一致.

mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生 binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率比较高，下一步问题来了，slave的 slave_sql_running线程将主库的 DDL和DML操作在 slave实施。DML，DDL的IO操作是随即的，不能顺序的，成本高很多，还有可能slave上的其他查询产生 lock，由于 slave_sql_running也是单线程的，所以一个 DDL卡住了，需要执行一段时间，那么所有之后的DDL会等待这个 DDL执行完才会继续执行，这就导致了延迟.由于master可以并发，Slave_sql_running线程却不可以，所以主库执行 DDL需求一段时间，在slave执行相同的DDL时，就产生了延迟.

主从同步延迟产生原因

当主库的TPS并发较高时，产生的DDL数量超过Slave一个 sql线程所能承受的范围，那么延迟就产生了，当然还有就是可能与 slave的大型 query语句产生了锁等待

首要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高

次要原因：读写 binlog带来的性能影响，网络传输延迟

4. 主从同步延迟解决方案

架构方面

1.业务的持久化层的实现采用分库架构，mysql服务可平行扩展分散压力

2.单个库读写分离，一主多从，主写从读，分散压力。

3.服务的基础架构在业务和mysql之间加放 cache层

4.不同业务的mysql放在不同的机器

5.使用比主加更了的硬件设备作slave

反正就是mysql压力变小，延迟自然会变小

硬件方面：

采用好的服务器

5. mysql主从同步加速

1、sync_binlog在slave端设置为0

2、-logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。

3、直接禁用slave端的binlog

4、slave端，如果使用的存储引擎是innodb，innodb_flush_log_at_trx_commit =2

从文件系统本身属性角度优化

master端

修改linux、Unix文件系统中文件的etime属性，由于每当读文件时OS都会将读取操作发生的时间回写到磁盘上，对于读操作频繁的数据库文件来说这是没必要的，只会增加磁盘系统的负担影响I/O性能。可以通过设置文件系统的mount属性，组织操作系统写atime信息，在linux上的操作为：

打开/etc/fstab，加上noatime参数

/dev/sdb1 /data reiserfs noatime 1 2

然后重新mount文件系统

#mount -oremount /data

主库是写，对数据安全性较高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置是需要的

而slave则不需要这么高的数据安全，完全可以将sync_binlog设置为0或者关闭binlog，innodb_flushlog也可以设置为0来提高sql的执行效率

1、sync_binlog=1

MySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。

默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。

如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。

但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。

虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。对于高并发事务的系统来说，"sync_binlog"设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

所以很多MySQL DBA设置的sync_binlog并不是最安全的1，而是2或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。

默认情况下，并不是每次写入时都将binlog与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃，有可能binlog中最后的语句丢失了。要想防止这种情况，你可以使用sync_binlog全局变量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog写入后与硬盘同步。即使sync_binlog设置为1,出现崩溃时，也有可能表内容和binlog内容之间存在不一致性。

2、innodb_flush_log_at_trx_commit （这个很管用）

抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。

日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。

3、ls命令可用来列出文件的 atime、ctime 和 mtime。

atime 文件的access time 在读取文件或者执行文件时更改的

ctime 文件的create time 在写入文件，更改所有者，权限或链接设置时随inode的内容更改而更改

mtime 文件的modified time 在写入文件时随文件内容的更改而更改

ls -lc filename 列出文件的 ctime

ls -lu filename 列出文件的 atime

ls -l filename 列出文件的 mtime

stat filename 列出atime，mtime，ctime

atime不一定在访问文件之后被修改

因为：使用ext3文件系统的时候，如果在mount的时候使用了noatime参数那么就不会更新atime信息。

这三个time stamp都放在 inode 中.如果mtime,atime 修改,inode 就一定会改, 既然 inode 改了,那ctime也就跟着改了.

之所以在 mount option 中使用 noatime, 就是不想file system 做太多的修改, 而改善读取效能

4.进行分库分表处理，这样减少数据量的复制同步操作

一、MySQL主从搭建

1. 主从库定义

主库 192.168.12.56 3306

从库1 192.168.12.56 3307

从库2 192.168.12.55 3306

2. 主库修改参数

====》192.168.12.56

# vi /etc/my.cnf

[mysqld]

server-id = 1

log-bin=mysql-bin

------重启mysql主

# /etc/init.d/mysqld restart

# netstat -nltpd |grep mysql

------检查参数

# ls -lrth /app/mysql/data/|grep mysql-bin

-rw-rw---- 1 mysql mysql 107 Oct 29 22:35 mysql-bin.000001

-rw-rw---- 1 mysql mysql 19 Oct 29 22:35 mysql-bin.index

# mysql -uroot -p111111 -e 'show variables;'|egrep "log_bin|server_id"

log_bin ON

server_id 1

3. 从库修改参数

3.1 从库1修改

====》192.168.12.56

# vi /mysqldata/3307/my3307.cnf

[mysqld]

server-id = 2

# mysqladmin -uroot -p1234567 -S /mysqldata/3307/mysql3307.sock shutdown

# mysqld_safe --defaults-file=/mysqldata/3307/my3307.cnf 2>&1 >/dev/null &

# mysql -uroot -p1234567 -S /mysqldata/3307/mysql3307.sock -e 'show variables like "server%"'

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

| Variable_name | Value |

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

| server_id | 2 |

3.2 从库2修改

====》192.168.12.55

# vi /etc/my.cnf

[mysqld]

server-id = 3

# /etc/init.d/mysqld restart

# netstat -nltpd |grep mysql

# mysql -uroot -p111111 -e 'show variables like "server%"'

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

| Variable_name | Value |

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

| server_id | 3 |

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

4. 主库创建同步帐号rep

# mysql -uroot -p111111

mysql> grant replication slave on *.* to 'rep'@'192.168.12.%' identified by '123456';

mysql> flush privileges;

----replication slave 为mysql同步的必须权限，此处不要授予all

----*.* 表示所有库所有表，也可以指定具体库和表进行复制。shaw_gbk_db.test_tb

----'rep'@'192.168.12.%' rep为同步帐号，192.168.12.%为授权主机网段

mysql> select user,host from mysql.user where user='rep';

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

| user | host |

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

| rep | 192.168.12.% |

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

1 row in set (0.04 sec)

mysql> show grants for rep@'192.168.12.%'\G

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

Grants for rep@192.168.12.%: GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.12.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

5. 从库搭建

5.1 ### 从库1搭建 ###(方式一)

5.1.1 主库导出数据

------主库加锁。加锁后，该窗口不能退出，并且受以下参数影响

mysql> show variables like '%timeout';

| interactive_timeout | 28800 |

| wait_timeout | 28800 |

mysql> flush table with read lock;

注意：mysql 5.1及mysql 5.5版本锁表方式不一样：

5.1版本： flush tables with read lock

5.5版本： flush table with read lock

------查看当前binlog

mysql> show master status;

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

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

| mysql-bin.000001 | 334 | | |

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

------新开窗口导出数据

# mkdir /bak

# mysqldump -uroot -p1111111 -A -B --events|gzip >/bak/mysql_bak_$(date +%F).sql.gz

# ll -lrht /bak/

total 144K

-rw-r--r-- 1 root root 141K Jan 10 07:58 mysql_bak_2018-01-10.sql.gz

------导完数据之后，查看binlog状态是否和之前一致，无误后解锁

mysql> show master status;

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

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

| mysql-bin.000001 | 334 | | |

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

mysql> unlock tables;

-------创建一个数据库，带回从库搭建后，看是否能自动同步过去

mysql> create database shaw_db;

Query OK, 1 row affected (0.02 sec)

mysql> show master status;

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

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

| mysql-bin.000001 | 423 | | |

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

mysql> show processlist;

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

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

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

5.1.2 从库1导入数据

# gzip -d /bak/mysql_bak_2018-01-10.sql.gz

# ls -lrht /bak/

total 516K

-rw-r--r-- 1 root root 516K Jan 10 07:58 mysql_bak_2018-01-10.sql

# mysql -uroot -p1234567 -S /mysqldata/3307/mysql3307.sock

mysql> show slave status\G

Empty set (0.00 sec)

5.1.3 从库1设置change master

# mysql -uroot -p1234567 -S /mysqldata/3307/mysql3307.sock<

change master to

master_host='192.168.12.56',

master_port=3306,

master_user='rep',

master_password='123456',

master_log_file='mysql-bin.000001',

master_log_pos=334;

EOF

====>从库1生成master.info

# ls -lrht /mysqldata/3307/data/

total 29M

drwx------ 2 mysql mysql 4.0K Oct 29 21:45 performance_schema

-rw-rw---- 1 mysql mysql 5.0M Oct 29 21:47 ib_logfile1

-rw-rw---- 1 mysql mysql 18M Oct 29 22:42 ibdata1

-rw-rw---- 1 mysql mysql 5.0M Oct 29 22:43 ib_logfile0

drwx------ 2 mysql root 4.0K Oct 29 23:42 mysql

-rw-rw---- 1 mysql mysql 78 Oct 29 23:45 master.info

-rw-rw---- 1 mysql mysql 107 Oct 29 23:45 mysql3307-relay-bin.000001

-rw-rw---- 1 mysql mysql 29 Oct 29 23:45 mysql3307-relay-bin.index

-rw-r----- 1 mysql root 6.0K Oct 29 23:45 xuan2.err

-rw-rw---- 1 mysql mysql 52 Oct 29 23:45 relay-log.info

# cat master.info

mysql-bin.000001

334

192.168.12.56

rep

123456

3306

5.1.4 从库1启动从库进程

mysql> start slave;

mysql> show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0 落后主库的秒数

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.12.56

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 423

Relay_Log_File: orcl-relay-bin.000002

Relay_Log_Pos: 342

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: 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: 423

Relay_Log_Space: 497

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)

mysql> show processlist;

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

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

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

3 rows in set (0.00 sec)

----检查，发现之前创建的数据

mysql> show databases like 'shaw_db';

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

| Database (shaw_db) |

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

| shaw_db |

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

1 row in set (0.00 sec)

5.1.5 再次测试同步情况

----主库

mysql> use shaw_db;

Database changed

mysql> create table t_zhong as select * from mysql.user;

Query OK, 3 rows affected (0.02 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> show master status;

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

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

| mysql-bin.000001 | 537 | | |

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

1 row in set (0.00 sec)

----从库1

mysql> select count(*) from shaw_db.t_zhong;

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

| count(*) |

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

| 3 |

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

1 row in set (0.00 sec)

--5.1.6 主从库产生的日志文件信息

---- 主库开启binlog后产生binlog日志

# ls -lrt |grep bin

-rw-rw---- 1 mysql mysql 33 Jan 10 07:13 mysql-bin.index

-rw-rw---- 1 mysql mysql 827 Jan 10 10:57 mysql-bin.000001

# cat mysql-bin.index

/mysqldata/3309/mysql-bin.000001

# mysqlbinlog mysql-bin.000001|more

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#180110 7:13:05 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.32-log created 1801

10 7:13:05 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

BINLOG '

。。。。。。。。。。。。。。。。。。

---- 从库开启未开启binlog，但是有relaylog，并且有master.info记录信息

# ls -lrt |grep relay

-rw-rw---- 1 mysql mysql 155 Jan 10 08:20 orcl-relay-bin.000001

-rw-rw---- 1 mysql mysql 48 Jan 10 08:20 orcl-relay-bin.index

-rw-rw---- 1 mysql mysql 746 Jan 10 10:57 orcl-relay-bin.000002

-rw-rw---- 1 mysql mysql 49 Jan 10 10:57 relay-log.info

# cat orcl-relay-bin.index ##relaylog索引文件

./orcl-relay-bin.000001

./orcl-relay-bin.000002

# cat relay-log.info relaylog是SQL线程

./orcl-relay-bin.000002

746 ## Relay_Log_Pos: 746表示从库sql应用日志的relaylog位置。

mysql-bin.000001 ##这个表示从库从主库取数据回来的binlog位置

827 ##Exec_Master_Log_Pos: 827 这个是从库从主库取数据回来的binlog日志中pos位置

# mysqlbinlog orcl-relay-bin.000002 |more

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#180110 8:20:25 server id 2 end_log_pos 107 Start: binlog v 4, server v 5.5.32 created 180110

8:20:25

。。。。。。。。。

# cat master.info ##master.info是IO线程

18 ##

mysql-bin.000001 ##主库binlog位置

827 ##主库pos位置

192.168.12.55 ##主库地址

rep ##主库连接帐号

123456 ##主库连接密码

3309 ##主库端口号

60 ##表示主从出现问题后，从库重试时间

1800.000

5.2 ### 从库2搭建 ###(方式二) 推荐

5.2.1 主库导出数据

====》192.168.12.56

---- 导出时加入master-data=1

主库导出备份加参数 master-data=1 后。导入从库后，change master to时不需要加以下参数，如果是master-data=2 则需要加以下参数

master_log_file='mysql-bin.000001',

master_log_pos=334;

## 导出数据

# mysqldump -uroot -p111111 -A -B -F --master-data=1 --events --single-transaction|gzip > /bak/mysqld_$(date +%F).sql.gz

===============================================================

## 参数说明：

-B 指定多个库，增加建库及use语句

--compact 去掉注释，适合调试输出，生产不用

-A 备份所有库

-F 刷新binlog日志

--master-data 增加binlog日志文件名及对应的位置点

-x, --lock-all-tables

Locks all tables across all databases.This is archieved by taking a global read lock for the duration of the whole dump. Automatically turns -single-transaction and -lock-tables off

-l, --lock-tables Lock all tables for read

-d 只备份表结构

-t 只备份数据

--single-transaction 适合InnoDB事务数据库备份

InnoDB表在备份时，通常启用选项-single-transaction来保证备份的一致性，实际上它的工作原理是设定本次会话的隔离级别为：repeatable read 以确保本次会话dump时不会看到其他会话已经提交的数据。

myisam备份命令

mysqldump -uroot -p111111 -A -B -F -master-data=2 -x -events|gzip > /opt/all.sql.gz

innodb备份命令：推荐

mysqldump -uroot -p111111 -A -B -F -master-data=2 -events -single-transaction |gzip >opt/all.sql.gz

===============================================================

5.2.2 从库2导入数据

====》192.168.12.55

# scp /bak/mysqld_2016-10-30.sql.gz root@192.168.12.55:/root

# gunzip mysqld_2016-10-30.sql.gz

# ls -lrht |grep mysqld

-rw-r--r-- 1 root root 520K Feb 27 14:12 mysqld_2016-10-30.sql

# mysql -uroot -p111111

mysql> show slave status\G

Empty set (0.00 sec)

5.2.3 从库2设置change master

# mysql -uroot -p1111111 <

change master to

master_host='192.168.12.56',

master_port=3306,

master_user='rep',

master_password='123456';

EOF

mysql> show slave status\G

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

Slave_IO_State:

Master_Host: 192.168.12.55

Master_User: rep

Master_Port: 3309

Connect_Retry: 60

Master_Log_File:

Read_Master_Log_Pos: 4

Relay_Log_File: mysql3308-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File:

Slave_IO_Running: No

Slave_SQL_Running: No

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

Relay_Log_Space: 107

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

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

1 row in set (0.00 sec)

5.2.4 从库2启动从库进程

mysql> start slave;

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.12.55

Master_User: rep

Master_Port: 3309

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 107

Relay_Log_File: mysql3308-relay-bin.000002

Relay_Log_Pos: 480

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1007

Last_Error: Error 'Can't create database 'shaw_db'; database exists' on query. Default database: 'shaw_db'. Query: 'create database shaw_db'

Skip_Counter: 0

Exec_Master_Log_Pos: 334

Relay_Log_Space: 1642

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

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 1007

Last_SQL_Error: Error 'Can't create database 'shaw_db'; database exists' on query. Default database: 'shaw_db'. Query: 'create database shaw_db'

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

1 row in set (0.00 sec)

报错原因： 在从库先创建个对象，数据库、表….然后在主库创建一个同名的对象就会出现以上报错

5.2.5 处理同步故障

----解决方法两种，一种如下：

stop slave;

set global sql_slave_skip_counter = 1;

start slave

----另一种，如下：需要重启mysql

根据错误号跳过指定的错误。

slave-skip-errors = 1032,1062,1007

1032：记录不存在

1062：字段值重复，入库失败

1007：数据库已存在，创建数据库失败

1050：数据表已存在

一般由于入库重复导致的失败，可以忽略。也可以使用all值忽略所有错误消息如下，但不建议。slave-skip-errors = all

===处理之后：

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.12.56

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 107

Relay_Log_File: orcl-relay-bin.000007

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: 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: 107

Relay_Log_Space: 554

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)

5.2.6 测试同步

---- 主库操作

mysql> use shaw_db;

Database changed

mysql> create table t_user as select * from mysql.user;

---- 从库1检查

mysql> show slave status\G

mysql> select count(*) from shaw_db.t_user;

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

| count(*) |

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

| 3 |

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

1 row in set (0.01 sec)

---- 从库2检查

mysql> show slave status\G

mysql> select count(*) from shaw_db.t_user;

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

| count(*) |

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

| 3 |

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

1 row in set (0.01 sec)

5.3 创建用户用于应用连接数据库

mysql> grant all privileges on *.* to user01@'192.168.12.%' identified by "111111";

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for user01@'192.168.12.%'\G

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

Grants for user01@192.168.12.%: GRANT ALL PRIVILEGES ON *.* TO 'user01'@'192.168.12.%' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA'

1 row in set (0.00 sec)

###### 注意：

========》在登录的时候，如果是本机登录，默认的认证方式是local，因此默认登录会报错，需要指定ip登录。如下：

# mysql -uuser01 -p111111

ERROR 1045 (28000): Access denied for user 'user01'@'localhost' (using password: YES)

# mysql -uuser01 -p111111 -h 192.168.12.56 -P3306

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 20

Server version: 5.5.32-log Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

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

mysql> exit

Bye

=====》这里处理方法是再创建local的用户

mysql> grant all privileges on *.* to user01@'localhost' identified by "111111";

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

# mysql -uuser01 -p111111

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 23

Server version: 5.5.32-log Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

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

mysql>

mysql> select user,host from mysql.user;

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

| user | host |

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

| root | 127.0.0.1 |

| rep | 192.168.12.% |

| user01 | 192.168.12.% |

| root | localhost |

| user01 | localhost |

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

5 rows in set (0.00 sec)

5.4 分别在主库、从库插入不同数据

----目的是后面读写分离时方便识别连接的库

----主库上操作192.168.12.56 3306

# mysql -uroot -p111111

mysql> use shaw_db;

mysql> create table t_usedb (id int,name varchar(20));

mysql> insert into t_usedb values(1,'master');

----从库1操作 192.168.12.55 3306

# mysql -uroot -p111111

mysql> use shaw_db;

mysql> select * from t_usedb;

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

| id | name |

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

| 1 | master |

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

mysql> insert into t_usedb values(2,'slave1');

----从库2操作 192.168.12.56 3307

# mysql -uroot -p111111 -S /mysqldata/3307/mysql3307.sock

mysql> select * from shaw_db.t_usedb;

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

| id | name |

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

| 1 | master |

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

mysql> insert into shaw_db.t_usedb values(3,'slave2');

二、基于Amoeba的MySQL读写分离

refencen

http://blog.chinaunix.net/uid-20639775-id-154600.html

https://www.cnblogs.com/liuyisai/p/6009379.html

https://www.cnblogs.com/xyp-blog123/p/6684118.html

1. Amoeba介绍

Amoeba的中文意思是阿米巴、变型虫

目前要实现mysql的主从读写分离，主要有以下几种方案：

a. 通过程序实现，网上很多现成的代码，比较复杂，如果添加从服务器要更改多台服务器的代码。

b. 通过mysql-proxy来实现，由于mysql-proxy的主从读写分离是通过lua脚本来实现，目前lua的脚本的开发跟不上节奏，而又没有完美的现成的脚本，因此导致用于生产环境的话风险比较大，据网上很多人说mysql-proxy的性能不高。

c. 自己开发接口实现，这种方案门槛高，开发成本高，不是一般的小公司能承担得起。

d. 利用阿里巴巴的开源项目Amoeba来实现，具有负载均衡、高可用性、sql过滤、读写分离、可路由相关的query到目标数据库，并且安装配置非常简单

Amoeba(变形虫)项目，专注分布式数据库 proxy 开发。在Client、DB Server(s)之间。对客户端透明。具有负载均衡、高可用性、sql过滤、读写分离、可路由相关的query到目标数据库、可并发请求多台数据库合并结果。

主要解决：

? 降低数据切分带来的复杂多数据库结构

? 提供切分规则并降低数据切分规则给应用带来的影响

? 降低db 与客户端的连接数

? 读写分离

2. 部署环境前介绍

主库 192.168.12.56 3306

从库1 192.168.12.56 3307

从库2 192.168.12.55 3306

Amoeba 192.168.12.55 8066

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

3. 安装Java环境

官方下载地址：

http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

http://download.oracle.com/otn-pub/java/jdk/8u161-b12/2f38c3b165be4555a1fa6e98c45e0808/jdk-8u161-linux-x64.rpm?AuthParam=1519720220_d473abf93bf78651f1ec927514473d86

二进制安装方法：

这里采用rpm包安装：

========》192.168.12.55

# rpm -ivh jdk-8u161-linux-x64.rpm

Preparing... ########################################### [100%]

1:jdk1.8 ########################################### [100%]

Unpacking JAR files...

tools.jar...

plugin.jar...

javaws.jar...

deploy.jar...

rt.jar...

jsse.jar...

charsets.jar...

localedata.jar...

# rpm -qa|grep jdk1.8

jdk1.8-1.8.0_161-fcs.x86_64

# rpm -ql jdk1.8-1.8.0_161-fcs.x86_64

/usr/java/jdk1.8.0_161/…….

## 配置java环境变量

# vim /etc/profile

#set java environment

export JAVA_HOME=/usr/java/jdk1.8.0_161

export JRE_HOME=$JAVA_HOME/jre

export CLASSPATH=.:$JAVA_HOME/lib:$JRE_HOME/lib:$CLASSPATH

export PATH=$JAVA_HOME/bin:$JRE_HOME/bin:$PATH

# source /etc/profile

# java -version

java version "1.8.0_161"

Java(TM) SE Runtime Environment (build 1.8.0_161-b12)

Java HotSpot(TM) 64-Bit Server VM (build 25.161-b12, mixed mode)

4. 安装Amoeba环境

官方下载地址：

https://sourceforge.net/projects/amoeba/

https://nchc.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/3.x/amoeba-mysql-3.0.5-RC-distribution.zip

Amoeba安装非常简单，直接解压即可使用，这里将Amoeba解压到/usr/local/amoeba目录下，这样就安装完成了

========》192.168.12.55

# mkdir /usr/local/amoeba/

# unzip -d /usr/local/amoeba/ amoeba-mysql-3.0.5-RC-distribution.zip

# cd /usr/local/amoeba/

# ls

benchmark bin conf jvm.properties lib

5. 配置Amoeba

5.1 配置文件介绍

Amoeba总共有7个配置文件，分别如下：

#/* Amoeba主配置文件($AMOEBA_HOME/conf/amoeba.xml)，用来配置Amoeba服务的基本参数，如Amoeba主机地址、端口、认证方式、用于连接的用户名、密码、线程数、超时时间、其他配置文件的位置等。

#/* 数据库服务器配置文件($AMOEBA_HOME/conf/dbServers.xml)，用来存储和配置Amoeba所代理的数据库服务器的信息，如:主机IP、端口、用户名、密码等。

#/* 切分规则配置文件($AMOEBA_HOME/conf/rule.xml)，用来配置切分规则。

#/* 数据库函数配置文件($AMOEBA_HOME/conf/functionMap.xml)，用来配置数据库函数的处理方法，Amoeba将使用该配置文件中的方法解析数据库函数。

#/* 切分规则函数配置文件($AMOEBA_HOME/conf/ruleFunctionMap.xml)，用来配置切分规则中使用的用户自定义函数的处理方法。

#/* 访问规则配置文件($AMOEBA_HOME/conf/access_list.conf)，用来授权或禁止某些服务器IP访问Amoeba。

#/* 日志规格配置文件($AMOEBA_HOME/conf/log4j.xml)，用来配置Amoeba输出日志的级别和方式。

5.2 配置脚本介绍

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

#### 注意点：

脚本中用 ""表示注释，如下面的这段，其实是注释了的

下面首先介绍dbServers.xml

[root@bogon amoeba]# cat conf/dbServers.xml

${defaultManager}

128

3306　　#设置Amoeba要连接的mysql数据库的端口，默认是3306

testdb　　#设置缺省的数据库，当连接amoeba时，操作表必须显式的指定数据库名，即采用dbname.tablename的方式，不支持 use dbname指定缺省库，因为操作会调度到各个后端dbserver

test1　　#设置amoeba连接后端数据库服务器的账号和密码，因此需要在所有后端数据库上创建该用户，并授权amoeba服务器可连接

111111

500　　#最大连接数，默认500

500　　　　#最大空闲连接数

1　　　　#最新空闲连接数

600000

true

　　#设置一个后端可写的dbServer，这里定义为writedb，这个名字可以任意命名，后面还会用到

192.168.2.204 #设置后端可写dbserver

　　#设置后端可读dbserver

192.168.2.205

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

1　　#选择调度算法，1表示复制均衡，2表示权重，3表示HA，这里选择1

slave　　#myslave组成员

另一个配置文件amoeba.xml

[root@bogon amoeba]# cat conf/amoeba.xml

8066　　　　#设置amoeba监听的端口，默认是8066

　　　　#下面配置监听的接口，如果不设置，默认监听所以的IP

128

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

root　　　　

123456

${amoeba.home}/conf/access_list.conf

128

500

utf8

com.meidusa.toolkit.net.AuthingableConnectionManager

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

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

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

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

1500

writedb　　#设置amoeba默认的池，这里设置为writedb

writedb　　#这两个选项默认是注销掉的，需要取消注释，这里用来指定前面定义好的俩个读写池

myslave　　 #

true

5.3 配置脚本dbServers.xml

首先注意到这个是一个抽象的父服务,用来给实际服务的继承使用. 每一个server都要有独立的后端mysql连接端口,数据库名称,帐号,密码, 连接池大小等.写在抽象父类中,方便统一管理. 如果不用继承的方式,也可以复制到每一个具体server中.

再创建两个实际的server.都继承了相同的父类,唯一不同的是他们的IP

再配置一个虚拟的server,相当于自由组合实际server为一个server,这里命名为multiPool,名称可以自定义.

# cd /usr/local/amoeba/conf/

# vi dbServers.xml

${defaultManager}

128

3306

shaw_db

user01

111111

500

600000

true

192.168.12.56

192.168.12.55

slave1

5.4 配置脚本amoeba.xml

配置amoeba的连接端口,以及amoeba的登录帐号密码,与dbServers.xml中的帐号密码不同,dbServers.xml是配置后端mysql的帐号密码

设置默认数据库为master,以及读和写策略,这里读策略是从虚拟服务中轮询, 当对一条sql语句解析不出的时候,无法正确路由到writePool或readPool时,就会路由到defaultPool.所以defaultPool一般配置为主节点.

# cd /usr/local/amoeba/conf

# vi amoeba.xml

8066

192.168.12.55

128

amobeba

123456

${amoeba.home}/conf/access_list.conf

128

500

utf8

com.meidusa.toolkit.net.AuthingableConnectionManager

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

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

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

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

1500

master

myslave

true

5.5 启动Amoeba服务

5.5.1 启动服务

# /usr/local/amoeba/bin/launcher

Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=16m; support was removed in 8.0

Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m; support was removed in 8.0

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

Error: Could not create the Java Virtual Machine.

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

5.5.2 处理报错

从错误文字上看，应该是由于stack size太小，导致JVM启动失败，要如何修改呢？

其实Amoeba已经考虑到这个问题，并将JVM参数配置写在属性文件里。现在，让我们通过该属性文件修改JVM参数。

修改jvm.properties文件JVM_OPTIONS参数。

# vim /usr/local/amoeba/jvm.properties

改成：

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

原为：

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

# /usr/local/amoeba/bin/launcher

2018-03-06 14:17:12,281 INFO net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066.

2018-03-06 15:19:46 [INFO] Project Name=Amoeba-MySQL, PID=34261 , System shutdown ....

Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=16m; support was removed in 8.0

Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m; support was removed in 8.0

2018-03-06 16:12:06 [INFO] Project Name=Amoeba-MySQL, PID=34592 , starting...

log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml

2018-03-06 16:12:06,852 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETA

log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf

2018-03-06 16:12:07,142 INFO net.ServerableConnectionManager - Server listening on /192.168.12.55:8066.

2018-03-06 16:22:06 [INFO] Project Name=Amoeba-MySQL, PID=34592 , System shutdown ....

Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=16m; support was removed in 8.0

Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m; support was removed in 8.0

2018-03-06 16:22:54 [INFO] Project Name=Amoeba-MySQL, PID=34684 , starting...

log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml

2018-03-06 16:22:54,571 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETA

log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf

2018-03-06 16:22:54,855 INFO net.ServerableConnectionManager - Server listening on /192.168.12.55:8066.

5.5.3 检查服务端口

# netstat -tlnp |grep java

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

5.5.4 停止服务

# /usr/local/amoeba/bin/shutdown

kill -15 34592

6. 测试Amoeba主从读写

## 注意看登录的提示amoeba

# mysql -uamobeba -p123456 -h 192.168.12.55 -P8066

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2012979869

Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

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

mysql>

## 我们之前在从库1上插入了一条slave1的数据，这里可以确定连接的是从库1

mysql> select * from t_usedb;

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

| id | name |

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

| 1 | master |

| 2 | slave1 |

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

## 插入一条数据

mysql> insert into t_usedb values (55,'amoeba');

Query OK, 1 row affected (0.10 sec)

mysql> select * from t_usedb;

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

| id | name |

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

| 1 | master |

| 2 | slave1 |

| 55 | amoeba |

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

### 此时我们登录到主库查看是否有新插入数据

# mysql -uroot -p111111

mysql> select * from shaw_db.t_usedb;

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

| id | name |

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

| 1 | master |

| 55 | amoeba |

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

### 此时我们登录到从库2查看是否有新插入数据

# mysql -uroot -p111111 -S /mysqldata/3307/mysql3307.sock

mysql> select * from shaw_db.t_usedb;

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

| id | name |

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

| 1 | master |

| 3 | slave2 |

| 55 | amoeba |

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

##### 我们把主库stop，然后再插入数据，看是否报错

# /etc/init.d/mysqld stop

# mysql -uamobeba -p123456 -h 192.168.12.55 -P8066

mysql> insert into t_usedb values (100,'amoeba222');

ERROR 1044 (42000): Amoeba could not connect to MySQL server[192.168.12.56:3306],Connection refused

mysql> select * from t_usedb;

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

| id | name |

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

| 1 | master |

| 2 | slave1 |

| 55 | amoeba |

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

3 rows in set (0.00 sec)

7. 添加从库2到amoeba

7.1 配置脚本dbServers.xml

### 此时只需要配置脚本dbServers.xml

# cd /usr/local/amoeba/conf

# /usr/local/amoeba/bin/shutdown

# vi dbServers.xml

${defaultManager}

128

3306

shaw_db

user01

111111

500

600000

true

192.168.12.56

192.168.12.55

192.168.12.56

3307

user01

111111

slave1,slave2

7.2 测试主从

# /usr/local/amoeba/bin/launcher &

[1] 34792

[root@orcl conf]# 2018-03-06 16:12:07,142 INFO net.ServerableConnectionManager - Server listening on /192.168.12.55:8066.