mysql配置读写分离无效,mysql主从配置和mysql-proxy读写分离踩过的坑

参考网上的配置，本人亲测，踩到三个大坑，如下为配置笔记：html

如下为VM中实现Mysql主从分离。mysql

mysql root / bin456sql

scp -r root@192.168.0.9:/usr/local/mysql /usr/local/数据库

scp -r root@192.168.0.9:/etc/my.cnf /etc/my.cnfide

/etc/my.cnfoop

set password=password('bin456');ui

./mysqld --initialize --user=root --basedir=/usr/local/mysql --datadir=/usr/local/mysql/datalua

grep -l bind-address ~/*.*spa

grep -r "bind-address" *rest

bind-address = 0.0.0.0

10.开启mysql服务，命令以下：

./support-files/mysql.server start

11.将mysql进程放入系统进程中，命令以下：

cp support-files/mysql.server /etc/init.d/mysqld

12.从新启动mysql服务，命令以下：

service mysqld restart

13.使用随机密码登陆mysql数据库，命令以下：

mysql -u root -p

等待系统提示，输入随机密码，便可登陆

14.进入mysql操做行，为root用户设置新密码(小编设为rootroot)：

alter user'root'@'localhost'identified by'rootroot';

15.设置容许远程链接数据库，命令以下：

update usersetuser.Host='%' whereuser.User='root';

16.刷新权限，命令以下：

flush privileges;

重启命令：/etc/init.d/mysql restart

vi etc/my.cnf

log_bin=mysql-bin //主库修改

server_id =1

主库开通从库同步权限：

grant replication slave on *.* to 'root'@'%' identified by 'bin456';

进入客户端：

[root@MiWiFi-R3-srv bin]# ./mysql -uroot -p

show master status;

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 |      589 |              |                  |                   |

从库中执行一下命令(master_log_file & master_log_pos是从上面主库查询获取)：

mysql> CHANGE MASTER TO MASTER_HOST = '192.168.0.9', MASTER_USER = 'root', MASTER_PASSWORD = 'bin456', MASTER_PORT = 3306,master_log_file=' mysql-bin.000001',master_log_pos= 757;

flush privileges;

start slave;

show slave status \G

报错:Starting MySQL. ERROR! The server quit without updating PID file (/usr/local/mysql/data/hadoopServer.pid:

chown -R mysql:mysql /usr/local/mysql/data

chmod -R 755  /usr/local/mysql/data

./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

在source那边，执行：

flush logs;

show master status;

记下File, Position。

在target端，执行：

CHANGE MASTER TO MASTER_LOG_FILE=' bin-log.000002',MASTER_LOG_POS=154;

stop slave;

start slave;

show slave status \G

一切正常。

如下为最大坑，网上说什么刷新主库日志，试了N次并重启仍是报错，结果下面三句搞定：

Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'解决办法：

stop slave;

reset slave;

start slave;

-------------------------------mysql -proxy 0.8.5/0.8.3

mysql 版本：Server version: 5.7.1，已经实现了主从，如上

如下经过Mysql-proxy实现主从分离，参考：https://www.cnblogs.com/lin3615/p/5684891.html

vi/etc/mysql-proxy.cnf

[mysql-proxy]

user=root

admin-username=root

admin-password=bin456

proxy-address=192.168.0.11:4040 //必定要记得修改成mysql-proxy的IP，这是我踩过的坑

proxy-read-only-backend-addresses=192.168.0.10:3306

proxy-backend-addresses=192.168.0.9:3306

proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua

admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua

log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log

log-level=info

daemon=true

keepalive=true

grant all on *.* to'root'@'192.168.0.11'identified by'bin456';

mysql -uroot-pbin456 -h192.168.0.11--port=4040

启动mysql-proxy

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

netstat -anltp

第二坑：

经过mysql-proxy连上了，可是没有下一步，不像网上说的那样，折腾很久，又不报错，无从下手，后来用mysql客户端验证，原来实现了，以下图：