mysql笔记
过去初学mysql的笔记，在此存一份在线文档
文章目录
一条sql的执行过程
连接数据库（如python的MySQLdb程序代码、api、客户端），创建起来通道连接，在数据库看来是一个空闲状态，创建好通道就可以向数据库服务器发送sql语句，不同的sql语句进入不同的接口（比如查询数据到一个接口，存储过程的sql就到另一个接口），然后开始解析（sql、权限），解析完了开始优化sql怎么去执行（比如是否使用索引、使用哪个索引），执行的时候会校验sql要查询的数据有没有缓存，因为mysql服务本身会占用大量的内存，会检查下内存中是否已经有数据，如果有缓存就返回来，如果没有缓存，就按引擎的规则来查询数据，查到后引擎把数据返回来。
如果是做更新也是在引擎做数据结构的变更，变更后再返回结果。
引擎决定数据怎么存储、怎么去使用，真正的存储是在物理磁盘上。
修改密码
5.6以上的安装完都会有个随机密码，存放在/root/.mysql_secret文件里
[root@host2 kpc]# cat /root/.mysql_secret
# The random password set for the root user at Sun Mar 3 23:47:03 2019 (local time): VzHDrhzu0QBw9sWw
首次登录，执行show databases的时候会要求重置随机密码
重置密码
set password=password(‘root’); 修改当前用户的密码（修改指定用户的密码SET PASSWORD FOR user = PASSWORD(‘some password’)）
mysql中账号密码信息存在mysql库下面的user表中
select * from mysql.user where user=‘root’ and host=‘localhost’\G;
修改了权限不生效，需要刷新权限
flush privileges;
mysqld
--basedir=/usr 软件或者服务安装路径
--datadir=/var/lib/mysql 数据存放路径（相当于下载视频的时候缓存的位置）
--plugin-dir=/usr/lib64/mysql/plugin
--user=mysql 启动账号，表示用mysql启的
--log-error=localhost.localdomain.err 错误日志，可见/etc/init.d/mysql
--pid-file=/data/mysql/localhost.localdomain.pid 进程号
--socket=/data/mysql/mysql.sock 本机登录需要用到，登录成功就会在数据路径（$datadir）下创建一个mysql.sock
启动文件
/etc/init.d/mysql
basedir=
datadir=
service_startup_timeout=900
lockdir='/var/lock/subsys'
lock_file_path="$lockdir/mysql"
mysqld_pid_file_path=
# $basedir软件或者服务安装路径
if test -z "$basedir"
then
basedir=/usr
bindir=/usr/bin
# 如果变量$datadir（数据存放路径）的值为空（test -z判断字符串长度为0），就赋值/var/lib/mysql
if test -z "$datadir"
then
datadir=/var/lib/mysql
fi
sbindir=/usr/sbin
libexecdir=/usr/sbin
else
bindir="$basedir/bin"
if test -z "$datadir"
then
datadir="$basedir/data"
fi
sbindir="$basedir/sbin"
libexecdir="$basedir/libexec"
fi
......
修改端口
先看下当前端口
[root@host2 mysql]# netstat -tanlp |grep -i mysql
tcp6 0 0 :::3306 ::😗 LISTEN 3194/mysqld
或者
默认的my.cnf没有指定端口，默认为3306，ps后面也不会显示port，指定了port，ps的时候后面就会显示port
show命令
mysql> show global variables\G; mysql的全局变量有440个（可配置）
mysql> show global variables like "%path%";
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| ssl_capath | |
| ssl_crlpath | |
+-----------------------+------------------------+
3 rows in set (0.00 sec)
使用sql语句看参数的配置，数据库都有存变量和值
mysql> show global variables like "%datadir%"
-> ;
+---------------+------------------+
| Variable_name | Value |
+---------------+------------------+
| datadir | /home/kpc/mysql/ |
+---------------+------------------+
1 row in set (0.01 sec)
mysql> show global variables like "%port%";
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_support_xa | ON |
| large_files_support | ON |
| port | 3306 |
| report_host | |
| report_password | |
| report_port | 3306 |
| report_user | |
+---------------------+-------+
7 rows in set (0.00 sec)
配置数据库的字符集为utf8编码
mysql> show global variables like "%char%";
+--------------------------+----------------------------+
| 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 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
在my.cnf的mysqld配置character_set_server = utf8
my.cnf
默认在安装目录下（basedir）/usr,是启动时会加载的配置文件。
mysql服务启不来，要注意这几点：
1.my.cnf里有么有[mysqld]的参数配置
2.setenforce 0; 关闭SELinux安全检查（强制使用默认参数，比如端口改了3307那就启动不了，一般都会关闭）----这是临时关闭，重启后会恢复回去
永久关闭：vi /etc/selinux/config
SELINUX=enforcing 修改为 SELINUX = disabled
服务能起来，但是本地登录的时候报错：…sock：
建议安装完mysql，直接把安装路径下的my.cnf移到/etc下，原因可以看/etc/init.d/mysql，启动的时候读取参数配置的判断逻辑
因为sock文件找不到登录不进去，解决办法
法1：直接把安装路径下的my.cnf移到/etc下
法2：先看下服务是否正常，然后找sock文件在哪里find / -name mysql.sock，比如在xxxx，然后在登录的时候跟上-S .sock文件路径
mysql -uroot -p1234 -S xxxx
$datadir目录下的文件解释
一个目录对应数据库服务里的一个库
auto.cnf 服务启动的时候自动生成，存一个uuid，相当于服务的id
ibdata1 共享表文件，回滚日志（事务日志）、快照数据
ib_logfile0 、ib_logfile1 事务日志，存变更记录，和回滚段一一对应，初始化大小，循环大小
host2.err 错误日志，以主机名定义
host2.pid 存服务的进程号
mysql 对应的是数据库服务里的mysql库，数据库中的一个Myisam引擎的表对应目录下的三个物理文件：表.frm（格式文件）,表.MYD（数据文件）,表.MYI（索引文件）。
Myisam 引擎的表，一个表对应三个物理文件，*.frm *.MYD *.MYI
Innodb 引擎的表，一个表对应两个物理文件，*.frm ，*.ibd
mysql.sock 套接字文件，登录的时候会生成
[root@host2 mysql]# file mysql.sock
mysql.sock: socket
performance_schema 对应的是数据库服务里的performance_schema库
test 对应的是数据库服务里的test库
RPM_UPGRADE_HISTORY、RPM_UPGRADE_MARKER-LAST 非mysql内容，是打RPM包的时候生成的，可以删除
mysql log
事务日志redo_log，回滚日志undo_log -->（存在ibdata1 ）
general log 全日志,会记录数据库的所有操作，在排查问题时可以打开一段时间（一定要记得关），可清理？，要注意：1，会消耗一定性能，2，文件会很大
mysql> show global variables like "%general%";
+------------------+---------------------------+
| Variable_name | Value |
+------------------+---------------------------+
| general_log | OFF |
| general_log_file | /home/kpc/mysql/host2.log |
+------------------+---------------------------+
2 rows in set (0.00 sec)
mysql> set global general_log=on;
Query OK, 0 rows affected (0.00 sec)
打开general_log开关，$datadir目录会有一个host2.log文件
做一个操作，查看下host2.log
mysql> select "huanghuang";
+------------+
| huanghuang |
+------------+
| huanghuang |
+------------+
1 row in set (0.00 sec)
[root@host2 mysql]# vi host2.log
/usr/sbin/mysqld, Version: 5.6.42 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /home/kpc/mysql/mysql.sock
Time Id Command Argument
190307 16:19:35 1 Quit
190307 16:19:51 2 Connect root@localhost on
2 Query select @@version_comment limit 1
190307 16:20:06 2 Query show global variables like "%general%"
190307 16:27:00 2 Query select "huanghuang"
Slow log 慢日志，记录执行时间超过一定阈值的SQL，抓下来查询慢的sql，做优化
1.查看慢日志开关
mysql> show global variables like "%slow%";
+---------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /home/kpc/mysql/host2-slow.log |
+---------------------------+--------------------------------+
5 rows in set (0.00 sec)
2.打开慢查询开关
mysql> set slow_query_log=on;
ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)
查看慢查询开关已开启
mysql> show global variables like "%slow%";
+---------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /home/kpc/mysql/host2-slow.log |
+---------------------------+--------------------------------+
5 rows in set (0.00 sec)
3.查看当前超时阈值
mysql> show global variables like "%long%"; 单位秒
+--------------------------------------------------------+-----------+
| Variable_name | Value |
+--------------------------------------------------------+-----------+
| long_query_time | 10.000000 |
| performance_schema_events_stages_history_long_size | 10000 |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_waits_history_long_size | 10000 |
+--------------------------------------------------------+-----------+
4 rows in set (0.00 sec)
5.设置阈值
mysql> set global long_query_time=1; 设置阈值为1秒，超过1秒的记录到慢日志
Query OK, 0 rows affected (0.00 sec)
执行一条2秒的sql
mysql> select sleep(2);
+----------+
| sleep(2) |
+----------+
| 0 |
+----------+
1 row in set (2.00 sec)
可以看到这条sql会被记录到慢日志
Binlog
二进制日志，最主要的作用是构建主从复制，记录的数据库的变化
主从复制：
A,B为30L水槽，B是隐藏的，加水、取水都在A操作，A的所有变化（没有发生变化的不会记录），B都会接受一次，B要与A保持一致就是依靠binlog，Binlog 可以当做一个A上面的账本，B会按照binlog账本依次做一次，但是binlog是有时间限制的，只保留一定时间内的记录。
mysql> show global variables like "%bin%";
log_bin只能在配置文件（my.cnf）修改，无法在mysql通过set global log_bin=on来修改，所以需要重启生效。
mysql> set global log_bin=on;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
修改配置文件并重启mysql
[root@host2 mysql]# vi /etc/my.cnf
[mysqld]
port = 3306
datadir = /home/kpc/mysql
socket = /home/kpc/mysql/mysql.sock
character_set_server = utf8
log_bin = /home/kpc/mysql/mysql-bin
再到mysql服务里查看log_bin变量
mysql> show global variables like "%log%";
此时再看（$datadir）/home/kpc/mysql下会有多出两个文件
[root@host2 mysql]# pwd
/home/kpc/mysql
[root@host2 mysql]# ll
total 110632
-rw-rw----. 1 mysql mysql 56 Mar 3 23:50 auto.cnf
-rw-rw----. 1 mysql mysql 6324 Mar 7 17:11 host2.err
-rw-rw----. 1 mysql mysql 2509 Mar 7 17:03 host2.log
-rw-rw----. 1 mysql mysql 6 Mar 7 17:11 host2.pid
-rw-rw----. 1 mysql mysql 575 Mar 7 16:47 host2-slow.log
-rw-rw----. 1 mysql mysql 12582912 Mar 7 17:11 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Mar 7 17:11 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Mar 3 23:47 ib_logfile1
drwx--x--x. 2 mysql mysql 4096 Mar 3 23:47 mysql
-rw-rw----. 1 mysql mysql 120 Mar 7 17:11 mysql-bin.000001
-rw-rw----. 1 mysql mysql 33 Mar 7 17:11 mysql-bin.index
srwxrwxrwx. 1 mysql mysql 0 Mar 7 17:11 mysql.sock
drwx------. 2 mysql mysql 4096 Mar 3 23:47 performance_schema
drwxr-xr-x. 2 mysql mysql 6 Mar 3 23:46 test
mysql-bin.000001怎么查看？
查看方式：mysqlbinlog +binlog
mysqlbinlog mysql-bin.000001
SET xxx 的都是环境变量
下面建一个数据库，然后查看mysql-bin.000001，可以看到日志记录（如果是查询，数据库数据没有发生变化，不会记录日志）
mysql> create databases tttt;
mysql> drop database tttt; 记录前面会有一个时间戳（秒级）
注意：以上set的值，重启服务器后就失效了，因为服务启动会重新加载my.cnf。set global 变量名=xxx 对全局有效，但是对当前连接无效，需要重新登录mysql。
set 变量名=xxx 对当前连接有效，不需要重新登录。
要想持久化，set后还要在my.cnf加上。平时工作中一般按全局来设置，然后修改配置文件来实现持久化。
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 322 |
| mysql-bin.000002 | 143 |
| mysql-bin.000003 | 167 |
| mysql-bin.000004 | 143 |
| mysql-bin.000005 | 701 |
+------------------+-----------+
5 rows in set (0.00 sec)
mysqladmin
mysql管理命令比如修改密码…
免密登录
忘记了密码，可以修改my.cnf，实现免密登录，但是进去后有些命令会无法执行，比如：
mysql> set password=password('1234');
ERROR 1290 (HY000): The MySQL server is running with th
e --skip-grant-tables option so it cannot execute this statement
下面演示免密登录：
[root@host2 mysql]# vi /etc/my.cnf
[mysqld]
port = 3306
datadir = /home/kpc/mysql
socket = /home/kpc/mysql/mysql.sock
character_set_server = utf8
log_bin = /home/kpc/mysql/mysql-bin
skip-grant-tables
[client] #[mysql]
socket = /home/kpc/mysql/mysql.sock
mysql -uroot 直接回车
