实验环境

序号主机名IP地址备注
1mysql-master192.168.204.201MySQL主库
2mysql-slave192.168.204.202MySQL从库
3appserver192.168.204.111应用服务器

安装配置MySQL数据库

1.使用yum安装mysql和mysql-server

yum install -y mariadb mariadb-server

2.启动mysql服务

systemctl start mariadb

systemctl
enable
mariadb

3.查看启动状态

systemctl status mariadb

netstat -anpt | grep
"mysql"
--color

4.允许3306端口通过防火墙

firewall-cmd --zone=public --add-port=3306/tcp --permanent

firewall-cmd --reload


5.设置MySQL密码

mysql_secure_installation

6.在mysql-master上创建数据库

使用root用户登录MySQL
mysql -uroot -p123456

创建数据库并添加数据
create
database db_test;

show
databases;

use db_test;


createtable
if
notexists
user_info(

username
varchar
(
16
)
notnull
,

password
varchar
(
32
)
notnull
,

realname
varchar
(
16
)
default''
,

primary
key (username)

)
default
charset
=
utf8;

show
tables;


insertinto
user_info(username, password, realname)
values
(
'10001'
,
'123456'
,
'小明'
),

(
'10002'
,
'123456'
,
'小红'
),

(
'10003'
,
'123456'
,
'小王'
),

(
'10004'
,
'123456'
,
'小张'
),

(
'10005'
,
'123456'
,
'小李'
);


select*from
user_info
where1
;

在mysql-master上授权数据库访问权限
GRANTallON
db_test.
*TO'admin'
@
'%'
identified
BY'123456'
;

flush privileges;

在mysql-slave、appserver上登录mysql-master数据库
mysql -h 192.168.204.201 -uroot -p123456

在mysql-master上撤销数据库访问权限
REVOKEallON
db_test.
*FROM'admin'
@
'%'
;

flush privileges;

配置master和slave两台mysql服务器的主从复制

1.在master数据库上启用binlog日志，建立从库账号rep

查看binlog日志状态
show
variables
like'log_bin'
;

更改my.cnf配置文件
cp
/usr/share/mysql/my-medium.cnf /etc/my.cnf

重启MySQL，查看binlog日志
systemctl restart mariadb

mysql -uroot -p123456 -e
"show variables like 'log_bin';"
mysql -uroot -p123456 -e
"show master status;"
记住此处File和Position的值
建立从库账号
grant replication slave on *.* to rep@
'192.168.204.202'
identified by
'123456'
;

show grants
for
rep@
'192.168.204.%'
;

2.在master数据库上备份现有数据库

对master数据库锁表
flush tables
with
read lock;

备份master数据库
mysqldump -uroot -p123456 --all-databases | gzip > /root/database_`
date'+%Y-%m-%d'
`.sql.gz

将备份文件拷贝至slave
scp database_*.sql.gz [email protected]:/root

3. 配置slave数据库，在slave上恢复数据库

配置slave数据库server-id，关闭binlog日志
cp
/usr/share/mysql/my-medium.cnf /etc/my.cnf

# vim /etc/my.cnf

#log-bin=mysql-bin
#binlog_format=mixed
server-id = 2

4.重启slave的mysql

重启mysql服务
systemctl restart mariadb

查看log_bin和server_id的值
show
variables
like'log_bin'
;

show
variables
like'server_id'
;

5.将数据恢复至slave

gzip -d /root/database_*.sql.gz

mysql -uroot -p123456 < /root/database_*.sql

mysql -uroot -p123456 -e
"show databases;"

6.在slave数据库上配置复制参数

在slave上配置复制参数
将MASTER_LOG_FILE和MASTER_LOG_POS的值替换成上述master上查询的值
change master
to
MASTER_HOST
='192.168.204.201'
,

MASTER_USER
='rep'
,

MASTER_PASSWORD
='123456'
,

MASTER_LOG_FILE
='mysql-bin.000002'
,

MASTER_LOG_POS
=245
;

在slave上配置启用复制
start
slave;

在slave上查看复制状态
show
slave status \G;

两个均为Yes即可
重启master和slave的mysql服务
systemctl restart mariadb

在master上为数据库db_test增加记录，在slave查看同步情况
-- mysql-master
insertinto
db_test.user_info (username, password, realname)
values
(
'20001'
,
'123456'
,
'Tom'
);

select*from
db_test.user_info
where1
;

-- mysql-slave
select*from
db_test.user_info
where1
;

已经实现了主从复制

在appserver上配置mysql读写分离

1.在appserver上安装mysql-proxy

从https://downloads.mysql.com/archives/proxy/下载mysql-proxy
cd
~

wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-sles11-x86-64bit.tar.gz

tar -xzvf mysql-proxy-0.8.5-linux-sles11-x86-64bit.tar.gz

cp
-r mysql-proxy-0.8.5-linux-sles11-x86-64bit /usr/local/mysql-proxy

2.在appserver上配置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.cnf
#创建配置文件
主配置文件内容
使用前，请去掉注释
#vim /etc/mysql-proxy.cnf


[mysql-proxy]

user=root #运行mysql-proxy用户

admin-username=myproxy #主从mysql共有的用户

admin-password=123456 #用户的密码

proxy-address=127.0.0.1:3306 #mysql-proxy运行ip和端口，不加端口默认4040

proxy-read-only-backend-addresses=192.168.204.202 #指定后端从slave读取数据

proxy-backend-addresses=192.168.204.201 #指定后端master写入数据

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 #定义log日志级别，由高到低分别(error|warning|info|message|debug)

修改权限
chmod
660 /etc/mysql-proxy.cnf

3.在appserver上修改读写分离配置文件

vim /usr/local/mysql-proxy/lua/rw-splitting.lua

修改以下内容
--- config
--
-- connection pool
ifnot
proxy.global.
config
.rwsplit
then
proxy.global.
config
.rwsplit = {

min_idle_connections =
1
,
-- 默认超过4个连接数时才开始读写分离
max_idle_connections =
1
,
-- 默认为8

is_debug =
false
}

end

4.在appserver上启动mysql-proxy

启动
/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf --daemon

查看进程
netstat -anpt | grep 3306

5.在mysql-master和mysql-slave上分别给myproxy授权

在mysql-master和mysql-slave上授权给mysql-proxy
grantallon*
.
*to'myproxy'
@
'192.168.204.%'
identified
by'123456'
;

flush privileges;

6.在appserver上连接mysql-proxy，测试读写分离

在appserver上通过mysql-proxy操作数据库
mysql -h 127.0.0.1 -umyproxy -p123456 -e
"select * from db_test.user_info where 1;"
mysql -h 127.0.0.1 -umyproxy -p123456 -e
"insert into db_test.user_info (username, password, realname) values ('30001', '123456', 'Jack');"
mysql -h 127.0.0.1 -umyproxy -p123456 -e
"select * from db_test.user_info where 1;"
在mysql-master上查询
mysql -uroot -p123456 -e
"select * from db_test.user_info where 1;"
在mysql-master上查询
mysql -uroot -p123456 -e
"select * from db_test.user_info where 1;"
经验证，已实现读写分离
链接：https://www.cnblogs.com/connect/p/mysql-master-slave-copy-and-read-write-separation.html
（版权归原作者所有，侵删）
继续阅读
阅读原文