Ran into an interesting situation trying to configure a MySQL JDBC driver to connect over TLS (though the driver may call it SSL, TLS is the name for more recent versions of the protocol).

The error I was getting was pretty generic:

Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

With the relevant parts of the stacktrace, also being non helpful:

at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835)
        at com.mysql.cj.jdbc.ConnectionImpl.(ConnectionImpl.java:455)
        at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
        at …

MySQL has added a lot of functionality for Geographical Information System (GIS) data in the last two releases. This has given us better ways to save and explore GIS data. MySQL Workbench is an amazing tool that many do not realize has vastly upped its game for supporting GIS information.  But did you know you can use MySQL Workbench with OpenStreetMap to plot locations?

1. Get your Longitude and Latitude
These, believe it or not, is the hard part.  Go to  https://www.latlong.net/ and type in the name of your location.  It will report back your longitude and your latitude.  For the example I will be using the information for the city of Justin, Texas.

The CTO has issued the decree: “We are moving to the cloud!”  Great, so now what do we do? When it comes to migrating from on-premise to the cloud, there are many factors to consider and decisions that need to be made.  First (and probably most important) on that list: managed DBaaS or setup and manage our own infrastructure?

Type of Cloud

Managed DBaaS options are great but come with some limitations.  The main two questions that should be considered here are around staff/experience and current architecture/database design.  In cases where there is a very limited database (or operations) team, a DBaaS is a great choice. Much of the operational infrastructure is already in place with general best practices in place.  However, a big tradeoff comes around flexibility. In cases where the current infrastructure is complex (for reasons right or wrong), a DBaaS is definitely the wrong choice as you will be …

It’s bad practice to provide world-writable access to critical files in Linux, though we’ve seen time and time again that this is done to conveniently share files with other users, applications, or services. But with Xtrabackup, preparing backups could go wrong if the backup configuration has world-writable file permissions.

Say you performed a backup on a MySQL instance configured with data-at-rest encryption using the keyring plugin. On the backup directory, the generated backup-my.cnf contains these instructions to load this plugin that will be used by Xtrabackup while preparing the backup:

backup-my.cnf

[mysqld]
innodb_checksum_algorithm=crc32
innodb_log_checksum_algorithm=strict_crc32
innodb_data_file_path=ibdata1:12M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=1073741824
innodb_fast_checksum=false
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_directory=./
innodb_undo_tablespaces=0 …

Most of us used to get errors like (Row not found, Duplicate row etc) on slave in Master slave replication and sometimes it is very difficult to find unsynced data and fix it while we know table name(s).
There are few recommended tools from percona to check replication integrity and fixed unsync data:
1. pt-table-checksum: performs an online replication consistency check by executing checksum queries on the master, which produces different results on replicas that are inconsistent with the master.
2. pt-table-sync: synchronizes data efficiently …

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).

Example: 

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` …

We bring you the best MySQL tutorial to learn all Basic to Advanced concepts step by step. This post covers all MySQL building blocks such as DDL, DML, DCL, and TCL. DDLs are commands to create, drop, alter, truncate, rename, comment databases, and tables. Also, DMLs are select, insert, update, delete commands to manipulate data. Moreover, you’ll also find the DCLs on this page such as grant and invoke to manage rights and permissions. After that, there are TCLs to control the transactions. So, let’s start by telling you that the father of MySQL is Michael Widenius, who named it

The post MySQL Tutorial – Learn Step by Step appeared first on Learn Programming and Software Testing.

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 …

What are things you must consider before using Galera Cluster Streaming Replication ? 

In Streaming Replication, the node breaks the transaction into fragments, then certifies and replicates them on the slaves while the transaction is still in progress. Once certified, the fragment can no longer be aborted by conflicting transactions. Additionally, Streaming Replication allows the node to process transaction write-sets greater than 2Gb. So, How do you decide whether go with Galera Cluster Streaming Replication or not ? We caution our customers before choosing Galera Cluster Streaming Replication considering several limitations and costs attached to it (explained below the limitations of Galera Cluster Streaming Replication in this post). Even you have decided to proceed with Galera Cluster Streaming Replication, We recommend you to enable it only for a session-level and then only on specific transactions.

…

Recently we had been bitten by a Systemd limitation at the “Tasks” created per-unit ie., process. This includes both the kernel threads and user-space threads, with each thread counting individually.

Am writing this blog as a reference for someone who might come across this limitation.

We have been actively working on migration DB instances, from one DC to the newly built DC .The instances on the newer DC were provisioned with the latest hardware and latest Debian OS. Below is the detailed spec of the system.

RAM             : 244G
Core             : 44Core
HardDisk.   : SSD
IOPS             : 120K
OS         …