Connect Your Own MySQL Server

InfoLobby gives you a managed database by default and you do not have to do anything to use it. If you would rather keep your data on a MySQL server you run, this page covers everything your server administrator needs to set up.

Plan on about 15 minutes: create a database, create a user, open the port.

Server requirements

Settings worth checking in your MySQL configuration:

• bind-address must not be 127.0.0.1. Use 0.0.0.0, or the address of the network interface we will reach.
• max_connections: leave real headroom. Page views, scheduled automations and bulk operations each use connections. 50 or more is a reasonable floor for a small team, higher if your automations run over large batches.
• max_allowed_packet: 16M or larger.
• sql_mode: leave it at the stock value. Unusual combinations can break queries.
• wait_timeout: the default of 8 hours is fine, and so is anything from 10 minutes up. Idle connections are reopened automatically.

Time zones do not matter. Dates and times are always stored in UTC.

Open your firewall

Open your MySQL port (3306 by default, though any port works) to our servers only, not to the whole internet.

Our current IP addresses are listed on the connection screen in InfoLobby, under Databases, then Add Server. Allow every address shown there.

# ufw
sudo ufw allow from OUR.IP.ADDRESS to any port 3306 proto tcp

# iptables
sudo iptables -A INPUT -p tcp -s OUR.IP.ADDRESS --dport 3306 -j ACCEPT

Our IP addresses can change

We add and replace servers over time, so the addresses are not permanent. The list on the connection screen is always the current one.

To keep your firewall in sync:

1. Allow every address shown on the connection screen, not just one of them.
2. Before we add or change an address, we email the owner of every account with a connected MySQL server. Make sure that mail reaches whoever manages your firewall, and treat it as a change request.
3. Add the new address alongside the ones you already allow. Both work during the changeover. Remove the retired address after the cutover date.
4. If a connection that used to work starts failing, compare the connection screen against your firewall rules before anything else.

If firewall changes go through a change-control process at your end, tell us at support and we will give you as much notice as we can.

Encryption in transit

InfoLobby connects over a standard MySQL connection without client certificates. Two settings will block it, so leave them off:

• REQUIRE SSL or REQUIRE X509 on the MySQL user you create for InfoLobby
• require_secure_transport=ON on the server

If encrypted transport is a hard requirement for you, terminate TLS in front of MySQL (a TLS proxy, or a site-to-site tunnel between your network and our addresses) and point InfoLobby at that endpoint instead.

Create a database

Create a new, empty database for InfoLobby. Do not reuse a database that already holds another application's data, because InfoLobby creates, changes and removes tables inside it.

CREATE DATABASE infolobby CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

You can host several InfoLobby databases on one server. After the server is connected you choose which database to use.

Create a user

Create one dedicated user for InfoLobby, with a long random password.

CREATE USER 'infolobby'@'%' IDENTIFIED BY 'your-long-random-password';

GRANT SELECT, INSERT, UPDATE, DELETE,
      CREATE, ALTER, DROP, INDEX, REFERENCES
  ON `infolobby`.* TO 'infolobby'@'%';

FLUSH PRIVILEGES;

Better still, restrict the user to our IP address as well as the database:

CREATE USER 'infolobby'@'OUR.IP.ADDRESS' IDENTIFIED BY 'your-long-random-password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX, REFERENCES
  ON `infolobby`.* TO 'infolobby'@'OUR.IP.ADDRESS';
FLUSH PRIVILEGES;

What each privilege is for:

• SELECT, INSERT, UPDATE, DELETE: reading and writing your records.
• CREATE, ALTER, DROP: every InfoLobby table is a real MySQL table and every field is a real column, so adding or deleting one changes the schema.
• INDEX: indexes and unique field constraints.
• REFERENCES: relationship and sub-table fields use foreign keys.

No server-wide privileges are needed. Do not grant SUPER, FILE, PROCESS, RELOAD or GRANT OPTION. The global SHOW DATABASES privilege is not needed either, because the user sees the databases it has been granted.

If the connection is refused with an authentication error but the password is definitely right, your server may be using an authentication plugin the connection cannot negotiate. Switching the user fixes it:

ALTER USER 'infolobby'@'%' IDENTIFIED WITH mysql_native_password BY 'your-long-random-password';

What InfoLobby puts in the database

• One MySQL table per InfoLobby table, and one column per field.
• Foreign key columns for relationship and sub-table fields.
• A small set of supporting tables, prefixed z_, holding record history, comments, and deleted records that are waiting to be removed for good.

Leave the z_ tables alone. You can read your record tables directly with SQL, and back them up like any other database. Writing to them directly is possible, but it skips history, validation and automations, so those changes will not appear in activity or trigger any flows.

File attachments are never stored in MySQL. They go to object storage.

Connect it

1. In InfoLobby, go to Databases and choose Add Server.
2. Name the connection, then enter the server address, port, username and password.
3. Click Test. This makes a real connection, so it tells you straight away whether your firewall and credentials are right.
4. Click Save, then choose the database to use.

If the test fails

Checklist

• [ ] MySQL 8.0 or newer, InnoDB
• [ ] Empty utf8mb4 database created
• [ ] bind-address accepts remote connections
• [ ] Firewall open to our IP addresses on your MySQL port
• [ ] Someone owns keeping those rules current when our addresses change
• [ ] No REQUIRE SSL or REQUIRE X509 on the user, require_secure_transport off
• [ ] Dedicated user with the nine privileges above, limited to that one database
• [ ] max_connections headroom, max_allowed_packet of 16M or more
• [ ] Your own backups of the database