MYSQL-AB复制/MYSQL+VSFTP。
SQL语句不区分大小写
给用户授权:
如何一个用户对所有数据库都有权限,则存放在user表中
而如果一个用户对某个数据库具有权限时,则在db表中
mysql> use mysql;
No connection. Trying to reconnect…
Connection id: 10
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select host,user,password from user;
授权:
其实修改的是user/db表。手动方法创建用户:
mysql> flush privileges;
用insert命令创建用户时必须刷新授权表,该命令只能用管理员来执行。
mysql> mysql> insert into mysql.user (host,user,password,select_priv) values(‘.0.%’,'us.%’,'user’,password(’123′),’y');
撤消权限:
mysql> revoke select on *.* from user@’192.168.0.%’;
撤消某个权限:
mysql> revoke update,delete on *.* from user@’192.168.0.%’;
设置某个用户只对某个表有某个权限:
mysql> grant insert,update on chunhui.uplooking to user1;
对某个字段具有某个权限:
mysql> grant update (id,name),select (email) on chunhui.uplooking to user1;
MYSQL管理员:
如果管理员的密码忘了,我们可以把原来的/var/usr/local/mysql/var/mysql目录改名为:mysql.old
然后重新初始化,再重现修改权限,然后重新启动MYSQL数据库,便可以不用密码登录到MYSQL数据库了,这样便可重新修改管理员的密码
第二种方法:
停止MYSQL服务
然后跳过授权表启动:
[root@stu15 ~]# mysqld_safe –skip-grant-table –user=mysql &
这里相当于Linux的但用户模式
然后修改密码:
mysql> update mysql.user set password=password(’123456′) where host=’localhost’ and user=’root’;
再关闭MYSQL服务:
[root@stu15 ~]# pkill mysql
然后再重新正常启动MYSQL数据库
mysql> grant usage on *.* to user2;
Query OK, 0 rows affected (0.01 sec)
用来测试是否能登录,授权登录,但啥都做不了!
二进制日志备份方法:
方法一:
[root@stu15 ~]# mysqld_safe –log-bin –user=mysql &
[1] 5373
[root@stu15 ~]# 100122 11:34:02 mysqld_safe Logging to ‘/usr/local/mysql/var/stu15.uplooking.com.err’.
100122 11:34:02 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
[root@stu15 var]# pwd
/usr/local/mysql/var
[root@stu15 var]# ls
chunhui ib_logfile0 phpbb stu15.uplooking.com.err
discuz ib_logfile1 stu15-bin.000001 stu15.uplooking.com.pid
ibdata1 mysql stu15-bin.index
方法二:
[root@stu15 ~]# vim /etc/my.cnf
1 [mysqld]
2 log-bin=mysql-bin(修改前缀名)
[root@stu15 ~]# mysqld_safe –user=mysql &
[1] 5481
[root@stu15 ~]# 100122 11:38:07 mysqld_safe Logging to ‘/usr/local/mysql/var/stu15.uplooking.com.err’.
100122 11:38:07 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
[root@stu15 ~]# ls /usr/local/mysql/var/
chunhui mysql stu15-bin.index
discuz mysql-bin.000001 stu15.uplooking.com.err
ibdata1 mysql-bin.index stu15.uplooking.com.pid
ib_logfile0 phpbb
ib_logfile1 stu15-bin.000001
mysql> show binlog events \G
*************************** 1. row ***************************
Log_name: mysql-bin.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 106
Info: Server ver: 5.1.34-log, Binlog ver: 4
1 row in set (0.00 sec)
mysql> use chunhui;
Database changed
mysql> create table t1 (id int,name char(10));
Query OK, 0 rows affected (0.01 sec)
[root@stu15 var]# mysqlbinlog -u root -p123456 mysql-bin.000001
会有很多输出
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)
根据时间恢复:
[root@stu15 var]# mysqlbinlog –stop-date=”10-01-22 11:46:39″ mysql-bin.000001 | mysql -u root -p123456
mysql> show tables;
+——————-+
| Tables_in_chunhui |
+——————-+
| t1 |
+——————-+
1 row in set (0.00 sec)
mysql> delete from t1;
Query OK, 0 rows affected (0.00 sec)
[root@stu15 var]# mysqlbinlog –start-date=”10-01-22 11:40:13″ –stop-date=”10-01-22 11:58:58″ mysql-bin.000001 | mysql -u root -p123456
时间备份不是怎么太准确,用如下方法:
[root@stu15 var]# mysqlbinlog –start-positione=645 –stop-position=738 mysql-bin.000001 | mysql -u root -p123456
[root@stu15 var]# mysqladmin flush-logs -u root -p123456
该命令用来产生一个新的日志文件
[root@stu15 var]# ll | grep mysql-bin
-rw-rw—- 1 mysql mysql 516 01-22 12:10 mysql-bin.000001
-rw-rw—- 1 mysql mysql 106 01-22 12:10 mysql-bin.000002
[root@stu15 var]# mysqldump –delete-master-logs –all-databases > /root/all.sql -u root -p123456
该命令在备份完后把日志删除,以后的会重新产生新的日志。
[root@stu15 var]# ll /root/all.sql
-rw-r–r– 1 root root 717149 01-22 12:15 /root/all.sql
[root@stu15 var]#
[root@stu15 var]# ll | grep mysql-bin
-rw-rw—- 1 mysql mysql 206 01-22 12:15 mysql-bin.000003
MYSQL--AB复制:
两台机器中的内容必须是一样的,实现数据同步
主的必须开启二进制日志
从的然后到主的请求二进制日志文件,方在数据库目录下,然后启动sql线程来重新执行该日志文件
主从的都开启I/O
主要用来实现读写分离
同时也实现了备份
主的步骤:
1:开启binlog
2:grant Rephcation
3:把主服务器做一个完全备份,同步数据
主的配置:192.168.0.2
[root@stu15 ~]# vim /etc/my.cnf
[root@stu15 ~]# [mysqld]
[root@stu15 ~]# server-id=1
[root@stu15 ~]# log-bin=mysql-bin
2:重新启动MYSQL服务
mysql> grant replication slave,reload,super on *.* to slave@192.168.0.254 identified by ’123′;
Query OK, 0 rows affected (0.00 sec)
登录测试一下
4:[root@stu15 var]# mysqldump –all-databases > /root/all.sql -u root -p123456
从的配置:192.168.0.254
[root@stu15 var]# scp 192.168.0.2:/root/all.sql /root
[root@stu15 var]# vim /etc/my.cnf
[root@stu15 var]# [mysqld]
[root@stu15 var]# server-id=2
[root@stu15 var]# master-host=192.168.0.2
[root@stu15 var]# master-user=slave
[root@stu15 var]# master-password=123456
2:重新启动MYSQL服务,加载配置文件
在[root@stu15 var]# pwd
/usr/local/mysql/var
目录中的文件会把主的复制过来,有一个叫master.info,里面记录着一些主的信息
3:登录测试
[root@stu15 var]# mysql -u root -p123
密码一定是主服务器MYSQL的密码
4:开启sql进程
mysql> slave start;
结束sql进程
mysql> slave stop;
在主的上面创建数据库来测试一下
如果不能同步数据:
主的:首先停止数据库,然后删除数据库目录下的日志文件,和.index文件也删除
从的:把-relay-文件删除
***********很重要*************
VSFTP+MYSQL
[root@stu15 ~]# cd /etc/pam.d
[root@stu15 pam.d]# ll vsftpd
-rw-r–r– 1 root root 329 2007-12-13 vsftpd
1 #%PAM-1.0
2 session optional pam_keyinit.so force re voke
3 auth required pam_listfile.so item=user sens e=deny file=/etc/vsftpd/ftpusers onerr=succeed
4 auth required pam_shells.so
5 auth include system-auth
6 account include system-auth
7 session include system-auth
8 session required pam_loginuid.so
1:生成模块
[root@stu15 ~]# ll pam_mysql-0.7RC1.tar.gz
-rw-r–r– 1 root root 335240 01-22 15:25 pam_mysql-0.7RC1.tar.gz
解压:
[root@stu15 pam_mysql-0.7RC1]# tar zxvf pam_mysql-0.7RC1.tar.gz -C /usr/local/
编译前的准备
[root@stu15 pam_mysql-0.7RC1]# ./configure –with-mysql=/usr/local/mysql/
make
make install
[root@stu15 pam_mysql-0.7RC1]# ls /usr/lib/security/pam_mysql.so
/usr/lib/security/pam_mysql.so
[root@stu15 pam_mysql-0.7RC1]# cp !$ /lib/security/
cp /usr/lib/security/pam_mysql.so /lib/security/
创建数据库:
create database ftp;
use ftp;
create table users (name char(10) primary key,pass varchar(20));
insert into users values (‘abc’,123),(‘mike’,123);
创建一个MYSQL用户:
grant select on ftp.users to vsftp@localhost identified by ’123′;
数据库的配置结束
修改一下认证的配置文件:(pam.d目录下)
[root@stu15 pam.d]# vim vsftpd.vu
1 auth required pam_mysql.so host=localhost user=vsf tp passwd=123 db=ftp table=users usercomlumn=name passwdcomlumn=pass crypt=0
2 account required pam_mysql.so host=localhost user= vsftp passwd=123 db=ftp table=users usercomlumn=na me passwdcomlumn=pass crypt=0
创建一个普通用户:
[root@stu15 pam.d]# useradd vu -s /sbin/nologin
修改配置文件:
[root@stu15 pam.d]# vim /etc/vsftpd/vsftpd.conf
114 pam_service_name=vsftpd.vu
115 userlist_enable=YES
116 guest_enable=YES
117 guest_username=vu
1 auth required pam_mysql.so host=localhost user=vsf tp passwd=123 db=ftp table=users usercomlumn=name passwdcomlumn=pass crypt=0
2 account required pam_mysql.so host=localhost user= vsftp passwd=123 db=ftp table=users usercomlumn=na me passwdcomlumn=pass crypt=0
114 pam_service_name=vsftpd.vu
115 userlist_enable=YES
116 guest_enable=YES
117 guest_username=vu
118 anon_world_readable_only=no
[root@stu15 pam.d]# service vsftpd restart
关闭 vsftpd: [确定]
为 vsftpd 启动 vsftpd: [确定]
114 pam_service_name=vsftpd.vu
115 userlist_enable=YES
116 guest_enable=YES
117 guest_username=vu
118 anon_world_readable_only=no
119 anon_upload_enable=yes