MySQL – Henry's dreamland

服务器运行2个月了一直靠着sftp在传文件，决定还是配个ftp server方便下其他需要文件传输的朋友。

根据版本号原则（选择版本号最丰富最高的那个），在ProFTP/Pure-ftp/vsftp中选择了ProFTP，部分原因也是为了能搭配Mysql来保存用户信息。
这样做的好处在于可以不必暴露服务器上真实的用户名密码，即使信息外泄也不会引发严重安全问题，而且修改用户信息定义也方便，无需root登录只要修改下数据库就生效了。

在Gentoo上安装时需要USE参数中有mysql才会编译proftpd的mysql支持。
下面就是proftpd.conf里的配置，辅以简单说明
[code lang=”bash”]
# 申明从mysql中读取用户信息
AuthOrder mod_sql.c
# SQL信息log输出，方便调试
SQLLogFile /var/log/proftpd.sqllog
# 指定验证方法，Backend即调用特定数据库的密码加密方法，如Mysql下的password()函数
SQLAuthTypes Backend
SQLBackend mysql
SQLAuthenticate users groups
#用户库连接信息数据库名@主机名用户名密码
SQLConnectInfo dbname@localhost dbuser dbpassword
# 数据库表的结构，如果你采用后文给出的db schema则无需更改
SQLUserInfo ftpusers userid passwd uid gid homedir shell
SQLGroupInfo ftpgroups groupname gid members
# 当用户登录时更新数据库表中count字段的值+1并且更新accessed字段值为当前时间。
#这两条并非必须，只是为了记录下用户登录时间次数。同理，可以将用户执行STOR或DELE的信息输出到日志
SQLLog PASS updatecount
SQLNamedQuery updatecount UPDATE “count=count+1,accessed=now() WHERE userid=’%u'” ftpusers
[/code]

[code lang=”sql”]
# 数据表结构如下
CREATE TABLE IF NOT EXISTS `ftpusers` (
`userid` varchar(40) NOT NULL,
`passwd` varchar(50) NOT NULL,
`uid` int(11) NOT NULL default ‘0’,
`gid` int(11) NOT NULL default ‘0’,
`homedir` text NOT NULL,
`shell` text NOT NULL,
`count` int(11) NOT NULL default ‘0’,
`accessed` datetime default NULL,
PRIMARY KEY (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ftpgroups` (
`groupname` varchar(50) NOT NULL,
`gid` int(11) NOT NULL,
`members` text NOT NULL,
PRIMARY KEY (`groupname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
[/code]

这里需要注意的是，user表中每个user的uid和gid字段值必须是系统中存在的并且有足够权限读写homedir指向的路径。举个例子，如果有虚拟用户ayue且homedir指向/var/ftp/ayue，并且/var/ftp/ayue实际上是由用户Juda拥有，则ayue的uid和gid必须和/etc/passwd中Juda的一样才能正常读写该目录。也就是说，我们在数据库中创建的虚拟用户ayue可以理解为系统中真实存在的用户Juda的一个映射，ayue上传的文件在系统里就是Juda（同uid，同gid）所拥有的。

最近遇到一个案列，MySQL主机已经存有10G左右的数据并且每天还在以1G为单位增长。现在需要制定备份策略并且用于数据备份和服务器维护的停机时间要尽可能的短。

对此，我们打算配置Master主服务器 -> Slave从服务器单向同步复制并且在Slave机上做周期增量备份。如果Master因意外灾难无法继续服务可以立即切换Slave机顶替服务。以下内容中Master机指主服务器，Slave机指从服务器。

1. 开启二进制日志服务（5.1版本中二进制日志替换了老的更新日志），启动MySQL时加–log-bin参数或在my.cnf中加上这一行。后面可以跟文件名来指定日志名，扩展名会被自动忽略。默认文件名为mysqld-bin.0000x。数字x会自动增长并且mysqld-bin.index中会指定当前的日志文件索引，请勿手工维护.index文件。

2. 建立账户用户数据复制，该账户必须授予REPLICATION SLAVE权限。鉴于我们还需要从Slave机上执行LOAD DATA FROM MASTER 和 LOAD DATA FROM MASTER，还需要授予账户SUPER和RELOAD全局权限。并且为所有想要装载的表授予SELECT权限。任何该账户不能SELECT的主服务器上的表被LOAD DATA FROM MASTER忽略掉。
例如[code lang=”sql”]
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%.mydomain.com’ IDENTIFIED BY ‘slavepass’;
[/code]

3. 执行FLUSH TABLES WITH READ LOCK语句清空所有表和块写入语句：
[code lang=”sql”]
mysql> FLUSH TABLES WITH READ LOCK；
[/code]

4. 鉴于原始数据库已经有超过10G的容量，现在使用mysqldump把数据备份成一个巨大的.sql文件显然是不明智的选择。可以简单地复制数据库文件到Slave机来快速创建一个主机的快照。Unix上用tar,windows上用winzip均可。

5. 当FLUSH TABLES WITH READ LOCK所置读锁定有效时，读取主服务器上当前的二进制日志名和偏移量值：
[code lang=”sql”]
mysql> SHOW MASTER STATUS;
[/code]

File列显示日志名，而Position显示偏移量。在该例子中，二进制日志值为mysql-bin.000007，偏移量为2268600。记录该值。以后设置从服务器时需要使用这些值。它们表示复制坐标，从服务器应从该点开始从主服务器上进行新的更新。

6. 主服务器数据库文件备份完成并且记录上述日志名和偏移量后，可以在主服务器上重新启用写活动：
[code lang=”sql”]
mysql> UNLOCK TABLES；
[/code]

7. 检查Master机上的my.cnf配置，确保在[mysqld]部分除开启log-bin外且指定server-id=1。这里我们将Master机上的server-id指定为1，Slave机上的my.cnf中将其指定为2。

8. 启动Slave机（在此之前已经复制了Master上的数据文件），用–skip-slave-start选项启动从服务器，以便它不立即尝试连接主服务器。

9. 在从服务器上执行下面的语句，用Master机的实际值替换选项值：
[code lang=”sql”]
mysql> CHANGE MASTER TO
-> MASTER_HOST=’master_host_name’,
-> MASTER_USER=’replication_user_name’,
-> MASTER_PASSWORD=’replication_password’,
-> MASTER_LOG_FILE=’recorded_log_file_name’,
-> MASTER_LOG_POS=recorded_log_position;
[/code]

10. 启动Slave服务器上的复制进程
[code lang=”sql”]
mysql> START SLAVE；
[/code]
执行这些程序后，从服务器应连接主服务器，并补充自从数据库文件备份以来发生的任何更新。

Tag: MySQL

ProFTP 在MySQL中配置用户信息

MySQL 同步和备份策略 [一]