Showing entries 1 to 10 of 72
10 Older Entries »
Displaying posts with tag: tutorial (reset)
Generating a mysqldump to import into Google Cloud SQL

This tutorial is for you that is trying to import your current database into a Google Cloud SQL instance, replica, that will be setup for replication purposes.

According to the documentation, you will need to run:

mysqldump \
--databases [DBS] \
--hex-blob --skip-triggers --master-data=1 \
--order-by-primary --compact --no-autocommit \
--default-character-set=utf8 --ignore-table [VIEW] \
--single-transaction --set-gtid-purged=on | gzip | \
gsutil cp - gs://[BUCKET]/[PATH_TO_DUMP]

The mysqldump parameters are:

  • -h the hostname or IPV4 address of the primary should replace [MASTER_IP]
  • -P the port or the primary server, usually …
[Read more]
Configuring GTID and binary logging

This tutorial demands a service restart since some flags here presented can not be dynamically changed

What is GTID and why do I need it? Directly from the MySQL documentation (excerpt taken as is with different jargons than used here, for master/slave we are using primary/replica):

A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (the master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication topology.

GTID assignment distinguishes between client transactions, which are committed on the master, and replicated transactions, which are reproduced on a slave. When a client transaction is committed …

[Read more]
How to setup a Replication User


A replication user is necessary to set up the relationship Primary/Replica. This is a short step but it needs a bit more of attention.

From the MySQL 5.7 documentation (highlights are my own):

Although you do not have to create an account specifically for replication, you should be aware that the replication user name and password are stored in plain text in the master info repository file or table (see Section, “Slave Status Logs”). Therefore, you may want to create a separate account that has privileges only for the replication process, to minimize the possibility of compromise to other accounts.

The following command specifically will allow replication from all databases and tables connecting from all hosts. For security reasons you may want to limit access to replication only to the IP address of the server doing the …

[Read more]
Hibernate database catalog multitenancy

Introduction As I explained in this article, multitenancy is an architectural pattern which allows you to isolate customers even if they are using the same hardware or software components. There are multiple ways you can achieve multitenancy, and in this article, we are going to see how you can implement a multitenancy architecture using the … Continue reading Hibernate database catalog multitenancy →

The post Hibernate database catalog multitenancy appeared first on Vlad Mihalcea.

How to reset your `root` password on your MySQL server

You don’t need this tutorial if you have access to the root user or another one with SUPER and GRANT privileges.

The following instructions works for MySQL 5.7. You will need to stop the MySQL server and start it with mysqld_safe with the option skip-grant-tables:

sudo service mysql stop
sudo mysqld_safe --skip-grant-tables &
mysql -u root mysql

If you get an error on start, chances are there is no folder created for the mysqld_safe executable to run, on my tests I was able to solve by doing:

sudo mkdir /var/run/mysqld
sudo chown -R mysql:mysql /var/run/mysqld

And then trying to start the mysqld_safe process again.

After this, the MySQL console will pop up, and you need to set up a new password for root. The second line is necessary due to a MySQL bug # …

[Read more]
The best way to use SQL functions in JPQL or Criteria API queries with JPA and Hibernate

Introduction When executing an entity query (e.g. JPQL, HQL or Criteria API), you can use any SQL function without having to register it as long as the function is passed directly to the WHERE clause of the underlying SQL statement. However, if the SQL function is used in the SELECT clause, and Hibernate has not … Continue reading The best way to use SQL functions in JPQL or Criteria API queries with JPA and Hibernate →

The post The best way to use SQL functions in JPQL or Criteria API queries with JPA and Hibernate appeared first on Vlad Mihalcea.

How to use MySQL 8.0.4 with a GUI

If you want to have a look on what is about to come in the new version of the popular database and is used to Syntax Highlighting you don’t need to be chained to the Terminal.

Some of you may use tools like MySQL Workbench or Sequel Pro (as of the release of this post both tools had the following error occurring), and even if you are using the Terminal (if you are using an old version of mysql​, like 5.7) you may encounter this error:

Unable to connect to host, or the request timed out.

Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).

MySQL said: Authentication plugin ‘caching_sha2_password’ cannot be loaded: …

[Read more]
9 High-Performance Tips when using MySQL with JPA and Hibernate

Introduction Although there is an SQL Standard, every relational database is ultimately unique, and you need to adjust your data access layer so that you get the most out of the relational database in use. In this article, we are going to see what you can do to boost up performance when using MySQL with … Continue reading 9 High-Performance Tips when using MySQL with JPA and Hibernate →

How to store date, time, and timestamps in UTC time zone with JDBC and Hibernate

Introduction Dealing with time zones is always challenging. As a rule of thumb, it’s much easier if all date/time values are stored in the UTC format, and, if necessary, dealing with time zone conversations in the UI only. This article is going to demonstrate how you can accomplish this task with JDBC and the awesome … Continue reading How to store date, time, and timestamps in UTC time zone with JDBC and Hibernate →

Converting comma separated fields to MySQL JSON – a case study

This post is a case study of a job I had to do in a legacy application, it doesn’t mean it will apply to you, but it might.

This is a table of contents:

[Read more]
Showing entries 1 to 10 of 72
10 Older Entries »