> For the complete documentation index, see [llms.txt](https://docs.keeper.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.keeper.io/keeper-connection-manager/jp/exporting-connections.md).

# 接続のエクスポート

## 概要

ここでは、同梱のPythonスクリプトを使って、接続データと接続パラメータをJSONファイルにエクスポートする方法を取り扱います。生成したファイルは、別のKeeperコネクションマネージャーインスタンスへの接続のインポート ([こちらのページ](/keeper-connection-manager/jp/using-keeper-connection-manager/creating-connections/batch-import-and-api.md#importing-connections-with-csv-json-or-yaml)) や、Keeperコネクションマネージャー (クラウド) 版への移行に使えます。

以下の例は、MySQLデータベースを使う標準的な[Dockerの自動インストール](/keeper-connection-manager/jp/installation/auto-docker-install.md)方式のKeeperコネクションマネージャーを前提としています。

### 手順1: MySQLポートをローカルマシンに開放する

PythonスクリプトからDockerコンテナ内のデータベースへクエリできるように、 `/etc/kcm-setup/docker-compose.yml` ファイルを編集し、以下のように「db」コンテナへ「ports」セクションを追加します。

```yaml
db:
        image: keeper/guacamole-db-mysql :2
        restart: unless-stopped 
        environment:
            ACCEPT EULA: "Y"
            GUACAMOLE_DATABASE: "guacamole_db"
            GUACAMOLE_USERNAME: "guacamole_user"
            GUACAMOLE_PASSWORD: "XXXXXXXXXXXXXXXXXXXXXXXXX"
            GUACAMOLE_ADMIN_PASSWORD: "XXXXXXXXXXXXXXXXXXXXXXXXX"
            MYSQL_ROOT_PASSWORD: "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
        ports:
            - "3306:3306"            
```

Docker Composeの更新を適用するには、以下の手順に従います。

1. Dockerの自動インストールを使用している場合は、 `kcm-setup.run` スクリプトがある場所へ移動して、以下を実行します。

   ```bash
   sudo ./kcm-setup.run apply
   ```
2. Docker Composeインストールの場合は、 `/etc/kcm-setup` フォルダへ移動して、以下を実行します。

   ```bash
   docker compose up -d
   ```

### 手順2: Pythonモジュールをインストールする

インスタンスにすでにPython3がある想定で、必要なPythonモジュールをインストールします。

{% tabs %}
{% tab title="MYSQL" %}

```bash
pip3 install mysql-connector
pip3 install pyYAML
```

{% endtab %}

{% tab title="POSTGRES" %}

```bash
pip3 install psycopg2-binary
pip3 install pyYAML
```

{% endtab %}
{% endtabs %}

システムに `python3-pip` をインストールするには、お使いのオペレーティングシステムに応じたコマンドを使用します。

**CentOS/RHEL 8以降:**

```bash
sudo dnf install python3-pip
```

**CentOS/RHEL 7以前:**

```bash
sudo yum install python3-pip
```

**Ubuntu:**

```bash
sudo apt install python3-pip
```

### 手順3: エクスポートスクリプトを作成する

以下のコードを `export.py` というファイルにコピーして貼り付け、Keeperコネクションマネージャーインスタンス上に配置します (たとえば `kcm-setup.run` ファイルと同じ場所)。このPythonスクリプトは以下を行います。

* 標準の `/etc/kcm-setup/` フォルダ内の `docker-compose.yml` ファイルを検出
* MySQL/PostgreSQLの認証情報を取得してデータベースへ接続
* 接続情報をエクスポートし、同じフォルダに `export.json` を作成

環境によっては、ファイルの編集が必要です。

{% tabs %}
{% tab title="MYSQL EXPORT" %}
{% code title="export.py" lineNumbers="true" %}

```python
import mysql.connector
import json
import yaml

# Path to the docker-compose.yml file
docker_compose_file = '/etc/kcm-setup/docker-compose.yml'

# Function to extract database credentials from docker-compose.yml
def get_db_config_from_compose():
    with open(docker_compose_file, 'r') as file:
        # Load the docker-compose YAML file
        compose_data = yaml.safe_load(file)
        
        # Extracting the necessary information from the 'db' service
        db_service = compose_data['services']['db']
        environment = db_service['environment']
        
        db_name = environment.get('GUACAMOLE_DATABASE', 'guacamole_db')
        db_user = environment.get('GUACAMOLE_USERNAME', 'guacamole_user')
        db_password = environment.get('GUACAMOLE_PASSWORD', 'password')  # Default in case it's not present
        
        return {
            'host': 'localhost',  # Assuming the database is local since it's inside Docker
            'user': db_user,
            'password': db_password,
            'database': db_name,
            'port': 3306  # Default MySQL port
        }

def build_connection_group_paths(cursor):
    """
    Build a dictionary mapping group IDs to their full paths by resolving parent-child relationships.
    """
    cursor.execute("SELECT connection_group_id, parent_id, connection_group_name FROM guacamole_connection_group")
    groups = cursor.fetchall()

    group_paths = {}

    def resolve_path(group_id):
        if group_id is None:
            return "ROOT"
        if group_id in group_paths:
            return group_paths[group_id]
        # Find the group details
        group = next(g for g in groups if g['connection_group_id'] == group_id)
        parent_path = resolve_path(group['parent_id'])
        full_path = f"{parent_path}/{group['connection_group_name']}"
        group_paths[group_id] = full_path
        return full_path

    # Resolve paths for all groups
    for group in groups:
        resolve_path(group['connection_group_id'])

    return group_paths

# SQL query to retrieve all connections, users, groups, and attributes
query = """
SELECT
    c.connection_id,
    c.connection_name AS name,
    c.protocol,
    cp.parameter_name,
    cp.parameter_value,
    e.name AS entity_name,
    e.type AS entity_type,
    g.connection_group_id,
    g.parent_id,
    g.connection_group_name AS group_name,
    ca.attribute_name,
    ca.attribute_value
FROM
    guacamole_connection c
LEFT JOIN
    guacamole_connection_parameter cp ON c.connection_id = cp.connection_id
LEFT JOIN
    guacamole_connection_attribute ca ON c.connection_id = ca.connection_id
LEFT JOIN
    guacamole_connection_group g ON c.parent_id = g.connection_group_id
LEFT JOIN
    guacamole_connection_permission p ON c.connection_id = p.connection_id
LEFT JOIN
    guacamole_entity e ON p.entity_id = e.entity_id;
"""

def export_to_json(db_config):
    try:
        # Connect to the database
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor(dictionary=True)  # Dictionary cursor for better handling

        # Build connection group paths
        connection_group_paths = build_connection_group_paths(cursor) 

        # Execute the query
        cursor.execute(query)
        rows = cursor.fetchall()

        # Organize the data into the expected format
        connections = {}
        for row in rows:
            conn_id = row['connection_id']
            if conn_id not in connections:
                # Resolve connection group path
                group_path = connection_group_paths.get(row['connection_group_id'], "ROOT")
                connections[conn_id] = {
                    'name': row['name'],
                    'protocol': row['protocol'],
                    'parameters': {},
                    'users': [],
                    'groups': [],  # User groups go here
                    'group': group_path,  # Connection group path 
                    'attributes': {}
                }
            # Handle parameters
            if row['parameter_name']:
                connections[conn_id]['parameters'][row['parameter_name']] = row['parameter_value']
            # Handle users
            if row['entity_type'] == 'USER' and row['entity_name'] not in connections[conn_id]['users']:
                connections[conn_id]['users'].append(row['entity_name'])
            # Handle user groups
            if row['entity_type'] == 'USER_GROUP' and row['entity_name'] not in connections[conn_id]['groups']:
                connections[conn_id]['groups'].append(row['entity_name'])
            # Handle attributes
            if row['attribute_name']:
                connections[conn_id]['attributes'][row['attribute_name']] = row['attribute_value']

        # Convert to list format
        connection_list = [conn for conn in connections.values()]

        # Output the data to a JSON file
        with open('export.json', 'w') as json_file:
            json.dump(connection_list, json_file, indent=4)

        print("Export successful! Data written to export.json")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        # Close the cursor and the connection
        if cursor:
            cursor.close()
        if conn:
            conn.close()

if __name__ == '__main__':
    # Get the database configuration from docker-compose.yml
    db_config = get_db_config_from_compose()
    export_to_json(db_config)

```

{% endcode %}
{% endtab %}

{% tab title="POSTGRES EXPORT" %}

```python
import psycopg2
import psycopg2.extras
import json
import yaml

# Path to the docker-compose.yml file
docker_compose_file = '/etc/kcm-setup/docker-compose.yml'

# Function to extract database credentials from docker-compose.yml
def get_db_config_from_compose():
    with open(docker_compose_file, 'r') as file:
        compose_data = yaml.safe_load(file)

        db_service = compose_data['services']['db']
        env = db_service['environment']

        return {
            'host': 'localhost',
            'user': env.get('GUACAMOLE_USERNAME', 'guacamole_user'),
            'password': env.get('GUACAMOLE_PASSWORD', 'password'),
            'database': env.get('GUACAMOLE_DATABASE', 'guacamole_db'),
            'port': 5432  # Default PostgreSQL port
        }

def build_connection_group_paths(cursor):
    cursor.execute("SELECT connection_group_id, parent_id, connection_group_name FROM guacamole_connection_group")
    groups = cursor.fetchall()

    group_paths = {}

    def resolve_path(group_id):
        if group_id is None:
            return "ROOT"
        if group_id in group_paths:
            return group_paths[group_id]
        group = next(g for g in groups if g['connection_group_id'] == group_id)
        parent_path = resolve_path(group['parent_id'])
        full_path = f"{parent_path}/{group['connection_group_name']}"
        group_paths[group_id] = full_path
        return full_path

    for group in groups:
        resolve_path(group['connection_group_id'])

    return group_paths

# SQL query remains the same (PostgreSQL-compatible syntax)
query = """
SELECT
    c.connection_id,
    c.connection_name AS name,
    c.protocol,
    cp.parameter_name,
    cp.parameter_value,
    e.name AS entity_name,
    e.type AS entity_type,
    g.connection_group_id,
    g.parent_id,
    g.connection_group_name AS group_name,
    ca.attribute_name,
    ca.attribute_value
FROM
    guacamole_connection c
LEFT JOIN
    guacamole_connection_parameter cp ON c.connection_id = cp.connection_id
LEFT JOIN
    guacamole_connection_attribute ca ON c.connection_id = ca.connection_id
LEFT JOIN
    guacamole_connection_group g ON c.parent_id = g.connection_group_id
LEFT JOIN
    guacamole_connection_permission p ON c.connection_id = p.connection_id
LEFT JOIN
    guacamole_entity e ON p.entity_id = e.entity_id;
"""

def export_to_json(db_config):
    try:
        conn = psycopg2.connect(
            host=db_config['host'],
            user=db_config['user'],
            password=db_config['password'],
            dbname=db_config['database'],
            port=db_config['port']
        )
        cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)

        connection_group_paths = build_connection_group_paths(cursor)

        cursor.execute(query)
        real_dict_rows = cursor.fetchall()
        # Convert RealDictRow objects to native dicts
        rows = [dict(row) for row in real_dict_rows]

        # Organize the data into the expected format
        connections = {}
        for row in rows:
            conn_id = row['connection_id']
            if conn_id not in connections:
                group_path = connection_group_paths.get(row['connection_group_id'], "ROOT")
                connections[conn_id] = {
                    'name': row['name'],
                    'protocol': row['protocol'],
                    'parameters': {},
                    'users': [],
                    'groups': [],
                    'group': group_path,
                    'attributes': {}
                }
            if row['parameter_name']:
                connections[conn_id]['parameters'][row['parameter_name']] = row['parameter_value']
            if row['entity_type'] == 'USER' and row['entity_name'] not in connections[conn_id]['users']:
                connections[conn_id]['users'].append(row['entity_name'])
            if row['entity_type'] == 'USER_GROUP' and row['entity_name'] not in connections[conn_id]['groups']:
                connections[conn_id]['groups'].append(row['entity_name'])
            if row['attribute_name']:
                connections[conn_id]['attributes'][row['attribute_name']] = row['attribute_value']

        with open('export.json', 'w') as json_file:
            json.dump(list(connections.values()), json_file, indent=4)

        print("Export successful! Data written to export.json")

    except psycopg2.Error as err:
        print(f"Database Error: {err}")
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

if __name__ == '__main__':
    db_config = get_db_config_from_compose()
    export_to_json(db_config)

```

{% endtab %}
{% endtabs %}

スクリプトを実行するには、以下を入力します。

```
sudo python3 export.py
```

ローカルフォルダに `export.json` ファイルが生成されます。

{% hint style="warning" %}
この `export.json` ファイルには、接続の作成方法によっては接続シークレットが含まれることがあります。Keeperボルトへ保存するなどして保護してください。
{% endhint %}

### 注記

* 「groups」オブジェクトはユーザーグループを表す
* 「group」オブジェクトは接続グループの場所を表す
* 別のKCMインスタンスへインポートする場合、ターゲット側に接続グループが存在する場合にのみ正常にインポートされる


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.keeper.io/keeper-connection-manager/jp/exporting-connections.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.