Home Ленты новостей Планета MySQL
Ðåéòèíã@Mail.ru
Newsfeeds
Planet MySQL
Planet MySQL - https://planet.mysql.com

  • Q & A on Webinar “How Safe is Asynchronous Master-Master Setup in MySQL?”
    First I want to thank everyone who attended my May 21, 2020 webinar “How Safe is Asynchronous Master-Master Setup in MySQL?“. Recording and slides are available on the webinar page. Here are answers to the questions from participants which I was not able to provide during the webinar. Q: What do you generally think of hosting Relational Databases on VM’s as opposed to Bare metals? A: With modern hardware and modern virtual machines this is absolutely possible. I know about many successful high loaded applications that run MySQL on VMs. Just note that running a few VMs on a single physical machine may lead to resource loss rather than saving. For example, MySQL uses the InnoDB buffer pool to optimize operations on InnoDB. This option is essential for performance and should be big enough. Ideally to store whole active working set in memory. Still, MySQL uses memory for other operations. By installing a few MySQL instances on the same physical machine you will have a smaller amount of data in those individual buffer pools than if you’d run a single server. The same reasoning applies to CPU cores which are used to serve active threads. Q: The ORACLE RDBMS currently has an ACTIVE/ACTIVE synchronous tool, called the Active Data Guard, to ensure deduplication amongst other functions. Are there any such tools in production, for MySQL? A: Active Data Guard is a physical replication solution which uses redo log to transfer data: WHITE PAPER/ Oracle (Active) Data Guard 19cHOW DATA GUARD SYNCHRONIZES STANDBYDATABASE(S)A Data Guard configuration includes a production database referred to as the primary database, and up to 30 directly connected replicas referred to as standby databases. Primary and standby databases connect over TCP/IP using Oracle Net Services. There are no restrictions on where the databases are physically located provided they can communicate with each other. A standby database is created from a backup of the primary database without requiring any downtime of the Production application or database. Once a standby database has been created and configured, Data Guard automatically synchronizes the primary database and the standby database by transmitting the primary database redo -the change vector information used by every Oracle Database to protect transactions –as it is generated at the Primary database and applying it to the standby database Oracle (Active) Data Guard19c White Paper Open Source versions of MySQL do not support physical replication. Rather they support only logical replication when updates, stored in the special update log files, called binary logs, are replayed to the slave server. Physical replication is by nature synchronous and will simply stop working if data mismatches. With logical replication, data mismatch does not immediately lead to work interruption. However, if a data mismatch is undesirable, there are few things that you can do. First, think about switching to synchronous replication, such as Galera, InnoDB clusters, or Percona XtraDB Cluster (PXC). If synchronous replication cannot be used, consider the following safety measures for the built-in, asynchronous, replication: Start the slave server with the option [super-]read-only (available in all supported versions of Percona Server) Use row-based replication to avoid data mismatch in case of not safe statements Use GTIDs to prevent applying the same transaction twice Periodically run pt-table-checksum to check if data mismatch happened Fix errors with the help of pt-table-sync Note that physical replication does not support active-active setup. Active Data Guard redirects DML statements to the master node to imitate active-active behavior. For MySQL, when you do not have to write directly to two nodes, you can use ProxySQL for the same purpose. Q: Also there is no equivalent to active data guard in MySQL. Oracle has MySQL utilities called mysqlfailover and mysqlreplicate that allow for automatic failover and fast promotion of former master to slave A: mysqlfailover and mysqlreplicate are part of MySQL Utilities package which is currently deprecated. For failover capabilities, we currently recommend using ProxySQL and Orchestrator. Check also this blog post with details on how to set up both products. Q: What is the best architecture to use master-master replication with active-active mode? A: It depends on your needs. If you can install your nodes close enough to use synchronous replication – do it. If nodes should be geographically distributed, the only option is asynchronous replication. In this case, consider safety recommendations that I discussed at the webinar.

  • Percona Projects for Google Summer of Code – 2020
    We are proud to announce that Percona was selected as a participating organization for the Google Summer of Code (GSoC) 2020 program, this is our second year as a participating org with the GSoC program. GSoC is a great program to involve young student developers in open source projects. We participated in the program in 2019 for the first time and we were really happy and satisfied with the results. Percona Platform Engineering team decided to participate again for the 2020 program and we are glad and really happy to inform you that we were selected and welcome the student to work with our team during the summer of 2020 on their GSoC Project. Preparations We started planning for GSoC around November-December 2019, with the help from our Product Management team, we were able to shortlist a few ideas which we thought were really the right fit for our students, with Google Summer of Code, we realized it is very important to select projects which fit the timeline of the program and justify the purpose of the project for both the student and organization, with the help of our Marketing and HR department were able to prepare a landing page for our potential GSoC Students with all relevant information about projects and communication platforms, from our past year’s experience and observation from other organizations,  we realized most of the students start their preparations right from the mid of January. Since this is just our second year as a participating organization we are really happy with the response we got from students, let’s look at the numbers and compare them with 2019, these numbers are based on org data exported from https://summerofcode.withgoogle.com/ Student and Projects The student intern who will be working with us is Meet Patel, This is the first time for Meet to be selected as a student intern with the GSoC Program. We selected two students for the program but unfortunately, one of our students failed to meet the eligibility criteria of the program and was dropped later. Meet Patel Meet is a 2nd year undergraduate at DAIICT, Gandhinagar, India; pursuing a bachelor’s degree in Information and Communication Technology with a minor in Computational Science. Meet is an open-source enthusiast and an avid developer, who is always excited to learn about new technologies. Meet will work on the GSoC project for the Refactoring of PMM Framework. PMM Framework is an automated testing framework that is used to set up PMM with various databases and their multiple instances, perform load tests and wipe everything after tests are done. One of the major objectives of this project is to make a well-documented script that helps easily set up PMM to the new users as well as refactoring it to make it more usable for internal testing. To track the progress of the project, please follow the GSoC Project Branch. The Percona mentors for the project are Puneet Kala, Frontend/Web QA Automation Engineer, Nailya Kutlubaeva, QA Engineer The GSoC team at Percona is thankful to everyone involved in this year’s application and selection process. We are excited to have a team of mentors helping students learn about our products and working in open source. We’re looking forward to enjoying the two-way dialogue and guiding the students to hone their skills as they experience working on these valuable PMM developments. If you have any questions about GSoC Program please feel free to write to us on gsoc@percona.com The post Percona Projects for Google Summer of Code – 2020 appeared first on Percona Community Blog.

  • Removing Constraint Checks
    Constraint Checks that actually checked the constraints were introduced last year with MySQL 8.0.16 and they really do help you keep bad data out of your database.  I have found them very handy in the past year but today I spotted a question on a website about how to remove a Constraint Check.What is a Constraint Check?It is an integrity check.  In the following example a constraint is set up to make sure the calues of the column 'is' are greater than one hundred.CREATE TABLE c1 (id INT NOT NULL,    CONSTRAINT id_gt_100 CHECK (('id' > 100)));A simple test with a value in range runs perfectly fine.INSERT INTO c1 (id) VALUES (10000);Query OK, 1 row affected (0.0037 sec)But you will receive an error if the value of 'is' is less than  100.INSERT INTO c1 (id) VALUES (10);ERROR: 3819: Check constraint 'id_gt_100' is violated.Remembers it is cheaper and easier to keep bad data out of your databases than it is to correct it later.Stopping the CheckIf you like the check but have some reason to turn it off, say to bulk load some data in a situation where you can clean up the upload later, then use ALTER TABLE to turn off enforcement.ALTER TABLE c1 ALTER CHECK id_gt_100 NOT ENFORCED;Query OK, 0 rows affected (0.0150 sec)Records: 0  Duplicates: 0  Warnings: 0INSERT INTO c1 (id) VALUES (10);Query OK, 1 row affected (0.0038 sec)Or you can remove the constraint check.ALTER TABLE c1 DROP CHECK id_gt_100;Query OK, 0 rows affected (0.0091 sec)Records: 0  Duplicates: 0  Warnings: 0 SHOW CREATE TABLE c1\G*************************** 1. row ***************************       Table: c1Create Table: CREATE TABLE `c1` (  `id` int NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.0005 sec)Further reading  MySQL 8.0.16 Introducing CHECK constraintMySQL Manual 13.1.20.6 CHECK ConstraintsAll opinions expressed in this blog are those of Dave Stokes who is actually amazed to find anyone else agreeing with him

  • Improving MySQL Password Security with Validation Plugin
    In systems nowadays, improving security is a must! One of the weakest links in the security system is the user password from where an attacker can enter. In order to improve password strength and security, MySQL provides a plugin called “Validation plugin” which can be configured to enforce a set of rules for passwords.   Installation The plugin can be enabled by executing the following at runtime:  INSTALL PLUGIN validate_password SONAME 'validate_password.so'; Or by adding the plugin in the configuration file, but this requires bouncing MySQL for it to take effect: [mysqld] plugin-load-add=validate_password.so It’s also suggested to add the following variable in my.cnf so that the plugin cannot be removed at runtime (also requires a MySQL bounce to take effect): [mysqld] validate-password=FORCE_PLUS_PERMANENT Checking Installation You can check if the plugin is installed by either checking plugins: mysql> SHOW PLUGINS \G … *************************** 53. row ***************************    Name: validate_password  Status: ACTIVE    Type: VALIDATE PASSWORD Library: validate_password.so License: GPL Or by checking if the following variables are enabled in MySQL: mysql> show global variables like 'validate%'; +--------------------------------------+--------+ | Variable_name                        | Value  | +--------------------------------------+--------+ | validate_password_check_user_name    | OFF    | | validate_password_dictionary_file    |        | | validate_password_length             | 8      | | validate_password_mixed_case_count   | 1      | | validate_password_number_count       | 1      | | validate_password_policy             | MEDIUM | | validate_password_special_char_count | 1      | +--------------------------------------+--------+ 7 rows in set (0.00 sec)   Usage A short summary of the variables that affect the password is: Validate_password_policy: Determines password strength policy which can be LOW, MEDIUM, or STRONG. Depending on which policy is set, some variables might be ignored. Policy Variables checked 0 or LOW Length 1 or MEDIUM Length; numeric, lowercase/uppercase, and special characters 2 or STRONG Length; numeric, lowercase/uppercase, and special characters; dictionary file Validate_password_length: Minimum number of characters Validate_password_mixed_case_count: Minimum number of upper and lower case characters Validate_password_number_count: Minimum number of numeric characters Validate_password_special_char_count: Minimum number of non-alpha-numeric characters Validate_password_check_user_name: If enabled, prevents a user from setting a password equal to its username or the reverse of it Validate_password_dictionary_file: Path for dictionary file with a maximum size of 1 MB containing one word per line. Any password with a substring that matches any of the words from the dictionary cannot be used, the check is case insensitive, and the minimum length of the dictionary is 4 letters per word. After modifying the dictionary file, “SET GLOBAL Validate_password_dictionary_file = ‘path_to_dictionary’;” must be executed again for the dictionary to be reloaded. Note: After increasing passwords policies, current passwords are not automatically expired and might potentially not fit in the new policy. A password expiration method should be used to force users to set a new password after a fixed number of days or when password policies have changed.   Examples mysql> show global variables like 'validate%'; +--------------------------------------+--------+ | Variable_name                        | Value  | +--------------------------------------+--------+ | validate_password_check_user_name    | OFF    | | validate_password_dictionary_file    |        | | validate_password_length             | 8      | | validate_password_mixed_case_count   | 1      | | validate_password_number_count       | 1      | | validate_password_policy             | MEDIUM | | validate_password_special_char_count | 1      | +--------------------------------------+--------+ 7 rows in set (0.00 sec) Trying to set the following passwords result in an error: mysql> set password = '12345678'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> set password = '123456Ab'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements Password is only accepted if having at least 8 characters, including 1 special character, 1 numeric, 1 lower and 1 uppercase like the following: mysql> set password = '123456Ab@'; Query OK, 0 rows affected (0.00 sec) If setting: mysql> set global validate_password_policy = 'STRONG'; mysql> set global validate_password_dictionary_file = '/tmp/dictionary.txt'; And having the following dictionary file (remember a minimum of 4 letter words) $ cat /tmp/dictionary.txt man1 The following passwords that include case insensitive string “man1” are not accepted: mysql> SET PASSWORD = ‘MAN12345#A'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> SET PASSWORD = 'man12345#A'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> SET PASSWORD = ‘2345man1#A’; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements But the following password is accepted since it complies with all the requirements: mysql> set password = '2345man2#A'; Query OK, 0 rows affected (0.00 sec)   Use cases Each company will have different password requirements depending on their needs and policies, but for some businesses and companies there are already standard in place, such as: PCI/DSS (Payment Card Industry Data Security Standards) which has various policies regarding security, but regarding passwords the requirements are: Require a minimum length of at least seven characters. Contain both numeric and alphabetic characters. Which can be implemented by setting the following: SET GLOBAL validate_password_length = 7; SET GLOBAL validate_password_mixed_case_count = 1; SET GLOBAL validate_password_number_count = 1; SET GLOBAL validate_password_special_char_count = 0; SET GLOBAL validate_password_policy = ‘MEDIUM’; Note: The above settings might seem weak as per nowadays standards, but the above settings are minimum to comply with PCI DSS and stronger policies can be used. Also, PCI/DSS has other requirements (which cannot be tackled down by validation plugin) such as password expiration policies, lockdown after 6 failed attempts, and more, and does not rely solely on password strength. NIST (National Institute of Technology) password minimum requirements only consists of a minimum length of 8 (or 6 for activation passwords) which can be implemented: SET GLOBAL validate_password_length = 8; SET GLOBAL validate_password_policy = ‘LOW’; NIST encourages to use more complex passwords, including special characters and dictionaries containing previously breached passwords, dictionary words, repetitive strings and common patterns such as “aaaa” or “1234” which can be fulfilled with the following config: SET GLOBAL validate_password_length = 8; SET GLOBAL validate_password_mixed_case_count = 1; SET GLOBAL validate_password_number_count = 1; SET GLOBAL validate_password_special_char_count = 1; SET GLOBAL validate_password_dictionary_file = 'dictionary.txt'; SET GLOBAL validate_password_policy = STRONG’; Having a dictionary file with prohibited words. Since the maximum dictionary size is 1 MB, the number of words and patterns that can exist in the dictionary might be limited to the required implementation of policy, although other measures such as password locking after many failures should also be implemented to comply with NIST. OWASP (Open Web Application Security Project) has the following minimal requirements: At least 1 uppercase character (A-Z) At least 1 lowercase character (a-z) At least 1 digit (0-9) At least 1 special character including punctuation marks & spaces Be at least 10 characters long. Do not have more than 2 identical characters in a row (‘aaa’, ‘bbb’, etc..) Compliance with the following configuration: SET GLOBAL validate_password_length = 10; SET GLOBAL validate_password_mixed_case_count = 1; SET GLOBAL validate_password_number_count = 1; SET GLOBAL validate_password_special_char_count = 1; SET GLOBAL validate_password_dictionary_file = 'dictionary.txt'; SET GLOBAL validate_password_policy = STRONG’; And having populated the dictionary with all possible combinations of consecutive characters. Conclusion The validation plugin can greatly help in enforcing passwords policies for increased system security and can be set up in a fast and easy way, but there should also be other security mechanisms to complement validation plugin such as: – password expiration – add delay to successive login retries – limit user attempts and lock account (connection control plugin + lock account) – secure password transmission – password reuse policy

  • SQL Query Formatting Tools Used At Percona
    Percona engineers often need to analyze and review complex SQL database queries. Good formatting can make these SQL queries much easier to understand and work with. Without clear formatting, queries can become confusing and hard to debug. Online query formatting services provide one set of solutions. Examples are Code Beautify, FreeFormatter, and sqlformat.org. However, many users are not comfortable sharing their queries with third-party services, especially if your SQL code contains confidential information. This article examines alternatives to online tools for SQL query formatting tools that have been successfully used by Percona engineers. These solutions come in different types: Plug-ins to your code editor or IDEs. Database Management tools Terminal Tools Let’s examine these options. Code editor or IDE Most modern code editors have functionality or plug-ins for formatting queries. We checked Visual Studio Code and Sublime because these popular code editors are available for Linux, Mac, and Windows. Visual Studio Code knows how to format SQL queries, as well as available Extensions for working with databases, for example, “SQLTools – Database tools”. Sublime does not have SQL formatting as standard functionality, but it is easy to add it through Package Control. Several packages are available for Query formatting, and we used Sql​Beautifier. In this case, you need to copy the request to the code editor and save it to a file. It turned out that most of the Percona team use console tools.   DBA tools Special database management and monitoring software also have functionality for query formatting. MySQL Workbench MySQL Workbench enables a DBA or developer to visually, generate, and manage databases. The Workbench allows you to format an SQL query into a new SQL tab and click the “Beautify/reformat the SQL script” button.   Percona Monitoring and Management (PMM) Percona Monitoring and Management (PMM) is a free, best-of-breed, open-source database monitoring and management solution. PMM has the useful functionality of PMM Query Analytics, which allows you to immediately view requests in formatted form.   Terminal tools The most popular console tool for formatting queries in our team is sqlparse (sqlformat). The developers of the popular online service SQLFormat were very kind and provided the source code of their excellent tool (sqlparse). This simple and useful tool you can use on different platforms. Docker If you love Docker and do not want to install the tool on your system. Percona Support Engineer Agustín Gallego has packed the data script into a docker. Here is a simple instruction on how you can use it. shell> docker pull guriandoro/sqlparse:0.3.1 shell> docker run --rm \ > --network=none \ > guriandoro/sqlparse:0.3.1 "SELECT several, columns from a_table as a join another_table as b where a.id = 1;" SELECT several, columns FROM a_table AS a JOIN another_table AS b WHERE a.id = 1; Cool, right? 🙂 Ubuntu It’s very simple, install the sqlformat package. # apt install sqlformat $ echo "SELECT several, columns from a_table as a join another_table as b where a.id = 1;" | sqlformat - -r -k upper SELECT several, columns FROM a_table AS a JOIN another_table AS b WHERE a.id = 1; Mac Install sqlparse using brew or another way to use the command sqlformat. # brew install sqlparse # echo "SELECT several, columns from a_table as a join another_table as b where a.id = 1;" |  sqlformat - -r -k upper SELECT several,        columns FROM a_table AS a JOIN another_table AS b WHERE a.id = 1; VIM And if you love and use VIM, you can also install sqlformat and use it. vmap <leader>sql :%!sqlformat - -r -k upper<CR> Conclusion We looked at Sqlparse and Sqlformat tools and their use on Linux, Mac, or Windows. We would like to note that it is very convenient for developers to use the Code Editor or IDE, especially if you become a regular user. Additionally, specialized database software such as MySQL Workbench or PMM can help with query formatting. These are SQL query formatting tools that the Percona team has utilized successfully. Share your experience with these or other tools here in the blog comments.

Баннер
© 2020 0A1.RU Ключевые моменты CMS Joomla!. Все права защищены.
Joomla! — свободное программное обеспечение, распространяемое по лицензии GNU/GPL.