Showing entries 1 to 10 of 236
10 Older Entries »
Displaying posts with tag: MySQL 8.0 (reset)
MySQL: Check who’s trying to access data they should not

To illustrate how easy it’s to see who’s trying to access data they have not been granted for, we will first create a schema with two tables:

mysql> create database mydata;
mysql> use mydata
mysql> create table table1 (id int auto_increment primary key, 
              name varchar(20), something varchar(20));
mysql> create table table2 (id int auto_increment primary key, 
              name varchar(20), something varchar(20));

Now, let’s create a user :

mysql> create user myuser identified by 'mypassword';

And as it’s always good to talk about SQL ROLES, let’s define 3 roles for our user:

  • myrole1: user has access to both tables in their entirety, reads and writes
  • myrole2: user has access only to `table2`, reads and writes
  • myrole3: user has only access to the column `name`of `table1` …
[Read more]
MySQL Clone Plugin Speed Test

In my previous blog, I have explained how the MySQL clone plugin works internally. In this blog, I am going to do a comparison of  Backup and Recovery speed of MySQL clone plugin with other available mysql open source backup tools.

Below tools are used for speed comparison of Backup and Recovery,

  1. Clone-Plugin
  2. Xtrabackup
  3. mysqldump
  4. mydumper with myloader
  5. mysqlpump

Test …

[Read more]
Using MySQL Community Repository with OL 8/RHEL 8/CentOS 8

MySQL 8.0 is now part of RedHat Enterprise 8 and other distros based on it like CentOS and Oracle Linux.. This is a very good thing !

However if for any reason you want to use the latest version of MySQL from the Community Repository, you may encounter some frustration if you are not familiar with the new way the package manager works.

Let’s start by verifying our system:

LSB Version:    :core-4.1-amd64:core-4.1-noarch
Distributor ID:    OracleServer
Description:    Oracle Linux Server release 8.0
Release:    8.0
Codename:    n/a

We can see that we are on Oracle Linux 8.0. So now let’s try to install MySQL Server:

[root@localhost ~]# dnf install mysql-server
Last metadata expiration check: 0:08:15 ago on Sat 02 Nov 2019 09:54:07 AM UTC.
Dependencies resolved.
============================================================================================
  Package                 Arch   Version …
[Read more]
Setup 2 MySQL InnoDB Clusters on 2 DCs and link them for DR

This article is an update of a previous post explaining how to setup a second cluster on a second data center to be used as disaster recovery (or to run some off site queries, like long reports, etc..).

This new article covers also the CLONE plugin. Before you ask, CLONE plugin and Replication Channel Based Filters are only available in MySQL 8.0 ! It’s time to upgrade, MySQL 8 is Great !

Also, for DR only, a single MySQL instance acting as asynchronous replica is enough. But if for any reason you want to also have a HA cluster in the second data …

[Read more]
MySQL & InnoDB Disk Space

Yesterday, Bhuvanesh published an article about how to verify the difference between allocated diskspace for a tablespace and the the data in it.

I commented with an old post explaining how to get some similar info only using SQL in case you don’t have filesystem access.

And finally, my friend Bill Karwin, commented how this info is not always accurate. Which, of course, I agree with.

This is why, I checked …

[Read more]
MySQL Server 8.0.18: Thanks for the Contributions

Tweet

In my blog series about external contributions to MySQL 8 we have reached version 8.0.18 which was released Monday 14 October 2019. Again the community has contributed to make MySQL better. Thank you.

The contributions to MySQL 8.0.18 includes several patches from Facebook as well as patches from Gillian Gunson, Przemysław Skibiński (Percona), Daniel Black, and Satya Bodapati (also Percona). The contributions are:

  • MySQL now provides more control over the use of compression to minimize the number of bytes sent over connections to the server. Previously, a given connection was either uncompressed or used the zlib compression algorithm. Now, it is also possible to use the zstd algorithm (zstd 1.3), and to …
[Read more]
MySQL load data infile made faster .

Loading any large file into MySQL server using the LOAD DATA INFILE is a time consuming process , because it is single threaded and it is a single transaction too. But with modern hardwares system resource is not a bottle neck. At Mydbops we focus on improving the efficiency of process as we value performance more. MySQL introduced the parallel load data operations in its latest minor release MySQL 8.0.17 . I had the curiosity to test this feature and wanted to know, how it can improve the data loading comparing to the existing single threaded method  . Through this blog I am going to  compare the both methods .

Remember you can use the parallel data loading utility only via MySQL Shell .

Internal Work Flow :

This section describes the …

[Read more]
MySQL 8.0 Clone Plugin and its internal process.

MySQL 8 has recently released clone plugin which makes DBA’s task of rebuilding the DB servers more easy.

  • Cloning is a process of creating an exact copy of the original. In technical terms cloning alias to (Backup + Recovery), MySQL database cloning requires a sequence of actions to be performed manually or in a scripted fashion with and without the tools involved.
  • Cloning is the first step when you want to configure the replication slave or Joining a new server to the InnoDB cluster. There was no native support for auto provisioning earlier. Percona XtraDB Cluster (MySQL + Galera Cluster) does cloning using xtrabackup tool by default when a new node joins the cluster.
  • Now MySQL simplified this task, In this post, We will see how to clone the database using clone plugin and its internals.
[Read more]
How to integrate ProxySQL in MySQL InnoDB Cluster

MySQL InnoDB Cluster is the most easy and integrated High Availability solution for MySQL.

The solution is composed of:

  • MySQL Server
  • MySQL Group Replication Plugin
  • MySQL Clone Plugin
  • MySQL Router
  • MySQL Shell

All those components are developed and tested together to provide the easiest and best experience to the MySQL users.

As the MySQL Router is a TCP Level 4 router (like HA Proxy), some users requiring a more “intelligent” proxy having other extra features like caching, read/write splitting in relation with the user or SQL, firewall, … may be interested in using ProxySQL… and this is a good choice !

ProxySQL / MySQL Router in OSI Model

However, even is ProxySQL supports MysQL Group Replication, for …

[Read more]
Create MySQL Test Instance with Oracle Cloud Free Tier

Tweet

Oracle announced this week at Oracle OpenWorld that it has introduced a new cloud offer called Oracle Cloud Free Tier. As the name suggest, it allows you to run a few limited instances in Oracle Cloud for free. I will in this blog show how you can use the free tier to setup a MySQL test instance.

Tip

If you want to read more about Oracle Cloud Free Tier see https://www.oracle.com/cloud/free/ and the FAQ at https://www.oracle.com/cloud/free/faq.html.

The first step is to sign up for the cloud service which you do by opening …

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