In my blog MySQL 8.0 Collations: Migrating from older collations I showed a query that could identify the values that might break a unique constraint when migrate your data. That query was not very efficient due to the self join of the converted values.…

Migrating a MySQL database usually requires only few simple steps, but can take quite some time, depending on the amount of data you would like to migrate.

The following steps will guide through how to export the MySQL database from the old server, secure it, copy it to the new server, import it successfully and make sure the data is there.

Exporting MySQL database to a dump file

Oracle provides a utility named mysqldump which allows to easily export the database structure and data to an SQL dump file. Use the following command:

mysqldump -u root -p --opt [database name] > [database name].sql

Few notes:

• We’re using the –single-transaction flag to avoid a database lock while exporting the data. It will allow you to continue updating data in your …

Dear MySQL Users,

MySQL Connector/J 5.1.45, a maintenance release of the production 5.1
branch has been released. Connector/J is the Type-IV pure-Java JDBC
driver for MySQL.

MySQL Connector Java is available in source and binary form from the
Connector/J download pages at
http://dev.mysql.com/downloads/connector/j/5.1.html
and mirror sites as well as Maven-2 repositories.

MySQL Connector Java (Commercial) is already available for download on the
My Oracle Support (MOS) website. This release will shortly be available on
eDelivery (OSDC).

As always, we recommend that you check the “CHANGES” file in the
download archive to be aware of changes in behavior that might affect
your application.

MySQL Connector/J 5.1.45 includes the following general bug fixes …

Percona announces the release of Percona Monitoring and Management 1.5.1. This release contains fixes for bugs found after Percona Monitoring and Management 1.5.0 was released.

Bug fixes

• PMM-1771: When upgrading PMM to 1.5.0 using Docker commands, PMM System Summary, PMM Add Instance, PMM Query Analytics dashboards were not available.
• PMM-1761: The PMM Query Analytics dashboard did not display the list of hosts correctly.
• …

Percona announces the release of Percona XtraBackup 2.3.10 on November 29, 2017. Downloads are available from our download site or Percona Software Repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, it drives down backup costs while providing unique features for MySQL backups.

This release is the current GA …

Percona announces the GA release of Percona XtraBackup 2.4.9 on November 29, 2017. You can download it from our download site and apt and yum repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, it drives down …

In my preview blog posts, I have written about this exam topics and this blog post will cover remaining topics.

Let start with it,

MySQL Security

This MySQL Security section covers general MySQL server inbuild option plus enterprise tools offered by MySQL enterprise edition.

• Execute MySQL Authorization and Privilege Management

Reference doc: MySQL privilege and access control

• Manage MySQL Password Policies

Explore for password_validation plugin and Password Expiration policies for MySQL USER.

• Describe MySQL Enterprise …

SUDOKU is an interesting problem.   Using SQL to solve this problem is not anything magical.



An interesting twitter post from

https://twitter.com/VadimTk/status/916734787557064704

Thanks to  Vadim Tkachenko


The following SQL with MySQL 8.0.3 RC1, the New Feature "Recursive Common Table Expression/CTE" enables the easy way of this SUDOKU solver.


select @myproblem:='..41..2.3........12.....8..82.6.43.....8.9.....67.2.48..5.....64........3.7..69..';

WITH RECURSIVE
        my19(n) AS (
        SELECT 1 AS n
        UNION ALL
        SELECT 1+n FROM my19 WHERE n<9
)
select substr(@myproblem,(n-1)*9+1,9) as sud from my19;

…

In this blog post I’ll look at the performance of Percona XtraDB Cluster on AWS using different service instances, and recommend some best practices for maximizing performance.

You can use Percona XtraDB Cluster in AWS environments. We often get questions about how best to deploy it, and how to optimize both performance and spend when doing so. I decided to look into it with some benchmark testing.

For these benchmark tests, I used the following configuration:

• Region:
  • Availability zones: US East – 1, zones: b, c, d
  • Sysbench 1.0.8
  • ProxySQL 1.4.3
  • 10 tables, 40mln records – ~95GB dataset
  • Percona XtraDB Cluster 5.7.18
  • Amazon Linux AMI

We …

Percona announces the release of Percona Monitoring and Management 1.5.0 on November 28, 2017.

This release focuses on the following features:

• Enhanced support for MySQL on Amazon RDS and Amazon Aurora – Dedicated Amazon Aurora dashboard offers maximum visibility into key database characteristics, eliminating the need for additional monitoring nodes.  We renamed Amazon RDS OS Metrics to Amazon RDS / Aurora MySQL Metrics
• Simpler configuration – Percona Monitoring and Management now offers easier configuration of key Amazon RDS and Amazon Aurora settings via a web interface
• One-click data collection – One button retrieves vital information on server performance …