mysql-案例--实现基于MySQL的虚拟用户FTP服务/基于mysql的GTID复制

一、实现基于MySQL的虚拟用户FTP服务

环境：
两台主机：一台ftp服务器，一台mariadb服务器

1 mariadb服务器

yum install mariadb-server

mysql > 
create database vsftpd;
use vsftpd
CREATE TABLE users (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
name CHAR(50) BINARY NOT NULL,
password CHAR(48) BINARY NOT NULL
);
CREATE TABLE users ( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, name CHAR(50) BINARY NOT NULL, password CHAR(48) BINARY NOT NULL );

insert users (name,password) value('ftpuser1',password('centos'));
insert users (name,password) value('ftpuser2',password('magedu'));
grant select on vsftpd.users to vsftpd@'192.168.37.%' identified by  'centos';

2 编译安装pam_mysql

tar xvf pam_mysql-0.7RC1.tar.gz  
cd pam_mysql-0.7RC1/
yum install gcc gcc-c++ pam-devel mariadb-devel
./configure  --with-pam-mods-dir=/lib64/security/
make && make install

3

yum install vsftpd
useradd -d /data/ftproot -s /sbin/nologin vuser
chmod 555 /data/ftproot
mkdir /data/ftproot/upload
setfacl -m u:vuser:rwx /data/ftproot/upload
vim /etc/vsftpd/vsftpd.conf 
pam_service_name=vsftpd.mysql
guest_enable=YES
guest_username=vuser
user_config_dir=/etc/vsftpd/vusers.d/  

mkdir /etc/vsftpd/vusers.d/  
cat /etc/vsftpd/vusers.d/ftpuser1
anon_upload_enable=YES
anon_mkdir_write_enable=YES
anon_other_write_enable=YES

cat /etc/vsftpd/vusers.d/ftpuser2
anon_upload_enable=YES
anon_mkdir_write_enable=YES
anon_other_write_enable=YES
local_root=/data/ftproot2

mkdir /data/ftproot2
chmod 555 /data/ftproot2
mkdir /data/ftproot2/upload
setfacl -m u:vuser:rwx /data/ftproot2/upload

二、基于mysql 5.7的GTID复制

1 主服务器上

vim  /etc/my.cnf
[mysqld]
server-id=17
log-bin
gtid_mode=ON
enforce_gtid_consistency


datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid

[client]
socket=/data/mysql/mysql.sock

service mysqld restart 

mysql> grant replication slave on *.* to repluser@'192.168.37.%' identified by 'centos';

2从服务器上

vim /etc/my.cnf
[mysqld]
server-id=27
gtid_mode=ON
enforce_gtid_consistency
                                                                                                                                                 
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid

[client]
socket=/data/mysql/mysql.sock

service mysqld restart 

mysql>CHANGE MASTER TO MASTER_HOST='主服务器',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;

mysql>start slave;