mysql

一、Mysql安装与连接

1.mysql5.7 修改root用户默认密码：https://www.cnblogs.com/halberd-lee/p/8534945.html

方法一：
[mysqld]
skip-grant-tables  //跳过密码验证，添加后可以用空密码登陆，改完记得去掉这行

systemctl restart mysqld(无效，需要在bt里操作)
mysql> update mysql.user set authentication_string=password('123') where user='root';
mysql> flush privileges; 
mysql> exit;

方法二：bt里数据库管理，修改root密码。

2.mysql5.7 开启远程连接：https://blog.csdn.net/sun614345456/article/details/53672150

首先确认防火墙3306端口是打开的

方法一：
mysql -uroot -p
use mysql;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES; 
service mysqld restart

方法二：bt里安装phpmyadmin,用phpmyadmin新建用户并设置远程访问权限。

二、Mysql日志处理（所有设置可以在phpmyadmin里操作）

通用查询和慢查询：https://mp.weixin.qq.com/s/I4Bw0qA-Rvtt38GPxpgmEg

通用
show variables like '%log%'; //显示与日志相关配置
show variables like '%quer%'; //mysql相关配置
show variables like '%log_output%'; //查看日志输出方式

show variables like '%version%';

set global general_log=on; //临时开启通用日志查询
set GLOBAL slow_query_log=on; //开启慢查询日志
set GLOBAL long_query_time=1; //设置慢查询阈值
set global log_output='FILE,TABLE'; //设置通用日志输出为表和文件方式

永久配置my.cnf文件格式如下
general_log=1 #为1表示开启通用日志查询，值为0表示关闭通用日志查询
general-log-file=/www/server/data/mysql-general.log
log_output=FILE,TABLE#设置通用日志的输出格式为文件和表

server-id = 1 
log-bin=mysql-bin  //这两行在mysql5.7中打开二进制日志  
binlog_format=mixed

expire_logs_days = 10
slow_query_log=1  //慢日志
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=1
#log_queries_not_using_indexes=on
early-plugin-load = ""

慢查询日志
#主要掌握以下的几个参数：
（1）slow_query_log 的值为ON为开启慢查询日志，OFF则为关闭慢查询日志。
（2）slow_query_log_file 的值是记录的慢查询日志到文件中（注意：默认名为主机名.log，慢查询日志是否写入指定文件中，需要指定慢查询的输出日志格式为文件，相关命令为：show variables like ‘%log_output%’；去查看输出的格式）。
（3）long_query_time 指定了慢查询的阈值，即如果执行语句的时间超过该阈值则为慢查询语句，默认值为10秒。
（4）log_queries_not_using_indexes 如果值设置为ON，则会记录所有没有利用索引的查询（注意：即该设置生效的前提是slow_query_log的值设置为ON），一般在性能调优的时候会暂时开启。

利用bin-log二进制日志文件恢复数据：

https://blog.csdn.net/RAYFUXK/article/details/84828090  //MySQL5.7 开启bin-log功能

https://www.fujieace.com/mysql/bin-log.html  //MYSQL二进制日志bin-log开启、查看、使用（恢复数据）详解

https://www.cnblogs.com/suixinpeng/p/mysqlbinlog.html  //mysql使用二进制日志恢复数据

https://blog.csdn.net/u010002184/article/details/78996283  //mysql查看binlog日志内容

https://blog.csdn.net/zyz511919766/article/details/38089393  //使用mysqlbinlog工具进行基于位置或时间点的数据恢复

mysql配置文件：
log-bin=E:/Mysql57/BinLog/binlog(日志路径和文件前缀名binlog，如果只写前缀名，日志保存在data目录下) 
expire_logs_days=10  //保存最近10天的日志，超过这个时间的会被删掉 https://blog.csdn.net/shaochenshuo/article/details/51513504
max_binlog_size=100M //设置完重启生效

mysql > show VARIABLES like '%log_bin%';  
mysql > SHOW MASTER STATUS;  //查看当前二进制文件的名称和路径
mysql > reset master;   //清空所有的bin-log日志
mysql > flush logs;   //会多一个最新的bin-log日志
mysql > show binlog events in 'mysql-bin.000011';  //查看日志文件中的事务

shell > ./mysqlbinlog /www/server/data/mysql-bin.000011 //查看日志内容
shell > /www/server/mysql/bin/mysqlbinlog /www/server/data/mysql-bin.000011 //查看日志内容2

shell > ./mysqlbinlog --no-defaults --start-position="4" --stop-position="498" /www/server/data/mysql-bin.000006  | mysql -u root -p  //把myssql-bin.000006里的位置4到位置498的语句再执行一次

 常用指令

mysql > flush logs; 会多一个最新的bin-log日志
show master status; 查看最后一个bin-log日志的相关信息
reset master; 清空所有的bin-log日志

三、分析sql查询

explain select * from tablename;

show full processlist;
kill queryid;

 四、mysql的between的边界，范围

SELECT * FROM `test` where id BETWEEN 3 and 7;
等价于 SELECT * FROM `test` where id>=3 and id<=7;
-----------------------------------------------------------
SELECT * FROM `test` where id NOT BETWEEN 3 and 7;
等价于 SELECT * FROM `test` where id<3 or id>7;

五、mysql导出导入数据

1、navicat导出向导导出sql会比转存sql慢。

2、导入数据前先把目标表的索引删除，导入数据后再把索引加上，可以直接把索引复制过来。

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

三、MySQL INTO OUTFILE/INFILE导出导入数据：https://www.cnblogs.com/bbcar/p/3830891.html  (执行起来太慢了)

SELECT * INTO OUTFILE 'E:\datatest\user.txt' FIELDS TERMINATED BY ',' FROM user;

1.SQL中Truncate的用法: https://www.cnblogs.com/zhoufangcheng04050227/p/7991759.html

当你不再需要该表时， 用 drop；当你仍要保留该表，但要删除所有记录时， 用 truncate；当你要删除部分记录时（always with a WHERE clause), 用 delete.

2.mysql优化文章集合：https://blog.csdn.net/ma15732625261/article/month/2018/08/4

3.我必须得告诉大家的MySQL优化原理: https://www.jianshu.com/p/d7665192aaaf

 

4.索引 ---- 最左前缀的解释

在很多数据库中，当创建多列的索引的时候，会出现最左前缀的现象。
假设有如下表： create table AAA (a varchar2(32) ,b varchar2(32),c date);

在a和c列上建普通索引： create index iN_AAA_1 on AAA (a, c);
1.索引左前缀性的第一层意思：必须用到索引的第一个字段。select * from AAA where b=:xxx and c=sysdate;则不会用到索引，因为必须有a出现在where 语句中才会使用到该索引。
2.索引前缀性的第二层意思：对于索引的第一个字段，用like时左边必须是固定值，通配符只能出现在右边。select * from AAA where a like '1%';会用到索引;而select * from AAA where a like '%1';不会用到索引。
3.索引前缀性的第三层意思：如果在字段前加了函数，则索引会被抑制，例如：select * from aaa where trim(a)=1,则不会用到索引。
在字段前嵌入了表达式，索引也将被抑制。假设a是date格式的，那么where a+7<sysdate将不会用到索引，而where a<sysdate-7会用到索引。
还有两个特殊声明：
1).select * from AAA where a=:xxx and c=sysdate与 select * from AAA where c=sysdate and a=:xxx;都会用到索引，即与where语句中字段出现的顺序无关；
2).select * from AAA where a=:xxx and b=1;会使用索引，此时A出现，即使其他字段不是索引字段也会使用到索引。

mysql 教程：https://www.runoob.com/php/func-mysqli-fetch-all.html

<?php  
// 假定数据库用户名：root，密码：123456，数据库：RUNOOB  
$con=mysqli_connect("localhost","root","123456","RUNOOB");  
if (mysqli_connect_errno($con))  
{  
    echo "连接 MySQL 失败: " . mysqli_connect_error();  
}  

$sql="SELECT name,url FROM websites ORDER BY alexa"; 
$result=mysqli_query($con,$sql); 

// 获取数据 
mysqli_fetch_all($result,MYSQLI_ASSOC); 

// 释放结果集 
mysqli_free_result($result); 

mysqli_close($con); 
?>

 mysqli操作类：joshcam/mysqli-database-class(不好用，有bug)

https://packagist.org/packages/joshcam/mysqli-database-class

 mysql里的ibdata1文件:

https://www.cnblogs.com/mikeguan/p/8018531.html

解决同步测试数据库结构到本地时函数无法导入的问题 ：This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable

不改配置时：set global log_bin_trust_function_creators=TRUE;
改配置时：log_bin_trust_function_creators=1