Translation of appendix about methods of copying and moving MySQL databases just published. This is just short overview of possible methods and does not pretend to be detailed guide. It starts as:

Appendix. Methods of copying and moving of MySQL databases.

In this application I'd like to shortly discuss general methods of backup and moving of mySQL databases.

Easier and recommended way of data moving is mysqldump utility. You can copy data with help of following command:


$mysqldump dbname [tblname ...] >dump.sql


...

and continues here …

This post describes how to make a custom cursor returning rows as dictionaries using MySQL Connctor/Python v0.2 (or later).

Problem: you want to fetch rows from the database and return them as a dictionary with keys being the column names.

First, lets check how you would do it without any custom cursor.

cnx = mysql.connector.connect(host='localhost',database='test')
cur = cnx.cursor()
cur.execute("SELECT c1, c2 FROM t1")
result = []
columns = tuple( [d[0].decode('utf8') for d in cur.description] )
for row in cur:
  result.append(dict(zip(columns, row)))
pprint(result)
cur.close()
cnx.close()
[python]

The above results in an output like this:

[python light="true"]
[{u'c1': datetime.datetime(2010, 10, 13, 8, 55, 35), u'c2': u'ham'},
 {u'c1': datetime.datetime(2010, 10, …

MySQL Replication from 1 Master to Multiple Slaves

A new white paper has been published that covers MySQL Replication – background information, how it works, how to use it and what’s new in MySQL 5.5. Simply register for the white paper at mysql.com and you’ll be sent your free copy.

The paper starts by covering the fundamental concepts behind replication such as the difference between synchronous and asynchronous replication and the idea behind semisynchronous replication.

It goes on to describe the common use-cases for replication – scaling out, high availability, geographic redundancy and offloading backups or analytics.

Various replication topologies are discussed from simple master-slave …

Next development release v0.2.0 of MySQL Connector/Python is available for download and testing. We still don’t recommend to use it in production: it is not beta or GA yet, but we are getting there.

Bug reports and feature requests are welcome through the Launchpad bug tracking tool.

Highlights:

• .executemany() now optimizes INSERT statements using the MySQL
  multiple row syntax.
• Setting sql_mode and time_zone when connecting as well as collation.
• Raw Cursors can be used when you want to do the conversion yourself.
• Unittests now bootstrap own MySQL server instance.
• Tidying the source tree.

Full list of …

I’ve written before about how to get accepted to the conference. We want great technical submissions in a broad variety of topics, for databases well beyond MySQL. I wanted to post a quick list of things that come to my mind as good ways to get voted down or rejected out of hand. In general, I can put it this way: you are being peer-reviewed by presenters and industry experts. You need to write your proposal for the committee as well as for attendees.

While reading through the manual I ran across something I had totally forgotten about from the early MySQL days. Early on, Monty (or was it Jani?) decided to name many development servers variants of “bitch” in different languages. I have no idea what the back-story was, but maybe Monty or Jani can fill it in. All of these names live on all over the place, such as in the MySQL and InnoDB documentation, bug reports, and mailing list messages. See:

• bitch.mysql.fi — English, of course.
• hundin.mysql.fi — German
• hynda.mysql.fi — Swedish
• narttu.mysql.fi — …

Oracle has released news about changing policies of MySQL Enterprise Support effectively dropping annual support for Basic and Silver. The entry level support is now $3000 per server per year. The MySQL support team now part of Oracle has great resources however Oracle is in the business of making money. When a general company question for OOW is company income, and the first option is < $50 million it highlights that startups, and smaller companies are clearly not a focus.

The success of MySQL as an open source company has lead to other leading providers that now can provide enterprise level support. More importantly many organizations over per-incident support which is more cost effect. News in the past week has included Percona - …

This is one of the MySQL presentations I’m doing on the OTN LAD Tour in South America, starting today in Lima, Peru.

MySQL Best Practices for DBAs and Developers

Learn the right techniques to maximize your investment in MySQL by knowing the best practices for DBAs and Developers. Understand what subtle differences between MySQL and other RDBMS products are essential to understand in order to maximize the benefits and strengths of MySQL. We will be covering areas including the minimum MySQL configuration, ideal SQL, MySQL security and schema optimizations.

• MySQL Configuration default settings including SQL_MODE
• Documenting, formatting and future proofing your SQL
• Developing and reviewing all SQL paths
• MySQL physical and user security
• The best schema optimizations
• Essential Monitoring and …

I am not an expert in InnoDB internals and have only little experience with using transactions actually. But I have started learning basics and understanding SHOW ENGINE INNODB STATUS (and the Information_Schema tables exposing same information in later versions). I stumbled across a different behavior between MySQL 5.0 and later and also find other transactional engines exposing yet another behavior. This is related to SELECT .. LOCK IN SHARE MODE primarily. But let us take SELECT .. FOR UPDATE as well for completness. Case(s) 1 below is about SELECT .. FOR UPDATE and case(s) 2 is about SELECT .. LOCK IN SHARE MODE.

– Case 1a (all InndoDB/XtraDB versions + PBXT)
–
– From connection 1: Execute the following
USE test;
CREATE TABLE blah(a INT PRIMARY KEY) ENGINE=INNODB;
INSERT INTO blah(a) VALUES(1);
INSERT INTO blah(a) VALUES(0);
START TRANSACTION;
SELECT * FROM blah FOR UPDATE; …

Benchmarking is liking running your system through it’s paces.  You don’t know how fast your software and hardware are until you’ve put some pressure on them.  Benchmarking tools allow you to do just that.  We use sysbench to look at the operating system and mysqlslap to run queries in the MySQL database.

Database Journal – MySQL Server Benchmarking 101