MYSQL主从和ATLAS读写分离的搭建

82次阅读

没有评论

共计 11649 个字符，预计需要花费 30 分钟才能阅读完成。

本篇内容介绍了“MYSQL 主从和 ATLAS 读写分离的搭建”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

一、环境准备

操作系统 CENTOS 7.5

数据库 MYSQL5.6.40

ATLAS2.2.1

IP 地址

主库 192.168.56.118

备库 192.168.56.119

ATLAS 中间件 192.168.56.117

我这里只写的是普通的读写分离搭建，没有那些 share 分表之类的那么多复杂的，以后有时间了再搞~_~

MYSQL 主从搭建，这个，其实可以直接去看我写的一篇 MYSQL 主从和主主的文章

http://blog.itpub.net/28371090/viewspace-2154828/

已经很详细了，下面再写一遍，只是想要更简洁一些。

MYSQL5.6.40 下载地址(源码包)

https://downloads.mysql.com/archives/get/file/mysql-5.6.40.tar.gz

ATLAS2.2.1 下载地址

https://github.com/Qihoo360/Atlas/releases/tag/2.2.1

两台服务器一样的安装方式

二、MYSQL 源码安装

使用本地 yum 源

[520]
name=520
baseurl=file:///mnt/cdrom
gpgcheck=0
enabled=1

1、安装依赖
yum -y install make gcc-c++ cmake bison-devel ncurses-devel perl
2、创建用户和组
groupadd mysql
useradd mysql -g mysql -M -s /sbin/nologin

3、解压 tar -zxvf mysql-5.6.40.tar.gz

cd /opt/

tar -zxvf mysql-5.6.40.tar.gz -C /opt/

cd /opt/mysql-5.6.40

mkdir /data/mysql5.6.40/

4、编译安装
cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql-5.6.40/ -DMYSQL_DATADIR=/data/mysql5.6.40/ -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci

5、编译

make make install

6、目录授权
chown mysql.mysql /data/mysql5.6.40/
mkdir /data/mysql5.6.40/log
chown mysql.mysql /data/mysql5.6.40/log
7、数据库初始化
cd /opt/mysql-5.6.40/
./scripts/mysql_install_db –user=mysql –datadir=/data/mysql5.6.40/
8、配置环境变量
vi /root/.bash_profile 在最后一行添加
PATH=$PATH:$HOME/bin:/data/mysql5.6.40/bin:/data/mysql5.6.40/lib

三、MYSQL5.6.40 主从搭建

1、Master 端配置部署 a、在主服务器上的 my.cnf 配置文件中的 [mysqld] 节点下添加以下配置

vi /etc/my.cnf

[mysqld]

server-id=101

default-storage-engine=InnoDB

lower_case_table_names=1

log-bin=/data/mysql5.6.40/log/mysql-bin.log

log-bin-index=/data/mysql5.6.40/log/mysql-bin.index

expire_logs_days=30

datadir=/data/mysql5.6.40/

socket=/tmp/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

#log-error=/data/mysql5.6.40/mysqld.log

#pid-file=/data/mysql5.6.40/mysqld.pid

这里有遇到启动不了的情况就注释掉

启动数据库
传统启动方式
/usr/local/mysql/bin/mysqld_safe –user=mysql
制作成服务启动
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
查看启动是否成功
netstat -tnl|grep 3306
ps -ef|grep mysql
相关命令
service mysql start
停止 mysql 服务
service mysql stop
重启 mysql 服务
service mysql restart
添加到开机启动项
chkconfig –add mysql

b、创建用户，并赋予权限：

登陆数据库时报错
ERROR 2002 (HY000): Can t connect to local MySQL server through socket /tmp/mysqld.sock (2)

ln -s /tmp/mysql.sock /tmp/mysqld.sock

登陆
mysql -uroot

create user repl_user;

GRANT REPLICATION SLAVE ON *.* TO repl @ % IDENTIFIED BY PASSWORD ******

设置密码时会遇到报错：

ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number

解决办法：用 select password(你想输入的密码查询出你的密码对应的字符串

select password(123456

查出的是 *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

GRANT REPLICATION SLAVE ON *.* TO repl_user @ % IDENTIFIED BY PASSWORD *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

2、Slave 端配置部署

vi /etc/my.cnf

[mysqld]

server-id=102

default-storage-engine=InnoDB

lower_case_table_names=1

log-bin=/data/mysql5.6.40/log/mysql-bin.log

log-bin-index=/data/mysql5.6.40/log/mysql-bin.index

expire_logs_days=30

datadir=/data/mysql5.6.40/

socket=/tmp/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

#log-error=/data/mysql5.6.40/mysqld.log

#pid-file=/data/mysql5.6.40/mysqld.pid

3、建立主从同步

由于我这里是新搭建的库，直接创建即可，不需要搞什么备份导入

主库：

登录 mysql

show master status;

记住 file 和 position

从库：

登录 mysql

change master to

master_host= 192.168.56.118 ,

master_port=3306,

master_user= repl_user ,

master_password= 123456 ,

master_log_file= mysql-bin.000002 ,

master_log_pos=120;

配置成功后，启动 slave

start slave;

验证主从是否搭建成功在从库执行

show slave status \G;

看到两个 YES 就 OK

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

在主库创建一个表

use test
create table aa (name char(10));
insert into aa values(Tom
在从库查询
use test
select * from aa;
查到刚刚插入的数据就 O 了

mysql  use mysql;

mysql  desc user;

mysql  GRANT ALL PRIVILEGES ON *.* TO root@ %  IDENTIFIED BY  root  // 授权远程连接

mysql  update user set Password = password(123456) where User= root  // 设置 root 用户密码

mysql  select Host,User,Password from user where User= root

mysql  flush privileges;

mysql  exit

四、报错问题处理：

MYSQL 启动报错

（1）

报错现象：

[root@node1 mysql]# service mysql start

Starting MySQL.. ERROR! The server quit without updating PID file (/var/lib/mysql/node1.pid).

查看报错日志：

[root@node1 mysql]# tail /var/log/mysqld.log

190512 19:59:10 InnoDB: Starting an apply batch of log records to the database…

InnoDB: Progress in percents: 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99

InnoDB: Apply batch completed

190512 19:59:10 InnoDB: Waiting for the background threads to start

190512 19:59:11 InnoDB: 5.5.40 started; log sequence number 1595675

190512 19:59:11 [Note] Server hostname (bind-address): 0.0.0.0 port: 3306

190512 19:59:11 [Note] – 0.0.0.0 resolves to 0.0.0.0

190512 19:59:11 [Note] Server socket created on IP: 0.0.0.0 .

190512 19:59:11 [ERROR] Fatal error: Can t open and lock privilege tables: Table mysql.host doesn t exist

190512 19:59:11 mysqld_safe mysqld from pid file /var/lib/mysql/node1.pid ended

解决方法：

查看配置文件，发现配置文件中 datadir 目录是默认的，需要修改成自己设置的 /usr/local/mysql/data/

[root@node1 mysql]# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[root@node1 mysql]# vi /etc/my.cnf

datadir=/usr/local/mysql/data/

修改完成后重新启动 MYSQL 成功

[root@node1 mysql]# service mysql start

Starting MySQL.. SUCCESS!

（2）

报错现象：

[root@node1 mysql]# /usr/local/mysql/bin/mysql -uroot

ERROR 2002 (HY000): Can t connect to local MySQL server through socket /tmp/mysql.sock (2)

查看配置文件发现 socket 文件在 /var/lib/mysql/mysql.sock，而不是在 /tmp/mysql.sock

[root@node1 mysql]# cat /etc/my.cnf

[mysqld]

datadir=/usr/local/mysql/data/

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

解决方法：
设置软链接

ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

或者，修改配置文件

注意一个问题，在生产环境，要注意时区问题

vi /etc/my.cnf

[mysqld]

default-time_zone = +8:00

使用北京时间的时区

注意事项：
mysql 异常宕机情况下，如果未设置 sync_binlog= 1 或者 innodb_flush_log_at_trx_commit= 1 很有可能出现 binlog 或者 relaylog 文件出现损坏，导致主从不一致。

五、ATLAS 读写分离配置

ATLAS2.2.1 下载地址

https://github.com/Qihoo360/Atlas/releases/tag/2.2.1

Atlas-2.2.1.el6.x86_64.rpm

rpm -ivh Atlas-2.2.1.el6.x86_64.rpm

安装完成后，它会默认在”/usr/local/mysql-proxy”目录下生成 4 个文件夹，以及需要配置的文件，如下：

[root@localhost ~]# ll /usr/local/mysql-proxy/
total 4
drwxr-xr-x. 2 root root 75 Jul 30 14:27 bin
drwxr-xr-x. 2 root root 22 Jul 30 16:35 conf
drwxr-xr-x. 3 root root 4096 Jul 30 14:27 lib
drwxr-xr-x. 2 root root 38 Jul 30 16:35 log

bin 目录下放的都是可执行文件

1.“encrypt”是用来生成 MySQL 密码加密的，在配置的时候会用到

2.“mysql-proxy”是 MySQL 自己的读写分离代理

3.“mysql-proxyd”是 360 弄出来的，后面有个“d”，服务的启动、重启、停止。都是用他来执行的

conf 目录下放的是配置文件

1.“test.cnf”只有一个文件，用来配置代理的，可以使用 vim 来编辑

lib 目录下放的是一些包，以及 Atlas 的依赖

log 目录下放的是日志，如报错等错误信息的记录

进入 bin 目录，使用 encrypt 来对数据库的密码进行加密，我的 MySQL 数据的用户名是 buck，密码是 hello，我需要对密码进行加密

[root@localhost bin]# ./encrypt hello
RePBqJ+5gI4=

配置 Atlas，使用 vim 进行编辑

[root@localhost conf]# cd /usr/local/mysql-proxy/conf/
[root@localhost conf]# vim test.cnf

这是用来登录到 Atlas 的管理员的账号与密码，与之对应的是“#Atlas 监听的管理接口 IP 和端口”，也就是说需要设置管理员登录的端口，才能进入管理员界面，默认端口是 2345，也可以指定 IP 登录，指定 IP 后，其他的 IP 无法访问管理员的命令界面。方便测试，我这里没有指定 IP 和端口登录。

# 管理接口的用户名
admin-username = user

# 管理接口的密码
admin-password = pwd

这里的管理接口用户名和密码也用的是默认的

这是用来配置主数据的地址与从数据库的地址，这里配置的主数据库是 118，从数据库是 119

#Atlas 后端连接的 MySQL 主库的 IP 和端口，可设置多项，用逗号分隔
proxy-backend-addresses = 192.168.56.118:3306

#Atlas 后端连接的 MySQL 从库的 IP 和端口，@后面的数字代表权重，用来作负载均衡，若省略则默认为 1，可设置多项，用逗号分隔
proxy-read-

这个是用来配置 MySQL 的账户与密码的，我的 MySQL 的用户是 buck, 密码是 hello, 刚刚使用 Atlas 提供的工具生成了对应的加密密码

# 用户名与其对应的加密过的 MySQL 密码，密码使用 PREFIX/bin 目录下的加密程序 encrypt 加密，下行的 user1 和 user2 为示例，将其替换为你的 MySQL 的用户名和加密密码！pwds = buck:RePBqJ+5gI4=

（RePBqJ+5gI4= 这个就是前面自己加密过的密码，填上去）

这是设置工作接口与管理接口的，如果 ip 设置的”0.0.0.0”就是说任意 IP 都可以访问这个接口，当然也可以指定 IP 和端口，方便测试我这边没有指定，工作接口的用户名密码与 MySQL 的账户对应的，管理员的用户密码与上面配置的管理员的用户密码对应。

#Atlas 监听的工作接口 IP 和端口
proxy-address = 0.0.0.0:1234
#Atlas 监听的管理接口 IP 和端口
admin-address = 0.0.0.0:2345

启动 Atlas

[root@localhost bin]# ./mysql-proxyd test start
OK: MySQL-Proxy of test is started

再 ATLAS 机器上，我是没有安装 MYSQL 的。

/etc/init.d/mysqld status 测试下也没问题，不过对于我来说意义不大，我根本没安装

ps -ef |grep mysql

使用如下命令，进入 Atlas 的管理模式“mysql -h227.0.0.1 -P2345 -uuser -ppwd ”，能进去说明 Atlas 正常运行着呢，因为它会把自己当成一个 MySQL 数据库，所以在不需要数据库环境的情况下，也可以进入到 MySQL 数据库模式。

[root@localhost bin]# mysql -h227.0.0.1 -P2345 -uuser -ppwd
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type help; or \h for help. Type \c to clear the current input statement.

mysql

可以访问“help”表，来看 MySQL 管理员模式都能做些什么。可以使用 SQL 语句来访问

mysql

也可以使用工作接口来访问，使用命令“mysql -h227.0.0.1 -P1234 -ubuck -phello”

[root@localhost bin]# mysql -h227.0.0.1 -P1234 -ubuck -phello
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.81-log
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type  help;  or  \h  for help. Type  \c  to clear the current input statement.
mysql

如果工作接口可以进入了，就可以在 Windows 平台下，使用 Navicat 来连接数据库，填写对应的 host，Port，用户名，密码就可以

4. 读写分离测试

这里测试读写分离需要使用到 Jmeter 了，它是 Java 写第一套开源的压力测试工具，因为这个比较方便。他有专门测试 MySQL 的模块，需要使用 MySQL 的 JDBC 驱动 jar 包，配置很简单，东西很好很强大很好用。

Jmeter 下载地址：

https://mirrors.tuna.tsinghua.edu.cn/apache//jmeter/binaries/apache-jmeter-5.1.1.zip

MySQL 的 JDBC 包：

https://cdn.mysql.com//archives/mysql-connector-java-5.1/mysql-connector-java-5.1.44.zip

JAVA8

https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

下载后，分别都解压开来，打开 Jmeter (在 bin 目录下有个 jmeter.bat，双击它前，你的机器需要先安装 JAVA8，只有这样才可以运行)，在测试计划中，导入 JDBC 的 jar 包

配置 JDBC 的驱动，如下图

名称可以随意填，URL、账密这些按之前设置的来即可，如下图

jdbc:mysql://192.168.56.117:1234/test
hello

配置完成后，进行测试

先创建一个组

添加测试的 JDBC REQUEST

先整一个查询的测试

点击执行之后，去服务器上面的主备库观察数据传输情况

sar -n DEV 1 10000 用这条命令

或者 iostat 也可以

主库：（明显是没有数据传输（几乎没读写））

备库：（明显红色框框看到有数据传输）

由此可见，数据读是从备库读取的。

写入数据也是一样

在主库

create database test;

create table sbtest (name varchar(9));

insert into sbtest values (Tom

insert into sbtest as select * from sbtest;

不停执行以下语句的循环插入

insert into sbtest as select * from sbtest;

有个上百万行，可以开始测试了。

“MYSQL 主从和 ATLAS 读写分离的搭建”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！

正文完

发表至：数据库

2023-07-28

转载说明：除特殊说明外本站除技术相关以外文章皆由网络搜集发布，转载请注明出处。

如何在MySQL中获得更好的搜索结果

mysql查询时offset过大影响性能的原因是什么

MySQL查询函数之为表和字段取别名的案例

怎么理解并掌握mysql参数文件和类型

如何使用SQL Mail收发和自动处理邮件中的扩展存储