mysql小知识点
参考：https://www.cnblogs.com/geaozhang/p/6834780.html
一、mysql选项查看，要会看这张表：
https://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.html
https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-status-variables/
mysqld选项：
选项名称；是否可以作为命令行选项；是否可以作为为配置文件选项；是否是系统变量，是否为状态变量；变量的作用范围（有global和session两种）；是否是动态参数
以sql-mode为例，它是命令行选项，又是文件选项，又是系统变量，变量作用范围为全局，动态：
| sql-mode | Yes | Yes | Yes | Both | Yes | |
|---|---|---|---|---|---|---|
1、sql-mode可以在运行mysqld时指定,下面指定seq-mode为空
[root@localhost ~]# /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --sql-mode=''
#可以在mysql中看到其值为空
mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)
2、可以作为文件选项，可以在mysql的配置文件my.cnf的mysqld段指定
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
sql-mode='ONLY_FULL_GROUP_BY'
...
[root@localhost ~]# systemctl restart mysqld
mysql> SELECT @@sql_mode;
+--------------------+
| @@sql_mode |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
1 row in set (0.00 sec)
3、sql-mode是系统变量，系统变量可以使用show variables查看,也可以通过set global设置
#服务器系统变量分为全局变量和会话变量两种
#获取系统变量有三种命令
mysql> SHOW VARIABLES LIKE 'sql_mode';
+---------------+--------------------+
| Variable_name | Value |
+---------------+--------------------+
| sql_mode | ONLY_FULL_GROUP_BY |
+---------------+--------------------+
1 row in set (0.01 sec)
mysql> select @@sql_mode;
+--------------------+
| @@sql_mode |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
1 row in set (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
#修改全局变量，两种命令
mysql> set @@global.sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL sql_mode='';
Query OK, 0 rows affected (0.00 sec)
#查看会话变量
mysql> SHOW SESSION VARIABLES;
#修改会话变量两种方式
SET @@session.system_var_name=value;
SET SESSION system_var_name=value;
4、sql_mode不是状态变量，因此查不到
#服务器状态变量分为全局和会话两种，状态变量只读
mysql> SHOW GLOBAL STATUS;
mysql> SHOW SESSION STATUS;
mysql> SHOW GLOBAL STATUS LIKE 'sql_mode';
Empty set (0.00 sec)
5、sql-mode该变量的范围是global和session，可以分别查看下
#全局变量下sql_mode为空是我上面使用SET GLOBAL sql_mode='';设置的
mysql> SHOW GLOBAL VARIABLES LIKE 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+--------------------+
| Variable_name | Value |
+---------------+--------------------+
| sql_mode | ONLY_FULL_GROUP_BY |
+---------------+--------------------+
1 row in set (0.00 sec)
6、sql_mode是动态参数
二、sql_mode的含义
对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置
参考：https://mariadb.com/kb/en/library/sql-mode/
比如： ONLY_FULL_GROUP_BY
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的。
三、mysqladmin检测mysql状态
mysqladmin可以修改密码，删除，创建数据库，刷新表，刷新日志，查看状态，关闭数据库
[root@localhost support-files]# mysqladmin -uroot -p ping
Enter password:
mysqld is alive
[root@localhost support-files]# mysqladmin -uroot -p status
Enter password:
Uptime: 6295 Threads: 1 Questions: 57 Slow queries: 0 Opens: 109 Flush tables: 1 Open tables: 102 Queries per second avg: 0.00
四、mysql忘记密码
网上文章还是挺多的！！！
https://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.html
从上面链接的表上可以看到，skip-grant-tables变量可以作为命令行选项用，也可以当配置文件选项来使用
| skip-grant-tables | Yes | Yes | |||
|---|---|---|---|---|---|
在命令行中使用：
关闭数据库，以--skip-grant-tables启动mysqld
[root@localhost ~]# /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --skip-grant-tables &
[root@localhost ~]# mysql -uroot -p
Enter password:
mysql> UPDATE mysql.user SET authentication_string=password('123456789') WHERE user='root' AND host='localhost';
mysql> FLUSH PRIVILEGES;
mysql> QUIT
[root@localhost ~]# pkill mysql
[root@localhost ~]# systemctl start mysql
在配置文件中使用
#my.cnf加一行或两行都加上
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
skip-grant-tables
skip-networking
[root@localhost ~]# systemctl restart mysql
[root@localhost ~]# mysql -uroot -p
Enter password:
mysql> UPDATE mysql.user SET authentication_string=password('123456') WHERE user='root' AND host='localhost';
mysql> FLUSH PRIVILEGES;
mysql> QUIT
#删掉my.cnf中的那两行，重启mysql
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# systemctl restart mysql
五、mysql的3306端口抓包实验
通过wireshark抓包，看到将mysql的数据包全部截获，并且全部是明文，mysql加密后的密码也截获到，mysql的密码加密机器简单，通过在线解密工具很容易得到其真实密码，这就是为何mysql的3306端口坚决不能暴露的原因。还有就是mysql一般监听在本地或者局域网内，公网的话如果必须要，那么一定要设置防火墙，安全组。
六、mysql怎么允许别的主机连
首先selinux和防火墙关掉或者设置规则
然后mysql的my.cnf要设置监听在所有地址上:bind-address=*，并重启mysql
接着mysql对其授权：mysql> GRANT ALL ON wordpress.* TO 'tom'@'192.168.38.%' IDENTIFIED BY '123456';
接着刷新权限：mysql> FLUSH PRIVILEGES;
不出意外就可以了：[root@web ~]# mysql -utom -p -h192.168.38.132
七、mysql备份还原注意
写好备份脚本后，一定要手动备份一次，看能否成功，并且将备份的数据库文件在测试机上做还原演练，看备份的对不对，能不能用
八、mysql的索引
索引的查看，添加，删除，以及explain查询是否使用了索引
查看表上的索引：
mysql> DESC students; #主键也是索引，索引分好多种
mysql> SHOW INDEX FROM hellodb.students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students | 0 | PRIMARY | 1 | StuID | A | 25 | NULL | NULL | | BTREE | | |
| students | 1 | idx_name | 1 | Name | A | 25 | NULL | NULL | | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
添加索引：
mysql> CREATE INDEX idx_name ON students(name);
如何确认查询使用了索引：
mysql> explain select * from students where stuid=16;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | students | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
删除索引：
mysql> DROP INDEX idx_name ON students;
九、myisam和innodb区别
我记得最深刻的一点就是myisam是表级锁，而innodb支持行级锁。好处就是不至于我改表上内容，别人完全无法修改此表
然后：Myisam不支持事务，不支持事务就比较难受了，innode支持
Myisam不支持热备，innode支持
（明天补充）
十、索引不是必须有
常用的索引类型是B tree，有助于读请求，但不利于写请求。
十一、事务的四个特性
参考：https://www.cnblogs.com/geaozhang/p/10478918.html
看到事务，让我联想到银行取钱，不会因为银行突然断电，钱取出来了钱没扣
ACID
A：原子性：一个事务中的操作要么都成功，要么都失败回滚
C：一致性：在整个事务的生命周期里面，查询到的数据是一致的；
I：隔离性：隔离级别；
D：持久性： 只要事务commit，这个事务不会因为系统的崩溃而丢失；
十二、MySQL默认每一条SQL都是一条事务
意思是：我这个命令只要回车了，立马会执行
mysql> SHOW VARIABLES LIKE '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
有DBA工程师说，关闭SQL的自动提交，避免误操作
十三、事务的隔离级别
1234
十四、mysqldump --single-transaction参数的作用
--single-transaction参数的作用，设置事务的隔离级别为可重复读，即REPEATABLE READ，这样能保证在一个事务中所有相同的查询读取到同样的数据，也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表的数据并提交，对该dump线程的数据并无影响，在这期间不会锁表。
十五、my_print_defaults
my_print_defaults 这个程序是用来解析my.cnf文件的，将其中的参数打印到终端。这个小程序在mysql的启动脚本中会有用到。
[root@localhost ~]# my_print_defaults --defaults-file=/etc/my.cnf client
--socket=/var/lib/mysql/mysql.sock
--port=3306
[root@localhost ~]# my_print_defaults --defaults-file=/etc/my.cnf mysqld
--server-id=2
--gtid-mode=on
--enforce_gtid_consistency=on
--datadir=/data/mysql
--socket=/var/lib/mysql/mysql.sock
--symbolic-links=0
--log-error=/var/log/mysqld.log
--pid-file=/var/run/mysqld/mysqld.pid
十六、binlog相关命令
#mysql外面看binlog，可以指定某一范围
[root@localhost ~]# mysqlbinlog -vvv /data/mysql/mysql-bin.000008
#mysql里面看binlog名字和大小
mysql> show binary logs;
#mysql里面看当前的binlog文件名和position位置
mysql> show master status;
#slave查看同步状态
mysql> show slave status\G
#开启线程，第一次开启后，以后重启数据库会自启
mysql> show slave status\G
#slave停止线程
mysql> stop slave;
#删除指定日期前面的binlog
mysql> PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';
sync_binlog=1 #每次写后立即同步二进制日志到此磁盘，性能差
innodb_flush_log_at_trx_commit=1 #每次事务提交立即同步日志写磁盘
十七、DDL，DML
DDL：数据定义语言，管理数据库的对象（CREATE，DROP，ALTER，TRUNCATE）
DML：数据操纵语言：SELECT，INSERT，UPDATE，DELETE，CALL，EXPLAIN
十八、从数据库如果还想再连slave数据库
#slave开启binlog和更新master的binlog到自己
[mysqld]
log_bin
log_slave_updates
十九、压力测试、优化吞吐量
参考：https://blog.csdn.net/qq_18312025/article/details/78897023
二十、事务
事务(Transaction)：组织多个操作为一个整体，要么全部都成功执行，要么失败回滚；
一个存储系统是否支持事务，测试标准：
ACID：
A：原子性；
C：一致性；
I：隔离性；
D：持久性；