MYSQL系列常用命令三（导入导出命令、系统参数、系统函数）

系列文档参考 MYSQL系列-整体架构介绍
本文紧接上一章MYSQL系列-常用命令二（索引、视图、存储过程、事务、数据类型）

本章主要列举MYSQL日常使用过程中用到的一些命令，主要用于总结和后续使用时可以快速查阅

导入导出命令

mysqldump

• 导出所有库数据：mysqldump -uroot -p密码 --all-databases > 备份文件名.sql

mysql@TOBY-HYW:~/mysql3306/bin$ ./mysqldump -uroot -p1Qaz1Qaz -S /home/mysql/mysql3306/mysqld.sock --all-databases > temp.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
Warning: A dump from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that were executed during its extraction and might not be represented in the dumped data. This might result in an inconsistent data dump.
In order to ensure a consistent backup of the database, pass --single-transaction or --lock-all-tables or --master-data.

• 导出部分库数据：mysqldump -uroot -p密码 --databases > 备份文件名.sql

mysql@TOBY-HYW:~/mysql3306/bin$ ./mysqldump -uroot -p1Qaz1Qaz -S /home/mysql/mysql3306/mysqld.sock --databases toby > temp.sql

• 导出MySQL单库中的一部分表数据:mysqldump –u 用户名 –h主机名 –p密码 库名 [表名1,表名2...]> 备份文件名.sql

mysql@TOBY-HYW:~/mysql3306/bin$ ./mysqldump -h127.0.0.1 -P3306 -uroot -p1Qaz1Qaz -S /home/mysql/mysql3306/mysqld.sock toby t1 > temp.sql

• 其他命令

-- 导出MySQL单表的部分数据（使用 --where 参数） 
mysqldump -u用户名 -p 库名 表名 --where="条件" > 备份文件名.sql 
-- 排除某些表，导出库中其他的所有数据（使用 --ignore-table 参数） 
mysqldump -u用户名 -p 库名 --ignore-table=表名1,表名2... > 备份文件名.sql 
-- 只导出表的结构（使用 --no-data 或者 -d 选项） 
mysqldump -u用户名 -p 库名 --no-data > 备份文件名.sql 
-- 只导出表的数据（使用 --no-create-info 或者 -t 选项） 
mysqldump -u用户名 -p 库名 --no-create-info > 备份文件名.sql 
-- 导出包含存储过程、函数的库数据（使用--routines 或者 -R选项） 
mysqldump -u用户名 -p -R --databases 库名 > 备份文件名.sql 
-- 导出包含事件（触发器）的库数据（使用 --events 或者 -E选项） 
mysqldump -u用户名 -p -E --databases 库名 > 备份文件名.sql

mysql

• 导入数据，数据库DB级别：mysql -u用户名 -p < xxx.sql
• 导入数据，表级别:mysql -u用户名 -p 库名 < xxx.sql

mysql@TOBY-HYW:~/mysql3306/bin$ ./mysql -uroot -p -S /home/mysql/mysql3306/mysqld.sock toby  "数据存放目录/xxx.txt"

mysql@TOBY-HYW:~/mysql3306/bin$ ./mysql -uroot -p1Qaz1Qaz -S /home/mysql/mysql3306/mysqld.sock -e"use toby;select id,b,c from t1 where id  temp.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql@TOBY-HYW:~/mysql3306/bin$ cat temp.sql
id      b       c
1       1       1
5       5       5


方式二：登录mysql客户端，使用into方式 select * from 表名 into outfile "备份文件名.txt";

mysql> use toby
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | /tmp/ |
+------------------+-------+
1 row in set (0.00 sec)
mysql> select id,b,c from t1 where id  ;
Query OK, 2 rows affected (0.00 sec)




导入数据

使用load data infile：load data infile "数据目录/xxx.txt" into table 库名.表名;
使用 mysqlimport:mysqlimport -u用户名 -p 库名 '数据存放目录/xxx.txt'

系统参数



完整版系统参数参考MYSQL官网
查看系统参数

show global variables;：查看全局所有用户级别可以看到的系统变量。
show session variables; | show variables;：查看当前会话的所有系统变量。
show variables like '%关键字%';：使用模糊查询搜索某个系统变量。

基础参数

max_connections：MySQL的最大连接数，超出后新到来的连接会阻塞或被拒绝。
version：当前数据库的版本。
tx_isolation：事务的隔离级别。
autocommit：事务自动提交机制。
optimizer_switch：MySQL隐藏参数的开关。

BIN LOG

log_bin：是否开启bin-log日志，默认ON开启，表示会记录变更DB的操作。
log_bin_basename：设置bin-log日志的存储目录和文件名前缀，默认为./bin.0000x。
log_bin_index：设置bin-log索引文件的存储位置，因为本地有多个日志文件，需要用索引来确定目前该操作的日志文件。
binlog_format：指定bin-log日志记录的存储方式，可选Statment、Row、Mixed。
max_binlog_size：设置bin-log本地单个文件的最大限制，最多只能调整到1GB。
binlog_cache_size：设置为每条线程的工作内存，分配多大的bin-log缓冲区。
sync_binlog：控制bin-log日志的刷盘频率。
binlog_do_db：设置后，只会收集指定库的bin-log日志，默认所有库都会记录。

慢查询日志

slow_query_log：设置是否开启慢查询日志，默认OFF关闭。
slow_query_log_file：指定慢查询日志的存储目录及文件名。
general_log：是否开启查询日志，默认OFF关闭。
general_log_file：指定查询日志的存储路径和文件名。
long_query_time:被认为是慢查询的执行时间阈值。超过该阈值的查询语句将被记录到慢查询日志中。默认值为10秒
log_queries_not_using_indexes：当启用该参数时，如果查询语句没有使用索引，也会被记录到慢查询日志中。默认情况下，它的值是OFF。

INNODB相关

innodb_page_size：InnoDB引擎数据页的大小。
innodb_autoinc_lock_mode：插入意向锁的工作模式。
innodb_lock_wait_timeout：InnoDB锁冲突时，阻塞的超时时间。
innodb_deadlock_detect：是否开启InnoDB死锁检测机制。
innodb_max_undo_log_size：本地磁盘文件中，Undo-log的最大值，默认1GB。
innodb_rollback_segments：指定回滚段的数量，默认为1个。
innodb_undo_directory：指定Undo-log的存放目录，默认放在.ibdata文件中。
innodb_undo_logs：指定回滚段的数量，默认为128个，也就是之前的innodb_rollback_segments。
innodb_undo_tablespaces：指定Undo-log分成几个文件来存储，必须开启innodb_undo_directory参数。
innodb_undo_log_truncate：是否开启Undo-log的压缩功能，即日志文件超过一半时自动压缩，默认关闭。
innodb_flush_log_at_trx_commit：设置redo_log_buffer的刷盘策略，默认每次提交事务都刷盘。
innodb_log_group_home_dir：指定redo-log日志文件的保存路径，默认为./。
innodb_log_buffer_size：指定redo_log_buffer缓冲区的大小，默认为16MB。
innodb_log_files_in_group：指定redo日志的磁盘文件个数，默认为2个。
innodb_log_file_size：指定redo日志的每个磁盘文件的大小限制，默认为48MB。
innodb_log_write_ahead_size：设置checkpoint刷盘机制每次落盘动作的大小。
innodb_log_compressed_pages：是否对Redo日志开启页压缩机制，默认ON。
innodb_log_checksums：Redo日志完整性效验机制，默认开启。
innodb_buffer_pool_size：InnoDB缓冲区的大小。
innodb_adaptive_hash_index：是否开启InnoDB的自适应哈希索引机制。
innodb_compression_level：调整压缩的级别，可控范围在1~9，越高压缩效果越好，但压缩速度也越慢。
innodb_compression_failure_threshold_pct：当压缩失败的数据页超出该比例时，会加入数据填充来减小失败率，为0表示禁止填充。
innodb_compression_pad_pct_max：一个数据页中最大允许填充多少比例的空白数据。
innodb_log_compressed_pages：控制是否对redo-log日志的数据也开启压缩机制。
innodb_cmp_per_index_enabled：是否对索引文件开启压缩机制。

系统函数
字符串函数

length(S)：返回字符串的占位空间，传入“竹子爱熊猫”，返回15，一个汉字占位3字节。
concat(S1,S2,...)：合并传入的多个字符串。
concat_wa(sep,S1,S2...)：合并传入的多个字符串，每个字符串之间用sep间隔。
left(S,index)：从左侧开始截取字符串S的index个字符。
trim(S)：删除字符S左右两侧的空格。
replace(S,old,new)：使用new新字符替换掉S字符串中的old字符。
substring(S,index,N)：截取S字符串，从index位置开始，返回长度为N的字符串。

日期、时间

curdate() | current_date()：返回当前系统的日期，如2022-10-21。
curtime() | current_time()：返回当前系统的时间，如17:30:52。
now() | sysdate()：返回当前系统的日期时间，如2022-10-21 17:30:59。
date_format(date,format)：将一个日期格式化成指定格式

聚合函数

max(字段名)：查询指定字段值中的最大值。
min(字段名)：查询指定字段值中的最小值。
count(字段名)：统计查询结果中的行数。
sum(字段名)：求和指定字段的所有值。
avg(字段名)：对指定字段的所有值，求出平均值。
group_concat(字段名)：返回指定字段所有值组合成的结果，如下：
distinct(字段名)：对于查询结果中的指定的字段去重。

加密函数

password(str)：将str字符串以数据库密码的形式加密，一般用在设置DB用户密码上。
md5(str)：对str字符串以MD5不可逆算法模式加密。
encode(str,key)：通过key密钥对str字符串进行加密（对称加密算法）。
decode(str,key)：通过key密钥对str字符串进行解密。
aes_encrypt(str,key)：通过key密钥对str字符串，以AES算法进行加密。
aes_decrypt(str,key)：通过key密钥对str字符串，以AES算法进行解密。
sha(str)：计算str字符串的散列算法校验值。
encrypt(str,salt)：使用salt盐值对str字符串进行加密。
decrypt(str,salt)：使用salt盐值对str字符串进行解密。

数学函数

abs(X)：返回X的绝对值，如传进-1，则返回1。
mod(X,Y)：返回X除以Y的余数。
ceil(X) | ceiling(X)：返回不小于X的最小整数，如传入1.23，则返回2。
round(X)：返回X四舍五入的整数。
floor(X)：返回X向下取整后的值，如传入2.34，会返回2。