兜兜    2021-09-22 10:18:21    2021-09-24 09:41:49   

k8s cephfs
#### 环境介绍 ```sh ceph集群节点: 172.16.100.1:6789,172.16.100.2:6789,172.16.100.11:6789 ceph version 15.2.13 (c44bc49e7a57a87d84dfff2a077a2058aa2172e2) octopus (stable) ceph client:15.2.14 (pod镜像:"elementalnet/cephfs-provisioner:0.8.0") ``` `注意:目前官网的quay.io/external_storage/cephfs-provisioner:latest镜像ceph版本为13.x,跟ceph集群的版本15不匹配,导致pv一直是pengding状态。这里使用第三方镜像:elementalnet/cephfs-provisioner:0.8.0` #### 安装ceph k8s节点安装客户端 ```sh $ yum install ceph-common -y ``` 拷贝ceph节点的key到k8s节点 ```sh $ scp /etc/ceph/ceph.client.admin.keyring 172.16.100.100:/etc/ceph ``` 配置访问ceph的secert ```sh $ ceph auth get-key client.admin | base64 QVFEa0RFTmhYQ1UzQUJBQXFmSWptMFJkSVpGaC9VR0V4M0RNc3c9PQ== ``` ```sh $ cat >cephfs-secret.yaml<<EOF apiVersion: v1 kind: Secret metadata: name: ceph-secret-admin namespace: cephfs type: "kubernetes.io/rbd" data: key: QVFEa0RFTmhYQ1UzQUJBQXFmSWptMFJkSVpGaC9VR0V4M0RNc3c9PQ== #替换上面的输出内容 EOF ``` ```sh $ kubectl create -f cephfs-secret.yaml ``` #### 安装cephfs provisioner 参考:https://github.com/kubernetes-retired/external-storage/tree/master/ceph/cephfs/deploy 创建RBAC ```sh $ cat >cephfs-rbac.yaml <<EOF --- apiVersion: v1 kind: ServiceAccount metadata: name: cephfs-provisioner namespace: cephfs --- apiVersion: rbac.authorization.k8s.io/v1 kind: Role metadata: name: cephfs-provisioner namespace: cephfs rules: - apiGroups: [""] resources: ["secrets"] verbs: ["create", "get", "delete"] - apiGroups: [""] resources: ["endpoints"] verbs: ["get", "list", "watch", "create", "update", "patch"] --- apiVersion: rbac.authorization.k8s.io/v1 kind: RoleBinding metadata: name: cephfs-provisioner namespace: cephfs roleRef: apiGroup: rbac.authorization.k8s.io kind: Role name: cephfs-provisioner subjects: - kind: ServiceAccount name: cephfs-provisioner --- kind: ClusterRole apiVersion: rbac.authorization.k8s.io/v1 metadata: name: cephfs-provisioner namespace: cephfs rules: - apiGroups: [""] resources: ["persistentvolumes"] verbs: ["get", "list", "watch", "create", "delete"] - apiGroups: [""] resources: ["persistentvolumeclaims"] verbs: ["get", "list", "watch", "update"] - apiGroups: ["storage.k8s.io"] resources: ["storageclasses"] verbs: ["get", "list", "watch"] - apiGroups: [""] resources: ["events"] verbs: ["create", "update", "patch"] - apiGroups: [""] resources: ["services"] resourceNames: ["kube-dns","coredns"] verbs: ["list", "get"] --- kind: ClusterRoleBinding apiVersion: rbac.authorization.k8s.io/v1 metadata: name: cephfs-provisioner subjects: - kind: ServiceAccount name: cephfs-provisioner namespace: cephfs roleRef: kind: ClusterRole name: cephfs-provisioner apiGroup: rbac.authorization.k8s.io EOF ``` ```sh $ kubectl create -f cephfs-rbac.yaml ``` 创建cephfs-provisioner ```sh $ cat > cephfs-provisioner.yaml <<EOF apiVersion: apps/v1 kind: Deployment metadata: name: cephfs-provisioner namespace: cephfs spec: replicas: 1 selector: matchLabels: app: cephfs-provisioner strategy: type: Recreate template: metadata: labels: app: cephfs-provisioner spec: containers: - name: cephfs-provisioner #image: "quay.io/external_storage/cephfs-provisioner:latest" #ceph集群版本为15.2,该版本对应的ceph客户端太旧,镜像没有更新,替换下面的镜像 image: "elementalnet/cephfs-provisioner:0.8.0" env: - name: PROVISIONER_NAME value: ceph.com/cephfs - name: PROVISIONER_SECRET_NAMESPACE value: cephfs command: - "/usr/local/bin/cephfs-provisioner" args: - "-id=cephfs-provisioner-1" serviceAccount: cephfs-provisioner EOF ``` ```sh $ kubectl create -f cephfs-provisioner.yaml ``` #### 创建存储类 ```sh $ cat > cephfs-storageclass.yaml <<EOF kind: StorageClass apiVersion: storage.k8s.io/v1 metadata: name: cephfs namespace: cephfs provisioner: ceph.com/cephfs parameters: monitors: 172.16.100.1:6789,172.16.100.2:6789,172.16.100.11:6789 adminId: admin adminSecretName: ceph-secret-admin adminSecretNamespace: cephfs claimRoot: /pvc-volumes EOF ``` ```sh $ kubectl create -f cephfs-storageclass.yaml ``` #### 创建测试pvc ```sh $ cat > cephfs-test-pvc.yaml <<EOF kind: PersistentVolumeClaim apiVersion: v1 metadata: name: cephfs-test-pvc-1 annotations: volume.beta.kubernetes.io/storage-class: "cephfs" spec: accessModes: - ReadWriteMany resources: requests: storage: 500Mi EOF ``` ```sh $ kubectl create -f cephfs-test-pvc.yaml ``` 获取pvc ```sh $ kubectl get pvc NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE cephfs-test-pvc-1 Bound pvc-571ae252-b080-4a67-8f3d-40bda1304fe3 500Mi RWX cephfs 2m1s ``` 获取pv ```sh $ kubectl get pv NAME CAPACITY ACCESS MODES RECLAIM POLICY STATUS CLAIM STORAGECLASS REASON AGE pvc-571ae252-b080-4a67-8f3d-40bda1304fe3 500Mi RWX Delete Bound default/cephfs-test-pvc-1 cephfs 2m3s ``` #### 创建nginx挂载pvc ```sh $ cat > cephfs-test-busybox-deployment.yml <<EOF apiVersion: apps/v1 kind: Deployment metadata: name: cephfs-test-deploy-busybox spec: replicas: 3 selector: matchLabels: app: cephfs-test-busybox template: metadata: labels: app: cephfs-test-busybox spec: containers: - name: busybox image: busybox command: ["sleep", "60000"] volumeMounts: - mountPath: "/mnt/cephfs" name: cephfs-test-pvc volumes: - name: cephfs-test-pvc persistentVolumeClaim: claimName: cephfs-test-pvc-1 EOF ``` ```sh $ kubectl create -f cephfs-test-busybox-deployment.yml ``` ```sh $ kubectl get pods NAME READY STATUS RESTARTS AGE cephfs-test-deploy-busybox-56556d86ff-4dmzn 1/1 Running 0 4m28s cephfs-test-deploy-busybox-56556d86ff-6dr6v 1/1 Running 0 4m28s cephfs-test-deploy-busybox-56556d86ff-b75mw 1/1 Running 0 4m28s ``` 测试pod挂载的cephfs文件读写是否同步 ```sh $ kubectl exec -ti cephfs-test-deploy-busybox-56556d86ff-4dmzn sh / # cd /mnt/cephfs/ /mnt/cephfs # ls /mnt/cephfs # touch cephfs-test-deploy-busybox-56556d86ff-4dmzn /mnt/cephfs # exit ``` ```sh $ kubectl exec -ti cephfs-test-deploy-busybox-56556d86ff-6dr6v sh / # cd /mnt/cephfs/ /mnt/cephfs # ls cephfs-test-deploy-busybox-56556d86ff-4dmzn #pod之间同步成功 ```
阅读 26 评论 0 收藏 0
阅读 26
评论 0
收藏 0

兜兜    2021-08-26 18:05:39    2021-08-26 18:09:23   

kubernetes k8s
#### 1.查看pod的docker容器ID ```bash $ kubectl get pod -n kube-system nginx-ingress-controller-8489c5b8c4-fccs5 -o json ``` ```yaml ... "containerStatuses": [ { "containerID": "docker://eda6562ab8d504599016d4dba8ceea4b9b255dbf97446f044ce89ad4410ab49a", "image": "registry-vpc.cn-shenzhen.aliyuncs.com/acs/aliyun-ingress-controller:v0.44.0.3-8e83e7dc6-aliyun", "imageID": "docker-pullable://registry-vpc.cn-shenzhen.aliyuncs.com/acs/aliyun-ingress-controller@sha256:7238b6230b678b312113a891ad5f9f7bbedc7839a913eaaee0def8aa748c3313", "lastState": { "terminated": { "containerID": "docker://e9a6d774429c0385be4f3a3129860677a4380277bfe5647563ae4541335111a7", "exitCode": 255, "finishedAt": "2021-07-09T06:48:39Z", "reason": "Error", "startedAt": "2021-07-08T03:29:36Z" } }, "name": "nginx-ingress-controller", "ready": true, "restartCount": 1, "started": true, "state": { "running": { "startedAt": "2021-07-09T06:48:56Z" } } } ], ... ``` #### 2.查看网卡接口索引号 ```bash $ docker exec eda6562ab8d504599016d4dba8ceea4b9b255dbf97446f044ce89ad4410ab49a /bin/bash -c 'cat /sys/class/net/eth0/iflink' ``` ```bash 7 ``` #### 3.查看网卡接口索引对应的veth网卡 ```bash $ IFLINK_INDEX=7 #IFLINK_INDEX值为上面查询的结果 $ for i in /sys/class/net/veth*/ifindex; do grep -l ^$IFLINK_INDEX$ $i; done ``` ```bash /sys/class/net/vethfe247b7f/ifindex ``` #### 4.tcpdump抓包pod ```bash $ tcpdump -i vethfe247b7f -nnn ``` ```bash listening on vethfe247b7f, link-type EN10MB (Ethernet), capture size 262144 bytes 18:04:47.362688 IP 100.127.5.192.25291 > 10.151.0.78.80: Flags [S], seq 3419414283, win 2920, options [mss 1460,sackOK,TS val 2630688633 ecr 0,nop,wscale 0], length 0 18:04:47.362704 IP 10.151.0.78.80 > 100.127.5.192.25291: Flags [S.], seq 3153672948, ack 3419414284, win 65535, options [mss 1460,sackOK,TS val 1408463572 ecr 2630688633,nop,wscale 9], length 0 18:04:47.362857 IP 100.127.5.192.25291 > 10.151.0.78.80: Flags [.], ack 1, win 2920, options [nop,nop,TS val 2630688633 ecr 1408463572], length 0 ```
阅读 46 评论 0 收藏 0
阅读 46
评论 0
收藏 0

兜兜    2021-08-26 14:51:18    2021-09-10 12:44:37   

k8s kubernets
这里以用户访问 https://gw.example.com gateway服务为例,整个网络包的调用过程如下: `CLIENT->阿里云SLB->K8S NODE(IPVS/kube-proxy)->INGRESS POD(nginx controller)->GATEWAY POD(gateway服务)` ```BASH CLIENT IP: CLIENT_IP SLB IP: 47.107.x.x K8S NODE IP: 172.18.238.85 INGRESS POD IP: 10.151.0.78 GATEWAY POD IP: 10.151.0.107 ``` #### 1.CLIENT-->阿里云SLB ```bash 解析gw.example.com 47.107.x.x(SLB公网ip), 数据包到达阿里云SLB(CLIENT_IP:RANDOM_PORT---->47.107.x.x:443) ``` #### 2.阿里云SLB-->K8S NODE(IPVS/kube-proxy) ```bash 阿里云SLB配置后端虚拟服务: TCP:443-->172.18.238.85:30483 数据包到达K8S NODE(CLIENT_IP:RANDOM_PORT---->172.18.238.85:30483) ``` K8S NODE抓包 ```bash $ tcpdump -i eth0 ip host CLIENT_IP -n 14:39:33.043508 IP CLIENT_IP.RANDOM_PORT > 172.18.238.85.30483: Flags [S], seq 1799504552, win 29200, options [mss 1460,sackOK,TS val 1092093183 ecr 0,nop,wscale 7], length 0 ``` #### 3.K8S NODE(IPVS/kube-proxy)-->INGRESS POD(nginx controller) IPVS配置后端服务: ```BASH $ ipvsadm -L -n TCP 172.18.238.85:30483 rr -> 10.151.0.78:443 Masq 1 2 40 -> 10.151.0.83:443 Masq 1 8 42 ``` ```BASH 数据包到达nginx ingress(CLIENT_IP:RANDOM_PORT---->10.151.0.78.443) ``` K8S NODE抓包nginx ingress服务([抓包pod教程](https://ynotes.cn/blog/article_detail/260)) ```bash $ tcpdump -i vethfe247b7f -nnn |grep "\.443" #vethfe247b7f为ingress controller pod的网卡 16:45:28.687578 IP CLIENT_IP.RANDOM_PORT > 10.151.0.78.443: Flags [S], seq 2547516746, win 29200, options [mss 1460,sackOK,TS val 1099648828 ecr 0,nop,wscale 7], length 0 ``` #### 4.INGRESS POD(nginx controller)->GATEWAY POD(gateway服务) ```bash $ kubectl get pods -o wide --all-namespaces|grep 10.151.0.78 kube-system nginx-ingress-controller-8489c5b8c4-fccs5 1/1 Running 1 49d 10.151.0.78 cn-shenzhen.172.18.238.85 <none> <none> ``` ```BASH 数据包到达gateway服务(10.151.0.78.57270---->10.151.0.107.18880) ``` K8S NODE抓包gateway服务 ```bash $ tcpdump -i veth553c1000 -nnn port 18880 17:05:58.463497 IP 10.151.0.78.57270 > 10.151.0.107.18880: Flags [S], seq 3538162899, win 65535, options [mss 1460,sackOK,TS val 878505289 ecr 0,nop,wscale 9], length 0 ```
阅读 39 评论 0 收藏 0
阅读 39
评论 0
收藏 0

兜兜    2021-08-20 16:24:24    2021-08-21 15:23:08   

jenkins
#### _介绍:jenkins的Image Tag Parameter插件支持harbor仓库中获取项目的Tag,可惜阿里云容器镜像仓库不支持Docker V2 API,不过阿里云镜像仓库提供自己一套API。_ #### _`解决方案:python Flask封装阿里云的API(阿里云API是通过access_key和access_secret认证授权,REST list Parameter插件目前不支持),jenkins通过REST list Parameter插件获取数据。`_ #### 一、封装阿里云的API 1.1 python安装Flask和阿里云SDK ```bash pip install flask pip install aliyun-python-sdk-cr==4.1.2 ``` 1.2 添加tools.py(封装阿里云的SDK) ```python #!/usr/bin/env python #coding=utf-8 from aliyunsdkcore.client import AcsClient from aliyunsdkcore.acs_exception.exceptions import ClientException from aliyunsdkcore.acs_exception.exceptions import ServerException from aliyunsdkcore.auth.credentials import AccessKeyCredential from aliyunsdkcore.auth.credentials import StsTokenCredential from aliyunsdkcr.request.v20181201.ListRepoTagRequest import ListRepoTagRequest from aliyunsdkcr.request.v20181201.GetRepositoryRequest import GetRepositoryRequest import json class ContainerImage: def __init__(self, access_key, access_secret, instance_id, region_id='cn-shenzhen', accept_format='json', encoding='utf-8'): self.client = AcsClient(region_id=region_id, credential=AccessKeyCredential(access_key,access_secret)) self.instance_id = instance_id self.accept_format = accept_format self.encoding = encoding def get_repo(self, space_name, repo_name): request = GetRepositoryRequest() request.set_accept_format(self.accept_format) request.set_InstanceId(self.instance_id) request.set_RepoNamespaceName(space_name) request.set_RepoName(repo_name) response = self.client.do_action_with_exception(request) return json.loads(str(response,encoding=self.encoding)) def list_repo_tag(self, space_name, repo_name): repo_obj = self.get_repo(space_name, repo_name) repo_id = repo_obj['RepoId'] request = ListRepoTagRequest() request.set_accept_format(self.accept_format) request.set_InstanceId(self.instance_id) request.set_RepoId(repo_id) response = self.client.do_action_with_exception(request) return json.loads(str(response,encoding=self.encoding)) ``` 1.3 添加Flask的文件app.py ```python from flask import Flask from tools import ContainerImage #导入tools中的ContainerImage类 #配置access_key和access_secret access_key='LTAI5tG3YCyHxxxxxxxxxx' access_secret='oNBXXKfIxxxxxxxxxxxxxxxxx' region_id='cn-shenzhen' instance_id='cri-xxxxxxxxxx' container_image=ContainerImage(access_key, access_secret, instance_id) app = Flask(__name__) #通过url路径获取space_name和repo_name @app.route('/repo/<space_name>/<repo_name>/tags') def list_tags(space_name,repo_name): list_repo_tags=container_image.list_repo_tag(space_name,repo_name) return list_repo_tags if __name__ == '__main__': app.run(host='0.0.0.0', debug=True) ``` 1.4 启动Flask ```bash python app.py ``` 1.5 测试结果 ```bash curl http://172.16.100.202:5000/repo/<space_name>/<repo_name>/tags ``` ```json { "Code": "success", "Images": [ { "Digest": "16c579443109881cd3ba264913824cb074d8e977bfd89d5860aaafad0b10194f", "ImageCreate": 1629278747000, "ImageId": "f79086b9b1a4532e44b30efbf761fde76792cd61be26e9bf5f19469d1e8e358d", "ImageSize": 55157349, "ImageUpdate": 1629278747000, "Status": "NORMAL", "Tag": "master-7d9acb6-17" }, { "Digest": "d577c281172233318ee4d9394882ae0bb6582bb01efc694654890ebf8118b0cf", "ImageCreate": 1629272078000, "ImageId": "8b52daeee868663c3d1fcd49447d17cf8bdd7f9b87ba07904e3a675e008ce90f", "ImageSize": 55157354, "ImageUpdate": 1629272078000, "Status": "NORMAL", "Tag": "master-7d9acb6-16" } ], "IsSuccess": true, "PageNo": 1, "PageSize": 30, "RequestId": "B81C478C-3607-590E-90EC-6C5120446D48", "TotalCount": 2 } ``` #### 三、jenkins pipeline配置REST list Parameter ```groovy parameters { RESTList( name: 'BUILD_IMAGE_TAG', description: '', restEndpoint: 'http://172.16.100.202:5000/repo/<space_name>/<repo_name>/tags', credentialId: '', mimeType: 'APPLICATION_JSON', valueExpression: '$.Images[*].Tag', cacheTime: 10, // optional defaultValue: '', // optional filter: '.*', // optional valueOrder: 'ASC' // optional ) } ```
阅读 48 评论 0 收藏 0
阅读 48
评论 0
收藏 0

兜兜    2021-08-12 16:12:02    2021-08-12 17:59:00   

ssl https certbot
`certbot(pip)安装要求:安装python3且安装了ssl模块,验证方式:import ssl,如果当前环境已满足要求,则直接跳到三、安装certbot` #### 一、安装openssl ```bash ##Download openssl file wget https://www.openssl.org/source/openssl-1.1.1a.tar.gz tar -xzvf openssl-1.1.1a.tar.gz ##decompression #Compile and install, install path is/usr/local/openssl cd openssl-1.1.1a ./config shared zlib --prefix=/usr/local/openssl && make && make install ./config -t make depend #Enter / usr/local to execute the following command ln -s /usr/local/openssl /usr/local/ssl ##Create Links #In/etc/Ld.so.confAt the end of the file, add the following: echo "/usr/local/openssl/lib" >> /etc/ld.so.conf #Execute the following command ldconfig #Set the environment variable for OPESSL and add it on the last line of the etc/profile file: cat >> /etc/profile <<EOF export OPENSSL=/usr/local/openssl/bin export PATH=\$OPENSSL:\$PATH:\$HOME/bin EOF ``` &nbsp; #### 二、安装python3 ```python wget https://www.python.org/ftp/python/3.9.2/Python-3.9.2.tgz#Download Python 3.9 tar zxvf Python-3.9.2.tgz #decompression cd Python-3.9.2 ``` 编辑文件Python3.9/Module/setup ```python # Socket module helper for socket(2) _socket socketmodule.c #Install socket module, source code is socketmodule.c # Socket module helper for SSL support; you must comment out the other # socket line above, and possibly edit the SSL variable: SSL=/usr/local/ssl _ssl _ssl.c \ #Install SSL module, source code is ssl.c -DUSE_SSL -I$(SSL)/include -I$(SSL)/include/openssl \ -L$(SSL)/lib -lssl -lcrypto ``` ```bash ./configure --with-openssl=/usr/local/openssl #Preinstall openssl directory --enable-optimizations #Optimize installation --with-ssl-default-suites=python #Install python's own ssl by default, #It's a little unclear--the difference between the with-openssl and--with-ssl-default-suites commands, but I still run them together make make install ``` ##### 测试python的ssl模块 ```python import ssl ``` &nbsp; #### 三、安装certbot ##### 安装python虚拟环境 ```bash python3 -m venv /opt/certbot/ /opt/certbot/bin/pip install --upgrade pip ``` ##### 安装certbot包 ```bash /opt/certbot/bin/pip install certbot certbot-nginx ln -s /opt/certbot/bin/certbot /usr/bin/certbot ``` certbot获取证书两种方式 `方式一:验证nginx获取证书` ```bash certbot certonly --nginx ``` `方式二:webroot文件获取证书` 修改nginx的server添加验证的location ```bash server { listen 443; server_name ynotes.cn www.ynotes.cn; ... # 配置webroot验证目录 location ^~ /.well-known/acme-challenge/ { default_type "text/plain"; root /var/www/letsencrypt; } } ``` webroot方式获取证书 ```bash certbot certonly --webroot --agree-tos --email sheyinsong@qq.com --webroot-path /var/www/letsencrypt --domains ynotes.cn ``` 配置nginx的SSL证书 ```bash server { listen 443; server_name ynotes.cn www.ynotes.cn; ssl on; ssl_certificate /etc/letsencrypt/live/www.ynotes.cn/fullchain.pem; ssl_certificate_key /etc/letsencrypt/live/www.ynotes.cn/privkey.pem; ssl_session_cache shared:SSL:1m; ssl_session_timeout 5m; ssl_protocols TLSv1 TLSv1.1 TLSv1.2; ssl_ciphers HIGH:!aNULL:!MD5; ssl_prefer_server_ciphers on; location ^~ / { root /var/www/html/v3/ynotes.cn; } error_page 500 502 503 504 /50x.html; error_page 404 https://www.ynotes.cn/; location = /50x.html { root html; } } ``` 添加计划任务 ```bash echo "0 0,12 * * * root /opt/certbot/bin/python -c 'import random; import time; time.sleep(random.random() * 3600)' && certbot renew -q" | sudo tee -a /etc/crontab > /dev/null ```
阅读 124 评论 0 收藏 0
阅读 124
评论 0
收藏 0

兜兜    2021-07-17 17:31:15    2021-07-17 17:31:15   

kubernets
阅读 167 评论 0 收藏 0
阅读 167
评论 0
收藏 0

兜兜    2021-07-05 00:27:42    2021-07-05 00:45:56   

sqlserver 锁表
查看锁表SQL(blocking_session_id为锁表session_id) ```sql SELECT es.session_id, database_name = DB_NAME(er.database_id), er.cpu_time, er.reads, er.writes, er.logical_reads, login_name, er.status, blocking_session_id, wait_type, individual_query = SUBSTRING (qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2), parent_query = qt.text, program_name, host_name, nt_domain, start_time FROM sys.dm_exec_requests er INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt WHERE es.session_id > 50 AND es.session_Id NOT IN (@@SPID) ORDER BY 1, 2 ``` 查询session_id的tcp连接信息(获取到对方的ip和端口) ```sql SELECT * FROM sys.dm_exec_connections WHERE session_id = [session_id] ``` `其他获取session_id的信息方法` 创建sp_who3 ```sql CREATE PROCEDURE sp_who3 ( @SessionID INT = NULL ) AS BEGIN SELECT SPID = er.session_id ,STATUS = ses.STATUS ,[Login] = ses.login_name ,Host = ses.host_name ,BlkBy = er.blocking_session_id ,DBName = DB_Name(er.database_id) ,CommandType = er.command ,SQLStatement = st.text ,ObjectName = OBJECT_NAME(st.objectid) ,ElapsedMS = er.total_elapsed_time ,CPUTime = er.cpu_time ,IOReads = er.logical_reads + er.reads ,IOWrites = er.writes ,LastWaitType = er.last_wait_type ,StartTime = er.start_time ,Protocol = con.net_transport ,ConnectionWrites = con.num_writes ,ConnectionReads = con.num_reads ,ClientAddress = con.client_net_address ,Authentication = con.auth_scheme FROM sys.dm_exec_requests er OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id WHERE er.session_id > 50 AND @SessionID IS NULL OR er.session_id = @SessionID ORDER BY er.blocking_session_id DESC ,er.session_id END GO ``` 查看session_id的信息 ```sql exec [db_name].dbo.sp_who3 [session_id] ```
阅读 161 评论 0 收藏 0
阅读 161
评论 0
收藏 0

兜兜    2021-06-16 20:11:18    2021-09-02 14:07:16   

mysql sqlserver SymmetricDS
##### 1.下载SymmetricDS [http://www.symmetricds.org/](https://www.symmetricds.org/) #### 2.安装配置 ##### 2.1解压安装包到E盘重命名为sym-38 ##### 2.2配置主从配置 拷贝安装包下的samples下的corp-000.properties和store-001.properties到目录engines 配置sqlserver(主) ```properties engine.name=corp-000 db.driver=net.sourceforge.jtds.jdbc.Driver db.url=jdbc:jtds:sqlserver://172.16.13.3:1433/dingding_source;sendStringParametersAsUnicode=false; useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880 db.user=sa db.password=xxxxxxxx registration.url= sync.url=http://172.16.13.3:8050/sync/corp-000 group.id=corp external.id=000 job.purge.period.time.ms=7200000 job.routing.period.time.ms=5000 job.push.period.time.ms=10000 job.pull.period.time.ms=10000 auto.registration=true initial.load.create.first=true ``` 配置mysql(从) ```properties engine.name=store-001 db.driver=com.mysql.jdbc.Driver db.url=jdbc:mysql://172.16.13.23/shudoon-dingding-source?tinyInt1isBit=false&useSSL=false db.user=root db.password=xxxxxx registration.url=http://172.16.13.3:8050/sync/corp-000 group.id=store external.id=001 job.routing.period.time.ms=5000 job.push.period.time.ms=10000 job.pull.period.time.ms=10000 ``` #### 3.初始化sym系统表 ```bat symadmin --engine corp-000 create-sym-tables ``` #### 4.重置sym系统表数据 ```bat dbimport --engine corp-000 E:\sym-38\samples\insert_sample.sql ``` #### 5.创建业务表 ```bat dbimport --engine store-001 --format XML E:\sym-38\samples\create_sample.xml ``` #### 6.启动master端 ```bat sym --engine corp-000 --port 8050 ``` #### 7.启动slave端 ```bat sym --engine store-001 --port 8010 ``` #### 8.初始化item表结构 ```bat symadmin --engine corp-000 send-schema item --node 001 ``` #### 8.初始化item表数据 ```bat symadmin --engine corp-000 reload-table item --node 001 ``` #### 9.新增表tCommunicationRecords同步 主库执行 ```sql insert into sym_channel (channel_id, processing_order, max_batch_size, enabled, description) values('c_tCommunicationRecords', 3, 100000, 1, ''); insert into sym_router (router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time) values('corp_tCommunicationRecords_2_store', 'corp', 'store', 'default',current_timestamp, current_timestamp); insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values('tCommunicationRecords','tCommunicationRecords','c_tCommunicationRecords',current_timestamp,current_timestamp); insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('tCommunicationRecords','corp_tCommunicationRecords_2_store', 200, current_timestamp, current_timestamp); ``` 命令行执行 ```bat symadmin --engine corp-000 send-schema tCommunicationRecords --node 001 symadmin --engine corp-000 reload-table tCommunicationRecords --node 001 ``` #### 常见问题: `1.处理sqlserver联合自增主键的表结构无法同步到mysql的问题?` 解决方法: 1.去掉建表语句中联合主键中非自增列,拷贝SQL语句手动在mysql建表。 2.修改sym_outgoing_batch表中batch_id对应数据的状态为OK ```sql update sym_outgoing_batch set status='OK' where batch_id='XXXXXX'; ``` 3.删除sym_data_event中对应batch_id和data_id的事件数据 ```sql delete from sym_data_event where batch_id='XXXXXX' and data_id='YYYYYY'; ```
阅读 201 评论 0 收藏 0
阅读 201
评论 0
收藏 0

第 1 页 / 共 8 页
 
第 1 页 / 共 8 页