Mysql命令详解

浏览数：20 / 时间：2015年06月12日

Mysql的所有命令都可以通过在线文档或者是通过help command方式来获得帮助。
以下说一些工作中经常用到的mysql操作命令。

[mysql数据库]
[descibe]语句:用来显示表的结构,即组成表的各字段(列)的信息.需要指定"库名.表名"作为参数

            mysql >use mysql;
            mysql >descibe user;

[create创建]
使用create创建用户

            mysql >create user ‘wangwu‘@‘192.168.1.%‘ identified by ‘123456‘;【创建用户王五，并限制到192.168.1.0网段】
            mysql >create database xianshang character set utf8 collate utf8_bin;【创建数据库，设置编码格式，设置排序规则】

           create table [表名] (字段1 名称 type,字段2 名称 type,.....,primary key (主健名))
[例如]
       创建users表,表中包含字段信息为username不超过16字节的字符串,userpassword不超过48字节的字符串,default用户设置默认的密码字符串为空,主健为username必须是唯一的.

            mysql >use auth;
            mysql >create talbe users (username char(16) not null,userpassword char(48) defautlt ‘‘,primary key (username));

[删除一个数据表/库]
drop table语句:用于删除库中的表,指定"库名.表名"作为参数

            mysql >drop table auth.users；
            mysql >drop database auth；

[插入数据记录]

      insert into用于向表中插入新的数据记录
       格式：insert into [表名]（字段1，字段2，....）values（字段1的值，字段2的值，....）
       [例如]
       向auth库的users表插入一条记录。

            mysql >use auth；
            mysql >insert into users(username,userpassword) values(‘zhangsan‘,password(‘123456‘))；

如果插入的记录包括表中的所有字段，则可省略字段部分

            mysql >insert into users values(‘zhangsan’，password(‘123456‘))；

[查询数据记录]
       select语句：表示所有字段时，可以用“*”此时where表达式可以省略。
       select 字段1，字段2，... from [表名] where 条件表达式

            mysql >select * from auth.users;
            mysql >select username,userpassword from auth.users where username=‘zhangsan‘;

[修改数据记录]
update语句：
       update 表名 set 字段名1=字段值1[，字段名2=字段值2] where 条件表达式
           mysql >update auth.users set userpassword=password(‘‘) where username=‘lisi‘;
在mysql数据库中的各种用户包括root的信息都保存在mysql的user表中，可以用update语句直接修改mysql库的user表的password字段值，也可以使用mysqladmin工具来设置密码。

[删除数据记录]
delete语句：
       delete from 表名 where 条件表达式

            mysql >delete from auth.users where username=‘lisi’；
            mysql >select * from auth.users;

mysql数据库中默认添加了从本机访问的空用户（用户名和密码都为空）基于安全考虑，应该删除这些空用户。

            mysql >select host,user,password from mysql.user where user=‘‘;
            mysql >delete from mysql.user where user=‘‘;

[维护mysql数据库]

[用户授权]
grant语句：当用户存在时，grant用于对用户信息修改，否则grant将创建新的用户

        grant 权限列表 on 库名.表名 to ‘用户名‘@‘来源地址‘ [identified by ‘密码‘;]
        mysql >grant select on auth.* to ‘wangfei‘@‘localhost‘ identified by ‘123‘;
        grant语句授权的用户记录会保存到mysql库的user，db，host，tables_priv等相关表中，无需刷新即可生效。

       来源地址可以是[(%代表任何)(域名/ip/网段)]
       例如："%.benet.com","192.168.88.%"等等。
       当省略[identified by]时，表示密码为空即可登录。

[查看授权]
       show grants语句：
       show grants for 用户名@来源地址

            mysql >show grants for ‘wangfei‘@‘192.168.88.109‘;

[撤销权限]
       revoke语句：
       revoke 权限列表 on 数据库名.表名 from 用户名@来源地址
       撤销张三用户从本机访问的所有权限如下：

            mysql >revoke all on auth.* from ‘zhangsan‘@‘localhost‘;
            mysql >show grants for ‘zhangsan‘@‘localhost‘;

[数据库的备份与恢复]

       备份可采用的方式有很多，比如可以打包数据库文件夹/var/local/mysql/var/或者使用专用的导出工具mysqldump
格式1：导出指定库中的部分表
       mysqldump [选项] 库名 [表名1] [表名2] ... > /备份路径/备份文件名
       将mysql库中的user表导出为mysql-user.sql

            mysql >mysqldump -u root -p mysql user  〉mysql-user.sql

格式2：导出一个或多个完整的库{包括其中的所有表}
mysqldump [选项] --databases 库名1 [库名2] ... > /备份路径/备份文件名
将整个auth库导出为auth.sql文件，都是以root用户进行身份验证。

            mysql >mysqldump -u root -p --databases auth >auth.sql

格式3：导出所有的库
mysqldump [选项] --all-databases > /备份路径/备份文件名
当需要备份整个mysql服务器中的所有库时，数据量相对较大，可以添加--opt选项一优化执行速度，例如：以下将创建备份文件all-data.sql其中包括mysql服务器的所有库。

        mysql >mysqldump -u root -p --opt --all-databases > all.-data.sql

       其中常用的选项包括-u，-p用来指定用户名和密码。

跨主机备份：
           下列的命令可以将host1上的sourceDB复制到host2上的targetDB，前提是host2上已经创建targetDB数据库。

            mysqldump --host=host1 --opt sourceDB | mysql --host=host2 -C targetDB

只备份表结构：

            mysqldump --no-data --databases mydatabase1 mydatabase2 mydatabase3 > test.sql

[查看备份文件内容]
通过mysqldump工具导出的sql脚本是文本文件，其中"/*..../"部分或以"--"开头的行表示注释信息。使用grep，less，cat等文本工具可以查看脚本内容。例如，执行以下操作可以过滤出auth.sql脚本中的数据库操作语句。

            [root@localhost ~]#grep -v "^--" auth.sql |grep -v "^/" |grep -v "^$"

[恢复数据库]
       mysql [选项] [库名] [表名] < /备份路径/备份文件名
       以下操作将表导入到库test

            [root@localhost ~]#mysql -u root -p test 〈 mysql-user.sql

以下操作将恢复所有的库

            [root@localhost ~]#mysql -u root -p 〈 ~/all-data.sql

------------------------------------------------------------------------------------------

【结合crontab命令实现定时备份】
每天的凌晨2:30备份某一个主机上的所有数据库并压缩dump文件为gz格式，crontab -e
30 2 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`date ‘+%m-%d-%Y‘`.sql.gz

前面五个参数分别表示分钟，小时，日，月，周，*表示任意，date ‘+%m-%d-%Y‘得到当前日期的MM-DD-YYYY格式。

未完待续。。。