Read the original article at Ten things to remember about MySQL backups

1. Use Hot Backups

Hot backups are an excellent way to backup MySQL.  They can run without blocking your application, and save tons on restore time.  Percona’s xtrabackup tool is a great way to do this.  We wrote a howto on using xtrabackup for hotbackups.

1. Use Logical Backups

Just because we love hot backups using xtrabackup doesn’t mean mysqldump isn’t useful.  Want to load data into Amazon RDS?  Want to isolate and load only one schema, or just one table?  All these great uses make mysqldump indispensable.  Use it in combination with periodic hot backups to give you more recovery options.

1. Replication isn’t a backup

While replication provides a great way to keep a hot copy of your production database, it’s not the same as a backup.  Why?  Operator error, that’s why!  People make mistakes, drop tables and database schemas that later need to be restored.  This can and will happen, so head off the disaster by doing real backups.

As an additional note, if you’re using replication, you surely want to perform regular checksums of your data.  These ensure that the primary and secondary do indeed contain the same data.

1. Firedrills & Restore Time

The only way to be sure your backup is complete is to test restoring everything.  Yes it’s a pain, but it will inevitably be a learning experience.  You’ll document the process to speed it up in future tests, you’ll learn how long recovery takes, and find additional pieces to the pie that must be kept in place.  Doing this in advance of d-day is

Different backups have different recovery times.  In the industry vernacular, your RTO or recovery time objective should inform what will work for you.  Although a mysqldump may take 30 minutes to complete, your restore of that data might take 8 hours or more.  That’s due in part to rebuilding all those indexes.  When you perform the dump one create index statement is formulated from the data dictionary, but on import the data must be sorted and organized to rebuild the index from scratch.  Percona’s mysqldump utility will capitalize on MySQL’s fast index rebuild for Innodb tables.  According to the Percona guys this can bring a big improvement in import time.  Yet another great reason to use the Percona distro!

1. Transaction Logs

If you want to be able to do point in time recovery, you’ll need all the binlog files as well.  These are being created all the time, while new transactions are completed in your database. If your last backup was last night at 3am, and you want to recovery today until 3pm, you’ll need all the binary logs from the intervening hours to apply to that backup.  This process is called point-in-time recovery, and can bring your database restore up to the current commited transactions.

1. Backup Config Files

Don’t forget that lonely /etc/my.cnf file.  That’s an important part of a backup if you’re rebuilding on a newly built server.  It may not need to be backed up with the same frequency, but should be included.

1. Stored Code & Grants

Stored procedures, triggers and functions are all stored in the mysql database schema.  If you are doing a restore of just one database schema, you may not have this, or it may make the restore more complicated.  So it can be a good idea to backup code separately.  mysqldump can do this with the –routines option.  Hot backups by their nature, will capture everything in the entire instance – that is all database schemas including the system ones.

Grants are another thing you may want to backup separately.  For the same reasons as stored code, grants are stored in the system tables.  Percona toolkit includes a nice tool for this called pt-show-grants.  We recommend running this periodically anyway, as it’ll give you some perspective on permissions granted in your database.  You’re reviewing those right?

1. Events & Cronjobs

MySQL allows the running of events inside the database.  SHOW EVENTS or SHOW EVENTS schema_name will display the events scheduled.

You may also have cronjobs enabled.  Use crontab -l to display those for specific users.  Be sure to check at least “mysql” and “root” users as well as other possible application users on the server.

1. Monitoring

Backups are a nit picky job, and often you don’t know if they’re complete until it’s time to restore.  That’s why we recommend firedrills above, and they’re very important.  You can also monitor the backups themselves.  Use an error log with mysqldump or xtrabackup, and check that logfile for new messages.  In addition you can check the size of the resulting backup file.  If it has changed measurably from the recent backup sizes, it may indicate problems.  Is your backup size 0, something serious is wrong.  Half the size of recent ones, it may have failed halfway through, or the filesystem filled up.

1. Security

This is often overlooked area, but may be a concern for some environments.  Is the data contained in your backup sensitive?  Consider where the backups are stored and retained for long term.  Reason who has access to those files, and make use of the least privileges rule.

For more articles like these go to iHeavy, Inc +1-212-533-6828

MySQL is the world’s most popular open-source database and platform for millions of web applications – it’s critical but cumbersome to monitor.

Monitis’ MySQL monitoring provides three key benefits:

Insight
» 246 potential monitoring variables
» 21 aggregated, percentage-based metrics
» Adjustable thresholds to separate real issues from false alarms

Control

» Monitor entire IT universe from one dashboard
» Quick diagnosis & root cause detection

Simplicity
» Cloud-based means no need to install, update or maintain it
» Leaves you time to focus on more important things

For a FREE trial, go to:

https://www.monitis.com/free_signup.jsp

New feature provides significantly faster insight and root cause analysis

SAN JOSE, Calif., February, 15, 2012 – Monitis, the leading cloud and web application monitoring software provider, today announces that it has added comprehensive MySQL database monitoring to its award-winning Application Performance Management & Monitoring platform. The robust Software-as-a-Service (SaaS) tool enables users to gain significantly faster insight when conducting root cause analysis.

The MySQL monitoring feature includes 246 monitoring variables and more than 21 different metrics to provide one of the easiest to use, yet comprehensive database monitoring tools available. It was first introduced into the free Monitor.Us platform back in June last year and has seen the code battle hardened by many hundred free users over the last 8 months.

Hovhannes Avoyan, founder and General Manager at Monitis commented “This feature makes it easy for systems administrators and web developers to measure and monitor the performance of their php environments such as Joomla and WordPress, from multiple locations around the world. It gives them a true picture of application performance from where ever in the world the user is. Because the Monitis MySQL monitor is part of the Monitis platform it provides an unprecedented holistic view of software, database and underlying hardware, giving pressured techs the ability to quickly and easily diagnose the root cause of slow application performance.”

The service uses a small Linux agent that can monitor inside an organization’s firewall and distributed systems. The Monitis agent connects to a local or remote database and collects a variety of health and performance metrics that are graphically displayed in real-time on a web-based dashboard. Users can also set up notifications (should their database go down or pass a certain level of access volume) based on client-defined thresholds and rules.

Some metrics tracked and reported on by Monitis — which can be set up in minutes — include:

• Database size
• Resource utilization
• Analysis of responsiveness/latency
• Analysis of throughput
• Database usage
• Scalability

“Monitis’ SaaS delivery and pay as you go pricing mean it’s easy to get going and has low set up and running costs. It puts the power of MySQL monitoring in the reach of techs who otherwise wouldn’t have had the time to set up or budget to afford such a comprehensive solution.” adds Avoyan

Monitis MySQL Monitoring provides much greater check-in frequency, as low as every 5 minutes, and the ability to check the performance of the database from multiple locations around the world.

About Monitis

Monitis is the leading provider of Cloud-based Application Performance Management & Monitoring solutions for System Admins and Web Developers. Over 80,000 users worldwide have chosen Monitis to increase uptime and user experience of their services and products.

Monitis’ core product offerings include website monitoring, website full page load testing, transaction monitoring, application and database monitoring, cloud resource monitoring, and server and internal network monitoring. What makes Monitis’ software different is how fast it is to deploy, its flexible pricing and feature-rich technology that provides a comprehensive single-pane view of on-premise and off-premise infrastructure and applications.

More information on Monitis MySQL Monitoring is available on our website.

Pluggable M3 (Monitis Monitor Manager) Framework

Who needs an introduction about M3? – Perhaps no one!
After gaining some reputation with M3, providing extra-easy integration of any monitor into Monitis it was time to take it to the next level.

Generally speaking, the work flow of M3 was described in detail in this article.

After some thought and design, we’ve decided it’d be best if M3 was pluggable. Pluggable in terms of being able to easily add execution and parsing plugins.
The interface and behavior of M3 stayed exactly the same, however now it is much easier to obtain data from any source and parse the data the way you want it.
Saying that, it was time to put the new design for a test. We tried to integrate the DBI support into M3.
Guess what – it was much easier than expected!

The general idea of DB integration with Monitis

DB (as in Database) integration with Monitis has shown to be invaluable. Being able to extract application related data from a DB and load it to Monitis means you can graph anything going on in your database related application. Monitoring your DB is an integral part of server monitoring in today’s world.

Be it maximum number of users at peak hours, gathering statistics about record usage or just checking if your DB is serving requests quickly enough – graphing performance and uptime. Your imagination is the limit – considering your application is DB related of course.

How it’s done – the technicalities beyond

The sharp-eyed among you would notice that M3 got beefed up with a directory named “Execution” and a directory named “Parsing”.
These directories hold the plugins M3 uses for each.

All that I had to do to add DBI support is to create a new execution plugin called “DBI.pm” and fill it up with code from the Monitis DBI module which already existed.

And that my friends, that, was easy. Mission accomplished – Pluggable M3 eases the job for us.

Another way to integrate MySQL

MySQL could have been integrated previously into M3 by executing mysql and feeding it with a query, having something similar to:

 <exectemplate>echo "select count(*) from mysql.user;" | mysql</exectemplate> 

This form of execution will invoke the ‘mysql’ executable, feeding it the above query. Execution of this will result in the response of the query being returned with some other useless output.

Please avoid this form of execution, as now DBI support is fully integrated into M3. In the next paragraph I’ll show how it should be done.

Lets fetch

While conveying ideas about M3, I decided to prefer to provide very simple examples of operation, however, I encourage you, the end user to let your imagination go wild, adding interesting counters which show how your application really perform and behave.

In this example we’ll just count the number of users in an installed MySQL database. An example anyone should be able to imitate and then scale to his own need.

We’ll examine the following example. I suggest opening the mentioned link in a new window while continuing the read.
Looking at this example it is quite straight-forward to understand. The query to execute is over there, together with some other parameters which are mandatory to connect to a database, such as the database name, user name, password, etc.

An explanation should be provided however for the odd db_statistics parameter. Those of you who have read the HTTP extraction article about M3 would have probably remembered the http_statistics parameter. Then, the db_statistics one does more or less the same, providing some meta counters for the query:

1. How long did the query take to process (in seconds)
2. The success/failure of the query (1 for success, 0 for failure)

I encourage to do the parsing of query results, or actually the lack-of-parsing of query results in a way that takes the whole output returns from the query, hence the regular expression of (.*).

If your query returns more than what you need, please tune your query better.
Test your execution with:

 # ./DryRun.pl dbi_sample.xml DB: 'root@DBI:mysql:mysql:localhost', Query: 'select count(*) from mysql.user;' at MonitisMonitorManager.pm line 223 Matched '(.*)'=>'6' in '6' at MonitisMonitorManager.pm line 237 OK This is a dry run, data for monitor 'Sample MySQL Monitor' was not really updated. at ./DryRun.pl line 18 

It should work. Then you can work your query to fit your own needs.

Next in line

We’re looking for some more new ideas to add as plugins for M3. If you have any idea about any plugin (execution or parsing plugin) that you would like to see in M3 – we want to hear you!

Integrating software into the powerful Monitis has never been easier, now that you have M3!

Monitis can monitor anything! Follow us also on Twitter and GitHub.

Pluggable M3 (Monitis Monitor Manager) Framework

Who needs an introduction about M3? – Perhaps no one!
After gaining some reputation with M3, providing extra-easy integration of any monitor into Monitis it was time to take it to the next level.

Generally speaking, the work flow of M3 was described in detail in this article.

After some thought and design, we’ve decided it’d be best if M3 was pluggable. Pluggable in terms of being able to easily add execution and parsing plugins.
The interface and behavior of M3 stayed exactly the same, however now it is much easier to obtain data from any source and parse the data the way you want it.
Saying that, it was time to put the new design for a test. We tried to integrate the DBI support into M3.
Guess what – it was much easier than expected!

The general idea of DB integration with Monitis

DB (as in Database) integration with Monitis has shown to be invaluable. Being able to extract application related data from a DB and load it to Monitis means you can graph anything going on in your database related application. Monitoring your DB is an integral part of server monitoring in today’s world.

Be it maximum number of users at peak hours, gathering statistics about record usage or just checking if your DB is serving requests quickly enough – graphing performance and uptime. Your imagination is the limit – considering your application is DB related of course.

How it’s done – the technicalities beyond

The sharp-eyed among you would notice that M3 got beefed up with a directory named “Execution” and a directory named “Parsing”.
These directories hold the plugins M3 uses for each.

All that I had to do to add DBI support is to create a new execution plugin called “DBI.pm” and fill it up with code from the Monitis DBI module which already existed.

And that my friends, that, was easy. Mission accomplished – Pluggable M3 eases the job for us.

Another way to integrate MySQL

MySQL could have been integrated previously into M3 by executing mysql and feeding it with a query, having something similar to:

 <exectemplate>echo "select count(*) from mysql.user;" | mysql</exectemplate> 

This form of execution will invoke the ‘mysql’ executable, feeding it the above query. Execution of this will result in the response of the query being returned with some other useless output.

Please avoid this form of execution, as now DBI support is fully integrated into M3. In the next paragraph I’ll show how it should be done.

Lets fetch

While conveying ideas about M3, I decided to prefer to provide very simple examples of operation, however, I encourage you, the end user to let your imagination go wild, adding interesting counters which show how your application really perform and behave.

In this example we’ll just count the number of users in an installed MySQL database. An example anyone should be able to imitate and then scale to his own need.

We’ll examine the following example. I suggest opening the mentioned link in a new window while continuing the read.
Looking at this example it is quite straight-forward to understand. The query to execute is over there, together with some other parameters which are mandatory to connect to a database, such as the database name, user name, password, etc.

An explanation should be provided however for the odd db_statistics parameter. Those of you who have read the HTTP extraction article about M3 would have probably remembered the http_statistics parameter. Then, the db_statistics one does more or less the same, providing some meta counters for the query:

1. How long did the query take to process (in seconds)
2. The success/failure of the query (1 for success, 0 for failure)

I encourage to do the parsing of query results, or actually the lack-of-parsing of query results in a way that takes the whole output returns from the query, hence the regular expression of (.*).

If your query returns more than what you need, please tune your query better.
Test your execution with:

 # ./DryRun.pl dbi_sample.xml DB: 'root@DBI:mysql:mysql:localhost', Query: 'select count(*) from mysql.user;' at MonitisMonitorManager.pm line 223 Matched '(.*)'=>'6' in '6' at MonitisMonitorManager.pm line 237 OK This is a dry run, data for monitor 'Sample MySQL Monitor' was not really updated. at ./DryRun.pl line 18 

It should work. Then you can work your query to fit your own needs.

Next in line

We’re looking for some more new ideas to add as plugins for M3. If you have any idea about any plugin (execution or parsing plugin) that you would like to see in M3 – we want to hear you!

Integrating software into the powerful Monitis has never been easier, now that you have M3!

Monitis can monitor anything! Follow us also on Twitter and GitHub.

I’ve  been getting more than my fair share of calls from recruiters of late. Even in this depressed economic climate where jobs are rarer than a cab at rush-hour, it’s heartening to know that tech engineers are in great demand. And it’s even more heartening to think that demand for MySQL DBAs has never been better.

My reckoning was confirmed by a Bloomberg news report about stalwart retailers suffering from a dearth of talented engineers. Bloomberg cited Target’s outage-prone e-commerce site as a symptom of, among other things the market’s shortage. One of the challenges old-timers like Target face is having to compete with Silicon Valley startups as a fulfilling and ultimately, financially rewarding place to work.

From the outside looking in, it's hard to say for sure why Target.com keeps crashing, but I can speculate on a few possible scenarios.

For one, the handoff from Amazon may have been less than smooth, lacking proper documentation and so forth. It could also be that the handoff went to less experienced DBAs or perhaps, those more versed in the legacy technologies of Oracle and much less in the free-wheeling open-source ones like MySQL. Other reasons could be failures in capacity planning, incomplete or incorrect systems integration, or simply misconfigurations in the load balancer, replication of database and memory settings.

If any of these scenarios had been true for Target, a sound experienced DBA and/or operations team attuned to scaling and disaster scenarios should have been able to anticipate these outages and mitigate their impact.  That is, if there were enough talented and experienced ones to go around.

From our vantage point, we think there’s room for more individuals to specialize in this area. What we do see are developers or Unix system administrators that include MySQL experience in their bag of skills but few who can actually manage a database eco-system.  Even in the Oracle space where there are a lot of career DBAs, many of them have moved over from the business side, so they lack certain computer science and engineering fundamentals and a pure science foundation.

Much of this boils down to universities not churning out enough engineers.  And the ones that do graduate are drawn to Startups; the coolest, smartest firms like Facebook and Google. If young college grads are gunning for the best job they can find, they're likely to shoot for the sexiest most cutting edge technologies.  In today's market that means programming jobs in Ruby on Rails or perhaps Node.js. Few would aspire to be in WebOps.

Not too sexy for Ops

If I were to really go out on a limb I might ask if you've ever heard of Dustin Moskovitz?  No?  Oh he's "the ops guy" from the original Facebook team and, with a net worth estimated at $3.5bln, the youngest billionaire in the world.  Did I imply that operations and database administration wasn't sexy?

Effective MySQL: Optimizing SQL Statements

by Ronald Bradford

No Nonsense, Readable, Practical, and Compact

I like that this book is small; 150 pages means you can carry it easily.  It's also very no nonsense.  It does not dig too deeply into theory unless it directly relates to your day-to-day needs.  And those needs probably cluster heavily around optimizing SQL queries, as those pesky developers are always breaking things

Jokes aside, this new book out on Oracle Press is a very readable volume. Bradford has drawn directly from real-world experience to give you the right bite size morsels you need in your day-to-day MySQL activities.

Highlights

Chapter one, The Five Minute DBA gives you the basic methodology if you don't already know it.  Enable the slow query log, analyze it, and use the explain facility.  Then index as appropriate, or eliminate queries if you can.

Chapter two digs a little deeper into the basics, introducing explain extended, table statistics and storage engines.  You'll also learn how to use show session & global status, as well as session & global variables.  You'll also have your first look at MySQL's data dictionary - INFORMATION_SCHEMA.

Chapter three is where it starts to get meaty.  You probably know that MySQL has b-tree indexes, but did you know that it has b+tree indexes, or hash indexes?

Chapter four digs into indexes further with single & multi-column indexes using them for sorting and joining.  You'll also find out about covering indexes which are multi-column matching the where clause, but also including columns needed in the SELECT predicate.  Do you have duplicate or unused indexes?  You'll learn why they matter to performance and how to eliminate them with tools like mk-duplicate-key-checker.

Chapter five continues along the same lines, with more coverage of indexes.  Learn to identify when you are using a covering index, fulfilling the entire query by only accessing the index.  You'll also learn about partial indexes, how they can reduce the size of index storage and retrieval while still getting your data for you.

Chapter six covers configuring the server itself, hitting on the system variables such as the innodb buffer pool (innodb_buffer_pool_size) and key buffer (key_buffer_size) as well as the query cache.  You'll also learn how to set the four main session memory settings - sort buffer (sort_buffer_size) and join buffer ( join_buffer_size) as well as the lesser known read buffers (read_buffer_size and read_rnd_buffer_size).

Chapter seven is all about the process of tuning and optimizing MySQL.  Rolling all the previous sections into marching orders, and prescriptive advice, he takes you through step by step how to apply the principles.  You'll get an introduction to mk-query-digest (though strangely without attribution to Baron Schwartz), the great maatkit tool for query analysis and aggregation, as well as the microsecond precision patch, which allows your mysql shell client to display more exact timing data.  For the patch he links back to an article on his own site which seems to be not found.  The author of the high precision mysql timer patch is Stewart Smith.

I personally got the most out of Chapter eight, full of self-described hidden performance tips.  From identifying unused or duplicate indexes, to replacing inefficient data types with better ones, why it's important to use NOT NULL where possible or how to store IP addresses efficiently, this chapter has a lot of goodies.  For those still struggling with SQL statement tuning, there are a few patterns that are described, offering advice on how to rewrite a subquery as an inner join,

What you might not know

• MySQL includes Oracle's index organized tables by a different name
• Too many indexes can dramatically impact INSERT & UPDATE performance
• Many DDL operations can be done online - see oak-online-alter-table (Shlomi Noach)
• Datatypes matter - use enum, int unsigned, timestamp & not null where possible
• Covering indexes are your friend, duplicate & unused indexes are not!
• A replication slave can have different storage engines or indexes from the master. These can support different uses - such as data warehousing or non-transactional requirements.
• While a_string LIKE '%end of my sentence.' won't use an index, you can index reverse_string, then use reverse_string LIKE REVERSE '%end of my sentence.' and MySQL will use this index.  You've simulated an advanced Oracle feature, reverse key indexes!

A few small gripes

If I were to add a few complaints it would be to say that some of the examples were rather simplistic.  In many cases tuning SQL is not as simple as just adding the right index.  For instance there was no good discussion of the dreaded "using temporary, using filesort" that we see a lot in MySQL explains when sorting has to be done, but will not fit in memory.  Or what about tmpdir=/dev/shm, how will that improve things?  What about UNION versus UNION ALL where appropriate.  Why does DISTINCT do a sort?

The book was also missing a discussion of triggers, stored procedures, when or if the query cache can cause problems and so forth.  Also the article link mentioned about chapter seven isn't the only missing link.  I followed links to optimizing sql  statements and it seems to go to a generic holding page.  Also the main link effectivemysql.com/book leads to an outline of an as yet unreleased title on Backup and Recovery.

All in all, well worth your money

However, other than these few gripes the book overall is a very welcome addition to the small family of MySQL books.  Get a copy quick before they're all gone!

Effective MySQL: Optimizing SQL Statements

by Ronald Bradford

No Nonsense, Readable, Practical, and Compact

I like that this book is small; 150 pages means you can carry it easily.  It's also very no nonsense.  It does not dig too deeply into theory unless it directly relates to your day-to-day needs.  And those needs probably cluster heavily around optimizing SQL queries, as those pesky developers are always breaking things

Jokes aside, this new book out on Oracle Press is a very readable volume. Bradford has drawn directly from real-world experience to give you the right bite size morsels you need in your day-to-day MySQL activities.

Highlights

Chapter one, The Five Minute DBA gives you the basic methodology if you don't already know it.  Enable the slow query log, analyze it, and use the explain facility.  Then index as appropriate, or eliminate queries if you can.

Chapter two digs a little deeper into the basics, introducing explain extended, table statistics and storage engines.  You'll also learn how to use show session & global status, as well as session & global variables.  You'll also have your first look at MySQL's data dictionary - INFORMATION_SCHEMA.

Chapter three is where it starts to get meaty.  You probably know that MySQL has b-tree indexes, but did you know that it has b+tree indexes, or hash indexes?

Chapter four digs into indexes further with single & multi-column indexes using them for sorting and joining.  You'll also find out about covering indexes which are multi-column matching the where clause, but also including columns needed in the SELECT predicate.  Do you have duplicate or unused indexes?  You'll learn why they matter to performance and how to eliminate them with tools like mk-duplicate-key-checker.

Chapter five continues along the same lines, with more coverage of indexes.  Learn to identify when you are using a covering index, fulfilling the entire query by only accessing the index.  You'll also learn about partial indexes, how they can reduce the size of index storage and retrieval while still getting your data for you.

Chapter six covers configuring the server itself, hitting on the system variables such as the innodb buffer pool (innodb_buffer_pool_size) and key buffer (key_buffer_size) as well as the query cache.  You'll also learn how to set the four main session memory settings - sort buffer (sort_buffer_size) and join buffer ( join_buffer_size) as well as the lesser known read buffers (read_buffer_size and read_rnd_buffer_size).

Chapter seven is all about the process of tuning and optimizing MySQL.  Rolling all the previous sections into marching orders, and prescriptive advice, he takes you through step by step how to apply the principles.  You'll get an introduction to mk-query-digest (though strangely without attribution to Baron Schwartz), the great maatkit tool for query analysis and aggregation, as well as the microsecond precision patch, which allows your mysql shell client to display more exact timing data.  For the patch he links back to an article on his own site which seems to be not found.  The author of the high precision mysql timer patch is Stewart Smith.

I personally got the most out of Chapter eight, full of self-described hidden performance tips.  From identifying unused or duplicate indexes, to replacing inefficient data types with better ones, why it's important to use NOT NULL where possible or how to store IP addresses efficiently, this chapter has a lot of goodies.  For those still struggling with SQL statement tuning, there are a few patterns that are described, offering advice on how to rewrite a subquery as an inner join,

What you might not know

• MySQL includes Oracle's index organized tables by a different name
• Too many indexes can dramatically impact INSERT & UPDATE performance
• Many DDL operations can be done online - see oak-online-alter-table (Shlomi Noach)
• Datatypes matter - use enum, int unsigned, timestamp & not null where possible
• Covering indexes are your friend, duplicate & unused indexes are not!
• A replication slave can have different storage engines or indexes from the master. These can support different uses - such as data warehousing or non-transactional requirements.
• While a_string LIKE '%end of my sentence.' won't use an index, you can index reverse_string, then use reverse_string LIKE REVERSE '%end of my sentence.' and MySQL will use this index.  You've simulated an advanced Oracle feature, reverse key indexes!

A few small gripes

If I were to add a few complaints it would be to say that some of the examples were rather simplistic.  In many cases tuning SQL is not as simple as just adding the right index.  For instance there was no good discussion of the dreaded "using temporary, using filesort" that we see a lot in MySQL explains when sorting has to be done, but will not fit in memory.  Or what about tmpdir=/dev/shm, how will that improve things?  What about UNION versus UNION ALL where appropriate.  Why does DISTINCT do a sort?

The book was also missing a discussion of triggers, stored procedures, when or if the query cache can cause problems and so forth.  Also the article link mentioned about chapter seven isn't the only missing link.  I followed links to optimizing sql  statements and it seems to go to a generic holding page.  Also the main link effectivemysql.com/book leads to an outline of an as yet unreleased title on Backup and Recovery.

All in all, well worth your money

However, other than these few gripes the book overall is a very welcome addition to the small family of MySQL books.  Get a copy quick before they're all gone!

Linux, Apache, MySQL and PHP — altogether they mean LAMP. I’m not talking about watts and bulbs.

And if you desire is for a comprehensive, robust server, your IT infrastructure has to include all of these systems.

Monitis has put together a checklist of 101 actions you can take to maximize security around LAMP.  Hopefully we’re shedding a little light around this issue for you to give you some new ideas on how to make administering your system easier — so that, in turn, you can focus on more strategic tasks. You can find previous posts about increasing security around Linux and Apache, but, in today’s post, we’ll offer tips on LAMP security around MySQL — a powerful open-source database.

MySQL security tips:

1. Grant minimal permissions

If a database user needs “read” access to two tables, you should not be giving that user full access to the entire database server. The problem is that novice database administrators will often grant full access to new MySQL users, either on a whole database or on the whole database server. Don’t make that mistake, read up on MySQL’s GRANT statements and how to use them.

2. Separate read/write users

Many web applications have distinct areas, some of which need only “read” access, while other areas might require “write” access to one or two tables, and yet other areas require write access to most of the database. Consider creating separate accounts for each area and walling the areas off. Also, double-check whether the payment processing code really needs full access, or whether INSERT access is sufficient. Use fine-grained security controls and keep up-to-date, relevant, documentation.

3. Use TLS

As with Apache, you should use SSL/TLS with MySQL as well. This may not be relevant if your database server is on the same host as your web server, but often enough it isn’t. Use “stunnel” to establish a secure connection between Apache and MySQL to prevent attackers from sniffing out your precious passwords.

4. Use syslog

Just like Apache, you should configure your MySQL server to send its logs to the syslog server. If you use a remote syslog daemon, check your setup: Make sure that the correct error levels are being logged, and that the log messages actually arrive on the other end. (See our blog
Apache and MySQL Logging with Syslog-ng)

5. Restrict user table access

The “user” table in MySQL is a rather precious table that only administrators should have access to. Disable access to the user table for all users except root — and any other administrative accounts. This applies to read and to write access, though, of course, you should be especially watchful with regards to write access.

6. Set a root password

As dumb as this sounds, the default MySQL installation often does not set a password for the “root” user. You wouldn’t leave your system root password empty, so you shouldn’t leave your MySQL root password empty either. This is a good chance to check that all the other accounts have passwords set as well.

7. Plain-text passwords

Don’t store plain-text passwords in your database, ever! The rule is as simple as that. In fact, don’t store plain-text passwords anywhere.

8. Disable external access

Hopefully at this point you’ve already configured iptables to disallow external access to your MySQL server. Just in case, you should tell MySQL not to accept connections from any hosts other than localhost. You might ask yourself what you should do if you need to connect externally? Remember, external connections should go through stunnel! To MySQL, it will look as if the connection is coming from localhost.

9. Run the MySQL server as a separate user

You should already be running Apache in a dedicated user account. Do the same with your MySQL server. Make up your own name and remember that non-default names are often good for security. You probably know this, but just in case: Don’t run your MySQL server as root, and don’t run it as “nobody”.

10. Database directory permissions

MySQL, as any other program, needs to store its data somewhere. Configuration is stored in /etc, of course, but where does the actual data go? This is installation-specific. The default location is /usr/local/mysql/data. Please make sure that this directory has very restrictive permissions, as it will include the “user” table. It will obviously also include your application data, which may or may not be sensitive.

11. my.cnf permissions

Allowing an attacker to modify your MySQL configuration is a big security issue. They could simply reset your MySQL root password and they’d get away with it.  Make sure my.cnf is only root-writable, and enforce this with a tripwire program.

12. Non-default user names

Like using a non-default SSH port, using non-default user names is only going to help your system’s security. As a minimal security measure, rename your MySQL root user to something other than “root”. For added security, consider naming your web application user something other than “www”, “php”, “web”, or “app”.

“test” user. Default user accounts are a real chore to remove, though if you remove the default “test” user account, you also get a lot of security in return. This “test” user is usually not protected with a password and can be a first entry point for an attacker. You can protect yourself from being low-hanging fruit.

13. Restrict “special” permissions

There are four “special” permissions that you have to watch out for: PROCESS, SUPER, SHUTDOWN, and FILE. The short version: Only “root” should have these permissions. The longer version is that they allow the calling user to shutdown your database server and change server variables, among other nasty stuff.

14. Disable LOAD DATA LOCAL INFILE

For speedy data loading, “LOAD DATA LOCAL INFILE” can be a real charm. You may want to enable it when you first deploy your database server. After that, you should disable this command, as it allows an attacker to read all local files that your MySQL server has access to.

15. Clean your history files

History files like “.mysql_history” often contain passwords from previous sessions. The same goes for your “.bash_history” if you’ve entered a MySQL account password on the command-line in the past. Check your history files and remove any passwords that you find and that shouldn’t be in there.

16. Backups

Yes, disaster recovery falls in the “information security” category. Backup your databases and automate the process. The backups are obvious, but how about the automation? You’ll find that you won’t be as inclined to forget to run an automated process, because it runs itself. This only goes so far, as you can’t fully automate exchanging tapes, but the further you automate the process, the more reliable it becomes. Oh, and don’t forget to test those backups!

17. Log everything

Enable logging for all connections and queries where you can. Logging all queries can be a real performance killer, so you have to make a trade-off. Yet, isn’t security always a matter of trade-offs. Log as much as you reasonably can and you’ll be thankful when someone does break in.

18. Database firewalls

Consider installing a database firewall to protect against SQL injection, but also against exploits against the database. Database firewall solutions are hard to find and often quite expensive, so you have to consider if this is worth it. You should definitely take a look.

Of course, another great way to boost security of your MySQL database is to employ a 24/7 monitoring service like Monitis. We operate whether your firewall is up or down, so there’s never any need to worry about downtime — at least on our part!

See also:

LAMP Security: 21 Tips for Apache

25 Linux Server Hardening Tips

Join me for a webinar where I discuss how the recent changes and trends in big data management effect the enterprise.  This event is sponsored by Red Rock and RockSolid.

Overview:

It is an exciting and interesting time to be involved in data. More change of influence has occurred in the database management in the last 18 months than has occurred in the last 18 years. New technologies such as NoSQL & Hadoop and radical redesigns of existing technologies, like NewSQL , will change dramatically how we manage data moving forward. 

These technologies bring with them possibilities both in terms of the scale of data retained but also in how this data can be utilized as an information asset. The ability to leverage Big Data to drive deep insights will become a key competitive advantage for many organisations in the future.

Join Tony Bain as he takes us through both the high level drivers for the changes in technology, how these are relevant to the enterprise and an overview of the possibilities a Big Data strategy can start to unlock.

https://redrockevents.webex.com/redrockevents/onstage/g.php?t=a&d=869100422