原文:http://www.2cto.com/database/201310/253698.html
转另一篇:http://www.111cn.net/database/mysql/50319.htm
MySQL查看用户权限的两种方法
ySQL查看用户权限命令的两方法:
一. 使用MySQL grants
使用方法:
mysql> show grants for username@localhost;实例:
mysql> show grants for root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.01 sec)
二. 直接通过mysql select查询语句:
mysql> select * from mysql.user where user='test' and host='127.0.0.1' G;
*************************** 1. row ***************************
Host: 127.0.0.1
User: test
Password: *EB3C643405D7F53BD4BF7FBA98DCF5641E228833
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
1 row in set (0.00 sec)
可以看到Select_priv,Insert_priv,Update_priv…(www.111cn.net)等为N表示没有权限,该用户权限一目了然.
这时可以使用命令给用户加权限
grant all privileges on *.* to 'test'@'127.0.0.1' identified by 'passwd';
flush privileges;另外:show可以看到很多东西:
show databases;
show tables;
show create database dbname; // 这个可以看到创建数据库时用到的一些参数。
show create table tablename; // 可以看到创建表时用到的一些参数
还有另一篇安装blog 也转载一下:http://blog.csdn.net/shehun1/article/details/38827631
在Linux操作系统下,安装MYSQL有两种方式:一种tar安装方式,另外一种是rpm安装方式。这两种安装方式有什么区别呢?尽管我们在Linux下常用tar来压缩/解压缩文件,但MYSQL的tar格式的文件其实只是mysql的文件包,并不能直接安装,需要操作configure、make、install等命令才能完成安装,是一种比较繁琐的安装方式。而rpm格式的文件是真正的安装包,相当于windows的exe文件,可以直接安装。
yum remove mysql mysql-server mysql-libs mysql-server;
|
find / -name mysql
|
MySQL-client-advanced-5.6.20-1.el6.i686.rpm #MySQL客户端程序
MySQL-devel-advanced-5.6.20-1.el6.i686.rpm #MySQL的库和头文件
MySQL-embedded-advanced-5.6.20-1.el6.i686.rpm #MySQL的嵌入式程序
MySQL-server-advanced-5.6.20-1.el6.i686.rpm
#MySQL服务端程序
MySQL-shared-advanced-5.6.20-1.el6.i686.rpm
#MySQL的共享库
MySQL-shared-compat-advanced-5.6.20-1.el6.i686.rpm
#RHEL兼容包
MySQL-test-advanced-5.6.20-1.el6.i686.rpm
#MySQL的测试组件
|
MySQL-client-advanced-5.6.20-1.el6.i686.rpm
MySQL-devel-advanced-5.6.20-1.el6.i686.rpm
MySQL-server-advanced-5.6.20-1.el6.i686.rpm
|
rpm -qa | grep -i mysql
|
yum -y remove mysql-libs*
|
rpm -ivh MySQL-server-advanced-5.6.20-1.el6.i686.rpm
rpm -ivh MySQL-devel-advanced-5.6.20-1.el6.i686.rpm
rpm -ivh MySQL-client-advanced-5.6.20-1.el6.i686.rpm
|
cp /usr/share/mysql/my-default.cnf /etc/my.cnf
|
/usr/bin/mysql_install_db
#初始化MySQL
service mysql start
#启动MySQL
cat /root/.mysql_secret
#查看root账号的初始密码,会出现下述所示信息
mysql -uroot -pZFRmqNPoFH3aO5PU
#使用root账号登陆MySQL
set password=password('123456');
#更改MySQL密码,注意;不可少
exit
#退出
mysql -uroot -p123456
#使用新密码登陆
|
use mysql;
mysql>
select host,user,password from user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *DFEB299B8A17E376E7804A115F471149953B5645 |
| chenxu | root | *6E4C48EDF3CC66BC5F6E6C0980935C8ED660EFAA |
| 127.0.0.1 | root | *6E4C48EDF3CC66BC5F6E6C0980935C8ED660EFAA |
| ::1 | root | *6E4C48EDF3CC66BC5F6E6C0980935C8ED660EFAA |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)
mysql>
update user set password=password('aqjccmtj') where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 4 Changed: 3 Warnings: 0
mysql>
update user set host='%' where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
exit
Bye
|
[root@favccxx local]# chkconfig mysql on [root@favccxx local]# chkconfig --list | grep mysql mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off |
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
lower_case_table_names=1
max_connections=1000
|
/var/lib/mysql/
#数据库目录
#启动脚本
|
[
root@favccxx local]#
service mysql restart
Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! |
1、准备安装程序(官方网站下载)
服务端:MySQL-server-community-5.1.44-1.rhel4.i386.rpm
客户端:MySQL-client-community-5.1.44-1.rhel4.i386.rpm
2、安装(打印信息略)
[root@localhost /]#rpm -ivh MySQL-client-community-5.1.44-1.rhel4.i386.rpm;
3、查看安装是否成功
显示3306端口开放表示服务安装成功
4、测试连接
(*修改root密码)
[root@localhost /]#mysql -u root -p
Enter password:xxxxxx
出现mysql>表示登陆成功!
5、对mysql用户设置远程访问权限
方法1、改表法:登陆mysql后,更改“mysql”数据库里的“user”表里的“host”项,将“localhost”改“%”
mysql>update user set host ='%' where user ='root';
mysql>select host,user from user;
mysql>FLUSH PRIVILEGES
方法2、授权法:假设允许用户username通过密码password从远程连接到mysql服务器
mysql>FLUSH PRIVILEGES;
6、安装目录结构
数据库目录:/var/lib/mysql/
配置文件:/usr/share/mysql(mysql.server命令及配置文件)
相关命令:/usr/bin(mysqladmin、mysqldump等命令)(*mysql的一种安全启动方式:/usr/bin/mysqld_safe --user=root &)
启动脚本:/etc/rc.d/init.d/
7、停止/启动mysql服务
[root@localhost /]#service mysql start;
8、修改字符编码
查看字符编码:
[root@localhost /]#show variables like 'collation_%';
停止mysql服务
将目录/usr/share/mysql下的文件my-medium.cnf拷贝到/etc/下并改名为my.cnf
打开my.cnf在[client]和[mysqld]下面均加上default-character-set=utf8,并保存
*mysql5.5 改了字符集设置参数 character-set-server=utf8
重启mysql服务
9、卸载mysql
显示:
MySQL-client-community-5.1.44-1.rhel4.i386.rpm
[root@localhost /]#rpm -e MySQL-server-community-5.1.44-1.rhel4.i386.rpm
[root@localhost /]#rpm -e MySQL-client-community-5.1.44-1.rhel4.i386.rpm
删除残余文件
[root@localhost /]#rm -f /var/lib/mysql
10、复制mysql数据库
源数据库名:source_db 用户名:root 密码:xxxxxx
目标数据库名:target_db 用户名:root 密码:xxxxxx 所在主机:221.218.9.41