Feed Aggregator

Hello everybody,

One month earlier than planned we have the great pleasure to announce you that the company called FromDual goes operational today!

We are excited about this step and it is an new era in our personal evolution to get back in full-contact with customers and solve their real life day-to-day MySQL problems.

So we are happy hearing from you and to help you solving your individual MySQL problems…

You can find us at FromDual or you can drop us a line.

Regards,
Oli Sennhauser (aka Shinguz)
Senior MySQL Consultant at FromDual

About FromDual

FromDual provides neutral and vendor independent MySQL consulting, training and other services around MySQL and its derivatives. The company concentrates on the individual needs of its customers and achieves, in a close co-operation the best results for their problems.

Our consultants have been working in many projects in Europe. We were involved in small start-ups, medium size enterprises and huge world wide operating top-500 companies and solved their …

Hello everybody,

One month earlier than planned we have the great pleasure to announce you that the company called FromDual goes operational today!

We are excited about this step and it is an new era in our personal evolution to get back in full-contact with customers and solve their real life day-to-day MySQL problems.

So we are happy hearing from you and to help you solving your individual MySQL problems…

You can find us at FromDual or you can contact us here.

Regards,
Oli Sennhauser (aka Shinguz)
Senior MySQL Consultant at FromDual

About FromDual

FromDual provides neutral and vendor independent MySQL consulting, training and other services around MySQL and its derivatives. The company concentrates on the individual needs of its customers and achieves, in a close co-operation the best results for their problems.

Our consultants have been working in many projects in Europe. We were involved in small start-ups, medium size enterprises and huge world wide operating top-500 companies and solved their …

The following presentations are available:

[http://www.blogger.com/profile/16041515498065869604 qu1j0t3] said…

ZFS can prevent corruption from ever reaching MySQL (or any application).

For mission critical data I wouldn’t use anything else.

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

Hi Shlomi,

Thanks a lot for your input!

I am not a security specialist so I do not know what those guys who implement audit solutions think about your approach. But I can imagine that moving login information away immediately gives less possibilities to manipulate those data.

But as SUPER users are not logged it is halve baked anyway.

Using your approach in combination with the federated or federatedX Storage Engine would give again the possibility to store the information remote.

Regards, Oli

[http://www.blogger.com/profile/11874165719204714241 Shlomi N.] said…

Hi, This is a cool hack! One can simplify it by not writing to the error log but to some internal log table, in which case you can use a normal stored function, no need for UTF.

To complicate the simplification: You can later on purge rows from that log file and do stuff with, like appending them to the error log, using external script.

Problem

A customer recently showed up with the following problem:

*With your guidelines
[ 1
] I am now able to send the MySQL error log to the syslog and in particular to an external log server.
But I cannot see which user connects to the database in the error log.

How can I achieve this?*

Idea

During night when I slept my brain worked independently on this problem and in the morning he had prepared a possible solution for it.

What came out is the following:

• We create an UDF which allows an application to write to the MySQL error log.
  See my previous article about this
  [ 2
  ].
• We specify in a simple SQL query how the string should look which we want to write to the MySQL error log file.
• We use the init_connect
  [ 3
  ] hook (= logon trigger) which MySQL provides to log the information to the error log.

How to solve it?

The UDF can be taken from
[ 4
]. Be not confused by the version number. It just worked with MySQL 5.1.42. Load the UDF according to the article into the MySQL database. Follow the little example …

[http://www.blogger.com/profile/05445777885744635565 Jo] said…

I wrote an interesting UDF you may be interested. JsMap - running JavaScript in MySQL

Today a customer with corrupted data files showed up. When we enquired a bit more he told us that he had a broken I/O controller. This is one of the worst things which can happen to you!

The reason is the following: When a I/O controller starts to die it often does not happen immediately. The controller dies slowly producing more and more corrupt data. When you just write data without checking or reading them it can take days or even weeks until you discover the problem.

But the nasty thing is, that even your backup is infected with the corrupted data. In worst case corruption started long before your oldest still existing backup was made.

Fortunately DBMS are a bit sensitive related to data corruptions and start to complain pretty early. So please consider warning or error messages about data corruption as serious and try to find the problem immediately and solve it!

What can we do against spreading data corruption?

• Monitor logs (syslog, database error log, application log, etc.).
• Ideally do physical AND …

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

Hello kabel,

Thanks for the flowers!

Anyhow, logical backups with such an amount of data is nearly impossible to restore (in a timely manner)!

But if you rely on physical backup methods you do not get rid of the corruption. So one is trapped in this situation. :-(

Do you have an idea why you got the corruptions? Playing around with DRBD or anything similar on file system level? Or just upgraded to 5.1? How does the corruption manifests?

Regards, Oli

[http://www.blogger.com/profile/08993510241119917712 kabel] said…

Wow. This post could not have been more timely. I have a (legacy) server with a large (600G) InnoDB tablespace that is slightly corrupted. A new machine has been rotated into production while I clean up this monster, but you just saved me a lot of heartache with the fatal lock timeout catch. It’s probably going to end up being a incremental table dump and restore.

Thanks!

[http://www.blogger.com/profile/17059197203923199652 Oliver] said…

I have set up almost identical scenarios on top of Xen, and I found performance to be quite acceptable.

I’ve also implemented the same thing with Oracle 10g XE (which was a little adventure trying to “staple on” HA to a product that does not support it in any way, and is as horribly installed as Oracle), and with Microsoft SQL Server running within Windows 2008 servers which were also VMs on Xen with DRBD as the backing storage.

Windows is a special case though as it is hard to get 2008 working on Xen with good PV drivers. Nonetheless, anything is possible.

Neutral and vendor independent services
for MariaDB, Galera Cluster and MySQL!

Services for MariaDB, Galera and MySQL

We offer the following services:

• Neutral and vendor independent consulting for MariaDB, Galera Cluster, MySQL and Percona Server.
• Enterprise support subscriptions for MariaDB, Galera Cluster and MySQL.
• MariaDB, Galera and MySQL training for database administrators (DBAs) and developers.
• With our remote DBA services we help you to operate your business-critical MariaDB and MySQL databases.
• Our software products: OpsCenter, Performance Monitor, Backup/Recovery Manager and myEnv support you in operating your infrastructure.

For further information please ge in contact with us…

Planned MariaDB, Galera and MySQL training courses

Further information about our training programme can be found under MariaDB, Galera and MySQL training.

Recently we had a case where a customer got some corrupted blocks in his InnoDB tables. His largest tables where quite big, about 30 to 100 Gbyte. Why he got this corrupted blocks we did not find out yet (disk broken?).

When you have corrupted blocks in InnoDB, it is mandatory to get rid of them again. Otherwise your database can crash suddenly.

If you are lucky only “normal” tables are concerned. So you can dump, drop, recreate and load them again as described in the InnoDB recovery procedure in the MySQL documentation [ 1 ].

If you are not so lucky you have to recreate your complete database or go back to an old backup and do a restore with a Point-in-Time-Recovery (PITR).

To find out if some tables are corrupted MySQL provides 2 tools: The innochecksum utility [ 2 ] and the mysqlcheck utility [ 3 ] or you can use the CHECK TABLE command manually (which is used by mysqlcheck).

I wanted to know how CHECK TABLE works in detail. So I looked first in the MySQL documentation [ 4 ]. But unfortunately …

FromDual offers trainings for MySQL, MySQL Enterprise, Galera Cluster, MariaDB or Percona Server.

We offer our MySQL, Galera Cluster and MariaDB trainings together with well-respected training organizations: The Linuxhotel in Essen (Germany), the Heinlein Academy in Berlin (Germany) and GFU Cyrus AG in Cologne (Germany).

If you prefer a MySQL, Galera Cluster or MariaDB in-house training at your location and if you provide the infrastructure we are happy to train you on-site either in German or English as well.

What are our customers saying about our MySQL trainings

Training modules

An overview of our MariaDB, Galera Cluster and MySQL training modules you can find here.

Booking

You can also book the trainings on-line at the class schedule. For in-house trainings please contact us directly.

Contact

If you need more information …

[http://www.blogger.com/profile/04408844340588067447 Charlie] said…

You should expect stellar performance, actually! DRBD, when configured well, has less than a 10% overhead.

When scaling to > 2 nodes, it’s probably recommended to use iSCSI. A pair of redundant iSCSI serveres (replicating with DRBD, of course) can serve 10+ VMware hosts if they have a few bonded gigabit NICs.

Or an active-active failover cluster à la VMware.

Today I have learned about a totally crazy/cool looking architecture where the expensive VMware ESX server was replace by a free/cheap VMware Workstation version in combination with DRBD.

Basically DRBD we name the poor man’s SAN and that is exactly what this customer is doing. He replaced the SAN with DRBD and now he can easily move one VMware instance to the other host. Possibly it is not that flexible and powerful as an ESX Server but also not so expensive…

The architecture looks as follows:

According to this customer it works stable on about a dozed of installations and they have not experienced any troubles during the failovers.

Please let me know your experience, thoughts or concerns with this architecture…

PS: When you consider such an architecture do not expect a very good performance!

According to different sources from the web the decision about the Oracle - Sun merger has been approved by the European commission soon. So at least in the West it is clear what is going on. Let us see what the East decides… [ 1 ], [ 2 ].

Oracles arch-enemy Microsoft has already brought its weapons in position against the target with its: Microsoft offers Oracle-phobes MySQL migration tool" [ 3 ], [ 4 ]. So far so good. Nothing new, nothing special.

What made me a bit edgy was the following Oracle blog series about their Oracle Warehouse Builder (OWB):

• OWB 11gR2 – MySQL Open Connectivity [ 5 ]
• OWB 11gR2 – MySQL Bulk Extract [ 6 ]

OWB seems to be a great tool to move data around from different sources, to mix them and to extract some useful results.

It looks like with the new 11gR2 release there "… were significant changes to mapping to support native heterogeneous connectivity to systems …".

What interests me more is what is MySQL related about it. In the second part of …

[http://www.blogger.com/profile/13094362409916524291 Antony] said…

Alas, the new log/audit framework I had developed for MySQL 6.0 would happily log to multiple targets. I guess I should find the time to extract the code from the defunct 6.0 repository and contribute it to MariaDB.

There are 2 different possible situations you can face when you have to deal with MySQL and syslog:

• MySQL is used as back-end for syslog to store the logging information.
  [ 6
  ]
• MySQL itself should report to the syslog.

In this blog article we look at the second situation: How can you make MySQL reporting to the syslog.

Since the version 5.1.20 MySQL is capable to log to the syslog
[ 1
],
[ 2
]. This is done by the MySQL angel process mysqld_safe.

You can enable the syslog when you add the syslog parameter to the MySQL configuration file (my.cnf) in the mysqld_safe section:

[mysqld_safe]
syslog

Currently MySQL is not capable to log to more than one logging facility at the same time. So you have to decide if you want to log either to the error log or to the syslog.

If you specify both, syslog and error-log, at the same time you will receive an error message if you start mysqld like this:

bin/mysqld_safe --defaults-file=/etc/my.cnf

But I assume that most of the MySQL users are using some kind of start/stop …