Two important MySQL 8.0 Shell Utilities we use regularly are the upgrade checker utility and JSON import utility . The upgrade checker utility simplifies the pre-upgrade compatibility audit (whether MySQL server instances are ready for upgrade), We have blogged about MySQL upgrade checker utility here . The upgrade checker utility does not support checking MySQL Server instances at a version earlier than MySQL 5.7. From MySQL Shell 8.0.16, the upgrade checker utility can check the configuration file (my.cnf or my.ini) for the server instance. The utility checks for any system variables that are defined in the configuration file but have been removed in the target MySQL Server release, and also for any system variables that are …[Read more]
10 Older Entries »
What is new with MySQL 8.0 Delayed Replication ?
Delayed Replication – You can deliberately execute transactions later than the master by a specific duration of time , Why you do that and for what ? Consider this, Accidentally someone did a wrong UPDATE / DELETE in the master and the transaction is committed, Now how can DBA rollback the database system to the last known good condition ? This is when we benefit from MySQL delayed slave replication investment. The default replication delay in MySQL is “0” seconds, To delay the slave by N seconds use the CHANGE MASTER TO MASTER_DELAY = N, The transactions received from the master is not executed until N seconds later than it’s commit on the immediate master. We have blogged here how to setup …[Read more]
MySQL has introduced the concept of functional index in MySQL 8.0.13. It is one of the much needed feature for query optimisation , we have seen about histogram in my last blog. Let us explore the functional index and its use cases.
For the below explanation, I have used a production scenario which has 16 core cpu, 32GB RAM and with MySQL version 8.0.16(Latest at the time of writing).
MySQL do support indexing on columns or prefixes of column values (length).
mysql>show create table app_user\G *************************** 1. row *************************** Table: app_user Create Table: CREATE TABLE `app_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ad_id` int(11) DEFAULT NULL, `source` varchar(32) DEFAULT NULL, `medium` varchar(32) DEFAULT NULL, `campaign` varchar(32) DEFAULT NULL, `timestamp` …[Read more]
Quickly configure replication using DBdeployer [SandBox]
We might have different scenarios when we need a quick setup of replication either between the same version of MySQL (Like 8.0 --> 8.0) or between the different version of MySQL (Like 5.7 --> 8.0) to perform some testings.
Here in this blog post, I will explain how we can create our replication lab setup quickly using the virtual machine and DBdeployer tool.
Let's see, how to create replication between the same version and different version of MySQL using DBdeployer step by step. Create CentOS VM Please find my this blog post link where you will get instruction about, …
MySQL supports different types of Indexes. They depend on the storage engine and on the type of data. This is the list of supported indexes:
- B-trees (the most common)
- R-trees (for spatial data)
- Hashes (for Memory engine)
- Inverted (for InnoDB Full Text)
My recommendation are valid for InnoDB storage engine. I won’t talk about MyISAM. There are some best practices to follow when designing your tables. These are the 3 most important:…[Read more]
MySQL 8.0 introduces many new features. We will have a look at the exciting histogram feature in MySQL 8.0
What is Histogram?
In General, a histogram is an accurate representation of the distribution of numerical data. In MySQL, It will be useful to collect data distribution for a specific column.
What problem it solves?
In general DB Optimizer gives us the best execution plan, But the stats make the execution plan better and better .The data distribution of values in columns can make good impact in optimiser in case of column with less distinct values.
We will see an example of how it helps optimizer in some cases.
I have used a production case. MySQL version is 8.0.15 installed in ubuntu 18.04 (32GB RAM,8 core) with optimal configuration. Let us try to optimise a …[Read more]
- A record for mysqld service startup and shutdown.
- Diagnostic messages such as errors, warnings, and notes during mysqld server startup, running or shut down.
- A stack trace if mysqld exits abnormally.
- mysqld_safe writes once it finds mysqld exists abnormally and mysqld_safe invoke/start mysqld.
Error Log Component Configuration MySQL 8.0 uses the MySQL error log component architecture that performs log event filtering and writing. These error log components can be configured using the …[Read more]
Do you know that it’s possible to get information from the CPUs of your MySQL Server from SQL ?
If you enable the status for the
INNODB_METRICS table in
INFORMATION_SCHEMA, you will be able to query CPU
First, check if those status are enabled:
MySQL> SELECT name, subsystem, status…[Read more]
FROM INFORMATION_SCHEMA.INNODB_METRICS where NAME like 'cpu%';
| name | subsystem | status |
| cpu_utime_abs | cpu | disabled |
| cpu_stime_abs | cpu | disabled |
| cpu_utime_pct | cpu | disabled |
| cpu_stime_pct | cpu | disabled |
| cpu_n | cpu | disabled |
5 rows in set (0.00 sec)
If you just migrated to MySQL 8.0, you may have seen that the default authentication plugin has been changed to a more secure one: caching_sha2_password and I’ve already written some articles about it.
Now let’s discover how Perl users can deal with MySQL 8.0.
The driver to use MySQL with Perl is perl-DBD-MySQL. MySQL 8.0 is supported but the new authentication plugin might not be. This depends of the mysql library linked during compilation of the module.
problem connecting to MySQL 8.0
The error you may encounter is the following:
DBI connect('host=localhost','fred',...) failed: Authentication plugin[Read more]
'caching_sha2_password' cannot be loaded: …
MySQL 8.0 upgrade checklist
Recently one of our customers in Fintech. business (among the largest one in the Asia) wanted to upgrade from MySQL 5.7 to MySQL. 8.0. and they approached us for a safest and durable MySQL upgrade strategy, roadmap and execution. In Fintech. business every transaction needs to durable from statutory regulatory compliance perspective and we at MinervaDB never wanted to go for unplanned / easy in-place MySQL 8.0 upgrade method here without proper pre-migration audit, We wanted to list down in detail what are the possible scenarios this MySQL 8.0 upgrade will fail and the compatibility issues between MySQL 5.7 and MySQL 8.0. Thankfully Upgrade Checker utility that comes with MySQL Shell 8.0 can be executed against MySQL 5.7 server to confirm upgrade readiness, We have written a blog on MySQL Shell 8.0 Upgrade Checker …[Read more]
10 Older Entries »