Every dump file created using the mysqldump command includes a clause named DEFINER. This clause cannot be excluded from the dump as of now, and hence whenever you try to restore the dumps on a remote database server or database server you would get an error referring to DEFINERS.

That is because DEFINERS have the information of the user and his IP, who has created or executed the SP/View/function from his login, hence it is not necessary for the user to have access over all the database servers.

You can remove the DEFINERS from the mysqldump file manually, however you would not want to do it by looking for DEFINERS on each line and then removing it. You need some script or a command to do this task for you.

Follow the below steps to achieving a dump file without DEFINERS,

Firstly create a mysql dump file,

>mysqldump –u root –p mydatabase>mydatabase.sql

I just got word that a talk about iSCSI High Availability with Pacemaker and DRBD, which I submitted for LinuxTag 2010, has been accepted by the selection committee.

The presentation is entitled Storage Done Right: Building a Resilient, Distributed, Highly Available Open Source iSCSI SAN and I will talk in the Storage track, in English. Questions in German, of course, won’t be a problem.

As the conference organizers have asked speakers not to publicize the temporary schedule (for obvious reasons — hey it’s temporary), I can’t give the exact time and location yet. But if you want to hear about how you can replace your six-figure SAN with something much more open, much less locked-in and much less expensive, plan a trip to Berlin in early …

After spending the morning looking over photographs with the amazingly talented Julian Cash, I drove through the pouring rain from San Francisco to the SJC airport, to pick up Brian Aker. From there, to the convention center, where I linked up with Monty Taylor, Jay Pipes, Stewart Smith, and a number of other people involved in Drizzle and Memcached. And from there, we all headed over to Sarah Sproehnle's place for a potluck party.

So much of the history and development of technology is based on a foundation of personal relationships. The people working on stuff get to know each other, and form friendships outside of just the work, and they introduce each other to their other friends, and from that, connections and cross-fertilization of ideas happen.

This is especially true for open source software and all the aspects of internet technology. The real but mostly …

I had a QA boffin email me with a question around the validity of data for a specific row in a development slave. Heres how I checked for him.

I set the pager output to md5sum. Heres an example:

Slave:
mysql> pager md5sum -
PAGER set to 'md5sum -'
mysql> select * from Residential where PropertyNumber = 106360678\G
0b07947002b59de27b6979fdcb57905a -
1 row in set (0.00 sec)


Master:
mysql> pager md5sum -
PAGER set to 'md5sum -'
mysql> select * from Residential where PropertyNumber = 106360678\G
1c6ac79d3fb2fd994ceb97406e9f2b1a -
1 row in set (0.00 sec)


Now onto why the slave is out of sync....

I spent most of the day traveling to Santa Clara, long-time home of the MySQL Conference. It took a while to get there, and I was denied a window seat at the very last minute but the flights weren't bad. I also got to try out in-flight WiFi which is pretty sweet to say the least, if a bit cramped. I spent a good chunk of time updating documentation and, yes, playing silly farm games. Landing in San Jose was a bit bumpy. Kind of a fun ride, really. Likewise the cab-ride was great. I really like talkative cab drivers, particularly when they seem genuinely interested. We even talked about MySQL, what the Oracle acquisition means, among other things.

But here I am chillin' in the hotel basically waiting until morning...and maybe playing more farm games (Damn you, South Park!)...by the way, the margherita pizza is delicious for those staying at the Hyatt. Yum!

It’s like my own little twitter. I don’t think I will be publishing much from this but it’s great for creating stub posts.

When replication runs out of sync first question you often ask is if someone could be writing to the slave. Of course there is read_only setting which is good to set in the slave but it is not set always and also users with SUPER privilege bypass it.

Looking into binary log is obvious choice - this is a good reason to have binary log on the slave if you do not need it for anything else. By default MySQL will only write statements which come to the server directly (not via replication thread) so you will know offender at once.

In many cases however log_slave_updates is enabled which makes slave to write all updates to binary logs - the ones executed directly on the host as well as coming through replication thread. There is however a way to know which is which - based on server_id.
Here is snippet from Slave binary log which has updates one on Master and another on Slave directly:

…

If you’re trying to install the MySql Workbench Doctrine Plugin for Mac and don’t know where your %APPDATA% directory is, here’s the path:

%YOUR HOME DIRECTORY%/Library/Application Support/MySQL/Workbench/modules/DoctrineExport.grt.lua

– or as in my case on my laptop –

/Users/robert/Library/Application Support/MySQL/Workbench/modules/DoctrineExport.grt.lua

Once your drop in the DoctrineExport.grt.lua script, restart Workbench and you’re ready to go.

‘Tis the day before the O’Reilly MySQL Conference & Expo 2010. You can still register onsite.

This one is long, and is divided into: Keynote Additions, Open Space, The Tweetup, and Videos/Live streaming of keynotes.

Keynote Additions
The earlier lineup was already excellent, and now we’ve filled it up more. In particular, let me draw attention to:

1. Tuesday, 10.00-10.20am: MySQL at Facebook – Mark …

Hello from Santa Clara, California! I've arrived here yesterday and am staying with Giuseppe and Jan in the same hotel. Currently, the weather isn't that nice and we're all busy making preparations for the upcoming MySQL conference next week.

I'm fiddling with my slide deck for my talk about "A look into a MySQL DBA's toolchest", realizing that there is such a wealth of great tools that it's unlikely to cover them all in a 45-minute session. But I hope it will give the audience some inspiration about what tools to take a closer look at!

On a related note, I've just reconfigured …