Starting with MySQL 8.0 we are introducing a new feature which will allow configuration variables to be persisted. This means that for a number of configuration settings, you will no longer need to manually edit a configuration file and can instead make modifications directly via an SQL connection.…
10 Older Entries »
MySQL has just released MySQL 8.0 DR (and yes, DR stands for Don’t Run-it-in-production-yet) so let’s jump right in and take a look at the hottest new features coming in this new release:
1. Persistent runtime configuration changes.
Love it. From now on we’ll be able to use
innodb_buffer_pool_size = X; instead of
innodb_buffer_pool_size = X; for the runtime changes to
persist during a restart. It may not make much sense if you’re
using a modern database that doesn’t even have a configuration
file, but for us who lived with MySQL for over 20 years, this is
How does it work? In a nutshell, these changes are saved in
mysqld-auto.cnf file in MySQL data directory.
2. MySQL privilege tables are now InnoDB. I think this was …[Read more]
MySQL Server has an extensive collection of SQL modes which control a range of behavior ranging from compatibility modes for other RDBMS dialects (ANSI_QUOTES, PIPES_AS_CONCAT) to security (NO_AUTO_CREATE_USER) to explicit storage engine selection (NO_ENGINE_SUBSTITUTION) to restricting lossy implicit data conversions ( …[Read more]
The default or “out of the box” experience is simply the experience for most people, so it’s critically important for us to ensure that the default configuration offers a good experience for the vast majority of cases. This is always a challenge, due to the matrix of factors involved–hardware configurations, software stacks, application use cases, etc.–but it’s most certainly a worthy endeavor.…
When MySQL becomes too slow (or too unstable), temptation usually is to tweak the MySQL configuration file. Indeed, it’s a good place to start. But if you ever looked at the available configuration options, you know things can get messy – MySQL now has over 450 configuration variables for your consideration, that are not classified in any way, and neither of them are included in the stock my.cnf. It’s hard to know where to start!
I’m hoping that this blog post will help you overcome the anxiety of tuning MySQL, whether you’re setting up a new server, or tuning an already running server for better performance.
Don’t do it the way rookies do it
During the last 9 years I’ve spent at Percona working as a MySQL performance and scalability consultant, I found that customers often use the trial and error approach when tuning MySQL configuration: they change a few things and check if it …[Read more]
Landing on an enterprise with ongoing projects mean that servers are often handed to IT staff without complete knowledge of what's inside. I've built a simple script, scraping from here and there, to gather a summary of relevant information. Once you've gained remote access to the MySQL instance, you can execute the queries to identify the following information regarding the target database
The binary log contains “events” that describe database changes. On a basic installation with default options, it's not turned on. This log is essential for accommodating the possible following requirements:
Replication: the binary log on a master replication server provides a record of the data changes to be sent to slave servers. Point in Time recovery: allow to recover a database from a full
MySQL 5.7 introduces a change in the way we query for global variables and status variables: the INFORMATION_SCHEMA.(GLOBAL|SESSION)_(VARIABLES|STATUS) tables are now deprecated and empty. Instead, we are to use the respective performance_schema.(global|session)_(variables|status) tables.
But the change goes farther than that; there is also a security change. Oracle created a pitfall of 2 changes at the same time:
- Variables/status moved to a different table
- Privileges required on said table
As an example, my non-root user gets:
mysql> show session variables like 'tx_isolation'; ERROR 1142 (42000): SELECT command denied to user 'normal_user'@'my_host' for table 'session_variables'
Who gets affected by this? Nearly everyone and everything.
- Your Nagios will not be able to read …
10 Older Entries »