PlanetMysql.ru — информация о СУБД MySQL

common_schema, revision 218 is released, with major new features, top one being server side scripting. Here are the highlights:

• QueryScript: server side scripting is now supported by common_schema, which acts as an interpreter for QueryScript code.
• Throttling for queries is now made available via the throttle() function.
• Enhancements to processlist-related views, including the new slave_hosts view.
• Inline documentation/help is available via the help() routine.
• more...

QueryScript

common_schema makes for a QueryScript implementation for MySQL. You can run server side scripts, interpreted by common_schema, which allow for easy syntax and greater power than was otherwise previously available on the MySQL server. For example:

foreach($table, $schema, $engine: table like '%')
  if ($engine = 'ndbcluster')
    ALTER ONLINE TABLE :$schema.:$table REORGANIZE PARTITION;

QueryScript includes flow control, conditional branching, variables & variable expansion, script throttling and more.

Read more on common_schema's QueryScript implementation.

Query throttling

Throttling for MySQL queries was suggested by means of elaborate query manipulation. It is now reduced into a single throttle function: one can now just invoke throttle(3) on one's query, so as to make the query execute for a longer time, while taking short sleep breaks during operation, easing up the query's demand for resources.

Read more on query throttling.

Process views

The processlist_grantees view provides with more details on the running processes. slave_hosts is a new view, listing hostnames of connected slaves.

Read more on process views.

help()

The common_schema documentation is now composed of well over 100 pages, including synopsis, detailed internals discussion, notes and examples. I can't exaggerate in saying that the documentation took the vast majority of time for this code to release.

The documentation is now made available inline, from within you mysql client, via the help() routine. Want to know more about redundant (duplicate) keys and how to find them? Just type:

call help('redundant');

and see what comes out!

The entire documentation, which is available online as well as a downloadable bundle, is embedded into common_schema itself. It's rather cool.

Tests

common_schema is tested. The number of tests in common_schema is rapidly growing, and new tests are introduced for new features as well as for older ones. There is not yet full coverage for all views, but I'm working hard at it. common_schema is a robust piece of code!

Get it!

Download common_schema on the common_schema project page.

Read the documentation online, or download it as well (or call for help())

common_schema is released under the BSD license.

PlanetMySQL Voting: Vote UP / Vote DOWN

Introducing QueryScript: a programming language aimed for SQL scripting, seamlessly combining scripting power such as flow control & variables with standard SQL statements or RDBMS-specific commands.

QueryScript is available fro MySQL via common_schema, which adds MySQL-specific usage.

What does QueryScript look like? Here are a few code samples:

Turn a bulk DELETE operation into smaller tasks. Throttle in between.

while (DELETE FROM archive.events WHERE ts < CURDATE() LIMIT 1000)
{
  throttle 2;
}

Convert all InnoDB tables in the 'sakila' database to compressed format:

foreach ($table, $schema, $engine: table in sakila)
{
  if ($engine = 'InnoDB')
    ALTER TABLE :$schema.:$table ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
}

Shard your data across multiple schemata:

foreach($shard: {USA, GBR, JAP, FRA})
{
  CREATE DATABASE db_:$shard;
  CREATE TABLE db_:$shard.city LIKE world.City;
  INSERT INTO db_:$shard.city SELECT * FROM world.City WHERE CountryCode = $shard;
}

This tight integration between script and SQL, with the power of iteration, conditional statements, variables, variable expansion, throttling etc., makes QueryScript a power tool, with capabilities superseding those of stored routines, and allowing for simplified, dynamic code.

QueryScript code is interpreted. It's just a text, so it can be read from a @user_defined_variable, a table column, text file, what have you. For example:

mysql> set @script := "while (TIME(SYSDATE()) < '17:00:00') SELECT * FROM world.City WHERE id = 1 + FLOOR((RAND()*4079));";
mysql> call run(@script);

For more details, consult the QueryScript site.

If you're a MySQL user/DBA, better read the common_schema QueryScript documentation, to better understand the specific common_schema implementation and enhanced features.

common_schema, including the QueryScript interpreter, can be downloaded from the common_schema project page.

PlanetMySQL Voting: Vote UP / Vote DOWN

Last week we concluded our first Oracle Technology Network Sys Admin Day in Sacramento (CA). Well, it was actually the second Sys Admin Day, but the first one that had two parallel tracks of sessions about both Oracle Linux and Oracle Solaris.

I helped preparing for the event by creating the Linux lab handbook as well as the VirtualBox appliance of Oracle Linux 6.1 that was used for the exercises. Unfortunately I could not be there in person, but it would have been pointless for me to go on an intercontinental flight just for one day.

From the feedback we've received so far, the attendees really enjoyed the event and were positively surprised about the depth and quality of the practical hands-on lab sessions.

If you've missed the first one and happen to live somewhere in the Seattle area, you have another chance to attend OTN sysadmin day: we'll be hosting another one on Thursday, September 22nd at The Westin Seattle (1900 5th Ave., Seattle, WA 98101). Again, attendance is free, all you need to bring is your own laptop with VirtualBox installed. We'll provide the rest. Space is limited — you can review the agenda and register here!

PlanetMySQL Voting: Vote UP / Vote DOWN

It happens that sometimes you need to access a remote box which supports ssh key authentication. Recently I was trying to reproduce a bug related to SSH public key authentication, so here I would like to share some of my experience.

There will be no explanation of the public key authentication itself here, rather the actual setup and steps to have a public key auth for Windows(client) -> Linux(server) working. Why Windows you would ask? Because interactions for Linux->Linux and for Mac OS X -> Linux simply work using the Unix way, while for Windows you may need some extra actions to do.

 

Setup

What I had at endpoints:

Linux – Ubuntu 11.04, sshd is set up to deny password auth.
Windows – well, it is an XP SP3 i386 box. MySQL Workbench 5.2.34+ is installed

First of all I created an encrypted pair of RSA keys, using Linux box’s ssh-keygen. After that the public key was added to ~/.ssh/authorized_keys and the private one was moved to the Windows box.

Naturally my first attempt was to simply specify path to the private key file in the server settings, just as I would do in Linux or OS X.

 

 

That did not work, just as the bug report had said. Moving key to $HOME/.ssh/id_rsa did not help. Could that be that paramiko can not handle openssh keys on Windows, or openssh’s encryption method?

 

 

Bazaar has similar issues on Windows, the solution they suggest is to either put keys into .ssh dir, or use pageant tool from PuTTY.  I tried .ssh, that did not work. So the latter way turned into conversion of the openssh private key into PuTTY ppk format. The conversion is done using PuTTYgen, then the key is loaded in the pageant. More details are given in the mentioned bazaar guide Bzr and SSH.

And this worked! Let me sum up the steps:

1. Generate keys, using either openssh on Linux, OSX, Cygwin, or using PuTTYgen;
2. Specify private ssh key in the appropriate section of the Workbench’s “Server Instance Editor”;
3. Add key to pageant tool.
4. At this moment passwords to unlock keys have to be entered both in MySQL Workbench and the pageant.
5. Use it…

PlanetMySQL Voting: Vote UP / Vote DOWN

I'm happy to announce that my talk "Making MySQL administration a breeze - a look into a MySQL DBA's toolchest" has been accepted for this year's edition of the MySQL Conference & Expo in Santa Clara, which will take place on April 12-15, 2010. The session is currently scheduled for Wednesday 14th, 10:50 in Ballroom E.

My plan is to provide an overview over the most popular utilities and applications that a MySQL DBA should be aware of to make his life easier. The focus will be on Linux/Unix applications available under opensource licenses that ease tasks related to user administration, setting up and administering replication setups, performing backups and security audits.

Of course I will cover the usual suspects (e.g. Maatkit), some of these are actually collections of different utilities by themselves. As it's impossible to go over each individual component in the given time frame, I will try to pick out the most popular/useful parts related to the scopes mentioned above. But I will also cover some lesser known gems that migh be worth taking a look at. What's your the most valued tool in your toolchest? I am still looking for more inspiration.

I look forward to being at the conference again and meeting with colleagues and friends in the MySQL community. Judging from the current schedule, it will be a very interesting mix of talks.

If you're interested in attending, you should consider registering soon! The early registration ends on March 15th. Until then, I encourage you to make use of this "Friend of Speaker" discount code (25% off): mys10fsp

PlanetMySQL Voting: Vote UP / Vote DOWN

PlanetMySQL Voting: Vote UP / Vote DOWN

Maatkit is a pretty useful set of utilities for MySQL. From their site:

You can use Maatkit to prove replication is working correctly, fix corrupted data, automate repetitive tasks, speed up your servers, and much, much more.

One of the first things you can do after installing the toolkit (which may already be installed if you are running CentOS or Debian) is to run the mk-audit utility. It will give you a nice summary of your server, as well as point out potential problems in your configuration.

Here's a list of all the utilities included in Maatkit:

• mk-archiver Archive rows from a MySQL table into another table or a file.
• mk-audit Analyze, summarize and report on MySQL config, schema and operation
• mk-checksum-filter Filter checksums from mk-table-checksum.
• mk-deadlock-logger Extract and log MySQL deadlock information.
• mk-duplicate-key-checker Find duplicate indexes and foreign keys on MySQL tables.
• mk-fifo-split Split files and pipe lines to a fifo without really splitting.
• mk-find Find MySQL tables and execute actions, like GNU find.
• mk-heartbeat Monitor MySQL replication delay.
• mk-kill Kill MySQL queries that match certain criteria.
• mk-loadavg Watch MySQL load and take action when it gets too high.
• mk-log-player Split and play MySQL slow logs.
• mk-parallel-dump Dump sets of MySQL tables in parallel.
• mk-parallel-restore Load files into MySQL in parallel.
• mk-profile-compact Compact the output from mk-query-profiler.
• mk-query-digest Parses logs and more. Analyze, transform, filter, review and report on queries.
• mk-query-profiler Execute SQL statements and print statistics, or measure activity caused by other processes.
• mk-show-grants Canonicalize and print MySQL grants so you can effectively replicate, compare and version-control them.
• mk-slave-delay Make a MySQL slave server lag behind its master.
• mk-slave-find Find and print replication hierarchy tree of MySQL slaves.
• mk-slave-move Move a MySQL slave around in the replication hierarchy.
• mk-slave-prefetch Pipeline relay logs on a MySQL slave to pre-warm caches.
• mk-slave-restart Watch and restart MySQL replication after errors.
• mk-table-checksum Perform an online replication consistency check, or checksum MySQL tables efficiently on one or many servers.
• mk-table-sync Synchronize MySQL tables efficiently.
• mk-upgrade Execute SQL statements against two MySQL servers and compare the results.
• mk-visual-explain Format EXPLAIN output as a tree.

PlanetMySQL Voting: Vote UP / Vote DOWN

I am publishing my MySQL related bookmark collection http://www.mysqlpreacher.com/bookmarks/. Feel free to send me links you think might be good to add in order to help others. Remember, SHARING IS CARING!!! …. we get so much for free, why shouldn’t we give some back? Cheers, Darren
PlanetMySQL Voting: Vote UP / Vote DOWN

I am publishing my MySQL related bookmark collection http://www.mysqlpreacher.com/bookmarks/. Feel free to send me links you think might be good to add in order to help others. Remember, SHARING IS CARING!!! …. we get so much for free, why shouldn’t we give some back? Cheers, Darren
PlanetMySQL Voting: Vote UP / Vote DOWN

Just a small note to advise that Securich reached 0.1.4. Some new tools include: * Added Password complexity * Enhanced `set_password` – Old password is now necessary to replace it by a new one * Enhanced Revoke privileges to accept regexp * Added Block user@hostname on a database [...]
PlanetMySQL Voting: Vote UP / Vote DOWN