零散note

sudo lsof -i -P -n | grep LISTEN
sudo netstat -tulpn | grep LISTEN
sudo ss -tulpn | grep LISTEN
sudo lsof -i:22 ## see a specific prot such as 22 ##
sudo nmap -sTU -O ip-address-here

vim my.cnf
basedir = /usr/local/mysql datadir=/data/mysql/3306/data pid_file = /data/mysql/3306/data/mysqld.pid  user = mysql port = 3306 mysqlx_port = 3306 msqlx_socket = /data/mysql/3306/data/mysqlx.sock  socket = /data/mysql/3306/data/mysql.sock log_error = /data/mysql/3306/data/mysqld.err log_timestamps = system)

# 压缩文件 file1 和目录 dir 2 到test.tar.gz
tar -zcvf test.tar.gz file1 dir2
# 解压 test.tar.gz(将c 换成x 即可）
tar -zxvf test.tar.gz
#列出压缩文件内容
tar -ztvf test.tar.gz
#sz,rz
sz test.tar.gz (传出文件）
rz -E test.tar.gz(传入文件）
---替换文件里面内容
sed -i 's/utf8mb4_0900_as_cs/utf8mb4_0900_bin/g' test.sql

----后台执行文件
 chmod u+x importddl.sh
 nohum ./importddl.sh &
----切割文件
cat test.sql | egrep 'account_' > a.sql 
---检索文件内容打印
grep -E "INSERT |INTO|account_*" a.txt 

#mysqldump
mysqldump -S /data/mysql/3306/data/mysql.sock --single -transaction --master -data = 2-E -R --triggers -A > full_backup.sql

mysql -S /data/mysql/3306/data/mysql.sock --single -transaction --master -data = 2-E -R --triggers -A > full_backup.sql
----- 导出表数据
/usr/local/mysql/bin/mysqldump -u root -p -h 172.31.65.99 -P 4000 -t dt_b2b account01 account02 --extended-insert = false --insert-ignore >/data/table_account.sql
----- 导出数据库数据
/usr/local/mysql/bin/mysqldump -u root -p -h 172.31.65.99 -P 4000 --databases dt-activity-data dt_b2b dt_player > full_klm_database.sql
-----导出数据结构
/usr/local/mysql/bin/mysqldump -u root -p -h 172.31.65.99 -P 4000 --no-data --databases bigdata_report --skip-lock-tables > full_database.sql
-----insert-ignore  导出数据
/usr/local/mysql/bin/mysqldump -u root -p -h 172.31.65.99 -P 4000 -t dt_b2b game_data language_relation --extended-insert=false --insert-ignore > /home/data/dt_b2b.sql

#查询数据库里面内容
select table_schema '数据库', table_name '表',table_collation '排序规则',concat('alter_table ', table_schema,'.' table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') '修正表规则SQL' from information_schema.tables where table_collation != 'utf8mb4_unicode_ci' and TABLE_SCHEMA not in ('dt_game_data');

#MGR
https://greatsql.cn/docs/8032-25/user-manual/8-mgr/2-mgr-install-deploy.html

# maxwell 管理脚本
sudo yum install java-11-openjdk-devel -y
/usr/local/bin/supervisord -c /etc/supervisord.conf
/usr/local/bin/supervisorctl status
/usr/local/bin/supervisorctl restart all
/usr/local/bin/supervisorctl stop all

#maxwell to kafka
#172.31.65.116 mysql
#172.31.65.96 kafka
/home/deploy/www/test-maxwell-risk-bettingrecord/bin/maxwell --user='maxwell' --password='TmwhJm1heHdlbGw=' --host='172.31.65.116' --product=kafka --kafka.bootstrap.server=172.31.65.96:9092 --kafka_topic=maxwell
# --producer=kafka:将数据写入到kafka
# --kafka.bootstrap.servers:kafka broker 集群地址
# --kafka_topic: 写入到kafka指定的topic，注意不需要提前创建topic。
/home/deploy/www/test-maxwell-risk-bettingrecord/bin/../lib/*:/home/deploy/www/test-maxwell-risk-beggingrecord/bin/../lib/kafka-clients/kafka-clients-2.7.0.jar com.zendesk.maxwell.Maxwell

# 创建用户密码
select concat('create user','usesrname','@','ip%','identified by ','"",right(to_base64(concat('1cmNl!&','username')),16),"';") as create_user

# mysql-slave
create user ''@'' identified with mysql_native_password by ''
grant rel,connect on *.* to 
change master to master_host='ip',master_user='root',master_password='123456',master_port=3366,master_log_file='master-bin-.000001',master_log_pos=154;

master_host='ip',master_user='root',master_password='123456',master_port=3366,master_log_file='master-bin-.000001',master_log_pos=154,master_connect_retry=10;

#bytebase install

docker run --init -itd \
 --name npzrptbytebase \
 --restart always \
 -p 8081:8080 \
 --add-host host.docker.internal:10.111.101.37 \
 --health-cmd "curl --fail http://localhost:8080/healthz || exit 1" \
 --health-interval 5m \
 --health-timeout 60s \
 -v /data/bytebase:/var/opt/bytebase \
 bytebase/bytebase:2.14.0 \
--data /data/bytebase/data \
--port 8080

#bytebase run
 docker run --init \
   --name bytebase \
   --publish 8080:8080 \
   --volume ~/.bytebase/data:/var/opt/bytebase \
   bytebase/bytebase:2.14.0



 

#docker
#1 首先配置yum仓库
 curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrrors.aliyun.com/repo/Centos-vault-8.5.2111.repo
 sed -i -e '/mirrors.cloud.aliyuncs.com/d' -e '/mirrors.aliyuncs.com/d' /etc/yum.repos.d/CentoOS-Base.repo
# 2 设置镜像的仓库
 wget https://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo
# 3 安装容器相关的 docker-ce（社区版） docker-ee(企业版)
yum install docker-ce
# 4 启动docker 服务并设置开机自启动
 systemctl start docker
 systemctl enable docker

#延迟从库快速接管测试
原文出处#https://dev.mysql.com/doc/refman/8.0/en/replica-logs.html
1:主库插入数据
insert into dba_test value(1,'saborn');
2:shutdown;(模拟故障）

3:show slave status\G;
slave_io_running:connection （延迟从库，io线程并未停止，sql线程正常）
slave_sql_running:yes

---从库
use game_rocord
 从库延迟最新数据查询不到
select * from dba_test;
--(关闭SQL线程。重置延迟为0，启动SQL线程，恢复数据）
1:stop salve SQL_THREAD
2:change master to master_dely - 0;
3: start slave sql_thread;
4:select * from dba_test;

https://blog.csdn.net/play_big_knife
https://www.bilibili.com/video/BV1wz4y127fd/