# CREATE SERVER

## Syntax

```sql
CREATE [OR REPLACE] SERVER [IF NOT EXISTS] server_name
    FOREIGN DATA WRAPPER wrapper_name
    OPTIONS (option [, option] ...)

option: <= MariaDB 11.6
  { HOST character-literal
  | DATABASE character-literal
  | USER character-literal
  | PASSWORD character-literal
  | SOCKET character-literal
  | OWNER character-literal
  | PORT numeric-literal }

option: >= MariaDB Enterprise Server 11.4 / Community Server 11.7
  { HOST character-literal
  | DATABASE character-literal
  | USER character-literal
  | PASSWORD character-literal
  | SOCKET character-literal
  | OWNER character-literal
  | PORT numeric-literal
  | PORT quoted-numerical-literal
  | identifier character-literal}
```

## Description

{% tabs %}
{% tab title="Current" %}
This statement creates the definition of a server for use with the [Spider](https://mariadb.com/docs/server/server-usage/storage-engines/spider), [Connect](https://mariadb.com/docs/server/server-usage/storage-engines/connect), [FEDERATED](https://mariadb.com/docs/server/server-usage/storage-engines/legacy-storage-engines/federated-storage-engine), or [FederatedX](https://mariadb.com/docs/server/server-usage/storage-engines/federatedx-storage-engine) storage engine. The `CREATE SERVER` statement creates a new row in the [servers](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables/mysql-servers-table) table within the mysql database. This statement requires the [FEDERATED ADMIN](https://mariadb.com/docs/server/reference/account-management-sql-statements/grant#federated-admin) privilege.
{% endtab %}

{% tab title="< 10.5.2" %}
This statement creates the definition of a server for use with the [Spider](https://mariadb.com/docs/server/server-usage/storage-engines/spider), [Connect](https://mariadb.com/docs/server/server-usage/storage-engines/connect), [FEDERATED](https://mariadb.com/docs/server/server-usage/storage-engines/legacy-storage-engines/federated-storage-engine), or [FederatedX](https://mariadb.com/docs/server/server-usage/storage-engines/federatedx-storage-engine) storage engine. The `CREATE SERVER` statement creates a new row in the [servers](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables/mysql-servers-table) table within the mysql database. This statement requires the [SUPER](https://mariadb.com/docs/server/reference/account-management-sql-statements/grant#super) privilege.
{% endtab %}
{% endtabs %}

The server\_name should be a unique reference to the server. Server definitions are global within the scope of the server, it is not possible to qualify the server definition to a specific database. server\_name has a maximum length of 64 characters (names longer than 64 characters are silently truncated), and is case-insensitive. You may specify the name as a quoted string.

The wrapper\_name may be quoted with single quotes. Supported values are:

* `mysql`
* `mariadb`

For each option you must specify either a character literal or numeric literal. Character literals are UTF-8, support a maximum length of 64 characters and default to a blank (empty) string. String literals are silently truncated to 64 characters. Numeric literals must be a number between `0` and `9999`, default value is `0`.

**Note**: The `OWNER` option is currently not applied, and has no effect on the ownership or operation of the server connection that is created.

The `CREATE SERVER` statement creates an entry in the [mysql.servers](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables/mysql-servers-table) table that can later be used with the `CREATE TABLE` statement when creating a [Spider](https://mariadb.com/docs/server/server-usage/storage-engines/spider), [Connect](https://mariadb.com/docs/server/server-usage/storage-engines/connect), [FederatedX](https://mariadb.com/docs/server/server-usage/storage-engines/federatedx-storage-engine) or [FEDERATED](https://mariadb.com/docs/server/server-usage/storage-engines/legacy-storage-engines/federated-storage-engine) table. The options that you specify will be used to populate the columns in the mysql.servers table. The table columns are `Server_name`, `Host`, `Db`, `Username`, `Password`, `Port`, and `Socket`.

Note: When used with the Spider storage engine, connection information provided via `CREATE SERVER` may be overridden by table-level or engine-defined parameters. For information on connection configuration and precedence rules, see the [Spider Storage Engine Core Concepts](https://mariadb.com/docs/server/server-usage/storage-engines/spider/spider-storage-engine-core-concepts) page.

[DROP SERVER](https://mariadb.com/docs/server/reference/sql-statements/data-definition/drop/drop-server) removes a previously created server definition.

`CREATE SERVER` is not written to the [binary log](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log), irrespective of the [binary log format](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/binary-log-formats) being used and therefore will not replicate.

{% tabs %}
{% tab title="Current" %}
[Galera](https://app.gitbook.com/o/diTpXxF5WsbHqTReoBsS/s/3VYeeVGUV4AMqrA3zwy7/) replicates the `CREATE SERVER`, [ALTER SERVER](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-server) and [DROP SERVER](https://mariadb.com/docs/server/reference/sql-statements/data-definition/drop/drop-server) statements.
{% endtab %}

{% tab title="< 10.1.13" %}
[Galera](https://app.gitbook.com/o/diTpXxF5WsbHqTReoBsS/s/3VYeeVGUV4AMqrA3zwy7/) does not replicate the `CREATE SERVER`, [ALTER SERVER](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-server) and [DROP SERVER](https://mariadb.com/docs/server/reference/sql-statements/data-definition/drop/drop-server) statements.
{% endtab %}
{% endtabs %}

For valid identifiers to use as server names, see [Identifier Names](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/identifier-names).

{% tabs %}
{% tab title="Current" %}
The [SHOW CREATE SERVER](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-server) statement can be used to show the `CREATE SERVER` statement that created a given server definition.
{% endtab %}

{% tab title="< Enterprise Server 11.4 / Community Server 11.7" %}
The [SHOW CREATE SERVER](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-server) statement cannot be used to show the `CREATE SERVER` statement that created a given server definition.
{% endtab %}
{% endtabs %}

#### OR REPLACE

If the optional `OR REPLACE` clause is used, it acts as a shortcut for:

```sql
DROP SERVER IF EXISTS name;
CREATE SERVER server_name ...;
```

#### IF NOT EXISTS

If the `IF NOT EXISTS` clause is used, MariaDB will return a warning instead of an error if the server already exists. Cannot be used together with `OR REPLACE`.

## Examples

```sql
CREATE SERVER s
FOREIGN DATA WRAPPER mariadb
OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
```

`OR REPLACE` and `IF NOT EXISTS`:

```sql
CREATE SERVER s 
FOREIGN DATA WRAPPER mariadb 
OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
ERROR 1476 (HY000): The foreign server, s, you are trying to create already exists

CREATE OR REPLACE SERVER s 
FOREIGN DATA WRAPPER mariadb 
OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
Query OK, 0 rows affected (0.00 sec)

CREATE SERVER IF NOT EXISTS s 
FOREIGN DATA WRAPPER mariadb 
OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
Query OK, 0 rows affected, 1 warning (0.00 sec)

SHOW WARNINGS;
+-------+------+----------------------------------------------------------------+
| Level | Code | Message                                                        |
+-------+------+----------------------------------------------------------------+
| Note  | 1476 | The foreign server, s, you are trying to create already exists |
+-------+------+----------------------------------------------------------------+
```

## See Also

* [Identifier Names](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/identifier-names)
* [ALTER SERVER](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-server)
* [DROP SERVER](https://mariadb.com/docs/server/reference/sql-statements/data-definition/drop/drop-server)
* [Spider Storage Engine](https://mariadb.com/docs/server/server-usage/storage-engines/spider)
* [Connect Storage Engine](https://mariadb.com/docs/server/server-usage/storage-engines/connect)
* [mysql.servers table](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables/mysql-servers-table)
* [SHOW CREATE SERVER](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-server)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}