mysql笔记

../images/${filename}

-- mysql
--启动
systemctl start mysqld

./mysqld --defaults-file=/etc/my.cnf --user=root

docker run -itd --name iwind_mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=iwind.123 -e TZ=Asia/Shanghai -d mysql/mysql-server:8.0 --lower_case_table_names=1

--解决办法调整MySQL密码验证规则，修改 policy 和 length 的值。
2、MySQL 5.7 进行如下设置，即可解决问题：

mysql>  set global validate_password_policy=0;
mysql> set global validate_password_length=1;

3、MySQL 8.0 执行代码：

mysql>  set global validate_password.policy=0;
mysql>  set global validate_password.length=1;

--查看进程
ps -ef | grep mysql

-- 查看数据默认的存储路径（一般在 /var/lib/mysql）
show global variables like '%datadir%'; 
--修改datadir
https://blog.csdn.net/gispipi/article/details/106494367

--连接
shell> mysql -h host -u user -p
shell> mysql -u user -p
--数据库
mysql> SHOW DATABASES;
mysql> CREATE DATABASE menagerie;
mysql> USE menagerie

--表
mysql> SHOW TABLES;
mysql> SHOW CREATE TABLE tablename;
mysql> DESCRIBE/DESC tablename;
CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

--增删改查
INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE tablename;
mysql> DELETE FROM tablename;
mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
mysql>SELECT what_to_select FROM which_table WHERE conditions_to_satisfy;

--创建表或列与数据库关键字冲突加上``反引号解决

--mysql看字符集
show variables like '%set%';

-- 查看是否区分大小写
show global variables like '%lower_case%';  1：不区分  0：区分

-- 查看时区
show VARIABLES like '%time_zone%';

--导出
select * into outfile 'D:/catid.xls' from half_year ;

--导入/还原
 LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE tablename;  -- 数据文件
 source /path/sql.sql  -- sql语句
 mysql -u root test < bak.sql

--要在服务器运行时查看服务器实际使用的当前系统变量值，请连接至该服务器并执行以下语句：
mysql> SHOW VARIABLES;  show variables like "max%";
mysql> set global max_connections=1000;
	--要查看正在运行的服务器的一些统计信息和状态指示符，请执行以下语句：
mysql> show status -- 显示全部mysql操作信息
mysql> show status like "com_insert%"; -- 获得mysql的插入次数;
mysql> show status like "com_delete%"; -- 获得mysql的删除次数;
mysql> show status like "com_select%"; -- 获得mysql的查询次数;
mysql> show status like "uptime"; 		 -- 获得mysql服务器运行时间
mysql> show status like 'connections'; -- 获得mysql连接次数
	
	--系统变量和状态信息也可以使用 mysqladmin命令获得：
	shell> mysqladmin variables
	shell> mysqladmin extended-status

--调整MySQL服务器时，要配置的两个最重要的系统变量  my.cnf/my.ini
--key_buffer_size和 table_open_cache
key_buffer_size：

key_buffer_size是用于索引块的缓冲区的大小。在32位平台上 ，最大允许设置为 4GB-1。对于64位平台，允许使用更大的值。
此变量建议值为计算机总内存的25％。但是，您应该意识到，如果将该值设置得太大（例如，超过计算机总内存的50％），则系统可能会开始分页并变得非常慢。
这是因为MySQL依赖操作系统来执行文件系统缓存以进行数据读取，因此您必须为文件系统缓存留出一些空间。

table_open_cache：
所有线程的打开表数。增大该值将增加mysqld所需的文件描述符的数量。您可以通过检查Opened_tables状态变量来检查是否需要增加表缓存。

--常用变量

--max_connections：
允许的最大同时客户端连接数。
--innodb_buffer_pool_size：
数据库使用的总内存大小  建议系统内存80%
--sort_buffer_size， read_buffer_size
sort_buffer_size：排序后缓冲区大小,一个会话创建一个缓冲区。 通过排序的那列大小计算。
默认值  262144  
极小值	32768
最大值（其他64位平台）	18446744073709551615
最大值（其他32位平台）	4294967295
最大值（Windows）	4294967295

--read_buffer_size：
sort后的数据是以key-value的形式存在的，使用这些行指针去读取数据，将是以指针数据物理的顺序去读取，很大程度上是随机的方式读取数据的。
MySQL从sort_buffer中读取这些行指针数据，然后通过指针排序后存入read_buffer中，之后再通过指针读取数据时，基本上都是顺序读取了。

--read_rnd_buffer_size：
read_buffer_size仅仅用于MyISAM引擎，read_rnd_buffer_size不仅仅用在MyISAM引擎，而是用在所有引擎。

--innodb_sort_buffer_size   
这个参数只会在创建索引的过程中被使用，不会用在后面的维护操作；在索引创建完毕后innodb_sort_buffer会被释放。
--max_sort_length: 
最大支持的一行的字节数,MAX_SORT_LENGTH 越大,则支持的容纳的行数就越少

--max_length_for_sort_data:
http://www.javacoder.cn/?p=13
https://blog.csdn.net/csd753111111/article/details/100428423

--join_buffer_size:关联查询缓存区
最好将全局设置保持较小，并且仅在进行大型联接的会话中将会话设置更改为较大的值。如果全局大小大于使用它的大多数查询所需的大小，则内存分配时间可能会导致性能大幅下降。

--sql_big_selects ，max_join_size：
sql_big_selects如果设置为OFF，MySQL将中止 SELECT可能花费很长时间执行的语句（即，优化器估计该语句所检查的行数超过的值的语句 max_join_size）。
sql_big_selects的默认值为 ON，它允许所有 SELECT语句。
如果将max_join_size 系统变量设置为以外的其他值 DEFAULT， sql_big_selects则设置为 OFF。
max_join_size默认值就是最大值。

--thread_cache_size：
缓存的线程数，减少重新创建线程所需要的资源
--thread_handling：
 服务器用于连接线程的线程处理模型。允许的值为 no-threads（服务器使用一个线程来处理一个连接）， 
 one-thread-per-connection（服务器使用一个线程来处理每个客户端连接）和 loaded-dynamically（由线程池插件在初始化时设置）。

--thread_pool_size：
线程池中的线程组数。这是控制线程池性能的最重要参数。它影响可以同时执行的语句数。如果指定了超出允许值范围的值，则不会加载线程池插件，并且服务器会将消息写入错误日志。

--thread_stack：
每个线程的堆栈大小。默认值足够大，可以正常运行。如果线程堆栈大小太小，则会限制服务器可以处理的SQL语句的复杂性，存储过程的递归深度以及其他消耗内存的操作

--innodb_purge_threads：5.x默认1  8.x默认4
事务被提交以后，其所使用的undolog可能就不再被需要了，因此需要使用Purge Thread 进行回收已经使用的undo页，
在InnoDB1.1版本前，purge操作只能在InnoDB存储引擎中的Master Thread中进行，在InnoDB1.1以后purge操作可以在单独的线程中进行，
来减轻master thread的工作压力，从而提高CPU的使用率和存储引擎的性能

--innodb_read_io_threads：
InnoDB中的读取操作的I / O线程数 ，默认4
--innodb_write_io_threads：
InnoDB中的写操作的I / O线程数 ，默认4
--innodb_thread_concurrency	
InnoDB定义允许的最大线程数 。值0（默认值）被解释为无限并发（无限制）。
--innodb_thread_sleep_delay
定义InnoDB线程在加入InnoDB队列之前休眠的时间（以微秒为单位）。默认值为10000。值为0将禁用睡眠。

-- 备份
mysqldump -u root 数据库名>备份文件名;   #备份整个数据库

mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表

-- mysqldump和mysqlpump差异
mysqlpump更像是mysqldump的进阶版，主要升级功能为：

1.支持并行。实际上拆分进程同样有并行效果
2.在数据插入完成后创建二级索引
3.在dump文件最后完成唯一性检查和外键检查
4.由于支持并行和二级索引、唯一性检查、外键检查放在最后的缘故，pump相对于dump会更快
5.dump和pump的命令非常像，但是使用细节还是有些不同，例如dump会在创建表前drop表，没有usedb，pump默认不会drop表，创建表时指定了db.tab
6.oracle的exp不可以指定并行，但是默认不删除表，支持快照查询，他有mysqldump和mysqlpump的部分特性。mysqldump更像是早期导出工具的版本，mysqlpump可能更成熟。
7.如果要远程导出，只能使用mysqldump。
8.如果要使用全量+增量数据备份，只能使用mysqldump。因为mysqlpump是并行备份数据，无法将二进制日志文件的名称和位置写入输出

-- mysqlimport  
Usage: mysqlimport [OPTIONS] database textfile...
Default options are read from the following files in the given order:
C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf C:\Program Files\MySQL\MySQL Server 5.7\my.ini C:\Program Files\MySQL\MySQL Server 5.7\my.cnf
The following groups are read: mysqlimport client
The following options may be given as the first argument:

用法：mysqlimport[OPTIONS]数据库文本文件。。。

默认选项按给定顺序从以下文件中读取：
C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf C:\Program Files\MySQL\MySQL Server 5.7\my.ini C:\Program Files\MySQL\MySQL Server 5.7\my.cnf

将读取以下组：mysqlimport client

https://www.cnblogs.com/shengdimaya/p/6920677.html 十分钟了结MySQL information_schema

innodb buffer pool 在内存中其实是一个 list, 维护数据的确是用 LRU, 但是做了点优化.

buffer pool 将数据切割成两块, 头部是 young page list, 尾部是 old page list. 两者的空间比例默认是5: 3, 可以通过 show variables like “innodb_old_blocks_pct” 查看, old block的比例即为 37%.

当数据第一次被从磁盘上读取, 要加入 buffer pool 时, 会先放在 old page list 的头部, 此时如果 old page list 满了,则会把它尾部的数据踢出去. 当数据在buffer pool中被再次访问的时候, 如果它在 old page list 中, 则会被转移到 young page list 的头部(如果数据已经在 young page list 中, 则只有它和头结点的距离超过一定长度时才会被转移到头部).

通过这种机制, 可以有效得避免临时的大量数据读取冲刷掉 hot page, 这些一次性的数据如果没有被访问的话,很快会被从old page list 中踢出去.

--查看存错过程（由于mysql模式名下没有）
SELECT ROUTINE_SCHEMA,ROUTINE_TYPE, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE='PROCEDURE'

-- 数据字典表 模式名：INFORMATION_SCHEMA 
catalogs：系统目录（Catalog）信息。
character_sets：系统支持的字符集。
check_constraints：CHECK 约束信息。
collations：字符集支持的排序规则。
column_statistics：字段的直方图统计信息。
column_type_elements：字段的数据类型信息。
columns：表中的字段信息。
dd_properties：存储数据字典的属性，例如版本。服务器利用这些信息决定是否需要升级数据字典。
events：事件调度器中的事件信息。
foreign_keys、foreign_key_column_usage：外键相关的信息。
index_column_usage：索引字段信息。
index_partitions：索引分区信息。
index_stats：ANALYZE TABLE 命令生成的动态索引统计。
indexes：表中的索引信息。
innodb_ddl_log：支持原子 DDL 操作的日志。
parameter_type_elements：存储过程和函数的参数信息，以及存储函数的返回值类型
parameters：关于存储过程和函数的信息。
resource_groups：关于资源组的信息。
routines：关于存储过程和函数的信息。
schemata：关于数据库/模式的信息。在 MySQL 中，模式和数据库是相同的概念。
st_spatial_reference_systems：可以用于空间数据的空间参照系统。
table_partition_values：表分区使用的分区值。
table_partitions：表的分区信息。
table_stats：ANALYZE TABLE 命令生成的动态表统计。
tables：数据库中的表。
tablespace_files：表空间使用的文件。
tablespaces：活动表空间。
triggers：触发器。
view_routine_usage：视图和存储函数之间的依赖关系。
view_table_usage：视图和基表之间的依赖关系。

-- 权限信息表 模式名：mysql
user：用户账户、全局权限以及其他信息。
global_grants：用户的动态全局权限。
db：数据库级别的权限。
tables_priv：表级别的权限。
columns_priv：字段级别的权限。
procs_priv：存储过程和函数上的权限。
proxies_priv：代理用户权限。
default_roles：用户连接并认证后默认激活的角色，或者执行 SET ROLE DEFAULT 命令后设置的角色。
role_edges：角色的授予关系。user 表中的一行数据既可能代表一个用户账户，也可能代表一个角色。
password_history：密码修改历史。

-- 对象信息表 模式名：mysql
-- 这些系统表包含了关于存储过程、组件、用户定义函数以及服务器端插件的信息：

component：服务器组件的注册信息。
func：用户定义函数（UDF）信息。
plugin：服务器端插件信息。
-- 查询日志表 模式名：mysql
general_log：通用查询日志表。
slow_log：慢查询日志表。
-- 这些日志表的存储引擎为 CSV。

-- 服务器端帮助信息表 模式名：mysql
--这些表中存储了服务器端相关的帮助信息：
help_category：帮助信息分类。
help_keyword：帮助信息关键字。
help_relation：关键字和帮助主题之间的关系。
help_topic：帮助主题的具体内容。

-- 时区信息表 模式名：mysql
--这些系统表包含了时区相关的信息：
time_zone：时区 ID 以及是否包含闰秒。
time_zone_leap_second：闰秒发生的情况。
time_zone_name：时区 ID 和名称的映射。
time_zone_transition、time_zone_transition_type：时区描述。

MySQL 8.0 中 4 个默认的系统数据库
https://blog.csdn.net/horses/article/details/106667755