首页 > 数据库 >配置MySQL主从复制和读写分离

配置MySQL主从复制和读写分离

时间:2024-05-16 09:19:26浏览次数:28  
标签:主从复制 slave 读写 MySQL db lua master proxy mysql

实验环境

序号主机名IP地址备注
1 mysql-master 192.168.204.201 MySQL主库
2 mysql-slave 192.168.204.202 MySQL从库
3 appserver 192.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;

create table if not exists user_info(
username varchar(16) not null,
password varchar(32) not null,
realname varchar(16) default '',
primary key (username)
)default charset=utf8;
show tables;

insert into user_info(username, password, realname) values
('10001', '123456', '小明'),
('10002', '123456', '小红'),
('10003', '123456', '小王'),
('10004', '123456', '小张'),
('10005', '123456', '小李');

select * from user_info where 1;

图片

mysql-master上授权数据库访问权限

GRANT all ON db_test.* TO 'admin'@'%' identified BY '123456';
flush privileges;

mysql-slaveappserver上登录mysql-master数据库

mysql -h 192.168.204.201 -uroot -p123456

图片

mysql-master上撤销数据库访问权限

REVOKE all ON 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_binserver_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_FILEMASTER_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
insert into db_test.user_info (username, password, realname) values ('20001', '123456', 'Tom');
select * from db_test.user_info where 1;

图片

-- mysql-slave
select * from db_test.user_info where 1;

图片

已经实现了主从复制

在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
if not 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-mastermysql-slave上授权给mysql-proxy

grant all on *.* 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

标签:主从复制,slave,读写,MySQL,db,lua,master,proxy,mysql
From: https://www.cnblogs.com/testzcy/p/18195286

相关文章

  • gorm实现MySQL的INSERT INTO ... ON DUPLICATE KEY UPDATE差异化插入和更新
    比如插入f_create_uid,更新时忽略f_create_uid,只更新f_update_uid。可使用gorm的BeforeCreate和BeforeUpdate钩子,这两个钩子分别在创建和更新记录之前被调用。//BeforeCreate在创建记录之前调用func(dob*MyStruct)BeforeCreate(tx*gorm.DB)(errerror){dob......
  • mysql中explain命令详解
    前言我们可以使用explain命令来查看SQL语句的执行计划,从而帮助我们优化慢查询。使用注意:使用的mysql版本为8.0.28数据准备CREATETABLE`tb_product2`(`id`bigintNOTNULLAUTO_INCREMENTCOMMENT'商品ID',`name`varchar(20)DEFAULTNULLCOMMENT'商品......
  • Linux Debian12 部署MySql 并建立外部连接
    一.下载MySql下载最新软件包。也可以在命令界面下使用下载最新的发行包。wgethttps://repo.mysql.com/mysql-apt-config_0.8.29-1_all.deb下载完成后,使用命令进行安装dpkg-imysql-apt-config_0.8.29-1_all.deb执行完后会跳转到安装MySql配置界面​TAB进行保存,选中OK,然......
  • mysql表名大小写敏感
    通过搜索了解到mysql在win下大小写不明感,在linux下大小写敏感本欲让ai写个脚本转表名,两次回答的结果都不能用后发现修改mysqld.conf中的lower_case_table_names=1可以忽略大小写但修改过后启动失败官方回答是初始化后不能再修改该参数,如需要修改需要重新初始化为了不折腾,......
  • MySQL存储过程中如何使用ROLLBACK
    在MySQL存储过程中,可以使用ROLLBACK来撤销之前执行的所有未提交的更改。当与BEGIN和COMMIT结合使用时,ROLLBACK可以帮助您管理事务并确保数据的完整性。以下是一个示例存储过程,它使用TRY...CATCH块来捕获异常,并在需要时执行ROLLBACK。请注意,MySQL本身并不直接支持TRY...CATCH错误......
  • Ubuntu20.04安装MySQL8主从
    ......
  • MySQL主从- slave跳过错误
    mysql主从复制,经常会遇到错误而导致slave端复制中断,这个时候一般就需要人工干预,跳过错误才能继续跳过错误有两种方式:一、跳过指定数量的事务mysql>slavestop;mysql>SETGLOBALSQL_SLAVE_SKIP_COUNTER=1#跳过一个事务mysql>slavestart二、修改mysql的配置文件通过sl......
  • MySQL数据库连接超时
    应用日志报错,应用反馈频繁连接超时,导致应用无法正常提供服务。 可能的问题原因如下:数据库timeout参数设置不合理数据库连接串设置有误连接池配置不是最佳实践或者连接池满网络波动防火墙策略异常数据库压力大导致响应缓慢数据库连接达到上限未知原因处理过程:检查......
  • MySQL 给用户添加 ALTER VIEW 的权限
    本文分享自华为云社区《MySQL给用户添加ALTERVIEW的权限》,作者:皮牙子抓饭。MySQL是一个广泛使用的关系型数据库管理系统,用于许多Web应用程序和企业级解决方案中。在MySQL中,用户权限的管理是非常重要的,以确保数据库安全性和数据完整性。在某些情况下,需要为用户添加特定......
  • mysql
    Mysql目录目录Mysqlmysql8.0.12winx64详细安装教程linuxmysql教程在Linux远程连接笔记中JDBC安装、使用JDBC安装JDBC使用三种执行对象:用于将SQL语句发送到数据库中createStatementStatementcreateStatement()创建一个Statement对象来将SQL语句发送到数据库。Statemen......