Initial importing your SQL dump

When importing SQL dumps in a database-cluster for initial setup of your databases, it's important to know this import can be growing your disk pretty fast. The reason is because of the use of binlogs. Every transaction is stored in the binlog-files. So if there are a lot of transactions, the binlogs will grow. See Choosing the right disk size for more information about this topic.

To prevent filling the disks with binlogs, we need to do the following

multi-node cluster

Create a single-node cluster. After the data is imported, we can scale up to multiple nodes.

The reason is, for replication the binlog-files are essential to ship all data from the main node to the other nodes. And we want to import the data without writing it into the binlog-files. So when we set up a multi-node cluster in the beginning, and we import the data without writing it to the binlog-files, the data is not transferred over to the other nodes. In case of failover, you lose data.

Note

Horizontal scaling is not possible yet via the cloud-portal. If you want to make use of this feature please contact support (support@tilaa.com)

single-node cluster

When creating a single-node cluster (without any replication) we still make use of the binlog-files. In case you want to enable backups, we need those binlog-files.

Therefore, importing data in a single-node cluster has the same issues as with a multi-node cluster. The only difference will be you don't need to scale to make use of extra nodes.

import the data

When importing the data, we want to make sure nothing is written to the binlogs. To do this, we have to set a session parameter sql_log_bin = 0

We can do this in the mysql command:

mysql --init-command="SET SQL_LOG_BIN = 0;" -u <user> -p -h <host> <db> < <import-file>.sql

We also can to this in the SQL file:

# head <sql-dump-file>.sql 
SET SQL_LOG_BIN = 0;
...

This option makes it easier to import the data with other tools (like phpMyAdmin or dbeaver).

Note

If you want to have a multi-node cluster please don't forget to scale your cluster to the desired number of nodes

The example shows a difference of about 2Gb in size which can make the difference if the database fits or not.

With sql_log_bin in the session enabled:

Without sql_log_bin in the session enabled: