MySQL主从复制搭建与读写分离实践教程-汇鑫科服|一站式ICT服务商

在数字化的今天，数据的处理和存储变得尤为重要。本文将带你了解如何配置一个简单的数据库环境，包括设置主从复制和读写分离，以满足高性能的数据处理需求。
首先，我们需要一个数据库服务器，这里我们选择MySQL数据库。我们可以通过以下命令安装MySQL：
```shell yum install -y mariadb mariadb-server ```
安装完成后，我们需要启动MySQL服务，并确保它能够在系统启动时自动启动：
```shell systemctl start mariadb systemctl enable mariadb ```
接下来，我们需要设置MySQL的访问权限。首先，我们为MySQL创建一个密码，然后创建一个具有所有权限的用户，以便远程访问数据库：
```shell mysql_secure_installation ```
现在，我们在MySQL主服务器上创建一个数据库，并添加一些示例数据：
```sql CREATE DATABASE db_test; 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;
INSERT INTO user_info(username, password, realname) VALUES ('10001', '123456', '小明'), ('10002', '123456', '小红'), ('10003', '123456', '小王'), ('10004', '123456', '小张'), ('10005', '123456', '小李'); ```
为了实现数据的备份和恢复，我们需要配置主从复制。首先，在主服务器上启用binlog日志，并创建一个用于复制的用户：
```sql GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%' IDENTIFIED BY '123456'; ```
然后，在从服务器上，我们需要配置MySQL以连接到主服务器，并从主服务器复制数据：
```sql 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; ```
启动从服务器的复制进程，并检查复制状态，确保复制已经成功启动：
```sql START SLAVE; SHOW SLAVE STATUS \G; ```
为了保证数据库的安全性，我们可以通过读写分离来实现。这通常需要使用一个中间件，如MySQL Proxy，来实现对读写请求的转发。
```shell /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf --daemon ```
最后，我们需要在应用服务器上连接到MySQL 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-slave、appserver上登录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 root@192.168.204.202:/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
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-master和mysql-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

首页

ICT产品

ICT服务

一站·全栈·系统化

智能系统

关于我们

MySQL主从复制搭建与读写分离实践教程

【限时免费】一键获取网络规划系统模板+传输架构设计+连通性评估方案

相关文章

热门排行

最新发布

您可添加官方企微

获取服务支持

首页

ICT产品

ICT服务

一站·全栈·系统化

智能系统

关于我们

MySQL主从复制搭建与读写分离实践教程

相关产品推荐

【限时免费】一键获取网络规划系统模板+传输架构设计+连通性评估方案

相关文章

热门排行

最新发布

您可添加官方企微

获取服务支持