May
09
2024
--

Securing Your MySQL Database: Essential Best Practices

MySQL Database SecurityHave you ever read a news story about a major company experiencing a data breach that exposed millions of customer records? These breaches can be devastating, causing significant financial losses, reputational damage, and even legal repercussions. Unfortunately, MySQL databases, one of the most popular relational database management systems, is at the heart of many critical […]

May
06
2024
--

Securing Your MongoDB Database: Essential Best Practices

MongoDB Database Security Best PracticesMongoDB offers powerful features and scalability, but like any database system, it has security challenges that must be addressed to protect sensitive data as well as comply with regulatory standards like GDPR, HIPAA, PCI DSS, and AM/ATF. A single breach can significantly impact a business, and failure to establish sufficient security measures can result in […]

Apr
30
2024
--

LDAP Authentication in PgBouncer Through PAM

There are many cases where external connection poolers like pgBouncer become unavoidable despite the costs and complexities associated with them. PgBouncer is one of the most popular external connection poolers for PostgreSQL. It is thin and lightweight, so it doesn’t have built-in authentication features like LDAP, which is essential for many enterprises. Luckily, pgBouncer has […]

Feb
06
2024
--

Are Your MySQL Users Using ‘password’ or ‘thebossisajerk’ as Passwords?

MySQL password securityAre your MySQL users using ‘password’, ‘s3cr3t’, or ‘thebossisajerk’ as their passwords? Easy-to-guess passwords can be disastrous to the security of your data, but there is a way to exclude inappropriate words or phrases from being used. The first step is to compile a list of words and phrases you want to exclude, and that […]

Jan
05
2024
--

Configuring Keyring for Encryption Using AWS Key Management Service in Percona Server for MySQL

Configuring Keyring for Encryption Using AWS Key Management ServiceThe AWS KMS component is now available in Percona Server for MySQL starting from version 8.0.30. This addition enables data-at-rest encryption by utilizing the AWS KMS component, providing the functionality to create and manage cryptographic keys across AWS services.How do we set up encryption using AWS KMS?You should only load a keyring component with a […]

Jan
04
2024
--

MySQL General Tablespaces: A Powerful Storage Option for Your Data

MySQL general tablespacesManaging storage and performance efficiently in your MySQL database is crucial, and general tablespaces offer flexibility in achieving this. This blog discusses general tablespaces and explores their functionalities, benefits, and practical usage, along with illustrative examples.What are MySQL general tablespaces?In contrast to the single system tablespace that holds system tables by default, general tablespaces are […]

Jan
02
2024
--

Audit DROP Statements in Percona Server for MySQL

Audit DROP Statements in Percona Server for MySQL

Managing database servers involves different aspects, among which security is critical. We know that we should always grant the minimal required permissions to the different user accounts in the database, as having a user with high-level permission can lead to unexpected results, such as having an index drop affecting the system performance or even more disastrous events such as having a table or a database dropped causing the system to crash and possible data loss. However, we know having a “runaway” grant is always a possibility, especially in environments with a large number of servers. One action item we can do is to trace all DROP events in the server and analyze them to understand if the behavior is normal.

Audit Log Plugin

One way to track these events is through auditing. Fortunately, in Percona Server for MySQL, this feature is free to use. The auditing plugin overview has been explained in the blog post Percona’s MySQL Audit Log Plugin – An Enterprise Feature at a Community Price. Installing the plugin is a straightforward operation that is explained in the blog post mentioned above.

How does it work?

In this blog post, we dig into the steps to track down the DROP statements in our database server. Once the plugin is installed, by default, it will track all events in the database because the variable “audit_log_policy” is set to ALL by default. The audit events are logged into a file defined using the variable “audit_log_file”; by default, it is “audit.log,” and it is stored in the “datadir”. As part of the initial setup, you may want to consider changing the default format to JSON, as you probably will have other tools absorbing the logs. To do this, you need to add the variable “audit_log_format” to your MySQL configuration file and restart the database instance, as this variable is not dynamic.

$ cat my.cnf
…
audit_log_format = JSON

### After restart:
mysql> select @@audit_log_format;
+--------------------+
| @@audit_log_format |
+--------------------+
| JSON               |
+--------------------+
1 row in set (0.02 sec)

We will narrow down the events to log only the drop events for the database-related objects. The drop events are listed below.

mysql> SELECT name FROM performance_schema.setup_instruments WHERE name LIKE "statement/sql/drop%" ORDER BY name;
+---------------------------------------------+
| name                                        |
+---------------------------------------------+
| statement/sql/drop_compression_dictionary   |
| statement/sql/drop_db                       |
| statement/sql/drop_event                    |
| statement/sql/drop_function                 |
| statement/sql/drop_index                    |
| statement/sql/drop_procedure                |
| statement/sql/drop_resource_group           |
| statement/sql/drop_role                     |
| statement/sql/drop_server                   |
| statement/sql/drop_spatial_reference_system |
| statement/sql/drop_table                    |
| statement/sql/drop_trigger                  |
| statement/sql/drop_user                     |
| statement/sql/drop_view                     |
+---------------------------------------------+
14 rows in set (0.09 sec)

We will focus on the DROP events for the database and tables, but be advised that it can be extended to any of the above events. Now, we will set the variable “audit_log_include_commands” to only track the DROP DATABASE and DROP TABLE events. Please note that we used the command SET PERSIST to make sure the changes survive a server restart. You can read more about the SET PERSIST option in SET PERSIST in MySQL: A Small Thing for Setting System Variable Values.

mysql> SET PERSIST audit_log_include_commands = 'drop_db,drop_table';
Query OK, 0 rows affected (0.04 sec)

Now, let’s imagine we have a user called “evil_user,” and this user has all permissions over our important database:

mysql> show grants for evil_user;
+-------------------------------------------------------------+
| Grants for evil_user@%                                      |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO `evil_user`@`%`                       |
| GRANT ALL PRIVILEGES ON `important_db`.* TO `evil_user`@`%` |
+-------------------------------------------------------------+
2 rows in set (0.02 sec)

Next, the “evil_user” connects to the database server and drops table “t3”:

mysql> DROP TABLE important_db.t3;
Query OK, 0 rows affected (0.04 sec)

Since we are tracking the DROP TABLE events, the “audit.log” file shows the DROP event, including the timestamp, the user, and the IP where the event originated:

mysql@897662d1f69c mysql]$ tail audit.log -n1
{"audit_record":{"name":"Query","record":"20409_2023-12-16T15:24:55","timestamp":"2023-12-16T15:33:04Z","command_class":"drop_table","connection_id":"9","status":0,"sqltext":"DROP TABLE important_db.t3","user":"evil_user[evil_user] @  [172.17.0.1]","host":"","os_user":"","ip":"172.17.0.1","db":"important_db"}}

### PRETTY FORMAT:
{
  "audit_record": {
    "name": "Query",
    "record": "20409_2023-12-16T15:24:55",
    "timestamp": "2023-12-16T15:33:04Z",
    "command_class": "drop_table",
    "connection_id": "9",
    "status": 0,
    "sqltext": "DROP TABLE important_db.t3",
    "user": "evil_user[evil_user] @  [172.17.0.1]",
    "host": "",
    "os_user": "",
    "ip": "172.17.0.1",
    "db": "important_db"
  }
}

Please notice the value of the “STATUS”; in this case, it is zero, which means the DROP operation succeeded. This is important because the audit plugin also logs the failed DROP commands. For example, the “evil_user” will try to drop a non-existent table “t4”; as expected, it gets an error.

mysql> DROP TABLE important_db.t4;
ERROR 1051 (42S02): Unknown table 'important_db.t4'

However, the event was still logged in the audit log file, but notice the value for STATUS. It is the same error code (1051) shown when the DROP command was executed, you need to take this into account when analyzing this audit events.

[mysql@897662d1f69c mysql]$ tail audit.log -n1
{"audit_record":{"name":"Query","record":"20410_2023-12-16T15:24:55","timestamp":"2023-12-16T15:34:35Z","command_class":"drop_table","connection_id":"9","status":1051,"sqltext":"DROP TABLE important_db.t4","user":"evil_user[evil_user] @  [172.17.0.1]","host":"","os_user":"","ip":"172.17.0.1","db":"important_db"}}

### PRETTY FORMAT:
{
  "audit_record": {
    "name": "Query",
    "record": "20410_2023-12-16T15:24:55",
    "timestamp": "2023-12-16T15:34:35Z",
    "command_class": "drop_table",
    "connection_id": "9",
    "status": 1051,
    "sqltext": "DROP TABLE important_db.t4",
    "user": "evil_user[evil_user] @  [172.17.0.1]",
    "host": "",
    "os_user": "",
    "ip": "172.17.0.1",
    "db": "important_db"
  }
}

Now, the “evil_user” will continue its evil actions and will DROP the important database:

mysql> DROP DATABASE important_db;
Query OK, 2 rows affected (0.11 sec)

This event is also tracked in the audit log file; please note the STATUS value. It indicates the operation was successful.

[mysql@897662d1f69c mysql]$ tail audit.log -n1
{"audit_record":{"name":"Query","record":"20411_2023-12-16T15:24:55","timestamp":"2023-12-16T15:35:36Z","command_class":"drop_db","connection_id":"9","status":0,"sqltext":"DROP DATABASE important_db","user":"evil_user[evil_user] @  [172.17.0.1]","host":"","os_user":"","ip":"172.17.0.1","db":"important_db"}}

### PRETTY FORMAT:
{
  "audit_record": {
    "name": "Query",
    "record": "20411_2023-12-16T15:24:55",
    "timestamp": "2023-12-16T15:35:36Z",
    "command_class": "drop_db",
    "connection_id": "9",
    "status": 0,
    "sqltext": "DROP DATABASE important_db",
    "user": "evil_user[evil_user] @  [172.17.0.1]",
    "host": "",
    "os_user": "",
    "ip": "172.17.0.1",
    "db": "important_db"
  }
}

To capture these events in real-time, you can have a cron task to monitor the audit log file constantly and alert you if any suspicious activity is found. Please notice that only the DROP TABLE and DROP DATABASE events are being logged in the audit log file. You can add more events or further filter the events to capture. It is also important to mention that despite the filter, the “Connect” and “Quit” connection events are also captured.

Conclusion

The audit plugin is a powerful tool we have at our disposal in Percona Server for MySQL. This is just a simple example of a critical usage of this tool. It has many additional features that we will review in future blogs. Please remember that this is a paid feature for MySQL Enterprise, while it is a free feature for Percona Server for MySQL. If you need to use this tool, please consider switching to Percona Server for MySQL, a true drop-in replacement for MySQL Community Version. Drop us a line if you need assistance switching to Percona Server; we are happy to help.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

 

Try Percona Distribution for MySQL today!

Aug
24
2023
--

Automated Percona Monitoring and Management Upgrades

Automated Percona Monitoring and Management Upgrades

Welcome to our guide on keeping your Percona Monitoring and Management (PMM) Server and Client up-to-date. In this blog post, we’ll walk you through a method to ensure your PMM solution runs the latest and most secure versions. It’s important to note that this is just one option among many, and we’re eager to hear your feedback and alternative approaches.

Maintaining an up-to-date PMM environment is crucial for accessing the latest features and ensuring maximum security. However, the update process can be complex and time-consuming. To simplify it, we’ll introduce you to a method involving Watchtower and cron jobs. Watchtower automates PMM Server updates, while cron jobs handle PMM Client updates.

We value your input and want to know if this approach works for you. Share your thoughts, suggestions, and alternative methods on our forum. Your feedback will help us improve the update process and provide better solutions for the PMM community.

Let’s explore the steps to install, configure, and automate the update process for your PMM Server and Client. Together, we can keep your monitoring solution up-to-date effortlessly and enhance its effectiveness.

Please remember this is not official documentation but a cookbook about easier PMM management. This method may not work in later versions.

PMM Server installation

Installing PMM is a straightforward process. You can refer to the instructions provided for detailed guidance.

To install PMM, you can use the following command:

curl -fsSL https://www.percona.com/get/pmm | /bin/bash

This command will install Docker and the PMM server for you. However, when updating PMM, there are a few considerations to remember.

The recommended approach for updating PMM is to replace the Docker image. This method offers greater stability and ensures a clean installation of the latest version. To update, you must stop the current PMM server, obtain the new Docker image, and start a new instance. PMM will handle any necessary migrations during the startup process. While this method provides a more stable update, it does require additional steps, such as accessing the terminal and executing specific commands. This can be cumbersome, especially if you rely on IT support for such tasks.

Alternatively, you can use the Update button within the PMM server itself, which offers a more convenient solution for updating. This method simplifies the process by automating the update within the PMM user interface. You can find more details on this approach in our documentation.

Considering the trade-off between stability and convenience, the Docker image replacement method is recommended for ensuring a reliable update. However, choosing the approach that best suits your requirements and operational considerations is important.

Now, let’s explore automation options for simplifying the update process of our PMM Server.

PMM Server update automation

To automate the update process for your PMM Server, we’ll utilize a tool called Watchtower. Watchtower is designed to monitor your Docker container and update it automatically whenever a new image becomes available.

To get started with Watchtower, you can follow these steps:

1. Run the following command to start Watchtower:

docker run -d --name pmm-watchtower -v /var/run/docker.sock:/var/run/docker.sock containrrr/watchtower pmm-server

2. That’s it! Watchtower is now actively monitoring your PMM Server container. It will regularly check for new versions of the pmm-server:2 image (used in the quick install script by default) every 24 hours. Watchtower automatically updates your PMM instance when a new image is detected, ensuring you have the latest version.

Please note that you should wait for the next official PMM release to be published before Watchtower can perform an update.

Watchtower simplifies the update process by eliminating the need for manual intervention and monitoring. It provides a convenient way to keep your PMM Server up-to-date with the latest releases, ensuring you have access to new features and security enhancements.

Next, let’s explore the process of updating the PMM Client and establishing a connection with the PMM Server.

PMM Client Installation and Сonfiguration

In addition to the PMM Server, the PMM Client is another critical component of the distributed PMM system. The PMM Client is required unless you are solely adding remote instances via the PMM Server, in which case the Client embedded in the PMM server itself is used and is always in sync with the PMM Server version.

To install the PMM Client, you can follow the instructions provided. This method uses the package manager on your system, offering a straightforward installation process that supports seamless upgrades.

Once the PMM Client is installed, it needs to be connected to the PMM Server. Use the following command to configure the connection:

pmm-admin config --server-insecure-tls --server-url=https://admin:admin@X.X.X.X:443

Replace X.X.X.X with the IP address or hostname of your PMM Server. This command establishes the necessary settings for communication between the Client and Server.

After PMM Client is configured, you probably will add some Database into PMM.

PMM Client update automation

Automating the PMM Client upgrade process can be a bit complex due to version compatibility requirements with the PMM Server. A typical PMM Client upgrade process involves executing the following commands when a new version is released:

sudo apt update
sudo apt install -y pmm2-client

However, it’s crucial to note that the PMM Server should be the same version as, or newer than, the PMM Client. Therefore, you must upgrade the PMM Server first before updating the clients. This condition adds complexity to automating upgrades because it requires checking the server version to ensure compatibility.

To address this challenge, we can leverage the pmm-admin status command, which provides information about the Server and Client versions. By running pmm-admin status, you can obtain details such as the Server URL and version, as well as the Client’s connection status, version, and other relevant data.

To simplify automation, the pmm-admin command can expose data in JSON format using the --JSON flag. This allows for easier integration with automation scripts.

To assist you further, I have created a simple script that performs the PMM Client update process. You can find the script at this URL: https://gist.github.com/rnovikovP/e6d7d1dcda7f5004ad186f6bc4de565a . I recommend placing this script in the /usr/local/percona/pmm2/cron/ directory, which serves as a centralized location for all PMM-related files.

Once you have downloaded the script, the next step is configuring it to run automatically using cron. Here’s a simple one-liner that creates the necessary directory, downloads the script, sets the appropriate permissions, and adds a cron job to execute it daily at 8 AM:

(mkdir -p /usr/local/percona/pmm2/cron/ && curl -sSL https://gist.githubusercontent.com/rnovikovP/e6d7d1dcda7f5004ad186f6bc4de565a/raw/ -o /usr/local/percona/pmm2/cron/pmm2-client-updater.sh && chmod +x /usr/local/percona/pmm2/cron/pmm2-client-updater.sh && (crontab -l 2>/dev/null; echo "0 8 * * * /usr/local/percona/pmm2/cron/pmm2-client-updater.sh") | crontab -)

This one-liner creates the necessary directory structure, downloads the script using curl, sets the script’s executable permission, and adds a cron job to execute the script at 8 AM daily.

By implementing this automation, the script will handle the PMM Client updates, checking for the latest version and upgrading the Client as needed. This saves you from manually executing the upgrade commands and ensures that your PMM Client is always up to date.

Next, we’ll review what we need to confirm for the automated updates process.

Update configuration confirmation

To ensure that your PMM update automation is functioning correctly, there are a few steps you can take. Since the configuration relies on officially released images and binaries, you will need to wait until the new version of PMM is officially released. Rest assured, the release is just around the corner.

Here’s what you can check in the meantime:

On the PMM Server, verify the running containers by executing the following command:
docker ps

You should see output similar to this:

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
e139815b8317 containrrr/watchtower "/watchtower pmm-ser…" 4 seconds ago Up 3 seconds 8080/tcp watchtower
8f9573bb063f percona/pmm-server:2 "/opt/entrypoint.sh" 21 hours ago Up 21 hours 80/tcp, 0.0.0.0:443->443/tcp, :::443->443/tcp pmm-server

This confirms that the PMM Server container is running successfully.

On the PMM Client, verify the cron job configuration by executing the following command:

crontab -l

You should see the following line in the output:

0 8 * * * /usr/local/percona/pmm2/cron/pmm2-client-updater.sh

This confirms that the cron job for the PMM Client updater script is set to run daily at 8 AM.

Additionally, you can manually execute the updater script to confirm that it is functioning correctly:
/usr/local/percona/pmm2/cron/pmm2-client-updater.sh

Running this command should display the PMM Server and Client versions, which should match the following:

PMM Server version: [2.39.0]
PMM Client version: [2.39.0]

Ensure that both versions are the same, indicating a successful update.

If all the checks above align with the presented information, it means your PMM update automation is on track. Now, all you need to do is wait for the new PMM release and observe your PMM system’s updated state.

Closing

In conclusion, this blog post has provided a comprehensive guide on keeping your PMM Server and Client updated. While the methods discussed here are effective, we acknowledge that alternative approaches or better options may be available. We value your input and encourage you to share any additional techniques or ideas that could improve the update process for PMM.

One topic worth exploring is automating the update process for PMM and its components. Are you interested in auto-updates for PMM? We would love to hear your thoughts on this matter. Share your experiences, suggestions, or concerns on the forum.

Remember, staying up-to-date with the latest versions of PMM is crucial for maintaining maximum security and accessing the latest features. Your feedback and contributions are invaluable as we strive to provide the best solutions for the PMM community.

Thank you for reading, and we appreciate your engagement in improving the PMM update process. Together, we can make monitoring with PMM even more efficient and user-friendly.

If you want to share your experience using PMM  – please don’t hesitate to schedule a call with me using this link; I’m always open to hearing users’ pains to drive the product in the right direction.

Percona Monitoring and Management is a best-of-breed open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.

 

Download Percona Monitoring and Management Today

Aug
10
2023
--

MySQL 8.0.34 Improved Password Management by Defining the Change Characters Count

MySQL 8.0.34 Improved Password Management

MySQL 8.0.34 brings us the new password validation parameter “validate_password.changed_characters_percentage”. Using this, we can control the minimum number of characters in a password that a user must change before validate_password accepts a new password for the user’s account. In this blog, I offer a few scenarios showing how the parameter “validate_password.changed_characters_percentage” affects user password changes.

Requirement

To make this work, we should enable the “Password Verification-Required Policy” (introduced in MySQL 8.0.13). We can allow it to GLOBALLY by using the parameter “password_require_current” or by specifying  “PASSWORD REQUIRE CURRENT” while creating or altering the user. This topic was already explained very well by Brain Sumpter in his post, MySQL 8: Password Verification Policy. I recommend you read it to get to know more about the “Password Verification-Required policy.” In my case, I just enabled the parameter “password_require_current” to enforce the “Password Verification-Required Policy” globally.

percona labs MySQL 8.0.34 > set persist password_require_current = 1;
Query OK, 0 rows affected (0.05 sec)

percona labs MySQL 8.0.34 > select @@password_require_current;
+----------------------------+
| @@password_require_current |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

Once we enable the “password_require_corrent” option, we should provide the old password in the REPLACE clause. Otherwise, it will not allow you to change the password. You will get the following error:

percona labs MySQL 8.0.34 > alter user 'test'@'localhost' identified by 'Test@321';
ERROR 3892 (HY000): Current password needs to be specified in the REPLACE clause in order to change it.

Note: The user with global CREATE USER and UPDATE privileges on “mysql” system database can still change the password without specifying the current password.

Creating a test environment

I have installed the MySQL 8.0.34 version in my test server and installed the “validate_password” component.

percona labs MySQL 8.0.34 > select @@version, @@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment            |
+-----------+------------------------------+
| 8.0.34    | MySQL Community Server - GPL |
+-----------+------------------------------+
1 row in set (0.00 sec)

percona labs MySQL 8.0.34 > INSTALL COMPONENT 'file://component_validate_password';
Query OK, 0 rows affected (0.00 sec)

percona labs MySQL 8.0.34 > select @@validate_password.changed_characters_percentage;
+---------------------------------------------------+
| @@validate_password.changed_characters_percentage |
+---------------------------------------------------+
|                                                 0 |
+---------------------------------------------------+
1 row in set (0.00 sec)

I recommend installing the “validate_password” component instead of the “validate_password” plugin (deprecated). You might not see this feature when installing it as a plugin.

Testing “changed_characters_percentage”

I have set the changed_characters_percentage value to 50. This means whenever the user tries to reset the password, the new password should not contain 50% of any of the old characters.

percona labs MySQL 8.0.34 > set global validate_password.changed_characters_percentage=50;
Query OK, 0 rows affected (0.00 sec)

percona labs MySQL 8.0.34 > select @@validate_password.changed_characters_percentage;
+---------------------------------------------------+
| @@validate_password.changed_characters_percentage |
+---------------------------------------------------+
|                                                50 |
+---------------------------------------------------+
1 row in set (0.00 sec)

Then, I created the user “percona1” with the password “Percona@321”.

percona labs MySQL 8.0.34 > create user 'percona1'@'localhost' identified by 'Percona@321';
Query OK, 0 rows affected (0.00 sec)

percona labs MySQL 8.0.34 > grant select on *.* to 'percona1'@'localhost';
Query OK, 0 rows affected (0.00 sec)

percona labs MySQL 8.0.34 > flush privileges;
Query OK, 0 rows affected (0.01 sec)

Now, let’s try to change the password to “Percona@567”.

percona labs MySQL 8.0.34 > select user();
+--------------------+
| user()             |
+--------------------+
| percona1@localhost |
+--------------------+
1 row in set (0.00 sec)

percona labs MySQL 8.0.34 > alter user percona1@localhost identified by 'Percona@567' replace 'Percona@321';
ERROR 4165 (HY000): The new password must have at least '5' characters that are different from the old password. It has only '3' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal.

It is not allowing me to change the password from “Percona@321” to “Percona@567” and the error explains the situation pretty clearly. I have a password with 11 characters and my new password only has three character differences ( Percona@321 to Percona@567 ). As per my “changed_characters_percentage” value, the new password should contain 50% new characters. This means my new password should have at least five different characters. So, the new password does not meet the requirement.

Now, let’s try with another new password, “Percona%#567”. It has five characters different from the previous password.

percona labs MySQL 8.0.34 > alter user percona1@localhost identified by 'Percona%#567' replace 'Percona@321';
Query OK, 0 rows affected (0.01 sec)

It works as it meets the requirement!

How does it work with UPPER/LOWER case letters?

To explain this situation, I have created another user, “percona2” with the password “PERCONa@321”.

percona labs MySQL 8.0.34 > create user 'percona2'@'localhost' identified by 'PERCONa@321';
Query OK, 0 rows affected (0.00 sec)

The password has 11 characters. So, we have to make at least five character changes in the new password. I will update the password from “PERCONa@321” to “perconA@321”. In this case, I will change seven characters from upper-lower and lower-upper cases. 

percona labs MySQL 8.0.34 > alter user percona2@localhost identified by 'perconA@321' replace 'PERCONa@321';
ERROR 4165 (HY000): The new password must have at least '5' characters that are different from the old password. It has only '0' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal.

Not working. It cannot change because the UPPER and LOWER case letters are considered equal.

How does it work with different character counts?

To test this scenario, I have created a user “percona3” with the password “Percona@321”. We can test the following scenarios.

  • More existing characters
  • More non-existing characters

More existing characters

To test this, I will change the password from “Percona@321” to “Percona@3213333333”. (Just adding seven “3” characters in the existing password ).  

percona labs MySQL 8.0.34 > select user();
+--------------------+
| user()             |
+--------------------+
| percona3@localhost |
+--------------------+
1 row in set (0.00 sec)

percona labs MySQL 8.0.34 > alter user percona3@localhost identified by 'Percona@3213333333' replace 'Percona@321';
ERROR 4165 (HY000): The new password must have at least '5' characters that are different from the old password. It has only '0' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal.

The error reports “0” characters difference because we added seven new characters in the password. But, the character (3) already exists in the password “Percona@3213333333”. In this case, it is not acceptable.

More non-existing characters

To test this, I am now changing the password from “Percona@321” to “Percona@3214455667788”. So, in this case, I am adding ten new characters to the existing password. But, I have five non-existing characters (4,5,6,7,8).

percona labs MySQL 8.0.34 > alter user percona3@localhost identified by 'Percona@3214455667788' replace 'Percona@321';
Query OK, 0 rows affected (0.01 sec)

It works!

So, from the above two examples, the password length may differ. But, it should meet the percentage of the changed characters.

Conclusion

MySQL 8 has a lot of security improvements and new implementations, and I will say this feature is really nice to improve password validations and add more security when changing the users’ passwords.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

 

Try Percona Distribution for MySQL today!

Jul
03
2023
--

PMM Is Getting a Modernized Enterprise-Grade Foundation

With Enterprise Linux 7 nearing its end-of-life date, the Percona Monitoring and Management (PMM) team has done a significant update to the base operating system we build our images on top of.  For several years now, PMM has been built on an Enterprise Linux 7 (EL7) base, specifically CentOS 7.  Even though it provided a stable platform to build on, it’s become outdated. More than anything, we believe in taking a proactive approach to modernization and security and didn’t want to wait until the last minute.

Starting with PMM 2.38.0, we’re making PMM publicly available on a newer base operating system based on Enterprise Linux 9 (EL9), specifically Oracle Linux 9.  In addition to more modern libraries to build on and better compatibility with newer technologies, we also get access to faster upstream responses to issues, including security related.

EL9-based PMM will now be the default installation option, and we will no longer be building docker containers, AMIs, or OVFs based on EL7.  As awesome as this update is, some technical limitations currently prevent us from simply upgrading everyone with the click of a button; the team is working through how best to get everyone there.  But we didn’t want those limitations to prevent adoption now.  We wanted to get this out as quickly as possible because a large population will be able to take advantage immediately as we work to unlock access for everyone else in future releases. 

We’re taking a phased approach to the rollout, but rest assured, we’ll continue to provide a UI upgrade to the latest version of PMM for those users that won’t immediately be able to upgrade to the EL9-based PMM.  

Automatic migration paths

  • Brand new installations of PMM, regardless of how you choose to run it
    • Docker containers from our docker hub: percona/pmm-server:2
    • OVF: Download directly from percona.com 
    • AMI: Loaded from AWS Marketplace
  • Docker/Podman users that do full container replacement upgrades to newer versions of PMM
    • Your existing docker pull or docker run commands will use the new image

If you only upgrade PMM using the UI’s “Upgrade Now” button, you’ll have to wait a little longer for an automated migration path (there are options, but they’re manual and will involve some downtime for your PMM server).  This includes:

  • AMI users
  • OVF users
  • Docker container users relying exclusively on the UI upgrade

For those in the latter group, we are working on mechanisms to support you as well, but there’s technical nuance in each situation that I won’t bore you with here (but ping me if you want to learn more because it’s a fascinating discussion).  If you’re super impatient and don’t want to wait for us, there’s a migration path that I’ll outline at the end.  

If PMM’s use of CentOS 7 has prevented you from using the most popular open source monitoring and management platform, then here’s a quick-start guide that will get you going in seconds!  So enjoy your new features and a more secure PMM!

Manual options

Don’t meet the criteria of the group that can take advantage immediately?  Here are some guides to help you if you don’t want to wait.  There are several ways to do this, but I think these are the most straightforward migration path options for OVF users or those that upgrade exclusively through the UI:

OVF/AMI upgrades

I’m not aware of any way to easily detach a volume from an OVF VM and attach it to another VM (I would love to know if it can be done…cuts out a bunch of steps), but at a high level, this is essentially a DR exercise and works the same for both OVF and AMI upgrades. 

  1. Create a new OVF of PMM server (new) alongside your current OVF-based PMM server (old)
  2. SSH to the old and issue a sudo supervisorctl stop all to quiesce the databases*
  3. SSH to new and issue the same command
  4. Tar and gzip the /srv directory on old with sudo tar -zcvf srv.tar.gz /srv
  5. Secure copy the file from old to new scp srv.tar.gz <username>@new.ovf.ip:~/
  6. SSH to new and move the /srv to a backup location with sudo mv /srv /srv.bak
  7. Extract the archive with cd /; sudo tar zxvf ~/srv.tar.gz
  8. Restart the supervisord on new sudo systemctl restart supervisord`
  9. Switch the IPs between old and new (if your clients are all registered to the server by IP) so that new has the IP previously held by old or update DNS so that the name you registered your clients with now points to the IP of the new OVF**

Metrics should start flowing as soon as the IP comes back up or your DNS TTL expires.  

* If you’re running PMM 2.32.0 or higher for a client, metrics will accumulate on the client automatically up to 1GB before any gaps in metrics appear once complete.
**There’s a known issue that pmm-client doesn’t pick up the DNS change without restarting the client, but vmagent (what actually sends the metrics to the server) will..as soon as you start seeing metrics flow into the new server, you can shut down the old pmm-server and pmm-agent will repoll DNS; all will work again!

UI – Docker

The steps to upgrade PMM running in docker can be found here and will walk you through taking a backup of the data, replacing the container, and restoring the data.  The only caveat here is that you may have passed custom environment variables when you created the PMM container, which can be found by looking at the output of docker inspect <pmm-server> and looking under the Config → Env section. I cheated and looked through my command history using history | grep “docker run” which may work for you too!

Percona customers can reach out to support for assistance in getting to the latest PMM.  If you’re not a Percona customer, I highly recommend becoming one, both because of the value it brings to you having the world’s best database experts at your fingertips and because it enables us to continue to innovate on our suite of free and open source software.  If that’s not an option, you can always reach out for community support on our forums.

Percona Monitoring and Management is a best-of-breed open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.

 

Download Percona Monitoring and Management Today

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com