ITPub博客

首页 > 数据库 > MySQL > MYSQL 主从 + ATLAS 读写分离 搭建

MYSQL 主从 + ATLAS 读写分离 搭建

原创 MySQL 作者:germany006 时间:2019-09-18 09:48:02 0 删除 编辑

一、环境准备

操作系统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> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从库

登录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 -h127.0.0.1 -P2345 -uuser -ppwd ”,能进去说明Atlas正常运行着呢,因为它会把自己当成一个MySQL数据库,所以在不需要数据库环境的情况下,也可以进入到MySQL数据库模式。


[root@localhost bin]# mysql -h127.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

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>


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


mysql> select * from help;
+----------------------------+---------------------------------------------------------+
| command                    | description                                             |
+----------------------------+---------------------------------------------------------+
| SELECT * FROM help         | shows this help                                         |
| SELECT * FROM backends     | lists the backends and their state                      |
| SET OFFLINE $backend_id    | offline backend server, $backend_id is backend_ndx's id |
| SET ONLINE $backend_id     | online backend server, ...                              |
| ADD MASTER $backend        | example: "add master 127.0.0.1:3306", ...               |
| ADD SLAVE $backend         | example: "add slave 127.0.0.1:3306", ...                |
| REMOVE BACKEND $backend_id | example: "remove backend 1", ...                        |
| SELECT * FROM clients      | lists the clients                                       |
| ADD CLIENT $client         | example: "add client 192.168.1.2", ...                  |
| REMOVE CLIENT $client      | example: "remove client 192.168.1.2", ...               |
| SELECT * FROM pwds         | lists the pwds                                          |
| ADD PWD $pwd               | example: "add pwd user:raw_password", ...               |
| ADD ENPWD $pwd             | example: "add enpwd user:encrypted_password", ...       |
| REMOVE PWD $pwd            | example: "remove pwd user", ...                         |
| SAVE CONFIG                | save the backends to config file                        |
| SELECT VERSION             | display the version of Atlas                            |
+----------------------------+---------------------------------------------------------+
16 rows in set (0.00 sec)

mysql>


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


[root@localhost bin]# mysql -h127.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 buck 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;

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




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28371090/viewspace-2652909/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
ORACLE,MYSQL,POSTGRESQL,SQLSERVER

注册时间:2013-03-06

  • 博文量
    763
  • 访问量
    916855