还不会MySQL数据库的读写分离？看这里，学会就是赚到

还不会MySQL数据库的读写分离？看这里，学会就是赚到

一、什么是数据库
1、数据库的好处
将数据持久化到本地
提供结构化查询功能
2、数据库的常见概念
DB：数据库，存储数据的仓库
DBMS：数据库管理系统，又称为数据库软件或者数据库产品，用于创建和管理数据库，常见的有MySQL、Oracle、SQL Server
DBS：数据库系统，数据库系统是一个通称，包括数据库、数据库管理系统、数据库管理人员等，是最大的范畴
SQL：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言
3、数据库的存储特点
数据存放到表中，然后表再放到库中
一个库中可以有多张表，每张表具有唯一的表名用来标识自己
表中有一个或多个列，列又称为“字段”，相当于Java中“属性”
表中的每一行数据，相当于Java中“对象”
4、数据库的常见分类
关系型数据库：MySQL、Oracle、DB2、SQL Server
非关系型数据库：
键值存储数据库：Redis、Memcached、MemcacheDB
列存储数据库：HBase、Cassandra
面向文档的数据库：MongDB、CouchDB
图形数据库：Neo4J
5、SQL语言的分类
DQL：数据查询语言：select、from、where
DML：数据操作语言：insert、update、delete
DDL：数据定义语言：create、alter、drop、truncate
DCL：数据控制语言：grant、revoke
TCL：事务控制语言：commit、rollback

二、MySQL的背景
1.MySQL的前身是属于MySQL AB，08年被SUN公司收购，09年SUN公司又被Oracle公司收购

三、MySQL的优点
1.成本低、开源免费
2.性能高、移植性好
3.体积小、便于安装

四、 安装MySQL读写分离
4.1、环境准备
复制CentOS 6 64 bit-2 > CentOS 6 64 bit-3

注意：因为CentOS 6 64 bit-2已经是配置好的slave节点了，我们只需要做少量修改就能实现一主两从的架构了，
所以我们复制CentOS 6 64 bit-2，当然，你要是时间允许，机器性能允许，您也可以重新按照主从复制的步骤在搭建
一台slave，在这里我们就不搭建了，只是进行一下复制修改，复制的时候，注意关机，先关闭slave，再关闭master，
再复制，再重命名，先启动master，在启动slave，在启动新复制的那台slave

启动CentOS 6 64 bit-3：选择“我已复制该虚拟机”
4.2、读写分离介绍
以上已经完成了主从复制（一主一从）配置，然而这只是一个基本配置，加上一个proxysql实现MySQL读写分离，proxysql支持MySQL协议的的数据库代理，程序访问的不再是具体的数据库，而是proxysql这个代理程序，用户请求发向proxysql，如果是写请求发往主节点，读请求发下从节点组中，以此实现读写分离一定程序上减轻了主数据库的io压力。

4.3、读写分离架构
实现目标：一主两从，基于上文中的一主一从，我们重新复制一个从节点，进行少量修改即可
4.4、修改从节点二

注意：主节点：CentOS 6 64 bit-1【128】、从节点一：CentOS 6 64 bit-2【129】、从节点二：CentOS 6 64 bit-3【130】

停止：

[root@caochenlei ~]# service mysql stop
Shutting down MySQL..                                      [确定]

删除：

[root@caochenlei ~]# rm -f /var/lib/mysql/auto.cnf

修改：

[root@caochenlei ~]# vi /usr/my.cnf

#服务器ID标识
server-id=3

启动：

[root@caochenlei ~]# service mysql start
Starting MySQL.......                                      [确定]

登录：

[root@caochenlei ~]# mysql -uroot -p123456

查看：

USE mytest; 
SELECT * FROM myuser;

测试：

第一步：在主节点（192.168.206.128）插入数据

INSERT INTO `mytest`.`myuser` (`id`, `username`, `password`) VALUES ('9', 'xiaojiu', '123456');

USE mytest; 
SELECT * FROM myuser;

第二步：在从节点（192.168.206.129）查看数据

USE mytest; 
SELECT * FROM myuser;

第三步：在从节点（192.168.206.130）查看数据

USE mytest; 
SELECT * FROM myuser;

注意：如果说从节点一或者从节点二有一个不正常工作，也就是不能主从复制，主要是它们的服务器ID标识复制的时候是一样的，开机因为已经添加了自启动，所以肯定会冲突，有一个不能够连接到主节点，只要这个时候重启一下不能正常工作的那台机器的mysql服务就行了，因为从节点二我们已经修改server-id=3了，这样它们两个从节点肯定不会冲突了，到这里，一主两从的模式就搭建好了，接下来就是配置读写分离了。

4.5、安装proxysql
设下载源：

cat << EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

安装依赖：

[root@caochenlei ~]# yum install -y mysql-libs perl-DBI perl-DBD-MySQL

开始安装：

[root@caochenlei ~]# yum install -y proxysql-2.0.13-1

开放端口：

[root@caochenlei ~]# /sbin/iptables -I INPUT -p tcp --dport 6032 -j ACCEPT
[root@caochenlei ~]# /etc/rc.d/init.d/iptables save
iptables：将防火墙规则保存到 /etc/sysconfig/iptables：[确定]

[root@caochenlei ~]# /sbin/iptables -I INPUT -p tcp --dport 6033 -j ACCEPT
[root@caochenlei ~]# /etc/rc.d/init.d/iptables save
iptables：将防火墙规则保存到 /etc/sysconfig/iptables：[确定]

添加自启动：

[root@caochenlei ~]# chkconfig --add proxysql
[root@caochenlei ~]# chkconfig proxysql on

删除文件：

[root@caochenlei ~]# rm -f /etc/proxysql.cnf

新增文件：

[root@caochenlei ~]# vi /etc/proxysql.cnf

datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"
admin_variables=
{
        #本地登录的账号和密码
        admin_credentials="admin:admin"
        #本地登录的IP和端口号
        mysql_ifaces="0.0.0.0:6032"
}
mysql_variables=
{
        threads=4
        max_connections=2048
        default_query_delay=0
        default_query_timeout=36000000
        have_compress=true
        poll_timeout=2000
        #远程登录的IP和端口号
        interfaces="0.0.0.0:6033"
        default_schema="information_schema"
        stacksize=1048576
        server_version="5.5.30"
        connect_timeout_server=3000
        #监控账号的账号和密码
        monitor_username="monitor"
        monitor_password="monitor"
        monitor_history=600000
        monitor_connect_interval=60000
        monitor_ping_interval=10000
        monitor_read_only_interval=1500
        monitor_read_only_timeout=500
        ping_interval_server_msec=120000
        ping_timeout_server=500
        commands_stats=true
        sessions_sort=true
        connect_retries_on_failure=10
}
#以下的配置我们会采用动态语句的形式配置
mysql_servers =
(
)
mysql_users:
(
)
mysql_query_rules:
(
)
scheduler=
(
)
mysql_replication_hostgroups=
(
)

注意：6032是连接proxysq的管理端口，6033是对外提供服务的端口。

启动服务：

[root@caochenlei ~]# service proxysql start
Starting ProxySQL: 2020-08-25 20:30:00 [INFO] Using config file /etc/proxysql.cnf
2020-08-25 20:30:00 [INFO] Using OpenSSL version: OpenSSL 1.1.1d  10 Sep 2019
2020-08-25 20:30:00 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates.
DONE!

4.6、删除自带库

[root@caochenlei ~]# rpm -qa | grep mysql
mysql-libs-5.1.73-8.el6_8.x86_64
[root@caochenlei ~]# rpm -e mysql-libs-5.1.73-8.el6_8.x86_64 --nodeps

4.7、安装依赖包

[root@caochenlei ~]# yum -y install numactl perl libaio wget

4.8、下载服务端
下载：

[root@caochenlei ~]# wget https://cdn.mysql.com//Downloads/MySQL-5.6/MySQL-server-5.6.49-1.el6.x86_64.rpm

查看：

[root@caochenlei ~]# ll MySQL-server-5.6.49-1.el6.x86_64.rpm
-rw-r--r--. 1 root root 65467136 6月   3 13:36 MySQL-server-5.6.49-1.el6.x86_64.rpm

4.9、安装服务端

[root@caochenlei ~]# rpm -ivh MySQL-server-5.6.49-1.el6.x86_64.rpm

4.10、下载客户端
下载：

[root@caochenlei ~]# wget https://cdn.mysql.com//Downloads/MySQL-5.6/MySQL-client-5.6.49-1.el6.x86_64.rpm

查看：

[root@caochenlei ~]# ll MySQL-client-5.6.49-1.el6.x86_64.rpm
-rw-r--r--. 1 root root 21584380 6月   3 13:36 MySQL-client-5.6.49-1.el6.x86_64.rpm

4.11、安装客户端

[root@caochenlei ~]# rpm -ivh MySQL-client-5.6.49-1.el6.x86_64.rpm

4.12、启动数据库服务

[root@caochenlei ~]# service mysql start
Starting MySQL.Logging to '/var/lib/mysql/caochenlei.err'.
..                                                         [确定]

4.13、查看初始化密码

[root@caochenlei ~]# cat /root/.mysql_secret
# The random password set for the root user at Tue Aug 25 20:31:19 2020 (local time): IFIn7pcTznYpoLm1

4.14、修改初始化密码
登录：

[root@caochenlei ~]# mysql -uroot -pIFIn7pcTznYpoLm1

修改：

mysql> set password = password('123456');
Query OK, 0 rows affected (0.01 sec)

退出：

mysql> exit
Bye

4.15、创建两个账号
这个操作是在master节点进行的，创建完成后，会自动复制到从节点，方便proxysql后来的监控和远程登录的认证连接

注意：可以使用 select user,host from mysql.user; 查看所有用户

为proxysql创建监控账号（主要做监控使用）：

create user 'monitor'@'%' identified by 'monitor';
grant all privileges on *.* to 'monitor'@'%' identified by 'monitor';
flush privileges;

为proxysql创建远程账号（主要是远程登录）：

create user 'proxysql'@'%' identified by 'proxysql';
grant all privileges on *.* to 'proxysql'@'%' identified by 'proxysql';
flush privileges;

退出：

mysql> exit
Bye

4.16、配置proxysql
这个操作是在安装proxysql的这台机器上的操作，6032端口的默认账号密码为admin，如果想要修改请在第12.5步修改，然后重启。

[root@caochenlei ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt 'admin>'

分别执行以下语句

# 配置主从复制的信息
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(10,'192.168.206.128',3306,1,1000,10,'write mysql');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(20,'192.168.206.129',3306,1,10000,10,'read mysql');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(20,'192.168.206.130',3306,1,10000,10,'read mysql');

# 本地主机登录的账号：默认就是admin:admin不用再配置

# 配置远程登录的账号：之前在master主节点中已经创建了
insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('proxysql','proxysql',1,20,1);

# 设置转发规则
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',10,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',20,1);

# 立刻加载配置
load mysql servers to runtime;
load mysql users to runtime;
load mysql variables to runtime;
load mysql query rules to runtime;

# 配置写到磁盘
save mysql servers to disk;
save mysql users to disk;
save mysql variables to disk;
save mysql query rules to disk;

执行完成以后退出

4.17、连接proxysql

注意：如果远程连接不上，请把防火墙全部关掉试试，如果不行，请重新启动一下proxysql，还是不行，请重头排查一下问题，或者查看日志tail /var/lib/proxysql/proxysql.log！

第一种：远程命令行连接测试

主节点、两个从节点随便找一台测试一下：

##连接
[root@caochenlei ~]# mysql -uproxysql -pproxysql -h192.168.206.131 -P6033

##查看所有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mytest             |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.05 sec)

第二种：远程图形化窗口连接

注意这个端口是6033而不是3306，账号和密码我之前设置的都是proxysql

4.18、查询监控数据
如果想要查看sql语句在哪个数据库执行的，在proxysql机器上请执行以下语句

[root@caochenlei ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt 'admin>'
admin> select * from stats_mysql_query_digest;

总结

学习起来都不容易，希望大家以后一起努力，共勉，需要学习资料的同学可以一起探讨哦！

更多JAVA面试题：点这里，CSDN资料库还有更多大厂面试专题资料和视频哦！