日常问题记录

apc 缓存失效，重启php

ps axu|grep mysqld|grep -v mysqld_safe|grep -v grep |awk '{ print $2 }'|xargs kill

确认mysql 是否重启成功
如果杀不掉直接执行：
ps axu|grep mysqld|grep -v mysqld_safe|grep -v grep |awk '{ print $2 }'|xargs kill -9

/usr/local/webserver/php5_6_36/sbin/php-fpm -c /usr/local/webserver/php5_6_36/etc/php.ini -y /usr/local/webserver/php5_6_36/etc/php-fpm.conf
ps -ef |grep php-fpm |grep conf |awk '{print $2}' |xargs kill -USR2

master 主库操作

flush logs;
show master status;
--------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------------------------+
| mysql-bin.001125 | 238780 | | mysql,test | 3ca19de1-933f-11e9-affe-42010a8c0007:3572093-6036466,
d7fb365e-9330-11e9-8609-42010a8c000a:1-130579098 |
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

slave 指定binlog 和postion

stop slave 从库
change master to master_log_file='mysql-bin.001125',master_log_pos=238780,MASTER_AUTO_POSITION =0;
start slave

mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000888

mysql> show global variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 1 |
+---------------+-------+
1 row in set (0.10 sec)

mysql> show global variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2 |
+--------------------------------+-------+
1 row in set (0.00 sec)

set @@global.sync_binlog=1, @@global.innodb_flush_log_at_trx_commit=2;

mysql> show global variables where variable_name like 'lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+------------------------+-------+
2 rows in set (0.02 sec)
lower_case_table_names = 1 表名存储在磁盘是小写的，但是比较的时候是不区分大小写
lower_case_table_names=0 表名存储为给定的大小和比较是区分大小写的

/usr/local/webserver/mysql5.7/bin/mysqldump -uroot -pkmi35AJb09N9aopKEd53bZ11Qf1Oc0y5 -S /data/hc3_hgameM/run/mysql.sock --single-transaction --set-gtid-purged=OFF hgame language_resource > hx-language_resource_0420.sql

slave_exec_mode来完成。

set global slave_exec_mode='IDEMPOTENT';

sed -i "s:\${PHP_BIN}:/usr/local/webserver/php5_3_27/bin/php/:g" comment.txt

mysql> create table bak_report_profit_loss_half_hour_2021_11_11 like report_profit_loss_half_hour;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into bak_report_profit_loss_half_hour_2021_11_11 select * from report_profit_loss_half_hour where report_date BETWEEN '2021-11-11' AND '2021-11-12';
Query OK, 13961 rows affected (0.28 sec)
Records: 13961 Duplicates: 0 Warnings: 0

performance_schema_max_table_instances=150
table_definition_cache=150
table_open_cache=64

sed -i '/ntpdate/d' /var/spool/cron/root

/usr/local/webserver/mysql5.6.19/bin/mysqladmin -uroot -pE6f23iZ3HU6kFjV0xSvWYUn8vvoyPW7S --socket=/data/hgame_lock/run/mysql.sock shutdown
/usr/local/webserver/mysql5.6.19/bin/mysqld_safe --defaults-file=/usr/local/webserver/mysql5.6.19/3307.cnf --user=mysql &

root@localhost hgame MASTER:(none)> show variables like "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 1500 |
+-----------------+-------+
1 row in set (0.00 sec)

添加索引

/usr/bin/pt-online-schema-change -ulocaldba -pMYE2E1DkLwoG5KuiXiLtSVC4dReWofc0 -S /data/mysql3306/run/mysql.sock D=hgame,t=user_pointset --alter "MODIFY COLUMN childpointsets_indefinite_max varchar(2048) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'json格式，直属下级不定位最大返点设置集(array( 彩种id=>值, 彩种id=>值))'" --recursion-method=none --no-check-replication-filters --charset=utf8 --chunk-size=300 --execute --print

git update-index --assume-unchanged application/extra/maccms.php application/database.php application/config.php .user.ini application/extra/timming.php
git pull
chown -R www.www oaiwehfj.com

acme.sh --issue -d preqitian3.91yule88.com --webroot /usr/local/webserver/nginx/html/ --debug
acme.sh --install-cert -d preqitian3.91yule88.com --key-file /usr/local/webserver/nginx/ssl/preqitian3.91yule88.com.key --fullchain-file /usr/local/webserver/nginx/ssl/fullchain.cer --reloadcmd "/usr/local/webserver/nginx/sbin/nginx -s reload" --debug
"/root/.acme.sh"/acme.sh --cron --home "/root/.acme.sh" --force > /dev/null

/root/.acme.sh/newpush.123ssc.net/newpush.123ssc.net.cer
/root/.acme.sh/newpush.123ssc.net/newpush.123ssc.net.key

GRANT SELECT, UPDATE ON `passport`.`userfund` TO 'hqttest'@'35.221.172.11'
flush privileges;

crontab -l |grep createlock_xx_ffc.php |awk '{print $(NF-1)}' |grep -i '[0-9]' |awk -F '_' '{print $NF}'

/usr/local/webserver/redis/bin/redis-cli -h 10.6.0.25 -p 7000 -c lrange chat:message:8385:336868 0 -1 > /tmp/ada88
/usr/local/webserver/redis/bin/redis-cli -h 10.4.1.5 -p 7000 -c lrange chat:message:1328669:1906123 0 -1 > /tmp/cfxf999

change master to master_host='10.140.0.10', master_user='replication',master_password='wjOpCyVJq7Ezmx8HNFAnfwgBrRQO7rmM',master_port=3306,master_auto_position=1;

/usr/local/webserver/mysql5.7/bin/mysqlbinlog ----no-defaults base64-output=decode-rows -v mysql-bin.001681 |grep -i '31455957' --color -C 30

INSERT INTO `issueinfo` VALUES (141220,43,'','19100101','2019-10-01','2019-10-01 06:59:00','2019-10-01 08:44:00','2019-10-01 08:44:00','2019-10-01 08:46:00','0000-00-00 00:00:00',0,'0000-00-00 00:00:00',0,0,2,0,0,0,0,0,2,0,2,'',0,0,0);
SET @@GLOBAL.GTID_PURGED='3ca19de1-933f-11e9-affe-42010a8c0007:1-1088091,
d7fb365e-9330-11e9-8609-42010a8c000a:1-259532948';

解决方案：去redis数据目录，将aof文件备份，用redis-check-aof工具修复

[root@db redis]# cd /usr/local/redis/bin

[root@db redis]# cp appendonly.aof appendonly.aof.bak

[root@db redis]# redis-check-aof --fix appendonly.aof

0x        c93488e5: Expected prefix '

AOF analyzed: size=3375772775, ok_up_to=3375663333, diff=109442

This will shrink the AOF from 3375772775 bytes, with 109442 bytes, to 3375663333 bytes

Continue? [y/N]: y

Successfully truncated AOF

 

 

zcat default.log.gz |grep -i 'cheng6851'

 

's1' and 'username=paeza881@th'

 

access_log_001|grep  " +0800]"|awk -F\" '{print$7,$3}'|sort -nr|head -n 10

nmap -sS 114.139.178.253 

screen -S  + "新进程名字"   //创建一个进程

screen -ls  // 查看当前所有的进程

screen -r +"进程名字或编号"  // 进入某个链

CTRL + A + D // 退出当前进程，这是一个组合键

1.访问次数最多的前10个IP

awk '{print $1}' default.log|sort|uniq -c|sort -rn|head -n 10

2.访问次数最多的10个页面。

awk '{print $7}' default.log|sort|uniq -c|sort -rn|head -n 10

3.访问最多的时间，取前十个。

awk '{print $4}'  xkwx-access.log|cut -c 14-18|sort|uniq -c|sort -rn|head -n10

4.查看下载次数最多的文件，显示前10个

awk '{print $7}' default.log|awk -F '/' '{print $NF}'|sort|uniq -c|sort -rn|head -n 10

5.统计网站流量，以M为单位。

awk '{sum+=$10} END {print sum/1024/1024}' default.log

trace -o /tmp/output.txt -T -tt -F -e trace=all -p 172915

6.统计IP平均流量、总流量。

awk 'BEGIN {print"ip average total"}{a[$1]+=$10;b[$1]++}END{for(i in a)print i,a[i]/1024/1024/b[i]"MB",  a[i]/1024/1024"MB"}' xkwx-access.log |column -t

#截取二月10号18点到20点之间日志

sed -n '/10\/Feb\/2013:18:[0-9][0-9]:[0-9][0-9]/,/10\/Feb\/2013:20:[0-9][0-9]:[0-9][0-9]/p'  access.log

awk '$9 ~ /403/ {print $7}' default.log|sort|uniq -c|sort -rn|head -n 80

awk '$9 ~ /404/ {print $7}' default.log|sort|uniq -c|sort -rn|head -n 80

STOP SLAVE;

SET @@SESSION.GTID_NEXT = '8fc8d9ac-a62b-11e6-a3ee-a4badb1b4a00:7649';

BEGIN; COMMIT;

SET @@SESSION.GTID_NEXT = AUTOMATIC;

mysqlbinlog /data/mysql/binlogs/server1.00002 00 –start-postion=1499 –stop-position=1646 –rewrite-db=’库名’> 文件

 begin commit  rollback

ifconfig |awk 'NR==2{print $2}' | cut -d : -f 2

mail_ok(){

        python /data/shell/mail.py "admin@weststarinc.co" "$name_ok" "$name_ok"

        #echo "$name_ok" | mail -s "$name_ok" admin@weststarinc.co

        }

mail_error(){

        #uuencode /tmp/clear.txt error.txt | mail -s "$name_error" admin@weststarinc.co

        python /data/shell/mail.py "admin@weststarinc.co" "$name_error" "<pre><h3>`cat $file`</pre>"

        }

sed -i 's/"//g' /tmp/2019-07-19hx_outip.txt

set global binlog_format=row;

binlog_format=row

//删除当前数据库中的所有Key

flushdb

//删除所有数据库中的key

Flushall

v

redis-cli -h ip -p port keys "*"| xargs redis-cli -h ip -p port del

rename .txt .js *.txt

for i in `ls | grep .txt`; do newfile=`echo $i | sed 's/\.txt/.js/g'` mv $newfile $i ;done

# for i in `ls | grep .txt | sed 's/\.txt/.js/g'`; do array[A]=$i echo ${array[*]}; done

Show global variables like ‘read_only’

Set global read_only=’on’

pt-query-digest  mysql-slow.log  --since '2019-12-30 00:00:00' --until '2019-12-30 23:59:59'

show binary logs;

 purge binary logs to 'mysql-bin.010900';

/usr/bin/pt-online-schema-change  -ulocaldba -pRiURQEUADwfVmtTyatNmJAiRPoTKCWV1 -S /data/mysql3306/run/mysql.sock D=hgame,t=projects  --alter="MODIFY COLUMN projectid bigint(11)  NOT NULL AUTO_INCREMENT"  --critical-load="Threads_running=200" --recursion-method=none --no-check-replication-filters --charset=utf8  --chunk-size=300 --dry-run --print

/usr/bin/pt-online-schema-change  -ulocaldba -pRiURQEUADwfVmtTyatNmJAiRPoTKCWV1 -S /data/mysql3306/run/mysql.sock D=hgame,t=projects  --alter="MODIFY COLUMN projectid bigint(11)  NOT NULL AUTO_INCREMENT"  --critical-load="Threads_running=200" --recursion-method=none --no-check-replication-filters --charset=utf8  --chunk-size=300  --execute --print

smem -p -s  uss -k | grep php | wc -l 按内存占比来判断进程数量

https://www.cnblogs.com/kenshinobiy/p/7470635.html

https://xueyuanjun.com/post/7522.html

https://xueyuanjun.com/post/7514.html

https://zhuanlan.zhihu.com/p/301690909