Mysql数据库常用操作

1.登录数据库

格式： mysql -h主机地址 -u用户名 －p用户密码–P端口 –D数据库–e “SQL 内容”

常用：mysql -uroot -p 数据库名称

2.选择数据库

常用：use 数据库名称

3.修改用户密码

方法一: 
(适用于管理员或者有全局权限的用户重设其它用户的密码)
进入命令行模式
mysql -u root -p
 
mysql>use mysql;
mysql> update user set authentication_string=password("gsyg123abc")where user="mtkj_test";

mysql> FLUSH PRIVILEGES;
mysql> flush privileges;
 
方法二:
mysql -u root -p 
mysql>use mysql; 
mysql> SET PASSWORD FOR   username=PASSWORD('new password');
mysql> QUIT
 方法三:
 mysqladmin -u root "old password" "new password"
注：new password请输入你想要设置的密码。

 

4.添加用户并授权

添加用户:

语法:create user [用户名]@[IP] identifiedby [密码];

例：

create user 'test'@'localhost' identified by'test';

create user 'test'@'%' identified by 'test';

create user 'test'@'192.168.1.1' identified by'test';

授权：

§  授权test用户拥有testDB数据库的所有权限（某个数据库的所有权限）：

　   格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";　　

     例：

     mysql>grant allprivileges on testDB.* to test@localhost identified by '1234';

 　　mysql>flush privileges;      //刷新系统权限表

§  指定部分权限给一用户，可以这样来写:

　　mysql>grant select,update on testDB.* totest@localhost identified by '1234';

　　mysql>flush privileges; //刷新系统权限表

§  授权test用户拥有所有数据库的某些权限： 　 

　　mysql>grant select,delete,update,create,drop on *.*to test@"%" identified by "1234";

     //test用户对所有数据库都有select,delete,update,create,drop 权限。

　 //@"%" 表示对所有非本地主机授权，不包括localhost。（localhost地址设为127.0.0.1，如果设为真实的本地地址，不知道是否可以，没有验证。）

//对localhost授权：加上一句grant all privileges on testDB.* to test@localhostidentified by '1234';即可。

撤销授权：

命令: REVOKE privilege ONdatabasename.tablename FROM 'username'@'host'; 
说明: privilege,databasename, tablename - 同授权部分. 
例子: REVOKESELECT ON *.* FROM 'pig'@'%'; 
注意: 假如你在给用户'pig'@'%'授权的时候是这样的(或类似的):GRANT SELECT ONtest.user TO 'pig'@'%', 则在使用REVOKE SELECT ON *.* FROM 'pig'@'%';命令并不能撤销该用户对test数据库中user表的SELECT 操作.相反,如果授权使用的是GRANT SELECTON *.* TO 'pig'@'%';则REVOKE SELECT ON test.user FROM 'pig'@'%';命令也不能撤销该用户对test数据库中user表的Select 权限. 
具体信息可以用命令SHOW GRANTSFOR 'pig'@'%'; 查看. 

5.删除用户

mysql>Delete FROM user Where User='test' and Host='localhost';

 mysql>flush privileges;       //刷新系统权限表

 mysql>drop database testDB; //删除用户的数据库

删除账户及权限：

>drop user 用户名@'%';

>drop user 用户名@ localhost; 

6.创建表

createtable s_position

(

idint not null auto_increment,

namevarchar(20) not nulldefault '经理',  #设定默认值

descriptionvarchar(100),

primarykey PK_positon (id) #设定主键

);

7.添加字段

alter table 表名 add column字段名 类型(int、varchar等) [not Null] [comment '注释'];

8.修改字段

修改字段名称：

alter table 表名称 change 原字段名称 新字段名称 字段类型 [是否允许非空];

修改字段类型或长度：

altertable 表名 modify column 字段名类型;

9.删除字段

ALTER TABLE 表名 DROP 字段名;

10.添加索引

添加PRIMARY KEY（主键索引）：

ALTERTABLE 表名 ADD PRIMARY KEY ( `字段名` );

添加UNIQUE(唯一索引或约束):

ALTERTABLE 表名ADD UNIQUE ( `字段名` );

添加INDEX(普通索引):

ALTERTABLE `表名` ADD INDEX index_name ( `字段名` );

添加FULLTEXT(全文索引):

ALTERTABLE `表名 ` ADD FULLTEXT ( `字段名`);

添加多列索引:

ALTERTABLE `表名` ADD INDEX index_name ( `字段名1`, `字段名2`, `字段名3` );

11.mysql查询缓存

开启查询缓存：

在my.cnf文件中添加如下配置：

query_cache_type= 1     # 0代表关闭查询缓存OFF，1代表开启ON，2（DEMAND）代表当sql语句中有SQL_CACHE关键词时才缓存

query_cache_size= 600000

查询缓区的碎片整理:

查询缓存使用一段时间之后，一般都会出现内存碎片，为此需要监控相关状态值，并且定期进行内存碎片的整理，碎片整理的操作语句：FLUSH QUERYCACHE；

清空查询缓存的数据:
那些操作操作可能触发查询缓存，把所有缓存的信息清空，以避免触发或需要的时候，知道如何做，二者可触发查询缓存数据全部清空的命令：
1. RESET QUERY CACHE；
2. FLUSH TABLES；

12.创建数据库

CREATEDATABASE 数据库名称 DEFAULTCHARACTER SET utf8 COLLATE utf8_general_ci

 

 

13.删除数据库

dropdatabase 数据库名称

 

14.备份（库或表）

mysqldump –hlocalhost -u root -p 数据库名 [表名] > xxx.sql

一次备份多个数据库：

mysqldump-uroot -p123456 --databases db1 db2 > xxx.sql

备份数据同时备份存储过程、函数、触发器

mysqldump -uroot -p123456 --single-transaction --routines --triggers 数据库名称 > xxx.sql

15.恢复（库或表）

mysql -u root -p[dbname][tablename] < xxx.sql

16.存储过程

执行权限问题：

       方法一：修改存储过程的definer

       update mysql.proc setdefiner='root@localhost' where db='db_name';

方法二：修改sql security

       sql secuirty的值决定了调用存储过程的方式，取值 ：definer或者invoker

       definer:在执行存储过程前验证definer对应的用户如：root@192.168.2.%是否存在，以及是否具有执行存储过程的权限，若没有则报错

        invoker:在执行存储过程时判断inovker即调用该存储过程的用户是否有相应权限，若没有则报错。

       修改语法：

        alter procedure pro_name sql securityinvoker;

17.修改数据库表编码方式和排序规则

ALTERTABLE 表名DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

18.主从复制

主库配置：

在my.cnf[mysqld]下配置

server_id= 3

#log_bin=mysql-bin

#binlog-do-db=yijiayi #表示需要同步的数据库名字

#binlog-ignore-db=information_schema #表示不需要同步的数据库名字

设置完毕，命令行输入：show master status;

创建用于主从的用户并授权。

从库配置：

在my.cnf[mysqld]下配置

server_id= 2

relay_log         = mysql-relay-bin

log_slave_updates= 1

read_only         = 1

#replicate-ignore-db= yijiayi

#replicate-ignore-db= information_schema

在mysql命令行中输入：

CHANGEMASTER TOMASTER_HOST='192.168.1.170',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=513;

19查看用户权限

show grants for 'cactiuser'@'%';