Planet MySQL

MySQL for Visual Studio 2.0.3 has been released

The MySQL Windows Experience Team is proud to announce the release of MySQL for Visual Studio 2.0.3 m2. Note that this is a development preview release and not intended for production usage.

MySQL for Visual Studio 2.0.3 M2 is the second development preview release of the MySQL for Visual Studio 2.0 series.  This series adds support for the new X DevAPI. The X DevAPI enables application developers to write code that combines the strengths of the relational and document models using a modern, NoSQL-like syntax that does not assume previous experience writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see http://dev.mysql.com/doc/x-devapi-userguide/en/. For more information about how the X DevAPI is implemented in MySQL for Visual Studio, and its usage, see http://dev.mysql.com/doc/refman/5.7/en/mysql-shell-visual-studio.html.

Please note that the X DevAPI requires at least MySQL Server version 5.7.12 or higher with the X Plugin enabled. For general documentation about how to get started using MySQL as a document store, see http://dev.mysql.com/doc/refman/5.7/en/document-store.html.

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

MySQL for Visual Studio 2.0.3 m2 can also be downloaded by using the product standalone installer found at http://dev.mysql.com/downloads/windows/visualstudio/, under the tab “Development Releases”.

Changes in MySQL for Visual Studio 2.0.3 m2

Bugs Fixed

  • The “mysqlx” module was not imported properly to execute JavaScript queries.  (Bug #23091964, Bug #81052)
  • After opening a valid MySQL connection and creating a new JavaScript MySQL script, disconnecting then reconnecting to the MySQL Server while changing the port to 33060 would fail.
  • MySQL for Visual Studio now shows a message stating that a SSL connection is required by the MySQL server if the require_secure_transport variable is set.
  • All script editors now display detailed information about the connection used. Before, the information was displayed in the toolbar as labels, but now all information is consolidated in a menu opened where the connection name is displayed. Additional information includes the connection method, host identifier, server version, user, and schema.
  • Output from executing JavaScript and Python commands were not visible unless the Output window was already opened.  The Output window now automatically opens when executing commands.

What’s new in 2.0.3 m2

  • Improved the handling of errors, warnings and execution stats output of X DevAPI statements. All messages are properly handled and displayed after batch or console execution.
  • Added SSL support for MySQL connections that use the X Protocol. SSL support works with PEM files, so SSL connections need to be created through the “MySQL Connections Manager” in MySQL for Visual Studio, or from MySQL Workbench.
  • Added support for the following X DevAPI functions:
    parseUri() and isOpen().
  • A new “MySQL Output” pane was added that contains a results grid view similar to the view found in MySQL Workbench. It contains the following data for executed statements: Success, Execution index, Execution Time, Query Text, Message (output from the server), and Duration / Fetch. This functionality is available for JavaScript and Python queries.
  • Added “Console Mode” support for JavaScript and Python script editors, where query execution mimics the way the MySQL Shell works, meaning X DevAPI statements are executed after hitting “ENTER” and results are displayed inline.
  • Added the ability to switch between “Batch” (execute multiple statements) and “Console” (execute each statement after pressing Enter) modes, from the Query Editor toolbar as a dropdown list.
  • A MySQL connection manager dialog was added to help fully manage MySQL connections. It supports connection sharing with MySQL Workbench, and supports create, edit, configure, and delete actions.  MySQL connections created with the connection manager where the password is securely stored in the system’s password vault functions with the Server Explorer in Visual Studio. The password is extracted from the password vault, and persists in the Server Explorer connections.

Known limitations

  • Some features such as Entity Framework and some Server Explorer functionality like drag & drop elements into a Dataset Designer or Design Tables do not work in this version.

Quick links

Enjoy and thanks for the support!

MySQL for Visual Studio Team.


PlanetMySQL Voting: Vote UP / Vote DOWN

Amazon RDS and pt-online-schema-change

In this blog post, I discuss some of the insights needed when using Amazon RDS and pt-online-schema-change together.

The pt-online-schema-change tool runs DDL queries (ALTER) online so that the table is not locked for reads and writes. It is a commonly used tool by community users and customers. Using it on Amazon RDS requires knowing about some specific details. First, a high-level explanation of how the tool works.

This is an example from the documentation:

pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor

The tool runs an ALTER on the table “actor” from the database “sakila.” The alter adds a column named “c1” of type “integer.” In the background, the tool creates a new empty table similar to “actor” but with the new column already added. It then creates triggers on the original table to update the corresponding rows in the new table. After, it starts copying rows to the new table (this is the phase that takes the longest amount of time). When the copy is done, the tables are swapped, triggers removed and the old table dropped.

As we can see, it is a tool that uses the basic features of MySQL. You can run it on MySQL, Percona Server, MariaDB, Amazon RDS and so on. But when using Amazon, there is a hidden issue: you don’t have SUPER privileges. This means that if you try to run the tool on an RDS with binary logs enabled, you could get the following error:

DBD::mysql::db do failed: You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable) [for Statement "CREATE TRIGGER `pt_osc_db_table_del` AFTER DELETE ON `db`.`table` FOR EACH ROW DELETE IGNORE FROM `db`.`_table_new` WHERE `db`.`_table_new`.`table_id` <=> OLD.`table_id` AND `db`.`_table_new`.`account_id` <=> OLD.`account_id`"] at /usr/bin/pt-online-schema-change line 10583.

The following documentation page explains the reason for this message:

http://dev.mysql.com/doc/refman/5.7/en/stored-programs-logging.html

The bottom line is creating triggers on a server with binary logs enabled requires a user with SUPER privileges (which is impossible in Amazon RDS). The error message specifies the workaround. We need to enable the variable log_bin_trust_function_creators. Enabling it is like saying to the server:

“I trust regular users’ triggers and functions, and that they won’t cause problems, so allow my users to create them.”

Since the database functionality won’t change, it becomes a matter of trusting your users. log_bin_trust_function_creators is a global variable that can be changed dynamically:

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

Run the tool again. This time, it will work. After the ALTER is done, you can change the variable to 0 again.

After you’re done with the ALTER process, you can change the variable to “0” again.


PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Connector/NET 7.0.3 m2 development has been released

MySQL Connector/Net 7.0.3 is the second development release of MySQL Connector/Net  7.0 series.

MySQL Connector/Net 7.0 adds support for the new X DevAPI which enables developers to write code that combines the strengths of the relational and document models using a modern, NoSQL-like syntax that does not assume previous experience writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see this User’s Guide. For more information about how the X DevAPI is implemented in Connector/Net, please check the official product documentation.

Please note that the X DevAPI requires at least MySQL Server version 5.7.12 or higher with the X Plugin enabled. For general documentation about how to get started using MySQL as a document store, see this chapter at the reference manual.

Changes in MySQL Connector/Net 7.0.3 (2016-06-20, Milestone 2)

Functionality Added or Changed:

  • Fixed binary collations as strings instead of bytes.
  • Added TLS support for TLSv1.1 and TLSv1.2 when connecting to MySQL Server 5.7.

Bugs Fixed:

  • Added results to the Commit() and Rollback() Session X DevAPI methods, in order to read Warnings. This feature has limitations that will be addressed in a future release.
  • Replaced the use of “@” for “$” in JSON path expressions for X DevAPI usage. This feature has limitations that will be addressed in a future release.
  • Added X DevAPI support for TLSv1.0. This feature has limitations that will be addressed in a future release.

Nuget packages are available at:

https://www.nuget.org/packages/MySql.Data/7.0.3-DMR

https://www.nuget.org/packages/MySql.Data.Entity/7.0.3-DMR

https://www.nuget.org/packages/MySql.Fabric/7.0.3-DMR

https://www.nuget.org/packages/MySql.Web/7.0.3-DMR

We love to hear your thoughts or any comments you have about our product. Please send us your feedback at our forums, fill a bug at our community site, or leave us any comment at the social media channels.

Enjoy and thanks for the support!

On behalf of the MySQL Release Team


PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL 5.7 for production

With the time MySQL as database getting better in terms of High performance , scalability and security.
MySQL 5.7 new features : http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

As a MySQL user my favorites are from above list:

  •  New options for replication :
    Changing replication filters online including and excluding table/db and enabling GTID transaction online.
  •  Innodb related changes:
    Online buffer pool resize and many defaults are changed to more secure and optimized values.
  • Security features :
    Improved User authentication like default users, SSL and data encryption with key capabilities in order to secure overall database.
  • Monitoring and analysis statistics :
    Improved performance schema for live transactions analysis which we can not mostly find out with SHOW PROCESSLIST command or Using INFORMATION_SCHEMA database tables.
  • All in this very important is “Setting configuration variables dynamically while server is running.This change will save many downtime’s or mysqld service restarts.
  • Optimizer:
    New optimizer changes will be the one doing magic inside for Query performance.

Something New :

  • Multi-source replication
  • Innodb tablespace encryption using key
  • MySQL x-protocol and Document store using json datatype capabilities.
  • Optimized and secure defaults settings for initial MySQL database setup.

I believe these are the most imported mysql database areas used by MySQL user in production environment.

How many of you using mysql-5.7 in production or planning for implementation in future? Please share your experience with it.



PlanetMySQL Voting: Vote UP / Vote DOWN

Column Level Privileges in MySQL

Recently I experimented with column-level privileges in MySQL. Column-level privileges are fairly straightforward, but given how infrequently they are used I think there are a few areas worth discussing.

Here are a few high-level observations:

  • Users can execute INSERT and UPDATE statements that affect columns they don't have privileges on, as long as they rely on implicit defaults
  • Since SQL is row-based, it doesn't make sense to support column-level DELETE privileges, thus only SELECT, INSERT, and UPDATE are supported
  • You can grant privileges on multiple columns in one GRANT statement or multiple GRANT statements, the results are cumulative

Read on for more details on each type of column-level privilege, along with example queries.

SELECT

Users may only reference columns that they have explicity privileges on. This applies to the entire SELECT statement, not just the SELECT clause. If you try to reference a column that you do not have privileges on in the WHERE, GROUP BY, HAVING, or ORDER BY clause then you will get an error.

To illustrate this I created a table with two rows of sample data for testing:

``` mysql> create table good_stuff (

-> id int unsigned not null auto_increment primary key, -> ctime timestamp default current_timestamp, -> mtime timestamp default current_timestamp on update current_timestamp, -> is_deleted tinyint not null default 0, -> public varchar(255) null, -> protected varchar(255) null, -> private varchar(255) null -> ) engine = innodb;

Query OK, 0 rows affected (0.03 sec)

mysql> insert into good_stuff (id,public,protected,private)

-> values (DEFAULT,'Hello world!','Red','Secret');

Query OK, 1 row affected (0.00 sec)

mysql> insert into good_stuff (id,public,protected,private)

-> values (DEFAULT,'Hi Scott','Blue','Boo Scott');

Query OK, 1 row affected (0.01 sec) ```

If I grant SELECT privileges on two columns in that table to a user named "scott", then scott may select those two columns:

mysql> -- as root, grant SELECT privileges to scott mysql> grant select (public,protected) on good_stuff to scott@'%'; Query OK, 0 rows affected (0.00 sec)

mysql> -- as scott, test SELECT privileges mysql> select public,protected from good_stuff; +--------------+-----------+ | public | protected | +--------------+-----------+ | Hello world! | Red | | Hi Scott | Blue | +--------------+-----------+ 2 rows in set (0.00 sec)

But scott may not reference another column in the ORDER BY clause:

mysql> -- as scott, test SELECT privileges mysql> select public from good_stuff order by id; ERROR 1143 (42000): SELECT command denied to user 'scott'@'localhost' for column 'id' in table 'good_stuff'

Table-level privileges take precendence, so granting table-level SELECT privileges to a user overrides any column-level SELECT privileges they may have.

If you happen to grant SELECT privileges on all columns in a table to a user, then the user is allowed to run SELECT * queries.

INSERT

Users may only explicitly insert data into columns for which they have the INSERT privilege. INSERT privileges do not rely on SELECT privileges, so it is possible to configure a user who may write data but not read it. Default values may be used, but only implicitly. If you try to explicitly reference a default value you will get an error.

If I give scott INSERT privileges on only the public column in my table, he can still insert a row as long as he only references that one column, and default values will be used for other columns (id, ctime, mtime, is_deleted).

mysql> -- as root, grant INSERT privileges to scott mysql> grant insert (public) on good_stuff to scott@'%'; Query OK, 0 rows affected (0.00 sec)

For example this works:

mysql> -- as scott, test INSERT privileges mysql> insert into good_stuff (public) values ('Hi everybody'); Query OK, 1 row affected (0.01 sec)

And results in a row like this:

mysql> -- as root, select full row that scott inserted mysql> select * from good_stuff where public = 'Hi everybody'; +----+---------------------+---------------------+------------+--------------+-----------+---------+ | id | ctime | mtime | is_deleted | public | protected | private | +----+---------------------+---------------------+------------+--------------+-----------+---------+ | 3 | 2016-06-30 20:37:39 | 2016-06-30 20:37:39 | 0 | Hi everybody | NULL | NULL | +----+---------------------+---------------------+------------+--------------+-----------+---------+

These statements all fail even though they have the same intent, because they are explicitly referencing columns that scott does not have privileges to INSERT:

mysql> -- as scott, test INSERT privileges mysql> insert into good_stuff (id,public) values (null,'Is it okay if I do this?'); ERROR 1143 (42000): INSERT command denied to user 'scott'@'localhost' for column 'id' in table 'good_stuff' mysql> insert into good_stuff (id,public) values (DEFAULT,'Is it okay if I do this?'); ERROR 1143 (42000): INSERT command denied to user 'scott'@'localhost' for column 'id' in table 'good_stuff'

I would get the same results if I tried to use null or DEFAULT with the other columns that have defaults values (ctime, mtime, is_deleted).

UPDATE

UPDATE statements support column-level privileges much the same way as SELECT and INSERT. In order to explicitly update a column a user needs UPDATE privileges on that column, but columns can be set to default values implicitly. If you reference a column in the WHERE clause of an UPDATE, then you need SELECT privileges on that column.

First I grant UPDATE privileges on the public column to scott:

mysql> -- as root, grant UPDATE privileges to scott mysql> grant update (public) on good_stuff to scott@'%'; Query OK, 0 rows affected (0.01 sec)

This is allowed:

mysql> -- as scott, test UPDATE privileges mysql> update good_stuff set public = lower(public); Query OK, 1 row affected (0.00 sec) Rows matched: 4 Changed: 1 Warnings: 0

This is also allowed:

mysql> -- as scott, test UPDATE privileges mysql> update good_stuff set public = upper(public) where protected = 'Blue'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0

Please note that the previous two statements will only work if scott has both SELECT and UPDATE privileges on the public column, since the existing value is being used in the UPDATE, so that requires both a read and a write.

These are not allowed because the WHERE clause references a column for which scott does not have SELECT privileges:

mysql> -- as scott, test UPDATE privileges mysql> update good_stuff set public = upper(public) where id = 2; ERROR 1143 (42000): SELECT command denied to user 'scott'@'localhost' for column 'id' in table 'good_stuff' mysql> update good_stuff set is_deleted = DEFAULT; ERROR 1143 (42000): UPDATE command denied to user 'scott'@'localhost' for column 'is_deleted' in table 'good_stuff'

Even though scott does not have privileges to update the mtime column, the on update current_timestamp is still invoked implicitly. To verify this I can select back the "Hi everybody" row I selected earlier to confirm that the mtime value changed:

mysql> -- as root, select full row that scott inserted mysql> select * from good_stuff where id = 3; +----+---------------------+---------------------+------------+--------------+-----------+---------+ | id | ctime | mtime | is_deleted | public | protected | private | +----+---------------------+---------------------+------------+--------------+-----------+---------+ | 3 | 2016-06-30 20:37:39 | 2016-06-30 21:15:03 | 0 | hi everybody | NULL | NULL | +----+---------------------+---------------------+------------+--------------+-----------+---------+

DELETE

As mentioned above, column-level DELETE privileges are not supported in MySQL.


PlanetMySQL Voting: Vote UP / Vote DOWN

Rescuing a crashed pt-online-schema-change with pt-archiver

This article discusses how to salvage a crashed pt-online-schema-change by leveraging pt-archiver and executing queries to ensure that the data gets accurately migrated. I will show you how to continue the data copy process, and how to safely close out the pt-online-schema-change via manual operations such as RENAME TABLE and DROP TRIGGER commands. The normal process to recover from a crashed pt-online-schema-change is to drop the triggers on your original table and drop the new table created by the script. Then you would restart pt-online-schema-change. In this case, this wasn’t possible.

A customer recently needed to add a primary key column to a very busy table (with around 200 million rows). The table only had a unique key on one column (called our_id below). The customer had concerns about slave lag, and wanted to ensure there was little or no lag. This, as well as the fact that you can’t add a primary key as an online DDL in MySQL and Percona Server 5.6, meant the obvious answer was using pt-online-schema-change.

Due to the sensitivity of their environment, they could only afford one short window for the initial metadata locks, and needed to manually do the drop swap that pt-online-schema-change normally does automatically. This is where no-drop-triggers and no-swap-tables come in. The triggers will theoretically run indefinitely to keep the new and old tables in sync once pt-online-schema-change is complete. We crafted the following command:

pt-online-schema-change --execute --alter-foreign-keys-method=auto --max-load Threads-running=30 --critical-load Threads_running=55 --check-slave-lag mysql-slave1,mysql-slave2,mysql-slave3 --max−lag=10 --chunk-time=0.5 --set-vars=lock_timeout=1 --tries="create_triggers:10:2,drop_triggers:10:2" --no-drop-new-table --no-drop-triggers --no-swap-tables --chunk-index "our_id" --alter "ADD newcol BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST" D=website,t=largetable --nocheck-plan

You can see some of the specifics of other flags and why we used them in the Percona Toolkit Manual.

Once we ran the command the customer got concerned, as their monitoring tools weren’t showing any work done (which is by design, pt-online-schema-change doesn’t want to hurt your running environment). The customer ran strace -p to verify it was working. This wasn’t a great choice as it crashed pt-online-schema-change.

At this point, we knew that the application (and management) would not allow us to take new metadata locks to create triggers on the table, as we had passed our metadata lock window.

So how do we recover?

First, let’s start with a clean slate. We issued the following commands to create a new table, where __largetable_new is the table created by pt-online-schema-change:

CREATE TABLE mynewlargetable LIKE __largetable_new; RENAME TABLE __largetable_new TO __largetable_old, mynewlargetable TO __largetable_new; DROP TABLE __largetable_old;

Now the triggers on the original table, largetable are updating the new empty table that has our new schema.

Now let’s address the issue of actually moving the data that’s already in largetable to __largetable_new. This is where pt-archiver comes in. We crafted the following command:

pt-archiver --execute --max-lag=10 --source D=website,t=largetable,i=our_id --dest D=website,t=__largetable_new --where "1=1" --no-check-charset --no-delete --no-check-columns --txn-size=500 --limit=500 --ignore --statistics

We use pt-archiver to slowly copy records non-destructively to the new table based on our_id and WHERE 1=1 (all records). At this point, we periodically checked the MySQL data directory over the course of a day with ls -l to compare table sizes.

Once the table files were close to the same size, we ran counts on the tables. We noticed something interesting: the new table had thousands more records than the original table.

This concerned us. We wondered if our “hack” was a mistake. At this point we ran some verification queries:

select min(our_id) from __largetable_new; select max(our_id) from __largetable_new; select min(our_id) from largetable; select max(our_id) from largetable;

We learned that there were older records that didn’t exist in the live table. This means that pt-archiver and the DELETE trigger may have missed each other (i.e., pt-archiver was already in a transaction but hadn’t written records to the new table until after the DELETE trigger already fired).

We verified with more queries:

SELECT COUNT(*) FROM largetable l WHERE NOT EXISTS (SELECT our_id FROM __largetable_new n WHERE n.our_id=l.our_id);

They returned nothing.

SELECT COUNT(*) FROM __largetable_new n WHERE NOT EXISTS (SELECT our_id FROM largetable l WHERE n.our_id=l.our_id);

Our result showed 4000 extra records in the new table. This shows that we ended up with extra records that were deleted from the original table. We ran other queries based on their data to verify as well.

This wasn’t a huge issue for our application, and it could have been easily dealt with using a simple DELETE query based on the unique index (i.e., if it doesn’t exist in the original table, delete it from the new one).

Now to complete the pt-online-schema-change actions. All we need to do is the atomic rename or drop swap. This should be done as soon as possible to avoid running in a degraded state, where all writes to the old table are duplicated on the new one.

RENAME TABLE largetable TO __largetable_old , __largetable_new TO largetable;

Then drop the triggers for safety:

DROP TRIGGER pt_osc_website_largetable_ins; DROP TRIGGER pt_osc_website_largetable_upd; DROP TRIGGER pt_osc_website_largetable_del;

At this point it is safer to wait for the old table to clear out of the buffer pool before dropping it, just to ensure there is no impact on the server (maybe a week to be safe). You can check information_schema for a more accurate reading on this:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE TABLE_NAME = '`website`.`__largetable_old`'; +----------+ | count(*) | +----------+ | 279175 | +----------+ 1 row in set (8.94 sec)

Once this goes to 0 you can issue:

DROP TABLE __largetable_old;


PlanetMySQL Voting: Vote UP / Vote DOWN

Planets9s - MySQL on Docker: Building the Container Images, Monitoring MongoDB and more

Welcome to this week’s Planets9s, covering all the latest resources and technologies we create around automation and management of open source database infrastructures.

MySQL on Docker: Building the Container Image

Building a docker image for MySQL is essential if you’d like to customize MySQL to suit your needs. In this second post of our ‘MySQL on Docker’ series, we show you two ways to build your own MySQL Docker image - changing a base image and committing, or using Dockerfile. We show you how to extend the Docker team’s MySQL image, and add Percona XtraBackup to it.

Read the blog

Sign up for our webinar on Monitoring MongoDB - Tuesday July 12th

MongoDB offers many metrics through various status overviews or commands, and as MySQL DBA, it might be a little unfamiliar ground to get started with. In this webinar on July 12th, we’ll discuss the most important ones and describe them in ordinary plain MySQL DBA language. We’ll have a look at the open source tools available for MongoDB monitoring and trending. And we’ll show you how to leverage ClusterControl’s MongoDB metrics, dashboards, custom alerting and other features to track and optimize the performance of your system.

Sign up for the webinar

StreamAMG chooses ClusterControl to support its online European football streaming

This week we’re delighted to announce a new ClusterControl customer, StreamAMG (Advanced Media Group), Europe’s largest player in online video solutions, helping football teams such as Liverpool FC, Aston Villa, Sunderland AFC and the BBC keep fans watching from across the world. StreamAMG replaced its previous environment, based on a master-slave replication topology, with a multi-master Galera Cluster; and Severalnines’ ClusterControl platform was applied to automate operational tasks and provide visibility of uptime and performance through monitoring capabilities.

Read the story

That’s it for this week! Feel free to share these resources with your colleagues and follow us in our social media channels.

Have a good end of the week,

Jean-Jérôme Schmidt
Planets9s Editor
Severalnines AB

Tags:
PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Password Security Changes for PHP Developers

MySQL 5.7 introduced many new facets to password security. The first thing most notice is that you are assigned a random root password at installation time. You then have to search the log file for this random password, use it to login, and then change it. For the examples on the post I am using a fresh install of 5.7.13 on Oracle Linux 7.1 and was provided with the easy to remember password of nLvQRk7wq-NY which to me looked like I forgot to hit escape when trying to get out of vim. A quick ALTER USER to change the password and you are on your way. Defaults Password Lifetime and Complexity5.7.13 now has the default password lifetime set to 0 or 'never expire'. My fresh install shows that the value of mysql.user.password_lifetime is set to NULL which means use the server default value. The lifetime is measured in days and stored in the password_last_changed column of the nysql.users table. If the password is expired, you are put into sandbox mode where the only command you can execute is to change the password. That works great for interactive users. But what about your application? It uses a username password pair to talk to the database but it is very unlikely that anyone planned on changing passwords upon expiration. I seriously doubt anyone has set up the exception routine to handle an expired password properly. And if so, how do you notify all involved about this new password --- securely. What to doThe best thing would be to set the default password lifetime for accounts used by applications to zero. It simply does not expire. QED & out.

But what if your company wants ALL password changed on a regular basis? And they do mean ALL. Earlier there was a listing of the defaults. The test system are set to a password length of eight characters minimum, requires mixed case, requires at least one upper case letter, one special (nonalphanumeric) character, and is of MEDIUM complexity.

MEDIUM complexity means that passwords need one numeric, one lower case, one upper case, and one special character. LOW tests the password length only. And STRONG adds a condition that sub strings of the length of four characters or long do not match entries in a specified password file (use to make sure swear words, common names, etcetera are not part of a password).

Lets create a dummy account.

CREATE USER 'foobar'@'Localhost' IDENTIFIED BY 'Foo@Localhost1' PASSWORD EXPIRE;

Checking the entry in the user table, you will find that the account's password is expired. For extra credit notice what the authentication string is set to. We can't have just a password string as some authentication tokens or hashes are not really password.

So login as foobar and you will get a notice that the password must be reset before we can do anything else.

ALTER USER 'foobar'@'localhost' IDENTIFIED By '1NewP@assword';

Corporate StandardYour corporate rules may require you to rotate password every N days and set the corresponding complexity. With MySQL 5.7 you can follow what their model is. If you do not have a standard and want to create one, be sure to DOCUMENT well what your standard is and make sure that standard is well known.

There are ways to use packages like PAM or LDAP for authentication but that is for another day.
PlanetMySQL Voting: Vote UP / Vote DOWN

2016 MySQL User Group Leaders Summit

In this post, I’ll share my experience attending the annual MySQL User Group Leaders Summit in Bucharest, Romania.

The MySQL User Group Leaders Summit gathers together as many of the global MySQL user group leaders as possible. At the summit, we discuss further actions on how we can better act for their local communities. This year, it focused primarily on cloud technologies.

As the Azerbaijan MySQL User Group leader, I felt a keen responsibility to go. I wanted to represent our group and learn as much as possible to take back to with me. Mingling and having conversations with other group leaders helps give me more ideas about how to spread the MySQL word!

The Conference

I attended three MySQL presentations:

  • Guided tour on the MySQL source code. In this session, we reviewed the layout of the MySQL code base, roughly following the query execution path. We also covered how to extend MySQL with both built-in and pluggable add-ons.
  • How profiling SQL works in MySQL. This session gave an overview of the performance monitoring tools in MySQL: performance counters, performance schema and SYS schema. It also covered some of the details in analyzing MySQL performance with performance_schema.
  • What’s New in MySQL 5.7 Security. This session presented an overview of the new MySQL Server security-related features, as well as the MySQL 5.6 Enterprise edition tools. This session detailed the shifting big picture of secure deployments, along with all of the security-related MySQL changes.

I thought that the conference was very well organized, with uniformly great discussions. We also participated in some city activities and personal interactions. I even got to see Le Fred!

I learned a lot from the informative sessions I attended. The MySQL source code overview showed me the general paths of MySQL source code, including the most important directories, the most important functions and classes. The session about MySQL profiling instrumentation sessions informed us of the great MySQL profiling improvements. It reviewed some useful tools and metrics that you can use to get info from the server. The last session about MySQL security covered improved defaults, tablespace encryption and authentication plugins.

In conclusion, my time was well spent. Meeting and communicating with other MySQL user group leaders gives me insight into the MySQL community. Consequently, I highly recommend everyone gets involved in your local user groups and attend get-togethers like the MySQL User Group Leaders Summit when you can find the time.

Below you can see some of the pics from the trip. Enjoy!

 

 

 

 


PlanetMySQL Voting: Vote UP / Vote DOWN

Q: Does MySQL support ACID? A: Yes

I was recently asked this question by an experienced academic at the NY Oracle Users Group event I presented at.

Does MySQL support ACID? (ACID is a set of properties essential for a relational database to perform transactions, i.e. a discrete unit of work.)

Yes, MySQL fully supports ACID, that is Atomicity, Consistency, Isolation and Duration. (*)

This is contrary to the first Google response found searching this question which for reference states “The standard table handler for MySQL is not ACID compliant because it doesn’t support consistency, isolation, or durability”.

The question is however not a simple Yes/No because it depends on timing within the MySQL product’s lifecycle and the version/configuration used in deployment. What is also *painfully* necessary is to understand why this question would even be asked of the most popular open source relational database.

MySQL has a unique characteristic of supporting multiple storage engines. These engines enabling varying ways of storing and retrieving data via the SQL interface in MySQL and have varying features for supporting transactions, locking, index strategies, compression etc. The problem is that the default storage engine from version 3.23 (1999) to 5.1 (2010) was MyISAM, a non-transactional engine, and hence the first point of confusion.

The InnoDB storage engine has been included and supported from MySQL 3.23. This is a transactional engine supporting ACID properties. However, not all of the default settings in the various MySQL versions have fully meet all ACID needs, specifically the durability of data. This is the second point of confusion. Overtime other transactional storage engines in MySQL have come and gone. InnoDB has been there since the start so there is no excuse to not write applications to fully support transactions. The custodianship of Oracle Corporation starting in 2010 quickly corrected this *flaw* by ensuring the default storage engine in MySQL 5.5 is InnoDB. But the damage to the ecosystem that uses MySQL, that is many thousands of open source projects, and the resources that work with MySQL has been done. Recently working on a MySQL 5.5 production system in 2016, the default engine was specifically defined in the configuration defined as MyISAM, and some (but not all tables) were defined using MyISAM. This is a further conversation as to why, is this a upgrade problem? Are there legacy dependencies with applications? Are the decision makers and developers simply not aware of the configuration? Or, are developers simply not comfortable with transactions?

Like other anti-reasonable MySQL defaults the unaware administrator or developer could consider MySQL as supporting ACID properties, however until detailed testing with concurrency and error conditions not realize the impact of poor configuration settings.

The damage of having a non-transactional storage engine as the default for over a decade has created a generation of professionals and applications that abuses one of the primary usages of a relational database, that is a transaction, i.e. to product a unit for work that is all or nothing. Popular open source projects such as WordPress, Drupal and hundreds more have for a long time not supported transactions or used InnoDB. Mediawiki was at least one popular open source project that was proactive towards InnoDB and transaction usage. The millions of plugins, products and startups that build on these technologies have the same flaws.

Further confusion arises when an application uses InnoDB tables but does not use transactions, or the application abuses transactions, for example 3 different transactions that should really be 1.

While newer versions of MySQL 5.6 and 5.7 improve default configurations, until these versions a more commonly implemented non-transactional use in a relational database will remain. A recent Effective MySQL NYC Meetup survey showed that installations of version 5.0 still exist, and that few have a policy for a regular upgrade cadence.


PlanetMySQL Voting: Vote UP / Vote DOWN

Lessons from database failures presentation

At our June meetup, our guest speaker and Chief Evangelist from MariaDb Colin Charles gave a presentation on “Lessons from database failures”. Some of the details of his presentation included:


Download PDF Presentation
  • Notable failures causing companies to go out of business
  • varying backup commands and options
  • Understanding a/semi/synchronous replication
  • Replication topology management tools
  • Proxy and sharding tools
  • Security, SQL injections and encryption

Download Introduction Slides

Thanks to our sponsors for June. Grovo, Webair, MariaDB


PlanetMySQL Voting: Vote UP / Vote DOWN

5 Database Insights Easy to See with VividCortex SaaS Monitoring

There are manifold ways to collect, visualize, and analyze data… but not all methods are equally useful. VividCortex, however, is singular as a database-centric SaaS monitoring platform, and it's designed to provide you with powerful insights into your system that are both inherently actionable and unique. Within minutes of first booting up VividCortex, users frequently discover new aspects of their system. They understand it in brand new ways, just by viewing our app’s basic dashboards and metrics.

But that's just the start. Even beyond those initial revelations, there are many more powerful insights that VividCortex can provide, if you know how and where to look. These views aren’t entirely automatic, but they’re simple to discover with a few tips. Here are 5 insights easy to see with VividCortex.

Find which queries affect the most rows

Understanding which queries are affecting the highest number of rows in your system is a useful way to understand the amount of change occurring in your dataset. By organizing this change as “affected rows,” you’re seeing these developments in terms of a powerful, raw metric. “Affected rows” refers to any row that was changed by an UPDATE, INSERT, or DELETE, based based on the OK Packet or Performance_schema data.

To view queries organized in terms of affected rows, head to the Profiler and then rank “Queries” by “Affected Rows.”

The Profiler will generate a view like this one, giving you immediate, legible insight into which queries are causing the widest range of change.



Find the largest group of similarly grouped queries

If you’re able to see the largest group of similar queries, it gives you a window into application behavior, which, in turn, can be used for sharding decisions and other growth strategies. No small thing. Alternatively, examining query verbs can very quickly show you the read to write ratio of a workload, which can be leveraged at further decision points.

To view queries this way, head back to the Profiler and rank them according to “Count.” You’ll then see the total number of queries, grouped similarity and organized by quantity. Alternatively, you can rank “Query Verbs” in the same way and retrieve the number according to command type. In both cases, you see which queries are executing the most frequently in your system.

Find memory allocation stalls

As explained by the kernel documentation, memory allocation stalls refer to times when a process stalls to run memory compaction so that a sizable page is free for use. With VividCortex, you’re able to see the number of times this happens in a given timeframe, allowing for further investigation. To do so, head to the Metrics dashboard and enter the metric text as “os.mem.compact_stalls”.



 

Find IO Wait

IO Wait — the time the CPU waits for IO to complete —  can cause stalls for page requests that memory buffers are unable to fulfill and during background page flushing. All of this can have widespread impacts on database performance and stability. Using the metrics dashboard in VividCortex, you’re able to see these stalls by duration over time and brokendown by host.


In the Metrics dashboard, use “os.cpu.io_wait_us” as the metric text.


Find long running transactions

Also in the metrics dashboard, you can see long running transactions by viewing the redo segment history length, which, in turn, represents transaction redo segment history length. This is essentially the overhead of yet-to-be-purged MVCC.

Naturally, seeing spikes of long running transactions and providing explanation for this overhead is a valuable ability — and easily accomplished with VividCortex. Just use the metric text “mysql.status.i_s_innodb_metrics.trx_rseg_history_len”.

Want to see more?

These tips and insights just scratch the surface. VividCortex has much more visibility available to anybody interested in seeing SaaS, database-centric monitoring in action. If you'd like to find further tips on how to get the most out of VividCortex — or would like to see how much value it can give you and your systems — don't hesitate to get in touch.  


PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL TCPCOPY

we use tcpcopy to make real traffic on our core systems. Many problems will be found in advance if we enlarge queries several times.

Read this PDF TCPCOPY


PlanetMySQL Voting: Vote UP / Vote DOWN

How to Install Nginx with PHP and MySQL (LEMP Stack) on CentOS 7.2

Nginx (pronounced "engine x") is a free, open-source, high-performance HTTP server. Nginx is known for its stability, rich feature set, simple configuration, and low resource consumption. This tutorial shows how you can install Nginx on a CentOS 7.2 server with PHP support (through PHP-FPM) and MySQL (Mariadb) support.
PlanetMySQL Voting: Vote UP / Vote DOWN

Press Release: Severalnines kicks off online European football streaming

Award-winning database management platform scores deal with continent’s largest online video solutions provider

Stockholm, Sweden and anywhere else in the world - 28/06/2016 - Severalnines, Europe’s leading database performance management provider, today announced its latest customer, StreamAMG (Advanced Media Group), a UK-based pioneer in the field of bespoke online video streaming and content management. StreamAMG is Europe’s largest player in online video solutions, helping football teams such as Liverpool FC, Aston Villa, Sunderland AFC and the BBC keep fans watching from across the world.

Long hailed as the future of online content, analysts predict that 90% of all consumer internet traffic will be video by 2019. This poses a challenge to streaming providers, both in terms of the amount of online video data to handle and the variety of ways the content is consumed. Customers expect a seamless viewing experience across any device on any operating system. Downtime, lag or disturbances to streaming can have serious repercussions for customer loyalty. Streaming providers should provide a secure and reliable media platform to maintain the interest of fans and attract new viewers, casting database performance in a starring role.

Founded in 2001, StreamAMG builds bespoke solutions for its customers to host and manage online video content. Its software delivers the high-availability needed for on-demand streaming or live broadcasting on any device. Loss of customer trust and damage to brand reputation are likely consequences of database failures, especially for those companies which operate in the online sports, betting and gaming industries.

Growing at 30% year on year required StreamAMG to have a scalable IT system to meet new customer demands and to maintain its leadership position in the market. StreamAMG reviewed its database performance as part of its IT infrastructure renewal project for to encompass new online channels, such as social media, and embedding marketing analytics to help its customers better understand and react to customer behaviour. It needed a solution to monitor and optimise its database management system and the detailed metrics to predict database failures.

After reviewing options provided by Oracle and AWS, amongst others, StreamAMG chose Severalnines to help future-proof its databases. The previous environment, based on a master-slave replication topology, was replaced with a multi-master Galera Cluster; and Severalnines’ ClusterControl platform was applied to automate operational tasks and provide visibility of uptime and performance through monitoring capabilities.

Thom Holliday, Marketing Manager StreamAMG, said: “With ClusterControl in place, StreamAMG’s flagship product is now backed with a fully automated database infrastructure which allows us to ensure excellent uptime. Severalnines increased our streaming speed by 76% and this has greatly improved the delivery of content to our customers. The implementation took only two months to complete and saved us 12% in costs. Expanding the current use of ClusterControl is definitely in the pipeline and we would love to work with Severalnines to develop new features.”

Vinay Joosery, Severalnines Founder and CEO, said: “Online video streaming is growing exponentially, and audiences expect quality, relevant content and viewing experiences tailor-made for each digital platform. I’m a big football fan myself and like to stay up to date with games whenever I can. Right now I’m following the European Championships and online streaming is key so I can watch the matches wherever I am. New types of viewerships place certain requirements on modern streaming platforms to create experiences that align with consumer expectations. StreamAMG is leading the way there, and helps its customers monetise online channels through a solidly architected video platform. We’re happy to be part of this.“

About Severalnines

Severalnines provides automation and management software for database clusters. We help companies deploy their databases in any environment, and manage all operational aspects to achieve high-scale availability.

Severalnines' products are used by developers and administrators of all skills levels to provide the full 'deploy, manage, monitor, scale' database cycle, thus freeing them from the complexity and learning curves that are typically associated with highly available database clusters. The company has enabled over 8,000 deployments to date via its popular online database configurator. Currently counting BT, Orange, Cisco, CNRS, Technicolour, AVG, Ping Identity and Paytrail as customers. Severalnines is a private company headquartered in Stockholm, Sweden with offices in Singapore and Tokyo, Japan. To see who is using Severalnines today visit: http://www.severalnines.com/customers

About StreamAMG

StreamAMG helps businesses manage their online video solutions, such as Hosting video, integrating platforms, monetizing content and delivering live events. Since 2001, it has enabled clients across Europe to communicate through webcasting by building online video solutions to meet their goals.

For more information visit: https://www.streamamg.com

Media Contact

Positive Marketing
Steven de Waal / Camilla Nilsson
severalnines@positivemarketing.com
0203 637 0647/0645

Tags:
PlanetMySQL Voting: Vote UP / Vote DOWN

On Using HP Vertica. Interview with Eva Donaldson.

“After you have built out your data lake, use it. Ask it questions. You will begin to see patterns where you want to dig deeper. The Hadoop ecosystem doesn’t allow for that digging and not at a speed that is customer facing. For that, you need some sort of analytical database.”– Eva Donaldson.

I have interviewed Eva Donaldson, software engineer and data architect at iContact. Main topic of the interview is her experience in using HP Vertica.

RVZ

Q1. What is the business of iContact?

Eva Donaldson: iContact is a provider of cloud based email marketing, marketing automation and social media marketing products. We offer expert advice, design services, and an award-winning Salesforce email integration and Google Analytics tracking features specializing in small and medium sized businesses and nonprofits in U.S. and internationally.

Q2. What kind of information are your customers asking for?

Eva Donaldson: Marketing analytics including but not limited to how customers reached them, interaction with individual messages, targeting of marketing based on customer identifiers.

Q3. What are the main technical challenges you typically face when performing email marketing for small and medium businesses?

Eva Donaldson: Largely our technical challenges are based on sheer size and scope of data processing. We need to process multiple data points on each customer interaction, on each customer individually and on landing page interaction.

Q4. You attempted to build a product on Infobright. Why did you choose Infobright? What was your experience?

Eva Donaldson: We started with Infobright because we were using it for log processing and review. It worked okay for that since all the logs are always referenced by date which would come in order. For anything but the most basic querying by date Infobright failed. Tables could not be joined. Selection by any column not in order was impossible in the size data we were processing. For really large datasets some rows would just not be inserted without warning or explanation.

Q5. After that, you deployed a solution using HPE Vertica. Why did you choose HPE Vertica? Why didn`t you instead consider another open source solution?

Eva Donaldson: Once we determined that Infobright was not the correct solution, we knew already that we needed an analytical style database. I asked anyone and everyone who was working with true analytics at scale what database backend they were using and if they were happy. Three products come to the forefront: Vertica, Teradata and Oracle. The people using Oracle who were happy were complete Oracle shops. Since we do not use Oracle for anything this was not the solution for us. We decided to review Vertica, Teradata and Netezza. Of the three Vertica for our needs came out the clear winner.

Vertica installs on commodity hardware which meant we could deploy it immediately on servers we had on hand already. Scaling out is horizontal since Vertica clusters natively which meant it fit exactly in with the way we already handled our scaling practices.

After the POC with Vertica’s free version and seeing the speed and accuracy of queries, there was no doubt we had picked the right one for our needs. Continued use and expansion of the cluster has continued to prove that Vertica stands up to everything we throw at it. We have been able to easily put in a new node, migrate nodes to beefy boxes when we needed to. Performance on queries has been unequaled. We are able to return complex analytical queries in milliseconds.

As to other open source tools, we did consider them. I looked at Greenplum and I don’t remember what all other columnar data stores. There are loads of them out there. But they are all limited in one way or another and most of them are very similar in ability to Infobright. They just don’t scale to what we needed.

The other place people always think is Hadoop. Hadoop and all the related ecosystem is a great place to put stuff while you are wondering what questions you can ask. It is nice to have Hadoop (Hive, Hbase, etc.) to have a place to stick EVERYTHING without question. Then from there you can begin to do some very broad analysis to see what you have. But nothing coming out of a basic file system is going to get you the nitty-gritty analysis to answer the real questions in a timely manner. After you have built out your data lake, use it. Ask it questions. You will begin to see patterns where you want to dig deeper. The Hadoop ecosystem doesn’t allow for that digging and not at a speed that is customer facing. For that, you need some sort of analytical database.

Q6. Can you give us some technical details on how you use HPE Vertica? What are the specific features of HPE Vertica you use and for what?

Eva Donaldson: We have Vertica installed on Ubuntu 12.04 in a three node cluster. We load data via the bulk upload methods available from the JDBC driver. Querying includes many of the advanced analytical functions available in the language as well as standard SQL statements.

We use the Management Console to get insight into query performance, system health, etc. Management Console also provides a tool to suggest and build projections based on queries that have been run in the past. We run the database designer on a fairly regular basis to keep things tuned to how it is actively being used.

We do most of our loading via Pentaho DI and quite a lot of querying from that as well. We also have connectors from Pentaho reports. We have some PHP applications that reach that data as well.

Q7. To query the database, did you have as requirement to use a standard SQL interface? Or it does not really matter which query language you use?

Eva Donaldson: Yes, we required a standard SQL interface and availability of a JDBC driver to integrate the database with our other tools and applications.

Q8. Did you perform any benchmark to measure the query performance you obtain with HPE Vertica? If yes, can you tell us how did you perform such benchmark (e.g. what workloads did you use, what kind of queries did you consider, etc,)

Eva Donaldson: To perform benchmarks we loaded our biggest fact table and its related dimensions. We took our most expensive queries and a handful of “like to have” queries that did not work at all in Infobright and pushed them through Vertica. I no longer have the results of those tests but obviously we were pleased as we chose the product.

Q9. What about updates? Do you have any measures for updates as well?

Eva Donaldson: We do updates regularly with both UPDATE and MERGE statements. MERGE is a very powerful utility. I do not have specific times but again Vertica performs splendidly. Updates on millions of rows performs accurately and within seconds.

Q10. What is your experience of using various Business Intelligence, Visualization and ETL tools in their environment with HPE Vertica?

Eva Donaldson: The only BI tools we use are all part of the Pentaho suite. We use Report Designer, Analyzer and Data Integration. Since Pentaho comes with Vertica connectors it was very easy to begin working with it as the backend of our jobs and reports.

Qx Anything else you wish to add?

Eva Donaldson: If you are looking for an easy to build and maintain, performant analytical database nothing beats Vertica, hands down. If you are working with enough data that you are wondering how to process it all having an analytical database to be able to actually process the data, aggregate it, ask complicated questions from is priceless. We have gained enormous insight into our information because we can ask it questions in so many different ways and because we can get the data back in a performant manner.

———————

Eva Donaldson is a software engineer and data architect with 15+ years of experience building robust applications to both gather data and return it to assist in solving business challenges in marketing and medical environments. Her experience includes both OLAP and OLTP style databases using SQL Server, Oracle, MySQL, Infobright and HP Vertica. In addition, she has architected and developed the data consumption middle and front end tiers in PHP, C#, VB.Net and Java.

Resources

– What’s in store for Big Data analytics in 2016, Steve Sarsfield, Hewlett Packard Enterprise. ODBMS.org, 3 FEB, 2016.

– Column store database formats like ORC and Parquet reach new levels of performance. Steve Sarsfield, HP Vertica. ODBMS.org, JUNE 15, 2016

Taking on some of big data’s biggest challenges.  Steve Sarsfield , HP Vertica. ODBMS.org, June 2016 – What’s New in Vertica 7.2?: Apache Kafka Integration!, HPE, February 2, 2016.

Related Posts

– On the Internet of Things. Interview with Colin Mahony. ODBMS Industry Watch, March 14, 2016.

– On Big Data Analytics. Interview with Shilpa Lawande, ODBMS Industry Watch, December 10, 2015.

Follow us on Twitter: @odbmsorg

##


PlanetMySQL Voting: Vote UP / Vote DOWN

Webinar Wednesday June 29: Percona XtraDB Cluster Reference Architecture

Please join Jay Janssen for the webinar Percona XtraDB Cluster Reference Architecture Wednesday, June 29 at 10:00 AM PDT (UTC- 7).

A reference architecture shows a typical, common, best-practice deployment of a system with all the surrounding infrastructure. In the case of database clusters, this can include the hosting platform, load balancing, monitoring, backups, etc.

Percona published a commonly referred to Percona XtraDB Cluster reference architecture on the Percona blog in 2012 (which is included in the current manual). However, this architecture is out of date.

This talk will present a revised and updated Percona XtraDB Cluster reference architecture for 2016, including:

  • Load balancing
  • Read/Write splitting
  • Monitoring
  • Backups

This will include some variants, such as:

  • AWS hosting
  • WAN deployments
  • Async slaves

Register now.

Jay Janssen, Managing Principal Architect

Jay came to Percona in 2011 after working seven years for Yahoo! Jay worked in a variety of fields including High Availability architectures, MySQL training, tool building, global server load balancing, multi-datacenter environments, operationalization, and monitoring. Jay holds a B.S. in Computer Science from Rochester Institute of Technology. He and his wife live with their four children in upstate New York.


PlanetMySQL Voting: Vote UP / Vote DOWN

The need for parallel crash recovery in MySQL

In this blog, I will discuss how parallel crash recovery in MySQL benefits several processes.

I recently filed an Oracle feature request to make crash recovery faster by running in multiple threads.

This might not seem very important, because MySQL does not crash that often. When it does crash, however, crash recovery can take 45 mins – as I showed in this post:

What is a big innodb_log_file_size?

Even in that case, it still might not be a big issue as you often failover to a slave.

However, crash recovery plays important part in the following processes:

  • Backups with Percona XtraBackup (and MySQL Enterprise Backups) and backups with filesystem snapshots.
    • Crash recovery is part of the backup process, and it is important to make the backup task faster.
  • State Snapshot Transfer in Percona XtraDB Cluster.
    • SST, either XtraBackup or rsync bases, also relies on the crash recovery process – so the faster it is done, the faster a new node joins the cluster.
    • It might seem that Oracle shouldn’t care about Percona XtraDB Cluster. But they are working on MySQL Group Replication. I suspect that when Group Replication copies data to the new node, it will also rely on some kind of snapshot technique. Unless they aren’t serious about this feature and will recommend mysqldump/mysqlpump for data copying).
  • My recent proof of concept for Automatic Slave propagation in Docker environment also uses Percona XtraBackup, and therefore crash recovery for new slaves.

In general, any process that involves MySQL/InnoDB data transfer will benefit from a faster crash recovery. In its current state uses just one thread to read and process data. This limits performance on modern hardware, which uses multiple CPU cores and fast SSD drives.

It is also important to consider that the crash recovery time affects how big log files can be. If we improve the crash recovery time, we can store very big InnoDB log files (which positively affects performance in general).

Percona is working on ways to make it faster. However, if faster recovery times are important to you environment, I encourage you to let Oracle know that you want to see parallel crash recovery in MySQL.


PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL on Docker: Building the Container Image

Building a docker image for MySQL is essential if you’d like to customize MySQL to suit your needs. The image can then be used to quickly start running MySQL containers, which can be operated individually. In our previous post, we covered the basics of running MySQL as container. For that purpose, we used the MySQL image from the official Docker repository. In this blog post, we’ll show you how to build your own MySQL image for Docker.

What is a container image?

A container requires an image to run. A container image is like a virtual machine template. It has all the required stuff to run the container. That includes operating system, software packages, drivers, configuration files and helper scripts packed in one bundle.

When running MySQL on a physical host, here is what you would normally do:

  1. Prepare a host with proper networking
  2. Install operating system of your choice
  3. Install MySQL packages via package repository
  4. Modify the MySQL configuration to suit your needs
  5. Start the MySQL service

Running a MySQL Docker image would look like this:

  1. Install Docker engine on the physical host
  2. Download a MySQL image from public (Docker Hub) or private repository, or build your own MySQL image
  3. Run the MySQL container based on the image, which is similar to starting the MySQL service

As you can see, the Docker approach contains less deployment steps to get MySQL up and running. 99% of the time, the MySQL service running in container will usually work in any kind of environment as long as you have the Docker engine running. Building a MySQL container image requires process flow, since Docker expects only one process per container.

Consider the following:

The above illustrates the following actions:

  1. The image is pulled from Docker Hub on the machine host by using:

    $ docker pull mysql
  2. Spin up two MySQL containers and map them with their respective volume:

    $ docker run -d --name=mysql1 -e MYSQL_ROOT_PASSWORD=’mypassword’ -v /storage/mysql1/mysql-datadir:/var/lib/mysql mysql $ docker run -d --name=mysql2 -e MYSQL_ROOT_PASSWORD=’mypassword’ -v /storage/mysql2/mysql-datadir:/var/lib/mysql mysql
How to build a MySQL image?

Take note that in this exercise, we are going to extend an existing MySQL image by adding Percona Xtrabackup onto it. We will then publish our image to Docker Hub and setup an automated build.

Base Image

To build a MySQL container image, we’ll start by pulling a base image. You can pull an image which contains a vanilla operating system of your choice, and start building the MySQL image from scratch:

$ docker pull debian Using default tag: latest Trying to pull repository docker.io/library/debian ... latest: Pulling from library/debian 17bd2058e0c6: Pull complete f854eed3f31f: Pull complete Digest: sha256:ff779f80153d8220904ec3ec6016ac6dd51bcc77e217587689feffcd7acf96a0 Status: Downloaded newer image for docker.io/debian:latest

However, this is perhaps not best practice. There are tons of MySQL container images available on Docker Hub that we can re-use and enhance with more functionality. For example, the MySQL image created by the Docker team may not contain things that we need, e.g., Percona Xtrabackup (PXB). PXB needs to have access to the local file system in order to perform hot backups. Thus, we have to install PXB on top of the MySQL container images created by Docker. Inheriting this MySQL image allows us to leverage the work done by the Docker team. We do not need to maintain the MySQL server parts, especially when a new version is released.

For now, let’s pull the MySQL image of our choice to the machine host. We are going to use MySQL 5.6 as the base image:

$ docker pull mysql:5.6 Trying to pull repository docker.io/library/mysql ... 5.6: Pulling from library/mysql 3153a44fc5c3: Pull complete ac82a224055d: Pull complete e3ce3c9ce67d: Pull complete 57c790472a9d: Pull complete 49772bf40877: Pull complete 73f07a1d187e: Pull complete 3446fa8ab4bb: Pull complete 70c40ffe6275: Pull complete 54672d2ddb6f: Pull complete Digest: sha256:03646869dfecf96f443640f8b9040fbde78a96269aaf47bbfbb505a4c1adcad9 Status: Downloaded newer image for docker.io/mysql:5.6

Let’s verify what images we do have now:

$ docker images REPOSITORY TAG IMAGE ID CREATED VIRTUAL SIZE docker.io/debian latest f854eed3f31f 5 days ago 125.1 MB docker.io/mysql latest b0e2c14c7e92 3 weeks ago 378.4 MB docker.io/mysql 5.6 54672d2ddb6f 3 weeks ago 329 MB

There are three Docker images available in the host, debian:latest, mysql:latest (MySQL 5.7) and mysql:5.6 (MySQL 5.6).

There are two different ways to build a new image, we’ll cover this in the next section.

Building the image

Essentially, there are two ways to build the image:

  1. Make changes to the base image and commit
  2. Use Dockerfile - A text file that contains all the commands to build an image
1) Change & Commit

By using this approach, you make the changes directly into the container image and commit. The commit operation will not include any data contained in volumes mounted inside the container. By default, the container being committed and its processes will be paused while the image is committed. This reduces the likelihood of encountering data corruption during the process of creating the commit.

Let’s run a MySQL container with a volume. Create a volume directory on the machine host and spin a MySQL instance based on the MySQL 5.6 image we have downloaded:

$ mkdir -p /storage/test-mysql/datadir $ docker run -d --name=test-mysql -e MYSQL_ROOT_PASSWORD=’mypassword’ -v /storage/test-mysql/datadir:/var/lib/mysql mysql:5.6

Verify if the container is running:

$ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 4e75117289ea mysql:5.6 "docker-entrypoint.sh" 5 seconds ago Up 5 seconds 3306/tcp test-mysql

Enter the container’s interactive shell:

$ docker exec -it test-mysql /bin/bash root@4e75117289ea:/#

This is a Debian 8 (Jessie) image with minimal installation. Some common commands like wget are not available and we need to install these beforehand:

$ apt-get update && apt-get install wget

Install the appropriate Percona apt repository and install the latest PXB 2.3:

$ wget https://repo.percona.com/apt/percona-release_0.1-3.jessie_all.deb $ dpkg -i percona-release_0.1-3.jessie_all.deb $ apt-get update $ apt-get install percona-xtrabackup-23 $ mkdir -p /backup/xtrabackups $ exit

That’s it. Those were changes we’ve made to the container. Let’s commit this container as another image so we can use it later. First, retrieve the container ID:

$ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 4e75117289ea mysql:5.6 "docker-entrypoint.sh" 12 minutes ago Up 12 minutes 3306/tcp test-mysql

Then, commit and push the changes to another image called “local/mysql-pxb:5.6”:

$ docker commit 4e75117289ea mysql-pxb:5.6 830fea426cfb27d4a520c25f90de60517b711c607dda576fca93ff3a5b03c48f

We can now see that our new image is ready:

$ docker images REPOSITORY TAG IMAGE ID CREATED VIRTUAL SIZE local/mysql-pxb 5.6 830fea426cfb 9 seconds ago 589.7 MB docker.io/debian latest f854eed3f31f 5 days ago 125.1 MB docker.io/mysql latest b0e2c14c7e92 3 weeks ago 378.4 MB docker.io/mysql 5.6 54672d2ddb6f 3 weeks ago 329 MB

This is just an example to show you how to create an image. We are not going to test this image further, nor upload it to the Docker Hub. Instead, we’ll use Dockerfile as described in the next section.

2) Dockerfile

Contrary to the change and commit approach, another way is to compile all the necessary steps that we used above in a text file called Dockerfile. Generally, it is better to use Dockerfiles to manage your images in a documented and maintainable way. We are not going to cover each of the syntax used in Dockerfile, you can refer to the Docker documentation for details.

Let’s start by creating a directory to place the Dockerfile:

$ mkdir -p ~/docker/severalnines/mysql-pxb $ cd ~/docker/severalnines/mysql-pxb

Create a new file called Dockerfile:

$ vim Dockerfile

And add the following lines:

## MySQL 5.6 with Percona Xtrabackup ## Pull the mysql:5.6 image FROM mysql:5.6 ## The maintainer name and email MAINTAINER Ashraf Sharif <ashraf@s9s.com> ## List all packages that we want to install ENV PACKAGE percona-xtrabackup-23 # Install requirement (wget) RUN apt-get update && apt-get install -y wget # Install Percona apt repository and Percona Xtrabackup RUN wget https://repo.percona.com/apt/percona-release_0.1-3.jessie_all.deb && \ dpkg -i percona-release_0.1-3.jessie_all.deb && \ apt-get update && \ apt-get install -y $PACKAGE # Create the backup destination RUN mkdir -p /backup/xtrabackups # Allow mountable backup path VOLUME ["/backup/xtrabackup"]

Save the file. What we are doing here is exactly the same with “change and commit” approach, where we extend the functionality of the existing MySQL image downloaded from Docker Hub by installing Percona apt repository and Percona Xtrabackup into it.

We can now build a new image from this Dockerfile:

$ docker build --rm=true -t severalnines/mysql-pxb:5.6 .

We saved the image with a proper naming format “username/image_name:tag”, which is required by Docker Hub if you would like to push and store the image there.

Let’s take a look on what we have now:

$ docker images REPOSITORY TAG IMAGE ID CREATED VIRTUAL SIZE severalnines/mysql-pxb 5.6 c619042c5b91 18 minutes ago 591.4 MB local/mysql-pxb 5.6 830fea426cfb 15 hours ago 589.7 MB docker.io/debian latest f854eed3f31f 6 days ago 125.1 MB docker.io/mysql latest b0e2c14c7e92 3 weeks ago 378.4 MB docker.io/mysql 5.6 54672d2ddb6f 3 weeks ago 329 MB

There are two same images, “local/mysql-pxb” and “severalnines/mysql-pxb”, where the latter was built by using Dockerfile. e are going to push it into Docker Hub.

This is what we have now in our machine host:

Next, we are going to test the image and make further modifications if necessary.

Testing

Let’s run a MySQL container and perform a backup using innobackupex. Create volume path directories on the machine host beforehand:

$ mkdir -p /storage/mysql-pxb/datadir $ mkdir -p /storage/backups $ docker run -d \ --name mysql-server \ -v /storage/mysql-server/datadir:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=mypassword \ severalnines/mysql-pxb:5.6

The above command runs a MySQL container called “mysql-server” from the newly built image, severalnines/mysql-pxb:5.6, which can be illustrated as below:

From Percona Xtrabackup documentation, the simplest innobackupex command is:

$ innobackupex --user=”[user]” --password=”[password]” [backup path]

Based on the above, we can execute the backup command in another container (mysql-run-backup), link it to the running MySQL container (mysql-server) and take advantage of the environment variables available for linked containers. By doing this, we don’t have to specify the credentials like host, port, username and password when running the innobackupex command.

Let’s run a linked container and perform the backup by using Docker’s environment variable:

$ docker run -it \ --link mysql-server:mysql \ --name=mysql-run-backup \ -v /storage/mysql-server/datadir:/var/lib/mysql \ -v /storage/backups:/backups \ --rm=true \ severalnines/mysql-pxb:5.6 \ sh -c 'exec innobackupex --host="$MYSQL_PORT_3306_TCP_ADDR" --port="$MYSQL_PORT_3306_TCP_PORT" --user=root --password="$MYSQL_ENV_MYSQL_ROOT_PASSWORD" /backups'

Our both containers are now working together like this:

However, the “run” command was pretty long and not very user friendly. We can simplify this by using a bash script. Create a bash script under the same path as the Dockerfile:

$ cd ~/docker/severalnines/mysql-pxb $ vim run_backup.sh

And add the following lines:

# Run innobackupex BACKUP_PATH=/backups innobackupex --host="$MYSQL_PORT_3306_TCP_ADDR" \ --port="$MYSQL_PORT_3306_TCP_PORT" \ --user=root \ --password="$MYSQL_ENV_MYSQL_ROOT_PASSWORD" \ $BACKUP_PATH

Give the script an executable permission:

$ chmod 755 run_backup.sh

Then, use the ADD command to copy the bash script into the image when building it. Our final version of Dockerfile is now:

## MySQL 5.6 with Percona Xtrabackup ## Pull the mysql:5.6 image FROM mysql:5.6 ## The maintainer name and email MAINTAINER Your Name <email@domain.xyz> ## List all packages that we want to install ENV PACKAGE percona-xtrabackup-22 # Install requirement (wget) RUN apt-get update && apt-get install -y wget # Install Percona apt repository and Percona Xtrabackup RUN wget https://repo.percona.com/apt/percona-release_0.1-3.jessie_all.deb && \ dpkg -i percona-release_0.1-3.jessie_all.deb && \ apt-get update && \ apt-get install -y $PACKAGE # Create backup directory RUN mkdir -p /backups # Copy the script to simplify backup command ADD run_backup.sh /run_backup.sh # Mountable backup path VOLUME ["/backups"]

Rebuild the image:

$ docker build --rm=true -t severalnines/mysql-pxb:5.6 .

Run a new container with the new simplified command “/run_backup.sh”:

$ docker run -it \ --link mysql-server:mysql \ --name=mysql-run-backup \ -v /storage/mysql-server/datadir:/var/lib/mysql \ -v /storage/backups:/backups \ --rm=true \ severalnines/mysql-pxb:5.6 \ sh -c 'exec /run_backup.sh'

You should see Xtrabackup output on the screen. Ensure you get the “completed OK” line indicating the backup is successfully created:

... innobackupex: Backup created in directory '/backups/2016-06-17_17-07-54' 160617 17:07:57 innobackupex: Connection to database server closed 160617 17:07:57 innobackupex: completed OK!

The container will then exit (we ran the image in foreground without --detach parameter) and automatically removed by Docker since we specified “--rm=true” in the “run” command line. On the machine host, we can see the backups are there:

$ ls -1 /storage/backups/ 2016-06-17_13-01-58 2016-06-17_13-07-28 2016-06-17_14-02-50

Now our Dockerfile and the image is ready to be published.

Upload to Docker Hub

Docker Hub greatly facilitates Docker image distribution and collaboration. It comes with a bunch of great features like image storage, automatic build and test, link to code repositories and integration with Docker Cloud. It also supports private image repository for those who don’t want to publish the images to the public.

In order to share the images onto Docker Hub, you must first register. After an account is created, run the following command on the machine host to authenticate to the site:

$ docker login Username: severalnines Password: Email: ashraf@domain.com WARNING: login credentials saved in /root/.docker/config.json Login Succeeded

Now you can push this repository to the registry designated by its name or tag:

$ docker push severalnines/mysql-pxb:5.6 Do you really want to push to public registry? [y/n]: y The push refers to a repository [docker.io/severalnines/mysql-pxb] (len: 1) c619042c5b91: Pushed cb679f373840: Pushed 29db1ab3b7c2: Pushed eadfe1149bcf: Pushed 73f07a1d187e: Pushed d574478a62d8: Pushed 9acd57cae939: Pushed b14dd0099b51: Pushed c03269bf1687: Pushed f50b60617e9c: Pushed ed751d9dbe3b: Pushed 23286f48d129: Pushed 5.6: digest: sha256:5cf2d7c339a9902ac3059b9ddac11beb4abac63e50fc24e6dfe0f6687cdfa712 size: 20156

Once completed, the image will be available publicly in Docker Hub for user “severalnines”:

Great! Our image is now live and hosted on the Docker Hub where the community can start pulling it.

Automated Build

It’s great to have Docker Hub store the image for you, for free. But, what if a newer version of Percona Xtrabackup is available and you want the image to have it? Or, what if we just want to make a slight modification to our Dockerfile? Should we repeat the building steps over and over again? The answer is no - if you make use of the automated build. Just tell Docker Hub where the code repository is (Github or BitBucket), it will keep an eye for any changes in the revision control of the repository and trigger the build process automatically

Automated builds have several advantages:

  • Images built in this way are built exactly as specified.
  • The Dockerfile is available to anyone with access to your Docker Hub repository.
  • Your repository is kept up-to-date with code changes automatically.

In this example, we use Github. Firstly, create a Github repository. Clone the Github repository into the machine host, and then push the Dockerfile and the run_backup.sh script into Github repository:

$ git clone severalnines/docker-mysql-pxb $ cd severalnines/docker-mysql-pxb $ git add * $ git commit -m ‘first release’ $ git push origin master

Next, link your Docker account with the Github repository. Refer to the instructions here. Once linked, go to Docker Hub > Create Automated Build > Create Auto-build Github > choose the repository and then specify the Docker repository name “mysql-pxb” and add a short description of the repository, similar to the following screenshot:

Then, configure the Build Settings according to the code branch, Dockerfile location and image tag:

In the above example, we also created a Dockerfile for MySQL 5.7 and set it as the default using the “latest” tag. Click on the “Trigger” button to immediately trigger a build job. Docker Hub will then put it in a queue and build the image accordingly.

Here is the final look of the Docker Hub page once you have linked the account and configured automated build:

That’s all. Now what you have to do is just make a change and commit to the Github repository, then Docker will re-build the image for you automatically. If you have a README.md file inside the Github repository, Docker Hub will also pull the content of it into the “Full Description” section of the Docker repository page. Here is what the build status should look like under “Build Details” tab:

We strongly suggest you to have a look at the Best Practice for Writing Dockerfiles. In fact, if you’re creating an official Image, you must stick to those practices.

That concludes this blog post. In the next post, we will look into Docker internetworking for MySQL containers.

Tags:
PlanetMySQL Voting: Vote UP / Vote DOWN

Pages