服务器配置LDAP

服务器配置LDAP显示mysql数据库数据

通过MobaXterm进入服务器，安装docker

卸载老版本

sudo apt-get remove docker docker-engine docker-ce docker.io

添加秘钥

curl -fsSL http://mirrors.aliyun.com/docker-ce/linux/ubuntu/gpg | sudo apt-key add -

设置存储库

sudo add-apt-repository "deb [arch=amd64] http://mirrors.aliyun.com/docker-ce/linux/ubuntu $(lsb_release -cs) stable"

安装

sudo apt-get update
sudo apt-get install docker-ce docker-ce-cli containerd.io docker-compose-plugin

查看版本

sudo docker version

查看状态

systemctl status docker

启动docker

sudo systemctl start docker

设置开机启动（可选）

sudo systemctl enable docker

demo验证（可选）

sudo docker run hello-world

进入容器（可选）

docker run -it ubuntu bash

清空（可选）

docker system prune

换源

#使用阿里云镜像加速器  上下方法选择其中一个
#源一
mkdir -p /etc/docker
tee /etc/docker/daemon.json <<-'EOF'
{
  "registry-mirrors": [
 	"https://hub-mirror.c.163.com",
    	"https://ghcr.io",
    	"https://mirror.baidubce.com"
]
}
EOF
systemctl daemon-reload
systemctl restart docker

#源二
(base) win10@win10:~$ sudo vim /etc/docker/daemon.json

填入下面内容

{
"registry-mirrors": [ "https://docker.mirrors.ustc.edu.cn" ]
}

重启docker和deamon

(base) win10@win10:~$ sudo systemctl daemon-reload
(base) win10@win10:~$ sudo systemctl restart docker

doker-compose安装

sudo apt install docker-compose

yml创建ldap-mysql

选定路径创建yml文件

(base) win10@win10:~$ mkdir ldap

(base) win10@win10:~$ vim /home/win10/ldap/compose.yml

填写内容如下

version: '2'
services: 
  db:
    image: mysql:latest
    volumes:
      - "./.data/db:/var/lib/mysql"
      - "./conf/mysql:/etc/mysql/conf.d"
    restart: always    
    ports: 
      - 3306:3306  
    environment:
      MYSQL_ROOT_PASSWORD: ldap
      MYSQL_DATABASE: ldap
      MYSQL_USER: vives
      MYSQL_PASSWORD: ldap

  ldap-client:
    image: osixia/phpldapadmin:latest
    hostname: vivesdata
    domainname: ldap.vives.be
    depends_on:
      - db
      - ldap
    links:
      - db
      - ldap:ldap.vives.be
    ports: 
      - 6443:443  
    restart: always
    environment:
      LDAP_DB_HOST: db:3306
      LDAP_DB_PASSWORD: ldap
      LDAP_DB_USER: vives
      LDAP_DB_NAME: ldap
      PHPLDAPADMIN_LDAP_HOSTS: ldap.vives.be   
      PHPLDAPADMIN_LDAP_CLIENT_TLS: "false"

  ldap:
    depends_on:
      - db
    image: osixia/openldap:latest    
    hostname: vivesdata
    domainname: ldap.vives.be
    ports:
      - "389:389"
    volumes:
      - "./.data/var/lib/ldap:/var/lib/ldap"
      - "./.data/etc/ldap/slapd.d:/etc/ldap/slapd.d"
    links:
      - db
    restart: always    
    environment: 
      LDAP_DB_HOST: db:3306
      LDAP_DB_PASSWORD: ldap
      LDAP_DB_USER: vives
      LDAP_DB_NAME: ldap 
      LDAP_ORGANISATION: Vives
      LDAP_DOMAIN: ldap.vives.be
      LDAP_ADMIN_PASSWORD: ldap
      LDAP_TLS: "false"

  ldapbackup:
    depends_on:
      - db
      - ldap  
    image: osixia/openldap-backup:latest  
    hostname: vivesdata
    domainname: ldap.vives.be
    #volumes:
    #  - "./.data/openldap/backup:/data/backup"
    #  - "./.data/etc/ldap/slapd.d:/etc/ldap/slapd.d"
    links:
      - db
      - ldap:ldap.vives.be
    restart: always    
    environment: 
      LDAP_DB_HOST: db:3306
      LDAP_DB_PASSWORD: ldap
      LDAP_DB_USER: vives
      LDAP_DB_NAME: ldap 
      LDAP_ORGANISATION: Vives
      LDAP_DOMAIN: ldap.vives.be
      LDAP_ADMIN_PASSWORD: ldap
      LDAP_BACKUP_CONFIG_CRON_EXP: "0 5 * * *"
      LDAP_BACKUP_DATA_CRON_EXP: "0 5 * * *"

进入刚刚创建文件的路径下

(base) win10@win10:~$ cd /home/win10/ldap/

运行一下命令创建环境

(base) win10@win10:~/ldap$ sudo docker-compose up

如果报错下面

ERROR: for ldap  Cannot start service ldap: driver failed programming external connectivity on endpoint ldap_ldap_1 (40c178b3b4e3                                                                    66905e743a9b9919edf1ad13ece57071014e8fdaf96f148db187): Error starting userland proxy: listen tcp4 0.0.0.0:389: bind: address alre                                                                    ady in use
ERROR: Encountered errors while bringing up the project.

说明389端口被占用，查看已有docker

sudo docker ps

停止运行该docker并删除该docker，如果NAMES不行就改成CONTAINER ID

sudo docker stop ldap_db_1
sudo docker rm ldap_db_1
#或者下面的命令查看创建失败的，将所有新建的都删除
(base) win10@win10:~/ldap$ sudo docker ps -a
(base) win10@win10:~/ldap$ sudo docker stop ldap-ldapbackup-1
(base) win10@win10:~/ldap$ sudo docker rm ldap-ldapbackup-1

如果没有报错，则直接跳过上面两步

重新创建环境，新版本的compose up命令如下

(base) win10@win10:~/ldap$ sudo docker compose up -d

如果还是报端口占用错误，查看端口所在进程

(base) win10@win10:~/ldap$ sudo lsof -i :389

杀死这个进程

(base) win10@win10:~/ldap$ sudo kill -9 3966705

然后重新运行，这个命令可以不显示运行过程

(base) win10@win10:~/ldap$ sudo docker compose up -d

另外开一个服务器窗口，进入compose.yml所在文件夹

运行进入 ldap container:

(base) win10@win10:~$ cd /home/win10/ldap/
(base) win10@win10:~/ldap$ sudo docker-compose exec ldap /bin/bash

运行搜索示例

ldapsearch -x -h localhost -b dc=ldap,dc=vives,dc=be -D "cn=admin,dc=ldap,dc=vives,dc=be" -w ldap

配置网页端PhpLdapAdmin

在ldap所在服务器上打开下面网页登陆

https://localhost:6443
login: cn=admin,dc=ldap,dc=vives,dc=be psw: ldap

允许远程mysql连接

进入mysql容器

在服务器再开一个窗口，此处容器名字是mysql所对应的

(base) win10@win10:~$ cd /home/win10/ldap/
(base) win10@win10:~/ldap$ sudo docker exec -it ldap-db-1 /bin/bash

进入mysql

密码为ldap

mysql -uroot -p
use ladp;

修改权限

此处不用修改代码

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';  ### 123456 mysql的登录密码flush privileges;

连接软件LDAP Admin

连接软件Navicat

给LDAP导入自定义的schema

因为ldap中只有部分属性名，不满足我们自己数据多样化需求，所以需要针对我们自己的数据创建新的属性名导入。

修改mysql数据库文件

ldap中属性名不支持下划线，而且需要唯一标识，所以先将我们从navicat导出的txt文件进行修改，py文件代码如下,其中路径需要对应修改

import os
# Path to the directory where the txt files are stored
dir_path = '/home/win/16t1/ldap/ldap_txt'
new_dir_path = '/home/win/16t1/ldap/new_txt'
# Read each txt file and process column names
for j,filename in enumerate(os.listdir(dir_path)):
    if filename.endswith(".txt"):
        file_path = os.path.join(dir_path, filename)
        with open(file_path, 'r') as file:
            # Read the lines of the file
            lines = file.readlines()
        
        # Assume the first line contains the column names
        columns = lines[0].strip().split('\t')  # Assuming tab-delimited file
        
        # Process column names
        processed_columns = []
        for i, col in enumerate(columns):
            # Remove underscores and capitalize the first letter of each word
            new_col = ''.join(word.capitalize() for word in col.replace('_', ' ').split())
            # Add the table number to the column name
            processed_columns.append(f'{new_col[:-1]}{j}"')
        
        # Join the processed columns back into a string
        lines[0] = '\t'.join(processed_columns) + '\n'

        new_filename =''.join(word.capitalize() for word in filename.replace('_', ' ').split()) # filename.replace('_','')
        new_file_path = os.path.join(new_dir_path, new_filename)
        # Write the modified content back to the file
        with open(new_file_path, 'w') as file:
            file.writelines(lines)

# Output a message when done
print("Column names processed and files updated.")

批量生成schema文件

分别添加包含数据表名和列名的schema，py文件内容如下

import pandas as pd
from icecream import install,ic
install()
import sys
input_file =sys.argv[1] #'/home/win/16t1/ldap/HS_ANNUAL_C_EMMSION_UPTAKE.txt'
n=sys.argv[2]
table_name = input_file.split('/')[-1].split('.')[0]
data = pd.read_csv(input_file,sep='\t')

data_columns = list(data.columns)
ic(data_columns)

with open(f'/home/win/16t1/ldap/schema/{table_name}.schema','w') as file:
    for i,col in enumerate(data_columns):
        data = f'''attributetype (
    2024.4.{n}.{i}
    NAME '{col}'
    DESC '{col}'
    SYNTAX 1.3.6.1.4.1.1466.115.121.1.15
    EQUALITY caseIgnoreMatch
    SUBSTR caseIgnoreSubstringsMatch
    SINGLE-VALUE )
'''
        file.write(data)
    table_data = f'''objectClass (
    2024.4.{n}
    NAME '{table_name}'
    DESC '{table_name}'
    SUP top
    MAY ( {" $ ".join(data_columns)} ))
'''
    file.write(table_data)

批量运行的sh文件内容如下

#!/bin/bash
# 文件夹路径作为参数
FOLDER_PATH='/home/win/16t1/ldap/new_txt'
# 检查文件夹路径是否已提供
if [[ -z "$FOLDER_PATH" ]]; then
  echo "Usage: $0 /path/to/folder"
  exit 1
fi

# 确保文件夹路径存在
if [[ ! -d "$FOLDER_PATH" ]]; then
  echo "The provided folder path does not exist."
  exit 1
fi

counter=1
# 遍历文件夹中的每个文件
for file in "$FOLDER_PATH"/*
do
  # 检查是否为文件
  if [[ -f "$file" ]]; then
    echo "Processing $file..."
    
    # 运行第一个 Python 脚本
    python /home/win/16t1/ldap/generate_schema.py "$file" "$counter"
    ((counter++))
    # 运行第二个 Python 脚本
    #python /home/win/16t1/ldap/generate_ldif.py "$file"
  fi
done

将生成的schema文件夹复制到/var/lib/ldap/路径

(base) win10@win10:~$ sudo cp -r /home/win10/ldap/schema /home/win10/ldap/.data/var/lib/ldap/

生成导入schema的config文件

import os

# Define the directory containing the schema files.
data_path = '/home/win/16t1/ldap/schema'
schema_dir = '/var/lib/ldap/schema'
config_file_path = '/home/win/16t1/ldap/1.conf'

# Write the include statements to the config file.
with open(config_file_path, 'w') as config_file:
    # List all files in the directory.
    for file in os.listdir(data_path):
        # Check if the file is a schema file.
        if file.endswith('.schema'):
            # Write the include statement to the config file.
            config_file.write(f'include {os.path.join(schema_dir, file)}\n')

print(f'Config file {config_file_path} has been generated.')

将生成的1.conf文件复制到/var/lib/ldap/路径

(base) win10@win10:~$ sudo cp -r /home/win10/ldap/1.conf /home/win10/ldap/.data/var/lib/ldap/

使用命令slaptest -f 1.conf -F ./;将会在当前目录生成一个./cn=config/cn=schema子目录,里面是每一个schema对应的ldif文件

root@vivesdata:/var/lib/ldap# slaptest -f 1.conf -F ./

针对每一个ldif文件,修改两行,将xxx修改为指定的objectClass名（比如ExtraNode``这种，就是对应``schema``的文件名），这里cn都填表名

dn: cn=xxx,cn=schema,cn=config      //第3行
cn: xxx                             //第5行

以下是批量修改的代码py

import re
import os
# Path to the directory where the txt files are stored
dir_path = '/home/win/16t1/ldap/cn=schema_old/'
new_dir_path = '/home/win/16t1/ldap/cn=schema/'
# Read each txt file and process it
for i,filename in enumerate(os.listdir(dir_path)):
    if filename.endswith(".ldif"):
        file_path = os.path.join(dir_path, filename)
        
        # Read the content of the file
        with open(file_path, 'r') as file:
            content = file.read()
        print(f'open {file_path}')
        # Search for the NAME attribute value in the olcObjectClasses line
        name_match = re.search(r'olcObjectClasses:.*?NAME \'([^\']+)\'', content)
        if name_match:
            print(f'new match{name_match}')
            new_name = name_match.group(1)
            
            # Replace the cn value after 'dn:' and 'cn:'
            content = re.sub(r'(dn: cn=)\{.*\}.*?(\n)', r'\1{}\2'.format(new_name+",cn=schema,cn=config"), content)
            
            content = re.sub(r'(cn: )\{.*\}.*?(\n)', r'\1{}\2'.format(new_name), content)
            print('content',content)
            # Write the modified content back to the file
            new_file_path = os.path.join(new_dir_path, filename)
            with open(new_file_path, 'w') as file:
                file.write(content)

# Output a message when done
print("Files have been processed.")

将修改后的cn=schema替换原来的

(base) win10@win10:~$ sudo rm -r /home/win10/ldap/.data/var/lib/ldap/cn\=config/cn=schema
(base) win10@win10:~$ sudo cp -r /home/win10/ldap/cn=schema /home/win10/ldap/.data/var/lib/ldap/cn=config/

进入cn=config/cn=schema路径导入

root@vivesdata:/var/lib/ldap/cn=config/cn=schema# for i in $(ls);do slapadd -l "$i" -n 0;done;

生成mysql数据对应的ldif文件并导入数据

生成数据对应的ldif

处理py文件代码如下

import pandas as pd
import sys
# 假设你的txt文件是以制表符分隔的
from icecream import install,ic
install()
data_path = sys.argv[1]
n = sys.argv[2]
ic(data_path)
df = pd.read_csv(data_path, sep='\t')
tabel_name = data_path.split('/')[-1].split('.')[0]
col = df.columns
# 假设你有列名与LDAP属性的映射
columns_mapping = {i:i for i in col}
with open(f'/home/win/16t1/ldap/data_ldif/aaa.ldif', 'w') as f:
    f.write(f"dn: ou=lingtandao,dc=ldap,dc=vives,dc=be\n")
    f.write(f"objectClass: organizationalUnit\n")
    f.write(f"ou: lingtandao\n")
    f.write(f"\n")

# 转换为LDIF格式
with open(f'/home/win/16t1/ldap/data_ldif/{tabel_name}.ldif', 'w') as f:
    # if n==0:
    #     f.write(f"dn: ou=lingtandao,dc=ldap,dc=vives,dc=be\n")
    #     f.write(f"objectClass: organizationalUnit\n")
    #     f.write(f"ou: lingtandao\n")
    #     f.write(f"\n")


    f.write(f"dn: ou={tabel_name},ou=lingtandao,dc=ldap,dc=vives,dc=be\n")
    f.write(f"objectClass: organizationalUnit\n")
    f.write(f"ou: {tabel_name}\n")
    f.write(f"\n")

    for index, row in df.iterrows():
        f.write(f"dn: {col[0]}={row[col[0]]},ou={tabel_name},ou=lingtandao,dc=ldap,dc=vives,dc=be\n")
        f.write(f"objectClass: {tabel_name}\n")
        #f.write(f"cn: {row[col[0]]}\n")
        for txt_col, ldap_attr in columns_mapping.items():
            f.write(f"{ldap_attr}: {row[txt_col]}\n")
        f.write("\n")

批量运行的sh代码如下

#!/bin/bash

# 定义包含txt文件的目录路径
DIRECTORY="/home/win/16t1/ldap/new_txt"
count=0
# 遍历指定目录下的所有txt文件
for FILE in "$DIRECTORY"/*.txt
do
    echo "Processing $FILE file..."
    # 调用Python脚本，并将当前txt文件路径作为参数传递
    python /home/win/16t1/ldap/data2ldif.py "$FILE" "$count"
    ((count++))
done

批量导入ldif

将ldap复制到对应路径下

(base) win10@win10:~/ldap$ sudo cp -r /home/win10/ldap/ldif /home/win10/ldap/.data/var/lib/ldap/

单个导入ldif

ldapadd -x -D "cn=admin,dc=ldap,dc=vives,dc=be" -W -f data.ldif

批量导入ldif

每个表需要输密码（用这个似乎不容易出错）

root@vivesdata:/var/lib/ldap# for f in ldif/*.ldif; do ldapadd -x -D "cn=admin,dc=ldap,dc=vives,dc=be" -W -f "$f"; done

不需要输密码

root@vivesdata:/var/lib/ldap# for f in ldif/*.ldif; do ldapadd -x -D "cn=admin,dc=ldap,dc=vives,dc=be" -w "ldap" -f "$f"; done