In PHP 5.3 the magic quotes feature has been deprecated and with PHP 6 magic quotes will be removed completely. There are good reasons why magic quotes are bad, but how to live without?
I think the most promising solution is to (finally) start using MySQLi instead of PHP's classic MySQL extension. It's not as easy and straightforward to use as the classic one, but it's easier and probably more secure than to fight SQL injections by ones own hands.
Today (September 10, 13:00 UTC), Mark Leith will show how to customize the MySQL Enterprise Monitor: Customizing MySQL Enterprise Monitor. Mark is a MySQL support manager, and knows Enterprise Monitor by heart.
For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.) All MySQL University sessions are recorded, that is, slides and voice can be viewed as a Flash movie (.flv). You can find those …
[Read more]Today (September 10, 13:00 UTC), Mark Leith will show how to customize the MySQL Enterprise Monitor: Customizing MySQL Enterprise Monitor. Mark is a MySQL support manager, and knows Enterprise Monitor by heart.
For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.) All MySQL University sessions are recorded, that is, slides and voice can be viewed as a Flash movie (.flv). You can find those …
[Read more]I’ve always believed that the MySQL Information_schema.tables.table_rows figure for Innodb tables to be while approximate, approximately accurate.
Today I found that the figures varied on one table from 10x to 100x wrong.
Before performing an ALTER I always verify sizes for reference.
+---------+------------+--------+------------+------------+----------------+------------+------------+-----------+------------+ | table_s | table_name | engine | row_format | table_rows | avg_row_length | total_mb | data_mb | index_mb | today | +---------+------------+--------+------------+------------+----------------+------------+------------+-----------+------------+ | XXXXX | s_a | InnoDB | Compact | 208993 | 7475 | 1491.5312 | 1490.0156 | 1.5156 | 2009-09-09 |
mysql> alter table s_a modify col VARCHAR(255);
Query OK, 23471 rows affected (4 min 26.23 sec)
After
…[Read more]
MySQL provides a level of statistics for your INSERT, UPDATE, DELETE, REPLACE Data Manipulation Language (DML) commands using the STATUS output of various Com_ variables, however it is per server stats. I would like per table stats.
You can achieve this with tools such as MySQL Proxy and
mk-query-digest, however there is actually a very simple solution
that requires no additional tools.
The following 1 line Linux command (reformatted for ease of
reading) gave me exactly what I wanted, and it had ZERO impact on
the database.
$ mysqlbinlog /path/to/mysql-bin.000999 | \ grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter" | \ cut -c1-100 | tr '[A-Z]' '[a-z]' | \ sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" | \ sort | uniq -c | sort -nr 33389 update e_acc 17680 insert into r_b 17680 insert into e_rec 14332 insert into rcv_c 13543 update e_rec 10805 …[Read more]
I wanted to play with the new version of MyQuery that was just released. In order to use it, I needed to use a ssh tunneling tool. I have used various hacks in the past but I wanted something that was at least moderately well done.
A bit of googling uncovered Bitvise’s Tunnelier. It is a free for personal use tool that is designed just for tunneling. It seems to handle it with some aplomb. Ten minutes after I downloaded it, I was online with a remote server using MyQuery.
If you are using a Windows desktop and want to use some of the various graphical client programs that don’t include port forwarding this could very easily fill your needs. I just wish I had found it a long time ago.
What is Nagios? Nagios IMHO is the best Open Source monitoring
system out there. It supports hosts checks, a level to determine
on a host level if a box is considered "up". It supports service
check, a level to determine if a particular service such as mySQL
is up. It has features to log all events to a flat file or to a
DB. It can notify you when a service is in a warning state, error
state or unknown state.
For the purpose of this article, I am going to talk about
handling events such as a clearing up swap.
First, let us look at some configuration of Nagios. We are going
to define a command, then service acting on that command. Let us
assume that the nagios install is in /usr/local/nagios.
Therefore, in /usr/local/nagios/ a few configuration files are
key:
- /usr/local/nagios/etc/objects/commands.cfg - the command file
where the checks are …
MyQuery 3.1 is now ready for download from Sourceforge! There is a bunch of
cool features, but the theme was Convenience and DBA Features. On
top of this, UTF-8 support has also been added, not in a terribly
advanced way, but it should work for most needs.
Among the new features are:
In addition, there has been some bug fixes, memory leak fixes, and as usual (you are sitting down now, right), updated documentation. I still have a …
[Read more]Just a quick note to let the masses know that I will be hosting a MySQL University session tomorrow, based on the talk that I gave at the MySQL UC in April - Customizing MySQL Enterprise Monitor.
It will be at 14:00 UTC - so if you are at all interested in MEM, and want to know how to bend it towards your needs, then come along! I’ll see you there.
EDIT: OOOPS, it’s 13:00 UTC
I just wanted to tip my hat to the MySQL Enterprise Tools
Engineering team for another great release of the Enterprise
Monitor. Not to name names, but I want to give a special thanks
to a team that always over delivers on a collective commitment to
producing quality software. So, a mega thanks to:
Andy Bang, Sloan Childers, Darren Oldag, Eric Herman, Jan
Kneschke, Kay Roepke, Mark Matthews, Bill Weber, Diego Medina,
Marcos Palacios, Carsten "Pino" Segieth, Josh Sled, Keith
Russell, Mark Leith, Heidi Bergh-Hoff, and Gary Whizin (and also
welcome Michael Schuster!)
Yet another great job guys!
The new version, 2.1, was posted as GA early on Tuesday and it is
quite possibly the best release of the Enterprise Monitor to
date.
For those not familiar with the Enterprise Monitor, it is
included in a MySQL Enterprise …