2021-09-28-＞杂记

node app 启动更新

//修改数据库可以使用聚合函数

select @@GLOBAL.sql_mode;

set GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

linux的java后台启动

nohup java -jar project-0.0.1-SNAPSHOT.jar &

nohup java -jar ruoyi-admin.jar >/dev/null 2>&1 &
nohup java -jar ruoyi-xiguabanka2.jar >/dev/null 2>&1 &
nohup java -jar ruoyi-xcx.jar >/dev/null 2>&1 &

explain 索引

ruoyi-adminshanchu.jar

5638

kill -9 端口号

解压sql.gz文件

gunzip rxzc-202104141705.sql.gz

查询进程

netstat -ntlp

查询java进程

ps -ef| grep java | grep -v grep
netstat -tunlp | grep 3306

查询nginx进程

ps -ef | grep nginx

停止 nginx 命令

./nginx -s stop

重启nginx命令

./nginx -s reload

查询redis端口

ps -ef |grep redisnetstat -lntp | grep 6379后台启动./redis-server ./redis.conf关闭reidsredis-cli shutdown启动redis-server./redis-server &./redis-cli &redis-cli -p 6379 -a password

清除redis中route_table_开头的所有key的缓存redis-cli KEYS "route_table_*" | xargs redis-cli DEL

给redis设置密码：

config set requirepass “123456”auth "123456"

启动mysql

service mysql startservice mysql restart 重启mysql

权限

chmod 777 test.sh

查询文件

find / -name file1

查看文件内容

cat file

从后查看文件内容

tac file

查询文件内存

//查看系统中文件的使用情况df -h//查看当前目录下各个文件及目录占用空间大小du -sh * //方法一：切换到要删除的目录，删除目录下的所有文件rm -f * //方法二：删除logs文件夹下的所有文件，而不删除文件夹本身rm -rf logs/*

shutdown –h 10 ‘This server will shutdown after 10 mins’ 这个命令告诉大家，计算机将在10分钟后关机，并且会显示在登陆用户的当前屏幕中。

shutdown –h now 立马关机shutdown –h 20:25 系统会在今天20:25关机shutdown –h +10 十分钟后关机shutdown –r now 系统立马重启shutdown –r +10 系统十分钟后重启reboot 就是重启，等同于 shutdown –r nowhalt 关闭系统，等同于shutdown –h now 和 poweroff

修改数据库可以远程链接

grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;flush privileges;

本地查看sql运行
http://localhost:8199/druid/weburi.html
集群查看集群
http://localhost:8088/druid/weburi.html

线程池

https://www.cnblogs.com/wajika/p/6681249.html

show status like 'table%';如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。# 查看锁show status like 'innodb_row_lock%'Innodb_row_lock_current_waits:当前正在等待锁的数量Innodb_row_lock_time:从系统启动到现在锁定的总时间长度Innodb_row_time_avg:每次等待所花的平均时间Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间Innodb_row_lock_waits:总共的等待次数解锁用这个show open tables;解锁第一种show processlist;找到锁进程，kill id ;第二种UNLOCK TABLES;锁表锁定数据表，避免在备份过程中，表被更新LOCK TABLES tbl_name READ;为表增加一个写锁定：LOCK TABLES tbl_name WRITE;

查看锁表语句 show OPEN TABLES where In_use > 0;找到锁表的进程 show processlist;查出死锁进程：SHOW PROCESSLIST其它关于查看死锁的命令：1：查看当前的事务SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;2：查看当前锁定的事务SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;3：查看当前等锁的事务SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

mysql调解并发量

查看最大并发量show global status like 'Max_used_connections';这是是查询数据库当前设置的最大连接数mysql> show variables like '%max_connections%';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_connections | 1000  |+-----------------+-------+可以在/etc/my.cnf里面设置数据库的最大连接数[mysqld]max_connections = 1000mysql> show status like 'Threads%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Threads_cached    | 58    || Threads_connected | 57    |   ###这个数值指的是打开的连接数| Threads_created   | 3676  || Threads_running   | 4     |   ###这个数值指的是激活的连接数，这个数值一般远低于connected数值+-------------------+-------+设置最大并发1024set GLOBAL max_connections=1024;查看mysql本地文件位置用于恢复show global variables like '%datadir%'设置sql过去时间show global variables like 'wait_timeout';set global wait_timeout=20;

mysql8.0转5.7

	utf8mb4_0900_ai_ci 替换为 utf8_general_ci	utf8mb4 替换为 utf8

mysql设置唯一约束

alter table 表名 add unique(字段);

创建账号（->读写分离）

create user 'mytest'@'127.0.0.1' identified by '123456';grant replication slave on *.* to 'mytest'@'127.0.0.1' identified by '123456';show master status; flush privileges;

设置配置

change master to master_host='127.0.0.1',master_port=3306,master_user='mytest',master_password='123456',master_log_file='mysql-bin.000007',master_log_pos=154;

停止同步

stop slave;

开始同步

start slave;

显示同步信息

show slave status;

配置主从数据库（<- 读写分离）

F:[mysqld]port=3306character_set_server=utf8basedir=E:\\tts\\mysql\\mysql-5.7.30-winx64datadir=E:\\tts\\mysql\\mysql-5.7.30-winx64\\dataserver-id=1sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONlower_case_table_names=1innodb_file_per_table = 1log_timestamps=SYSTEMlog-error   = error.logslow_query_log = 1slow_query_log_file = slow.loglong_query_time = 5log-bin = mysql-binbinlog_format = rowreplicate-do-db=ry-vueexpire_logs_days = 15log_bin_trust_function_creators = 1[client]   default-character-set=utf8Z:[mysqld]port=3307character_set_server=utf8basedir=E:\\tts\\mysql\\mysql-5.7.30-winx64-3307datadir=E:\\tts\\mysql\\mysql-5.7.30-winx64-3307\\dataserver-id=2sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONlower_case_table_names=1innodb_file_per_table = 1log_timestamps=SYSTEMlog-error   = error.logslow_query_log = 1slow_query_log_file = slow.loglong_query_time = 5log-bin = mysql-binbinlog_format = rowreplicate-do-db=ry-vueexpire_logs_days = 15log_bin_trust_function_creators = 1log-slave-updates[client]   default-character-set=utf8

mysql 排行

SET @var=0;SELECT (@var:=@var+1) AS 'index' FROM [table]

查询重复数据

select (字段) from (表名) group by (字段) having count(*) > 1

mysql in String

FIND_IN_SET(id, ?)的使用select user_id        from sys_userWHERE			STATUS = '0'			AND del_flag = 0			AND reserv1='0'			AND status=0			AND FIND_IN_SET(				user_id,(				SELECT					children_id				FROM					rxzc_transaction_details				WHERE					user_id = 109				AND del_flag = 0 )

mysql 判空

IFNULL(reserv1,'') != #{reserv1}

%temp% 清理缓存

mysql安装路径

show variables like '%basedir%';--数据地址show variables Like '%datadir%';

切割

select substring(‘sqlstudy.com’, 3, 6);

加敏

select INSERT(‘Quadratic’, 3, 4, ‘****’);

 select user_id,(case when SUM(amount)<10000  then concat(convert(SUM(amount),decimal(12,2)),'元')             when SUM(amount)<100000000 then concat(convert(SUM(amount)/10000,decimal(12,2)),'万')else concat(convert(SUM(amount)/100000000,decimal(12,2)),'亿') end) as amount ,	DATE_FORMAT( creat_time, '%Y-%m-%d' ) AS creatTime from rxzc_trading_volumeWHERE user_id IN (SELECT user_id FROM sys_user WHERE reserv1=0 AND (parents_code=109 OR user_id=109))	GROUP BY DATE_FORMAT(creat_time,'%Y-%m-%d')	ORDER BY creat_time DESCLIMIT 7

select (case when SUM(amount)< 10000  then concat(convert(SUM(amount),decimal(12,2)),'元')        when SUM(amount) < 100000000 then concat(convert(SUM(amount)/10000,decimal(12,2)),'万')        else concat(convert(SUM(amount)/100000000,decimal(12,2)),'亿') end) as amount ,        DATE_FORMAT( creat_time, '%Y-%m-%d' ) AS creatTime        from rxzc_trading_volume        WHERE user_id IN (SELECT user_id FROM sys_user WHERE reserv1=1 AND status=0 AND (parents_code=109 OR user_id=109))        GROUP BY DATE_FORMAT(creat_time,'%Y-%m-%d')        ORDER BY creat_time DESC        LIMIT 7

MYSQL 分区

-- 添加一个时间字段ALTER TABLE `rxzc_order` ADD COLUMN `days` INT (11) UNSIGNED NOT NULL DEFAULT 19700101 COMMENT '日期(格式:Ymd)' AFTER `id`;-- 取消id主见ALTER TABLE `rxzc_order` MODIFY COLUMN `id` INT (11) UNSIGNED NOT NULL COMMENT 'id' FIRST;-- 取消表主见ALTER TABLE `rxzc_order` DROP PRIMARY KEY;-- 添加联合主键 第一个值表主键 第二个值分区主键ALTER TABLE `rxzc_order` ADD PRIMARY KEY (`id`, `field1`);-- 添加自增ALTER TABLE `rxzc_order` MODIFY COLUMN `id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id' FIRST;-- 开分区ALTER TABLE `rxzc_order` PARTITION BY RANGE COLUMNS(field1) (    PARTITION `p197001` VALUES LESS THAN ('20210428'),    PARTITION `p202003` VALUES LESS THAN ('20210501'),    PARTITION `p202004` VALUES LESS THAN ('20210601'),    PARTITION `p202005` VALUES LESS THAN ('20210701'),    PARTITION `p202006` VALUES LESS THAN ('20210801'),    PARTITION `p202007` VALUES LESS THAN ('20210901'),		partition p202008 values less than maxvalue);-- 测试select * from rxzc_order partition (p202004) ;select * from rxzc_order WHERE cr_time>'2021-09-01 00:02:24' ;