Planet MySQL

How-To: Guide to Database Migration from MS Access using MySQL Workbench

MySQL Workbench 6.2 introduces support for MS Access migration. This tutorial should help you get your Access tables, indexes, relationships and data in MySQL.

Preparation

Because MS Access ODBC drivers are only available for Windows, migrating from it is also only possible from Windows. As for the destination MySQL server, you can have it in the same local machine or elsewhere in your network.

MS Access stores relationship/foreign key information in an internal table called MSysRelationships. That table is protected against read access even to the Admin user, so if you try to migrate without opening up access to it, you will get an error like this:

[42000] [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'msysobjects'. (-1907) (SQLExecDirectW)

The steps to grant read access to Admin are explained below. Unfortunately, the Access UI for that seems to change every version, but we’ll cover at least version 2007.

Preparing a Database Under MS Access 2007
  1. Open the database in Access
  2. Under the “Database Tools”, click the “Macro -> Visual Basic” button to open the VB console
  3. To confirm that you’re logged in as “Admin”, type the “? CurrentUser” and press Enter, in the “Immediate” panel:

    ? CurrentUser
    Admin

  4. Type the following command to grant access:

    CurrentProject.Connection.Execute “GRANT SELECT ON MSysRelationships TO Admin”

  5. Quit
 Open MySQL Workbench and start the Migration Wizard

From the main MySQL Workbench screen you can start the Migration Wizard by clicking on the Database Migration launcher in the Workbench Central panel or through Database –> Migrate in the main menu.

A new tab showing the Overview page of the Migration Wizard should appear.

 

ODBC Drivers

To check if you have the ODBC driver installed, click “Open ODBC Administrator” to open the system ODBC tool and look at the Drivers tab.

Important: MySQL Workbench has 32bit and 64bit executables. The ODBC drivers you use must be of the same architecture as the Workbench binaries you are using. So if you’re using Workbench 32bits, you must have 32bit ODBC drivers. Same for 64bits. Because Office 2007 and older was 32bit only and even Office 2010 installs as 32bit by default, you may need to install Workbench 32bits to migrate from Access, even if you have a 64bit machine. If during migration you get an ODBC error about “architecture mismatch between the Driver and Application”, you installed the wrong Workbench.

 

In the User DSN tab, click on Add… to create a DSN for for your database file. For the example, we created one for the northwind sample database.

 

Set up the parameters for your source Access database

Click on the Start Migration button in the Overview page to advance to the Source Selection page. In this page you need to provide the information about the RDBMS you are migrating, the ODBC driver to use and the parameters for the connection.

If you open the Database System combo box you’ll find a list of the supported RDBMSes. Select Microsoft Access from the list. Just below it there’s another combo box named Stored Connection. It will list saved connection settings for that RDBMS. You can save connections by marking the checkbox at the bottom of the page and giving them a name of your preference.

The next combo box is for the selection of the Connection Method. This time we are going to select ODBC Data Source from the list. This allows you to select pre-existing DSNs that you have configured in your system.

The DSN dropdown will have all DSNs you have defined in your system. Pick the one you created for the DB being migrated from the list.

In the Default Character Set field you can select the character set of your database. If your Access version uses western/latin characters, you can leave the default cp1252. However if you use a localized version of Access, such as Japanese, you must enter the correct characterset used by your edition of Office, otherwise the data will be copied incorrectly.

Click on the Test Connection button to check whether an ODBC connection can be established. If you put the right parameters you should see a message reporting a successful connection attempt.

Set up the parameters to connect to your target MySQL database

Click on the Next button to move to the Target Selection page. Once there set the parameters to connect to your MySQL Server instance. When you are done click on the Test Connection button and verify that you can successfully connect to it.

Select the objects to migrate

Move to the next page using the Next button. You should see the reverse engineering of the selected database in progress. At this point the Migration Wizard is retrieving relevant information about the involved database objects (table names, table columns, primary and foreign keys, indices, triggers, views, etc.). You will be presented a page showing the progress as shown in the image below.

Wait for it to finish and verify that everything went well. Then move to the next page. In the Source Objects page you will have a list with the objects that were retrieved and are available for migration. It will look like this:

As you can see the Migration Wizard discovered table and view objects in our source database. Note that only the table objects are selected by default to be migrated. You can select the view objects too, but you would have to provide their corresponding MySQL equivalent code later (no automatic migration is available for them yet) so let’s leave them off for now. The same applies for stored procedures, functions and triggers.

If you click on the Show Selection button you will be given the oportunity to select exactly which of them you want to migrate as shown here:

The items in the list to the right are the ones to be migrated. Note how you can use the filter box to easily filter the list (wildcards are allowed as you can see in the image above). By using the arrow buttons you can filter out the objects that you don’t want to migrate. At the end, don’t forget to clear the filter text box to check the full list of the selected objects. We are going to migrate all of the table objects, so make sure that all of them are in the Objects to Migrate list and that the Migrate Table Objects checkbox is checked. Most of the time you’ll want to migrate all objects in the schema anyway, so you can just click Next.

Review the proposed migration

Move to the next page. You will see the progress of the migration there. At this point the Migration Wizard is converting the objects you selected into their equivalent objects in MySQL and creating the MySQL code needed to create them in the target server. Let it finish and move to the next page. You might have to wait a little bit before the Manual Editing page is ready but you’ll end up with something like this:

As you can see in the image above there is a combo box named View. By using it you can change the way the migrated database objects are shown. Also take a look at the Show Code and Messages button. If you click on it you can see (and edit!) the generated MySQL code that corresponds to the selected object. Furthermore, you can double click in a row in the object tree and edit the name of the target object. Suppose you want your resultant database to have another name. No problem: double click on the Northwind row and rename it.

An interesting option in the View combo box is the Column Mappings one. It will show you all of the table columns and will let you individually review and fix the mapping of column types, default values and other attributes.

Run the resulting MySQL code to create the database objects

Move to the Target Creation Options page. It will look like this:

As you can see there, you are given the options of running the generated code in the target RDBMS (your MySQL instance from the second step) or just dumping it into a SQL script file. Leave it as shown in the image and move to the next page. The migrated SQL code will be executed in the target MySQL server. You can view its progress in the Create Schemata page:

Once the creation of the schemata and their objects finishes you can move to the Create Target Results page. It will present you a list with the created objects and whether there were errors while creating them. Review it and make sure that everything went OK. It should look like this:

You can still edit the migration code using the code box to the right and save your changes by clicking on the Apply button. Keep in mind that you would still need to recreate the objects with the modified code in order to actually perform the changes. This is done by clicking on the Recreate Objects button. You may need to edit the generated code if its execution failed. You can then manually fix the SQL code and re-execute everything. In this tutorial we are not changing anything, so leave the code as it is and move to the Data Transfer Setup page by clicking on the Next button.

Transfer the data to the MySQL database

The next steps in the Migration Wizard are for the transference of data from the source Access database into your newly created MySQL database. The Data Transfer Setup page allows you to configure this process.

There are two sets of options here. The first one allows you to perform a live transference and/or to dump the data into a batch file that you can run later. The other set of options gives you a way to tune up this process.

Leave the default values for the options in this page as shown in the above image and move to the actual data transference by jumping to the next page. It will take a little while to copy the data. At this point the corresponding progress page will look familiar:

Once it finishes, move to the next page. You will be presented a report page summarizing the whole process:

And that should be it. Click on the Finish button to close the Migration Wizard.

 

 

 

 


PlanetMySQL Voting: Vote UP / Vote DOWN

Using resource monitoring to avoid user service overload

Wed, 2014-08-20 11:47anatoliydimitrov

With MariaDB, as with any service, you must monitor user resource usage to ensure optimal performance. MariaDB provides detailed statistics for resource usage on per-user basis that you can use for database service monitoring and optimization. User statistics are especially useful in shared environments to prevent a single gluttonous user from causing server-wide performance deterioration. If you detect abnormal use, you can apply fine-grained limits, as we'll see.

To enable user statistics in MariaDB, edit the server configuration file /etc/my.cnf.d/server.cnf. In the [mysqld] section, add userstat = 1, then restart the service.

Now MariaDB will gather and store usage statistics in the table USER_STATISTICS in the database information_schema. USER_STATISTICS uses the Memory engine and does not preserve information upon service restarts, so statistics are reset when you restart the MariaDB service. You can also reset statistics manually with the command FLUSH USER_STATISTICS;.

Retrieving user statistics

To see all the user statistics, use the command SHOW USER_STATISTICS. It returns all the information about all the users, and gives you an overall look at resource usage. The output can help you spot inappropriately high usage by one user compared to others.

You can get more summarized information by filtering your query and retrieving information directly from the database with a command such as select CPU_TIME from information_schema.USER_STATISTICS where USER='test1';. This command shows the cumulative CPU time in seconds spent on serving user test1's connections.

Understanding user statistics

An example output of all the user statistics for user test1 might look like this:

MariaDB [(none)]> select * from information_schema.USER_STATISTICS where USER='test1' \G *************************** 1. row *************************** USER: test1 TOTAL_CONNECTIONS: 105 CONCURRENT_CONNECTIONS: 0 CONNECTED_TIME: 0 BUSY_TIME: 0.10427200000000013 CPU_TIME: 0.028732600000000018 BYTES_RECEIVED: 8190 BYTES_SENT: 86520 BINLOG_BYTES_WRITTEN: 0 ROWS_READ: 630 ROWS_SENT: 735 ROWS_DELETED: 0 ROWS_INSERTED: 0 ROWS_UPDATED: 0 SELECT_COMMANDS: 210 UPDATE_COMMANDS: 0 OTHER_COMMANDS: 0 COMMIT_TRANSACTIONS: 105 ROLLBACK_TRANSACTIONS: 0 DENIED_CONNECTIONS: 15 LOST_CONNECTIONS: 0 ACCESS_DENIED: 15 EMPTY_QUERIES: 0 1 row in set (0.00 sec)

The names of the fields explain what information they hold. The most important ones are:

  • TOTAL_CONNECTIONS, CONCURRENT_CONNECTIONS, and CONNECTED_TIME – If any or all of these are high, you may see errors such as 'Too many connections.' By default, in MariaDB the maximum number of connections to the servers is just 151, which aggressive users can easily exhaust.

     

  • BUSY_TIME and CPU_TIME – BUSY_TIME indicates for how long there was activity on the user connections, while CPU_TIME indicates the CPU time spent on servicing the user connections. The latter is more important, as it shows the direct user impact on CPU utilization.

     

  • BYTES_RECEIVED and BYTES_SENT – These two indicators are useful for monitoring network traffic that originates with MariaDB users. Usually high traffic is not a problem with databases, but in times of service overload the traffic statistics could help spot the base problem faster.
  • BINLOG_BYTES_WRITTEN – This indicator may help spot abnormal activity in the binary logs, which are used for replication or backup purposes. If your binary log starts growing unexpectedly, check this indicator first.
  • ROWS_READ, ROWS_SENT, ROWS_DELETED, ROWS_INSERTED, SELECT_COMMANDS, UPDATE_COMMANDS, OTHER_COMMANDS, COMMIT_TRANSACTIONS – These indicators give detailed information about a user's SQL work. Along with BUSY_TIME and CPU_TIME, they can give a full picture of the user's impact on the system's load.
  • ROLLBACK_TRANSACTIONS – An unusually high number or peaks in this indicator may show problems in the front-end application. A high number of rollback transactions may cause overload, because the front-end application is usually supposed to try recreating the information or query, thus causing additional load for every rolled-back transaction.
  • DENIED_CONNECTIONS and ACCESS_DENIED – These two indicators are useful mostly for security purposes and for troubleshooting application problems with incorrect logins. When a user is denied a connection, the attempt goes to DENIED_CONNECTIONS. A denied connection usually indicates incorrect privileges to establish the connection in the first place. ACCESS_DENIED, on the other hand, usually appears when a user has already established a successful connection but has been denied access to certain resource (database or table).
Taking action

Once you detect abnormally high activity from a user, you can take action to limit the resources allocated to the user by using the account resource limits feature. For instance, for the test1 user, you could run the query update mysql.user set max_connections=10,max_updates=100,max_questions=1000 where user='test1';. To make this change take effect, also run the command flush privileges;. The specified user's resources will be limited, and your server's performance should return to normal.

As you can see, MariaDB resource statistics and limits are useful for maintaining optimal service performance.

Tags: DBAHowto About the Author Anatoliy Dimitrov

Anatoliy Dimitrov is an open source enthusiast with substantial professional experience in databases and web/middleware technologies. He is as interested in technical writing and documentation as in practical work on complex IT projects. His favourite databases are MariaDB (sometimes MySQL) and PostgreSQL. He is currently graduating his master's degree in IT and aims to a PhD in Bionformatics in his home town University of Sofia.


PlanetMySQL Voting: Vote UP / Vote DOWN

How to use MySQL Global Transaction IDs (GTIDs) in production

Reconfiguring replication has always been a challenge with MySQL. Each time the replication topology has to be changed, the process is tedious and error-prone because finding the correct binlog position is not straightforward at all. Global Transaction IDs (GTIDs) introduced in MySQL 5.6 aim at solving this annoying issue.

The idea is quite simple: each transaction is associated with a unique identifier shared by all servers in a given replication topology. Now reconfiguring replication is easy as the correct binlog position can be automatically calculated by the server.

Awesome? Yes it is! However GTIDs are also changing a lot of things in how we can perform operations on replication. For instance, skipping transactions is a bit more difficult. Or you can get bitten by errant transactions, a concept that did not exist before.

This is why I will be presenting a webinar on Aug. 27 at 10 a.m. PDT: Using MySQL Global Transaction IDs in Production.

You will learn what you need to operate a replication cluster using GTIDs: how to monitor replication status or to recover from replication errors, tools that can help you and tools that you should avoid and also the main issues that can occur with GTIDs.

This webinar is free but you can register today to reserve your seat. And a recording will be available afterwards. See you next week!

The post How to use MySQL Global Transaction IDs (GTIDs) in production appeared first on MySQL Performance Blog.


PlanetMySQL Voting: Vote UP / Vote DOWN

An open-source MySQL/MariaDB GUI client on Linux

We've written a GUI application. Its command-line options are like those in the mysql client. Its graphic features are an SQL-statement text editor and a scrollable SQL result set. It runs on Linux now and we believe it could be ported to other platforms.

Here are four screenshots.

The startup is as non-GUI as can be -- in fact it gets options from the command line, or from my.cnf, the same way that the mysql client does. Wherever it seemed reasonable, we asked: What would mysql do?

The statement (at the bottom of the screenshot) has the usual GUI features for editing, and has syntax highlighting -- comments are green, reserved words are magenta, and so on.

Here the result is appearing in a grid. The second column is long so it's on multiple lines with its own scroll bars.

Finally, here's the same result after fooling around with cosmetics -- the colours and fonts for each section of the screen are resettable via menu items, and the column widths can be changed by dragging.

The program is written in C++ and uses Qt. Qt is available on many operating systems including Mac and Windows but we only tried Linux. The license is GPL version 2. The status is alpha -- there are bugs. Source and executable files can be downloaded from https://github.com/ocelot-inc/ocelotgui.


PlanetMySQL Voting: Vote UP / Vote DOWN

Which tech do startups use most?

Leo Polovets of Susa Ventures publishes an excellent blog called Coding VC. There you can find some excellent posts, such as pitches by analogy, and an algorithm for seed round valuations and analyzing product hunt data. He recently wrote a blog post about a topic near and dear to my heart, Which Technologies do Startups […]
PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Workbench 6.2.1 BETA has been released


The MySQL Workbench team is announcing availability of the first beta release of its upcoming major product update, MySQL  Workbench 6.2.

MySQL Workbench 6.2 focuses on support for innovations released in MySQL 5.6 and MySQL 5.7 DMR (Development Release) as well as MySQL Fabric 1.5, with features such as:
  • A new spatial data viewer, allowing graphical views of result sets containing GEOMETRY data and taking advantage of the new GIS capabilities in MySQL 5.7.
  • Support for new MySQL 5.7.4 SQL syntax and configuration options.
  • Metadata Locks View shows the locks connections are blocked or waiting on.
  • MySQL Fabric cluster connectivity - Browsing, view status, and connect to any MySQL instance in a Fabric Cluster.
  • MS Access migration Wizard - easily move to MySQL Databases.

Other significant usability improvements were made, aiming to raise productivity for advanced and new users:
  • Direct shortcut buttons to commonly used features in the schema tree.
  • Improved results handling. Columns have better auto-sizing and their widths are saved. Fonts can also be customized. Results "pinned" to persist viewing data.
  • A convenient Run SQL Script command to directly execute SQL scripts, without loading them first.
  • Database Modeling has been updated to allow changes to the formatting of note objects and attached SQL scripts can now be included in forward engineering and synchronization scripts.
  • Integrated Visual Explain within the result set panel.
  • Visual Explain drill down for large to very large explain plans.
  • Shared SQL snippets in the SQL Editor, allowing multiple users to share SQL code by storing it within a MySQL instance.
  • And much more.

The list of provided binaries was updated and MySQL Workbench binaries now available for:
  • Windows 7 or newer
  • Mac OS X Lion or newer
  • Ubuntu 12.04 LTS and Ubuntu 14.04
  • Fedora 20
  • Oracle Linux 6.5
  • Oracle Linux 7
  • Sources for building in other Linux distributions

For the full list of changes in this revision, visit
http://dev.mysql.com/doc/relnotes/workbench/en/changes-6-2.html

For discussion, join the MySQL Workbench Forums:
http://forums.mysql.com/index.php?151

Download MySQL Workbench 6.2.1 now, for Windows, Mac OS X 10.7+, Oracle Linux 6 and 7, Fedora 20, Ubuntu 12.04 and Ubuntu 14.04 or sources, from:

http://dev.mysql.com/downloads/tools/workbench/

On behalf of the MySQL Workbench and the MySQL/ORACLE RE Team.

PlanetMySQL Voting: Vote UP / Vote DOWN

5 great new features from Percona Cloud Tools for MySQL

It’s been three months since we announced anything for Percona Cloud Tools, not because we’ve been idle but because we’ve been so busy the time flew by!  Here’s the TL;DR to pique your interest:

  • EXPLAIN queries in real-time through the web app
  • Query Analytics for Performance Schema
  • Dashboards: customizable, shared groups of charts
  • Install and upgrade the agent with 1 command line
  • Unified UI: same time range, same host wherever you go

Percona Cloud Tools for MySQL is a hosted service providing access to query performance insights for all MySQL uses. After a brief setup, unlock new information about your database and how to improve your applications. There’s a lot more, but let’s just look at these five new features…

 

EXPLAIN queries in real-time through the web app

Like many people, to get a query’s EXPLAIN plan you probably copy the query, ssh to the server, log in to MySQL, then paste the query after typing “EXPLAIN”.  With Percona Cloud Tools’ new real-time EXPLAIN feature you can simply click a button.  It’s a real timesaver.

The EXPLAIN plan is a vital part of diagnosing the query.  Now with Percona Cloud Tools you have a lot of powerful information in one place: the query, its metrics, its EXPLAIN plan, and more–and more to come, too!

 

Query Analytics for Performance Schema

The MySQL slow log is a wealth of indispensable data about queries that you cannot get anywhere else.  That’s why it’s the default for Percona Cloud Tools Query Analytics.  Like most things, however, it has tradeoffs: for one, it can be time-consuming to parse, especially on very busy servers.  Or, in the case of Amazon RDS, the slow log may simply not be available.  That’s ok now because with MySQL 5.6 or newer (including Percona Server 5.6 or newer) you can parse queries from the Performance Schema.  It’s not as data-rich as the slow log, but it has the basics and it’s a great alternative (and sometimes the only alternative) to the slow log.

 

Dashboards: customizable, shared groups of charts

Metrics Monitor has a default dashboard (a collection of charts) for MySQL.  The default dashboard is a great start because it’s created by us (Vadim, actually) so you know it’s relevant and meaningful for MySQL.  However, it presents only a fraction of the data that percona-agent collects, so we need more dashboards to organize and present other data.  Now you can create new dashboards which are accessible to everyone in the organization.  For example, Peter was recently benchmarking TokuDB, so he created a TokuDB-specific dashboard.

 

Install and upgrade the agent with 1 command line

As of percona-agent 1.0.6, you can install, upgrade, and uninstall the agent with a single command line, ran as root, like:

# curl -s https://cloud.percona.com/install | bash /dev/stdin -api-key <API KEY>

For many environments this is all you need for a first-time install of a new agent.  The install will auto-detect MySQL, configure, and run all services by default.  You can tweak things later in the web app.  This also means you can install percona-agent in an automated environment.

 

Unified UI: same time range, same host wherever you go

Like most projects, Percona Cloud Tools has evolved over time.  Consequently, certain parts of the web app were different than other parts.  These differences had workarounds, but now the system is unified. Pick a MySQL instance, pick a time range, then view whatever part of the app you want and these selections will stay the same.  This is, of course, a natural expectation because it allows you to see easily examine a specific system at a specific time range from different perspectives.

There’s a lot more, but we don’t want to take up too much of your time!

Percona Cloud Tools is still in free beta, but not for much longer, so be sure to sign up today!

The post 5 great new features from Percona Cloud Tools for MySQL appeared first on MySQL Performance Blog.


PlanetMySQL Voting: Vote UP / Vote DOWN

Building MaxScale from source with Vagrant and Puppet

Tue, 2014-08-19 11:57hartmut


MaxScale for MariaDB and MySQL hides the complexity of database scaling from the application. To streamline building MaxScale from source and running the test suite, you can automate the process with some useful tools to meet your needs.

I have created a Vagrant / Puppet setup that takes care of:

  • Creating a build / test VM with all necessary stuff installed
  • Checking out MaxScale source from git (with repository and branch being configurable)
  • Configuring the build system
  • Building and installing MaxScale
  • Starting a set of mysqld instances configured as master/slave set ready for being used by the test suite

With this set up, a virtual MaxScale build/test machine is only a "git clone" and a "vagrant up" away from you.

You can find the source for this setup at GitHub as well as its README that has more detailed information on it's usage and inner works.

Tags: DeveloperMaxScale About the Author Hartmut Holzgraefe

Hartmut has a long experience of MariaDB and MySQL.


PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Workbench 6.2.1 BETA has been released

Dear MySQL Users,

The MySQL Workbench team is announcing availability of the first beta
release of its upcoming major product update, MySQL Workbench 6.2.

MySQL Workbench 6.2 focuses on support for innovations released in MySQL
5.6 and MySQL 5.7 DMR (Development Release) as well as MySQL Fabric 1.5,
with features such as:

* A new spatial data viewer, allowing graphical views of result sets
containing GEOMETRY data and taking advantage of the new GIS
capabilities in MySQL 5.7.
* Support for new MySQL 5.7.4 SQL syntax and configuration options.
* Metadata Locks View shows the locks connections are blocked or waiting
on.
* MySQL Fabric cluster connectivity – Browsing, view status, and connect
to any MySQL instance in a Fabric Cluster.
* MS Access migration Wizard – easily move to MySQL Databases.

Other significant usability improvements were made, aiming to raise
productivity for advanced and new users:

* Direct shortcut buttons to commonly used features in the schema tree.
* Improved results handling. Columns have better auto-sizing and their
widths are saved. Fonts can also be customized. Results “pinned” to
persist viewing data.
* A convenient Run SQL Script command to directly execute SQL scripts,
without loading them first.
* Database Modeling has been updated to allow changes to the formatting
of note objects and attached SQL scripts can now be included in
forward engineering and synchronization scripts.
* Integrated Visual Explain within the result set panel.
* Visual Explain drill down for large to very large explain plans.
* Shared SQL snippets in the SQL Editor, allowing multiple users to
share SQL code by storing it within a MySQL instance.
* And much more.

The list of provided binaries was updated and MySQL Workbench binaries
now available for:
* Windows 7 32 and 64bits
* Mac OS X Lion or newer
* Ubuntu 12.04 LTS and Ubuntu 14.04
* Fedora 20
* Oracle Linux 6.5
* Oracle Linux 7
* Sources for building in other Linux distributions

For the full list of changes in this revision, visit

http://dev.mysql.com/doc/relnotes/workbench/en/changes-6-2.html

For discussion, join the MySQL Workbench Forums:

http://forums.mysql.com/index.php?151

Download MySQL Workbench 6.2.1 now, for Windows, Mac OS X 10.7+, Oracle
Linux 6 and 7, Fedora 20, Ubuntu 12.04 and Ubuntu 14.04 or sources,
from:

http://dev.mysql.com/downloads/tools/workbench/

On behalf of the MySQL Workbench and the MySQL/ORACLE RE Team.
Vishal Chaudhary


PlanetMySQL Voting: Vote UP / Vote DOWN

#DBHangOps 08/21/14 -- GTIDs, Shared Storage, and more!

#DBHangOps 08/21/14 -- GTIDs, Shared Storage, and more!

Hello everybody!

Join in #DBHangOps this Thursday, August, 21, 2014 at 11:00am pacific (18:00 GMT), to participate in the discussion about:

  • GTIDs in MariaDB Demo from Gerry!
  • Shared storage and MySQL
    • How about NFS?
  • Index Fragmentation (requested by Shlomi)

Be sure to check out the #DBHangOps twitter search, the @DBHangOps twitter feed, or this blog post to get a link for the google hangout on Thursday!

See all of you on Thursday!


PlanetMySQL Voting: Vote UP / Vote DOWN

Getting my hands dirty on an OpenStack lab

Like you all may know, OpenStack is currently one of the coolest open source projects, so I was thrilled when I was asked to manage the deployment of an OpenStack lab for internal Percona use. Starting from basically zero, I created tasks in our Jira and assigned them to a pool of volunteer consultants. As usual in a service company, billing is the priority so I ended up losing the 2 senior guys but fortunately most of my time was with a customer that wasn’t very demanding and I could easily multitask with the project and fill the gap. So, here it goes…

Hardware

To deploy the OpenStack lab we were given 8 similar servers in our Durham, N.C. offices. The specs are:

  • CPU: 12 physical cores (24 with HT)
  • Disks: 1 sata 4 TB drive and one 480GB SSD drive
  • Nics: 2x GbE
  • OS: Centos 6

The hardware is recent and decent, a good start.

Deployment choices

Given the hardware we had, I picked the first to be the controller and jumphost, the second to be the network node (a bit overkill) and the remaining 6 nodes would become the compute nodes. I also wanted to use Ceph and RBD with 2 types of volumes, the default using SATA and a SSD type using the SSD drives. The servers only have a single GbE interface to use with Ceph, that’s not ideal but sufficient.

So, we basically followed: OpenStack doc for Centos and had our share of learning and fun. Overall, it went relatively well with only a few hiccups with Neutron and Ceph.

Neutron

Neutron is probably the most difficult part to understand. You need to be somewhat familiar with all the networking tools and protocols to find your way around. Neutron relies on network namespaces, virtual network switches, GRE tunnels, iptables, dnsmasq, etc. For my part, I discovered network namespaces and virtual network switches.

The tasks of providing isolated networks to different tenants with their own set of IPs and firewalling rules, on the same infrastructure, is a challenging tasks. I enjoyed a lot reading Networking in too much detail, from there, things just started to make sense.

A first issue we encountered was that the iproute package on Centos is old and it does not support the network namespaces. It just needs to be replaced by a newer version. It took me some time to understand how things are connected, each tenant has its own Gre tunnel id and vlan. I can only recommend you read the above document and look at your setup. Don’t forget, you have one set of iptables rules per network namespace… The network node is mainly dealing with Natting, SNAT and DNAT while the security group rules are set on the compute nodes. The “ip netns exec …” and the “ovs-ofctl dump-flows …” have been my best friends for debugging.

Once I got things working, I realized the network performance was, to say the least, pretty bad. I switch “gro off” on the Nics but it made very little change. Finally, I found how the MTU of the VMs were too large, an easy fix by adding a configuration file for dnsmasq with “dhcp-option-force=26,1400″. With an MTU of 1400, less than the NIC MTU + the GRE header, packets were no longer split and performance went back to normal.

More on Ceph

The integration of Ceph happened to be more challenging than I first thought. First, let’s say the documentation is not as polished as the Openstack one, there are some rough edges but nothing unmanageable. The latest version, at the time, had no rpms for Centos but that was easy to work around if you know rpmbuild. Same for the Centos RPM for qemu-kvm and nova required a patch to support rbd devices. I succeeded deploying Ceph over the SATA drives, configured Glance and Cinder to use it and that was all good. The patch for nova allows to launch instances on clones of an image. While normally the image has to be copied to the local drive, an operation that can take some time if the image is large, with a clone, barely a few second after you started a VM, it is actually starting. Very impressive, I haven’t tested but I suppose the same can be accomplished with btrfs or ZFS, shared over iscsi.

Since the servers all have a SSD drive, I also tackled the task of setting up a SSD volume type. That has been a bit tricky, you need to setup a rule so that a given storage pool uses the SSD drives. The SSD drives must be placed all in their own branch in the ceph osd tree and then, you need to decompile the current rule set (crush map), modify it by adding a rule that use the ssd drives, recompile and then define a storage pool that uses the ssd rule. Having done this, I modified the cinder configuration for the “volume-ssd” type and finally, I could mount a ssd backed volumes, replicated, to a VM, quite cool.

The only drawback I found using Ceph is when you want to create a snapshot. Ideally, Ceph should handle the snapshot and it should be kept there as is. The way it works is less interesting, a snapshot is created but it is then copied to /tmp of the compute node, uncompressed…, and then copied back to a destination volume. I don’t know why it is done like that, maybe some workaround for limitations of other Cinder backends. The compute nodes have only 30GB available for /tmp and with Ceph, you must use raw images so that’s quite limiting. I already started to look at the code, maybe this could be my first contribution to the Openstack project.

Overall impressions

My first impression is that OpenStack is a big project with many moving, many moving parts. Installing OpenStack is not a beginners project, my experience saved me quite a few times. Logging, when you activate verbose and even debug in the configuration files is abundant and if you take your time, you can figure out what is going on and why something is failing.

Regarding the distribution, I am not sure Centos was the right fit, I think we had a rougher ride than we could have had using Ubuntu. My feeling is that the packages, not necessarily the OpenStack ones, were not as up to date as they should have compared to Ubuntu.

Ceph rbd backend is certainly a good point in my experience, I always wanted to touch Ceph and this project has been a very nice opportunity. The rbd backend works very well and the ability to launch instances almost instantaneously is just awesome. Another great plus is the storage redundancy (2 replica), like EBS, and the storage saving of working only on a clone of the original image. Also, the integration of the SSD backed pool adds a lot of flexibility. There’s only the instance snapshot issue that will need to be worked on.

So, that’s the current status of our OpenStack lab. I hope to be able to add a few more servers that are currently idle, allowing me to replace the over powerful network node and recycle it as a compute node Another thing I would really like to do is to mix the hypervisor types, I’d like to be able to use a lightweight container like LXC or docker. Also, although this is just a lab for the consultants, I’d like to see how to improve its available which is currently quite low. So, more fun to come!

The post Getting my hands dirty on an OpenStack lab appeared first on MySQL Performance Blog.


PlanetMySQL Voting: Vote UP / Vote DOWN

How to Install ClusterControl on Servers without Internet Access

August 18, 2014 By Severalnines

There are several ways to get ClusterControl installed on your database infrastructure, as described in the ClusterControl Quick Start Guide. One simple way is to use an installation script, install-cc.sh. This script automates the whole process, and is executed on the host where you want to install ClusterControl. By default, it assumes the host has internet connectivity during the installation process.

 

For users who are not able to have their ClusterControl hosts connect to the Internet during the installation, we have some good news! The installer script now supports offline installations.

 

Requirements

 

Prior to the offline install, make sure you meet the following requirements for the ClusterControl node:

  • Ensure the offline repository is ready. We assume that you already configured an offline repository for this guide. Details on how to setup offline repository is explained on the next section.
  • Firewall, SElinux or AppArmor must be turned off. You can turn on the firewall once the installation has completed. Make sure to allow ports as defined on this page.
  • MySQL server must be installed on the ClusterControl host.
  • ClusterControl packages for the selected version must exist under s9s_tmp directory from the script’s execution path.

We will now explain these steps in the following sections.

 

Setting Up Offline Repository

 

The installer script requires an offline repository so it can automate the installation process by installing dependencies. 

 

Redhat/CentOS

 

1. Insert the DVD installation disc into the DVD drive.

 

2. Mount the DVD installation disc into the default media location at /media/CentOS:

$ mount /dev/cdrom /media/CentOS

 

read more


PlanetMySQL Voting: Vote UP / Vote DOWN

Enabling GTIDs for server replication in MariaDB 10.0

Mon, 2014-08-18 08:55gerrynarvaja

Replication has been one of the most popular MySQL features since it made its way into the application more than a decade ago. However, as replication implementations became more complex over time, some limitations of MySQL’s original replication mechanisms started to surface. To address those limitations, MySQL v5.6 introduced the concept of Global Transaction IDs, which enable some new, advanced replication features. MySQL DBAs were happy with this, but complained that in order to implement GTIDs you needed to stop all the servers in the replication group and restart them with the feature enabled. There are workarounds – for instance, Booking.com documented a procedure to enable GTIDs with little or no downtime, but it involves more complexity than most organization are willing to allow.

MariaDB 10.0 implements GTIDs differently from MySQL, making it possible to enable and disable it with no downtime. Here’s how.

A Simple HA Implementation

Let’s start with a common high-availability (HA) implementation, with three servers running MariaDB 10.0 or higher: One active master (server A), one passive master (server B), and a slave replicating from the active master (server C). The active and passive masters are set up to replicate master-master.

I’m not showing it, but between servers A and B and the application you would want an additional layer to switch database traffic to server B in case A fails. Some organizations might deploy another slave replicating from B, or a mechanism to move server C to replicate from B, such as Master High Availability Manager (MHA), but let’s keep things simple here.

Step 1 - Configuration Files

GTIDs in MariaDB 10 have three parts: server ID, transaction ID, and domain ID. The server ID and transaction ID are similar in concept to those found in MySQL 5.6, which you can read about in the documentation. In MariaDB, the server ID is a number and not a UUID, and it is taken from the server_id global variable. The domain ID is an important new concept for multisource replication, which you can read more about in the domain ID article in the MariaDB knowledgebase. In our case, this is the only variable we need to set up; the server ID should already be set up if replication is functional. Let’s use 1 for a domain ID for server A, 2 for B, and 3 for C by executing commands like the following on each of the servers:

SET GLOBAL gtid_domain_id = 1;

Keep in mind that each session can have its own value for gtid_domain_id, so you'll have to reset all existing connections to properly reset the gtid_domain_id. Finally, persist the values in the corresponding my.cnf files:

# Domain = 1 for active master: server A gtid-domain-id=1Step 2 - Changing Replication on the Slave

Running SHOW MASTER STATUS on server A, the active master, shows the current coordinates for its binary log file:

MariaDB [(none)]> SHOW MASTER STATUS\G *************************** 1. row *************************** File: mariadb-bin.000001 Position: 510 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)

We can use these coordinates to find the information we need in order to use GTIDs from the active master by running the command:

SELECT BINLOG_GTID_POS('mariadb-bin.000001', 510); +--------------------------------------------+ | BINLOG_GTID_POS('mariadb-bin.000001', 510) | +--------------------------------------------+ | 1-101-1 | +--------------------------------------------+ 1 row in set (0.00 sec)

Note that the GTID can be an empty string, for clarity purposes the examples work with non-empty GTID values. The result from the function call is the current GTID, which corresponds to the binary file position on the master. With this value, we can now modify the slave configuration on servers B and C executing the following statements on each of them:

STOP SLAVE; SET GLOBAL gtid_slave_pos = '1-101-1'; CHANGE MASTER TO master_use_gtid=slave_pos; START SLAVE;

Check the slave status to see that the change has taken effect:

MariaDB [mysql]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.101 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000001 Read_Master_Log_Pos: 510 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 642 Relay_Master_Log_File: mariadb-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ... Using_Gtid: Slave_Pos Gtid_IO_Pos: 1-101-1 1 row in set (0.00 sec)

The last two lines of SHOW SLAVE STATUS indicate that the slave is now using GTIDs to track replication.

Conclusion

As you can see, the procedure to enable GTIDs is straightforward, and doesn’t require restarting servers or planning for downtime. If you want to revert back to regular replication using binary log position, you can do so by using RESET SLAVE on the slave and resetting the proper binary log coordinates the traditional way. In fact, once you update your servers to use MariaDB 10.0 and review the binary log files with the mysqlbinlog, you'll notice that every transaction in the MariaDB 10.0 binary logs has the GTID already included. For the binary log in the examples used in this article:

# at 314 #140807 14:16:01 server id 101 end_log_pos 352 GTID 1-101-1 /*!100001 SET @@session.gtid_domain_id=1*//*!*/; /*!100001 SET @@session.server_id=101*//*!*/; /*!100001 SET @@session.gtid_seq_no=1*//*!*/;

I hope that the ease of implementing GTIDs in MariaDB 10.0 piques your curiosity and encourages you to explore the new replication features.

Tags: ClusteringHigh AvailabilityMaxScale About the Author Gerry Narvaja

Gerardo Narvaja is a Senior Sales Engineer. He has been in the software industry for almost 3 decades and involved with MySQL as a Sales Engineer and DBA for more than 1 decade, including a little over six years at MySQL Ab.


PlanetMySQL Voting: Vote UP / Vote DOWN

Items Affecting Performance of the MySQL Database

To learn about the many factors that can affect the performance of the MySQL Database, take the MySQL Performance Tuning course.

You will learn:

  • How your hardware and operating system can affect performance
  • How to set up and logging to improve performance
  • Best practices for backup and recovery
  • And much more

You can take this 4-day instructor-led course through the following formats:

  • Training-on-Demand: Start training within 24 hours of registering for training, following lectures at your own pace through streaming video and booking time on a lab environment to suit your schedule.
  • Live-Virtual Event: Attend a live event from your own desk, no travel required. Choose from a selection of events on the schedule to suit different time-zones.
  • In-Class Event: Travel to an education center to attend this course. Below is a selection of events already on the schedule.

 Location

 Date

 Delivery Language

 Brussels, Beligum

 10 November 2014

 English

 Sao Paolo, Brazil

 25 August 2014

 Brazilian Portuguese

 London, England

 20 October 2014

 English

 Milan, Italy

 20 October 2014

 Italian

 Rome, Italy

 1 December 2014

 Italian

 Riga, Latvia

 29 September 2014

 Latvian

 Petaling Jaya, Malaysia

 22 September 2014

 English

 Utrecht, Netherlands

 10 November 2014

 English

 Warsaw, Poland

 1 September 2014

 Polish

 Barcelona, Spain

 14 October 2014

 Spanish

To register for an event, request an additional event, or learn more about the authentic MySQL Curriculum, go to http://education.oracle.com/mysql.


PlanetMySQL Voting: Vote UP / Vote DOWN

Take image from corrupted hard drive

There are at least two cases when it makes sense to take an image from a corrupted hard drive as soon as possible: disk hardware errors and corrupted filesystem. Faulty hard drives can give just one chance to read a block, so there is no time for experiments. The similar picture with corrupted filesystems. Obviously something went wrong, it’s hard to predict how the operating system will behave next second and whether it will cause even more damage.

Save disk image to local storage

Probably the best and fastest way is to plug the faulty disk into a healthy server and save the disk image locally:

# dd if=/dev/sdb of=/path/on/sda/faulty_disk.img conv=noerrror

Where /dev/sdb is the faulty disk and faulty_disk.img is the image on the healthy /dev/sda disk.

conv=noerrror tells dd to continue reading even if read() call exited with an error. Thus dd will skip bad areas and dump as much information from the disk as possible.

By default dd reads 512 bytes and it is a good value. Reading larger blocks would be faster, but the larger block will fail even if a small portion of the block is unreadable. InnoDB page is 16k, so dd reads one page in eight operations. It’s possible to extract information even if the page is partially corrupt. So, reading in 512 bytes blocks seems to be optimal unless somebody convinces me in opposite.

Save disk image to remote storage

If the faulty disk can’t be unplugged the best (if not only) way is to save the disk image on a remote storage.

Netcat is an excellent tool for this purpose.

Start on the destination side a server:

# nc -l 1234 > faulty_disk.img

On the server with the faulty disk take a dump and stream it over network

# dd if=/dev/sdb of=/dev/stdout conv=noerrror | nc a.b.c.d 1234

a.b.c.d is the IP address of the destination server.

Why dd is better for MySQL data recovery

There is a bunch of good file recovery or file undelete tools. However they serve slightly different purpose. In short they try to reconstruct a file. They care about a file system.

For MySQL data recovery we don’t need files, we need data. InnoDB page can be recognized by a short signature in the beginning of the page. In the fixed places there are two internal records in every index page infimum and supremum:

00000000 3f ff 6f 3d 00 00 11 e0 ff ff ff ff 00 00 11 e8 |?.o=............| 00000010 00 00 00 00 14 8f 8f 57 45 bf 00 00 00 00 00 00 |.......WE.......| 00000020 00 00 00 00 00 00 00 17 3b 58 00 af 1d 95 1d fb |........;X......| 00000030 1d 3e 00 02 00 03 00 5a 00 00 00 00 00 00 00 00 |.>.....Z........| 00000040 00 00 00 00 00 00 00 00 00 01 00 00 00 00 00 00 |................| 00000050 00 00 00 00 00 00 00 00 00 00 00 00 00 00 08 01 |................| 00000060 00 00 03 00 8d 69 6e 66 69 6d 75 6d 00 09 03 00 |.....infimum....| 00000070 08 03 00 00 73 75 70 72 65 6d 75 6d 00 38 b4 34 |....supremum.8.4| 00000080 30 28 24 20 18 11 0b 00 00 10 15 00 d5 53 59 53 |0($ .........SYS| 00000090 5f 46 4f 52 45 49 47 4e 00 00 00 00 03 00 80 00 |_FOREIGN........|

If the header is good then we know what table the page belongs to, how many records to expect etc. Even if the rest of the page is heavily corrupted it’s possible to extract all survived records.

I had several cases when dd excelled.
Story #1.

It was a dying hard drive. InnoDB crashed all the time. When a customer figured out the problem was with the disk they tried to copy MySQL file. But simple copy has failed. The customer had tried to read the files with some file recovery tool.

MySQL refused to start and reported checksum mismatched in the error log.

The customer provided the recovered files. Size of ibdata1 file was reasonable, but stream_parser has found ~20MB of pages. ibdata1 was almost empty inside – just all zeroes where the data should be. I doubt that even 40% of data was recovered.

Then we tried to take a dump of the disk and recover InnoDB tables from the image. First of all, there were found ~200MB of pages. Many tables were 100% recovered and around 80-90% records were fetched from corrupted tables.

Story #2.

A customer has dropped InnoDB database. MySQL was  running with innodb_file_per_table=ON. So, the tables were in .ibd file that were deleted. It was a Windows server and the customer used some tool to undelete the .ibd files from NTFS filesystem. The tool restored the files, but the ibd files were almost empty inside. The recovery rate was close to 20%.

Recovery from a disk dump gave around 70-80% of records.

The post Take image from corrupted hard drive appeared first on Backup and Data Recovery for MySQL.


PlanetMySQL Voting: Vote UP / Vote DOWN

The new cloud backup option of MySQL Enterprise Backup

MySQL Enterprise Backup 3.10 support backups to the cloud. The only supported cloud service is Amazon S3.

When the cloud destination is used mysqlbackup will upload the backup as an image file.

You can specify all options on the commandline:
mysqlbackup --cloud-service=s3 --cloud-aws-region=eu-west-1 \--cloud-access-key-id=AKIAJLGCPXEGVHCQD27B \--cloud-secret-access-key=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 \--cloud-bucket=meb_myserver --cloud-object-key=firstbackup --cloud-trace=0 \--backup-dir=/tmp/firstbackup --backup-image=- --with-timestamp backup-to-image

But you can also put the settings in the my.cnf
[mysqlbackup_cloud]
cloud-service=s3
cloud-aws-region=eu-west-1
cloud-access-key-id=AKIAJLGCPXEGVHCQD27B
cloud-secret-access-key=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
cloud-bucket=meb_myserver
cloud-trace=0
backup-dir=/data/cloudbackup
backup-image=-
with-timestamp

The with-timestamp option is important as the backup won't start if the backup-dir already exists. This is because mysqlbackup will leave the backup directory exists after uploading the backup. The backup directory will only have meta info and the log file, not the actual backup.

By using a group suffix like _cloud you can put settings for multiple types of backups in one cnf file.

mysqlbackup --defaults-group-suffix='_cloud' \
--cloud-object-key=backup_2014081701 backup-to-image

The account you're using should have this policy to be allowed to read and write to the s3 bucket:
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "Stmt1408302840000",
"Effect": "Allow",
"Action": [
"s3:GetObject",
"s3:ListBucket",
"s3:ListBucketMultipartUploads",
"s3:PutObject"
],
"Resource": [
"arn:aws:s3:::meb_myserver/*"
]
}
]
}
This looks like a good option to me if you're already using mysqlbackup and amazon. It would be nice if the next version would support other cloud providers (e.g. openstack swift, ceph). Implementing this should be easy for those with an s3 compatibility layer, but will probably take more time for others.

I did find some bugs (just search for tag=cloud on http://bugs.mysql.com if you're interested).
PlanetMySQL Voting: Vote UP / Vote DOWN

libAttachSQL 0.2.0 alpha released!

Hot on the heals of last week's release we have released version 0.2.0 alpha of libAttachSQL.  For those who have missed my previous blog posts, libAttachSQL is a lightweight C connector for MySQL servers I'm creating with HP's Advanced Technology Group.  It has an Apache 2 license so is good for linking with most Open Source licenses as well as commercial software projects.

Changes in this release:

  • Added support for query result buffering
  • Passive connect on first query is now asynchronous
  • Improved memory handling
  • Many documentation changes, including API examples
  • Many other smaller fixes
For more information see the libAttachSQL documentation and the release itself can be found on the libAttachSQL website.
We have had some great feedback so far.  Thanks to everyone who has contacted me since the first release.  As always if you have any questions feel free to contact me or file an issue on GitHub.
PlanetMySQL Voting: Vote UP / Vote DOWN

Pages