Questo è il mio blog!

Uno dei tanti....
Newsfeeds
Planet MySQL
Planet MySQL - https://planet.mysql.com

  • ProxySQL-Admin 2.x: Encryption of Credential Information
    Starting with the release of proxysql-admin 2.0.15,  the proxysql-admin 2.x series can now encrypt the credentials needed to access proxysql and cluster nodes. This only applies to the proxysql-admin configuration, this does not change the ProxySQL config, so those credentials are still unencrypted. The credentials file is the unencrypted file containing the usernames, passwords, hostnames, and ports needed to connect to ProxySQL and PXC (Percona XtraDB Cluster). The proxysql-login-file tool is used to encrypt the credentials file. This encrypted file is known as a login-file. This login-file can then be used by the proxysql-admin and proxysql-status scripts. Note: This feature requires OpenSSL v1.1.1 and above (with the exception of Ubuntu 16.04). Please see the supported platforms topic below. Configuration Precedence command-line options the encrypted login-file options (if the login-file is used) the unencrypted proxysql-admin configuration file values Example Usage # create the credentials file $ echo "monitor.user=monitor" > credentials.cnf $ echo "monitor.password=password" >> credentials.cnf # Choose a password $ passwd="secret" # Method (1) : Encrypt this data with --password $ proxysql-login-file --in credentials.cnf --out login-file.cnf --password=${passwd} # Method (2a) : Encrypt the data with --password-file # Sending the password via the command-line is insecure, # it's better to use --password-file so that the # password doesn't show up in the command-line $ proxysql-login-file --in credentials.cnf --out login-file.cnf \ --password-file=<(echo "${passwd}") # Method (2b) : Running the command using sudo will not work with # bash's process substition. In this case, sending the # password via stdin is another option. $ sudo echo "${passwd}" | proxysql-login-file --in credentials.cnf --out login-file.cnf \ --password-file=/dev/stdin # Method (3) : The script will prompt for the password # if no password is provided via the command-line options. $ proxysql-login-file --in credentials.cnf --out login-file.cnf Enter the password: # Remove the unencrypted credentials file $ rm credentials.cnf # Call the proxysql-admin script with the login-file $ proxysql-admin --enable --login-file=login-file.cnf \ --login-password-file=<(echo "${passwd}") This script will assist with configuring ProxySQL for use with Percona XtraDB Cluster (currently only PXC in combination with ProxySQL is supported) ... # Call proxysql-status with the login-file $ proxysql-status --login-file=login-file.cnf \ --login-password-file=<(echo "${passwd}") ............ DUMPING MAIN DATABASE ............ ***** DUMPING global_variables ***** +--------------------------------------------------------------+-----------------------------+ | variable_name | variable_value | +--------------------------------------------------------------+-----------------------------+ | mysql-default_charset | utf8 | | ... Credentials File Format # -------------------------------- # This file is constructed as a set of "name=value" pairs. # Notes: # (1) Comment lines start with '#' and must be on separate lines # (2) the name part # - The only acceptable names are shown below in this example. # Other values will be ignored. # (3) The value part: # - This does NOT use quotes, so any quote character will be part of the value # - The entire line will be used (be careful with spaces) # # If a value is not specified here, than the default value from the # configuration file will be used. # -------------------------------- # -------------------------------- # proxysql admin interface credentials. # -------------------------------- proxysql.user=admin proxysql.password=admin proxysql.host=localhost proxysql.port=6032 # -------------------------------- # PXC admin credentials for connecting to a PXC node. # -------------------------------- cluster.user=admin cluster.password=admin cluster.host=localhost cluster.port=4110 # -------------------------------- # proxysql monitoring user. proxysql admin script will create # this user in PXC to monitor a PXC node. # -------------------------------- monitor.user=monitor monitor.password=monitor # -------------------------------- # Application user to connect to a PXC node through proxysql # -------------------------------- cluster-app.user=cluster_one cluster-app.password=passw0rd  Requirements and Supported Platforms OpenSSL 1.1.1 (and higher) is an installation requirement (with the exception of Ubuntu 16.04 (xenial), see the comment below). Centos 7 The OpenSSL 1.1.1+ package must be installed. This can be installed withyum install openssl11This command will install OpenSSL 1.1 alongside the system installation and the script will use the openssl11 binary.  Centos 8 The default version of OpenSSL is v1.1.1 Ubuntu 16.04 (xenial) For Ubuntu xenial (16.04), installation of OpenSSL v1.1.1+ is not required, a purpose-built binary used for the encryption/decryption (proxysql-admin-openssl) will be installed alongside the proxysql-admin scripts. Ubuntu 18.04 (bionic) The default version of OpenSSL is v1.1.1

  • Chaos Testing Leads to More Stable Percona XtraDB Cluster
    In my talk at Percona Live 2021, “Creating Chaos in Databases”, I discussed how creating a controlled interruption in available resources (I used primary pod and network interruptions) allows us to test the stability of a database, and in our case, Percona XtraDB Cluster. I also mentioned in the talk that my testing led to diagnosing a few unpleasant bugs, namely: PXC-3437: Node fails to join in the endless loop PXC-3580: Aggressive network outages on one node makes the whole cluster unusable PXC-3596: Node stuck in aborting SST Currently, I am happy to report these bugs are fixed in Percona XtraDB Cluster 8.0.23 and this version will provide you with a much better and stable experience, especially when used in a combination with our Percona Distribution for MySQL Operator. I am not able to break Percona XtraDB Cluster 8.0.23 as I was able to in previous releases. It seems I need to be more creative to find more network-related bugs, so we will see how it goes. As a side note, I would like to mention that our fixes are available to everybody who would like to improve the stability of their products based on the Galera library. We do not hide our source code behind “Enterprise” paywalls or hide them in combined .tar.gz source code dumps. For example, a bug fix for bug https://jira.percona.com/browse/PXC-3580 is available in the pull request https://github.com/percona/galera/pull/214/files. Percona is committed to providing you with a real Open Source experience. Happy Clustering!

  • Disaster Recovery with Galera Cluster 2021 Edition Webinar
    We talk a lot about Galera Cluster for MySQL being great for High Availability, but what about Disaster Recovery (DR)? Database outages may occur when you lose a data centre due to power outages or natural disaster, so why not plan appropriately in advance? In this webinar, we will discuss the business considerations including achieving the highest possible uptime, focus on business impact, risk mitigation, and disaster recovery itself. What scenarios are right for you: fully synchronous replication across various data centres? A mix of synchronous and asynchronous to an offsite location? We will discuss Galera Cluster, mixed setups, the use of proxies, and also evaluate some third party solutions. We will debunk claims about how Galera Cluster might not work across data centers, tell you how to successfully do this across data centres, with performance tuning settings for all the segments. Galera 4 has also been released, making the networking protocol a lot more robust, and the changes will be discussed. This webinar will focus on architecture solutions, DR scenarios and how to ensure you are successful at the end of it all. EMEA Webinar, Thursday July 8 2021, 2:00-2.50 PM CEST JOIN EMEA TIMEZONE WEBINAR    USA Webinar, Thursday July 8 2021, 9:00-9.50 AM PDT JOIN USA TIMEZONE WEBINAR 

  • MySQL BETWEEN Operator Queries – Are they inclusive?
    I recently learned of some odd behavior using MySQL BETWEEN operator queries, filtering by a DATETIME column. I wrote about this over on Medium so I am sharing the post for any readers here who are interested… Image by _Alicja_ from Pixabay  Self-Promotion: If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like! I’ve finally started a weekly email, covering SQL/PHP related content. If you’re interested in subscribing, use this sign-up form or the one located at the bottom of the page. Thank you! In my eclectic day job, I cover many tasks. Most of them I do not care for. However, I work extremely hard at those so I can work on the one task I do like; programming a custom internal LAMP stack web application. I have been working on this web application for over a year and it has grown immensely. Anyways, enough yada yada. On to the crux of the matter. Consider making a donation as I continue to provide valuable content here on my blog. Thank you so much!!! Every bit is much appreciated and helps tremendously! While prototyping out a query which retrieves PDF’s stored in the database (check out this post on the intricacies), I learned some interesting aspects of the BETWEEN operator involving DATETIME column values. I’ll leave it there for now and let you dig into the actual article, Are MySQL BETWEEN Operator Queries Inclusive. Feel free to share your thoughts or any comments you may have about these types of queries in the comments section below. Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. The post MySQL BETWEEN Operator Queries – Are they inclusive? appeared first on Digital Owl's Prose.

  • MySQL Outer Joins
    The students needed yet another example of LEFT JOIN, RIGHT JOIN, and FULL JOIN syntax (by combining a left and right join with the UNION set operator). To that end, I put this set of examples together. The example also shows how to order the result set from a derived table with the UNION operator. It uses the WITH clause to build a Common Table Expression (CTE), which allows the query to order the UNION set operator’s product based on the left and right join queries. It uses a CASE statement to order the result sets. The left_table is the parent table and the right_table is the child table in the relationship, which means the right_table holds a left_id foreign key column that lets you connect matching rows in the left_table. You build the little model with the following script: -- ----------------------------------------------------------------- -- Drop the demonstration tables. -- ----------------------------------------------------------------- DROP TABLE IF EXISTS left_table, right_table; -- ----------------------------------------------------------------- -- Create left_table. -- ----------------------------------------------------------------- CREATE TABLE left_table ( left_id int unsigned primary key auto_increment , leftstring varchar(10)); -- ----------------------------------------------------------------- -- Create left_table. -- ----------------------------------------------------------------- CREATE TABLE right_table ( right_id int unsigned primary key auto_increment , left_id int unsigned , rightstring varchar(10)); -- ----------------------------------------------------------------- -- Insert five rows to the left table, which holds a -- left_id primary key column. -- ----------------------------------------------------------------- INSERT INTO left_table (leftstring) values ('One'); INSERT INTO left_table (leftstring) values ('Two'); INSERT INTO left_table (leftstring) values ('Three'); INSERT INTO left_table (leftstring) values ('Four'); INSERT INTO left_table (leftstring) values ('Five'); -- ----------------------------------------------------------------- -- Delete row four to create a gap. -- ----------------------------------------------------------------- DELETE FROM left_table where left_id = 4; -- ----------------------------------------------------------------- -- Insert four rows, skipping a foreign key value for the -- left_id primary key value of 2. -- ----------------------------------------------------------------- INSERT INTO right_table (rightstring,left_id) values ('One',1); INSERT INTO right_table (rightstring,left_id) values ('Three',3); INSERT INTO right_table (rightstring,left_id) values ('Four',4); INSERT INTO right_table (rightstring,left_id) values ('Five',5); Here are the join statements: INNER JOIN The INNER JOIN only returns those rows that match between a primary and foreign key column or set of columns. SELECT l.left_id , l.leftstring , r.left_id , r.right_id , r.rightstring FROM left_table l INNER JOIN right_table r ON l.left_id = r.left_id; It produces the following result set: +---------+------------+---------+----------+-------------+ | left_id | leftstring | left_id | right_id | rightstring | +---------+------------+---------+----------+-------------+ | 1 | One | 1 | 1 | One | | 3 | Three | 3 | 2 | Three | | 5 | Five | 5 | 4 | Five | +---------+------------+---------+----------+-------------+ 3 rows in set (0.00 sec) LEFT OUTER JOIN The LEFT OUTER JOIN only returns those rows that match between a primary and foreign key column or set of columns and any rows in the table on the lefthand side of the join that fail to match with any row on the righthand side of the join. The non-matching rows are also known as the right complement of the join. SELECT l.left_id , l.leftstring , r.left_id , r.right_id , r.rightstring FROM left_table l LEFT JOIN right_table r ON l.left_id = r.left_id; It produces the following result set: +---------+------------+---------+----------+-------------+ | left_id | leftstring | left_id | right_id | rightstring | +---------+------------+---------+----------+-------------+ | 1 | One | 1 | 1 | One | | 2 | Two | NULL | NULL | NULL | | 3 | Three | 3 | 2 | Three | | 5 | Five | 5 | 4 | Five | +---------+------------+---------+----------+-------------+ 4 rows in set (0.00 sec) Add the following line 8 to the query and you get only those rows in the lefthand table that have no child-related rows in the righthand table. These rows are sometimes called childless parent rows. More or less, the use case for this type of query is to find order headers without order lines. FROM left_table l LEFT JOIN right_table r ON l.left_id = r.left_id WHERE r.left_id IS NULL; It produces the following result set: +---------+------------+---------+----------+-------------+ | left_id | leftstring | left_id | right_id | rightstring | +---------+------------+---------+----------+-------------+ | 2 | Two | NULL | NULL | NULL | +---------+------------+---------+----------+-------------+ 1 row in set (0.00 sec) RIGHT OUTER JOIN SELECT l.left_id , l.leftstring , r.left_id , r.right_id , r.rightstring FROM left_table l RIGHT JOIN right_table r ON l.left_id = r.left_id; It produces the following result set: +---------+------------+---------+----------+-------------+ | left_id | leftstring | left_id | right_id | rightstring | +---------+------------+---------+----------+-------------+ | 1 | One | 1 | 1 | One | | 3 | Three | 3 | 2 | Three | | NULL | NULL | 4 | 3 | Four | | 5 | Five | 5 | 4 | Five | +---------+------------+---------+----------+-------------+ 4 rows in set (0.00 sec) Add the following line 8 to the query and you get only those rows in the righthand table that have no parent-related rows in the lefthand table. These rows are sometimes called orphans because they have no parent row. More or less, the use case for this type of query is to find latent order lines after deleting the order header. FROM left_table l LEFT JOIN right_table r ON l.left_id = r.left_id WHERE l.left_id IS NULL; It produces the following result set: +---------+------------+---------+----------+-------------+ | left_id | leftstring | left_id | right_id | rightstring | +---------+------------+---------+----------+-------------+ | NULL | NULL | 4 | 3 | Four | +---------+------------+---------+----------+-------------+ 1 row in set (0.00 sec) FULL OUTER JOIN The full outer join doesn’t exist in MySQL, so you combine a LEFT OUTER JOIN and RIGHT OUTER JOIN with the UNION operator. The UNION operator eliminates the duplicate row from the intersection of the joins. Here’s the full query: WITH cte AS (SELECT l.left_id AS primary_left_id , l.leftstring , r.left_id AS foreign_left_id , r.right_id , r.rightstring FROM left_table l LEFT JOIN right_table r ON l.left_id = r.left_id UNION SELECT l.left_id AS primary_left_id , l.leftstring , r.left_id AS foreign_left_id , r.right_id , r.rightstring FROM left_table l RIGHT JOIN right_table r ON l.left_id = r.left_id) SELECT * FROM cte ORDER BY CASE WHEN 'One' IN (leftstring, rightstring) THEN 1 WHEN 'Two' IN (leftstring, rightstring) THEN 2 WHEN 'Three' IN (leftstring, rightstring) THEN 3 WHEN 'Four' IN (leftstring, rightstring) THEN 4 WHEN 'Five' IN (leftstring, rightstring) THEN 5 END; It produces the following result set: +-----------------+------------+-----------------+----------+-------------+ | primary_left_id | leftstring | foreign_left_id | right_id | rightstring | +-----------------+------------+-----------------+----------+-------------+ | 1 | One | 1 | 1 | One | | 2 | Two | NULL | NULL | NULL | | 3 | Three | 3 | 2 | Three | | NULL | NULL | 4 | 3 | Four | | 5 | Five | 5 | 4 | Five | +-----------------+------------+-----------------+----------+-------------+ 5 rows in set (0.00 sec) Add the following lines 18 and 19 to the query and you get only those rows that are childless parent rows or orphaned child rows. More or less, the use case for this type of query is to find both order headers without order lines and order lines abandoned by deleted order headers. SELECT * FROM cte WHERE primary_left_id IS NULL OR foreign_left_id IS NULL It produces the following result set: +-----------------+------------+-----------------+----------+-------------+ | primary_left_id | leftstring | foreign_left_id | right_id | rightstring | +-----------------+------------+-----------------+----------+-------------+ | 2 | Two | NULL | NULL | NULL | | NULL | NULL | 4 | 3 | Four | +-----------------+------------+-----------------+----------+-------------+ 2 rows in set (0.00 sec) The ORDER BY clause used is a variation on the more common choice of: WHEN leftstring = 'One' OR rightstring = 'One' THEN 1 The position of two string literals in any statement is a bad idea. Inverting the literal on the right and using a IN operator gives you a better and clearer WHEN statement: WHEN 'One' IN (leftstring, rightstring) THEN 1 As always, I hope this helps those looking for syntax examples.