Archive for the ‘administration’ Category

Speaking at the O’Reilly MySQL Conference & Expo: "A look into a MySQL DBA’s toolchest"

Март 9th, 2010


O'Reilly MySQL Conference & Expo 2010I'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
 

Posted in Linux, OSS, administration, community, conference, event, mysql, tools, utility

Комментарии отключены

 

Purging binary logs.

Январь 13th, 2010
Being a MySQL DBA , one faces a common issue in replication environment -> Disk space issue on master, since the number of binary logs have increased.
Now, one of the solution to this would be using expire_logs_days parameter in your mysql config file.
But what if, the slave is lagging by few hours or if the slave is broken since few days and the binary logs are removed due to the parameter set. Whenever the salve comes up, it will go bonkers, knowing that the binary log where it last stopped no more exists.

I faced this issue a couple of times until I decided to automate it using a script. Herewith I am attaching the URL to my python script which can run regularly in cron.
Features :
  • Checks the slaves connected to the master (I have limit it to 3 for now.)
  • Checks the last binary log file which is being used by the slave.
  • All the binary logs until the last bin log used by slave are purged.
  • If a slave is not connected, purging is aborted, so that important bin logs are not purged.
You can download this script from here .

Tips and Warnings to use this script
  • Test a couple of times, different test cases, before using this script on critical or production databases.
  • It is advisable to take backup of data before running this script.
  • You can send the output of this script to syslog or to different mail addresses.
  • You can embed into you alerting system, so that whenever there is a disk space warning on the machine, this scrip is fired.

PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in Python, Replication, administration, automate, binary logs, purge

Комментарии отключены

 

Use MySQL? You need Maatkit

Октябрь 23rd, 2009

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
 

Posted in Databases, administration, mysql, tools, utils

Комментарии отключены

 

MySQL related bookmark collection

Сентябрь 17th, 2009
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
 

Posted in Beginner, Cluster, Databases, HA, InnoDB, administration, benchmarking, bookmarks, configuration, general knowledge, monitoring, mysql, security, slow queries

Комментарии отключены

 

MySQL related bookmark collection

Сентябрь 17th, 2009
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
 

Posted in Beginner, Cluster, Databases, HA, Information, InnoDB, administration, benchmarking, collection, configuration, monitoring, mysql, performance analysis, security

Комментарии отключены

 

Securich – 0.1.4

Август 10th, 2009
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
 

Posted in Beginner, administration, grant, group, groups, mysql, password complexity, password expiry, revoke, role, roles, securich, security, user, users

Комментарии отключены