mysql日常命令一

xmxm76

一、Mysql字符集和存储引擎
　　安装数据库的时候，需要加扩展命令，添加所有字符集
　　# ./ configure   --prefix=/usr/local/mysql   --with-charset=gbk   --with-extra-charset=all
　　
　　测试是否支持gbk编码：
　　set names gbk；
　　
　　查看数据库的编码
　　mysql> show character set;
　　
　　创建数据库，使用gbk编码
　　create database infosww default character set 'gbk';
　　导入数据，使UTF-8编码方式连接
　　/usr/local/mysql/bin/mysql -u root -p  ypk --default-character-set=utf8 < ypk.sql
　　
　　
　　查看修改字符集
　　（1）查看数据库默认的字符集
　　Mysql > show variables like 'character\_set\_%';
　　显示
　　+--------------------------+--------+
　　| Variable_name            | Value  |
　　+--------------------------+--------+
　　| character_set_client     | latin1 |
　　| character_set_connection | latin1 |
　　| character_set_database   | latin1 |
　　| character_set_filesystem | binary |
　　| character_set_results    | latin1 |
　　| character_set_server     | latin1 |
　　| character_set_system     | utf8 |
　　+--------------------------+--------+
　　
　　（2）查看某个表的字符集
　　Mysql > show create table 表名;
　　
　　（3）修改表的字符集
　　Mysql > ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name
　　如
　　Mysql > alter table 表名 convert to CHARACTER set gbk;
　　
　　（4）查看某个库的字符集
　　Mysql> show create database 库名;

　　Mysql>>　　
　　
　　Mysql查看可用的字符串
　　Mysql > SHOW CHARACTER SET; //列出可用的字符串
　　+----------+-----------------------------+---------------------+--------+
　　| Charset  | Description                 | Default collation   | Maxlen |
　　+----------+-----------------------------+---------------------+--------+
　　| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
　　| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
　　| cp850    | DOS West European           | cp850_general_ci    |      1 |
　　| hp8      | HP West European            | hp8_english_ci      |      1 |
　　| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
　　| latin1   | cp1252     West European    | latin1_swedish_ci   |      1 |
查看mysql支持的存储引擎：mysql> show engines;        查看myslq默认的存储引擎：mysql> show variables like '%storage_engine%';查看表的具体引擎：  mysql> show table status like 'user' \G*************************** 1. row ***************************           Name: user         Engine: MyISAM        Version: 10     Row_format: Dynamic           Rows: 7 Avg_row_length: 68    Data_length: 480Max_data_length: 281474976710655   Index_length: 2048      Data_free: 0 Auto_increment: NULL    Create_time: 2012-07-02 18:21:30    Update_time: 2012-07-03 14:39:15     Check_time: NULL      Collation: utf8_bin       Checksum: NULL Create_options:　　Comment: Users a
　　
二、创建Mysql用户
　　创建用户并给其权限
　　grant all on 库名.* to 用户@localhost identified by '密码';
　　
　　创建远程用户
　　grant all privileges on *.* to sj@'%' identified by 'xywyclient123' with grant option;
　　example：
　　grant all privileges on ysq_xspace.* to clubuser@xxx.xxx.156.202 identified by &quot;clubuser&(*&quot;;
　　
　　删除用户
　　delete from user WHERE User=&quot;phplamp&quot; and Host=&quot;localhost&quot;;
　　flush privileges;
　　
　　设置用户访问权限和取消
　　grant all privileges on *.* to sum@192.168.0.97 identified by 'mima' with grant option;
　　use mysql;
　　update db set host = ''localhost' where user = ''用户名'';
　　//如果写成 host=localhost 那此用户就不具有远程访问权限
　　flush privileges;
　　
　　给多个数据库相同的用户权限
　　grant all on `cmsv53_%`.* to cmsdb_209@xxx.xxx.xxx209 identified by 'cmsv53_2010%^*'
　　// 这里使用`` 而不是’’引号，对所有cmsv53_开头的数据库给同一个用户权限。
　　
　　设置用户多权限
　　grant process,super on *.* to cacti@xxx.xxx.xxx.211 identified by 'cacti';
　　//将process和super权限赋予cacti
　　查询远程用户的权限 见十三也可。
　　use mysql;
　　select host,user,password from user; 这里列出来的用户的所有列表
　　show grants for root@localhost;
　　附加：下面详细介绍下mysql权限如果给用户多个权限 +“，” 即可。
　　一．权限表
　　mysql数据库中的3个权限表：user 、db、 host
　　权限表的存取过程是：
　　1)先从user表中的host、 user、 password这3个字段中判断连接的IP、用户名、密码是否存在表中，存在则通过身份验证；
　　2)通过权限验证，进行权限分配时，按照user&#61664;db&#61664;tables_priv&#61664;columns_priv的顺序进行分配。即先检查全局权限表
　　user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db,
　　tables_priv,columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检
　　查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推。
　　二．MySQL各种权限（共27个）
　　（以下操作都是以root身份登陆进行grant授权，以p1@localhost身份登陆执行各种命令。）
　　1.  usage
　　连接（登陆）权限，建立一个用户，就会自动授予其usage权限（默认授予）。
　　mysql> grant usage on *.* to ‘p1&prime;@’localhost’ identified by ‘123&prime;;
　　该权限只能用于数据库登陆，不能执行任何操作；且usage权限不能被回收，也即REVOKE用户并不能删除用户。
　　2.  select
　　必须有select的权限，才可以使用select  table
　　mysql> grant select on pyt.* to ‘p1&prime;@’localhost’;
　　mysql> select * from shop;
　　3.  create
　　必须有create的权限，才可以使用create  table
　　mysql> grant create on pyt.* to ‘p1&prime;@’localhost’;
　　4.  create routine
　　必须具有create  routine的权限，才可以使用{create |alter|drop} {procedure|function}
　　mysql> grant create routine on pyt.* to ‘p1&prime;@’localhost’;
　　当授予create routine时，自动授予EXECUTE, ALTER ROUTINE权限给它的创建者：
　　mysql> show grants for ‘p1&prime;@’localhost’;
　　+—————————————————————————+
　　Grants  for  p1@localhost
　　+————————————————————————–+
　　| GRANT USAGE ON *.* TO ‘p1&prime;@’localhost’ IDENTIFIED BY PASSWORD ‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257&prime; |
　　|  GRANT SELECT, CREATE, CREATE ROUTINE ON `pyt`.* TO ‘p1&prime;@’localhost’|
　　| GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `pyt`.`pro_shop1` TO ‘p1&prime;@’localhost’ |
　　+————————————————————————————-+
　　5.  create temporary tables(注意这里是tables，不是table)
　　必须有create temporary tables的权限，才可以使用create temporary tables.
　　mysql> grant create temporary tables on pyt.* to ‘p1&prime;@’localhost’;
　　[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt
　　mysql> create temporary table tt1(id int);
　　6.  create view
　　必须有create view的权限，才可以使用create view
　　mysql> grant create view on pyt.* to ‘p1&prime;@’localhost’;
　　mysql> create view v_shop as select price from shop;
　　7.  create user
　　要使用CREATE USER，必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
　　mysql> grant create user on *.* to ‘p1&prime;@’localhost’;
　　或：mysql> grant insert on *.* to p1@localhost;
　　8.  insert
　　必须有insert的权限，才可以使用insert into ….. values….
　　9.  alter
　　必须有alter的权限，才可以使用alter table
　　alter table shop modify dealer char(15);
　　10.  alter routine
　　必须具有alter routine的权限，才可以使用{alter |drop} {procedure|function}
　　mysql>grant alter routine on pyt.* to ‘p1&prime;@’ localhost ‘;
　　mysql> drop procedure pro_shop;
　　Query OK, 0 rows affected (0.00 sec)
　　mysql> revoke alter routine on pyt.* from ‘p1&prime;@’localhost’;
　　[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt
　　mysql> drop procedure pro_shop;
　　ERROR 1370 (42000): alter routine command denied to user ‘p1&prime;@’localhost’ for routine ‘pyt.pro_shop’
　　11.  update
　　必须有update的权限，才可以使用update table
　　mysql> update shop set price=3.5 where article=0001 and dealer=’A';
　　12.  delete
　　必须有delete的权限，才可以使用delete from ….where….(删除表中的记录)
　　13.  drop
　　必须有drop的权限，才可以使用drop database db_name;  drop table tab_name;
　　drop view vi_name;  drop index in_name;
　　14.  show database
　　通过show database只能看到你拥有的某些权限的数据库，除非你拥有全局SHOW DATABASES权限。
　　对于p1@localhost用户来说，没有对mysql数据库的权限，所以以此身份登陆查询时，无法看到mysql数据库：
　　mysql> show databases;
　　+——————–+
　　| Database |
　　+——————–+
　　| information_schema|
　　| pyt   |
　　| test   |
　　+——————–+
　　15.  show view
　　必须拥有show view权限，才能执行show create view。
　　mysql> grant show view on pyt.* to p1@localhost;
　　mysql> show create view v_shop;
　　16.  index
　　必须拥有index权限，才能执行[create |drop] index
　　mysql> grant index on pyt.* to p1@localhost;
　　mysql> create index ix_shop on shop(article);
　　mysql> drop index ix_shop on shop;
　　17.  excute
　　执行存在的Functions,Procedures
　　mysql> call pro_shop1(0001,@a)；
　　+———+
　　| article |
　　+———+
　　| 0001 |
　　| 0001 |
　　+———+
　　mysql> select @a;
　　+——+
　　| @a |
　　+——+
　　| 2 |
　　+——+
　　18.  lock tables
　　必须拥有lock tables权限，才可以使用lock tables
　　mysql> grant lock tables on pyt.* to p1@localhost;
　　mysql> lock tables a1 read;
　　mysql> unlock tables;
　　19.  references
　　有了REFERENCES权限，用户就可以将其它表的一个字段作为某一个表的外键约束。
　　20.  reload
　　必须拥有reload权限，才可以执行flush [tables | logs | privileges]
　　mysql> grant reload on pyt.* to p1@localhost;
　　ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
　　mysql> grant reload on *.* to ‘p1&prime;@’localhost’;
　　Query OK, 0 rows affected (0.00 sec)
　　mysql> flush tables;
　　21.  replication client
　　拥有此权限可以查询master server、slave server状态。
　　mysql> show master status;
　　ERROR 1227 (42000): Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation
　　mysql> grant Replication client on *.* to p1@localhost;
　　或：mysql> grant super on *.* to p1@localhost;
　　mysql> show master status;
　　+——————+———-+————–+——————+
　　| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
　　+——————+———-+————–+——————+
　　| mysql-bin.000006 | 2111 |    |     |
　　+——————+———-+————–+——————+
　　mysql> show slave status;
　　22.  replication slave
　　拥有此权限可以查看从服务器，从主服务器读取二进制日志。
　　mysql> show slave hosts;
　　ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation
　　mysql> show binlog events;
　　ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation
　　mysql> grant replication slave on *.* to p1@localhost;
　　mysql> show slave hosts;
　　Empty set (0.00 sec)
　　mysql>show binlog events;
　　+—————+——-+—————-+———–+————-+————–+
　　| Log_name | Pos | Event_type | Server_id| End_log_pos|Info |
　　+—————+——-+————–+———–+————-+—————+
　　| mysql-bin.000005 | 4 | Format_desc | 1 | 98 | Server ver: 5.0.77-log,
　　Binlog ver: 4 | |mysql-bin.000005|98|Query|1|197|use `mysql`; create
　　table a1(i int)engine=myisam|
　　……………………………………
　　23.  Shutdown
　　关闭MySQL：
　　[mysql@mydev ~]$ mysqladmin shutdown
　　重新连接：
　　[mysql@mydev ~]$ mysql
　　ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
　　[mysql@mydev ~]$ cd /u01/mysql/bin
　　[mysql@mydev bin]$ ./mysqld_safe &
　　[mysql@mydev bin]$ mysql
　　24.  grant option
　　拥有grant option，就可以将自己拥有的权限授予其他用户（仅限于自己已经拥有的权限）
　　mysql> grant Grant option on pyt.* to p1@localhost;
　　mysql> grant select on pyt.* to p2@localhost;
　　25.  file
　　拥有file权限才可以执行 select ..into outfile和load data infile…操作，但是不要把file, process, super权限授予管理员以外的账号，这样存在严重的安全隐患。
　　mysql> grant file on *.* to p1@localhost;
　　mysql> load data infile ‘/home/mysql/pet.txt’ into table pet;
　　26.  super
　　这个权限允许用户终止任何查询；修改全局变量的SET语句；使用CHANGE MASTER，PURGE MASTER LOGS。
　　mysql> grant super on *.* to p1@localhost;
　　mysql> purge master logs before ‘mysql-bin.000006&prime;;
　　27.  process
　　通过这个权限，用户可以执行SHOW PROCESSLIST和KILL命令。默认情况下，每个用户都可以执行SHOW PROCESSLIST命令，但是只能查询本用户的进程。
　　mysql> show processlist;
　　+—-+——+———–+——+———+——+——-+——————+
　　| Id | User | Host  | db   | Command | Time | State | Info  |
　　+—-+——+———–+——+———+——+——-+——————+
　　| 12 | p1 | localhost | pyt  | Query |  0 | NULL | show processlist |
　　+—-+——+———–+——+———+——+——-+——————+
　　另外，
　　管理权限（如 super， process， file等）不能够指定某个数据库，on后面必须跟*.*
　　mysql> grant super on pyt.* to p1@localhost;
　　ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
　　mysql> grant super on *.* to p1@localhost;
　　Query OK, 0 rows affected (0.01 sec)