Planet MySQL

SQL Wildcard Characters Example | SQL Wildcard Operators Tutorial

SQL Wildcard Characters Example | SQL Wildcard Operators Tutorial is today’s topic. SQL wildcard characters are used for substituting one or more characters in a string. Wildcard operators or you can say characters are used with LIKE operators. The like operator is used with where clause for searching some specified pattern from a column. If you are unfamiliar with SQL datatype then check out my SQL Datatypes tutorial on this blog.

SQL Wildcard Characters

The Types of SQL wildcard operators are following.
1) %
2) _
3) [char list]

The percent sign represents zero, one or multiple characters. The underscore represents a single number or a character. These symbols can be used in combinations.

Let’s get in brief with all the characters one by one.

% operator in SQL

This operator is used to find any string from a column whose complete string information is unknown to you. Let’s understand this with an example.

Table_1: Employee

Emp_id Emp_name City State Salary 101 Rohit Patna Bihar 30000 201 Shivam Jalandhar Punjab 20000 301 Karan Allahabad Uttar Pradesh 40000 401 Suraj Kolkata West Bengal 60000 501 Akash Vizag Andhra Pradesh 70000

 

Example 1 Select * from employee where city like 'P%'; Output

 

Explanation

This query resulted in all the details of an employee whose city name starts with P.

Example 2 Select * from employee where city like '%a'; Output

 

Explanation

The above query resulted in all the details of an employee where the city name ends with a.

Example 3 select * from employee where city like '%p%'; Output

 

Explanation

The above query resulted in all the details of an employee whose city name have p in any position.

An _ operator in SQL

The _ operator is used as a substitute for one or more characters based on the number of times _ is used. Let’s understand this with an example. See the following table.

Table_1: Employee

Emp_id Emp_name City State Salary 101 Rohit Patna Bihar 30000 201 Shivam Jalandhar Punjab 20000 301 Karan Allahabad Uttar Pradesh 40000 401 Suraj Kolkata West Bengal 60000 501 Akash Vizag Andhra Pradesh 70000

 

Example 1 Select * from employee where city like '_atna'; Output

 

Explanation

The above SQL statements select all employees whose city starts with any character followed by ‘atna’.

Example 2 select * from employee where city like 'Kol_a_a'; Output:

 

Explanation:

The following SQL statement selects all type of employees whose city starts with Kol followed by any character than a followed by a character then at last ends with a.

Operations performed by both % and _ operator

Table_1: Employee

Emp_id Emp_name City State Salary 101 Rohit Patna Bihar 30000 201 Shivam Jalandhar Punjab 20000 301 Karan Allahabad Uttar Pradesh 40000 401 Suraj Kolkata West Bengal 60000 501 Akash Vizag Andhra Pradesh 70000

 

Example 1 Select * from employee where state like '_u%'; Output

 

Explanation

The above query will print the details of an employee whose state name 1st position start with any character followed by u and rest any other characters.

Example 2 select * from employee where state like 'a_%_%'; Output

 

Explanation

The above query will print the details of an employee whose state name 1st position start with A, followed by any other characters but should be of at least three characters in length.

[ char list] operator

The char list operator is used for representing any single character within the brackets.

Suppose, if we declare h[ao]t in a query then the resulting set will consist of either hat as output or hot as output but not hit as i is not present in the brackets.

Let’s clear this with more example.

Consider the following table.

Table_1: Employee

Emp_id Emp_name City State Salary 101 Rohit Patna Bihar 30000 201 Shivam Jalandhar Punjab 20000 301 Karan Allahabad Uttar Pradesh 40000 401 Suraj Kolkata West Bengal 60000 501 Akash Vizag Andhra Pradesh 70000

 

Example 1 select * from employee where city like '[PJA]%'; Output Emp_id Emp_name City State Salary 101 Rohit Patna Bihar 30000 201 Shivam Jalandhar Punjab 20000 301 Karan Allahabad Uttar Pradesh 40000

 

Explanation

The above query will print the details of the employee whose city the first character either starts with ‘P’ or ‘J’ or ‘A’.

Example 2 select * from employee where city like '[!PJA]%';

OR

Select * from employee where city like ‘[^PJA]%’; Output Emp_id Emp_name City State Salary 401 Suraj Kolkata West Bengal 60000 501 Akash Vizag Andhra Pradesh 70000

 

Explanation

The above query will print the details of the employee whose city the first character does not start with ‘P’ or ‘J’ or ‘A’. Here this query was used for fetching non-matching set or range of characters specified inside the brackets.

Example 3 Select * from employee where city like ‘[P-Z]%’; Output Emp_id Emp_name City State Salary 101 Rohit Patna Bihar 30000 501 Akash Vizag Andhra Pradesh 70000

 

Explanation

In this query details of all employee are displayed whose city the first character lies in the range from P to Z. Note: P and Z are inclusive.

#SQL Wildcard Characters

A wildcard character is used to substitute one or more characters in a string.

Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

#Wildcard Characters in MS Access Symbol Description Example * Represents zero or more characters bl* finds bl, black, blue, and blob. ? Represents a single character h?t finds a hot, hat, and hit. [] Represents any single character within the brackets h[oa]t finds hot and hat, but not hit. ! Represents any character, not in the brackets h[!oa]t finds hit, but not hot and hat. – Represents a range of characters c[a-b]t finds cat and cbt. # Represents any single numeric character 2#5 finds 205, 215, 225, 235, 245, 255, 265, 275, 285, and 295. #Wildcard Characters in SQL Server Symbol Description Example % Represents zero or more characters. bl% finds bl, black, blue, and blob. _ Represents a single character. h_t finds a hot, hat, and hit. [] Represents any single character within the brackets. h[oa]t finds hot and hat, but not hit. ^ Represents any character, not in the brackets. h[^oa]t finds hit, but not hot and hat. – Represents a range of characters. c[a-b]t finds cat and cbt.

All the wildcards can also be used in combinations!

Here are some examples showing different LIKE operators with ‘%’ and ‘_’ wildcards:

LIKE Operator Description WHERE CustomerName LIKE ‘a%’ Finds any values that start with “a”. WHERE CustomerName LIKE ‘%a’ Finds any values that end with “a”. WHERE CustomerName LIKE ‘%or%’ Finds any values that have “or” in any position. WHERE CustomerName LIKE ‘_r%’ Finds any values that have “r” in the second position. WHERE CustomerName LIKE ‘a_%_%’ Finds any values that start with “a” and are at least 3 characters in length. WHERE ContactName LIKE ‘a%o’ Finds any values that start with “a” and ends with “o”.

 

Finally, SQL Wildcard Characters Example | SQL Wildcard Operators Tutorial is over.

The post SQL Wildcard Characters Example | SQL Wildcard Operators Tutorial appeared first on AppDividend.

Jun 24: Where you can find MySQL this week

We would like to remind you about the shows where you can find MySQL during next week, Jun 24-Jun30. Please find them below.

  • Devoxx, Krakow, Poland, Jun 24-26, 2019
    • You can find MySQL among other groups (Java, Graal VM, Fn, Linux) at the Oracle booth in the expo area. Do not miss the demos (not only MySQL) we are going to run at our booth.
  • MySQL User Group Frankfurt - MySQL Meetup, Germany, Jun 27, 2019
    • We are happy to invite you to join the MySQL meetup organized by MySQL User Group Frankfurt. This time Carsten Thalheimer, the  MySQL Master Principal Sales consultant is invited as a speaker. He will be talking about "MySQL 8 - The Next big Thing". The meetup starts at 7pm. More details here.

 

    Shinguz: Do not underestimate performance impacts of swapping on NUMA database systems

    If your MariaDB or MySQL database system is swapping it can have a significant impact on your database query performance! Further it can also slow down your database shutdown and thus influence the whole reboot of your machine. This is especially painful if you have only short maintenance windows or if you do not want to spend the whole night with operation tasks.

    When we do reviews of our customer MariaDB or MySQL database systems one of the items to check is Swap Space and swapping. With the free command you can find if your system has Swap Space enabled at all and how much of your Swap Space is used:

    # free total used free shared buff/cache available Mem: 16106252 3300424 697284 264232 12108544 12011972 Swap: 31250428 1701792 29548636

    With the command:

    # swapon --show NAME TYPE SIZE USED PRIO /dev/sdb2 partition 29.8G 1.6G -1

    you can show on which disk drive your Swap Space is physically located. And with the following 3 commands you can find if your system is currently swapping or not:

    # vmstat 1 procs ------------memory------------ ---swap-- -----io---- -system-- ------cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 1 0 1701784 692580 355716 11757864 2 12 685 601 237 146 9 3 86 2 0 0 0 1701784 692472 355720 11757840 0 0 0 196 679 2350 2 1 97 1 0 0 0 1701784 692720 355728 11757332 0 0 0 104 606 2170 0 1 98 1 0 # sar -W 1 15:44:30 pswpin/s pswpout/s 15:44:31 0.00 0.00 15:44:32 0.00 0.00 15:44:33 0.00 0.00 # sar -S 1 15:43:02 kbswpfree kbswpused %swpused kbswpcad %swpcad 15:43:03 29548648 1701780 5.45 41552 2.44 15:43:04 29548648 1701780 5.45 41552 2.44 15:43:05 29548648 1701780 5.45 41552 2.44

    Side note: Recent Linux distributions tend to use Swap Files instead of Swap Partitions. The performance impact seems to be negligible compared to the operational advantages of Swap Files... [ 1 ] [ 2 ] [ 3 ] [ 4 ]

    What is Swap Space on a Linux system

    Modern Operating Systems like Linux manage Virtual Memory (VM) which consists of RAM (fast) and Disk (HDD very slow and SSD slow). If the Operating System is short in fast RAM it tries to write some "old" pages to slow disk to get more free fast RAM for "new" pages and/or for the file system cache. This technique enables the Operating System to keep more and/or bigger processes running than physical RAM is available (overcommitment of RAM).
    If one of those "old" pages is needed again it has to be swapped in which technically is a physical random disk read (which is slow, this is also called a major page fault).
    If this block is a MariaDB or MySQL database block this disk read to RAM will slow down your SELECT queries but also INSERT, UPDATE and DELETE when you do write queries. This can severely slow down for example your clean-up jobs which have to remove "old" data (located on disk possibly in Swap Space).

    Sizing of Swap Space for database systems

    A rule of thumb for Swap Space is: Have always Swap Space but never use it (disk is cheap nowadays)!

    A reasonable Swap Space sizing for database systems is the following:

    Amount of RAM Swap Space 4 GiB of RAM or less a minimum of 4 GiB of Swap Space, is this really a Database server? 8 GiB to 16 GiB of RAM a minimum of once the amount of RAM of Swap Space 24 GiB to 64 GiB of RAM a minimum of half the amount of RAM of Swap Space more than 64 GiB of RAM a minimum of 32 GiB of Swap Space

    If you have a close look at your Swap usage and if you monitor your Swap Space precisely and if you know exactly what you are doing you can lower these values...

    It is NOT recommended to disable Swap Space

    Some people tend to disable Swap Space. We see this mainly in virtualized environments (virtual machines) and cloud servers. From the VM/Cloud administrator point of view I can even understand why they disable Swap. But from the MariaDB / MySQL DBA point of view this is a bad idea.

    If you do proper MariaDB / MySQL configuration (innodb_buffer_pool_size = 75% of RAM) the server should not swap a lot. But if you exaggerate with memory configuration the system starts swapping heavily. Till to the end the OOM-Killer will be activated by your Linux killing the troublemaker (typically the database process). If you have sufficient Swap Space enabled you get some time to detect a bad database configuration and act accordingly. If you have Swap Space disabled completely you do not get this safety buffer and OOM killer will act immediately and kill your database process when you run out of RAM. This really cannot be in the interest of the DBA.

    Some literature to read further about Swap: In defence of swap: common misconceptions

    Influence swapping - Swappiness

    The Linux kernel documentation tells us the following about swappiness:

    swappiness

    This control is used to define how aggressive the kernel will swap memory pages. Higher values will increase aggressiveness, lower values decrease the amount of swap. A value of 0 instructs the kernel not to initiate swap until the amount of free and file-backed pages is less than the high water mark in a zone.

    The default value is 60.

    Source: Documentation for /proc/sys/vm/*

    A informative article on StackExchange: Why is swappiness set to 60 by default?

    To change your swappiness the following commands will help:

    # sysctl vm.swappiness vm.swappiness = 60 # sysctl vm.swappiness=1 # sysctl vm.swappiness vm.swappiness = 1

    To make these changes persistent you have to write it to some kind of configuration file dependent on your Operating System:

    # # /etc/sysctl.d/99-sysctl.conf # vm.swappiness=1
    Who is using the Swap Space?

    For further analysing your Swap Space and to find who is using your Swap Space please see our article MariaDB and MySQL swap analysis.

    What if your system is still swapping? - NUMA!

    If you did everything correctly until here and your system is still swapping you possibly missed one point: NUMA systems behave a bit tricky related to Databases and swapping. The first person who wrote extensively about this problem in the MySQL ecosystem was Jeremy Cole in 2010 in his two well written articles which you can find here:

    What NUMA is you can find here: Non-uniform memory access.

    If you have spent your money for an expensive NUMA system you can find with the following command:

    # lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 56 On-line CPU(s) list: 0-55 Thread(s) per core: 2 Core(s) per socket: 14 Socket(s): 2 NUMA node(s): 2 Vendor ID: GenuineIntel CPU family: 6 Model: 79 Model name: Intel(R) Xeon(R) CPU E5-2690 v4 @ 2.60GHz Stepping: 1 CPU MHz: 2600.000 CPU max MHz: 2600.0000 CPU min MHz: 1200.0000 BogoMIPS: 5201.37 Virtualization: VT-x Hypervisor vendor: vertical Virtualization type: full L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 35840K NUMA node0 CPU(s): 0-13,28-41 NUMA node1 CPU(s): 14-27,42-55

    If you are now in the unfortunate situation of having such a huge box with several sockets you can do different things:

    • Configuring your MariaDB / MySQL database to allocate memory evenly on both sockets with the parameter innodb_numa_interleave. This works since MySQL 5.6.27, MySQL 5.7.9 and MariaDB 10.2.4 but there were various bugs in this area in Debian and CentOS packages (e.g. #80288, #78953, #79354 and MDEV-18660).
    • Disable NUMA support in your BIOS (Node Interleaving = enabled). Then there is no NUMA presentation to the Operating System any more.
    • Start your MariaDB / MySQL database with numactl --interleave all as described here: MySQL and NUMA.
    • Set innodb_buffer_pool_size to 75% of half of your RAM. Sad for having too much of RAM.
    • Playing around with the following Linux settings could help to decrease swapping: vm.zone_reclaim_mode=0 and kernel.numa_balancing=0.
    Literature

    Some further information about Swap Space you can find here:

    Taxonomy upgrade extras:  swap numa performance database

    SQL Distinct Keyword Tutorial | Distinct Statement in SQL Example

    SQL Distinct Keyword Tutorial | Distinct Statement in SQL Example is today’s topic. The SQL distinct keyword is used for removing duplicate records from a column at the time of displaying the resulting sets from any table that is present in a database. The distinct keyword is used with the select statement for fetching unique values from a table. There also exists some situation where there are multiple duplicate records. So, it makes more sense in retrieving unique values rather than of retrieving duplicate records.

    SQL Distinct Keyword Tutorial

    The Select DISTINCT statement is used to return the distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

    #Syntax Select distinct column1, column2, …., column n from table_name where[condition]; #Parameters
    1. Distinct is a keyword which is used for retrieving unique records.
    2. Columns are the name of the columns.
    3. Table_name is the name of the condition.
    4. Where is the condition which is used for retrieving records based on some condition?
    #Note
    1. When only one expression is provided in a distinct clause, the query will generate unique values for only that expression.
    2. When more than one expression is listed using distinct clause, then the unique combinations for the expressions will be listed.
    3. The distinct clause does not ignore NULL values, so if there is any NULL value in the table, then that NULL value will be displayed in the resulting set representing it as a unique.

    Let’s clear this by looking in an example.

    Example: (For Single columns)

    Consider Table: (CUSTOMERS)

    CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000 601 SHOUVIK JALANDHAR 4 20000 701 ROHIT PATNA 2 40000

     

    #Query: (Without distinct keyword) select city from customers; #Output

     

    #Explanation

    In this, the query has generated all the city names from the customers duplicate without removing the duplicates.

    #Query: (Using distinct keyword) select distinct city from customers; #Output

     

    #Explanation

    In this, the query has generated the city names, which are unique in the customers’ table.

    Example: (For multiple Columns)

    Consider Table: (CUSTOMERS)

    CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000 601 SHOUVIK JALANDHAR 4 20000 701 ROHIT PATNA 2 40000

     

    #QUERY: (Without Distinct Keyword) select city, amount from customers order by amount; #Output

     

    #Explanation

    In this query, all city along with the amount is displayed in the resulting set without any removal of duplicate values.

    #QUERY: (With Distinct Keyword) Select distinct city, amount from customers order by amount; #Output

     

    #Explanation

    In this query, the combinations of unique values from city and amount columns are displayed. You can see from the above output that city Patna and Allahabad was listed at once whose amount was the same.

    Example: (With NULL VALUES)

    Consider Table: (CUSTOMERS)

    CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000 601 SHOUVIK JALANDHAR 4 20000 701 ROHIT PATNA 2 40000 801 ROUNAK NULL 5 10000

     

    #Query select distinct city from customers; #Output

     

    #Explanation

    NULL values are also displayed in the city name as a distinct clause does not ignore it.

    EXAMPLE: (Using Where Condition)

    Consider Table: (CUSTOMERS)

    CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000 601 SHOUVIK JALANDHAR 4 20000 701 ROHIT PATNA 2 40000 801 ROUNAK NULL 5 10000

     

    #Query select distinct city, grade from customers where amount=10000; #Output

     

    #Explanation

    In the above query city and grades are displayed whose amount is equal to 10000. Compare the output table with the original table from there you can see that Kolkata along with the grade 1 and amount with 10000, was displayed once which was present twice in the customer’s table.

    EXAMPLE: (Group BY VS Distinct)

    Consider Table: (CUSTOMERS)

    CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000 601 SHOUVIK JALANDHAR 4 20000 701 ROHIT PATNA 2 40000 801 ROUNAK NULL 5 10000

     

    #QUERY select city, grade, amount from customers group by city, grade, amount order by city, grade, amount; AND select distinct city, grade, amount from customers order by city, grade, amount; #OUTPUT

     

    #Explanation

    In the above queries, you can see that the resulting sets of both group by and distinct clause combinations are the same. Compare the output table from the Original table to spot the difference. You will notice that KOLKATA under the city column whose grade was 1 under the column grade and whose amount was 10000 under the column amount was displayed once. We have also used order by clause in the above query. DISTINCT can be used with aggregates: COUNT, AVG, MAX, etc.

    Finally, SQL Distinct Keyword Tutorial | Distinct Statement in SQL Example is over.

    The post SQL Distinct Keyword Tutorial | Distinct Statement in SQL Example appeared first on AppDividend.

    MySQL for Excel 1.3.8 has been released

    Dear MySQL users,

    The MySQL Windows Experience Team is proud to announce the release of MySQL for Excel version 1.3.8. This is a maintenance release for 1.3.x. It can be used for production environments.

    MySQL for Excel is an application plug-in enabling data analysts to very easily access and manipulate MySQL data within Microsoft Excel. It enables you to directly work with a MySQL database from within Microsoft Excel so you can easily do tasks such as:

      * Importing MySQL Data into Excel

      * Exporting Excel data directly into MySQL to a new or existing table

      * Editing MySQL data directly within Excel

    MySQL for Excel is installed using the MySQL Installer for Windows.

    The MySQL Installer comes in 2 versions

    – Full (400 MB) which includes a complete set of MySQL products with
      their binaries included in the download.

    – Web (18 MB – a network install) which will just pull the MySQL for
      Excel over the web and install it when run.

    You can download MySQL Installer from our official Downloads page at
    http://dev.mysql.com/downloads/installer/

    The MySQL for Excel product can also be downloaded by using the product standalone installer found at this link http://dev.mysql.com/downloads/windows/excel/

    Changes in MySQL for Excel 1.3.8 (2019-06-10, General Availability) * Functionality Added or Changed * Bugs Fixed Functionality Added or Changed * Previously, 1000 (first rows of a MySQL table) was the value limit for previewing a small amount of data in Excel. However, setting the value to 300 or greater generated an exception and prevented additional editing operations. The upper threshold now is 100, instead of 1000 (see Advanced Import Data Options, General Tab (https://dev.mysql.com/doc/mysql-for-excel/en/mysql-for-e xcel-import-options-advanced.html#mysql-for-excel-import- options-advanced-general)). (Bug #29745518) * A new global option, Tolerance for FLOAT and DOUBLE comparisons in WHERE clause, provides a way to edit data of these types that enables proper row-matching in the database when it is used together with optimistic updates (see Global Options, Edit Sessions Tab (https://dev.mysql.com/doc/mysql-for-excel/en/mysql-for-e xcel-config-options.html#mysql-for-excel-global-options-e dit-sessions)). (Bug #29179195, Bug #93824) * The Import Data operation adds digits to floating-point numbers. For example, instead of rendering a value such as 5.3 precisely from the database, the operation displays 5.0000019073486 after importing the data. This behavior affects FLOAT and DOUBLE data types, which adhere to the IEEE-754 standard and are stored as approximate values. A new option now provides a way to import floating-point numbers using the DECIMAL data type, which then stores and displays the exact value from the database (see Advanced Import Data Options, Formatting Tab (https://dev.mysql.com/doc/mysql-for-excel/en/mysql-for-e xcel-import-options-advanced.html#mysql-for-excel-import- advanced-format)). (Bug #26008777) * Support was added for encrypted connections in the form of SSL certificates and SSH tunneling, without the requirement of having intermediate proxy software to create the tunnel. Encrypted connections can be configured from the MySQL for Excel add-in directly or they can be configured with MySQL Workbench and then used to open a connection from the add-in. (Bug #18550080) * The Import Data operation for stored procedures now enables the selection of individual columns to be imported from each returned result set, which is similar to the way imported column data already works for table and view data. (Bug #16239029) Bugs Fixed * The Export and Append Data actions for a cell with data in a worksheet were transferred unexpectedly to a cell without data on a second worksheet when the active focus was shifted to the second worksheet. (Bug #29839647) * A lack of contrast between onscreen message data and the background obscured the connection information when some themes (such as Dark Gray) were set on the host. This fix extends the selected theme colors to the MySQL for Excel add-in for the following versions of Excel: 2007, 2010, 2013, 2016, 365, and 2019. (Bug #29826900) * When mappings were stored for Append Data operations, the add-in did not check for blank and duplicate stored mapping names. Now, validation ensures that all names are unique and that existing names are not overwritten without permission. (Bug #29826762) * Microsoft Excel prompted users to save workbooks that were unchanged. This fix alters the way metadata for connection information (used by Import and Edit Data operations) is created and stored, and ignores unrelated actions. (Bug #29625731) * When schema information was retrieved using a stored procedure, the operation was unable to find the mysql.proc system table. The operation now retrieves schema information from INFORMATION_SCHEMA tables. (Bug#29215137, Bug #93814) * With the option to create Excel relationships for imported tables enabled, an attempt to import a table (with related tables) generated an exception when the tables had circular references. This fix modifies the way relationships are created for Import Data operations for multiple tables, such that relationships among tables that could create a circular reference are not added to the Excel data model. (Bug #29050558) * The Edit Data operation returned an error message intermittently (value not suitable to be converted to a DateTime data type), even for tables without a DATETIME column. This fix updates the library used for internal connections to MySQL 8.0 server instances and the caching_sha2_password plugin. In addition, the updated library resolves an error in which fetched schema information for columns returns the rows in alphabetical order, instead of ordinal order. (Bug #29030866, Bug#93501) * Data imported to a worksheet could not be refreshed if the worksheet was renamed after the import operation. The add-in now inspects the connection information of imported tables to determine whether the associated worksheet name changed, and if so, it updates the connection metadata. Also, it removes the connection information for missing or deleted worksheets. (Bug#27441407, Bug #89387) * After editing, committing, and then refreshing the data from the database, subsequent commits were not recognized by the Edit Data operation. (Bug #27365464, Bug #87642) * An error was generated when an Edit Data operation involved changing the value of a date or time field. Now the value of each date or time field is wrapped with single quotes and the edits are saved to the database. (Bug #26301455, Bug #86723) * When an existing workbook was opened, a second (blank) workbook instance was also opened. (Bug #26245818, Bug#86633) * Some unsupported connection methods were shown as valid options to select. (Bug #26025950) * The Windows automatic scaling of visual elements did not work as expected when the operating system was configured to use a DPI value other than 100%. (Bug #23218058, Bug#81003)

    Quick links:
    MySQL for Excel documentation: http://dev.mysql.com/doc/en/mysql-for-excel.html.
    Inside MySQL blog (NEW blog home): http://insidemysql.com/
    MySQL on Windows blog (OLD blog home): http://blogs.oracle.com/MySQLOnWindows
    MySQL for Excel forum: http://forums.mysql.com/list.php?172.
    MySQL YouTube channel: http://www.youtube.com/user/MySQLChannel.

    Enjoy and thanks for the support!
    The MySQL on Windows team at Oracle.

    Open Core Summit and OSS glue code

    I am optimistic about the Open Core Summit. It can be something that benefits users, startups, the source-available community and the OSS crowd. The summit has many of the important people from the open core community. It is an opportunity for them to collaborate -- form a foundation, reduce open core license proliferation, discuss the next license to be reviewed by OSI and most importantly -- fix the open core marketing approach.

    I appreciate that startups put so much time and money into building interesting system software that is shared either as OSS or source-available. I haven't enjoyed much of the marketing about the challenges that cloud creates for VC-funded OSS. I am sure cloud makes it harder but the approach has been too negative with too much snark directed towards OSI and AWS. There is a better way.

    Glue code

    It is all about the glue code.

    Stateful services are a hard problem. It is not trivial to scale MySQL by enabling a small DBA team to support a growing database deployment. I am sure the same is true for other DBMS. This is done with some changes to MySQL and a huge amount of glue code. While the GPL doesn't require sharing of the diffs to MySQL my teams have always been happy to do that. But the glue code is the secret sauce and neither the GPL nor the AGPL require it to be shared. The SSPL would change that, although I am wary of the SSPL given uncertainty about how far down the stack the sharing requirement extends.

    While the glue code is the secret sauce I wonder whether it has any value outside of a web-scale company.
    1. The glue code isn't portable as it depends on other systems internal to a web-scale company.
    2. Documentation for internal systems is frequently not good and success depends on the shared knowledge of the current teams. Even with access to the internal dependencies the glue code is unusable without the team that knows how to use it.
    Therefore I am more interested in OSS glue code that is useful to a community and less interested in licenses that force the (unusable to me) glue code to be published. The glue code should assume environments open to the public -- public clouds and k8s.

    What is the state of OSS glue code for MySQL? MySQL needs it to remain competitive. Much of the glue code is baked into MongoDB so that it is easier to scale MongoDB clusters even if you aren't an Atlas customer.

    SQL Aggregate Functions Example | Min, Max, Count, Avg, Sum Tutorial

    SQL Aggregate Functions Example | Min, Max, Count, Avg, Sum Tutorial is today’s topic. SQL provides many inbuilt functions that are used for performing various operations in data.  Aggregate Functions are used for performing operations on multiple rows of a particular column and result in a single value. An aggregate function allows you to perform the calculation on a set of values to return the single scalar value. We often use the aggregate functions with the GROUP BY and HAVING clauses of the SELECT statement.

    Aggregate Functions are:

    1. AVG()
    2. COUNT()
    3. MAX()
    4. MIN()
    5. SUM()
    6. STDDEV()
    7. VARIANCE()

    Let’s understand all this function with examples.

    #Avg() in SQL

    The avg() function is used to return an average value after the calculation performed in a numeric column.

    #Syntax Select avg (column_name) from table_name;

    Consider the following table.

    Consider Table: (Customers)

    CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000

     

    #Query select avg(amount) from customers; #Output

     

    #Explanation

    The above query resulted in the average salary of customers in a table customer.

    #Count() in SQL

    The count() function is used to count a total number of records in a table with a condition or without a condition.

    #Syntax Select count(column_name) from table_name;

    Consider Table: (CUSTOMERS)

    CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000

     

    #Query: (Without a condition) Select count(cust_code) from customers;

    See the following output.

     

    #Explanation

    The above query resulted in the total number of customers in a table.

    #Query: (With a condition) Select count(cust_code) from customers where amount > 10000; #Output

     

    #Explanation

    The above query returned a total number of customers whose amount where more than 1000.

    #Max() in SQL

    The max() function is used to return the maximum value in a column.

    #Syntax Select max(column_name) from table_name;

    Consider Table: (CUSTOMERS)

    CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000

     

    #Query Select max(amount) from customers; #Output

     

    #Explanation

    The max() function resulted in the max amount present in the customer table.

    #Min() in SQL

    The min() function is used to return the minimum value in a column.

    #Syntax Select MIN(column_name) from table_name;

    Consider Table: (CUSTOMERS)

    CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000

     

    #Query Select min(amount) from customers; #Output

     

    #Explanation

    The min() function query resulted in the min amount present in the customer table.

    #Sum() in SQL

    The sum() function is used to return the submission of all numeric value in a column.

    #Syntax Select SUM(column_name) from table_name;

    Consider Table: (CUSTOMERS)

    CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000

     

    #Query Select sum(amount) from customers; #Output

     

    #Explanation

    The sum() function query resulted in the total sum of the amount column.

    #STDDEV() in SQL

    The stddev() function is used to return the standard deviation of a selected column.

    #Syntax Select STDDEV(column_name) from table_name;

    Consider Table: (CUSTOMERS)

    CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000

     

    #Query Select STDDEV(amount) from customers; #Output

     

    #Explanation

    The STDDEV query resulted in the standard deviation of an amount column.

    #Variance() in SQL

    The variance() function is used to return the Variance of a selected column.

    #Syntax Select VARIANCE(column_name) from table_name;

    Consider Table: (CUSTOMERS)

    CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000

     

    #Query Select variance(amount) from customers; #Output

     

    #Explanation

    The above query resulted in the variance of the amount column.

    Finally, SQL Aggregate Functions Example | Min, Max, Count, Avg, Sum Tutorial is over.

    The post SQL Aggregate Functions Example | Min, Max, Count, Avg, Sum Tutorial appeared first on AppDividend.

    Percona XtraDB Cluster 5.6.44-28.34 Is Now Available

    Percona is glad to announce the release of Percona XtraDB Cluster 5.6.44-28.34 on June 19, 2019. Binaries are available from the downloads section or from our software repositories.

    Percona XtraDB Cluster 5.6.44-28.34 is now the current release, based on the following:

    All Percona software is open-source and free.

    Bugs Fixed
    • PXC-2480: In some cases, Percona XtraDB Cluster could not replicate CURRENT_USER() used in the ALTER statement. USER() and CURRENT_USER() are no longer allowed in any ALTER statement since they fail when replicated.
    • PXC-2487: The case when a DDL or DML action was in progress from one client and the provider was updated
      from another client could result in a race condition.
    • PXC-2490: Percona XtraDB Cluster could crash when binlog_space_limit was set to a value other than zero during wsrep_recover mode.
    • PXC-2497: The user can set the preferred donor by setting the wsrep_sst_donor variable. An IP address is not valid as the value of this variable. If the user still used an IP address, an error message was produced that did not provide sufficient information. The error message has been improved to suggest that the user check the value of the wsrep_sst_donor for an IP address.

    Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

    Migrating from MySQL Enterprise to MariaDB 10.3

    While it shares the same heritage with MySQL, MariaDB is a different database. Over the years as new versions of MySQL and MariaDB were released, both projects have differed into two different RDBMS platforms.

    MariaDB becomes the main database distribution on many Linux platforms and it’s getting high popularity these days. At the same time, it becomes a very attractive database system for many corporations. It’s getting features that are close to the enterprise needs like encryption, hot backups or compatibility with proprietary databases.

    But how do new features affect MariaDB compatibility with MySQL? Is it still drop replacement for MySQL? How do the latest changes amplify the migration process? We will try to answer that in this article.

    What You Need to Know Before Upgrade

    MariaDB and MySQL differ from each other significantly in the last two years, especially with the arrival of their most recent versions: MySQL 8.0, MariaDB 10.3 and MariaDB 10.4 RC (we discussed new features of MariaDB 10.4 RC quite recently so If you would like to read more about what's upcoming in 10.4 please check two blogs of my colleague Krzysztof, What's New in MariaDB 10.4 and second about What's New in MariaDB Cluster 10.4).

    With the release MariaDB 10.3, MariaDB surprised many since it is no longer a drop-in replacement for MySQL. MariaDB is no longer merging new MySQL features with MariaDB noir solving MySQL bugs. Nevertheless version 10.3 is now becoming a real alternative to Oracle MySQL Enterprise as well as other enterprise proprietary databases such as Oracle 12c (MSSQL in version 10.4).

    Preliminary Check and limitations

    Migration is a complex process no matter which version you are upgrading to. There are a few things you need to keep in mind when planning this, such as essential changes between RDBMS versions as well as detailed testing that needs to lead any upgrade process. This is especially critical if you would like to maintain availability for the duration of the upgrade.

    Upgrading to a new major version involves risk, and it is important to plan the whole process thoughtfully. In this document, we’ll look at the important new changes in the 10.3 (and upcoming 10.4) version and show you how to plan the test process.

    To minimize the risk, let’s take a look on platform differences and limitations.

    Starting with the configuration there are some parameters that have different default values. MariaDB provides a matrix of parameter differences. It can be found here.

    In MySQL 8.0, caching_sha2_password is the default authentication plugin. This enhancement should improve security by using the SHA-256 algorithm. MySQL has this plugin enabled by default, while MariaDB doesn’t. Although there is already a feature request opened with MariaDB MDEV-9804. MariaDB offers ed25519 plugin instead which seems to be a good alternative to the old authentication method.

    MariaDB's support for encryption on tables and tablespaces was added in version 10.1.3. With your tables being encrypted, your data is almost impossible for someone to steal. This type of encryption also allows your organization to be compliant with government regulations like GDPR.

    MariaDB supports connection thread pools, which are most effective in situations where queries are relatively short and the load is CPU bound. On MySQL’s community edition, the number of threads is static, which limits the flexibility in these situations. The enterprise plan of MySQL includes threadpool capabilities.

    MySQL 8.0 includes the sys schema, a set of objects that helps database administrators and software engineers interpret data collected by the Performance Schema. Sys schema objects can be used for optimization and diagnosis use cases. MariaDB doesn’t have this enhancement included.

    Another one is invisible columns. Invisible columns give the flexibility of adding columns to existing tables without the fear of breaking an application. This feature is not available in MySQL. It allows creating columns which aren’t listed in the results of a SELECT * statement, nor do they need to be assigned a value in an INSERT statement when their name isn’t mentioned in the statement.

    MariaDB decided not to implement native JSON support (one of the major features of MySQL 5.7 and 8.0) as they claim it’s not part of the SQL standard. Instead, to support replication from MySQL, they only defined an alias for JSON, which is actually a LONGTEXT column. In order to ensure that a valid JSON document is inserted, the JSON_VALID function can be used as a CHECK constraint (default for MariaDB 10.4.3). MariaDB can't directly access MySQL JSON format.

    Oracle automates a lot of tasks with MySQL Shell. In addition to SQL, MySQL Shell also offers scripting capabilities for JavaScript and Python.

    Migration Process Using mysqldump

    Once we know our limitations the installation process is fairly simple. It’s pretty much related to standard installation and import using mysqldump. MySQL Enterprise backup tool is not compatible with MariaDB so the recommended way is to use mysqldump. Here is the example process is done on Centos 7 and MariaDB 10.3.

    Create dump on MySQL Enterprise server

    $ mysqldump --routines --events --triggers --single-transaction db1 > export_db1.sql

    Clean yum cache index

    sudo yum makecache fast

    Install MariaDB 10.3

    sudo yum -y install MariaDB-server MariaDB-client

    Start MariaDB service.

    sudo systemctl start mariadb sudo systemctl enable mariadb

    Secure MariaDB by running mysql_secure_installation.

    # mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. Set root password? [Y/n] y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] y ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB!

    Import dump

    Mysql -uroot -p > tee import.log > source export_db1.sql Review the import log. $vi import.log

    To deploy an environment you can also use ClusterControl which has an option to deploy from scratch.

    ClusterControl Deploy MariaDB

    ClusterControl can be also used to set up replication or to import a backup from MySQL Enterprise Edition.

    Migration Process Using Replication

    The other approach for migration between MySQL Enterprise and MariaDB is to use replication process. MariaDB versions allow replicating to them, from MySQL databases - which means you can easily migrate MySQL databases to MariaDB. MySQL Enterprise versions won’t allow replication from MariaDB servers so this is one-way route.

    Based on MariaDB documentation: https://mariadb.com/kb/en/library/mariadb-vs-mysql-compatibility/. X refers to MySQL documentation. Related resources  ClusterControl for MariaDB  What's New in MariaDB 10.4  How to Manage MySQL - for Oracle DBAs

    Here are some general rules pointed by the MariaDB.

    • Replicating from MySQL 5.5 to MariaDB 5.5+ should just work. You’ll want MariaDB to be the same or higher version than your MySQL server.
    • When using a MariaDB 10.2+ as a slave, it may be necessary to set binlog_checksum to NONE.
    • Replicating from MySQL 5.6 without GTID to MariaDB 10+ should work.
    • Replication from MySQL 5.6 with GTID, binlog_rows_query_log_events and ignorable events works starting from MariaDB 10.0.22 and MariaDB 10.1.8. In this case, MariaDB will remove the MySQL GTIDs and other unneeded events and instead adds its own GTIDs.

    Even if you don’t plan to use replication in the migration/cutover process having one is a good confidence-builder is to replicate your production server on a testing sandbox, and then practice on it.

    We hope this introductory blog post helped you to understand the assessment and implementation process of MySQL Enterprise Migration to MariaDB.

    Tags:  MySQL MariaDB database migration migration

    How to validate server configuration settings.

    After upgrading the server many users start it with an unchanged config file only to find some deprecated options that they were using are no longer supported by the later server version, which causes the upgraded server to shutdown. In other cases modifying the server configuration file results in the server refusing to start when an invalid name is mistakenly entered in the configuration file.…

    Facebook Twitter Google+ LinkedIn

    How To Insert Multiple Rows in SQL With Example

    How To Insert Multiple Rows in SQL With Example is today’s topic. In SQL, inserting values in the rows is a tedious task when a table comes with a lot of attributes. Previously, we were habituated to add values in row one at a time which is very time-taking and which also results in a lot of error. If we want to get rid of this, we are going to learn how to insert the multiple rows in a table at a time.

    How To Insert Multiple Rows in SQL With Example

    There are different methods to insert values in a table:

    1. Using INSERT statement.
    2. Using INSERT INTO SELECT statement.
    3. Using UNION ALL keyword.

    #Syntax INSERT INTO table_name(column1, column2, ……., column n) VALUES (values in row 1), (values in row 2), ……… (values in row n); #Parameters

    1) Table_name is the name of the table.
    2) Columns are the name of the columns.
    3) Values in rows are the set of the values to be inserted in the table.

    #Note
    1) At a time, you can insert 1000 rows using INSERT statements. To insert more rows than that used multiple insert keywords or use BULK INSERT.
    2) Only SQL SERVER 2008 allows adding multiple rows in the table. #Examples

    Suppose a table is created named as STUDENT.

    ID NAME CITY 101 Shubh Kolkata 102 Sandeep Asansol 103 Shouvik Patna

     

    #QUERY INSERT INTO STUDENT (ID, NAME, CITY) VALUES (101,’Shubh’,’Kolkata’), (102,’Sandeep’,’Asansol’), (103,’Shouvik’,’Patna’);

    See the output.

     

    Using INSERT INTO SELECT statement.

    #Syntax INSERT into table_1 (column1, column2, …., column n) SELECT column1, column2, …., column n from table_2 ; #Parameters
    • table_1 is the name of the newly created table
    • table_2 is the name of the previously created table.
    • Columns are the respective columns in which you want to insert values.
    #Examples

    Consider table STUDENT having attributes like ID, NAME, CITY, as discussed in the previous example. Now to add values to it, we are going to use the table.

    new_student ID First_name City 1 Anshuman Asansol 2 Karthik Allahabad 3 Karan Malda

     

    #Query INSERT INTO STUDENT (ID, name, city) SELECT * from new_student;

    See the following output.

     

    #Explanation

    In the parentheses of table student, we have given the column name in the same order which was discussed in the table in the previous example. Now when we have executed a select * statement, then the values from new_student table gets copied to student table.

    The order should be the same as that of the student table if you are affecting the whole table.

    #NOTE

    It is not necessary that all rows should be executed, you can also insert specific rows using where condition and also specific columns which you want.

    #Using UNION ALL keyword #Syntax Insert into table (column1, column2, …., column n) SELECT values UNION ALL SELECT values_1 UNION ALL ….; #Parameters
    1. The table is the name of the table.
    2. Columns are the name of the columns.
    3. Values are the set of data you want to insert.
    4. UNION ALL is the keyword.
    #Example

    Consider table STUDENT having attributes like ID, NAME.

    Now to add values use the following query.

    #Query Insert into student (ID, Name) Select 1,'Shubh' UNION ALL Select 2,'Sandeep' UNION ALL Select 3,'Shouvik'; #Output

     

    #Explanation

    Here values are inserted in the same order as mentioned in parentheses. UNION ALL is used for merging all the values in rows.

    Finally, How To Insert Multiple Rows in SQL With Example is over.

    The post How To Insert Multiple Rows in SQL With Example appeared first on AppDividend.

    Beyond Multi-AZ; Active - Passive (Bidirectional) Replication in Amazon RDS


    Limitations of Multi-AZ setup in RDSA generally recommended High Availability setup in RDS is to create a Multi-AZ master and one (or more) Read Replica for read scaling. Multi-AZ setup will automatically failover to the standby replica when the primary becomes unavailable, or a manual "reboot with failover" is triggered.
    Multi-AZ Master + One Read Replica
    But HA provided by Multi-AZ may not be the best fit for you under certain scenarios
    • Multi-AZ costs just the double that of Single-AZ
    • Stand by replica cannot be used for read scaling
    • Multi-AZ has higher commit latency due to synchronous replication
    • The failover window could range anywhere between 20 seconds to couple of minutes 
    • MySQL upgrades will shutdown both the instances in the Multi-AZ, which means downtime
    So these scenarios calls for a HA solution in RDS beyond just Multi-AZ

    Problem with Promoting Read Replica

    Creating a read replica is easy and promoting that as the Master is also just a click of the mouse. But once the read replica becomes the Active master then the replication is cut between the instances, and need a rebuild of new slave from the current Master.
    Solution Active Master - Passive Master setup is where each instance is replicating from the other. But at any given time only the instance that plays the Active role will be Writable and other will be read-only.

    Acitve - Passive setup

    1. Create a read replica of the primary RDS instance 
    2. Enable backup (backup retention > 0) on the read replica, which will in turn enable binary logging
    3. create a replication user on the read replica
    4. Call the procedure mysql.rds_set_external_master() with appropriate parameters on the Primary instance. the binary log position can be just any recent log position from the replicas show master status command output
    5. call mysql.rds_start_replication() on the Primary to start the replication
    6. Now both the instances are in Active Master(W) <-> Passive Master(R) configuration

    Failover

    We don't use the "Promote Read Replica" option here, because that would result in resetting the slave settings on the replica. Since active - passive is all setup the only job left are
    1. Make the replica writable
    2. Stop the writes on the Primary master
    3. Switch the application traffic to replica 
    4. Make the old Primary as read-only

    Since the RDS do not allow SUPER we can not set or reset the global read_only variable. So you have to update the parameter file to switch the read_only variable 




    MariaDB 10.4.6, First Stable 10.4 Release, and MariaDB Connector/J 2.4.2 Now Available

    The MariaDB Foundation is pleased to announce the availability of MariaDB 10.4.6, the first stable release in the MariaDB 10.4 series, and MariaDB Connector/J 2.4.2, the latest stable release in the MariaDB Connector/J 2.4 series. See the release notes and changelogs for details. Download MariaDB 10.4.6 Release Notes Changelog What is MariaDB 10.4? MariaDB APT […]

    The post MariaDB 10.4.6, First Stable 10.4 Release, and MariaDB Connector/J 2.4.2 Now Available appeared first on MariaDB.org.

    Configuring the Tungsten Connector for PCI Compliance

    The Question Recently, a customer asked us:

    We were wondering if the Connectors would be able to bind to localhost/127.0.0.1 instead of 0.0.0.0:3306? Since the Connector is installed on the application servers, all of the connections are coming from localhost. We would like to limit this exposure so that the 3306 port is not exposed externally. We ask because we are failing PCI checks that are able to access the database port externally.

    The Answer YES!

    You may set the IP address for the Connector to listen on by using the tpm command option: property=server.listen.address={IP_ADDRESS}

    To force the Connector to listen on the localhost only use the following example:

    shell> tools/tpm configure alpha --property=server.listen.address=127.0.0.1 shell> tools/tpm update --replace-release

    Use the IP address instead of the hostname to prevent DNS or /etc/hosts lookups.

    For more information about configuring the Connector IP address, please visit the docs page at https://docs.continuent.com/tungsten-clustering-6.0/connector-advanced-listen-address.html

    Summary The Wrap-Up

    In this blog post we discussed one way to configure the Tungsten Connector for PCI Compliance.

    To learn about Continuent solutions in general, check out https://www.continuent.com/solutions

    The Library Please read the docs!

    For more information about configuring the Connector IP address, please visit the docs page at https://docs.continuent.com/tungsten-clustering-6.0/connector-advanced-listen-address.html

    For more information about Tungsten clusters, please visit https://docs.continuent.com.

    Tungsten Clustering is the most flexible, performant global database layer available today – use it underlying your SaaS offering as a strong base upon which to grow your worldwide business!

    For more information, please visit https://www.continuent.com/solutions

    Want to learn more or run a POC? Contact us.

    How to Autoscale ProxySQL in the cloud

    ProxySQL is a great tool. It’s one of the most recommended technologies in our Open Source Database practice.

    Many of our clients are running it or are migrating towards it, but we’ve seen that it is pretty CPU-intensive. We’ve also seen strange behavior in the connection handling when reaching the CPU saturation point.

    At this point, we noticed that the frontend_connections counter in the stats_mysql_users table was not decreasing even after the connections were no longer present at the network level. This counter is used to check the max_connections value in the mysql_users configuration table, causing frontend connections to receive a “Too many connections” error. So we determined that the key element here is to scale it properly. Obviously, all the major cloud providers can help us here as they all have features like auto-scaling groups.

    Jervin Real of Percona has recently blogged about running a load-balanced ProxySQL in Google Cloud Platform. We decided to take it one step further: “Autoscaling a load-balanced ProxySQL in Google Cloud Platform”.

    While this blog is about how we did it in GCP, similar things should just be valid in platforms like AWS. The terminology might be a bit different but the basic principle should be pretty much the same.

    Creating the autoscaling managed instance group

    To be able to create an instance group in GCP, you first need an instance template. Such a template defines all the properties of the machine, for example, CPU and memory resources, what kind of disks should be attached, networking options, etc. What you can also do is define a startup and shutdown script for this instance. These scripts will be run, obviously, on startup and shutdown of the machines. So in the autoscaling group that we will be creating, these will be executed on scale up and scale down events. For more convenience, we would recommend using the startup-script-url and shutdown-script-url options so that you can manage the scripts in a Google Cloud Storage bucket instead of having to recreate the instance template every time. These scripts will (among other things) be used to announce our instance to the cluster or to remove it from the group.

    Our instance group is set to always keep two nodes online (for HA purposes) and it can scale up to (currently) five nodes. And, just like Jervin described, we have put a Google Internal Load-balancer in front of it to listen on port 3306. We also had to create the health checks and add the correct network tags to allow the health checks to reach our instances.

    A side effect of the autoscaling is that hostnames and IP addresses are changing all the time; therefore, we opted to create an additional (smaller) instance that we call our “proxysql-master”. This instance is not part of the Managed Instance Group and will also not receive any traffic from the load balancer. We only use it for configuring the ProxySQL cluster and to create backups of our configuration.

    Introducing Consul key-value store

    The way we will make this autoscaling work is to use Consul as a service discovery platform. Consul is a lightweight distributed key-value store that is frequently used for service discovery, configuration, and segmentation functionality. The first two are exactly what we are going to use it for.

    For starters, we need a Consul “server” cluster. Consul server is the component that will actually store data. As mentioned before this is a distributed service so we need more than one node. In our example here we will start with a three-node setup; three nodes because we want a quorum possibility in case of network partitioning.

    Next to these Consul server nodes, we will install a Consul agent on any other server in our setup. This will result in the following Consul cluster:

    $ consul members Node Address Status Type Build Protocol DC Segment consul-1 10.1.0.2:8301 alive server 1.4.3 2 dc1 <all> consul-2 10.2.0.2:8301 alive server 1.4.3 2 dc1 <all> consul-3 10.3.0.2:8301 alive server 1.4.3 2 dc1 <all> proxysql-2ml7 10.1.0.4:8301 alive client 1.4.3 2 dc1 <default> proxysql-gf41 10.2.0.3:8301 alive client 1.4.3 2 dc1 <default> proxysql-master 10.1.0.3:8301 alive client 1.4.3 2 dc1 <default>

    We have 3 servers (this server binary also acts as an agent) and 3 clients (only agents). This is the configuration of our agent:

    $ cat /etc/consul/consul.conf.json { "bind_addr": "10.1.0.3", "datacenter": "dc1", "data_dir": "/var/lib/consul/", "log_level": "INFO", "node_name": "proxysql-master", "server": false, "ui": true, "client_addr": "0.0.0.0", "retry_join": [ "10.1.0.2", "10.2.0.2", "10.3.0.2" ] }

    The only difference between client and server configuration is this “server”: “false” option which we obviously change to “true” on the servers. The server also needs the option bootstrap_expect which we set to three because we expect three nodes to join our cluster.

    Our startup script looks (in a little simplified version) like this:

    #!/bin/bash LOCAL_IP=$(curl http://metadata.google.internal/computeMetadata/v1/instance/network-interfaces/0/ip -H "Metadata-Flavor: Google") LOCAL_HOST=$(curl http://metadata.google.internal/computeMetadata/v1/instance/name -H "Metadata-Flavor: Google") # Install, configure (basic) and start ProxySQL ... # Install, configure and start Consul Agent ... /usr/local/bin/consul kv put proxysql/servers/${LOCAL_HOST} ${LOCAL_IP}

    This last line is where the magic happens. This line puts this ProxySQL node into the Consul K/V store, announcing it for the service discovery.

    When all our instances are running, this is what our K/V store looks like:

    $ consul kv export proxysql/servers [ { "key": "proxysql/servers/proxysql-2ml7", "flags": 0, "value": "10.1.0.4" }, { "key": "proxysql/servers/proxysql-gf41", "flags": 0, "value": "10.2.0.3" }, { "key": "proxysql/servers/proxysql-master", "flags": 0, "value": "10.1.0.3" } ]

    (Keep in mind that if you run this command in your environment, the values will be base64 encoded. For this blog post we have decoded them to show what is actually in there.)

    Let’s configure ProxySQL

    Great! We now have all our ProxySQL instances in the Consul storage. Let’s put them in the ProxySQL configuration.

    Option 1: Create a bash script that we can run in the ProxySQL scheduler function. This script will connect via the local consul agent to the “leader” server and fetch the hostname and IPs from the K/V store and put it into the proxysql_servers table.
    Works great, but this was found to be a bit a waste of CPU cycles, as the scheduler would run it every x milliseconds (x > 0).

    Option 2: Consul Template! Consul Template is a tool released by the creators of Consul. This tool will connect to the Consul cluster and it will start watching the key/value store for any keys you have defined in a template file. Whenever the values for these keys change in the K/V store, Consul Template will regenerate the “output file” with the newly received values. After this task is done it has the ability to execute a command to activate these changes (for example call a SIGHUP on a service).

    This is the template we gave to Consul Template to update proxysql_servers table:

    $ cat /etc/consul-template/templates/proxysql_servers.tpl DELETE FROM proxysql_servers; {{range ls "proxysql/servers"}} REPLACE INTO proxysql_servers (hostname, port, comment) VALUES ('{{.Value}}', 6032, '{{.Key}}');{{end}} SAVE PROXYSQL SERVERS TO DISK; LOAD PROXYSQL SERVERS TO RUNTIME;

    This results in this output file:

    $ cat /etc/consul-template/templates/proxysql_servers.sql DELETE FROM proxysql_servers REPLACE INTO proxysql_servers (hostname, port, comment) VALUES ('10.1.0.4', 6032, 'proxysql-2ml7'); REPLACE INTO proxysql_servers (hostname, port, comment) VALUES ('10.2.0.3', 6032, 'proxysql-gf41'); REPLACE INTO proxysql_servers (hostname, port, comment) VALUES ('10.1.0.3', 6032, 'proxysql-master'); SAVE PROXYSQL SERVERS TO DISK; LOAD PROXYSQL SERVERS TO RUNTIME;

    And this is the configuration we use to activate the changes in ProxySQL:

    $ cat /etc/consul-template/config/consul-template.cfg consul { ... address = "127.0.0.1:8500" ... } ... template { source = "/etc/consul-template/templates/proxysql_servers.tpl" destination = "/etc/consul-template/templates/proxysql_servers.sql" command = "/bin/bash -c 'mysql --defaults-file=/etc/proxysql-admin.my.cnf < /etc/consul-template/templates/proxysql_servers.sql'" command_timeout = "60s" perms = 0644 backup = false wait = "2s:6s" }

    This what our autoscaling monitoring looked like when we were load testing:

    Our scaling policy is based on CPU usage exceeding 60%. As you can see, we start with two instances so the green line on the bottom graph shows us we have a capacity of 120% (2* 60%). When our load goes up, the auto-scaler adds a 3rd and even a 4th instance. After the load test finishes and the CPU load goes down, our proxies start scaling down again. We have configured the cool-down period for scaling down to be three minutes. If you do not want it to scale down too soon, you can easily configure this to a higher value.

    Conclusion

    Leveraging the automatic scaling of your services is not that hard, all major cloud providers will offer this kind of service. When using the right tools for the job you can achieve very nice results that will be very beneficial for your application performance at peak-load times. It will also be a great way to reduce the cost of operations because you will not be paying for idle services.

    Jun 17: Events with MySQL this week!

    Just a reminder for the shows where you can find MySQL at this week, please find them below:

    • GigaCon - OS Business, Warsaw, Poland, Jun 18, 2019
      • Vittorio Cioe, the Sr. MySQL Sales Consultant will talk about MySQL Enterprise during the shared slot with Linux/Cloud team. Talk is scheduled for 10:05-10:25 on Jun 18.
    • OpenExpo Europe, Madrid, Spain, Jun 20, 2019
      • Find MySQL & Linux team at our shared booth at OpenExpo Europe as Gold sponsor.
      • Planned MySQL talk:
        • "MySQL 8.0: Highly Available, JSON , NoSQL & Document Store" by Keith Hollman, the MySQL Principal Sales Consultant. Time: 16:40-17:10.
    • DataOps Barcelona, Spain, Jun 20-21, 2019
      • Do not miss the MySQL talk on: "Deep dive into MySQL Group Replication: the magic explained" given by Frederick Descamps, the MySQL Community Manager. His talk is scheduled for June 20 @11:30 in the Room A-4.
    • RootConf 2019, Bangalore, India, Jun 21-22, 2019
      • There should be a MySQL 8 talk, but for some reason it is not in the official schedule. Come to our booth to ask for the talk location, hearing about the news in MySQL and talking to our MySQL experts! We are looking forward seeing you there.
    • DevConf, Moscow, RF, Jun 21-22, 2019
      • Do not. miss the MySQL talk on: "NoSQL + SQL = MySQL 8 Document Store!" given by Vittorio Cioe, the Sr. MySQL Sales Consultant. Vittorio's talk is scheduled for Jun 26th @12:30pm. 

    Histogram​ in MySQL 8.0

    MySQL 8.0 introduces many new features. We will have a look at the exciting histogram  feature in MySQL 8.0

    Histogram:

    What is Histogram?

    In General, a histogram is an accurate representation of the distribution of numerical data. In MySQL, It will be useful to collect data distribution for a specific column.

    What problem it solves?

    In general DB Optimizer gives us the best execution plan, But the stats make the execution plan better and better .The data distribution of values in columns can make good impact in optimiser in case of column with less distinct values.

    We will see an example of how it helps optimizer in some cases. 

    I have used a production case. MySQL version is 8.0.15 installed in ubuntu 18.04 (32GB RAM,8 core) with optimal configuration. Let us try to optimise a query using histogram.

    root@localhost:(none)>show create table app.app_user_state\G *************************** 1. row *************************** Table: app_user_state Create Table: CREATE TABLE `app_user_state` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` int(11) DEFAULT NULL, `state` int(11) DEFAULT NULL, `subState` int(11) DEFAULT NULL, `changedOn` datetime DEFAULT NULL, `description` varchar(128) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=67975632 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) SELECT * FROM app.app_user_state WHERE state = ? AND changedOn > ? AND changedOn < ?; root@localhost:(none)>select min(changedOn),max(changedOn) from app.app_user_state; +---------------------+---------------------+ | min(changedOn) | max(changedOn) | +---------------------+---------------------+ | 2018-04-26 08:28:45 | 2019-05-23 10:00:03 | +---------------------+---------------------+ 1 row in set (15.75 sec)

    Query fetching data from July 2018 to August 2018 with a state value of 52(contains 60133 records). Note No index on column changedOn.

    root@localhost:(none)>explain SELECT * FROM app.app_user_state WHERE state = 53 AND changedOn > '2018-07-17 00:00:00' and ChangedOn < '2018-08-07 00:00:00'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: app_user_state rows: 3984266 filtered: 11.11 Extra: Using where 1 row in set, 1 warning (0.00 sec)

    Query is trying to fetch data from 26, April 2018 to 27, April 2018 ( contains no records and but still it tooks around 6 seconds)

    root@localhost:(none)>explain SELECT * FROM app.app_user_state WHERE state = 53 AND changedOn > '2018-04-26 08:28:45' and ChangedOn < '2018-04-27 08:28:45'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: app_user_state rows: 3984266 filtered: 11.11 Extra: Using where 1 row in set, 1 warning (0.00 sec)

    Without any distribution stats, the optimizer considers data is equally distributed across all days in a year. We can provide intelligence to optimizer by using the histogram.

    To Create histogram (syntax ):

    Analyze table table_name update histogram on column_name with N buckets;

    Histogram contains buckets (N) for part of the range of the values stored in the column.If N clause is ignored, the number of buckets by default is 100. (range from 0 to 1024).

    mysql> analyze table app.app_user_state update histogram on changedOn\G *************************** 1. row *************************** Table: app.app_user_state Op: histogram Msg_type: status Msg_text: Histogram statistics created for column 'changedOn'. 1 row in set (21.76 sec)

    Histogram is created for the column changedOn.

    mysql> SELECT TABLE_NAME,COLUMN_NAME,HISTOGRAM->>'$."data-type"' AS 'data-type', JSON_LENGTH(HISTOGRAM->>'$."buckets"') AS 'bucket-count'        FROM INFORMATION_SCHEMA.COLUMN_STATISTICS; +---------------+-------------+-----------+--------------+ | TABLE_NAME | COLUMN_NAME | data-type | bucket-count | +---------------+-------------+-----------+--------------+ | app_user_state | changedOn | datetime | 100 | +---------------+-------------+-----------+--------------+ 1 row in set (0.00 sec)

    Execution plan post histogram

    root@localhost:(none)>explain SELECT * FROM app.app_user_state WHERE state = 53 AND changedOn > '2018-04-26 08:28:45' and ChangedOn < '2018-04-26 08:28:45'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: app_user_state rows: 3984266 filtered: 0.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)

    In this can, we can observe the difference in the filtered columns of explain plan. Filtering can even occur in the absence of an index.

    Histogram statistics are widely used for non-indexed columns. We can also add an index to histogram columns that will help the optimizer to make row estimates, But index should be updated whenever the table modification is higher. This is not a problem in the histogram. We can create/update histogram when there is a demand. So, There is no overhead in the histogram when the table is modified.

    We can disable histogram by two methods

    1)  By dropping the histogram for the particular column of a table.

    Analyze table table_name drop histogram on column_name;

      2)  By disabling condition_fanout_filter of optimizer switch.

    Set optimizer_switch=’condition_fanout_filter=off’;

    We can get more information on histogram stats from Information_schema.column_statistics table.

    root@localhost:(none)>select * from information_schema.column_statistics\G *************************** 1. row *************************** SCHEMA_NAME: app TABLE_NAME: app_user_state COLUMN_NAME: changedOn HISTOGRAM: {"buckets": [["2018-04-26 08:28:45.000000", "2018-07-19 11:39:51.000000", 0.031245524205098827, 778563], ... ... "2019-05-29 00:50:31.000000", 0.9998813914351189, 266432]], "data-type": "datetime", "null-values": 0.00011860856488112289, "collation-id": 8, "last-updated": "2019-05-29 00:52:57.168591", "sampling-rate": 0.012078229085799905, "histogram-type": "equi-height", "number-of-buckets-specified": 32}

    Histogram Objects have keys like buckets, data-type, collation-id, last-updated, sampling-rate, histogram-type, number-of-buckets-specified.

    Buckets: Bucket structure depends on the histogram type

    Histogram Types:    

    1) Singleton histogram  

    2) Equi height histogram

    Singleton: One bucket represents one single value in the column. This type of histogram will be created when the number of distinct values in the column is less than or equal to the number of buckets specified in the analyze table syntax.

    Equi-height: One bucket represents a range of values. This type of histogram will be created when distinct values in the column are greater than the number of buckets specified in the analyze table syntax.

    For Example State column in the app.app_user_state table is having 6 distinct values. Now creating a histogram with 6 buckets as equal as distinct values in analyze table syntax.

    root@localhost:(none)>analyze table app.app_user_state update histogram on state with 6 buckets\G *************************** 1. row *************************** Table: app.app_user_state Op: histogram Msg_type: status Msg_text: Histogram statistics created for column 'state'. root@localhost:(none)>select histogram from information_schema.column_statistics\G HISTOGRAM: {"buckets": [[1, 0.19453761379971252], [32, 0.31223197172975564], [33, 0.7182019645424054], [34, 0.7733506528509823], [53, 0.8396232630570196], [86, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "last-updated": "2019-05-29 01:18:47.982666", "sampling-rate": 0.03623352139303918, "histogram-type": "singleton", "number-of-buckets-specified": 6}.

    We can see that the type of histogram created is Singleton. In singleton histogram, each buckets contain two values [1, 0.19453761379971252]

    value 1: bucket value (distinct values in column)

    value 2: cumulative frequency for each value

    Let try updating histogram with 2 buckets

    root@localhost:(none)>analyze table app.app_user_state update histogram on state with 2 buckets\G *************************** 1. row *************************** Table: app.app_user_state Op: histogram Msg_type: status Msg_text: Histogram statistics created for column 'state'. root@localhost:(none)>select histogram from information_schema.column_statistics\G HISTOGRAM: {"buckets": [[1, 32, 0.31338171297403283, 2], [33, 86, 1.0, 4]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "last-updated": "2019-05-29 01:31:21.307545", "sampling-rate": 0.036234657847286224, "histogram-type": "equi-height", "number-of-buckets-specified":2}

    In an equi-height histogram, bucket contains 4 values. [1, 32, 0.31338171297403283, 2]

    Values 1 & 2: The lower and upper ranges for the bucket(Range of values)

    Value3: The cumulative frequency for the values

    Value4: The number of distinct values in the range.

    data-type: The type of data this histogram contains.

    number-of-buckets-specified: Number of buckets specified in the analyze table statement. (100 is the default)

    last-updated: when the histogram is generated.

    null-values: The value lies between 0.0 and 1.0. The fraction of column values that contain null values. 0 means that the column contains no null values.

    collation-id: The collation-id for the histogram data.

    Sampling-rate: The value ranges between 0.0 and 1.0. This indicates that the fraction of data that was sampled while creating the histogram. If the value is 1 means that all the data was read while creating the histogram(No sampling occurs)

    The memory allocation for sampling is controlled by the variable histogram_generation_max_mem_size. It can be defined in both the global and session level while creating the histogram.  While we creating a histogram for the particular column, the server will read all the data into the memory and then process it. If we are doing histogram for larger tables, there is a risk of reading all data into memory. we can avoid this problem by using this variable. The server will calculate how many rows can be fit into the memory defined by the histogram memory variable histogram_generation_max_mem_size. If all the values in the column are not fitted defined by the variable, the server allows only values fitted into memory and starts to take sampling.

    How to chose a column for histogram ?

    1. values which do not change much over time
    2. Columns with low cardinality values
    3. Columns with uneven distribution

    Columns to be avoided in histograms?

    1. Column with more distinct values and inserts are high (manual intervention need to update histogram frequently)

    Histogram is an wonderful feature in MySQL 8.0 which can help DBA’s in optimising their SQL’s for better response time.

    More Reads on Histogram:

    https://www.slideshare.net/SvetaSmirnova/billion-goods-in-few-categories-how-histograms-save-a-life-148508659

    https://dev.mysql.com/worklog/task/?id=8943

    Image by Plush Design Studio on Unsplash

    Benefits of SQL Diagnostic Manager for MySQL (formerly Monyog)

    Welcome to our new blog series Benefits of SQL Diagnostic Manager for MySQL (formerly Monyog). This series will discuss the features of SQL Diagnostic Manager for MySQL designed to enable organizations to scale their existing resources for database administration. This week, we focus on the challenges associated with MySQL and MariaDB database systems.

    The MySQL and MariaDB database systems are rapidly becoming the de-facto choice for traditional enterprises as well as pioneering Web 2.0 companies. Webyog has been consistently delivering powerful tools exclusively on the MySQL and MariaDB platform for the last 15 years.

    While the adoption rate of MySQL and MariaDB continues to grow in momentum, it poses some unique challenges for traditional database administrators and their organizations. Some of these challenges are:

    • Find problematic SQL queries
    • Analyze MySQL and MariaDB performance data collected over a period
    • Ensure availability of critical MySQL and MariaDB systems
    • Keep the systems well-tuned
    • Get proactive alerts before problems start surfacing
    • Identify problems quickly
    • Monitor MySQL and MariaDB server health continuously
    • Profile queries
    • Protect critical systems

    SQL Diagnostic Manager for MySQL enables database administrators to meet these challenges. It could help database administrators working for large organizations who have deployed hundreds of MySQL and MariaDB servers. It could also help database administrators working for small organizations with only a handful of MySQL and MariaDB servers. It is designed to scale resources related to database administration by providing a unified view of the health, security, performance, and availability of the entire MySQL and MariaDB server environment.

    SQL Diagnostic Manager for MySQL is a ‘database administrator in a box’ for MySQL and MariaDB that enables database administrators to manage more database servers, tune their database servers, and find and fix problems with their database applications before they become serious problems and costly outages. It proactively monitors enterprise database environments. It provides expert advice on how even those who are new to MySQL and MariaDB can tighten security, optimize performance and reduce downtime of their MySQL and MariaDB powered systems.

    Read more in the full solution brief.

    Find and fix MySQL performance problems on-premises and in the cloud with SQL Diagnostic Manager for MySQL.

    The post Benefits of SQL Diagnostic Manager for MySQL (formerly Monyog) appeared first on Monyog Blog.

    Using MariaDB MaxScale for Archiving MariaDB Database with Mq and Tee filters

    Archiving MariaDB Database with Mq and Tee filters

    This whitepaper is about how to use MariaDB MaxScale filters for Archiving MariaDB database. We have considered two MariaDB / MySQL servers in this exercise, one is the MariaDB Production Database Server and other one for Archiving MariaDB Production Database Server . We have also accommodated RabbitMQ and MariaDB MaxScale servers in their respective Instances .

    Instance Details IP MariaDB Production Database Server 192.168.56.101 MariaDB Archive Database Server 192.168.56.102

    Our use case is a MariaDB Production Database Server and MariaDB archive Database Server where only INSERT, UPDATE AND DELETE statements are routed. The queries routed to the archive servers are also transformed into a canonical format and sent to a RabbitMQ broker for analysis. This setup allows us to control what we send to the server and could possibly allow us to filter out DELETE statements completely, making the archive server a true archive of all data.

    Reference source – MariaDB.com 

    Installation and configuration of MariaDB MaxScale

    We have blogged about MariaDB MaxScale installation and configuration – https://minervadb.com/index.php/2019/06/06/installation-and-configuration-of-mariadb-galera-cluster-and-mariadb-maxscale-on-centos/

    Installation and configuration of RabbitMQ Server on CentOS 7

    First we need the EPEL and Erlang repositories for CentOS 7 after which we will install the RabbitMQ server:

    sudo yum -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm sudo yum -y install http://packages.erlang-solutions.com/erlang-solutions-1.0-1.noarch.rpm sudo yum -y install https://www.rabbitmq.com/releases/rabbitmq-server/v3.5.6/rabbitmq-server-3.5.6-1.noarch.rpm

    Start RabbitMQ Server

    sudo systemctl start rabbitmq-server

    Create RabbitMQ msguser account ( with read, write and configurations permissions on the default virtual host ) to relay the messages to the broker using rabbitmqctl command:

    sudo rabbitmqctl add_user "msguser" "msgpwd" sudo rabbitmqctl set_permissions "msguser" ".*" ".*" ".*"

    Configure MariaDB MaxScale Server adding both MariaDB Production Database Server and MariaDB Archive Database Server

    [Production-DB] type=server address=192.168.56.101 port=3306 protocol=MySQLBackend [Archive-DB] type=server address=192.168.0.102 port=3000 protocol=MySQLBackend

    After successfully configuring the Production-DB and Archive-DB servers,  We need a monitor module for those servers, We have blogged about installation and configuration of MariaDB MaxScale monitoring here

    [MySQL Monitor] type=monitor module=mysqlmon servers=Production-DB, Archive-DB user=maxscaleuser passwd=maxscaleuserpassword monitor_interval=5000

    The monitor will use the user maxscaleuser with the password maxscalepassword to connect to the servers and query them for their state. In the servers parameter we have listed both of the Production-DB and Archive-DB servers. The monitor_interval parameter controls how often the monitor will poll the servers for status. For this tutorial, we’ve set it to 5000 milliseconds.

    Next step is the configuration of the Production and Archive services. The Production service will be the main access point to the system.

    [Production-DB] type=service router=readconnroute servers=Production-DB user=maxscaleuser passwd=maxscaleuserpassword filters=Tee [Archive-DB] type=service router=readconnroute servers=Archive-DB user=maxscaleuser passwd=maxscaleuserpassword filters=MQ Filter

    The filters parameters for the services refer to the filters we will be creating next. The Production service will use the Tee filter to duplicate INSERT, UPDATE and DELETE statements to the Archive service. The statements passed to the Archive service will use the MQ Filter to send the canonical versions of the statements to the RabbitMQ broker. The Production service will use the Production-DB server and the Archive service will use the Archive-DB server. Both services user the maxscaleuser user with the maxscalepassword password.

    Next we will configure the listeners for these two services.

    [Production Listener] type=listener service=Production protocol=MySQLClient port=4000 [Archive Listener] type=listener service=Archive protocol=MySQLClient port=4001

    The port parameter controls which port the listener will listen on and where the client connections should be made. The service parameter tells which listener belongs to which service.

    After the services and their listeners are configured we will configure the two filters we’ll use. We begin with the Tee filter.

    [Tee] type=filter module=tee service=Archive match=\(insert\)\|\(update\)\|\(delete\)

    The service parameter controls which service we want to duplicate the statements to. This needs to be set to the section name of the archive service: Archive. The match parameter is a regular expression which, if matched, cause the statement to be duplicated. Here we will want a regular expression which will match the INSERT, UPDATE and DELETE statements.

    After the Tee filter is configured, we will configure the Mqfilter to communicate with the RabbitMQ server we’ve set up.

    [MQ Filter] type=filter module=mqfilter hostname=192.168.56.101 port=5672 username=msguser password=msgpwd exchange=msg-ex-1 queue=msg-queue-1 key=MaxScale

    The hostname is the address and the port is the port of the RabbitMQ server. For username and password we will use the msguser and msgpwd credentials we created earlier. The exchange is the name of the RabbitMQ exchange we’re using and the key is the key used for all the sent messages. The messages will be stored in the mxs-queue-1 queue.

    The next thing to configure is the administration interface.

    [MaxAdmin Service] type=service router=cli [MaxAdmin Listener] type=listener service=MaxAdmin Service protocol=maxscaled port=6603

    We have created MaxScale configuration file in  /etc/maxscale.cnf , Here is the complete configuration file:

    # The production and archive servers [Production-DB] type=server address=192.168.56.101 port=3306 protocol=MySQLBackend [Archive-DB] type=server address=192.168.56.102 port=3000 protocol=MySQLBackend # MySQL server monitor [MySQL Monitor] type=monitor module=mysqlmon servers=Production-DB, Archive-DB user=maxscaleuser passwd=maxscalepassword monitor_interval=5000 # Production service [Production] type=service router=readconnroute servers=Production-DB user=maxscaleuser passwd=maxscalepassword filters=Tee # Archive service [Archive] type=service router=readconnroute servers=Archive-DB user=maxscaleuser passwd=maxscalepassword filters=MQ Filter # Listeners for the services [Production Listener] type=listener service=Production protocol=MySQLClient port=4000 [Archive Listener] type=listener service=Archive protocol=MySQLClient port=4001 # Tee filter to duplicate insert, update and delete # statements to the archive server [Tee] type=filter module=tee service=Archive match=\(insert\)\|\(update\)\|\(delete\) # Mqfilter to log the canonical versions of the archive # server queries to a RabbitMQ broker [MQ Filter] type=filter module=mqfilter hostname=192.168.56.101 port=5672 username=msguser password=msgpwd exchange=msg-ex-1 queue=msg-queue-1 key=MaxScale # Administration interface setup [MaxAdmin Service] type=service router=cli [MaxAdmin Listener] type=listener service=MaxAdmin Service protocol=maxscaled port=6603

    Testing the new infra. with MariaDB MaxScale and RabbitMQ Server

    Step 1. Restart MariaDB MaxScale:

    sudo systemctl start maxscale

    Step 2. Confirm the state of the two servers with MaxAdmin:

    maxadmin list servers Servers. -------------------+-----------------+-------+-------------+-------------------- Server | Address | Port | Connections | Status -------------------+-----------------+-------+-------------+-------------------- Production-DB | 192.168.56.101 | 3306 | 0 | Running Archive-DB | 192.168.56.102 | 3000 | 0 | Running -------------------+-----------------+-------+-------------+--------------------

    Step 3. check the queue status on the RabbitMQ server:

    sudo rabbitmqctl list_queues Listing queues …….

    So now if we connect to MariaDB Production Service on port 4000 and execute the data modifying statement then we should be seeing the equal number of statements being sent to the RabbitMQ Server:

    MariaDB [(none)]> insert into DB1.tab1 values(500); Query OK, 1 row affected (0.09 sec) MariaDB [(none)]> insert into DB1.tab1 values(1000); Query OK, 1 row affected (0.07 sec) MariaDB [(none)]> update DB1.tab1 set id = 250; Query OK, 5 rows affected (0.08 sec) Rows matched: 5  Changed: 5 Warnings: 0

    On the RabbitMQ server we can see the new queue msg-queue-1 with three new messages in it.

    sudo rabbitmqctl list_queues Listing queues ... msg-queue-1 3

    We can also see the data on the Archive Server:

    MariaDB [(none)]> select * from DB1.tab1; +------+ | id   | +------+ |    0 | |    0 | +------+ 2 rows in set (0.00 sec)

     

    The post Using MariaDB MaxScale for Archiving MariaDB Database with Mq and Tee filters appeared first on MySQL Consulting, Support and Remote DBA Services.

    Pages