Showing entries 11 to 20 of 280
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: MySQL 8.0 (reset)
MySQL Compressed Binary Logs

Tweet

On a busy server, the binary logs can end up being one of the largest contributors to amount of disk space used. That means higher I/O, larger backups (you are backing up your binary logs, right?), potentially more network traffic when replicas fetch the logs, and so on. In general, binary logs compress well, so it has been a long time wish for a feature that allowed you to compress the logs while MySQL are still using them. Starting from MySQL 8.0.20 that is now possible. I will take a look at the new feature in this post.

Configuration

The binary log compression feature is controlled by two variables, one for enabling the feature and …

[Read more]
GeoSpatial data handling made easy with MySQL 8.0

Human geography examines the dynamics of cultures, societies and economies, and physical geography examines the dynamics of physical landscapes and the environment.

In this blog i am going to explain what are the new Geography features available in MySQL 8.0

Major Difference in MySQL 5.7 & 8.0 Geography :

There is a big change from MySQL 5.7 to 8.0 when it comes to spatial data, it’s support, multiple spatial reference systems and geographic computations.

This means that the SRIDs of geometries actually have meaning and affect computations. In 5.7 and earlier, however, the SRIDs(spatial reference identifier) are ignored, and all computations are Cartesian.

What’s new in MySQL 8.0?

* Introduced ellipsoid computation instead of flat plane cartesian.

* Introduced multiple spatial reference …

[Read more]
Apress Blog: MySQL Performance Tuning Best Practices

Tweet

To celebrate the publishing of my new book MySQL 8 Query Performance Tuning, the Apress team invited me (thanks Jonathan and Liz) to write a post for the Apress blog. I decided to write about my top six best practices:

  • Be wary of best practices
  • Monitor
  • Work methodically
  • Consider the full stack
  • Make small, incremental changes
  • Understand the change

Yes, my first best practice is to be wary of best practices. Read why I added that and the other best practices at …

[Read more]
MySQL 8.0.20: Thanks for the Contributions

As you know, today, MySQL 8.0.20 has been released. I started this new thread category with 8.0.19 (see this post).

A late thanks to Guoji Ma, for a contribution to bug #95801 “show create table output can’t be executed” that was used as inspiration for the actual fix that was pushed in MySQL 8.0.18.

MySQL 8.0.20, includes contributions from Daniel Black, Cai Yibo, Jericho Rivera, Matti Sillanpää, Nick Pollett, Bruce Feng, Kamil Holubicky, Facebook.

Thank you all for your great contributions. MySQL is an Open Source project, GPL, and we accept contribution of course. Sometimes it’s also good to remind it

Here is the list of the contributions above:

  • Impossible WHERE for a!=a, a<a, …
[Read more]
MySQL 8.0.20: Index-Level Optimizer Hints

Tweet

MySQL introduced optimizer hints in version 5.7 and greatly extended the feature in MySQL 8. One thing that has been missing though is the ability to specify index hints using the syntax of optimizer hints. This has been improved of in MySQL 8.0.20 with the introduction of index-level optimizer hints for the FORCE and IGNORE versions of the index hints. This blog will look at the new index hint syntax.

Warning

Do not add index hints – neither using the old or new style – unless you really need them. When you add index hints, you limit the options of the optimizer which can prevent the optimizer obtaining the optimal query plan as new optimizer improvements are implemented or the data changes.

On …

[Read more]
Presentation : Group Replication in MySQL 8.0

This presentation covers about the basics of Group replication in MySQL 8.0. Group replication world on the basis of Multi Paxos ( a variant of it ). The author covers the leader election algorithm , switching the primary node and replication modes in this presentation.

Group Replication in MySQL 8.0 ( A Walk Through ) from Mydbops

Like to have this high availability MySQL solution configured on your environment. Mydbops Consultants can provide the best solution with right cost for performance.

Using OCI to install Moodle with MySQL 8.0

Let’s continue our series of using OCI to install popular Open Source programs with MySQL 8.0. After WordPress, Drupal, it’s not the Moodle’s turn!

This is a request from my Japanese colleague Machiko

We will use two different compute instances:

  • the application server (apache, PHP and Moodle)
  • the database server (MySQL 8.0)

The application server will be reachable from the Internet on port 22 (ssh) and 80 (http). The database server won’t be reachable from outside, we will have to use the application server as jumphost to install the server.

This is an overview of the proposed architecture:

Deploying 2 compute instances

The first step is to install 2 compute instances, one in the Public Subnet and one in the Private Subnet.

We first start with the application server (called myMoodle):

As the second instance will be located …

[Read more]
MySQL Lock information in MySQL Shell

Last Tuesday, it was the very first session of DB AMA, Morgan Tocker made a nice presentation of MySQL Performance_Schema and illustrated it with some nice queries to get Meta et Data Locks.

As those queries were not that simple to write or at least to remember, I thought it might be a good idea to add them to MySQL Shell, the best MySQL DBA Tool !

I’ve then added a new method to the check plugin: getLocks().

Let’t see it in action:

As you can see, this is a small extension that can offers you a better view of what’s locked per transaction.

You can find several MySQL Shell Extension directly on github: …

[Read more]
HA for our website’s database in OCI with MySQL InnoDB Cluster

In the previous post, we setup a Drupal website with MySQL as backend on a different compute instance in a different subnet.

Today we will increase the High Availability of our Database by transforming this single MySQL instance into a MySQL InnoDB Cluster!

The architecture will be the following:

We will use 2 different Availability Domains (AD), of course we could have used 3, it’s up to you.

We will install MySQL Router on the Application Server as advised, and we will modify our Drupal’s configuration to connect locally on the R/W port of the MySQL Router.

You will see that this is very easy to achieve, the most …

[Read more]
MySQL Failed Logins Locking Account

MySQL 8.0.19 introduced the ability to lock an account on a MySQL instance after too many failed logins.  This is not turned on by default, the locking period is measured in days (more below), and I would urge caution as not to 'denial of service' yourself out of your server.

Syntax CREATE USER 'foobar'@'localhost'
IDENTIFIED by 'password'
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 2;

DataAnd if you look at the mysql.user entry you will see

User_attributes: {"Password_locking": {"failed_login_attempts": 3, "password_lock_time_days": 2}}

Example$mysql -u foobar -p
Enter password:
ERROR 1045 (28000): Access denied for user 'foobar'@'localhost' (using password: YES)
$ mysql -u foobar -p
Enter password:
ERROR 1045 (28000): Access denied for user 'foobar'@'localhost' (using password: …

[Read more]
Showing entries 11 to 20 of 280
« 10 Newer Entries | 10 Older Entries »