用户管理

登录用户

mysql -h localhost -u root -p密码 -P 端口号 数据库名 -e 执行SQL语句
-p密码 中间不允许空格

创建用户

CREATE USER '名字'@'localhost' IDENTIFIED BY '密码'
CREATE USER '名字'@'%' IDENTIFIED BY '密码'

%号表示任何主机上都可以登录

创建用户推荐使用grant
GRANT select on myschool.grade to '名字'@'localhost' identified by '密码';
GRANT select,update,delete,insert on *.* to '名字'@'localhost' identified by '密码';

表示创建的用户可对全部表格进行增删改查

删除用户

DELETE FROM mysql.user where host='localhost' and user='名字';
使用grant创建后想删除用户
DROP USER 'tom'@'localhost';
DROP USER 'andy'@'localhost';
DROP USER 'sam'@'%';

修改root密码

方法1
mysqladmin -h localhost -u root -p password "新密码";
//新密码必须使用双引号；提示输入密码为旧密码。
flush privileges;//刷新权限表
方法2
root登录进去
update mysql.user set authentication_string=password("新密码") where user='root' and host='localhost';
flush privileges; //刷新权限表
方法3
root登录进去
SET PASSWORD = password('新密码');
flush privileges; //刷新权限表

修改普通用户密码

root用户登录进去
SET PASSWORD FOR '用户名'@‘主机’ = password("新密码")
FLUSH PRIVILEGES;
或
UPDATE mysql.user SET authentication_string=PASSWORD('888') WHERE user='tom' AND host='localhost';
FLUSH PRIVILEGES;
或
GRANT USAGE ON myschool.grade TO 'tom'@'localhost' IDENTIFIED BY 'tom123';
或者普通用户自己登录进去
SET PASSWORD = password("新密码")
FLUSH PRIVILEGES;

root用户密码遗失处理方法

1、停止mysql服务
如果mysql有注册为系统服务，使用命令net stop mysql停止服务。wamp则直接停止所有服务
2、使用mysqld --skip-grant-tables选项启动mysql
在Linux系统中，使用mysqld_safe --skip-grant-tables user=mysql启动。
初次使用，windows防火墙会提示是否允许该进程访问网络，选择允许。
3、另外打开一个命令行窗口，使用root空密码登录，执行UPDATE命令，更新user表的密码
update mysql.user set authentication=password("新密码") where user='root' and host='localhost';
4、刷新权限表
flush privileges;

设置权限

GRANT（授权）和REVOKE（取消授权）语句所涉及的权限名称如下表所示：

例子：
grant select,insert on . to ‘用户名’@’localhost’ identified by ‘123’ with grant option;
grant all on myschool.* to ‘用户名’@’localhost’ with max_connections_per_hour 50;
grant update(studentName) on myschool.student to ‘用户名’@’localhost’; //授权用户对myschool.student表的studentName字段有UPDATE权限
| MAX_QUERIES_PER_HOUR count #设置每小时可以执行count次查询
| MAX_UPDATES_PER_HOUR count #设置每小时可以执行count次更新
| MAX_CONNECTIONS_PER_HOUR count #设置每小时可以建立count个连接
| MAX_USER_CONNECTIONS count #设置单个用户可以同时建立count个连接

收回权限

收回全部权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
收回指定权限
REVOKE INSERT ON *.*,grant option FROM 'test'@'localhost';
查看用户权限
show grants for '用户名'@'localhost';或 select select_priv,insert_priv,grant_priv from mysql.user where user='用户名' and host='localhost';

数据备份和恢复

数据备份

备份整个数据库
mysqldump -u root -p密码 备份的数据库>d:/myschool_20180620.sql
备份单张表
mysqldump -u root -p密码 备份的数据库 student>d:/student_20180620.sql
备份多个数据库
mysqldump -u root -p密码 --databases 备份的数据库1 数据库2>d:/db_20180620.sql
备份整个数据库
mysqldump -u root -p密码 --all--databases>d:/db_20180620.sql

恢复备份

先创建数据库，再执行以下代码
mysql -u root -p密码 备份的数据库<d:/db_20180620.sql

数据迁移

将A主机上的MySQL数据库全部迁移到B主机上
mysqldump -h www.a.com -uUser -pPassword DBName | mysql -h www.b.com -uUser - pPassword
如果要全部迁移，使用—all-databases参数。

表的导出与导入

使用select导出表
S``ELECT 字段 FROM 表名 WHERE 条件 INTO OUTFILE '文件路径' [选项]
注意实现：需要修改my.ini的“secure-file-priv=路径名”，MySQL只支持导出到指定目录。
或使用MySQL命令导出文本文件
mysql -uUser -pPassword --execute="SELECT 语句" DBName >FileName.txt

使用LOAD DATA INFILE导入文本文件
LOAD DATA INFILE 'filename.txt' INTO TABLE TableName
或使用MySQLimport命令导入文本文件
mysqlimport -uUser -pPassword DBName fileName.txt

日志管理

二进制日志开启的设置
在my.ini配置文件的[mysqld]节点添加以下配置信息：

; 二进制日志设置开始
; 开启二进制日志，默认存放在mysql/data中
log-bin
;如果需要修改二进制日志文件的存储位置和名称，则修改my.ini配置文件中log-bin的值：
log-bin="D:/logs/binlog"
;重启mysql服务后，日志将保存在“D:/logs”目录中，并且文件名为“binlog”。
; 自动清除10天过期日志。MySQL服务重启或者日志刷新时候将被删除。
expire-logs-days=10
; 单个日志文件大小
max_binlog_size=100M
;二进制日志设置结束

查看日志是否开启
登录之后输入 show variables like 'log_%';
查看日志文件文件名和大小
登录之后 show binary logs;
查看日志文件内容
查看之前需要注释掉my.ini中[client]下的utf8设置
无需登录 输入 mysqlbinlog -v日志路径

用二进制日志恢复数据
1、首先进入二进制文件盘 d:
2、全部恢复：mysqlbinlog d:/mysqlLogs/binLogs/log.000001 | mysql -u root -p123
3、根据时间恢复：
mysqlbinlog --stop-datetime="2018-06-20 15:22:30" d:/mysqlLogs/binLogs/log.000001 | mysql -u root -p123
—start-datetime：指定恢复数据库的起始时间点。
—start-position：恢复的开始位置（日志中的位置）。
—stop-position：恢复的结束位置。
暂时启动和停止二进制进程
登录之后 set sql_log_bin=1 //启动 set sql_log_bin=0//停止

删除二进制日志
删除全部
登录之后 reset master;
删除某个之前
登录之后 purge master logs to "log.0000005" //会删除5之前的全部日志

事件

查看事件是否开启三种方式
SHOW VARIABLES LIKE 'event_scheduler';
SELECT @@event_scheduler;
SHOW PROCESSLIST;— 查看mysql服务器在运行线程

开启事件
SET GLOBAL event_scheduler = ON; //重启后失效
my.ini文件的[mysqld]部分添加：event_scheduler=ON

# 创建一个每隔10秒往test表中插入一条数据的事件（立刻开始运行）
DROP EVENT IF EXISTS ev_insert_data;
CREATE EVENT IF NOT EXISTS ev_insert_data 
   ON SCHEDULE EVERY 10 SECOND
   ON COMPLETION PRESERVE -- 表示事件重复
   DO INSERT INTO etest(id,data) VALUES(DEFAULT,NOW());
# 创建一个1分钟后清空test表数据的事件
DROP EVENT  IF EXISTS  ev_clear_data;
CREATE EVENT IF NOT EXISTS ev_clear_data
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
    DO TRUNCATE TABLE etest; 
# 某一时刻清除数据
DROP EVENT  IF EXISTS  ev_clear_data_attime;
CREATE EVENT IF NOT EXISTS ev_clear_data_attime
    ON SCHEDULE AT TIMESTAMP '2018-10-10 00:00:00'
    DO TRUNCATE TABLE etest; 
# 某个时间段执行事件
DROP EVENT IF EXISTS ev_do_between_time;
CREATE EVENT IF NOT EXISTS ev_do_between_time
    ON SCHEDULE EVERY 3 SECOND
       STARTS '2018-08-22 21:49:00'
       ENDS '2018-08-22 21:49:00'+ INTERVAL  10 MINUTE
    ON COMPLETION PRESERVE
    DO INSERT INTO etest(id,data) VALUES(DEFAULT,NOW());
# 调用存储过程(一分钟后转账)
DROP EVENT IF EXISTS ev_call_trans_proc;
CREATE EVENT IF NOT EXISTS ev_call_trans_proc
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
    DO call transamount

存储过程

视图

索引

全文索引

• MySQL 5.6以前的版本，只有MyISAM存储引擎支持全文索引；
• MySQL 5.6及以后的版本，MyISAM 和 InnoDB存储引擎均支持全文索引;
• 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

select * from fulltext_test where match(content,tag) against('xxx');