MySQL

The Call For Papers for SCaLE 18x ends soon and I would love to have your talk on MySQL be part of the MySQL track! The track is usually on the Friday of the show!I am 'curating' the MySQL track again and would love to have you talk on MySQL.  Do you ... [More] have a story about using MySQL, some trick admin skills you would like to share, a beginners guide to X & MySQL, or a case study? Well then, please submit.What is SCaLE?  Well it is roughly 5,000 people in Pasadena, California next March 5-8 2020 at the convention center in the heart of the City.  It is the only big open source in Southern California and features multiple tracks on subjects ranging from AI to how to work remotely.  The expo hall is a few acres of the best tech and projects that you will find anywhere.Do I really have to write a paper? Well, no.  You do need to fill out a form online on your proposal that you would like to talk and make sure that you mark it for the MySQL track if it is MySQL related!  The dead line is the end of November!Fear of public speaking? Well, this is a great event if you are timid or think you might be. This is like the large user group with techies who want to hear your story, see you succeed, and want to hear you opinion.What else do I get?  Well you get a badge that says 'speaker' along with the ability to go to as many sessions as you can at the most exciting open source show.  And if you are on the MySQL track maybe I will get you some specialized MySQL swag!So if you have an idea for a talk please submit it before November 30th!Need help or want an extra set of eyes for you proposal -- then contact me! Please! I want you MySQL talk next March!All opinions expressed in this blog are those of Dave Stokes who is actually amazed to find anyone else agreeing with him [Less]

SQL UPPER function is used for converting all the characters in the source string to Uppercase characters. If any number is present in the string, then it remains unaffected. SQL UPPER() function converts a string to the upper-case. SQL upper() ... [More] Function Suppose you have the online shopping website. Customers visit the website and provide the necessary information while creating the login account. Each customer provides few compulsory information such as first name, last name, phone number, email address, and residential address. Each customer is different, so you cannot expect the similar format for all inputs. For example, you get a following entries in an SQL table. We do not see all words following the consistent pattern. It does not look good as well if you have to share the report daily to higher management for all newly enrolled customers. The SQL UPPER function converts all the letters in a string into uppercase. If you want to convert the string to lowercase, you use the LOWER function instead. Syntax SELECT UPPER (String); Parameters String: The source string whose characters have to be replaced with Uppercase characters. It can be any literal character string, variable, character expression, or any table column. Example Query 1 SELECT UPPER (‘AppDividend.Com’); Output APPDIVIDEND.COM Query 2 SELECT UPPER (‘sql123sql’); Output SQL123SQL As discussed above, numbers remain unaffected to UPPER function. Let’s apply the UPPER function to a table. Table: Employee Emp_id Emp_name City State Salary 101 Rohit Patna Bihar 30000 201 Shivam Jalandhar Punjab 20000 301 Karan Allahabad Uttar Pradesh 40000 401 Suraj Kolkata West Bengal 60000 501 Akash Vizag Andhra Pradesh 70000   Suppose we want to change the characters of the City of each employee to Uppercase, then the following query has to be written. Query 3 SELECT Emp_name, City, UPPER (City) AS UPPER_CITY from Employee; Output Emp_name City UPPER_CITY Rohit Patna PATNA Shivam Jalandhar JALANDHAR Karan Allahabad ALLAHABAD Suraj Kolkata KOLKATA Akash Vizag VIZAG   As you can see above, the name of the city is converted to Uppercase characters. MySQL UPPER() Function In MySQL, the UPPER() function converts a string to upper-case. Syntax UPPER(text) In this syntax, the text parameter can be a literal character string, variable, character string expression, or table column. SQL Server UPPER() Function The UPPER() function converts an input string into uppercase. Syntax The following shows the syntax of the UPPER() function: UPPER(string) In this syntax, the string parameter can be a literal character string, variable, character string expression, or table column. The type of the string must be implicitly convertible to VARCHAR. Otherwise, you must use the CAST() function to convert the string explicitly. SQL Server UPPER() function returns the uppercase form of the input string. Using the UPPER() function with literal strings See the following query. SELECT UPPER('appdividend') result; See the output. result APPDIVIDEND Finally, SQL Upper Function Example | MySQL And SQL Server Upper() Tutorial is over. Recommended Posts SQL UNICODE Function SQL Replicate Function SQL Left Function Example SQL Format Function SQL LTRIM Function The post SQL Upper Function Example | MySQL And SQL Server Upper() appeared first on AppDividend. [Less]

The London Open Source Database Meetup is hosting me on December 4th -RSVPand I will be talking about  New MySQL Features and a Brief Look into 2020!MySQL has made lot of progress with version 8.0 and it has become the fasted adopted new version of ... [More] MySQL in history. The new CI/CD release pattern had been delivering a lot of new features on a regular basis that you may have not noticed. There is JSON Document Validation thanks to the good people at JSON-schema.org, random passwords, hash joins, explain analyze, constraint checks, and more. So if you need to caught up this is you chance and if you are wondering what is in the near term future this is your opportunity.All opinions expressed in this blog are those of Dave Stokes who is actually amazed to find anyone else agreeing with him [Less]

Introduction While developing a Spring Boot application is rather easy, tuning the performance of a Spring Boot application is a more challenging task, as, not only it requires you to understand how the Spring framework works behind the scenes, but ... [More] you have to know what is the best way to use the underlying data access framework, like Hibernate for instance. In a previous article, I showed you how easily to optimize the performance of the Petclinic demo application. However, by default, the Petclinic Spring Boot application uses the in-memory HSQLDB database, which... Read More The post Spring Boot performance tuning appeared first on Vlad Mihalcea. [Less]

Attend this half-day event to hear why MySQL is the open source database of choice for business leaders, developers and system architects. Please RSVP here! With the official release of version 8.0, MySQL now offers SQL and NoSQL capabilities.  We ... [More] illdemonstrate how MySQL helps our customers shorten time to market, reduce IT costs, and increase revenue growth – all while providing enterprise grade security via advanced encryption authentication, firewall, and more.Takeaway tips and techniques on: Containers Securing your data - GDPR MySQL without the SQL Date and Time: Tue, November 12, 2019  9:30 AM – 1:00 PM CSTLocation: Oracle 2300 Cloud Way Austin, TX 78741  I hope to see all y'all there!All opinions expressed in this blog are those of Dave Stokes who is actually amazed to find anyone else agreeing with him [Less]

Recently I was doing some small testing by using EC2 instances on AWS and I noticed the execution time and performance highly depend on which time of the day I am running my scripts. I was using t3.xlarge instance type as I didn’t need many CPUs and ... [More] memory for my tests, but from time to time I planned to use all the resources for a short time (few minutes), and this is when I noticed the difference. First, let’s see what AWS says about T3 instances: T3 instances start in Unlimited mode by default, giving users the ability to sustain high CPU performance over any desired time frame while keeping cost as low as possible. In theory, I should not have any issues or performance differences. I have also monitored the CPU credit balance and there was no correlation between the balance and the performance at all, and because these were unlimited instances the balance should not have any impact. I have decided to start a longer sysbench test on 3 threads to see how the QPS changes over the day. As you can see, the Query Per Second could go down by almost 90%, which is a lot. It’s important to highlightthat the sysbench script should have generated a very steady workload. So what is this big difference? After checking all the graphs I found this: Stealing! A lot of stealing! Here is a good article which explains stealing very well. So probably, I have a noisy neighbor. This instance was running in N. California. I have stopped it and tried to start new instances to repeat the test but I have always gotten very similar results. There was a lot of stealing which was hurting the performance a lot, probably because that region is very popular and resources are limited. Out of curiosity, I have started two similar instances in the Stockholm region and repeated the same test and I got very steady performance as you can see here: I guess this region is not that popular or filled yet, and we can see there is a huge difference between where you start your instance. I also repeated the tests with the m5.xlarge instance type to see if it has the same behavior or not. N. California Stockholm After I changed the instance type, we can see that both regions give very similar, steady performance, but if we take a closer look: N. California Stockholm The instance in Stockholm still performs almost 5% more QPS as in N. California, and uses more CPU as well. Conclusion If you are using T2 and T3 instance types, you should monitor the CPU usage very closely because noisy neighbors can hurt your performance a lot.  If you need stable performance, T2 and T3 are not recommended but if you only need a short burst it might work but still, you have to monitor the steal. Other instance types can give you a much more stable performance but you could still see some difference between the regions. [Less]

At times, when playing with different InnoDB Clusters for testing (I usually deploy all Group Replication instances on the same host on different ports) I find myself stopping the group and doing operations on every instance (e.g. a static ... [More] reconfiguration). Or I may need to shutdown all instances at once. Scripting is the usual approach, but in addition, MySQL Shell offers a very nice (and powerful) way to integrate custom scripts into the Shell itself to manage an InnoDB Cluster. This is the purpose of MySQL Shell extensions, to create new custom reports and functions and have the flexibility to manage one or more instances at once. I found particularly practical the new plugin feature, introduced in MySQL Shell 8.0.17, that can aggregate reports and functions under the same umbrella: the plugin.As an example of the things that are possible, I have modified Rene's great example so to stop Group Replication in a shot from MySQL Shell, and it's particularly easy, check the following script. Create directory  ~/.mysqlsh/plugins/ext/idc/ Create there init.js script as follows, it will be loaded at MySQL Shell startup. // Get cluster object, only if session is createdfunction get_cluster(session, context) {  if (session) {    try {      return dba.getCluster();    } catch (err) {      throw "A session to a cluster instance is required: " + err.message    }  } else {    throw "A session must be established to execute this " + context  }}function stop_cluster() {  var cluster = get_cluster(shell.getSession(), "function");  var data = cluster.status();  var topology = data.defaultReplicaSet.topology;  var sess = shell.getSession()  var uri = sess.getUri()  var user = (uri.split('//')[1]).split('@')[0]  // iterate through members in the cluster  for (index in topology) { if (topology[index].status=="ONLINE") print("\n-----> " + topology[index].address + " is ONLINE and will be evicted from GR\n") var sess = shell.connect(user + "@" + topology[index].address) var result = sess.runSql("STOP GROUP_REPLICATION;") //print(JSON.stringify(result, null, 4))  }  // Reconnect original session  shell.connect(uri)  return;}// Creates the extension objectvar idc = shell.createExtensionObject()// Adds the desired functionality into itshell.addExtensionObjectMember(idc, "stopCluster", stop_cluster, {  brief: "Stops GR on all nodes, secondaries first, primary instance the last.",  details: [    "This function will stop GR on all nodes.",    "The information is retrieved from the cluster topology."]});// Register the extension object as a global objectshell.registerGlobal("idc", idc, {  brief:"Utility functions for InnoDB Cluster."})The script defines stop_cluster function that is invoked with idc.stopCluster() and: Get the cluster object from the session (session against any member must be created beforehand) Fetch topology from cluster object Iterate through members belonging to the topology and get the address For every member, establish a session using same session user (e.g. root or whatever, it is a prerequisite to administer a cluster with the same user) Send command to stop Group Replication After iterating through all members, reset the original session The script also creates an extension object, registers it as global object and adds the function so it can be invoked as follows: It is also possible to restart the cluster with the built-in dba global object, with function dba.rebootClusterFromCompleteOutage();  So in short, it is possible to start and stop the cluster with one command and from the same MySQL Shell session. This is only a quick skeleton (can be improved e.g. like stopping GR starting by secondary instances, and the primary at last) to connect to instances and do operations, there is no limit to the number of things that are possible. Read more on LeFred's blog here.  [Less]

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 ... [More] 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 Repository Size ============================================================================================ Installing: mysql-server x86_64 8.0.17-3.module+el8.0.0+5253+1dce7bb2 ol8_appstream 22 M Installing dependencies: mysql-errmsg x86_64 8.0.17-3.module+el8.0.0+5253+1dce7bb2 ol8_appstream 557 k mysql-common x86_64 8.0.17-3.module+el8.0.0+5253+1dce7bb2 ol8_appstream 143 k protobuf-lite x86_64 3.5.0-7.el8 ol8_appstream 150 k mysql x86_64 8.0.17-3.module+el8.0.0+5253+1dce7bb2 ol8_appstream 11 M mariadb-connector-c-config … Enabling module streams: mysql 8.0 Transaction Summary ============================================================================================ Install 44 Packages Total download size: 48 M Installed size: 257 M Note that in RedHat and CentOS, the repository is called AppStream We can see that the package manager wants to install by default MySQL 8.0.17 ! Pretty recent, good ! We can also see that there is a module stream called mysql that is used. Let’s take a look at it: [root@localhost ~]# dnf module list mysql Last metadata expiration check: 0:00:53 ago on Sat 02 Nov 2019 10:17:51 AM UTC. Oracle Linux 8 Application Stream (x86_64) Name Stream Profiles Summary mysql 8.0 [d] client, server [d] MySQL Module Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled The module is indeed enabled at set to default. Now we will install our Community Repository from https://dev.mysql.com/downloads/repo/yum/: [root@localhost ~]# rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el8-1.noarch.rpm Retrieving https://dev.mysql.com/get/mysql80-community-release-el8-1.noarch.rpm warning: /var/tmp/rpm-tmp.hxFUWs: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Verifying… ################# [100%] Preparing… ################# [100%] Updating / installing… 1:mysql80-community-release-el8-1 ################# [100%] But if we try to install MySQL Community Server, the system one from AppStream is always selected. Whatever the package name used: mysql-server or mysql-community-server. We need to disable the mysql module from the package manager: [root@localhost ~]# dnf module disable mysql Last metadata expiration check: 0:01:24 ago on Sat 02 Nov 2019 10:17:51 AM UTC. Dependencies resolved. =========================================================================================== Package Arch Version Repository Size =========================================================================================== Disabling module streams: mysql Transaction Summary =========================================================================================== Is this ok [y/N]: y Complete! And now it’s possible to install the lastest MySQL (8.0.18 at this moment): [root@localhost ~]# dnf install mysql-server Last metadata expiration check: 0:01:42 ago on Sat 02 Nov 2019 10:17:51 AM UTC. Dependencies resolved. =========================================================================================== Package Arch Version Repository Size =========================================================================================== Installing: mysql-community-server x86_64 8.0.18-1.el8 mysql80-community 52 M Installing dependencies: mysql-community-client x86_64 8.0.18-1.el8 mysql80-community 12 M mysql-community-common x86_64 8.0.18-1.el8 mysql80-community 601 k mysql-community-libs x86_64 8.0.18-1.el8 mysql80-community 1.4 M perl-constant noarch 1.33-396.el8 ol8_baseos_latest 25 k … perl-parent noarch 1:0.237-1.el8 ol8_baseos_latest 20 k Transaction Summary =========================================================================================== Install 36 Packages Total download size: 77 M Installed size: 394 M Is this ok [y/N]: Note that you can now also use mysql-community-server as package name. We are very happy to see that MySQL 8.0 is now available with a very updated version by default in these major distribution. And now you also know how to enable the MySQL repository if you want to use it too. [Less]

The cloud provides very flexible environments to work with. You can easily scale it up and down by adding or removing nodes. If there’s a need, you can easily create a clone of your environment. This can be used for processes like upgrades, load ... [More] tests, disaster recovery. The main problem you have to deal with is that applications have to connect to the databases in some way, and flexible setups can be tricky for databases - especially with master-slave setups. Luckily, there are some options to make this process easier.  One way is to utilize a database proxy. There are several proxies to pick from, but in this blog post we will use ProxySQL, a well known proxy available for MySQL and MariaDB. We are going to show how you can use it to efficiently move traffic between MySQL nodes without visible impact for the application. We are also going to explain some limitations and drawbacks of this approach. Initial Cloud Setup At first, let’s discuss the setup. We will use AWS EC2 instances for our environment. As we are only testing, we don’t really care about high availability other than what we want to prove to be possible - seamless master changes. Therefore we will use a single application node and a single ProxySQL node. As per good practices, we will collocate ProxySQL on the application node and the application will be configured to connect to ProxySQL through Unix socket. This will reduce overhead related to TCP connections and increase security - traffic from the application to the proxy will not leave the local instance, leaving only ProxySQL - > MySQL connection to encrypt. Again, as this is a simple test, we will not setup SSL. In production environments you want to do that, even if you use VPC. The environment will look like in the diagram below: As the application, we will use Sysbench - a synthetic benchmark program for MySQL. It has an option to disable and enable the use of transactions, which we will use to demonstrate how ProxySQL handles them. Installing a MySQL Replication Cluster Using ClusterControl To make the deployment fast and efficient, we are going to use ClusterControl to deploy the MySQL replication setup for us. The installation of ClusterControl requires just a couple of steps. We won’t go into details here but you should open our website, register and installation of ClusterControl should be pretty much straightforward. Please keep in mind that you need to setup passwordless SSH between ClusterControl instance and all nodes that we will be managing with it. Once ClusterControl has been installed, you can log in. You will be presented with a deployment wizard: As we already have instances running in cloud, therefore we will just go with “Deploy” option. We will be presented with the following screen: We will pick MySQL Replication as the cluster type and we need to provide connectivity details. It can be connection using root user or it can as well be a sudo user with or without a password. In the next step, we have to make some decisions. We will use Percona Server for MySQL in its latest version. We also have to define a password for the root user on the nodes we will deploy. In the final step we have to define a topology - we will go with what we proposed at the beginning - a master and three slaves. ClusterControl will start the deployment - we can track it in the Activity tab, as shown on the screenshot above. Once the deployment has completed, we can see the cluster in the cluster list: Installing ProxySQL 2.0 Using ClusterControl The next step will be to deploy ProxySQL. ClusterControl can do this for us. We can do this in Manage -> Load Balancer. As we are just testing things, we are going to reuse the ClusterControl instance for ProxySQL and Sysbench. In real life you would probably want to use your “real” application server. If you can’t find it in the drop down, you can always write the server address (IP or hostname) by hand. We also want to define credentials for monitoring and administrative user. We also double-checked that ProxySQL 2.0 will be deployed (you can always change it to 1.4.x if you need). On the bottom part of the wizard we will define the user which will be created in both MySQL and ProxySQL. If you have an existing application, you probably want to use an existing user. If you use numerous users for your application you can always import the rest of them later, after ProxySQL will be deployed. We want to ensure that all the MySQL instances will be configured in ProxySQL. We will use explicit transactions so we set the switch accordingly. This is all we needed to do - the rest is to click on the “Deploy ProxySQL” button and let ClusterControl does its thing. When the installation is completed, ProxySQL will show up on the list of nodes in the cluster. As you can see on the screenshot above, it already detected the topology and distributed nodes across reader and writer hostgroups. Installing Sysbench The final step will be to create our “application” by installing Sysbench. The process is fairly simple. At first we have to install prerequisites, libraries and tools required to compile Sysbench: root@ip-10-0-0-115:~# apt install git automake libtool make libssl-dev pkg-config libmysqlclient-dev Then we want to clone the sysbench repository: root@ip-10-0-0-115:~# git clone https://github.com/akopytov/sysbench.git Finally we want to compile and install Sysbench: root@ip-10-0-0-115:~# cd sysbench/ root@ip-10-0-0-115:~/sysbench# ./autogen.sh && ./configure && make && make install This is it, Sysbench has been installed. We now need to generate some data. For that, at first, we need to create a schema. We will connect to local ProxySQL and through it we will create a ‘sbtest’ schema on the master. Please note we used Unix socket for connection with ProxySQL. root@ip-10-0-0-115:~/sysbench# mysql -S /tmp/proxysql.sock -u sbtest -psbtest mysql> CREATE DATABASE sbtest; Query OK, 1 row affected (0.01 sec) Now we can use sysbench to populate the database with data. Again, we do use Unix socket for connection with the proxy: root@ip-10-0-0-115:~# sysbench /root/sysbench/src/lua/oltp_read_write.lua --threads=4 --events=0 --time=3600 --mysql-socket=/tmp/proxysql.sock --mysql-user=sbtest --mysql-password=sbtest --tables=32 --report-interval=1 --skip-trx=on --table-size=100000 --db-ps-mode=disable prepare Once the data is ready, we can proceed to our tests.  Conclusion In the second part of this blog, we will discuss ProxySQL’s handling of connections, failover and its settings that can help us to manage the master switch in a way that will be the least intrusive to the application. Tags:  MySQL MariaDB proxysql database proxy load balancing cloud AWS [Less]

Some say that the best password is the one you don’t have to remember. That’s possible with MySQL, thanks to the auth_socket plugin and its MariaDB version unix_socket. Neither of these plugins is new, and some words have been written about the ... [More] auth_socket on this blog before, for example: how to change passwords in MySQL 5.7 when using plugin: auth_socket. But while reviewing what’s new with MariaDB 10.4, I saw that the unix_socket now comes installed by default and is one of the authentication methods (one of them because in MariaDB 10.4 a single user can have more than one authentication plugin, as explained in the Authentication from MariaDB 10.4 document). As already mentioned this is not news, and even when one installs MySQL using the .deb packages maintained by the Debian team, the root user is created so it uses the socket authentication. This is true for both MySQL and MariaDB: root@app:~# apt-cache show mysql-server-5.7 | grep -i maintainers Original-Maintainer: Debian MySQL Maintainers Original-Maintainer: Debian MySQL Maintainers Using the Debian packages of MySQL, the root is authenticated as follows: root@app:~# whoami root= root@app:~# mysql Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.27-0ubuntu0.16.04.1 (Ubuntu) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user, host, plugin, authentication_string from mysql.user where user = 'root'; +------+-----------+-------------+-----------------------+ | user | host      | plugin | authentication_string | +------+-----------+-------------+-----------------------+ | root | localhost | auth_socket |                       | +------+-----------+-------------+-----------------------+ 1 row in set (0.01 sec) Same for the MariaDB .deb package: 10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04 MariaDB [(none)]> show grants; +------------------------------------------------------------------------------------------------+ | Grants for root@localhost                                                                      | +------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION | | GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                  | +------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) For Percona Server, the .deb packages from the official Percona Repo are also setting the root user authentication to auth_socket. Here is an example of Percona Server for MySQL 8.0.16-7 and Ubuntu 16.04: root@app:~# whoami root root@app:~# mysql Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.16-7 Percona Server (GPL), Release '7', Revision '613e312' Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user, host, plugin, authentication_string from mysql.user where user ='root'; +------+-----------+-------------+-----------------------+ | user | host      | plugin | authentication_string | +------+-----------+-------------+-----------------------+ | root | localhost | auth_socket |                       | +------+-----------+-------------+-----------------------+ 1 row in set (0.00 sec) So, what’s the magic? The plugin checks that the Linux user matches the MySQL user using the SO_PEERCRED socket option to obtain information about the user running the client program. Thus, the plugin can be used only on systems that support the SO_PEERCRED option, such as Linux. The SO_PEERCRED socket option allows retrieving the uid of the process that is connected to the socket. It is then able to get the user name associated with that uid. Here’s an example with the user “vagrant”: vagrant@mysql1:~$ whoami vagrant vagrant@mysql1:~$ mysql ERROR 1698 (28000): Access denied for user 'vagrant'@'localhost' Since no user “vagrant” exists in MySQL, the access is denied. Let’s create the user and try again: MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'vagrant'@'localhost' IDENTIFIED VIA unix_socket; Query OK, 0 rows affected (0.00 sec) vagrant@mysql1:~$ mysql Welcome to the MariaDB monitor.  Commands end with ; or \g. Your MariaDB connection id is 45 Server version: 10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show grants; +---------------------------------------------------------------------------------+ | Grants for vagrant@localhost                                                    | +---------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'vagrant'@'localhost' IDENTIFIED VIA unix_socket | +---------------------------------------------------------------------------------+ 1 row in set (0.00 sec) Success! Now, what about on a non-debian distro, where this is not the default? Let’s try it on Percona Server for MySQL 8 installed on a CentOS 7: mysql> show variables like '%version%comment'; +-----------------+---------------------------------------------------+ | Variable_name   | Value                                   | +-----------------+---------------------------------------------------+ | version_comment | Percona Server (GPL), Release 7, Revision 613e312 | +-----------------+---------------------------------------------------+ 1 row in set (0.01 sec) mysql> CREATE USER 'percona'@'localhost' IDENTIFIED WITH auth_socket; ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded Failed. What is missing? The plugin is not loaded: mysql> pager grep socket PAGER set to 'grep socket' mysql> show plugins; 47 rows in set (0.00 sec) Let’s add the plugin in runtime: mysql> nopager PAGER set to stdout mysql> INSTALL PLUGIN auth_socket SONAME 'auth_socket.so'; Query OK, 0 rows affected (0.00 sec) mysql> pager grep socket; show plugins; PAGER set to 'grep socket' | auth_socket                     | ACTIVE | AUTHENTICATION | auth_socket.so | GPL     | 48 rows in set (0.00 sec) We got all we need now. Let’s try again: mysql> CREATE USER 'percona'@'localhost' IDENTIFIED WITH auth_socket; Query OK, 0 rows affected (0.01 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'percona'@'localhost'; Query OK, 0 rows affected (0.01 sec) And now we can log in as the OS user “percona”. [percona@ip-192-168-1-111 ~]$ whoami percona [percona@ip-192-168-1-111 ~]$ mysql -upercona Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 19 Server version: 8.0.16-7 Percona Server (GPL), Release 7, Revision 613e312 Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user, host, plugin, authentication_string from mysql.user where user ='percona'; +---------+-----------+-------------+-----------------------+ | user    | host   | plugin   | authentication_string | +---------+-----------+-------------+-----------------------+ | percona | localhost | auth_socket |                       | +---------+-----------+-------------+-----------------------+ 1 row in set (0.00 sec) Success again! Question: Can I try to log as the user percona from another user? [percona@ip-192-168-1-111 ~]$ logout [root@ip-192-168-1-111 ~]# mysql -upercona ERROR 1698 (28000): Access denied for user 'percona'@'localhost' No, you can’t. Conclusion MySQL is flexible enough in several aspects, one being the authentication methods. As we see in this post, one can achieve access without passwords by relying on OS users. This is helpful in several scenarios, but just to mention one: when migrating from RDS/Aurora to regular MySQL and using IAM Database Authentication to keep getting access without using passwords. [Less]