Beta Branch add SET_DEFAULT_MYSQLCHARSET variable in 123.09beta01

eva2000 · Jul 16, 2019

add SET_DEFAULT_MYSQLCHARSET variable in 123.09beta01

If you set SET_DEFAULT_MYSQLCHARSET='utf8mb4' in persistent config file /etc/centminmod/custom_config.inc prior to initial Centmin Mod install, it allows changing MariaDB MySQL /etc/my.cnf default characterset & collation from utf8 defaults to utf8mb4 defaults for both MySQL client and server.

Continue reading...

123.09beta01 branch

Branch: https://github.com/centminmod/centminmod/tree/123.09beta01

Commit History: https://github.com/centminmod/centminmod/commits/123.09beta01

EckyBrazzz · Oct 20, 2019

Just reading after an issue on Install - Discussion: how do you initially install & setup your Centmin Mod server? but what is the benefit to change it to utf8mb4 and how to change on an existing install if we did not have it on a fresh installation.

Guess it’s playing a hard game to change it because most is utf8. Please correct me if I'm wrong.

eva2000 · Oct 20, 2019

It was originally intended for when you have web apps with mysql databases created with utf8mb4 character set and collation so that default mysqldump backups end up backing up data as utf8mb4 instead of mysql default uft8 - otherwise data can be corrupted i.e. xenforo emojis need utf8mb4 to work. See Public service announcement: Check your MySQL backup! and my reply at Public service announcement: Check your MySQL backup!

Or if you have mix of database character set and collations other than utf8mb4 and utf8, then you'd need use mysqldump with --default-character-set=utf8mb4 where mysql database is utf8mb4 and the intended characterset is the one your database is i.e. uft8mb4

Setting uft8mb4 as default, would bring interesting and potential issues ? If you have other non-xenforo mysql databases that don't use uft8mb4 but utf8 or non-utf8 too on the same server. You could end up messing up mysqldumps for those non-xenforo mysql databases ?

Setting --default-character-set=utf8mb4 command on mysqldump command line would seem safe in such cases
Click to expand...

Your mysql backup scripts would also need adjusting to correct mysql database characterset if your mysql server i.e. MariaDB server is of one character set default - on Centmin Mod that is utf8 or non-Centmin Mod MariaDB is latin1 characterset and your database is utf8mb4

dbbackup.sh smarter mysql backups

Centmin Mod premium members dbbackup.sh mysql backup script has conditional mysql database characterset detection so it backups up your database according to the characterset of the database detected MariaDB - dbbackup.sh - quick mysql database backups for Centmin Mod stack. One of the features of dbbackup.sh = Smarter per database character set and collation handling for backups to prevent database table corruption due to character set and mixed collations.
eva2000 said: ↑
dbbackup.sh 0.6.4 updated database character set & collation detection

Updated dbbackup.sh 0.6.4 MySQL database backup routine to set smarter --default-character-set default character sets for each database you backup to prevent potential corruption of database table data when the table data collation and character set is not the same as the MySQL database server.

If empty database default character set, fallback to server default character set found in variable character_set_server.

Otherwise, set mysqldump to use character set of tables if utf8mb4 or utf8 detected. So if mixed latin1, utf8 and utf8mb4 table collations, then --default-character-set=utf8mb4 will be used. If mixed latin1, utf8 table collations, --default-character-set=utf8 will be used.

Otherwise, fall back to database's default character set which in most cases for databases created on same server will be same as server default character set. If different, database's default character set takes priority.

Example of backing up 3 databases named, testdb, sbt, sbt_uft8mb4
Code (Text):
mysql -e "SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;"
+--------------------+---------+--------------------+
| database           | charset | collation          |
+--------------------+---------+--------------------+
| information_schema | utf8    | utf8_general_ci    |
| mysql              | utf8    | utf8_general_ci    |
| performance_schema | utf8    | utf8_general_ci    |
| sbt                | utf8    | utf8_general_ci    |
| sbt_uft8mb4        | utf8mb4 | utf8mb4_general_ci |
| testdb             | utf8mb4 | utf8mb4_general_ci |
+--------------------+---------+--------------------+
Code (Text):
/root/tools/dbbackup.sh
backup /etc/my.cnf

[backup data + schema: testdb  ]
    mysqldump --single-transaction --default-character-set=utf8mb4 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob testdb > /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-270818-135734.sql
    [real:0.01s user:0.00s sys:0.00s cpu: 66% maxmem: 3684 KB cswaits: 23]

[backup schema only: testdb  ]
    mysqldump --single-transaction --default-character-set=utf8mb4 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob -d testdb > /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-schema-270818-135734.sql
    [real:0.01s user:0.00s sys:0.00s cpu: 68% maxmem: 3684 KB cswaits: 24]

[compression routine: testdb  ]
    pigz -4R /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-270818-135734.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 50% maxmem: 1004 KB cswaits: 5]
    515 /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-270818-135734.sql.gz
    pigz -4R /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-schema-270818-135734.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 0% maxmem: 1004 KB cswaits: 5]
    522 /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-schema-270818-135734.sql.gz

[backup data + schema: sbt  ]
    mysqldump --single-transaction --default-character-set=utf8 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob sbt > /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-270818-135734.sql
    [real:0.01s user:0.00s sys:0.00s cpu: 58% maxmem: 3724 KB cswaits: 37]

[backup schema only: sbt  ]
    mysqldump --single-transaction --default-character-set=utf8 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob -d sbt > /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-schema-270818-135734.sql
    [real:0.01s user:0.00s sys:0.00s cpu: 64% maxmem: 3716 KB cswaits: 37]

[compression routine: sbt  ]
    pigz -4R /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-270818-135734.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 50% maxmem: 1140 KB cswaits: 5]
    5.2K /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-270818-135734.sql.gz
    pigz -4R /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-schema-270818-135734.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 100% maxmem: 1008 KB cswaits: 6]
    748 /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-schema-270818-135734.sql.gz

[backup data + schema: sbt_uft8mb4  ]
    mysqldump --single-transaction --default-character-set=utf8mb4 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob sbt_uft8mb4 > /home/nginx/domains/testdb.com/backup/mysql/sbt_uft8mb4-backup-270818-135734.sql
    [real:0.01s user:0.00s sys:0.00s cpu: 60% maxmem: 3728 KB cswaits: 36]

[backup schema only: sbt_uft8mb4  ]
    mysqldump --single-transaction --default-character-set=utf8mb4 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob -d sbt_uft8mb4 > /home/nginx/domains/testdb.com/backup/mysql/sbt_uft8mb4-backup-schema-270818-135734.sql
    [real:0.01s user:0.00s sys:0.00s cpu: 57% maxmem: 3720 KB cswaits: 36]

[compression routine: sbt_uft8mb4  ]
    pigz -4R /home/nginx/domains/testdb.com/backup/mysql/sbt_uft8mb4-backup-270818-135734.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 100% maxmem: 1140 KB cswaits: 5]
    5.2K /home/nginx/domains/testdb.com/backup/mysql/sbt_uft8mb4-backup-270818-135734.sql.gz
    pigz -4R /home/nginx/domains/testdb.com/backup/mysql/sbt_uft8mb4-backup-schema-270818-135734.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 100% maxmem: 1004 KB cswaits: 5]
    768 /home/nginx/domains/testdb.com/backup/mysql/sbt_uft8mb4-backup-schema-270818-135734.sql.gz

------------------------------------------------------------------------------
[Databases backed up locally at: /home/nginx/domains/testdb.com/backup/mysql]
-rw-r--r-- 1 root nginx  522 Aug 27 13:57 testdb-backup-schema-270818-135734.sql.gz
-rw-r--r-- 1 root nginx  515 Aug 27 13:57 testdb-backup-270818-135734.sql.gz
-rw-r--r-- 1 root nginx  768 Aug 27 13:57 sbt_uft8mb4-backup-schema-270818-135734.sql.gz
-rw-r--r-- 1 root nginx 5.2K Aug 27 13:57 sbt_uft8mb4-backup-270818-135734.sql.gz
-rw-r--r-- 1 root nginx  748 Aug 27 13:57 sbt-backup-schema-270818-135734.sql.gz
-rw-r--r-- 1 root nginx 5.2K Aug 27 13:57 sbt-backup-270818-135734.sql.gz
-rw-r--r-- 1 root nginx 5.2K Aug 27 13:57 my.cnf-backup-270818-135734
Click to expand...
changing default characterset/collations

If all your mysql databases are utf8 character set based or non-utf8mb4 based, then Centmin Mod's MariaDB default utf8 characterset should suffice. If any of your mysql databases are only utf8mb4, then MariaDB server may need utf8mb4 defaults which can be set via 1st post persistent variable for initial Centmin Mod installs or you can change it manually in /etc/my.cnf on existing installs - which only affect new mysql databases created from the time of the /etc/my.cnf change of default characterset. Existing mysql databases won't change characterset/collations unless you manually convert your databases and database tables' characterset and collation at database, table and column/field levels.

EckyBrazzz · Oct 20, 2019

Thx, for me no need to change it as I only have WP sites and a bunch others with utf8 character. So no performance gain or so.

Log in / Register

Forums

Discover Centmin Mod today

Beta Branch add SET_DEFAULT_MYSQLCHARSET variable in 123.09beta01

eva2000 Administrator Staff Member

EckyBrazzz Active Member

eva2000 Administrator Staff Member

dbbackup.sh smarter mysql backups

changing default characterset/collations

EckyBrazzz Active Member

.

Log in / Register

Useful Searches

Discover Centmin Mod today

Beta Branch add SET_DEFAULT_MYSQLCHARSET variable in 123.09beta01

eva2000 Administrator Staff Member

EckyBrazzz Active Member

eva2000 Administrator Staff Member

dbbackup.sh smarter mysql backups

changing default characterset/collations

EckyBrazzz Active Member

.