Sammlung von Newsfeeds

FromDual has the pleasure to announce the release of the new version 1.1.0 of its popular Database Performance Monitor for MariaDB, MySQL and Galera Cluster fpmmm.

The FromDual Performance Monitor for MariaDB and MySQL (fpmmm) enables DBAs and System Administrators to monitor what is going on inside their MariaDB and MySQL databases and on their machines where the databases reside.

More detailed information your can find in the fpmmm Installation Guide.

Download

The new FromDual Performance Monitor for MariaDB and MySQL (fpmmm) can be downloaded from here. How to install and use fpmmm is documented in the fpmmm Installation Guide.

In case you find a bug in the FromDual Performance Monitor for MariaDB and MySQL please report it to the FromDual Bugtracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to us.

Monitoring as a Service (MaaS)

You do not want to set-up your Database monitoring yourself? No problem: Choose our MariaDB and MySQL Monitoring as a Service (Maas) program to safe time and costs!

Installation of Performance Monitor 1.1.0

A complete guide on how to install FromDual Performance Monitor you can find in the fpmmm Installation Guide.

Upgrade from 1.0.x to 1.1.0 shell> cd /opt shell> tar xf /download/fpmmm-1.1.0.tar.gz shell> rm -f fpmmm shell> ln -s fpmmm-1.1.0 fpmmm
Changes in FromDual Performance Monitor for MariaDB and MySQL 1.1.0

This release contains various bug fixes.

You can verify your current FromDual Performance Monitor for MariaDB and MySQL version with the following command:

shell> fpmmm --version
General

• fpmmm is now available for Cent OS with RPM packages and for Ubuntu with DEB packages.
• MariaDB 10.4 seems to work and thus is officially declared as supported.
• TimeZone made configurable.
• Error printed to STDOUT changed to STDERR.
• Return codes made unique.
• De-support PHP versions older than 7.0.
• All old PHP 5.5 stuff removed, we need now at least PHP 7.0.
• Cosmetic fixes and error handling improved.

fpmmm agent

• Error message typo fixed.
• All mpm remainings removed.
• Upload: Error exit handling improved.

fpmmm Templates

• InnoDB Template: Links to mysql-forum replaced by links to fromdual.com.
• Templates: Zabbix 4.0 templates added and tpl directory restructured.

fpmmm Modules

• Backup: Backup hook added to templates as example.
• InnoDB: InnoDB buffer pool flushing data and graph added.
• InnoDB: innodb_metrics replacing mostly SHOW ENGINE INNODB STATUS.
• InnoDB: Started replacing SHOW ENGINE INNODB STATUS by I_S.innodb_metrics with Adaptive Hash Index (AHI).
• InnoDB: innodb_file_format removed.
• InnoDB: InnoDB files items and graph added.
• InnoDB: Negative values of innodb_buffer_pool_pages_misc_b fixed.
• InnoDB: Bug report of Wang Chao about InnoDB Adaptive Hash Index (AHI) size fixed.
• Memcached: Memcached module fixed.
• MySQL: MariaDB thread pool items and graph added.
• MySQL: Slow Queries item fixed and graph added.
• Server: Smartmon monitor added to monitor HDD/SSD.
• Server: Server module made more robust and numactl replaced by cpuinfo.
• Server: Server free function adapted according to Linux free command.
• Server: Function getFsStatsLinux added for global file descriptor limits.
• Aria: Aria cleaned-up, old mariadb_* variables removed, Aria transaction log graph added.
• Aria: Aria pagecache blocks converted to bytes.

fpmmm agent installer

• No changes.

For subscriptions of commercial use of fpmmm please get in contact with us.

Taxonomy upgrade extras: performancemonitormonitoringfpmmmmaasrelease

Hiermit präsentieren wir Ihnen das FromDual Schulungsprogramm 2020 für MariaDB und MySQL. Auch nächstes Jahr führen wir wieder zahlreiche MariaDB und MySQL Schulungen für Anfänger und Fortgeschrittene bei unseren 3 Partnern in Essen, Köln und Berlin durch.

Die Liste mit Schulungsterminen 2020 finden Sie hier.

Welches für Sie das richtige Schulungsmodul ist, erläutern wir unter der Seite Schulungsmodule.

Gleich richtig los geht es anfangs Januar mit MariaDB und MySQL für Einsteiger. Dieser Kurs ist schon fast ausgebucht und findet daher sicher statt. Sichern Sie sich also noch die letzten Plätze!

FromDual Technik-Blog

Im FromDual Blog berichten wir regelmässig darüber, mit welchen Themen wir unser Wissen erweitert haben. Diese Artikel sind frei verfügbar und somit auch Ihnen zugänglich:

• Mit MySQL 8.0 wird utf8mb4 das neue Default Character Set. Was Sie hierzu beachten sollten, haben wir im Artikel: MariaDB and MySQL Character Set Conversion beschrieben.
• Wie Sie den Progress Indicator des Backup und Recovery Managers nutzen, finden Sie im Artikel: FromDual Recovery Manager (rman) with progress indicator.
• Falls Sie sich entscheiden sollten, die Seite zu wechseln, gibt es im Artikel Migration from MySQL 5.7 to MariaDB 10.4 einige nützliche Tipps hierzu.
• Wenn Sie genauer wissen wollen, wo Ihr RAM geblieben ist gibt Ihnen der Artikel: Who else is using my memory - File System Cache analysis möglicherweise neue Denkanstösse.
• Bei der Untersuchung von Problemen nutzen wir immer wieder das General Query Log. Wie man es aber nur für eine bestimmte Verbindung aktiviert, finden Sie hier beschrieben: Enable General Query Log per Connection in MariaDB.
• Auch wir sind (leider) nicht ganz fehlerfrei. Wie wir unser CRM kaputt und anschliessend wieder heile gemacht haben beschreiben wir in: Oops! - That SQL Query was not intended... Flashback.
• Falls Sie sich das Swappen auf Ihrem System nicht erklären können, empfehlen wir Ihnen folgenden Artikel: Do not underestimate performance impacts of swapping on NUMA database systems.

Werkzeuge für den täglichen Datenbankbetrieb

FromDual bietet Ihnen auch eine breite Palette von nützlichen Werkzeuge für den täglichen Betrieb und die Pflege Ihrer MariaDB und MySQL Datenbanken an:

• Das überaus nützliche sys Schema haben wir von MySQL nach MariaDB portiert. Das sys Schema können Sie jetzt von Github beziehen. Wie sie dazu vorgehen müssen beschreiben wir im Artikel MariaDB sys Schema.
• Im Sommer haben wir einen weiteren Release des FromDual Ops Centers für MariaDB und MySQL freigegeben. Point-in-Time Recovery ist jetzt ebenfalls über das Ops Center möglich.
• Ebenfalls im August ist die neuste Version des FromDual Backup und Recovery Managers (brman) für MariaDB und MySQL erschienen.
• Hoffentlich noch dieses Jahr releasen wir die nächste Version des FromDual Performance Monitors für MariaDB und MySQL (fpmmm).
• Sämtliche FromDual Software kann von FromDual Servern unter Download heruntergeladen werden.

Monitoring as a Service

Falls Sie sich keine eigene Monitoring Lösung antun wollen, bieten wir Ihnen auch gerne unsere preiswerte Monitoring-as-a-Service (MaaS) Lösung an. Nehmen Sie hierzu einfach mit uns Kontakt auf. Wir unterbreiten Ihnen gerne ein Angebot.

Ich denke, hiermit haben Sie genügend zu tun, damit Ihnen über die Feiertag nicht langweilig wird... :-)

Schöne Weihnachten und einen gute Rutsch ins neue Jahr wünscht Ihnen
Ihr FromDual Schulungsteam

Taxonomy upgrade extras: 2020schulungtrainingmysql-trainingmariadb traininggalera cluster trainingmariadb schulunggalera cluster schulung

Hiermit präsentieren wir Ihnen das FromDual Schulungsprogramm 2020 für MariaDB und MySQL. Auch nächstes Jahr führen wir wieder zahlreiche MariaDB und MySQL Schulungen für Anfänger und Fortgeschrittene bei unseren 3 Partnern in Essen, Köln und Berlin durch.

Die Liste mit Schulungsterminen 2020 finden Sie hier.

Welches für Sie das richtige Schulungsmodul ist, erläutern wir unter der Seite Schulungsmodule.

Gleich richtig los geht es anfangs Januar mit MariaDB und MySQL für Einsteiger. Dieser Kurs ist schon fast ausgebucht und findet daher sicher statt. Sichern Sie sich also noch die letzten Plätze!

FromDual Technik-Blog

Im FromDual Blog berichten wir regelmässig darüber, mit welchen Themen wir unser Wissen erweitert haben. Diese Artikel sind frei verfügbar und somit auch Ihnen zugänglich:

• Mit MySQL 8.0 wird utf8mb4 das neue Default Character Set. Was Sie hierzu beachten sollten, haben wir im Artikel: MariaDB and MySQL Character Set Conversion beschrieben.
• Wie Sie den Progress Indicator des Backup und Recovery Managers nutzen, finden Sie im Artikel: FromDual Recovery Manager (rman) with progress indicator.
• Falls Sie sich entscheiden sollten, die Seite zu wechseln, gibt es im Artikel Migration from MySQL 5.7 to MariaDB 10.4 einige nützliche Tipps hierzu.
• Wenn Sie genauer wissen wollen, wo Ihr RAM geblieben ist gibt Ihnen der Artikel: Who else is using my memory - File System Cache analysis möglicherweise neue Denkanstösse.
• Bei der Untersuchung von Problemen nutzen wir immer wieder das General Query Log. Wie man es aber nur für eine bestimmte Verbindung aktiviert, finden Sie hier beschrieben: Enable General Query Log per Connection in MariaDB.
• Auch wir sind (leider) nicht ganz fehlerfrei. Wie wir unser CRM kaputt und anschliessend wieder heile gemacht haben beschreiben wir in: Oops! - That SQL Query was not intended... Flashback.
• Falls Sie sich das Swappen auf Ihrem System nicht erklären können, empfehlen wir Ihnen folgenden Artikel: Do not underestimate performance impacts of swapping on NUMA database systems.

Werkzeuge für den täglichen Datenbankbetrieb

FromDual bietet Ihnen auch eine breite Palette von nützlichen Werkzeuge für den täglichen Betrieb und die Pflege Ihrer MariaDB und MySQL Datenbanken an:

• Das überaus nützliche sys Schema haben wir von MySQL nach MariaDB portiert. Das sys Schema können Sie jetzt von Github beziehen. Wie sie dazu vorgehen müssen beschreiben wir im Artikel MariaDB sys Schema.
• Im Sommer haben wir einen weiteren Release des FromDual Ops Centers für MariaDB und MySQL freigegeben. Point-in-Time Recovery ist jetzt ebenfalls über das Ops Center möglich.
• Ebenfalls im August ist die neuste Version des FromDual Backup und Recovery Managers (brman) für MariaDB und MySQL erschienen.
• Hoffentlich noch dieses Jahr releasen wir die nächste Version des FromDual Performance Monitors für MariaDB und MySQL (fpmmm).
• Sämtliche FromDual Software kann von FromDual Servern unter Download heruntergeladen werden.

Monitoring as a Service

Falls Sie sich keine eigene Monitoring Lösung antun wollen, bieten wir Ihnen auch gerne unsere preiswerte Monitoring-as-a-Service (MaaS) Lösung an. Nehmen Sie hierzu einfach mit uns Kontakt auf. Wir unterbreiten Ihnen gerne ein Angebot.

Ich denke, hiermit haben Sie genügend zu tun, damit Ihnen über die Feiertag nicht langweilig wird... :-)

Schöne Weihnachten und einen gute Rutsch ins neue Jahr wünscht Ihnen
Ihr FromDual Schulungsteam

Taxonomy upgrade extras: 2020schulungtrainingmysql-trainingmariadb traininggalera cluster trainingmariadb schulunggalera cluster schulung

Hiermit präsentieren wir Ihnen das FromDual Schulungsprogramm 2020 für MariaDB und MySQL. Auch nächstes Jahr führen wir wieder zahlreiche MariaDB und MySQL Schulungen für Anfänger und Fortgeschrittene bei unseren 3 Partnern in Essen, Köln und Berlin durch.

Die Liste mit Schulungsterminen 2020 finden Sie hier.

Welches für Sie das richtige Schulungsmodul ist, erläutern wir unter der Seite Schulungsmodule.

Gleich richtig los geht es anfangs Januar mit MariaDB und MySQL für Einsteiger. Dieser Kurs ist schon fast ausgebucht und findet daher sicher statt. Sichern Sie sich also noch die letzten Plätze!

FromDual Technik-Blog

Im FromDual Blog berichten wir regelmässig darüber, mit welchen Themen wir unser Wissen erweitert haben. Diese Artikel sind frei verfügbar und somit auch Ihnen zugänglich:

• Mit MySQL 8.0 wird utf8mb4 das neue Default Character Set. Was Sie hierzu beachten sollten, haben wir im Artikel: MariaDB and MySQL Character Set Conversion beschrieben.
• Wie Sie den Progress Indicator des Backup und Recovery Managers nutzen, finden Sie im Artikel: FromDual Recovery Manager (rman) with progress indicator.
• Falls Sie sich entscheiden sollten, die Seite zu wechseln, gibt es im Artikel Migration from MySQL 5.7 to MariaDB 10.4 einige nützliche Tipps hierzu.
• Wenn Sie genauer wissen wollen, wo Ihr RAM geblieben ist gibt Ihnen der Artikel: Who else is using my memory - File System Cache analysis möglicherweise neue Denkanstösse.
• Bei der Untersuchung von Problemen nutzen wir immer wieder das General Query Log. Wie man es aber nur für eine bestimmte Verbindung aktiviert, finden Sie hier beschrieben: Enable General Query Log per Connection in MariaDB.
• Auch wir sind (leider) nicht ganz fehlerfrei. Wie wir unser CRM kaputt und anschliessend wieder heile gemacht haben beschreiben wir in: Oops! - That SQL Query was not intended... Flashback.
• Falls Sie sich das Swappen auf Ihrem System nicht erklären können, empfehlen wir Ihnen folgenden Artikel: Do not underestimate performance impacts of swapping on NUMA database systems.

Werkzeuge für den täglichen Datenbankbetrieb

FromDual bietet Ihnen auch eine breite Palette von nützlichen Werkzeuge für den täglichen Betrieb und die Pflege Ihrer MariaDB und MySQL Datenbanken an:

• Das überaus nützliche sys Schema haben wir von MySQL nach MariaDB portiert. Das sys Schema können Sie jetzt von Github beziehen. Wie sie dazu vorgehen müssen beschreiben wir im Artikel MariaDB sys Schema.
• Im Sommer haben wir einen weiteren Release des FromDual Ops Centers für MariaDB und MySQL freigegeben. Point-in-Time Recovery ist jetzt ebenfalls über das Ops Center möglich.
• Ebenfalls im August ist die neuste Version des FromDual Backup und Recovery Managers (brman) für MariaDB und MySQL erschienen.
• Hoffentlich noch dieses Jahr releasen wir die nächste Version des FromDual Performance Monitors für MariaDB und MySQL (fpmmm).
• Sämtliche FromDual Software kann von FromDual Servern unter Download heruntergeladen werden.

Monitoring as a Service

Falls Sie sich keine eigene Monitoring Lösung antun wollen, bieten wir Ihnen auch gerne unsere preiswerte Monitoring-as-a-Service (MaaS) Lösung an. Nehmen Sie hierzu einfach mit uns Kontakt auf. Wir unterbreiten Ihnen gerne ein Angebot.

Ich denke, hiermit haben Sie genügend zu tun, damit Ihnen über die Feiertag nicht langweilig wird... :-)

Schöne Weihnachten und einen gute Rutsch ins neue Jahr wünscht Ihnen
Ihr FromDual Schulungsteam

Taxonomy upgrade extras: 2020schulungtrainingmysql-trainingmariadb traininggalera cluster trainingmariadb schulunggalera cluster schulung

Up to version 5.5 MariaDB and MySQL can be considered as "the same" databases. The official wording at those times was "drop-in-replacement". But now we are a few years later and times and features changed. Also the official wording has slightly changed to just "compatible".
FromDual recommends that you consider MariaDB 10.3 and MySQL 8.0 as completely different database products (with some common roots) nowadays. Thus you should work and act accordingly.

Because more and more FromDual customers consider a migration from MySQL to MariaDB we were testing some migration paths to find the pitfalls. One upgrade of some test schemas led to the following warnings:

# mysql_upgrade --user=root MariaDB upgrade detected Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.columns_priv OK ... mysql.user OK Phase 2/7: Installing used storage engines Checking for tables with unknown storage engine Phase 3/7: Fixing views from mysql sys.host_summary Error : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist status : Operation failed sys.host_summary_by_file_io Error : Column count of mysql.proc is wrong. Expected 21, found 20. Created with MariaDB 50723, now running 100407. Please use mysql_upgrade to fix this error error : Corrupt ... sys.x$host_summary Error : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist Error : View 'sys.x$host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt ... sys.x$waits_global_by_latency OK Phase 4/7: Running 'mysql_fix_privilege_tables' Phase 5/7: Fixing table and database names Phase 6/7: Checking and upgrading tables Processing databases staging staging.sales OK staging.sugarcrm_contact_export Warning : Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline. status : OK Phase 7/7: Running 'FLUSH PRIVILEGES' OK

If you run the mysql_upgrade utility a 2nd time all issues are gone...

# mysql_upgrade --user=root --force
Some hints for upgrading

• Make a backup first before you start!
• Dropping MySQL sys Schema before the upgrade and installing MariaDB sys Schema again afterwards reduces noise a bit and lets you having a working sys Schema again.
  The MariaDB sys Schema you can find at GitHub: FromDual / mariadb-sys .
• It makes sense to read this document before you begin with the upgrade: MariaDB versus MySQL: Compatibility.

Literature

• Troubleshooting Row Size Too Large Errors with InnoDB
• mysql_upgrade
• MySQL - MariaDB sidegrade
• MariaDB and MySQL Upgrade Problems

Taxonomy upgrade extras: upgradesidegrademigrationmariadbmysql5.710.4

Up to version 5.5 MariaDB and MySQL can be considered as "the same" databases. The official wording at those times was "drop-in-replacement". But now we are a few years later and times and features changed. Also the official wording has slightly changed to just "compatible".
FromDual recommends that you consider MariaDB 10.3 and MySQL 8.0 as completely different database products (with some common roots) nowadays. Thus you should work and act accordingly.

Because more and more FromDual customers consider a migration from MySQL to MariaDB we were testing some migration paths to find the pitfalls. One upgrade of some test schemas led to the following warnings:

# mysql_upgrade --user=root MariaDB upgrade detected Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.columns_priv OK ... mysql.user OK Phase 2/7: Installing used storage engines Checking for tables with unknown storage engine Phase 3/7: Fixing views from mysql sys.host_summary Error : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist status : Operation failed sys.host_summary_by_file_io Error : Column count of mysql.proc is wrong. Expected 21, found 20. Created with MariaDB 50723, now running 100407. Please use mysql_upgrade to fix this error error : Corrupt ... sys.x$host_summary Error : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist Error : View 'sys.x$host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt ... sys.x$waits_global_by_latency OK Phase 4/7: Running 'mysql_fix_privilege_tables' Phase 5/7: Fixing table and database names Phase 6/7: Checking and upgrading tables Processing databases staging staging.sales OK staging.sugarcrm_contact_export Warning : Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline. status : OK Phase 7/7: Running 'FLUSH PRIVILEGES' OK

If you run the mysql_upgrade utility a 2nd time all issues are gone...

# mysql_upgrade --user=root --force
Some hints for upgrading

• Make a backup first before you start!
• Dropping MySQL sys Schema before the upgrade and installing MariaDB sys Schema again afterwards reduces noise a bit and lets you having a working sys Schema again.
  The MariaDB sys Schema you can find at GitHub: FromDual / mariadb-sys .
• It makes sense to read this document before you begin with the upgrade: MariaDB versus MySQL: Compatibility.

Literature

• Troubleshooting Row Size Too Large Errors with InnoDB
• mysql_upgrade
• MySQL - MariaDB sidegrade
• MariaDB and MySQL Upgrade Problems

Taxonomy upgrade extras: upgradesidegrademigrationmariadbmysql5.710.4

Up to version 5.5 MariaDB and MySQL can be considered as "the same" databases. The official wording at those times was "drop-in-replacement". But now we are a few years later and times and features changed. Also the official wording has slightly changed to just "compatible".
FromDual recommends that you consider MariaDB 10.3 and MySQL 8.0 as completely different database products (with some common roots) nowadays. Thus you should work and act accordingly.

Because more and more FromDual customers consider a migration from MySQL to MariaDB we were testing some migration paths to find the pitfalls. One upgrade of some test schemas led to the following warnings:

# mysql_upgrade --user=root MariaDB upgrade detected Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.columns_priv OK ... mysql.user OK Phase 2/7: Installing used storage engines Checking for tables with unknown storage engine Phase 3/7: Fixing views from mysql sys.host_summary Error : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist status : Operation failed sys.host_summary_by_file_io Error : Column count of mysql.proc is wrong. Expected 21, found 20. Created with MariaDB 50723, now running 100407. Please use mysql_upgrade to fix this error error : Corrupt ... sys.x$host_summary Error : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist Error : View 'sys.x$host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt ... sys.x$waits_global_by_latency OK Phase 4/7: Running 'mysql_fix_privilege_tables' Phase 5/7: Fixing table and database names Phase 6/7: Checking and upgrading tables Processing databases staging staging.sales OK staging.sugarcrm_contact_export Warning : Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline. status : OK Phase 7/7: Running 'FLUSH PRIVILEGES' OK

If you run the mysql_upgrade utility a 2nd time all issues are gone...

# mysql_upgrade --user=root --force
Some hints for upgrading

• Make a backup first before you start!
• Dropping MySQL sys Schema before the upgrade and installing MariaDB sys Schema again afterwards reduces noise a bit and lets you having a working sys Schema again.
  The MariaDB sys Schema you can find at GitHub: FromDual / mariadb-sys .
• It makes sense to read this document before you begin with the upgrade: MariaDB versus MySQL: Compatibility.

Literature

• Troubleshooting Row Size Too Large Errors with InnoDB
• mysql_upgrade
• MySQL - MariaDB sidegrade
• MariaDB and MySQL Upgrade Problems

Taxonomy upgrade extras: upgradesidegrademigrationmariadbmysql5.710.4

Since version 2.1.0 the FromDual Recovery Manager (rman) for MariaDB and MySQL has also a progress indicator for the restore of logical backups made with mysqldump. This feature was implemented because of the numerous requests of FromDual rman users who were not happy with the default mysql behavior.

You can check your current rman version as follows:

# ./bin/rman --version 2.2.1

As with all FromDual tools you get a command overview with examples with the --help option:

# ./bin/rman --help | less ... progress Print progress information to STDOUT. ...

A backup for example is done as follows:

# ./bin/bman --target=brman:secret@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102

The Recovery Manager progress indicator logs to STDOUT:

# ./bin/rman --target=brman:secret@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102 --progress --backup-name=bck_qamariadb102_full_2019-08-20_21:15:23.sql Reading configuration from /etc/mysql/my.cnf No rman configuration file. Command line: /home/mysql/product/brman-2.2.1/bin/rman.php --target=brman:******@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102 --progress --backup-name=bck_qamariadb102_full_2019-08-20_21:15:23.sql Options from command line target = brman:******@127.0.0.1:3308 type = full mode = logical progress = backup-name = bck_qamariadb102_full_2019-08-20_21:15:23.sql policy = daily instance-name = qamariadb102 Resulting options target = brman:******@127.0.0.1:3308 type = full mode = logical progress = backup-name = bck_qamariadb102_full_2019-08-20_21:15:23.sql policy = daily instance-name = qamariadb102 log = ./rman.log datadir = /var/lib/mysql owner = mysql backupdir = /home/mysql/bck binlog-policy = binlog Logging to ./rman.log Backupdir is /home/mysql/bck Version is 2.2.1 Start restore at 2019-08-20 21:18:46 mysql --user=brman --password=****** --host=127.0.0.1 --port=3308 From backup file: /home/mysql/bck/daily/bck_qamariadb102_full_2019-08-20_21:15:23.sql.gz Restore progress: . schema brman_catalog . . table backup_details 0 statements, 0 rows, 0 bytes . . table backups 0 statements, 0 rows, 0 bytes . . table binary_logs 0 statements, 0 rows, 0 bytes . . table files 0 statements, 0 rows, 0 bytes . . table metadata 1 statements, 2 rows, 78 bytes . schema foodmart . schema fromdual_a . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema fromdual_b . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table employees 0 statements, 0 rows, 0 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema fromdual_c . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema mysql . . table column_stats 0 statements, 0 rows, 0 bytes . . table columns_priv 0 statements, 0 rows, 0 bytes . . table db 1 statements, 2 rows, 267 bytes . . table event 0 statements, 0 rows, 0 bytes . . table func 0 statements, 0 rows, 0 bytes . . table gtid_slave_pos 0 statements, 0 rows, 0 bytes . . table help_category 1 statements, 39 rows, 1202 bytes . . table help_keyword 1 statements, 464 rows, 7649 bytes . . table help_relation 1 statements, 1028 rows, 9861 bytes . . table help_topic 1 statements, 527 rows, 419915 bytes . . table host 0 statements, 0 rows, 0 bytes . . table index_stats 0 statements, 0 rows, 0 bytes . . table innodb_index_stats 1 statements, 207 rows, 20611 bytes . . table innodb_table_stats 1 statements, 29 rows, 1622 bytes . . table plugin 0 statements, 0 rows, 0 bytes . . table proc 1 statements, 2 rows, 2220 bytes . . table procs_priv 0 statements, 0 rows, 0 bytes . . table proxies_priv 1 statements, 2 rows, 140 bytes . . table roles_mapping 0 statements, 0 rows, 0 bytes . . table servers 0 statements, 0 rows, 0 bytes . . table table_stats 0 statements, 0 rows, 0 bytes . . table tables_priv 0 statements, 0 rows, 0 bytes . . table time_zone 0 statements, 0 rows, 0 bytes . . table time_zone_leap_second 0 statements, 0 rows, 0 bytes . . table time_zone_name 0 statements, 0 rows, 0 bytes . . table time_zone_transition 0 statements, 0 rows, 0 bytes . . table time_zone_transition_type 0 statements, 0 rows, 0 bytes . . table user 1 statements, 5 rows, 1042 bytes . . table general_log 0 statements, 0 rows, 0 bytes . . table slow_log 0 statements, 0 rows, 0 bytes . schema test . . table test 347 statements, 4621 rows, 286528 bytes . schema test_catalog . schema world . . table City 1 statements, 4079 rows, 177139 bytes . . table Country 1 statements, 239 rows, 36481 bytes . . table CountryLanguage 1 statements, 984 rows, 26160 bytes . schema brman_catalog . schema foodmart . schema fromdual_a . schema fromdual_b . schema fromdual_c . schema mysql . schema test . schema test_catalog . schema world Schemas: 9, Tables: 55, Statements: 376, Rows: 12275, Bytes: 992736 WARNING: Progress numbers for Total Byte Counter may be different of dump file size. Restore time was: 0d 0h 1' 28" End restore at 2019-08-20 21:20:14 (rc=0)

The overhead of FromDual Recovery Manager progress indicator for MariaDB and MySQL is not significant. We measured less than 1% longer recovery times with the progress indicator compared to pure mysql restoration.

Taxonomy upgrade extras: RestoreRecoverypitrbrmanrmanFromDual Backup and Recovery Manager

Since version 2.1.0 the FromDual Recovery Manager (rman) for MariaDB and MySQL has also a progress indicator for the restore of logical backups made with mysqldump. This feature was implemented because of the numerous requests of FromDual rman users who were not happy with the default mysql behavior.

You can check your current rman version as follows:

# ./bin/rman --version 2.2.1

As with all FromDual tools you get a command overview with examples with the --help option:

# ./bin/rman --help | less ... progress Print progress information to STDOUT. ...

A backup for example is done as follows:

# ./bin/bman --target=brman:secret@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102

The Recovery Manager progress indicator logs to STDOUT:

# ./bin/rman --target=brman:secret@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102 --progress --backup-name=bck_qamariadb102_full_2019-08-20_21:15:23.sql Reading configuration from /etc/mysql/my.cnf No rman configuration file. Command line: /home/mysql/product/brman-2.2.1/bin/rman.php --target=brman:******@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102 --progress --backup-name=bck_qamariadb102_full_2019-08-20_21:15:23.sql Options from command line target = brman:******@127.0.0.1:3308 type = full mode = logical progress = backup-name = bck_qamariadb102_full_2019-08-20_21:15:23.sql policy = daily instance-name = qamariadb102 Resulting options target = brman:******@127.0.0.1:3308 type = full mode = logical progress = backup-name = bck_qamariadb102_full_2019-08-20_21:15:23.sql policy = daily instance-name = qamariadb102 log = ./rman.log datadir = /var/lib/mysql owner = mysql backupdir = /home/mysql/bck binlog-policy = binlog Logging to ./rman.log Backupdir is /home/mysql/bck Version is 2.2.1 Start restore at 2019-08-20 21:18:46 mysql --user=brman --password=****** --host=127.0.0.1 --port=3308 From backup file: /home/mysql/bck/daily/bck_qamariadb102_full_2019-08-20_21:15:23.sql.gz Restore progress: . schema brman_catalog . . table backup_details 0 statements, 0 rows, 0 bytes . . table backups 0 statements, 0 rows, 0 bytes . . table binary_logs 0 statements, 0 rows, 0 bytes . . table files 0 statements, 0 rows, 0 bytes . . table metadata 1 statements, 2 rows, 78 bytes . schema foodmart . schema fromdual_a . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema fromdual_b . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table employees 0 statements, 0 rows, 0 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema fromdual_c . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema mysql . . table column_stats 0 statements, 0 rows, 0 bytes . . table columns_priv 0 statements, 0 rows, 0 bytes . . table db 1 statements, 2 rows, 267 bytes . . table event 0 statements, 0 rows, 0 bytes . . table func 0 statements, 0 rows, 0 bytes . . table gtid_slave_pos 0 statements, 0 rows, 0 bytes . . table help_category 1 statements, 39 rows, 1202 bytes . . table help_keyword 1 statements, 464 rows, 7649 bytes . . table help_relation 1 statements, 1028 rows, 9861 bytes . . table help_topic 1 statements, 527 rows, 419915 bytes . . table host 0 statements, 0 rows, 0 bytes . . table index_stats 0 statements, 0 rows, 0 bytes . . table innodb_index_stats 1 statements, 207 rows, 20611 bytes . . table innodb_table_stats 1 statements, 29 rows, 1622 bytes . . table plugin 0 statements, 0 rows, 0 bytes . . table proc 1 statements, 2 rows, 2220 bytes . . table procs_priv 0 statements, 0 rows, 0 bytes . . table proxies_priv 1 statements, 2 rows, 140 bytes . . table roles_mapping 0 statements, 0 rows, 0 bytes . . table servers 0 statements, 0 rows, 0 bytes . . table table_stats 0 statements, 0 rows, 0 bytes . . table tables_priv 0 statements, 0 rows, 0 bytes . . table time_zone 0 statements, 0 rows, 0 bytes . . table time_zone_leap_second 0 statements, 0 rows, 0 bytes . . table time_zone_name 0 statements, 0 rows, 0 bytes . . table time_zone_transition 0 statements, 0 rows, 0 bytes . . table time_zone_transition_type 0 statements, 0 rows, 0 bytes . . table user 1 statements, 5 rows, 1042 bytes . . table general_log 0 statements, 0 rows, 0 bytes . . table slow_log 0 statements, 0 rows, 0 bytes . schema test . . table test 347 statements, 4621 rows, 286528 bytes . schema test_catalog . schema world . . table City 1 statements, 4079 rows, 177139 bytes . . table Country 1 statements, 239 rows, 36481 bytes . . table CountryLanguage 1 statements, 984 rows, 26160 bytes . schema brman_catalog . schema foodmart . schema fromdual_a . schema fromdual_b . schema fromdual_c . schema mysql . schema test . schema test_catalog . schema world Schemas: 9, Tables: 55, Statements: 376, Rows: 12275, Bytes: 992736 WARNING: Progress numbers for Total Byte Counter may be different of dump file size. Restore time was: 0d 0h 1' 28" End restore at 2019-08-20 21:20:14 (rc=0)

The overhead of FromDual Recovery Manager progress indicator for MariaDB and MySQL is not significant. We measured less than 1% longer recovery times with the progress indicator compared to pure mysql restoration.

Taxonomy upgrade extras: RestoreRecoverypitrbrmanrmanFromDual Backup and Recovery Manager

Since version 2.1.0 the FromDual Recovery Manager (rman) for MariaDB and MySQL has also a progress indicator for the restore of logical backups made with mysqldump. This feature was implemented because of the numerous requests of FromDual rman users who were not happy with the default mysql behavior.

You can check your current rman version as follows:

# ./bin/rman --version 2.2.1

As with all FromDual tools you get a command overview with examples with the --help option:

# ./bin/rman --help | less ... progress Print progress information to STDOUT. ...

A backup for example is done as follows:

# ./bin/bman --target=brman:secret@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102

The Recovery Manager progress indicator logs to STDOUT:

# ./bin/rman --target=brman:secret@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102 --progress --backup-name=bck_qamariadb102_full_2019-08-20_21:15:23.sql Reading configuration from /etc/mysql/my.cnf No rman configuration file. Command line: /home/mysql/product/brman-2.2.1/bin/rman.php --target=brman:******@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102 --progress --backup-name=bck_qamariadb102_full_2019-08-20_21:15:23.sql Options from command line target = brman:******@127.0.0.1:3308 type = full mode = logical progress = backup-name = bck_qamariadb102_full_2019-08-20_21:15:23.sql policy = daily instance-name = qamariadb102 Resulting options target = brman:******@127.0.0.1:3308 type = full mode = logical progress = backup-name = bck_qamariadb102_full_2019-08-20_21:15:23.sql policy = daily instance-name = qamariadb102 log = ./rman.log datadir = /var/lib/mysql owner = mysql backupdir = /home/mysql/bck binlog-policy = binlog Logging to ./rman.log Backupdir is /home/mysql/bck Version is 2.2.1 Start restore at 2019-08-20 21:18:46 mysql --user=brman --password=****** --host=127.0.0.1 --port=3308 From backup file: /home/mysql/bck/daily/bck_qamariadb102_full_2019-08-20_21:15:23.sql.gz Restore progress: . schema brman_catalog . . table backup_details 0 statements, 0 rows, 0 bytes . . table backups 0 statements, 0 rows, 0 bytes . . table binary_logs 0 statements, 0 rows, 0 bytes . . table files 0 statements, 0 rows, 0 bytes . . table metadata 1 statements, 2 rows, 78 bytes . schema foodmart . schema fromdual_a . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema fromdual_b . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table employees 0 statements, 0 rows, 0 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema fromdual_c . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema mysql . . table column_stats 0 statements, 0 rows, 0 bytes . . table columns_priv 0 statements, 0 rows, 0 bytes . . table db 1 statements, 2 rows, 267 bytes . . table event 0 statements, 0 rows, 0 bytes . . table func 0 statements, 0 rows, 0 bytes . . table gtid_slave_pos 0 statements, 0 rows, 0 bytes . . table help_category 1 statements, 39 rows, 1202 bytes . . table help_keyword 1 statements, 464 rows, 7649 bytes . . table help_relation 1 statements, 1028 rows, 9861 bytes . . table help_topic 1 statements, 527 rows, 419915 bytes . . table host 0 statements, 0 rows, 0 bytes . . table index_stats 0 statements, 0 rows, 0 bytes . . table innodb_index_stats 1 statements, 207 rows, 20611 bytes . . table innodb_table_stats 1 statements, 29 rows, 1622 bytes . . table plugin 0 statements, 0 rows, 0 bytes . . table proc 1 statements, 2 rows, 2220 bytes . . table procs_priv 0 statements, 0 rows, 0 bytes . . table proxies_priv 1 statements, 2 rows, 140 bytes . . table roles_mapping 0 statements, 0 rows, 0 bytes . . table servers 0 statements, 0 rows, 0 bytes . . table table_stats 0 statements, 0 rows, 0 bytes . . table tables_priv 0 statements, 0 rows, 0 bytes . . table time_zone 0 statements, 0 rows, 0 bytes . . table time_zone_leap_second 0 statements, 0 rows, 0 bytes . . table time_zone_name 0 statements, 0 rows, 0 bytes . . table time_zone_transition 0 statements, 0 rows, 0 bytes . . table time_zone_transition_type 0 statements, 0 rows, 0 bytes . . table user 1 statements, 5 rows, 1042 bytes . . table general_log 0 statements, 0 rows, 0 bytes . . table slow_log 0 statements, 0 rows, 0 bytes . schema test . . table test 347 statements, 4621 rows, 286528 bytes . schema test_catalog . schema world . . table City 1 statements, 4079 rows, 177139 bytes . . table Country 1 statements, 239 rows, 36481 bytes . . table CountryLanguage 1 statements, 984 rows, 26160 bytes . schema brman_catalog . schema foodmart . schema fromdual_a . schema fromdual_b . schema fromdual_c . schema mysql . schema test . schema test_catalog . schema world Schemas: 9, Tables: 55, Statements: 376, Rows: 12275, Bytes: 992736 WARNING: Progress numbers for Total Byte Counter may be different of dump file size. Restore time was: 0d 0h 1' 28" End restore at 2019-08-20 21:20:14 (rc=0)

The overhead of FromDual Recovery Manager progress indicator for MariaDB and MySQL is not significant. We measured less than 1% longer recovery times with the progress indicator compared to pure mysql restoration.

Taxonomy upgrade extras: RestoreRecoverypitrbrmanrmanFromDual Backup and Recovery Manager

Die Sommerferien sind vorbei. Mit neuem Elan in den Herbst! Zeit für eine Weiterbildung?

Vom 19. bis 20. September führt FromDual wieder die Galera Cluster Schulung Galera Cluster für MySQL und MariaDB in Berlin durch. Siehe auch unsere weiteren Schulungstermine.

Es hat noch Plätze frei! Anmelden können Sie sich direkt bei unserem Schulungs-Partner, der Heinlein Akademie.

Diese MariaDB/MySQL Weiterbildung richtet sich an alle DBAs, DevOps und System Administratoren, welche MariaDB und MySQL Datenbanken mit einem Galera Cluster zu betreuen haben und gerne besser verstehen wollen, wie Sie den Galera Cluster sicher und stabil betreiben.

In dieser Schulung behandeln wir, wie Sie einen Galera Cluster richtig designen und aufsetzten, wie Sie ihn installieren, konfigurieren und betreiben. Zudem betrachten wir mögliche Load Balancing Mechanismen und besprechen Performance Fragen zu Galera.

Das Ganze ist mit zahlreichen Übungen versehen, damit Sie das gelernte auch gleich praktisch anwenden können!

Die Schulung findet in deutscher Sprache statt.

Den detaillierten Inhalt dieser zweitägigen Galera Cluster Schulung finden Sie hier.

Bei weiteren Fragen nehmen Sie bitte mit uns Kontakt auf.

Taxonomy upgrade extras: schulunggaleraclustertraining

Die Sommerferien sind vorbei. Mit neuem Elan in den Herbst! Zeit für eine Weiterbildung?

Vom 19. bis 20. September führt FromDual wieder die Galera Cluster Schulung Galera Cluster für MySQL und MariaDB in Berlin durch. Siehe auch unsere weiteren Schulungstermine.

Es hat noch Plätze frei! Anmelden können Sie sich direkt bei unserem Schulungs-Partner, der Heinlein Akademie.

Diese MariaDB/MySQL Weiterbildung richtet sich an alle DBAs, DevOps und System Administratoren, welche MariaDB und MySQL Datenbanken mit einem Galera Cluster zu betreuen haben und gerne besser verstehen wollen, wie Sie den Galera Cluster sicher und stabil betreiben.

In dieser Schulung behandeln wir, wie Sie einen Galera Cluster richtig designen und aufsetzten, wie Sie ihn installieren, konfigurieren und betreiben. Zudem betrachten wir mögliche Load Balancing Mechanismen und besprechen Performance Fragen zu Galera.

Das Ganze ist mit zahlreichen Übungen versehen, damit Sie das gelernte auch gleich praktisch anwenden können!

Die Schulung findet in deutscher Sprache statt.

Den detaillierten Inhalt dieser zweitägigen Galera Cluster Schulung finden Sie hier.

Bei weiteren Fragen nehmen Sie bitte mit uns Kontakt auf.

Taxonomy upgrade extras: schulunggaleraclustertraining

Die Sommerferien sind vorbei. Mit neuem Elan in den Herbst! Zeit für eine Weiterbildung?

Vom 19. bis 20. September führt FromDual wieder die Galera Cluster Schulung Galera Cluster für MySQL und MariaDB in Berlin durch. Siehe auch unsere weiteren Schulungstermine.

Es hat noch Plätze frei! Anmelden können Sie sich direkt bei unserem Schulungs-Partner, der Heinlein Akademie.

Diese MariaDB/MySQL Weiterbildung richtet sich an alle DBAs, DevOps und System Administratoren, welche MariaDB und MySQL Datenbanken mit einem Galera Cluster zu betreuen haben und gerne besser verstehen wollen, wie Sie den Galera Cluster sicher und stabil betreiben.

In dieser Schulung behandeln wir, wie Sie einen Galera Cluster richtig designen und aufsetzten, wie Sie ihn installieren, konfigurieren und betreiben. Zudem betrachten wir mögliche Load Balancing Mechanismen und besprechen Performance Fragen zu Galera.

Das Ganze ist mit zahlreichen Übungen versehen, damit Sie das gelernte auch gleich praktisch anwenden können!

Die Schulung findet in deutscher Sprache statt.

Den detaillierten Inhalt dieser zweitägigen Galera Cluster Schulung finden Sie hier.

Bei weiteren Fragen nehmen Sie bitte mit uns Kontakt auf.

Taxonomy upgrade extras: schulunggaleraclustertraining

Contents

• Introduction
• Character Sets
• Steps to convert Character Set to utf8mb4
  • Analyzing the Server
  • Analyzing the Application and the Clients
  • Preparation of the Server Settings and the Application
  • Convert Tables to utf8mb4
  • Testing of new Character Set
• MySQL Pump
• MySQL Master/Slave Replication for Character Set conversion
• MySQL Shell, mysqlsh Upgrade Checker Utility

Introduction

Recently we had a consulting engagement where we had to help the customer to migrate from latin1 Character Set to utf8mb4 Character Set. In the same MySQL consulting engagement we considered to upgrade from MySQL 5.6 to MySQL 5.7 as well [ Lit. ]. We decided to split the change in 2 parts: Upgrading to 5.7 in the first step and converting to uft8mb4 in the second step. There were various reasons for this decision:

• 2 smaller changes are easier to control then one big shot.
• We assume that in 5.7 we experience less problems with utf8mb4 because the trend given by MySQL was more towards utf8mb4 in 5.7 than in MySQL 5.6. So we hope to hit less problems and bugs.

For Upgrading see also MariaDB and MySQL Upgrade Problems

Remark: It makes possibly also sens to think about Collations before starting with the conversion!

Character Sets

Historically MariaDB and MySQL had the default Character Set latin1 (Latin-1 or ISO-8859-1) which was sufficient for most of the western hemisphere.

But as technology spreads and demands increase other cultures want to have their characters represented understandably as well. So Unicode standard was invented. And MariaDB and MySQL applied this standard as well.

The original MariaDB/MySQL utf8(mb3) implementation was not perfect or complete so they implemented utf8mb4 as a super set of utf8(mb3). So at least since MariaDB/MySQL version 5.5 latin1, utf8 and utf8mb4 are available. The current MySQL 5.7 utf8mb4 implementation should cover Unicode 9.0.0:

SQL> SELECT * FROM information_schema.character_sets WHERE character_set_name LIKE 'utf8%' OR character_set_name = 'latin1'; +--------------------+----------------------+----------------------+--------+ | CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN | +--------------------+----------------------+----------------------+--------+ | latin1 | latin1_swedish_ci | cp1252 West European | 1 | | utf8 | utf8_general_ci | UTF-8 Unicode | 3 | | utf8mb4 | utf8mb4_general_ci | UTF-8 Unicode | 4 | +--------------------+----------------------+----------------------+--------+

The default Character Set up to MariaDB 10.4 and MySQL 5.7 was latin1. In MySQL 8.0 the default Character Set has changed to utf8mb4. There are no signs so far that MariaDB will take the same step:

SQL> status -------------- mysql Ver 8.0.16 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL) Connection id: 84 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.16 MySQL Community Server - GPL Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/run/mysqld/mysql-3332.sock Uptime: 3 days 47 min 13 sec

So we see a general trend from latin1 to utf8(mb3) to utf8mb4 technically and business wise (aka globalization).

For the DBA this means sooner or later we have to think about a conversion of all tables of the whole database instance (all tables of all schemata) to utf8mb4!

Steps to convert Character Set to utf8mb4 Analyzing the Server

First of all one should analyze the system (O/S, database instance and client/application). On the server we can run the following command to verify the actual used and supported Character Set:

# locale LANG=en_GB.UTF-8 LANGUAGE= LC_CTYPE="en_GB.UTF-8" LC_NUMERIC="en_GB.UTF-8" LC_TIME="en_GB.UTF-8" LC_COLLATE="en_GB.UTF-8" LC_MONETARY="en_GB.UTF-8" LC_MESSAGES="en_GB.UTF-8" LC_PAPER="en_GB.UTF-8" LC_NAME="en_GB.UTF-8" LC_ADDRESS="en_GB.UTF-8" LC_TELEPHONE="en_GB.UTF-8" LC_MEASUREMENT="en_GB.UTF-8" LC_IDENTIFICATION="en_GB.UTF-8" LC_ALL=

On the MariaDB/MySQL database instance we check the current server configuration and the session configuration with the following commands:

SQL> SHOW SESSION VARIABLES WHERE Variable_name LIKE 'character_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--------------------------+-------------------+ SQL> SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--------------------------+-------------------+

These configuration variables are for Client/Server communication: character_set_client, character_set_connection and character_set_results. These for Server configuration: character_set_server and character_set_database (deprecated in MySQL 5.7). And these for System internals and File System access: character_set_system and character_set_filesystem.

Sometimes we see customers using the Logon Trigger init_connect to force clients for a specific Character Set:

SQL> SHOW GLOBAL VARIABLES LIKE 'init_connect'; +---------------+------------------+ | Variable_name | Value | +---------------+------------------+ | init_connect | SET NAMES latin1 | +---------------+------------------+

The SET NAMES command sets the character_set_client, character_set_connection and character_set_results session variables. [ Lit. ]

Analyzing the Application and the Clients

Similar steps to analyze the Application and Clients should be taken. We want to answer the following questions:

• Support of utf8 of Application/Client O/S (Windows)?
• Support of utf8 of Web Server (Apache (AddDefaultCharset utf-8), Nginx, IIS, ...)
• Version of programming language (Java, PHP (5.4 and newer?), ...)
• Version of MariaDB and MySQL Connectors (JDBC (5.1.47 and newer?), ODBC (5.3.11 and newer?), mysqli/mysqlnd (⋝7.0.19?, ⋝7.1.5?), ...)
• Application code (header('Content-Type: text/html; charset=utf-8');, <?xml version="1.0" encoding="UTF-8"?>, <meta http-equiv="Content-Type" content="text/html; charset=utf-8">, <form accept-charset="utf-8">, htmlspecialchars($str, ENT_NOQUOTES, "UTF-8"), $mysqli->set_charset('utf8mb4');, mbstring [ Lit. ], etc.

See also: Configuring Application Character Set and Collation

If you do not have your Application under control your DBA can help you to find out with the General Query Log (SET GLOBAL general_log = 1;) what is going on:

190815 19:03:00 12 Connect root@localhost on 12 Query select @@version_comment limit 1 12 Query SET NAMES latin1 12 Query SET NAMES utf8 190815 19:05:24 12 Quit

or with some queries on the PERFORMANCE_SCHEMA:

-- Works since MySQL 5.6/MariaDB 10.0 SQL> SELECT t.thread_id, t.processlist_id, t.processlist_user, t.processlist_host, t.processlist_db , sca.attr_name, sca.attr_value FROM performance_schema.threads AS t JOIN performance_schema.session_connect_attrs AS sca ON sca.processlist_id = t.processlist_id WHERE t.processlist_user IS NOT NULL AND t.thread_id = 103 ; +-----------+----------------+------------------+------------------+----------------+----------------------------------+---------------------+ | thread_id | processlist_id | processlist_user | processlist_host | processlist_db | attr_name | attr_value | +-----------+----------------+------------------+------------------+----------------+----------------------------------+---------------------+ | 103 | 78 | replication | localhost | NULL | _os | linux-glibc2.12 | | 103 | 78 | replication | localhost | NULL | _client_name | libmysql | | 103 | 78 | replication | localhost | NULL | _pid | 29269 | | 103 | 78 | replication | localhost | NULL | program_name | mysqld | | 103 | 78 | replication | localhost | NULL | _platform | x86_64 | | 103 | 78 | replication | localhost | NULL | _client_replication_channel_name | NULL | | 103 | 78 | replication | localhost | NULL | _client_role | binary_log_listener | | 103 | 78 | replication | localhost | NULL | _client_version | 5.7.26 | +-----------+----------------+------------------+------------------+----------------+----------------------------------+---------------------+ -- Works since MySQL 5.7 only SELECT t.thread_id, t.processlist_id, t.processlist_user, t.processlist_host, t.processlist_db , vbt.variable_name, vbt.variable_value FROM performance_schema.threads AS t JOIN performance_schema.variables_by_thread AS vbt ON vbt.thread_id = t.thread_id WHERE t.processlist_user IS NOT NULL AND (vbt.variable_name like 'charac%' OR vbt.variable_name LIKE 'coll%') AND t.thread_id = 103 ; +-----------+----------------+------------------+------------------+----------------+--------------------------+-------------------+ | thread_id | processlist_id | processlist_user | processlist_host | processlist_db | variable_name | variable_value | +-----------+----------------+------------------+------------------+----------------+--------------------------+-------------------+ | 103 | 78 | replication | localhost | NULL | character_set_client | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_connection | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_database | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_filesystem | binary | | 103 | 78 | replication | localhost | NULL | character_set_results | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_server | latin1 | | 103 | 78 | replication | localhost | NULL | collation_connection | latin1_swedish_ci | | 103 | 78 | replication | localhost | NULL | collation_database | latin1_swedish_ci | | 103 | 78 | replication | localhost | NULL | collation_server | latin1_swedish_ci | +-----------+----------------+------------------+------------------+----------------+--------------------------+-------------------+
Preparation of the Server Settings and the Application

To have a better control of the impact of some changes we decided to do some changes on the Application first:

• Application is setting the Character Set properly itself ($mysqli->set_charset('utf8mb4') [ Lit. ]). In the same step also sql_mode can be set by the application so we can use the defaults on server side in the future.
• Apache and PHP are configured to support UTF-8.
• After this step init_connect, character_set_server and character_set_database can be changed to utf8mb4 on the Server and --skip-character-set-client-handshake can be removed at the same time [ Lit. ]

Convert Tables to utf8mb4

First we checked and converted the default Character Set of the Schemata/Databases:

SQL> SELECT schema_name, default_character_set_name, default_collation_name FROM information_schema.schemata WHERE schema_name NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') ; +-------------+----------------------------+------------------------+ | schema_name | default_character_set_name | default_collation_name | +-------------+----------------------------+------------------------+ | focmm | latin1 | latin1_swedish_ci | | test | latin1 | latin1_swedish_ci | | foodmart | latin1 | latin1_swedish_ci | | erp | latin1 | latin1_swedish_ci | | world | latin1 | latin1_swedish_ci | +-------------+----------------------------+------------------------+

Converting the Schemata is done as follows:

SQL> ALTER DATABASE ... DEFAULT CHARACTER SET utf8mb4;

which is a fast operation.

To convert the tables there are many different possibilities we considered:

• The easy one: ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4;
• The possibly faster one: dump/restore with sed 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8mb4/'
• The possibly fastest one: drop Secondary Indexes, dump/restore with sed, create Secondary Indexes again (mysqlpump).
• The automated one: With Perconas pt-online-schema-change [ Lit. ] or Facebooks OnlineSchemaChange OSC [ Lit. ]
• The most elegant but not supported one: Master/Slave Replication.

Already with converting the first table we run into a problem:

SQL> ALTER TABLE test CONVERT TO CHARACTER SET utf8mb4; ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

This table had a Primary Key of a length of more than 255 bytes and this is not possible with the old InnoDB Antelope File Format:

SQL> SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'innodb_file_format%' OR Variable_name LIKE 'innodb_large%'; +--------------------------+----------+ | Variable_name | Value | +--------------------------+----------+ | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_large_prefix | OFF | +--------------------------+----------+

So we have to find out first, which tables are still in old Antelope File Format:

SQL> SELECT table_schema , CASE WHEN row_format = 'Dynamic' THEN 'Barracuda' WHEN row_format = 'Compressed' THEN 'Barracuda' WHEN row_format = 'Compact' THEN 'Antelope' WHEN row_format = 'Redundant' THEN 'Antelope' END AS 'file_format' , COUNT(*) FROM information_schema.tables WHERE engine = 'InnoDB' AND table_schema NOT IN ('information_schema', 'sys', 'mysql') GROUP BY table_schema, file_format ; +--------------+-------------+----------+ | table_schema | file_format | count(*) | +--------------+-------------+----------+ | foodmart | Barracuda | 23 | | test | Barracuda | 1 | | world | Antelope | 2 | | world | Barracuda | 1 | +--------------+-------------+----------+

Then we could covert the table correctly forcing the Barracuda File format:

SQL> ALTER TABLE test CONVERT TO CHARACTER SET utf8mb4, ROW_FORMAT=dynamic;
Testing of new Character Set

The last but most important step is to test the changes. Here we recommend to do as many as possible different tests:

• MySQL CLI: mysql
• phpMyAdmin
• MySQL Workbench
• Other GUIs
• Your Application

Especially test careful data with umlauts (öäüÄÖÜß), foreign Characters (Turkish, Cyrillic, CJK Characters) and Emojis. Good Candidates are: Lastname, City or free text fields like Comment.

Some aid you also can get from here:

• My character encoding seems to be wrong. How can I fix it?
• Pile of Poo Emoji

MySQL Pump

mysqlpump — A Database Backup Program

This utility is currently not available for MariaDB. If works for MySQL 5.7 and newer and does NOT support MySQL 5.6. So we cannot use it for the Upgrade Process from MySQL 5.6 to 5.7. Newest MySQL Releases contain Bug fixes and even new Features in mysqlpump so we can assume it is still supported and actively maintained. Recent releases contain some fixes for trivial bugs so we can assume mysqlpump is not widely used yet and not as mature yet as mysqldump. An alternative product would be MyDumper from Domas@Facebook (Lauchpad, GitHub).

Interesting features are:

• Parallel dumping of databases.
• Secondary Index restore separated from Table Restore.

Missing features are:

• Does not support MariaDB and MySQL 5.6 and earlier.
• No binary log position is stored with backup (--master-data) but only GTID? So we have to use/enable GTID based Replication.

First test:

# mysqlpump --user=root --all-databases --single-transaction --triggers --routines --events --hex-blob > /tmp/mysqlpump_backup.sql Dump progress: 1/4 tables, 0/87 rows Dump progress: 41/46 tables, 789469/6956116 rows Dump progress: 41/46 tables, 1608219/6956116 rows Dump progress: 45/46 tables, 2358412/6956116 rows Dump progress: 45/46 tables, 3437912/6956116 rows Dump progress: 45/46 tables, 4493662/6956116 rows Dump progress: 45/46 tables, 5177662/6956116 rows Dump progress: 45/46 tables, 5568662/6956116 rows Dump progress: 45/46 tables, 6216162/6956116 rows Dump progress: 45/46 tables, 6716662/6956116 rows Dump completed in 9860 milliseconds ALTER TABLE `foodmart`.`account` ADD KEY `i_account_parent` (`account_parent_id`); ALTER TABLE `foodmart`.`category` ADD KEY `i_category_parent` (`category_parent_id`); ALTER TABLE `foodmart`.`employee` ADD KEY `i_employee_department_id` (`department_id`); ALTER TABLE `foodmart`.`employee` ADD KEY `i_employee_store_id` (`store_id`); ALTER TABLE `foodmart`.`employee` ADD KEY `i_employee_supervisor_id` (`supervisor_id`); ALTER TABLE `foodmart`.`expense_fact` ADD KEY `i_expense_store_id` (`store_id`); ALTER TABLE `foodmart`.`expense_fact` ADD KEY `i_expense_account_id` (`account_id`); ALTER TABLE `foodmart`.`expense_fact` ADD KEY `i_expense_time_id` (`time_id`);

Test against MySQL 5.6:

Server version is not compatible. Server version should be 5.7.8 or above.
MySQL Master/Slave Replication for Character Set conversion

Replication of Columns Having Different Data Types should work since MySQL 5.1.21. General rule when Statement Based Replication is used: If the query would work executed directly on the Slave it should also work on the Slave with Replication! So utf8mb4 on the Slave should also be possible with SBR. In MySQL 5.5.3 Attribute Promotion and Attribute Demotion was introduced with LOSSY and NON_LOSSY slave_type_conversions. But: Replication between columns using different character sets is not supported!

Some tests:

MasterSQL> INSERT INTO test VALUES (NULL, 'öäüÄÖÜß', NULL); MasterSQL> SELECT * FROM test; +----+----------------+---------------------+ | id | data | ts | +----+----------------+---------------------+ | 1 | öäüÄÖÜß | 2019-08-15 19:48:48 | | 2 | öäüÄÖÜß | 2019-08-15 19:50:00 | | 3 | öäüÄÖÜß | 2019-08-15 19:52:29 | +----+----------------+---------------------+ SlaveSQL> SHOW SLAVE STATUS\G ... Last_SQL_Errno: 1677 Last_SQL_Error: Column 1 of table 'test.test' cannot be converted from type 'varchar(255(bytes))' to type 'varchar(1020(bytes) utf8mb4)' ... SlaveSQL> SELECT * FROM test; +----+----------------+---------------------+ | id | data | ts | +----+----------------+---------------------+ | 1 | öäüÄÖÜß | 2019-08-15 19:48:48 | | 2 | öäüÄÖÜß | 2019-08-15 19:50:00 | +----+----------------+---------------------+

See also: Why is varchar(255) not varchar(255)? And:

MasterSQL> SET SESSION binlog_format=STATEMENT; MasterSQL> INSERT INTO test VALUES (NULL, 'öäüÄÖÜß', NULL); SlaveSQL> SELECT * FROM test; +----+----------------+---------------------+ | id | data | ts | +----+----------------+---------------------+ | 1 | öäüÄÖÜß | 2019-08-15 19:48:48 | | 2 | öäüÄÖÜß | 2019-08-15 19:50:00 | | 4 | öäüÄÖÜß | 2019-08-15 19:54:16 | +----+----------------+---------------------+
MySQL Shell, mysqlsh Upgrade Checker Utility

Since MySQL 5.7 we have a new MySQL Utility, the MySQL Shell (mysqlsh). The Upgrade Checker Utility in MySQL Shell will replace mysql_upgrade in MySQL 8.0. So we wanted to know if it is of any use for converting Character Set to utf8mb4? You can use the Upgrade Checker Utility to check MySQL 5.7 server instances for compatibility errors and issues for upgrading. But: The Upgrade Checker Utility does not support checking MySQL Server instances 5.6 an earlier. MySQL Shell 8.0 is highly recommended for use with MySQL Server 8.0 and 5.7. Please upgrade to MySQL Shell 8.0. [ Lit. ]

# ./mysqlsh root@localhost:3319 JS > util.checkForServerUpgrade() The MySQL server at localhost:3319, version 5.6.42-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 8.0.17... Util.checkForServerUpgrade: Detected MySQL server version is 5.6.42, but this tool requires server to be at least at version 5.7 (LogicError) JS > util.checkForServerUpgrade('root@127.0.0.1:3309') The MySQL server at localhost:3309, version 5.7.26-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 8.0.17... ... 2) Usage of db objects with names conflicting with reserved keywords in 8.0 No issues found 3) Usage of utf8mb3 charset Warning: The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support. More information: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html test.test.data - column's default character set: utf8 4) Table names in the mysql schema conflicting with new tables in 8.0 No issues found ... Errors: 0 Warnings: 1 Notices: 0 No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading. JS > util.checkForServerUpgrade('root@127.0.0.1:3309', {'targetVersion':'5.7.27'}) The MySQL server at 127.0.0.1:3309, version 5.7.26-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 5.7.27... Util.checkForServerUpgrade: This tool supports checking upgrade to MySQL server versions 8.0.11 to 8.0.17 (LogicError)

Because latin1 tables are still supported in MySQL 8.0 the Upgrade Checker Utility does not complain or do anything for latin1 tables. So it is not of any use for the utf8mb4 conversion task except you already have some old utf8(mb3) tables.

Taxonomy upgrade extras: character setconversionupgradelatin1utf8utf8mb4replicationmysqlshmysqlpump

Contents

• Introduction
• Character Sets
• Steps to convert Character Set to utf8mb4
  • Analyzing the Server
  • Analyzing the Application and the Clients
  • Preparation of the Server Settings and the Application
  • Convert Tables to utf8mb4
  • Testing of new Character Set
• MySQL Pump
• MySQL Master/Slave Replication for Character Set conversion
• MySQL Shell, mysqlsh Upgrade Checker Utility

Introduction

Recently we had a consulting engagement where we had to help the customer to migrate from latin1 Character Set to utf8mb4 Character Set. In the same MySQL consulting engagement we considered to upgrade from MySQL 5.6 to MySQL 5.7 as well [ Lit. ]. We decided to split the change in 2 parts: Upgrading to 5.7 in the first step and converting to uft8mb4 in the second step. There were various reasons for this decision:

• 2 smaller changes are easier to control then one big shot.
• We assume that in 5.7 we experience less problems with utf8mb4 because the trend given by MySQL was more towards utf8mb4 in 5.7 than in MySQL 5.6. So we hope to hit less problems and bugs.

For Upgrading see also MariaDB and MySQL Upgrade Problems

Remark: It makes possibly also sens to think about Collations before starting with the conversion!

Character Sets

Historically MariaDB and MySQL had the default Character Set latin1 (Latin-1 or ISO-8859-1) which was sufficient for most of the western hemisphere.

But as technology spreads and demands increase other cultures want to have their characters represented understandably as well. So Unicode standard was invented. And MariaDB and MySQL applied this standard as well.

The original MariaDB/MySQL utf8(mb3) implementation was not perfect or complete so they implemented utf8mb4 as a super set of utf8(mb3). So at least since MariaDB/MySQL version 5.5 latin1, utf8 and utf8mb4 are available. The current MySQL 5.7 utf8mb4 implementation should cover Unicode 9.0.0:

SQL> SELECT * FROM information_schema.character_sets WHERE character_set_name LIKE 'utf8%' OR character_set_name = 'latin1'; +--------------------+----------------------+----------------------+--------+ | CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN | +--------------------+----------------------+----------------------+--------+ | latin1 | latin1_swedish_ci | cp1252 West European | 1 | | utf8 | utf8_general_ci | UTF-8 Unicode | 3 | | utf8mb4 | utf8mb4_general_ci | UTF-8 Unicode | 4 | +--------------------+----------------------+----------------------+--------+

The default Character Set up to MariaDB 10.4 and MySQL 5.7 was latin1. In MySQL 8.0 the default Character Set has changed to utf8mb4. There are no signs so far that MariaDB will take the same step:

SQL> status -------------- mysql Ver 8.0.16 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL) Connection id: 84 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.16 MySQL Community Server - GPL Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/run/mysqld/mysql-3332.sock Uptime: 3 days 47 min 13 sec

So we see a general trend from latin1 to utf8(mb3) to utf8mb4 technically and business wise (aka globalization).

For the DBA this means sooner or later we have to think about a conversion of all tables of the whole database instance (all tables of all schemata) to utf8mb4!

Steps to convert Character Set to utf8mb4 Analyzing the Server

First of all one should analyze the system (O/S, database instance and client/application). On the server we can run the following command to verify the actual used and supported Character Set:

# locale LANG=en_GB.UTF-8 LANGUAGE= LC_CTYPE="en_GB.UTF-8" LC_NUMERIC="en_GB.UTF-8" LC_TIME="en_GB.UTF-8" LC_COLLATE="en_GB.UTF-8" LC_MONETARY="en_GB.UTF-8" LC_MESSAGES="en_GB.UTF-8" LC_PAPER="en_GB.UTF-8" LC_NAME="en_GB.UTF-8" LC_ADDRESS="en_GB.UTF-8" LC_TELEPHONE="en_GB.UTF-8" LC_MEASUREMENT="en_GB.UTF-8" LC_IDENTIFICATION="en_GB.UTF-8" LC_ALL=

On the MariaDB/MySQL database instance we check the current server configuration and the session configuration with the following commands:

SQL> SHOW SESSION VARIABLES WHERE Variable_name LIKE 'character_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--------------------------+-------------------+ SQL> SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--------------------------+-------------------+

These configuration variables are for Client/Server communication: character_set_client, character_set_connection and character_set_results. These for Server configuration: character_set_server and character_set_database (deprecated in MySQL 5.7). And these for System internals and File System access: character_set_system and character_set_filesystem.

Sometimes we see customers using the Logon Trigger init_connect to force clients for a specific Character Set:

SQL> SHOW GLOBAL VARIABLES LIKE 'init_connect'; +---------------+------------------+ | Variable_name | Value | +---------------+------------------+ | init_connect | SET NAMES latin1 | +---------------+------------------+

The SET NAMES command sets the character_set_client, character_set_connection and character_set_results session variables. [ Lit. ]

Analyzing the Application and the Clients

Similar steps to analyze the Application and Clients should be taken. We want to answer the following questions:

• Support of utf8 of Application/Client O/S (Windows)?
• Support of utf8 of Web Server (Apache (AddDefaultCharset utf-8), Nginx, IIS, ...)
• Version of programming language (Java, PHP (5.4 and newer?), ...)
• Version of MariaDB and MySQL Connectors (JDBC (5.1.47 and newer?), ODBC (5.3.11 and newer?), mysqli/mysqlnd (⋝7.0.19?, ⋝7.1.5?), ...)
• Application code (header('Content-Type: text/html; charset=utf-8');, <?xml version="1.0" encoding="UTF-8"?>, <meta http-equiv="Content-Type" content="text/html; charset=utf-8">, <form accept-charset="utf-8">, htmlspecialchars($str, ENT_NOQUOTES, "UTF-8"), $mysqli->set_charset('utf8mb4');, mbstring [ Lit. ], etc.

See also: Configuring Application Character Set and Collation

If you do not have your Application under control your DBA can help you to find out with the General Query Log (SET GLOBAL general_log = 1;) what is going on:

190815 19:03:00 12 Connect root@localhost on 12 Query select @@version_comment limit 1 12 Query SET NAMES latin1 12 Query SET NAMES utf8 190815 19:05:24 12 Quit

or with some queries on the PERFORMANCE_SCHEMA:

-- Works since MySQL 5.6/MariaDB 10.0 SQL> SELECT t.thread_id, t.processlist_id, t.processlist_user, t.processlist_host, t.processlist_db , sca.attr_name, sca.attr_value FROM performance_schema.threads AS t JOIN performance_schema.session_connect_attrs AS sca ON sca.processlist_id = t.processlist_id WHERE t.processlist_user IS NOT NULL AND t.thread_id = 103 ; +-----------+----------------+------------------+------------------+----------------+----------------------------------+---------------------+ | thread_id | processlist_id | processlist_user | processlist_host | processlist_db | attr_name | attr_value | +-----------+----------------+------------------+------------------+----------------+----------------------------------+---------------------+ | 103 | 78 | replication | localhost | NULL | _os | linux-glibc2.12 | | 103 | 78 | replication | localhost | NULL | _client_name | libmysql | | 103 | 78 | replication | localhost | NULL | _pid | 29269 | | 103 | 78 | replication | localhost | NULL | program_name | mysqld | | 103 | 78 | replication | localhost | NULL | _platform | x86_64 | | 103 | 78 | replication | localhost | NULL | _client_replication_channel_name | NULL | | 103 | 78 | replication | localhost | NULL | _client_role | binary_log_listener | | 103 | 78 | replication | localhost | NULL | _client_version | 5.7.26 | +-----------+----------------+------------------+------------------+----------------+----------------------------------+---------------------+ -- Works since MySQL 5.7 only SELECT t.thread_id, t.processlist_id, t.processlist_user, t.processlist_host, t.processlist_db , vbt.variable_name, vbt.variable_value FROM performance_schema.threads AS t JOIN performance_schema.variables_by_thread AS vbt ON vbt.thread_id = t.thread_id WHERE t.processlist_user IS NOT NULL AND (vbt.variable_name like 'charac%' OR vbt.variable_name LIKE 'coll%') AND t.thread_id = 103 ; +-----------+----------------+------------------+------------------+----------------+--------------------------+-------------------+ | thread_id | processlist_id | processlist_user | processlist_host | processlist_db | variable_name | variable_value | +-----------+----------------+------------------+------------------+----------------+--------------------------+-------------------+ | 103 | 78 | replication | localhost | NULL | character_set_client | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_connection | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_database | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_filesystem | binary | | 103 | 78 | replication | localhost | NULL | character_set_results | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_server | latin1 | | 103 | 78 | replication | localhost | NULL | collation_connection | latin1_swedish_ci | | 103 | 78 | replication | localhost | NULL | collation_database | latin1_swedish_ci | | 103 | 78 | replication | localhost | NULL | collation_server | latin1_swedish_ci | +-----------+----------------+------------------+------------------+----------------+--------------------------+-------------------+
Preparation of the Server Settings and the Application

To have a better control of the impact of some changes we decided to do some changes on the Application first:

• Application is setting the Character Set properly itself ($mysqli->set_charset('utf8mb4') [ Lit. ]). In the same step also sql_mode can be set by the application so we can use the defaults on server side in the future.
• Apache and PHP are configured to support UTF-8.
• After this step init_connect, character_set_server and character_set_database can be changed to utf8mb4 on the Server and --skip-character-set-client-handshake can be removed at the same time [ Lit. ]

Convert Tables to utf8mb4

First we checked and converted the default Character Set of the Schemata/Databases:

SQL> SELECT schema_name, default_character_set_name, default_collation_name FROM information_schema.schemata WHERE schema_name NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') ; +-------------+----------------------------+------------------------+ | schema_name | default_character_set_name | default_collation_name | +-------------+----------------------------+------------------------+ | focmm | latin1 | latin1_swedish_ci | | test | latin1 | latin1_swedish_ci | | foodmart | latin1 | latin1_swedish_ci | | erp | latin1 | latin1_swedish_ci | | world | latin1 | latin1_swedish_ci | +-------------+----------------------------+------------------------+

Converting the Schemata is done as follows:

SQL> ALTER DATABASE ... DEFAULT CHARACTER SET utf8mb4;

which is a fast operation.

To convert the tables there are many different possibilities we considered:

• The easy one: ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4;
• The possibly faster one: dump/restore with sed 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8mb4/'
• The possibly fastest one: drop Secondary Indexes, dump/restore with sed, create Secondary Indexes again (mysqlpump).
• The automated one: With Perconas pt-online-schema-change [ Lit. ] or Facebooks OnlineSchemaChange OSC [ Lit. ]
• The most elegant but not supported one: Master/Slave Replication.

Already with converting the first table we run into a problem:

SQL> ALTER TABLE test CONVERT TO CHARACTER SET utf8mb4; ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

This table had a Primary Key of a length of more than 255 bytes and this is not possible with the old InnoDB Antelope File Format:

SQL> SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'innodb_file_format%' OR Variable_name LIKE 'innodb_large%'; +--------------------------+----------+ | Variable_name | Value | +--------------------------+----------+ | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_large_prefix | OFF | +--------------------------+----------+

So we have to find out first, which tables are still in old Antelope File Format:

SQL> SELECT table_schema , CASE WHEN row_format = 'Dynamic' THEN 'Barracuda' WHEN row_format = 'Compressed' THEN 'Barracuda' WHEN row_format = 'Compact' THEN 'Antelope' WHEN row_format = 'Redundant' THEN 'Antelope' END AS 'file_format' , COUNT(*) FROM information_schema.tables WHERE engine = 'InnoDB' AND table_schema NOT IN ('information_schema', 'sys', 'mysql') GROUP BY table_schema, file_format ; +--------------+-------------+----------+ | table_schema | file_format | count(*) | +--------------+-------------+----------+ | foodmart | Barracuda | 23 | | test | Barracuda | 1 | | world | Antelope | 2 | | world | Barracuda | 1 | +--------------+-------------+----------+

Then we could covert the table correctly forcing the Barracuda File format:

SQL> ALTER TABLE test CONVERT TO CHARACTER SET utf8mb4, ROW_FORMAT=dynamic;
Testing of new Character Set

The last but most important step is to test the changes. Here we recommend to do as many as possible different tests:

• MySQL CLI: mysql
• phpMyAdmin
• MySQL Workbench
• Other GUIs
• Your Application

Especially test careful data with umlauts (öäüÄÖÜß), foreign Characters (Turkish, Cyrillic, CJK Characters) and Emojis. Good Candidates are: Lastname, City or free text fields like Comment.

Some aid you also can get from here:

• My character encoding seems to be wrong. How can I fix it?
• Pile of Poo Emoji

MySQL Pump

mysqlpump — A Database Backup Program

This utility is currently not available for MariaDB. If works for MySQL 5.7 and newer and does NOT support MySQL 5.6. So we cannot use it for the Upgrade Process from MySQL 5.6 to 5.7. Newest MySQL Releases contain Bug fixes and even new Features in mysqlpump so we can assume it is still supported and actively maintained. Recent releases contain some fixes for trivial bugs so we can assume mysqlpump is not widely used yet and not as mature yet as mysqldump. An alternative product would be MyDumper from Domas@Facebook (Lauchpad, GitHub).

Interesting features are:

• Parallel dumping of databases.
• Secondary Index restore separated from Table Restore.

Missing features are:

• Does not support MariaDB and MySQL 5.6 and earlier.
• No binary log position is stored with backup (--master-data) but only GTID? So we have to use/enable GTID based Replication.

First test:

# mysqlpump --user=root --all-databases --single-transaction --triggers --routines --events --hex-blob > /tmp/mysqlpump_backup.sql Dump progress: 1/4 tables, 0/87 rows Dump progress: 41/46 tables, 789469/6956116 rows Dump progress: 41/46 tables, 1608219/6956116 rows Dump progress: 45/46 tables, 2358412/6956116 rows Dump progress: 45/46 tables, 3437912/6956116 rows Dump progress: 45/46 tables, 4493662/6956116 rows Dump progress: 45/46 tables, 5177662/6956116 rows Dump progress: 45/46 tables, 5568662/6956116 rows Dump progress: 45/46 tables, 6216162/6956116 rows Dump progress: 45/46 tables, 6716662/6956116 rows Dump completed in 9860 milliseconds ALTER TABLE `foodmart`.`account` ADD KEY `i_account_parent` (`account_parent_id`); ALTER TABLE `foodmart`.`category` ADD KEY `i_category_parent` (`category_parent_id`); ALTER TABLE `foodmart`.`employee` ADD KEY `i_employee_department_id` (`department_id`); ALTER TABLE `foodmart`.`employee` ADD KEY `i_employee_store_id` (`store_id`); ALTER TABLE `foodmart`.`employee` ADD KEY `i_employee_supervisor_id` (`supervisor_id`); ALTER TABLE `foodmart`.`expense_fact` ADD KEY `i_expense_store_id` (`store_id`); ALTER TABLE `foodmart`.`expense_fact` ADD KEY `i_expense_account_id` (`account_id`); ALTER TABLE `foodmart`.`expense_fact` ADD KEY `i_expense_time_id` (`time_id`);

Test against MySQL 5.6:

Server version is not compatible. Server version should be 5.7.8 or above.
MySQL Master/Slave Replication for Character Set conversion

Replication of Columns Having Different Data Types should work since MySQL 5.1.21. General rule when Statement Based Replication is used: If the query would work executed directly on the Slave it should also work on the Slave with Replication! So utf8mb4 on the Slave should also be possible with SBR. In MySQL 5.5.3 Attribute Promotion and Attribute Demotion was introduced with LOSSY and NON_LOSSY slave_type_conversions. But: Replication between columns using different character sets is not supported!

Some tests:

MasterSQL> INSERT INTO test VALUES (NULL, 'öäüÄÖÜß', NULL); MasterSQL> SELECT * FROM test; +----+----------------+---------------------+ | id | data | ts | +----+----------------+---------------------+ | 1 | öäüÄÖÜß | 2019-08-15 19:48:48 | | 2 | öäüÄÖÜß | 2019-08-15 19:50:00 | | 3 | öäüÄÖÜß | 2019-08-15 19:52:29 | +----+----------------+---------------------+ SlaveSQL> SHOW SLAVE STATUS\G ... Last_SQL_Errno: 1677 Last_SQL_Error: Column 1 of table 'test.test' cannot be converted from type 'varchar(255(bytes))' to type 'varchar(1020(bytes) utf8mb4)' ... SlaveSQL> SELECT * FROM test; +----+----------------+---------------------+ | id | data | ts | +----+----------------+---------------------+ | 1 | öäüÄÖÜß | 2019-08-15 19:48:48 | | 2 | öäüÄÖÜß | 2019-08-15 19:50:00 | +----+----------------+---------------------+

See also: Why is varchar(255) not varchar(255)? And:

MasterSQL> SET SESSION binlog_format=STATEMENT; MasterSQL> INSERT INTO test VALUES (NULL, 'öäüÄÖÜß', NULL); SlaveSQL> SELECT * FROM test; +----+----------------+---------------------+ | id | data | ts | +----+----------------+---------------------+ | 1 | öäüÄÖÜß | 2019-08-15 19:48:48 | | 2 | öäüÄÖÜß | 2019-08-15 19:50:00 | | 4 | öäüÄÖÜß | 2019-08-15 19:54:16 | +----+----------------+---------------------+
MySQL Shell, mysqlsh Upgrade Checker Utility

Since MySQL 5.7 we have a new MySQL Utility, the MySQL Shell (mysqlsh). The Upgrade Checker Utility in MySQL Shell will replace mysql_upgrade in MySQL 8.0. So we wanted to know if it is of any use for converting Character Set to utf8mb4? You can use the Upgrade Checker Utility to check MySQL 5.7 server instances for compatibility errors and issues for upgrading. But: The Upgrade Checker Utility does not support checking MySQL Server instances 5.6 an earlier. MySQL Shell 8.0 is highly recommended for use with MySQL Server 8.0 and 5.7. Please upgrade to MySQL Shell 8.0. [ Lit. ]

# ./mysqlsh root@localhost:3319 JS > util.checkForServerUpgrade() The MySQL server at localhost:3319, version 5.6.42-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 8.0.17... Util.checkForServerUpgrade: Detected MySQL server version is 5.6.42, but this tool requires server to be at least at version 5.7 (LogicError) JS > util.checkForServerUpgrade('root@127.0.0.1:3309') The MySQL server at localhost:3309, version 5.7.26-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 8.0.17... ... 2) Usage of db objects with names conflicting with reserved keywords in 8.0 No issues found 3) Usage of utf8mb3 charset Warning: The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support. More information: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html test.test.data - column's default character set: utf8 4) Table names in the mysql schema conflicting with new tables in 8.0 No issues found ... Errors: 0 Warnings: 1 Notices: 0 No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading. JS > util.checkForServerUpgrade('root@127.0.0.1:3309', {'targetVersion':'5.7.27'}) The MySQL server at 127.0.0.1:3309, version 5.7.26-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 5.7.27... Util.checkForServerUpgrade: This tool supports checking upgrade to MySQL server versions 8.0.11 to 8.0.17 (LogicError)

Because latin1 tables are still supported in MySQL 8.0 the Upgrade Checker Utility does not complain or do anything for latin1 tables. So it is not of any use for the utf8mb4 conversion task except you already have some old utf8(mb3) tables.

Taxonomy upgrade extras: character setconversionupgradelatin1utf8utf8mb4replicationmysqlshmysqlpump

Contents

• Introduction
• Character Sets
• Steps to convert Character Set to utf8mb4
  • Analyzing the Server
  • Analyzing the Application and the Clients
  • Preparation of the Server Settings and the Application
  • Convert Tables to utf8mb4
  • Testing of new Character Set
• MySQL Pump
• MySQL Master/Slave Replication for Character Set conversion
• MySQL Shell, mysqlsh Upgrade Checker Utility

Introduction

Recently we had a consulting engagement where we had to help the customer to migrate from latin1 Character Set to utf8mb4 Character Set. In the same MySQL consulting engagement we considered to upgrade from MySQL 5.6 to MySQL 5.7 as well [ Lit. ]. We decided to split the change in 2 parts: Upgrading to 5.7 in the first step and converting to uft8mb4 in the second step. There were various reasons for this decision:

• 2 smaller changes are easier to control then one big shot.
• We assume that in 5.7 we experience less problems with utf8mb4 because the trend given by MySQL was more towards utf8mb4 in 5.7 than in MySQL 5.6. So we hope to hit less problems and bugs.

For Upgrading see also MariaDB and MySQL Upgrade Problems

Remark: It makes possibly also sens to think about Collations before starting with the conversion!

Character Sets

Historically MariaDB and MySQL had the default Character Set latin1 (Latin-1 or ISO-8859-1) which was sufficient for most of the western hemisphere.

But as technology spreads and demands increase other cultures want to have their characters represented understandably as well. So Unicode standard was invented. And MariaDB and MySQL applied this standard as well.

The original MariaDB/MySQL utf8(mb3) implementation was not perfect or complete so they implemented utf8mb4 as a super set of utf8(mb3). So at least since MariaDB/MySQL version 5.5 latin1, utf8 and utf8mb4 are available. The current MySQL 5.7 utf8mb4 implementation should cover Unicode 9.0.0:

SQL> SELECT * FROM information_schema.character_sets WHERE character_set_name LIKE 'utf8%' OR character_set_name = 'latin1'; +--------------------+----------------------+----------------------+--------+ | CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN | +--------------------+----------------------+----------------------+--------+ | latin1 | latin1_swedish_ci | cp1252 West European | 1 | | utf8 | utf8_general_ci | UTF-8 Unicode | 3 | | utf8mb4 | utf8mb4_general_ci | UTF-8 Unicode | 4 | +--------------------+----------------------+----------------------+--------+

The default Character Set up to MariaDB 10.4 and MySQL 5.7 was latin1. In MySQL 8.0 the default Character Set has changed to utf8mb4. There are no signs so far that MariaDB will take the same step:

SQL> status -------------- mysql Ver 8.0.16 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL) Connection id: 84 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.16 MySQL Community Server - GPL Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/run/mysqld/mysql-3332.sock Uptime: 3 days 47 min 13 sec

So we see a general trend from latin1 to utf8(mb3) to utf8mb4 technically and business wise (aka globalization).

For the DBA this means sooner or later we have to think about a conversion of all tables of the whole database instance (all tables of all schemata) to utf8mb4!

Steps to convert Character Set to utf8mb4 Analyzing the Server

First of all one should analyze the system (O/S, database instance and client/application). On the server we can run the following command to verify the actual used and supported Character Set:

# locale LANG=en_GB.UTF-8 LANGUAGE= LC_CTYPE="en_GB.UTF-8" LC_NUMERIC="en_GB.UTF-8" LC_TIME="en_GB.UTF-8" LC_COLLATE="en_GB.UTF-8" LC_MONETARY="en_GB.UTF-8" LC_MESSAGES="en_GB.UTF-8" LC_PAPER="en_GB.UTF-8" LC_NAME="en_GB.UTF-8" LC_ADDRESS="en_GB.UTF-8" LC_TELEPHONE="en_GB.UTF-8" LC_MEASUREMENT="en_GB.UTF-8" LC_IDENTIFICATION="en_GB.UTF-8" LC_ALL=

On the MariaDB/MySQL database instance we check the current server configuration and the session configuration with the following commands:

SQL> SHOW SESSION VARIABLES WHERE Variable_name LIKE 'character_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--------------------------+-------------------+ SQL> SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--------------------------+-------------------+

These configuration variables are for Client/Server communication: character_set_client, character_set_connection and character_set_results. These for Server configuration: character_set_server and character_set_database (deprecated in MySQL 5.7). And these for System internals and File System access: character_set_system and character_set_filesystem.

Sometimes we see customers using the Logon Trigger init_connect to force clients for a specific Character Set:

SQL> SHOW GLOBAL VARIABLES LIKE 'init_connect'; +---------------+------------------+ | Variable_name | Value | +---------------+------------------+ | init_connect | SET NAMES latin1 | +---------------+------------------+

The SET NAMES command sets the character_set_client, character_set_connection and character_set_results session variables. [ Lit. ]

Analyzing the Application and the Clients

Similar steps to analyze the Application and Clients should be taken. We want to answer the following questions:

• Support of utf8 of Application/Client O/S (Windows)?
• Support of utf8 of Web Server (Apache (AddDefaultCharset utf-8), Nginx, IIS, ...)
• Version of programming language (Java, PHP (5.4 and newer?), ...)
• Version of MariaDB and MySQL Connectors (JDBC (5.1.47 and newer?), ODBC (5.3.11 and newer?), mysqli/mysqlnd (⋝7.0.19?, ⋝7.1.5?), ...)
• Application code (header('Content-Type: text/html; charset=utf-8');, <?xml version="1.0" encoding="UTF-8"?>, <meta http-equiv="Content-Type" content="text/html; charset=utf-8">, <form accept-charset="utf-8">, htmlspecialchars($str, ENT_NOQUOTES, "UTF-8"), $mysqli->set_charset('utf8mb4');, mbstring [ Lit. ], etc.

See also: Configuring Application Character Set and Collation

If you do not have your Application under control your DBA can help you to find out with the General Query Log (SET GLOBAL general_log = 1;) what is going on:

190815 19:03:00 12 Connect root@localhost on 12 Query select @@version_comment limit 1 12 Query SET NAMES latin1 12 Query SET NAMES utf8 190815 19:05:24 12 Quit

or with some queries on the PERFORMANCE_SCHEMA:

-- Works since MySQL 5.6/MariaDB 10.0 SQL> SELECT t.thread_id, t.processlist_id, t.processlist_user, t.processlist_host, t.processlist_db , sca.attr_name, sca.attr_value FROM performance_schema.threads AS t JOIN performance_schema.session_connect_attrs AS sca ON sca.processlist_id = t.processlist_id WHERE t.processlist_user IS NOT NULL AND t.thread_id = 103 ; +-----------+----------------+------------------+------------------+----------------+----------------------------------+---------------------+ | thread_id | processlist_id | processlist_user | processlist_host | processlist_db | attr_name | attr_value | +-----------+----------------+------------------+------------------+----------------+----------------------------------+---------------------+ | 103 | 78 | replication | localhost | NULL | _os | linux-glibc2.12 | | 103 | 78 | replication | localhost | NULL | _client_name | libmysql | | 103 | 78 | replication | localhost | NULL | _pid | 29269 | | 103 | 78 | replication | localhost | NULL | program_name | mysqld | | 103 | 78 | replication | localhost | NULL | _platform | x86_64 | | 103 | 78 | replication | localhost | NULL | _client_replication_channel_name | NULL | | 103 | 78 | replication | localhost | NULL | _client_role | binary_log_listener | | 103 | 78 | replication | localhost | NULL | _client_version | 5.7.26 | +-----------+----------------+------------------+------------------+----------------+----------------------------------+---------------------+ -- Works since MySQL 5.7 only SELECT t.thread_id, t.processlist_id, t.processlist_user, t.processlist_host, t.processlist_db , vbt.variable_name, vbt.variable_value FROM performance_schema.threads AS t JOIN performance_schema.variables_by_thread AS vbt ON vbt.thread_id = t.thread_id WHERE t.processlist_user IS NOT NULL AND (vbt.variable_name like 'charac%' OR vbt.variable_name LIKE 'coll%') AND t.thread_id = 103 ; +-----------+----------------+------------------+------------------+----------------+--------------------------+-------------------+ | thread_id | processlist_id | processlist_user | processlist_host | processlist_db | variable_name | variable_value | +-----------+----------------+------------------+------------------+----------------+--------------------------+-------------------+ | 103 | 78 | replication | localhost | NULL | character_set_client | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_connection | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_database | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_filesystem | binary | | 103 | 78 | replication | localhost | NULL | character_set_results | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_server | latin1 | | 103 | 78 | replication | localhost | NULL | collation_connection | latin1_swedish_ci | | 103 | 78 | replication | localhost | NULL | collation_database | latin1_swedish_ci | | 103 | 78 | replication | localhost | NULL | collation_server | latin1_swedish_ci | +-----------+----------------+------------------+------------------+----------------+--------------------------+-------------------+
Preparation of the Server Settings and the Application

To have a better control of the impact of some changes we decided to do some changes on the Application first:

• Application is setting the Character Set properly itself ($mysqli->set_charset('utf8mb4') [ Lit. ]). In the same step also sql_mode can be set by the application so we can use the defaults on server side in the future.
• Apache and PHP are configured to support UTF-8.
• After this step init_connect, character_set_server and character_set_database can be changed to utf8mb4 on the Server and --skip-character-set-client-handshake can be removed at the same time [ Lit. ]

Convert Tables to utf8mb4

First we checked and converted the default Character Set of the Schemata/Databases:

SQL> SELECT schema_name, default_character_set_name, default_collation_name FROM information_schema.schemata WHERE schema_name NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') ; +-------------+----------------------------+------------------------+ | schema_name | default_character_set_name | default_collation_name | +-------------+----------------------------+------------------------+ | focmm | latin1 | latin1_swedish_ci | | test | latin1 | latin1_swedish_ci | | foodmart | latin1 | latin1_swedish_ci | | erp | latin1 | latin1_swedish_ci | | world | latin1 | latin1_swedish_ci | +-------------+----------------------------+------------------------+

Converting the Schemata is done as follows:

SQL> ALTER DATABASE ... DEFAULT CHARACTER SET utf8mb4;

which is a fast operation.

To convert the tables there are many different possibilities we considered:

• The easy one: ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4;
• The possibly faster one: dump/restore with sed 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8mb4/'
• The possibly fastest one: drop Secondary Indexes, dump/restore with sed, create Secondary Indexes again (mysqlpump).
• The automated one: With Perconas pt-online-schema-change [ Lit. ] or Facebooks OnlineSchemaChange OSC [ Lit. ]
• The most elegant but not supported one: Master/Slave Replication.

Already with converting the first table we run into a problem:

SQL> ALTER TABLE test CONVERT TO CHARACTER SET utf8mb4; ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

This table had a Primary Key of a length of more than 255 bytes and this is not possible with the old InnoDB Antelope File Format:

SQL> SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'innodb_file_format%' OR Variable_name LIKE 'innodb_large%'; +--------------------------+----------+ | Variable_name | Value | +--------------------------+----------+ | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_large_prefix | OFF | +--------------------------+----------+

So we have to find out first, which tables are still in old Antelope File Format:

SQL> SELECT table_schema , CASE WHEN row_format = 'Dynamic' THEN 'Barracuda' WHEN row_format = 'Compressed' THEN 'Barracuda' WHEN row_format = 'Compact' THEN 'Antelope' WHEN row_format = 'Redundant' THEN 'Antelope' END AS 'file_format' , COUNT(*) FROM information_schema.tables WHERE engine = 'InnoDB' AND table_schema NOT IN ('information_schema', 'sys', 'mysql') GROUP BY table_schema, file_format ; +--------------+-------------+----------+ | table_schema | file_format | count(*) | +--------------+-------------+----------+ | foodmart | Barracuda | 23 | | test | Barracuda | 1 | | world | Antelope | 2 | | world | Barracuda | 1 | +--------------+-------------+----------+

Then we could covert the table correctly forcing the Barracuda File format:

SQL> ALTER TABLE test CONVERT TO CHARACTER SET utf8mb4, ROW_FORMAT=dynamic;
Testing of new Character Set

The last but most important step is to test the changes. Here we recommend to do as many as possible different tests:

• MySQL CLI: mysql
• phpMyAdmin
• MySQL Workbench
• Other GUIs
• Your Application

Especially test careful data with umlauts (öäüÄÖÜß), foreign Characters (Turkish, Cyrillic, CJK Characters) and Emojis. Good Candidates are: Lastname, City or free text fields like Comment.

Some aid you also can get from here:

• My character encoding seems to be wrong. How can I fix it?
• Pile of Poo Emoji

MySQL Pump

mysqlpump — A Database Backup Program

This utility is currently not available for MariaDB. If works for MySQL 5.7 and newer and does NOT support MySQL 5.6. So we cannot use it for the Upgrade Process from MySQL 5.6 to 5.7. Newest MySQL Releases contain Bug fixes and even new Features in mysqlpump so we can assume it is still supported and actively maintained. Recent releases contain some fixes for trivial bugs so we can assume mysqlpump is not widely used yet and not as mature yet as mysqldump. An alternative product would be MyDumper from Domas@Facebook (Lauchpad, GitHub).

Interesting features are:

• Parallel dumping of databases.
• Secondary Index restore separated from Table Restore.

Missing features are:

• Does not support MariaDB and MySQL 5.6 and earlier.
• No binary log position is stored with backup (--master-data) but only GTID? So we have to use/enable GTID based Replication.

First test:

# mysqlpump --user=root --all-databases --single-transaction --triggers --routines --events --hex-blob > /tmp/mysqlpump_backup.sql Dump progress: 1/4 tables, 0/87 rows Dump progress: 41/46 tables, 789469/6956116 rows Dump progress: 41/46 tables, 1608219/6956116 rows Dump progress: 45/46 tables, 2358412/6956116 rows Dump progress: 45/46 tables, 3437912/6956116 rows Dump progress: 45/46 tables, 4493662/6956116 rows Dump progress: 45/46 tables, 5177662/6956116 rows Dump progress: 45/46 tables, 5568662/6956116 rows Dump progress: 45/46 tables, 6216162/6956116 rows Dump progress: 45/46 tables, 6716662/6956116 rows Dump completed in 9860 milliseconds ALTER TABLE `foodmart`.`account` ADD KEY `i_account_parent` (`account_parent_id`); ALTER TABLE `foodmart`.`category` ADD KEY `i_category_parent` (`category_parent_id`); ALTER TABLE `foodmart`.`employee` ADD KEY `i_employee_department_id` (`department_id`); ALTER TABLE `foodmart`.`employee` ADD KEY `i_employee_store_id` (`store_id`); ALTER TABLE `foodmart`.`employee` ADD KEY `i_employee_supervisor_id` (`supervisor_id`); ALTER TABLE `foodmart`.`expense_fact` ADD KEY `i_expense_store_id` (`store_id`); ALTER TABLE `foodmart`.`expense_fact` ADD KEY `i_expense_account_id` (`account_id`); ALTER TABLE `foodmart`.`expense_fact` ADD KEY `i_expense_time_id` (`time_id`);

Test against MySQL 5.6:

Server version is not compatible. Server version should be 5.7.8 or above.
MySQL Master/Slave Replication for Character Set conversion

Replication of Columns Having Different Data Types should work since MySQL 5.1.21. General rule when Statement Based Replication is used: If the query would work executed directly on the Slave it should also work on the Slave with Replication! So utf8mb4 on the Slave should also be possible with SBR. In MySQL 5.5.3 Attribute Promotion and Attribute Demotion was introduced with LOSSY and NON_LOSSY slave_type_conversions. But: Replication between columns using different character sets is not supported!

Some tests:

MasterSQL> INSERT INTO test VALUES (NULL, 'öäüÄÖÜß', NULL); MasterSQL> SELECT * FROM test; +----+----------------+---------------------+ | id | data | ts | +----+----------------+---------------------+ | 1 | öäüÄÖÜß | 2019-08-15 19:48:48 | | 2 | öäüÄÖÜß | 2019-08-15 19:50:00 | | 3 | öäüÄÖÜß | 2019-08-15 19:52:29 | +----+----------------+---------------------+ SlaveSQL> SHOW SLAVE STATUS\G ... Last_SQL_Errno: 1677 Last_SQL_Error: Column 1 of table 'test.test' cannot be converted from type 'varchar(255(bytes))' to type 'varchar(1020(bytes) utf8mb4)' ... SlaveSQL> SELECT * FROM test; +----+----------------+---------------------+ | id | data | ts | +----+----------------+---------------------+ | 1 | öäüÄÖÜß | 2019-08-15 19:48:48 | | 2 | öäüÄÖÜß | 2019-08-15 19:50:00 | +----+----------------+---------------------+

See also: Why is varchar(255) not varchar(255)? And:

MasterSQL> SET SESSION binlog_format=STATEMENT; MasterSQL> INSERT INTO test VALUES (NULL, 'öäüÄÖÜß', NULL); SlaveSQL> SELECT * FROM test; +----+----------------+---------------------+ | id | data | ts | +----+----------------+---------------------+ | 1 | öäüÄÖÜß | 2019-08-15 19:48:48 | | 2 | öäüÄÖÜß | 2019-08-15 19:50:00 | | 4 | öäüÄÖÜß | 2019-08-15 19:54:16 | +----+----------------+---------------------+
MySQL Shell, mysqlsh Upgrade Checker Utility

Since MySQL 5.7 we have a new MySQL Utility, the MySQL Shell (mysqlsh). The Upgrade Checker Utility in MySQL Shell will replace mysql_upgrade in MySQL 8.0. So we wanted to know if it is of any use for converting Character Set to utf8mb4? You can use the Upgrade Checker Utility to check MySQL 5.7 server instances for compatibility errors and issues for upgrading. But: The Upgrade Checker Utility does not support checking MySQL Server instances 5.6 an earlier. MySQL Shell 8.0 is highly recommended for use with MySQL Server 8.0 and 5.7. Please upgrade to MySQL Shell 8.0. [ Lit. ]

# ./mysqlsh root@localhost:3319 JS > util.checkForServerUpgrade() The MySQL server at localhost:3319, version 5.6.42-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 8.0.17... Util.checkForServerUpgrade: Detected MySQL server version is 5.6.42, but this tool requires server to be at least at version 5.7 (LogicError) JS > util.checkForServerUpgrade('root@127.0.0.1:3309') The MySQL server at localhost:3309, version 5.7.26-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 8.0.17... ... 2) Usage of db objects with names conflicting with reserved keywords in 8.0 No issues found 3) Usage of utf8mb3 charset Warning: The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support. More information: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html test.test.data - column's default character set: utf8 4) Table names in the mysql schema conflicting with new tables in 8.0 No issues found ... Errors: 0 Warnings: 1 Notices: 0 No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading. JS > util.checkForServerUpgrade('root@127.0.0.1:3309', {'targetVersion':'5.7.27'}) The MySQL server at 127.0.0.1:3309, version 5.7.26-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 5.7.27... Util.checkForServerUpgrade: This tool supports checking upgrade to MySQL server versions 8.0.11 to 8.0.17 (LogicError)

Because latin1 tables are still supported in MySQL 8.0 the Upgrade Checker Utility does not complain or do anything for latin1 tables. So it is not of any use for the utf8mb4 conversion task except you already have some old utf8(mb3) tables.

Taxonomy upgrade extras: character setconversionupgradelatin1utf8utf8mb4replicationmysqlshmysqlpump

FromDual has the pleasure to announce the release of the new version 0.9.2 of its popular FromDual Ops Center for MariaDB and MySQL focmm.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to manage MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center for MariaDB and MySQL (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center 0.9.2

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.9.x to 0.9.2

Upgrade from 0.9.x to 0.9.2 should happen automatically. Please do a backup of your Ops Center Instance before you upgrade! Please also check Upgrading.

Changes in Ops Center 0.9.2 Instance

• Replication: Mariadb 10.4 problem with mysql.user table fixed.
• Restore: Clean-up work done.
• Restore: Point-in-Time-Recovery is implemented.
• Operations: Binary Log state added.

Security

• Potential XSS hole fixed.
• Bootstrap CSS updated to 4.3.1

General

• Newest MyEnv Library added.
• Function array_pop replaced by array_shift.
• Tests against MariaDB 10.4 were successful. MariaDB 10.4 is officially supported now.

Build and Packaging

• Dependency bug in Debian control file fixed.

Taxonomy upgrade extras: OperationsreleaseBackupfailoverRestoreFromDual Ops Centerops centerfocmm

FromDual has the pleasure to announce the release of the new version 0.9.2 of its popular FromDual Ops Center for MariaDB and MySQL focmm.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to manage MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center for MariaDB and MySQL (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center 0.9.2

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.9.x to 0.9.2

Upgrade from 0.9.x to 0.9.2 should happen automatically. Please do a backup of your Ops Center Instance before you upgrade! Please also check Upgrading.

Changes in Ops Center 0.9.2 Instance

• Replication: Mariadb 10.4 problem with mysql.user table fixed.
• Restore: Clean-up work done.
• Restore: Point-in-Time-Recovery is implemented.
• Operations: Binary Log state added.

Security

• Potential XSS hole fixed.
• Bootstrap CSS updated to 4.3.1

General

• Newest MyEnv Library added.
• Function array_pop replaced by array_shift.
• Tests against MariaDB 10.4 were successful. MariaDB 10.4 is officially supported now.

Build and Packaging

• Dependency bug in Debian control file fixed.

Taxonomy upgrade extras: OperationsreleaseBackupfailoverRestoreFromDual Ops Centerops center

FromDual has the pleasure to announce the release of the new version 0.9.2 of its popular FromDual Ops Center for MariaDB and MySQL focmm.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to manage MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center for MariaDB and MySQL (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center 0.9.2

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.9.x to 0.9.2

Upgrade from 0.9.x to 0.9.2 should happen automatically. Please do a backup of your Ops Center Instance before you upgrade! Please also check Upgrading.

Changes in Ops Center 0.9.2 Instance

• Replication: Mariadb 10.4 problem with mysql.user table fixed.
• Restore: Clean-up work done.
• Restore: Point-in-Time-Recovery is implemented.
• Operations: Binary Log state added.

Security

• Potential XSS hole fixed.
• Bootstrap CSS updated to 4.3.1

General

• Newest MyEnv Library added.
• Function array_pop replaced by array_shift.
• Tests against MariaDB 10.4 were successful. MariaDB 10.4 is officially supported now.

Build and Packaging

• Dependency bug in Debian control file fixed.

Taxonomy upgrade extras: OperationsreleaseBackupfailoverRestoreFromDual Ops Centerops center

FromDual has the pleasure to announce the release of the new version 2.2.1 of its popular Backup and Recovery Manager for MariaDB and MySQL (brman).

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is describe in FromDual Backup and Recovery Manager (brman) installation guide.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to the FromDual Bugtracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Upgrade from 2.x to 2.2.1 shell> cd ${HOME}/product shell> tar xf /download/brman-2.2.1.tar.gz shell> rm -f brman shell> ln -s brman-2.2.1 brman
Changes in FromDual Backup and Recovery Manager 2.2.1

This release is a new minor release. It contains only bug fixes. We have tried to maintain backward-compatibility with the 1.2, 2.0 and 2.1 release series. But you should test the new release seriously!

You can verify your current FromDual Backup Manager version with the following command:

shell> fromdual_bman --version shell> bman --version shell> rman --version
General

• No changes.

FromDual Backup Manager

• Workaround for MariaDB binlog purge problem added do binlog backup.

FromDual Recovery Manager

• PiTR did not find binary logs in case they were from a foreign instance.

FromDual brman Catalog

• No changes.

Subscriptions for commercial use of FromDual Backup and Recovery Manager you can get from from us.

Taxonomy upgrade extras: BackupRestoreRecoverypitrbrmanreleasebmanrmanFromDual Backup and Recovery Manager