Disable SELinux? Think again!

SELinux is always a complicated topic. If you search this on the web, most people will advise just disabling it, but that may not be acceptable from a security point of view in your organization. In this case, we are going to see how to solve an issue with SELinux and MySQL log rotation

We had configured log rotation as per this post. The scripts seemed to work perfectly when running manually. However, when running under cron they would fail to run. Since there were no other errors in the logs, eventually I tracked that down to SELinux. What I found is that SELinux default policies prevent logrotate daemon from making the changes to files outside of /var/log. In this case, MySQL logs were living on /var/lib/mysql so that was clearly the problem.

Figuring out SELinux

The first thing to do when debugging a …

Multi-Valued Indexes are going to change the way you think about using JSON data and the way you architect your data. Before MySQL 8.0.17 you could store data in JSON arrays but trying to search on that data in those embedded arrays was tricky and usually required a full table scan.  But now it is easy and very quick to search and to access the data in JSON arrays.
Multi-Valued IndexesA Multi-Valued Index (MVI) is a secondary index defined on a column made up of an array of values.  We are all used to traditional indexes where you have one value per index entry, a 1:1 ratio.  A MVI can have multiple records for each index record.  So you can have multiple postal codes, phone numbers, or other attributes from one JSON document indexed for quick access. See Multi-Valued Indexes for details.

…

In my roles as a DBA at various companies, I generally found the tooling to be quite lacking. Everything from metrics collection, alerting, backup management; they were either missing, incomplete or implemented poorly. DBA-Tools was born from a desire to build backup tools that supported my needs in smaller/non-cloud environments. As BASH is easily the most common shell available out there on systems running MySQL® or MariaDB®, it was an easy choice.

How DBA-Tools came to be

While rebuilding my home-lab two years ago, I decided I wanted some simple tools for my database environment. Being a fan of NOT re-inventing the wheel, I thought I would peruse GitHub and Gitlab to see what others have put together. Nothing I saw looked quite like what I wanted. They all hit one or more of the checkboxes I wanted, but never all of them.

My checklist when searching …

While we often run MySQL on larger scale systems in Production for Test and Dev, sometimes we want to run MySQL on the tiniest cloud instances possible or just run it on our laptops. In these cases, MySQL 8 and MySQL 5.7 memory consumption is quite important.

In comparing MySQL 8 vs MySQL 5.7, you should know that MySQL 8 uses more memory. Basic tests on a 1GB VM with MySQL 8 and MySQL 5.7 (actually they’re Percona Server versions) running the same light workload, I see the following vmstat output:

MySQL 5.7 vmstat output

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 4  0  65280  71608  58352 245108    0    0  2582  3611 1798 8918 18  9 11 33 30
 4  0  65280  68288  58500 247512    0    0  2094  2662 1769 8508 19  9 13 30 29
 3  1  65280  67780  58636 249656    0    0  2562  3924 1883 9323 20  9  7 37 27
 4  1  65280 …

Today I am going to explain ways to execute shell or bash commands from mysql clients or stored procedure and function. There are basically 2 method to do so: Method 1: Use MySQL Client inbuilt feature  To run single command: \! command or system command. eg \! uptime or system command  To get terminal \! bash or \! sh
Method 2: Deploy external plugin (lib_mysqludf_sys) Step 1: Download lib_mysqludf_sys from github:

git clone https://github.com/mysqludf/lib_mysqludf_sys.git 

Step 2: Install libmysqlclient15-dev, for Ubuntu you …

FromDual has the pleasure to announce the release of the new version 0.9.2 of its popular FromDual Ops Center for MariaDB and MySQL focmm.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to manage MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center for MariaDB and MySQL (focmm) can be downloaded from here. How to install and use focmm is documented in the …

Partnering to participate knowledge sharing for the community of Database Administrators.

In continuation to the community contribution with knowledge gained out of experience and networking, Mydbops IT Solutions, conducted its 4th Database Meetup on Saturday, 3rd of August, 2019. Here was the first time, that we had changed our venue. With the like-minded sponsor in M/s.Zenefits Technologies India Pvt. Ltd. They played a role of a perfect host for all of us as attendees. Their venue was a perfectly equipped to suit the knowledge sharing exercise.

Key note by Mr.Ramesh Aithal, Head of Engineering at Zenefits was build on statistics, of how the Industry is taking shape and the galore of opportunities in the coming days. The attendees from multiple companies were a great …

To set correct system variable values is the essential step to get the correct server behavior against the workload.
In MySQL, we have many System variables that can be changed at runtime, and most of them can be set at the session or global level.

To change the value of a system variable at the global level in the past, users needed to have SUPER privileges. Once the system variable value is modified as global, the server will change this behavior for the session, and obviously as global scope.

For instance, one of the most commonly adjusted variables is probably max_connections.

If you have max_connection=100 in your my.cnf or as the default value, and during the day as DBA you notice that it is not enough, it is easy just to add new connections on the fly with the command:

SET GLOBAL …

Introduction In this article, we are going to see how we can limit the SQL query result set to the Top-N rows only. Limiting the SQL result set is very important when the underlying query could end up fetching a very large number of records, which can have a significant impact on application performance. Why limit the number of rows of a SQL query? Fetching more data than necessary is the number one cause of data access performance issues. When a given business use case is developed, the amount of data available... Read More

The post How to limit the SQL query result set to Top-N rows only appeared first on Vlad Mihalcea.

ProxySQL 2.0.5, released by ProxySQL, is now available for download in the Percona Repository along with Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL and database servers in the MySQL ecosystem (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

This release includes ProxySQL 2.0.5 which fixes many bugs and introduces a number of features …