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.
Database and firewall configuration
All database operations are carried out by the geographically closest of our worker servers, from a fixed IP address:
This means your database must be open to connections from the assigned server.
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'@'126.96.36.199' 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'@'188.8.131.52';
To only grant access to specific databases:
-- to access database/table infos (recommended) GRANT SELECT ON mysql.* TO 'togetherdb'@'184.108.40.206'; -- grant access to your databases GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase1.* TO 'togetherdb'@'220.127.116.11'; GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase2.* TO 'togetherdb'@'18.104.22.168'; ...
To use TogetherDB in read-only mode:
GRANT SELECT ON *.* TO 'togetherdb'@'22.214.171.124';
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.
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.
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>);
To "get out" simply delete your togetherdb user:
DROP USER 'togetherdb'@'126.96.36.199';
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).