Setting up MySQL for TogetherDB

All versions of MySQL (5.6, 5.7 and 8.0) are supported. You will need access details to your database: ip, host, port, username and password. You can use your default root user details or create a separate user, see below how.

You need to allow access from the database's closest TogetherDB worker server. Accessing databases on your local computer is not supported yet but will come soon.

After the setup below go to your dashboard and at the bottom choose new connection. This will open a dialog that guides you through the settings.

Recommended setup

Database and firewall configuration

All database operations are carried out by the geographically closest of our worker servers, from a fixed IP address:

worker server	ipaddress
Singapore	13.251.134.44
Germany	3.124.227.165
California	54.193.163.108

This means your database must be open to connections from the assigned server.

For self-hosting

If you self-host your database, make sure your database is open to accept connections from outside. You need to edit the file /etc/mysql/my.cnf and look for the line bind-address = 127.0.0.1 and put a hash sign "#" at the start of the line, then restart MySQL with sudo service mysql restart. If you can't find the configuration file, search for it with grep -r --include \*.cnf "bind-address" /etc/mysql.

Adjust the firewall

For Amazon AWS and Google Cloud configure in the interface which IP addresses are allowed to connect.

If you have a firewall, you might need to adjust that as well.

Create the database user

Create a new user in your database to control which databases and which permissions this connection has access to. Also if you one day want to leave TogetherDB you only need to delete this user from your database to retract all access.

This user should be allowed to connect from the designated TogetherDB worker server only:

CREATE USER 'togetherdb'@'3.124.227.165' IDENTIFIED BY 'secret';

Note that this user has no permissions by default and can only connect from the selected worker server. So even if that password would leak noone would be able to access the database.

Grant access rights

To view and edit data in all your databases (recommended):

GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'togetherdb'@'3.124.227.165';

To only grant access to specific databases:

-- to access database/table infos (recommended)
GRANT SELECT ON mysql.* TO 'togetherdb'@'3.124.227.165';

-- grant access to your databases
GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase1.* TO 'togetherdb'@'3.124.227.165';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase2.* TO 'togetherdb'@'3.124.227.165';
...

To use TogetherDB in read-only mode:

GRANT SELECT ON *.* TO 'togetherdb'@'3.124.227.165';

Note that these commands do not include the ALTER permission and thus don't allow changing the structure of tables or columns.

Now you are ready to setup your connection in your dashboard.

Self hosting

We are working on self hosting the worker server on your own machines - coming soon!

This will allow you to have full control of any contact points to your database and also to manage the databases on your local machine.

Load implications

We are very careful to not impose unnecessary load onto a connected database. TogetherDB is meant to work fine even when connected to a production database under high load with hundreds of millions of rows. As a general rule we do not issue any queries without previous interface interaction. Also we use connection pools so there can never be more than 10 connections to your database from TogetherDB.

Issued SQL queries

To give you an idea of the load imposed on your database when using TogetherDB we collected the executed queries in a real world example (as of 12th January 2021).

When navigating to a database the first time or when hard-reloading a table view page the database structure gets fetched (4 queries to the information_schema).

-- lists accessible database names for this connection
SHOW DATABASES;

-- table names
SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='<database-name>' ORDER BY TABLE_NAME ASC;

-- references
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE NOT REFERENCED_TABLE_SCHEMA IS NULL;

-- columns
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT, EXTRA, PRIVILEGES, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.columns
WHERE TABLE_SCHEMA!='information_schema' AND TABLE_SCHEMA!='performance_schema' AND TABLE_SCHEMA!='mysql'
ORDER BY ORDINAL_POSITION ASC

When viewing a table we get the actual rows, the referenced rows as well as the cached approximate row count from information_schema. The exact row count is determined only when explicitly requested in the interface or when the approximation results in less than 10 000 rows.

-- get actual rows and its references
SELECT * FROM table_name LIMIT 50;
SELECT * FROM referenced_table1_name WHERE `id` IN (<referenced ids>);

-- get approximate row counts from cached statistics. we avoid exact counting here for performance reasons
SELECT TABLE_SCHEMA, TABLE_NAME,
    CASE
        WHEN avg_row_length=0 THEN 0
        ELSE ROUND(data_length / avg_row_length)
    END AS approx_count_1,
    table_rows AS approx_count_2
FROM information_schema.TABLES
WHERE TABLE_SCHEMA!='sys' AND TABLE_SCHEMA!='mysql' AND TABLE_SCHEMA!='information_schema' AND TABLE_SCHEMA!='performance_schema';

When scrolling down in the table to load more rows:

SELECT * FROM table_name LIMIT 50 OFFSET 100
SELECT * FROM referenced_table1_name WHERE `id` IN (<newly referenced ids>);

When switching to a different table the system gets the rows and approximate row count again but uses the initially fetched structure data.

When updating a value:

-- updates by primary key column which are always indexed
UPDATE table_name SET column_name=<value> WHERE `id`=row_id LIMIT 1;

-- get row values again
SELECT * FROM table_name WHERE `id`=row_id LIMIT 1;
SELECT * FROM referenced_table1_name WHERE `id` IN (<newly referenced ids>);

Leaving TogetherDB

To "get out" simply delete your togetherdb user:

DROP USER 'togetherdb'@'3.124.227.165';

If you don't remember the exact user specification use this to list all your users:

SELECT Host, User FROM mysql.user;

TogetherDB doesn't create any additional users, databases or tables in your system. All meta data, like your column settings or team permissions, is stored on the assigned worker server. This meta data can contain some of your table or column names and will get erased when you delete your account (or send us an email to make us do it manually).