Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 29 10 Older Entries

Displaying posts with tag: primary (reset)

(More) Secure local passwords in MySQL 5.6 and up
+1 Vote Up -0Vote Down

I log into a lot of different servers running MySQL and one of the first things I do is create a file in my home directory called ‘.my.cnf’ with my credentials to that local mysql instance:

[client]
user=root
password=secret

This means I don’t have to type my password in every time, nor am I tempted to include it on the command line with -p and get the dreaded (but completely accurate):

Warning: Using a password on the command line interface can be insecure.

MySQL 5.6 introduces a utility to make this …

  [Read more...]
Schema changes in MySQL for OpenStack Trove users
+0 Vote Up -0Vote Down

People using OpenStack Trove instances can hit a common issue in the MySQL world: how to perform schema change operations while minimizing the impact on the database server? Let’s explore the options that can allow online schema changes.

Summary

With MySQL 5.5, pt-online-schema-change from Percona Toolkit is your best option for large tables while regular ALTER TABLE statements are only acceptable for small tables. Also beware of metadata locks.

With MySQL 5.6, almost all types of …

  [Read more...]
Sys Schema for MySQL 5.6 and MySQL 5.7
+1 Vote Up -0Vote Down

Performance Schema (P_S) has been available since MySQL 5.5, more than 4 years ago. It is still difficult to see production servers with P_S enabled, even with MySQL 5.6 where the feature is enabled by default. There have been several complaints like the overhead, that the fix is a work in progress, and the ease of use. 52 tables to query and 31 configuration variables is enough to scare people.

There is a solution for the second problem, the usability. It’s name is “sys schema“. It is a collection of views, functions and procedures to make P_S more user friendly.

  [Read more...]
How a set of queries can be killed in MySQL using Percona Toolkit’s pt-kill
+0 Vote Up -0Vote Down

You might have encountered situations where you had to kill some specific select queries that were running for long periods and choking the database. This post will go into more detail with an example of report query offloading.

Report query (select) offloading to a slave server is a common practice to reduce the workload of the master DB server. The long running selects will be executed in the slave for report generation. I have observed in many cases where the slave used to get delayed or the slave DB encounters a slowdown due to some heavy long-running orphaned selects from some wrong reports.

There are two …

  [Read more...]
Avoiding MySQL ALTER table downtime
+0 Vote Up -0Vote Down

MySQL table alterations can interrupt production traffic causing bad customer experience or in worst cases, loss of revenue. Not all DBAs, developers, syadmins know MySQL well enough to avoid this pitfall. DBAs usually encounter these kinds of production interruptions when working with upgrade scripts that touch both application and database or if an inexperienced admin/dev engineer perform the schema change without knowing how MySQL operates internally.

Truths
* Direct MySQL ALTER table locks for duration of change (pre-5.6)
* Online DDL in MySQL 5.6 is not always online and may incurr locks
* Even with …


  [Read more...]
MySQL’s INNODB_METRICS table: How much is the overhead?
+0 Vote Up -0Vote Down

Starting with MySQL 5.6 there is an INNODB_METRICS table available in INFORMATION_SCHEMA which contains some additional information than provided in the SHOW GLOBAL STATUS output – yet might be more lightweight than PERFORMANCE_SCHEMA.

Too bad INNODB_METRICS was designed during the Oracle-Sun split under MySQL leadership and so it covers only InnoDB counters. I think this would be a great replacement to all counters that are currently provided though SHOW STATUS – it captures more information such as providing MIN/MAX counts for …

  [Read more...]
Typical misconceptions on Galera Replication for MySQL
+0 Vote Up -0Vote Down

Even if a Galera node looks like a regular MySQL server, the underlying replication mechanism is very different. This implies some changes in the way you have to configure the Galera nodes. Here are some of the most common misconceptions about Galera when using Percona XtraDB Cluster.

Q: Why should I enable binary logging as it is not needed by Galera replication?
Unlike for regular asynchronous MySQL replication, it is true that …

  [Read more...]
Optimizing MySQL for Zabbix
+0 Vote Up -0Vote Down


This blog post was inspired by my visit at the annual Zabbix Conference in Riga, Latvia this year, where I gave a couple of talks on MySQL and beyond.

It was a two day single-track event with some 200 participants, a number of interesting talks on Zabbix (and related technologies) and really well-organized evening activities. I was amazed how well organized the event …


  [Read more...]
Log rotate and the (deleted) MySQL log file mystery
+0 Vote Up -0Vote Down

Did your logging stop working after you set up logrotate? Then this post might be for you.

Archive your log files!

Some time ago, Peter Boros wrote about Rotating MySQL Slow Logs safely, explaining the steps of a “best practice” log rotate/archive. This post will add more info about the topic.

When running logrotate for MySQL (after proper setting the /etc/logrotate.d/mysql conf file) from anacron, there’s a situation that you might potentially face if the user and password used to execute the …

  [Read more...]
Impressions from MongoDB Day London 2014
+0 Vote Up -0Vote Down

I visited MongoDB Day in London on November 6. Here are a few observations:

App-Developer Centric. It is interesting to see how much MongoDB is about developers; the ops side is something which is a necessary evil developers have to deal with. The ops topics covered in principle that there are no topics about choices of operating systems or hardware for MongoDB beyond flash and more memory.

Development Stacks. Being application centric there was good coverage of the MongoDB-powered …

  [Read more...]
Showing entries 1 to 10 of 29 10 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.