配置主服务器
vi /etc/my.cnf
1 2 3 4 5 | log-bin=mysql-bin #将mysql二进制日志取名为mysql-bin binlog_format=mixed #二进制日志的格式,有三种:statement/row/mixed,具体分别不多做解释,这里使用mixed server- id =1 #为服务器设置唯一id |
在主服务器上为从服务器分配一个账号权限
1 | GRANT replication slave ON *.* TO 'slave' @ '%' IDENTIFIED BY '123456' ; |
重启mysql
1 | service mysqld restart |
查看主服务器BIN日志的信息
1 | show master status; |
配置从服务器
进入从服务器,配置从服务器的my.cnf,配置跟主一样,唯一的区别是,server-id,完成后重启
关闭slave
1 | stop slave; |
配置复制主服务信息
1 2 3 4 5 6 | CHANGE MASTER TO MASTER_HOST= '192.168.104.130' , MASTER_USER= 'slave' , MASTER_PASSWORD= '123456' , MASTER_LOG_FILE= 'mysql-bin.000002' , MASTER_LOG_POS=106; |
MASTER_HOST : 设置要连接的主服务器的ip地址
MASTER_USER : 设置要连接的主服务器的用户名
MASTER_PASSWORD : 设置要连接的主服务器的密码
MASTER_LOG_FILE : 设置要连接的主服务器的bin日志的日志名称,即第3步得到的信息
MASTER_LOG_POS : 设置要连接的主服务器的bin日志的记录位置,即第3步得到的信息,(这里注意,最后一项不需要加引号。否则配置失败)
从服务器配置完成,启动从服务器:
1 | start slave; |
查看是否配置成功:
1 | show slave status; |
上面两项均为yes,说明配置成功,到这里MySQL的主从复制就配置完了
中间件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | wget --no-check-certificate https: //downloads .mysql.com /archives/get/file/mysql-proxy-0 .8.5-linux-el6-x86-64bit. tar .gz mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy cd /usr/local/mysql-proxy mkdir lua mkdir logs cp share /doc/mysql-proxy/rw-splitting .lua . /lua cp share /doc/mysql-proxy/admin-sql .lua . /lua vim /etc/mysql-proxy .conf [mysql-proxy] user = root admin-username = root admin-password = 123456 proxy-address = 192.168.104.130:4040 proxy- read -only-backend-addresses = 192.168.104.130 proxy-backend-addresses = 192.168.104.131 proxy-lua-script = /usr/local/mysql-proxy/lua/rw-splitting .lua admin-lus-script = /usr/local/mysql-proxy/lua/admin-sql .lua log- file = /usr/local/mysql-proxy/logs/mysql-proxy .log log-level = debug daemon = true keepalive = true |
user = root 运行代理的用户
admin-username = mysql_proxy_user # mysql-proxy连接后端mysql服务器的用户
admin-password = mysql_proxy_pass # mysql-proxy连接后端mysql服务器的密码
proxy-address = 192.168.0.71:3307 # mysql-proxy监听的IP和端口,端口默认4040
proxy-read-only-backend-addresses = 192.168.0.73 #只读服务
proxy-backend-addresses = 192.168.0.72 # 读写服务器
proxy-lua-script = /usr/local/mysql-proxy/lua/rw-splitting.lua # 指明读写分离配置文件
admin-lus-script = /usr/local/mysql-proxy/lua/admin-sql.lua # 管理脚本路径
log-file = /usr/local/mysql-proxy/logs/mysql-proxy.log # 日志文件路径
log-level = debug #日志记录等级
daemon = true # 程序是否在后端启动
keepalive = true # mysql-proxy崩溃后,尝试重启
修改读写配置文件
1 2 3 4 5 6 7 8 | vim /usr/local/mysql-proxy/lua/rw-splitting .lua if not proxy.global.config.rwsplit then proxy.global.config.rwsplit = { min_idle_connections = 1, #默认超过4个连接数时,才开始读写分离,改为1 max_idle_connections = 1, #默认8,改为1 is_debug = false } |
启动mysql-proxy
1 | /usr/local/mysql-proxy/bin/mysql-proxy --defaults- file = /etc/mysql-proxy .conf |
是否启动
1 | ss -tnl
|
友情提示:垃圾评论一律封号...