How can I set mysql character_set_database as utf8mb4 without changing the config file?(since I have no access to the files)

Question

I am using AWS Lightsail MySQL and I'm trying to set the encoding as utf8mb4. I was able to set some variables using the command

SET NAMES utf8mb4;
ALTER DATABASE etl_db CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

The result is

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | latin1             |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | latin1             |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | latin1_swedish_ci  |
| collation_server         | latin1_swedish_ci  |
+--------------------------+--------------------+

But I cannot find a way to change character_set_database or the rest. The solutions online are all about changing the configure file. However, since I am using the AWS Lightsail MySQL service, I have no access to the files. Then how can I change the encoding of the rest variables?

btw, i wonder why can i change something within mysql while i can only change other things in my.cnf? Can I set buffer_size within mysql?

score 0 · Accepted Answer · answered Apr 24 '19 at 02:26

0

Lightsail Database parameters are configurable using aws-cli.

https://lightsail.aws.amazon.com/ls/docs/en/articles/amazon-lightsail-updating-database-parameters

You can change some values temporarily because the value of those variables are connection-specific SET NAMES only changes your current connection, not the default for all connections to the server itself.

answered Apr 24 '19 at 02:26

Michael - sqlbot

169,571
25
353
427

thanks! but I am not sure if the document is up-to-date, it says using ```aws lightsail get-relational-database-parameters --relational-database-name DatabaseName``` but there isn't ```get-relational-database-parameters``` options for ```aws lightsail```. I wonder can you take a look at it? – wwwwan Apr 24 '19 at 02:32

score 0 · Answer 2 · answered Apr 29 '19 at 21:43

ALTER DATABASE etl_db CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

only changes the defaults for that database. That default is used only(?) when you do CREATE TABLE without specifying a charset or collation.

The simple workaround is to always specify charset and/or collation when creating tables. This is a good practice anyway.

How can I set mysql character_set_database as utf8mb4 without changing the config file?(since I have no access to the files)

2 Answers2