Replication stars

Datacharmer post:
Working with replication, you come across many topologies, some of them sound and established, some of them less so, and some of them still in the realm of the hopeless wishes. I have been working with replication for almost 10 years now, and my wish list grew quite big during this time. In the last 12 months, though, while working at Continuent, some of the topologies that I wanted to work with have moved from the cloud of wishful thinking to the firm land of things that happen. My quest for star replication starts with the most common topology. One master, many slaves (...)

Implementing table quotas in MySQL

Interesting post from The Data Charmer blog

MySQL Workbench

MySQL Workbench is a great tool, useful for any MySQL administrator or developer. There is a lot more that the Workbench can do; it can be used to create and run scripts on data, it can do data modeling, and more. It is really a very powerful and versatile tool.

 Download it

MySQL Community Server 5.1.47 has been released

MySQL Community Server 5.1.47, a new version of the popular Open
Source Database Management System, has been released.  MySQL 5.1.47 is
recommended for use on production systems.

For an overview of what's new in MySQL 5.1, please see

http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html

How MySQL Powers Web 2.0

From MySql  web site

An overview of how MySQL helps power Web 2.0 technologies and companies

Web 2.0 can be thought of as the technologies and web sites that leverage users and developers in a socially collaborative manner in order to rapidly develop data and applications with a high level of integration across platforms and other services.

MySQL enables up-and-coming Web 2.0 sites like Wikipedia, FeedBurner and digg, - as well as established web properties like Craigslist, Google and Yahoo! - to scale out and meet the ever-increasing volume of users, transactions and data.

The information presented here will be valuable to entrepreneurs about to create their own Web 2.0 business, existing web properties wishing to bring their applications to the next level, but also to the large number of enterprises interested in leveraging Web 2.0 technologies. You will also gain an understanding of how MySQL can be used in conjunction with other open source components to deliver low-cost, reliable, scalable, high performance Web 2.0 applications.

Read it 

Exchanging partitions with tables

Last post from the Data Charmer blog,  in this post Giuseppe explain in details the new feature instantaneous exchange between a partition and a table with the same structure.
Read it

Introduction to MySQL 5.5

In this John Russell's article you can read about speed, scalability, usability of the MySQL database and the InnoDB storage engine.
Read more.

Rinaldo

MySQL :: Top Reasons to Use MySQL

MySQL® database maybe the world most popular open source why?
MySQL :: Top Reasons to Use MySQL

Secure MySQL Database Design

Currently I am working on a project that used MySql. The database needs to be secured by searching the net I found this article written by Kristy Westphal dating from 2003 but still current.


Rinaldo

jAPS 2.0 version 2.0.10 Released

jAPS 2.0 version 2.0.10 has been released on Sourceforge, where are available the following packages:

• jAPS2.0-2.0.10-src
• jAPS2-0-2.0.10-PortalExample-Project
• jAPS2-0-2.0.10-PortalExample-Standalone
• jAPS Official Plugin: as usual, all those plugins that were modified to work with the new release of jAPS are released as well; the remaning are intended to be fully compatible

Among the important innovations of jAPS 2.0.10 version there is the new "entity interface" which allows the configuration of the entity types, the CMS content types and the modification of the entity attributes.
jAPS version 2.0.10 also includes:

• MySQL scripts
• Search by page code in Page Management
• Update of moo-jAPS library
• Two new fields in CMS contents: the version and the username
• Translation of Javadoc notes in the source code
• Update of contents preview
• Links to a page with the current stable version number in the back office

More informations are available in the ChangeLog file.
Links

• Download jAPS 2.0 version 2.0.10

(via jAPSPortal.org)

JavaPassion MySql Course started

The MySQL® database is "The world's most popular open-source database" and for good reasons.  It provides fast performance, high reliability and ease of use. MySQL runs on more than 20 platforms including Linux, Windows, Mac OS, Solaris, HP-UX, IBM AIX, giving you the kind of flexibility that puts you in control. 
So if you want to know more about MySQL I suggest you to have a look at the  Sang Shin course, www.javapassion.com/mysql

Db and Pentaho video tutorials

Today I present you a very useful site for all those who want to start using MySQL and Pentaho

Rinaldo

Maatkit Power tools for open-source databases

Maatkit, a set of command-line tool f not only for MySql, released under the GPL, written in Perl. 
I will suggest it to all DBA.
List of commands:

• mk-archiver Archive rows from a MySQL table into another table or a file.
• 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-error-log Find new and different MySQL error log entries.
• 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 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 queries on multiple servers and check for differences.
• mk-visual-explain Format EXPLAIN output as a tree.

References: Maatic web site

MySQL Sandbox

MySQL Sandbox is a tool that installs one or more MySQL servers within seconds, easily, securely, and with full control. Once installed, the sandbox is easily used and maintained, without using complex options.
Replicated and multiple sandboxes can be used individually or all at once

Have a look at this tool,created and initially developed by Giuseppe Maxia

Optimizing db performance

A couple of weeks ago my colleague Daniel asked me tips and info on optimizing db.
Afficher en écriture latine

With the documentation I have, I found a certain number of techniques, some available on opensource DBMS and  other only on proprietary DBMS.

• Indexing normaly when you query a table all the table data will be read (full table scan). Indexing data is like indexes in a book. So primary key is a special kind of index, it's the unique identifier for a row on the db. How to build indexes? the answer is not easy, but we must always remember that creating a db is not a project but an ongoing process.
  If for example I want to get all the sales ordered by date I will create an index on date_sales field with descending sort order. In this way retrieving last week's sales won't be slow.
• Bitmap indexing this is a sepcial form of index (unfortunatly  not available on MySql) normaly used in columns with a low number of unique values gender, color, ...). The idea is to create for the distinct number of the values in the column, a bit projection is created. This enable faster access to the indexed values.
• Partitioning introduced in MySql 5.1 it's the capibility  to slice a table into multiple physical pieces. For example a large table containing sales data can be partioned by year so that queries for a given year will be performed only on the portion of the data for the requested year.
• ...

 This is only a starting point, in future I will investigate Change Data Capture

techniques, use of products such as Kettle, Mondrian, ...

Good eveningRinaldo

Mysql Access denied for user: 'root@localhost'

For those who like me receives this error due to a change in the db.I propose this solution which works on Ubuntu 9.04:
$ sudo service mysql stop
$ sudo mysqld -skip-grant-tables
so use your favorite tool to do the "grant" of users, then stop mysqld and restarted the mysql service
$ sudo service mysql startI hope this post will serve.


keep in touch
regards
Rinaldo