MySQL

Zero Downtime SaaS Provider — How to Easily Deploy MySQL Clusters in AWS and Recover from Multi-Zone AWS Outages This is the second post in a series of blogs in which we cover a number of different Continuent Tungsten customer use cases that center ... [More] around achieving continuous MySQL operations with commercial-grade high availability (HA), geographically redundant disaster recovery (DR) and global scaling – and how we help our customers achieve this. This use case looks at a multi-year (since 2012) Continent customer who is a large Florida-based SaaS provider dealing with sensitive (HIPAA Compliant) medical data, which offers electronic health records, practice management, revenue cycle management and data analytics for thousands of doctors. What is the Challenge? Lack of high availability in AWS. The challenge they were facing came from using AWS, which allowed them to rapidly provision database and application servers – BUT the instances, underlying storage, and management interface were not highly available. What is the Solution? By using Tungsten Clustering, they could and can quickly deploy large numbers of Tungsten MySQL clusters in AWS and recover from multi-zone AWS outages. The solution uses the Composite Active/Passive Tungsten Clustering topology – a Pod Architecture with multiple 3-node Active Clusters (for High Availability) and 3-node Passive Clusters (for Disaster Recovery). The Pod Architecture provides practically infinite scalability for the SaaS providers as they can just keep adding new Pods when and as their customer base grows. Each Pod includes a 3-node Tungsten Cluster deployed in multi-AZ AWS, and a 3-node DR cluster deployed in another AWS Region with all sensitive traffic encrypted in flight (this covers application traffic as well). What are the Benefits? The benefits this SaaS provider customer is able to reap from our solution include continuous operations, high availability, scalability and better data protection. One very important item, often overlooked, is that beside the excellent MySQL HA/DR/Geo-clustering software we offer, Continuent also helps sustain our customer’s continuous operations with our 24/7/365 support. Our average response time for urgent support requests has been less than three (3) minutes during the past two years. All requests are handled by highly qualified MySQL experts with decades of operational experience with business-critical deployments. This use case, like the others we’re covering in this blog series, clearly showcases how our solutions and best practices can help customers who manage business-critical MySQL environments achieve the availability, scalability and safe operations with fast response times they are looking for. Related Resources Use Case Blog: Geo-Scale MySQL for Continuous Global Operations & Fast Response Times Technical Product Blog: Global Read-Scaling for MySQL / MariaDB / Percona Server using Tungsten Clustering Technical Blog: How To Architect MySQL to be Available No Matter What Technical Blog: How To Architect MySQL for Zero Downtime Maintenance About Tungsten Clustering Tungsten Clustering allows enterprises running business-critical MySQL database applications to cost-effectively achieve continuous operations with commercial-grade high availability (HA), geographically redundant disaster recovery (DR) and global scaling. To find out more, visit our Tungsten Clustering product page. [Less]

MySQL has been ranked as the second most popular database since 2012 according to DB-Engines. Three features help it retain its top position: replication, storage engines, and NoSQL support. During this webinar, we’ll discuss the MySQL architecture ... [More] surrounding these features and how to utilize their full power when your application hits performance or design limits. This webinar is geared towards new MySQL users as well as those with other database administration experience. However, it’s also useful for experienced users looking to refresh their knowledge. Please join Sveta Smirnova on Thurs, Dec 19, 10 to 11 am PST. Register Now If you can’t attend, sign up anyways we’ll send you the slides and recording afterward. [Less]

Andreas Andreakis, Ioannis Papapanagiotou Continue reading on Netflix TechBlog »

This webinar will cover the advantages and process for migrating from MariaDB/Galera cluster to MySQL InnoDB Cluster.

Relational DBMSs allow to grant users permissions on certain tables or columns. Here we'll discuss how to restrict access to a certain set of rows.

Database administrators are responsible for maintaining the privacy and integrity of data. When the data contains confidential information, your company has a legal obligation to ensure that privacy is maintained. Even so, being able to access the ... [More] information contained in that dataset, for example for testing or reporting purposes, has great value so what to do? MySQL Enterprise Edition offers data masking and de-identification, so I decided to contribute similar functionality to Percona Server for MySQL. In this post, I provide some background context and information on how to use these new functions in practice. Some context One of the most important assets of any company is data. Having good data allows engineers to build better systems and user experiences. Even through our most trivial activities, we continuously generate and share great volumes of data. I’m walking down the street and if I take a look at my phone it’s quite straightforward to get recommendations for a place to have lunch. The platform knows that it’s almost lunch time and that I have visited this nearby restaurant, or a similar one, a few times in the past. Sounds cool, right? But this process could be more manual than we might think at first. Even if the system has implemented things like AI or Machine Learning, a human will have validated the results; they might have taken a peek to ensure that everything is fine; or perhaps they are developing some new cool feature that must be tested… And this means that someone, somewhere has the ability to access my data. Or your data. Now, that is not so great, is it? In the last decade or so, governments around the world have taken this challenge quite seriously. They have enforced a series of rules to guarantee that the data is not only safely stored, but also safely used. I’m sure you will have heard terms like PCI, GDPR or HIPAA. They contain mandatory guidelines for how our data can be used, for primary or secondary purposes, and if it can be used at all. Data masking and de-identification One of the most basic safeguarding rules is that if the data is to be used for secondary purposes – such as for data analytics – it has to be de-identified in a way that it would make impossible identify the original individual. Let’s say that the company ACME is storing employee data. We will use the example database of employees that’s freely available. Employee number First name Last name Birth date Gender Hire date Gross salary Salary from date Salary to date We can clearly see that all those fields can be classified as private information. Some of these directly identify the original individual, like employee number or first + last name. Others could be used for indirect identification: I could ask my co-workers their birthday and guess the owner of that data using birth date. So, here is where de-identification and data-masking come into play. But what are the differences? De-identification transforms the original data into something different that could look more or less real. For example, I could de-identify birth date and get a different date. However, this method would make that information unusable if I want to see the relationship between salary and employee’s age. On the other hand, data-masking transforms the original data leaving some part untouched. I could mask birth date replacing the month and day for January first. That way, the year would be retained and that would allow us to identify that salary–employee’s age relationship. Of course, if the dataset I’m working with is not big enough, certain methods of data-masking would be inappropriate as I could still deduce who the data belonged to. MySQL data masking Oracle’s MySQL Enterprise Edition offers a de-identification and data-masking solution for MySQL, using a flexible set of functions that cover most of our needs. Percona Server for MySQL 8.0.17 introduces that functionality as an open source plugin, and is compatible with Oracle’s implementation. You no longer need to code slow and complicated stored procedures to achieve data masking, and you can migrate the processes that were written for the MySQL Enterprise Edition to Percona Server for MySQL. Go grab a cup of coffee and contribute something cool to the community with all that time you have got back. ☺ In the lab Put on your thinking cap and let’s see how it works. First we need an instance of Percona MySQL Server 8.0.17 or newer. I think containers are the most flexible way to test new stuff so I will be using that, but you could use a virtual server or just a traditional setup. Let’s download the latest version of Percona MySQL Server in a ready to run container: docker pull percona:8.0.17-1 Eventually that command should work but sadly, Percona hadn’t built this version of the docker image when this article was written. Doing it yourself is quite simple, though, and by the time you read this it will likely be already there. Once in place, Running an instance of Percona MySQL Server has never been so easy: docker run --name ps -e MYSQL_ROOT_PASSWORD=secret -d percona:8.0.17-8 We’ll logon to the new container: docker exec -ti ps mysql -u root -p Now is the time to download the test database employees from GitHub and load it into our Percona Server. You can follow the official instructions in the project page. Next step is to enable the data de-identification and masking feature. Installing the data masking module in Percona MySQL Server is easier than in Oracle. mysql> INSTALL PLUGIN data_masking SONAME 'data_masking.so'; Query OK, 0 rows affected (0.06 sec) This automatically defines a set of global functions in our MySQL instance, so we don’t need to do anything else. A new concept: Dictionaries Sometimes we will like to generate new data selecting values from a predefined collection. For example we could want to have first name  values that are really first names and not a random alphanumeric. This will make our masked data looks real, and it’s perfect for creating demo or QA environments. For this task we have dictionaries. They are nothing more than text files containing a value per line that are loaded into MySQL memory. You need to be aware that the contents of the file are fully loaded into memory and that the dictionary only exists while MySQL is running. So keep this in mind before loading any huge file or after restarting the instance. For our lab we will load two dictionaries holding first and last names. You can use these files or create different ones: first names and last names Store the files in a folder of your database server (or container) readable by the mysqld  process.wget https://raw.githubusercontent.com/philipperemy/name-dataset/master/names_dataset/first_names.all.txt docker cp first_names.all.txt ps:/tmp/ wget https://raw.githubusercontent.com/philipperemy/name-dataset/master/names_dataset/last_names.all.txt docker cp last_names.all.txt ps:/tmp/ Once the files are in our server we can map them as MySQL dictionaries. mysql> select gen_dictionary_load('/tmp/first_names.all.txt', 'first_names'); +----------------------------------------------------------------+ | gen_dictionary_load('/tmp/first_names.all.txt', 'first_names') | +----------------------------------------------------------------+ | Dictionary load success                                        | +----------------------------------------------------------------+ 1 row in set (0.04 sec) mysql> select gen_dictionary_load('/tmp/last_names.all.txt', 'last_names'); +--------------------------------------------------------------+ | gen_dictionary_load('/tmp/last_names.all.txt', 'last_names') | +--------------------------------------------------------------+ | Dictionary load success                                      | +--------------------------------------------------------------+ 1 row in set (0.03 sec) Masking some data Now let’s take another look at our employees tablemysql> show columns from employees; +------------+---------------+------+-----+---------+-------+ | Field      | Type   | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+-------+ | emp_no     | int(11)   | NO | PRI | NULL    | | | birth_date | date          | NO | | NULL   | | | first_name | varchar(14)   | NO | | NULL   | | | last_name  | varchar(16)   | NO | | NULL   | | | gender     | enum('M','F') | NO   | | NULL   | | | hire_date  | date   | NO | | NULL    | | +------------+---------------+------+-----+---------+-------+ Ok, it’s very likely we will want to de-identify everything in this table. You can apply different methods to achieve your security requirements, but I will create a view with the following transformations: emp_no: get a random value from 900.000.000 to 999.999.999 birth_date: set it to January 1st of the original year first_name: set a random first name from a list of names that we have in a text file last_name: set a random last name from a list of names that we have in a text file gender: no transformation hire_date: set it to January 1st of the original year CREATE VIEW deidentified_employees AS SELECT   gen_range(900000000, 999999999) as emp_no,   makedate(year(birth_date), 1) as birth_date,   gen_dictionary('first_names') as first_name,   gen_dictionary('last_names') as last_name,   gender,   makedate(year(hire_date), 1) as hire_date FROM employees; Let’s check how the data looks in our de-identified view. mysql> SELECT * FROM employees LIMIT 10; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date  | +--------+------------+------------+-----------+--------+------------+ |  10001 | 1953-09-02 | Georgi     | Facello | M | 1986-06-26 | |  10002 | 1964-06-02 | Bezalel    | Simmel | F | 1985-11-21 | |  10003 | 1959-12-03 | Parto      | Bamford | M | 1986-08-28 | |  10004 | 1954-05-01 | Chirstian  | Koblick | M | 1986-12-01 | |  10005 | 1955-01-21 | Kyoichi    | Maliniak | M | 1989-09-12 | |  10006 | 1953-04-20 | Anneke     | Preusig | F | 1989-06-02 | |  10007 | 1957-05-23 | Tzvetan    | Zielinski | F | 1989-02-10 | |  10008 | 1958-02-19 | Saniya     | Kalloufi | M | 1994-09-15 | |  10009 | 1952-04-19 | Sumant     | Peac | F | 1985-02-18 | |  10010 | 1963-06-01 | Duangkaew  | Piveteau | F | 1989-08-24 | +--------+------------+------------+-----------+--------+------------+ 10 rows in set (0.00 sec) mysql> SELECT * FROM deidentified_employees LIMIT 10; +-----------+------------+------------+---------------+--------+------------+ | emp_no    | birth_date | first_name | last_name     | gender | hire_date | +-----------+------------+------------+---------------+--------+------------+ | 930277580 | 1953-01-01 | skaidrīte  | molash | M | 1986-01-01 | | 999241458 | 1964-01-01 | grasen     | cessna | F | 1985-01-01 | | 951699030 | 1959-01-01 | imelda     | josephpauline | M | 1986-01-01 | | 985905688 | 1954-01-01 | dunc       | burkhardt | M | 1986-01-01 | | 923987335 | 1955-01-01 | karel      | wanamaker | M | 1989-01-01 | | 917751275 | 1953-01-01 | mikrut     | allee | F | 1989-01-01 | | 992344830 | 1957-01-01 | troyvon    | muma | F | 1989-01-01 | | 980277046 | 1958-01-01 | aliziah    | tiwnkal | M | 1994-01-01 | | 964622691 | 1952-01-01 | dominiq    | legnon | F | 1985-01-01 | | 948247243 | 1963-01-01 | sedale     | tunby | F | 1989-01-01 | +-----------+------------+------------+---------------+--------+------------+ 10 rows in set (0.01 sec) The data looks quite different, but remains good enough to apply some analytics and get meaningful results. Let’s de-identify the table salaries  this time.mysql> show columns from salaries; +-----------+---------+------+-----+---------+-------+ | Field     | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+-------+ | emp_no    | int(11) | NO   | PRI | NULL |       | | salary    | int(11) | NO   | | NULL |       | | from_date | date    | NO | PRI | NULL |       | | to_date   | date | NO   | | NULL |       | +-----------+---------+------+-----+---------+-------+ We could use something like this: CREATE VIEW deidentified_salaries AS SELECT gen_range(900000000, 999999999) as emp_no, gen_range(40000, 80000) as salary, mask_inner(date_format(from_date, '%Y-%m-%d'), 4, 0) as from_date, mask_outer(date_format(to_date, '%Y-%m-%d'), 4, 2, '0') as to_date FROM salaries; We are using again the function gen_range . For the dates this time we are using the very flexible functions mask_inner  and mask_outer  that replace some characters in the original string. Let’s see how the data looks now. In a real life exercise we would like to have the same values for emp_no across all the tables to keep referential integrity. This is where I think the original MySQL data-masking plugin falls short, as we don’t have deterministic functions using the original value as seed. mysql> SELECT * FROM salaries LIMIT 10; +--------+--------+------------+------------+ | emp_no | salary | from_date  | to_date | +--------+--------+------------+------------+ |  10001 |  60117 | 1986-06-26 | 1987-06-26 | |  10001 |  62102 | 1987-06-26 | 1988-06-25 | |  10001 |  66074 | 1988-06-25 | 1989-06-25 | |  10001 |  66596 | 1989-06-25 | 1990-06-25 | |  10001 |  66961 | 1990-06-25 | 1991-06-25 | |  10001 |  71046 | 1991-06-25 | 1992-06-24 | |  10001 |  74333 | 1992-06-24 | 1993-06-24 | |  10001 |  75286 | 1993-06-24 | 1994-06-24 | |  10001 |  75994 | 1994-06-24 | 1995-06-24 | |  10001 |  76884 | 1995-06-24 | 1996-06-23 | +--------+--------+------------+------------+ 10 rows in set (0.00 sec) mysql> SELECT * FROM deidentified_salaries LIMIT 10; +-----------+--------+------------+------------+ | emp_no    | salary | from_date  | to_date | +-----------+--------+------------+------------+ | 929824695 | 61543  | 1986XXXXXX | 0000-06-00 | | 954275265 | 63138  | 1987XXXXXX | 0000-06-00 | | 948145700 | 53448  | 1988XXXXXX | 0000-06-00 | | 937927997 | 54704  | 1989XXXXXX | 0000-06-00 | | 978459605 | 78179  | 1990XXXXXX | 0000-06-00 | | 993464164 | 75526  | 1991XXXXXX | 0000-06-00 | | 946692434 | 51788  | 1992XXXXXX | 0000-06-00 | | 979870243 | 54807  | 1993XXXXXX | 0000-06-00 | | 958708118 | 70647  | 1994XXXXXX | 0000-06-00 | | 945701146 | 76056  | 1995XXXXXX | 0000-06-00 | +-----------+--------+------------+------------+ 10 rows in set (0.00 sec) Clean-up Remember that when you’re done, you can free up memory by removing the dictionaries. Restarting the instance will also remove the dictionaries. mysql> SELECT gen_dictionary_drop('first_names'); +------------------------------------+ | gen_dictionary_drop('first_names') | +------------------------------------+ | Dictionary removed                 | +------------------------------------+ 1 row in set (0.01 sec) mysql> SELECT gen_dictionary_drop('last_names'); +------------------------------------+ | gen_dictionary_drop('last_names') | +------------------------------------+ | Dictionary removed                 | +------------------------------------+ 1 row in set (0.01 sec) If you use the MySQL data-masking plugin to define different levels of access to the data, remember that you will need to load the dictionaries each time the instance is restarted. With this usage, for example, you could control the data that someone in support has access to, very much like a bargain-basement virtual private database solution. (I’m not proposing this for production systems!) Other de-identification and masking functions Percona Server for MySQL Data-Masking includes more functions that the ones we’ve seen here. We have specialized functions for Primary Account Numbers (PAN), Social Security Numbers (SSN), phone numbers, e-Mail addresses… And also generic functions that will allow us to de-identify types without a specialized method. Being an open source plugin it should be quite easy to implement any additional methods and contribute it to the broader community. Next Steps Using these functions we can de-identify and mask any existing dataset. But if you are populating a lower level environment using production data you would want to store the transformed data only. To achieve this you could choose between various options. Small volumes of data: use “de-identified” views to export the data and load into a new database using mysqldump or mysqlpump. Medium volumes of data: Clone the original database and de-identify locally the data using updates. Large volumes of data option one: using replication, create a master -> slave chain with STATEMENT binlog format and define triggers de-identifying the data on the slave. Your master can be a slave to the master (using log_slave_updates), so you don’t need to run your primary master in STATEMENT mode. Large volumes of data option two: using multiplexing in ProxySQL, configure ProxySQL to send writes to a clone server where you have defined triggers to de-identify the data. Future developments While de-identifying complex schemas we could find that, for example, the name of a person is stored in multiple tables (de-normalized tables). In this case, these functions would generate different names and the resulting data will look broken. You can solve this using a variant of the dictionary functions that will obtain the value based on the original value and passed as parameter: gen_dictionary_deterministic('Francisco', 'first_names') This not-yet-available function would always return the same value using that dictionary file, but in such a way that the de-identification cannot be reversed. Oracle doesn’t currently support this, so we will expand Percona Data-Masking plugin to introduce this as a unique feature. However, that will be in another contribution, so stay tuned for more exciting changes to Percona Server for MySQL Data Masking. —Image: Photo by Finan Akbar on Unsplash The content in this blog is provided in good faith by members of the open source community. Percona has not edited or tested the technical content (although in this case, of course, we have tested the data masking feature incorporated into Percona Server for MySQL 8.0/17, just not the examples in this blog). Views expressed are the authors’ own. When using the advice from this or any other online resource test ideas before applying them to your production systems, and always secure a working back up. The post Percona Server for MySQL 8.0 – New Data Masking Feature appeared first on Percona Community Blog. [Less]

See some update at the end: 23 Dec 2019 MySQL and PHP is a love story that started long time ago. However the love story with MySQL 8.0 was a bit slower to start… but don’t worry it rules now ! The support of MySQL 8.0’s new default ... [More] authentication method in PHP took some time and was added in PHP 7.2.8 but removed in PHP 7.2.11. Now it’s fully supported in PHP 7.4 ! If you have installed PHP 7.4, you can see that the new plugin auth_plugin_caching_sha2_passwordis now available: # php -i | grep "Loaded plugins\|PHP Version " | tail -n2 PHP Warning: Module 'mysql_xdevapi' already loaded in Unknown on line 0 PHP Version => 7.4.0 Loaded plugins => mysqlnd,debug_trace,auth_plugin_mysql_native_password, auth_plugin_mysql_clear_password, auth_plugin_caching_sha2_password, auth_plugin_sha256_password So no need to create a user with mysql_native_passwordas authentication method in MySQL 8.0 as explained in the following posts: https://lefred.be/content/mysql-8-0-17-and-drupal-8-7/ https://lefred.be/content/migrating-to-mysql-8-0-for-wordpress-episode-1/ https://lefred.be/content/drupal-and-mysql-8-0-11-are-we-there-yet/ https://lefred.be/content/joomla-and-mysql-8-0-12/ https://lefred.be/content/mysql-8-0-and-magento/ In summary, if you want to use a more secure method to connect to your MySQL 8.0 form your PHP application, make sure you upgrade to PHP 7.4 Update: The drawback of this new mysqli.so is that if you don’t modify the php.ini and by adding a value to mysqli.default_socket, when you try to connect to MySQL on localhost without specifying the socket path, the connection will fail with the following message: PHP Warning: mysqli::__construct(): (HY000/2002): No such file or directory in .php on line 45 An error occurred when trying to establish a connection to the database: Error #2002 You have then 2 solutions: add in php.ini a default value for mysqli.default_socket or specify the socket path when you initiate the connection to MySQL using mysqli in your code This is also the default behavior on Ubuntu when using PHP 7.4 from ppa:ondrej/php repository. [Less]

MySQL and PHP is a love story that started long time ago. However the love story with MySQL 8.0 was a bit slower to start… but don’t worry it rules now ! The support of MySQL 8.0’s new default authentication method in PHP took some time and was ... [More] added in PHP 7.2.8 but removed in PHP 7.2.11. Now it’s fully supported in PHP 7.4 ! If you have installed PHP 7.4, you can see that the new plugin auth_plugin_caching_sha2_passwordis now available: # php -i | grep "Loaded plugins\|PHP Version " | tail -n2 PHP Warning: Module 'mysql_xdevapi' already loaded in Unknown on line 0 PHP Version => 7.4.0 Loaded plugins => mysqlnd,debug_trace,auth_plugin_mysql_native_password, auth_plugin_mysql_clear_password, auth_plugin_caching_sha2_password, auth_plugin_sha256_password So no need to create a user with mysql_native_passwordas authentication method in MySQL 8.0 as explained in the following posts: https://lefred.be/content/mysql-8-0-17-and-drupal-8-7/ https://lefred.be/content/migrating-to-mysql-8-0-for-wordpress-episode-1/ https://lefred.be/content/drupal-and-mysql-8-0-11-are-we-there-yet/ https://lefred.be/content/joomla-and-mysql-8-0-12/ https://lefred.be/content/mysql-8-0-and-magento/ In summary, if you want to use a more secure method to connect to your MySQL 8.0 form your PHP application, make sure you upgrade to PHP 7.4 [Less]

MySQL and PHP is a love story that started long time ago. However the love story with MySQL 8.0 was a bit slower to start… but don’t worry it rules now ! The support of MySQL 8.0’s new default authentication method in PHP took some time and was ... [More] added in PHP 7.2.8 but removed in PHP 7.2.11. Now it’s fully supported in PHP 7.4 ! If you have installed PHP 7.4, you can see that the new plugin auth_plugin_caching_sha2_passwordis now available: # php -i | grep "Loaded plugins|PHP Version " | tail -n2 PHP Warning: Module 'mysql_xdevapi' already loaded in Unknown on line 0 PHP Version => 7.4.0 Loaded plugins => mysqlnd,debug_trace,auth_plugin_mysql_native_password, auth_plugin_mysql_clear_password, auth_plugin_caching_sha2_password, auth_plugin_sha256_password So no need to create a user with mysql_native_passwordas authentication method in MySQL 8.0 as explained in the following posts: https://lefred.be/content/mysql-8-0-17-and-drupal-8-7/ https://lefred.be/content/migrating-to-mysql-8-0-for-wordpress-episode-1/ https://lefred.be/content/drupal-and-mysql-8-0-11-are-we-there-yet/ https://lefred.be/content/joomla-and-mysql-8-0-12/ https://lefred.be/content/mysql-8-0-and-magento/ In summary, if you want to use a more secure method to connect to your MySQL 8.0 form your PHP application, make sure you upgrade to PHP 7.4 [Less]

Introduction In this article, we are going to see how a CROSS JOIN works, and we will also make use of this SQL join type to build a poker card game. Database table model For our poker card game application, we have created the ranks and suits ... [More] database tables: The ranks table defines the ranking of cards, as well as the name and symbol used for each card rank: The suits table describes the four possible categories used by the French playing cards: Cartesian product In the set theory, the Cartesian product... Read More The post A beginner’s guide to SQL CROSS JOIN appeared first on Vlad Mihalcea. [Less]