mysql nfs读写分离_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=27log-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 connectionid is 3Server 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 clearthe 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.00sec)

MariaDB [(none)]> grant all on *.* to 'repluser'@'192.168.37.%' identified by 'centos';

Query OK,0 rows affected (0.00sec)

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 connectionid is 5Server 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 clearthe 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 <[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.00sec)

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.00sec)

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.00sec)

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.00sec)

MySQL [(none)]>load mysql users to runtime;

Query OK,0 rows affected (0.00sec)

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.01sec)

MySQL [(none)]>load mysql query rules to runtime;

Query OK,0 rows affected (0.00sec)

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.confDirectoryIndex index.phpindex.htmlAddType 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~]# idapache

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]# lsindex.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.00sec)

MariaDB [(none)]> grant all on wordpress.* to 'wordpress'@'192.168.37.%' identified by 'centos';

Query OK,0 rows affected (0.00sec)

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.00sec)

MySQL [(none)]>load mysql users to runtime;

Query OK,0 rows affected (0.00sec)

MySQL [(none)]>load mysql users to disk;

ERROR1045 (#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', '');