Managed Mysql specifics
Specific options when creating or updating a MySQL service
You can find all the specific options for MySQL by using the CLI help:
exo dbaas create --help-mysql
--mysql-admin-password custom password for admin user
--mysql-admin-username custom username for admin user
--mysql-backup-schedule automated backup schedule (format: HH:MM)
--mysql-fork-from name of a Database Service to fork from
--mysql-ip-filter allow incoming connections from CIDR address block
--mysql-recovery-backup-time the timestamp of the backup to restore when forking from a Database Service
--mysql-settings MySQL configuration settings (JSON format)
--mysql-version MySQL major version
Restricting connections from the internet
By default, Exoscale DBaaS are not accessible from the whole internet. Data does not transmit in clear over the network as it is SSL encrypted by default and authenticated.
To allow incoming connections to your database service, you can add a filter allowing:
- just one IP address,
- a network range,
- or a combination of IP address and network range
To do this, update your service or create it with the IP filter, which is a comma separated list of CIDRs:
exo dbaas update -z de-muc-1 test-mysql --mysql-ip-filter=a.b.c.d/32,w,x,y,z/24
Managing JSON configuration settings
You can list all settings available for the database service by using the command:
exo dbaas type show mysql --settings mysql
┼──────────────────────────────────┼───────────────┼──────────────────────────────────────────────────────┼
│ KEY │ TYPE │ DESCRIPTION │
┼──────────────────────────────────┼───────────────┼──────────────────────────────────────────────────────┼
│ sql_mode │ string │ Global SQL mode. Set to empty to use MySQL server │
│ │ │ defaults. When creating a new service and not │
│ │ │ setting this field Aiven default SQL mode (strict, │
│ │ │ SQL standard compliant) will be assigned. │
│ │ │ * Example: ANSI,TRADITIONAL │
│ interactive_timeout │ integer │ The number of seconds the server waits for │
│ │ │ activity on an interactive connection before │
│ │ │ closing it. │
│ │ │ * Minimum: 30 / Maximum: 604800 │
│ │ │ * Example: 3600 │
│ max_allowed_packet │ integer │ Size of the largest message in bytes that can be │
│ │ │ received by the server. Default is 67108864 (64M) │
│ │ │ * Minimum: 102400 / Maximum: 1.073741824e+09 │
│ │ │ * Example: 6.7108864e+07 │
│ group_concat_max_len │ integer │ The maximum permitted result length in bytes for │
│ │ │ the GROUP_CONCAT() function. │
│ │ │ * Minimum: 4 / Maximum: 1.8446744073709552e+19 │
│ │ │ * Example: 1024 │
│ innodb_print_all_deadlocks │ boolean │ When enabled, information about all deadlocks in │
│ │ │ InnoDB user transactions is recorded in the error │
│ │ │ log. Disabled by default. │
│ │ │ * Example: true │
│ innodb_online_alter_log_max_size │ integer │ The upper limit in bytes on the size of the │
│ │ │ temporary log files used during online DDL │
│ │ │ operations for InnoDB tables. │
│ │ │ * Minimum: 65536 / Maximum: 1.099511627776e+12 │
│ │ │ * Example: 1.34217728e+08 │
│ max_heap_table_size │ integer │ Limits the size of internal in-memory tables. Also │
│ │ │ set tmp_table_size. Default is 16777216 (16M) │
│ │ │ * Minimum: 1.048576e+06 / Maximum: 1.073741824e+09 │
│ │ │ * Example: 1.6777216e+07 │
│ default_time_zone │ string │ Default server time zone as an offset from UTC │
│ │ │ (from -12:00 to +12:00), a time zone name, or │
│ │ │ 'SYSTEM' to use the MySQL server default. │
│ │ │ * Example: +03:00 │
│ innodb_ft_min_token_size │ integer │ Minimum length of words that are stored in an │
│ │ │ InnoDB FULLTEXT index. │
│ │ │ * Minimum: 0 / Maximum: 16 │
│ │ │ * Example: 3 │
│ net_read_timeout │ integer │ The number of seconds to wait for more data from a │
│ │ │ connection before aborting the read. │
│ │ │ * Minimum: 1 / Maximum: 3600 │
│ │ │ * Example: 30 │
│ innodb_rollback_on_timeout │ boolean │ When enabled a transaction timeout causes InnoDB │
│ │ │ to abort and roll back the entire transaction. │
│ │ │ * Example: true │
│ sort_buffer_size │ integer │ Sort buffer size in bytes for ORDER BY │
│ │ │ optimization. Default is 262144 (256K) │
│ │ │ * Minimum: 32768 / Maximum: 1.073741824e+09 │
│ │ │ * Example: 262144 │
│ tmp_table_size │ integer │ Limits the size of internal in-memory tables. Also │
│ │ │ set max_heap_table_size. Default is 16777216 (16M) │
│ │ │ * Minimum: 1.048576e+06 / Maximum: 1.073741824e+09 │
│ │ │ * Example: 1.6777216e+07 │
│ wait_timeout │ integer │ The number of seconds the server waits for │
│ │ │ activity on a noninteractive connection before │
│ │ │ closing it. │
│ │ │ * Minimum: 1 / Maximum: 2.147483e+06 │
│ │ │ * Example: 28800 │
│ innodb_log_buffer_size │ integer │ The size in bytes of the buffer that InnoDB uses │
│ │ │ to write to the log files on disk. │
│ │ │ * Minimum: 1.048576e+06 / Maximum: 4.294967295e+09 │
│ │ │ * Example: 1.6777216e+07 │
│ net_write_timeout │ integer │ The number of seconds to wait for a block to be │
│ │ │ written to a connection before aborting the write. │
│ │ │ * Minimum: 1 / Maximum: 3600 │
│ │ │ * Example: 30 │
│ internal_tmp_mem_storage_engine │ string │ The storage engine for in-memory internal │
│ │ │ temporary tables. │
│ │ │ * Supported values: │
│ │ │ - TempTable │
│ │ │ - MEMORY │
│ │ │ * Example: TempTable │
│ information_schema_stats_expiry │ integer │ The time, in seconds, before cached statistics │
│ │ │ expire │
│ │ │ * Minimum: 900 / Maximum: 3.1536e+07 │
│ │ │ * Example: 86400 │
│ innodb_ft_server_stopword_table │ [null string] │ This option is used to specify your own InnoDB │
│ │ │ FULLTEXT index stopword list for all InnoDB │
│ │ │ tables. │
│ │ │ * Example: db_name/table_name │
│ sql_require_primary_key │ boolean │ Require primary key to be defined for new tables │
│ │ │ or old tables modified with ALTER TABLE and fail │
│ │ │ if missing. It is recommended to always have │
│ │ │ primary keys because various functionality may │
│ │ │ break if any large table is missing them. │
│ │ │ * Example: true │
│ long_query_time │ number │ The slow_query_logs work as SQL statements that │
│ │ │ take more than long_query_time seconds to execute. │
│ │ │ Default is 10s │
│ │ │ * Minimum: 0 / Maximum: 3600 │
│ │ │ * Example: 10 │
│ slow_query_log │ boolean │ Slow query log enables capturing of slow queries. │
│ │ │ Setting slow_query_log to false also truncates the │
│ │ │ mysql.slow_log table. Default is off │
│ │ │ * Example: true │
│ connect_timeout │ integer │ The number of seconds that the mysqld server waits │
│ │ │ for a connect packet before responding with Bad │
│ │ │ handshake │
│ │ │ * Minimum: 2 / Maximum: 3600 │
│ │ │ * Example: 10 │
│ innodb_lock_wait_timeout │ integer │ The length of time in seconds an InnoDB │
│ │ │ transaction waits for a row lock before giving up. │
│ │ │ * Minimum: 1 / Maximum: 3600 │
│ │ │ * Example: 50 │
┼──────────────────────────────────┼───────────────┼──────────────────────────────────────────────────────┼
You can also update the settings of your database service:
exo dbaas update --zone de-fra-1 target-mysql-service-name --mysql-settings '{"max_allowed_packet":1073741824}'
Note
The parameter of --mysql-settings has to be in JSON format.
Refer to the equivalent section for Managed PostgreSQL
Migrating to managed MySQL
Migrate your MySQL server database to our managed MySQL service. Two methods are supported: the replication method and the dump method.
Replication
The replication method is preferred for production databases because it allows for the migration of data with minimal downtime.
First, the process will do a mysqldump to seed the schema and bulk-copy the data. If the preconditions are met for ongoing replication, then it will configure MySQL as a replica of the external database.
Prerequisites
- The source server needs to be publicly available. You should be able to connect to your target database from the source using MySQL commands.
Note
You may rely on Exoscale IP ranges for your firewall restrictions.
- A MySQL user on the source server with sufficient privileges to perform replication.
CREATE USER 'migration_user'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'migration_user'@'%' WITH GRANT OPTION;
Note
Save username and password of your replication user for later.
- GTID is enabled on the source database.
The global transaction identifier is a unique identifier created and associated with each transaction committed on the server of origin. It allows the continuously generated data to migrate on the sources that are not included in the initial dump.
To review the current GTID setting, run the following command on the source cluster:
show global variables like 'gtid_mode';
Refer to Enabling GTID Transactions Online on the MySQL documentation.
- The source database version should be between 5.7 and 8.0
- All databases use the InnoDB engine
- server_id on the source and the target must not overlap. If they do, update your MySQL configuration file and set server-id to a different number. Then restart your service.
Dump
The source database should not be used while migrating using the dump method, otherwise data will be inconsistent.
Prerequisites
-
The source server needs to be publicly available.
-
A MySQL user on the source server with sufficient privileges to perform migration.
Performing migration
The tool used for migration depends on whether the migration happens during service creation or after.
Migration during MySQL service creation
You can either use the Portal or the CLI for migration.
First, you need to specify the name of your managed MySQL and add the appropriate IP Filter:
Enable migration by checking the checkbox under the Migrate from existing DB header. Provide the required parameters:
Migration after MySQL service creation
If you want to migrate your MySQL database after a managed MySQL service creation, use the Exoscale CLI
Note
Use CLI version 1.53.0 or higher.
exo dbaas update --mysql-migration-dbname to_migrate --mysql-migration-host 89.145.160.149 --mysql-migration-method replication --mysql-migration-password password --mysql-migration-username migration_user --mysql-migration-port 3306 my-managed-mysql
To check status of our migration we can run command:
exo dbaas migration status my-managed-mysql
And if we want to stop the running migration:
exo dbaas migration stop my-managed-mysql
MYSQL Read Replicas
MYSQL read replicas provide improved performance by distributing read queries, thus reducing the load on the primary database. They also enhance high availability, scalability, and disaster recovery. Additionally, replicas can be placed in different geographical zones to further improve availability and reduce latency.
Creating a Read Replica
To create a read replica for an existing MYSQL database in another zone, you can use the following command:
exo api --zone at-vie-1 create-dbaas-service-mysql rocket <<EOF
{
"name": "rocket",
"plan": "startup-8",
"integrations": [{"type": "read_replica", "source-service": "launch-site"}]
}
EOF
This will create an instance called rocket in Zone AT-VIE-1 for an existing database called launch-site.
Note
Do not forget to configure the IP Filter to access the new database.
Verifying the Replica
SHOW SLAVE STATUS is the command in MySQL that provides information about the replication status of a replica (slave) server.
It includes details about the slave I/O and SQL threads, the replication position, and any errors.
Run the following SQL query on the replica server:
SHOW SLAVE STATUS\G
This command outputs detailed information such as Slave_IO_State, Master_Host, Master_User, Master_Log_File, and Seconds_Behind_Master.
Promoting the Replica to Master
Step 1: Get details of the replica service to find the integration ID
exo api get-dbaas-service-mysql -z at-vie-1 rocket
Step 2: Check Integration Status
exo api get-dbaas-integration <integration_id>
Step 3: Promote Replica to a master Database (Remove Replication)
exo api delete-dbaas-integration <integration_id>
Note
- Customers can create a read replica on a startup plan, regardless of the main cluster’s plan.
- The read replica node is vertically scalable.
- It can be horizontally scaled by promoting it to the primary, becoming a standalone service detached from the main cluster.