mysql读写分离（proxySQL) lamp+proxysql+nfs

 

先在主从节点安装mysql

[root@master-mariadb ~]# yum install mariadb-server -y
[root@slave-mariadb ~]# yum install mariadb-server -y

 

配置主节点和从节点

[root@master-mariadb ~]# vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server_id=27
log-bin=master-log
skip_name_resolve=ON
innodb_file_per_table=ON

[root@slave-mariadb ~]# vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
relay-log=slave-log
read_only=1
skip_name_resolve=ON
innodb_file_per_table=ON

 

配置主节点做成master

[root@master-mariadb ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-log.000001 |     30358 |
| master-log.000002 |   1038814 |
| master-log.000003 |       245 |
+-------------------+-----------+
3 rows in set (0.00 sec)

MariaDB [(none)]> grant all on *.* to 'repluser'@'192.168.37.%' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 

配置从服务器同步主节点

[root@slave-mariadb ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> change master to master_host='192.168.37.27',master_user='repluser',master_password='centos',master_log_file='master-log.000003',master_log_pos=245;
MariaDB [(none)]> start slave;

 

配置PorxySQL服务器的Yum源安装proxysql

[root@msyql-proxy ~]# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
> [proxysql_repo]
> name= ProxySQL YUM repository
> baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
> gpgcheck=1
> gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
> EOF

[root@msyql-proxy ~]# yum install proxysql -y
[root@msyql-proxy ~]# systemctl start proxysql

[root@msyql-proxy ~]# mysql -uadmin -padmin -P 6032 -h127.0.0.1

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.37.27',3306);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.37.28',3306);
Query OK, 1 row affected (0.00 sec)        #：将主从加入到proxysql

MySQL [(none)]> load mysql servers to runtime;  # 加载到内存
Query OK, 0 rows affected (0.00 sec)　　　　　　　　
MySQL [(none)]> save mysql servers to disk;     #保存到磁盘
Query OK, 0 rows affected (0.01 sec)
 

 

在master节点添加一个监控后端服务器的用户

 MariaDB [(none)]> grant replication client on *.* to monitor@'192.168.37.%' identified by 'magedu';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 

在proxysql服务器添加监控后端服务器的用户

MySQL [(none)]> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> set mysql-monitor_password='magedu';
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> save mysql variables to disk;
Query OK, 97 rows affected (0.00 sec)

MySQL [(none)]> insert into mysql_replication_hostgroups values(10,20,"test");
Query OK, 1 row affected (0.00 sec)  #设置分组

MySQL [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.01 sec)

 

在主节点创建一个用户可以让该用户操作主从节点的数据库

MariaDB [(none)]> grant all on *.* to sqluser@'192.168.8.%' identified by 'magedu';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 

在proxysql服务器，将该用户添加到proxysql表里

MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup) values('sqluser','magedu',10);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> save mysql users to disk
    -> ;
Query OK, 0 rows affected (0.00 sec)

 

在proxysql上配置路由规则

MySQL [(none)]> insert into mysql_query_rules
    -> (rule_id,active,match_digest,destination_hostgroup,apply)VALUES
    -> (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);
Query OK, 2 rows affected (0.01 sec)

MySQL [(none)]> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> save mysql query rules to disk;
Query OK, 0 rows affected (0.00 sec)

 

在rs2服务器安装httpd php-fpm php-mysql

[root@rs2 ~]# yum install php-fpm php-mysql httpd -y

 

在rs2服务器配置httpd

[root@rs2 html]# vim /etc/httpd/conf/httpd.conf
<IfModule dir_module>
    DirectoryIndex index.phpindex.html
</IfModule>
AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .phps
ProxyPassMatch "^/(.*\.php)$" "fcgi://127.0.0.1:9000/var/www/html/$1"
[root@rs2 html]# systemctl restart httpd

 

然后配置nfs服务器

[root@NFS ~]# yum install nfs-util rpcbind -y

[root@NFS ~]# useradd apache  #创建映射用户
[root@NFS ~]# id apache
uid=1000(apache) gid=1000(apache) groups=1000(apache)


[root@NFS ~]# vim /etc/exports.d/httpd.exports    #将目录共享给http服务器
/data/httpd 192.168.37.25(rw,async,all_squash,anonuid=1000,anongid=1000)
/data/httpd 192.168.37.24(rw,async,all_squash,anonuid=1000,anongid=1000)


[root@NFS ~]# mkdir /data/httpd/  #创建目录
[root@NFS wordpress]# systemctl start nfs

 

将wordpress程序移动到nfs服务器目录并解压然后修改目录权限

[root@NFS httpd]# ls
index.html  wordpress  wordpress-5.0.4-zh_CN.tar.gz
[root@NFS httpd]# setfacl -Rm u:apache:rwx /data/httpd/

 

在master-mysql服务器授权wordpress账户

MariaDB [(none)]> create database wordpress
    -> ;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> grant all on wordpress.* to 'wordpress'@'192.168.37.%' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

 

在Proxysql 服务器将用户添加到msyql_user表中

MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup) values ('wordpress','centos',10);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> load mysql users to disk;
ERROR 1045 (#2800): near "load": syntax error
MySQL [(none)]> save mysql users to disk;
Query OK, 0 rows affected (0.00 sec)

 

在nfs服务器配置wordpress的配置文件

[root@NFS wordpress]# cp wp-config-sample.php wp-config.php 
[root@NFS wordpress]# vim wp-config.php 
define('DB_NAME', 'wordpress');

/** MySQL?版.搴..?峰. */
define('DB_USER', 'wordpress');

/** MySQL?版.搴..?.*/
define('DB_PASSWORD', 'centos');

/** MySQL涓绘. */
define('DB_HOST', '192.168.37.23:6033');

/** ?.缓?版.琛ㄦ.榛..?..瀛.??.*/
define('DB_CHARSET', 'utf8');

/** ?版.搴..?.被?.€.?涓.‘瀹..?挎.??*/
define('DB_COLLATE', '');