Whether you’re completely new to MySQL databases or someone who knows it well, Learning MySQL will really help you put structure around your knowledge while teaching you a number of nice to have things about MySQL. Covering a range of topics, this book will help you understand more about MySQL installation and performance tuning, provide detailed instructions on automating simple backups and restores, all while keeping you concscious of keeping your installation or web application secure.
Installing MySQL on Linux, Windows and OSX
The book has an overwhelming amount of detail describing various ways of installing MySQL on a Unix-like system of your choice. For starters, multiple Linux distros are covered with exact commands to be used. Whether you’re a Debian fan or a RedHat like OS user – it will have detailed instructions on how to update your repos and get MySQL installed.
Windows install instructions are also provided, and even if you are a proud owner of a Mac system you’ll find commands to install MySQL quickly and easily.
There is a description of the contents of MySQL directory and even description of how file storage differs for MyISAM and InnoDB storage engines.
A description of various installation screnarios and post-install actions is very useful because it explains that installing from sources is slightly different than deploying MySQL from packages. Those of you who will use MySQL long enough to warrant an upgrade will find instructions on updating the database software to its most up-to-date version.
The same section of the book has a good reference on MySQL config file options – all the parameters are provided with a brief set of instructions on how and when they should be applied.
There is definitely enough information for you to get you started – that is, to have MySQL installed and ready to be used.
Basic and advanced querying using SQL
There is enough of a database theory given in the book to provide a solid introuduction into basic database design principles. Good examples are provided on how NOT to create tables and storage data, explaining how each scenario can be improved with just a few tweaks. Entities and relationships are covered in good detail with complex topics like JOINs well covered with good examples.
There are many examples for data manipulation – inserting and updating date and creating/updating tables.
I really enjoyed instructions on using CSV files – for both import and export. It’s a common enough task frequently requested by users so knowing best ways to use it is certainly a worthy skill.
Finally, more advanced topics are covered in a separate chapter, this is where data aggregation, advanced joins and nested queries are explained. User variables get mentioned and basic idea behind transactions and locking is given.
User management and security in MySQL
I quited liked this section of the book, mainly because it not only explained how privileges work but also hinted at a privileges-related security improvements you can easily make. I also learned a few new commands which make life a lot easier cause you run them instead of logging into MySQL with a default client and typing SQL queries.
You will learn commands for showing privileges and explore various ways of controlling access given to a specific user. Passwords management is explained very well and shown with many examples.
An overview of the default MySQL installation completes this part of the book with a list of security aspects that you should particularly be mindful of.
MySQL backups and recovery
If you are looking for a quick guide into backing up and restoring your MySQL databases quickly, you’re covered: there’s a whole chapter in the book talking about exactly that. Apart from simple use of mysqldump and a few tips on command line parameters, this section provides a good deal of information about binary logs and commands for their management and analysis. Of course, instructions on using binary logs for point-in-time recovery are provided.
I was impressed with the depth of this section in the book, because (similar to the installation chapter) it provided instructions for a complete solution rather just the element of initiating a backup or a restore. You’ll find instructions in scheduling your backups and restores, and scenarios explain not only typical situations but trickier ones as well – like checking/repairing corrupted tables or even restoring (actually, re-creating) corrupted GRANT tables (the privileges management ones).
There is no mention of database replication, but that’s probably to keep things simple. Those of you familiar with replication will know that although it solves quite a few requirements for backup and restore, it’s really more a high availability solution than a proper backup. If you get your dataset corrupted due to an error in your software code, replication won’t be of much use as it will simply replicate your changes to all of your MySQL servers.
Tuning MySQL for maximum performance
Another immediately applicable section of the book. Starting with a review of the configurable and tunable parameters of your MySQL options file, you will be shown techniques for analysing and improving performance of your database solution. Both instructions for improving the performance of your MySQL server and the efficiency of your SQL code are provided.
I quite liked the InnoDB section which lists main features and explains when and why you may want to use this database storage engine.
There is also a good introduction into working with slow queries, particularly the part of defining of what exactly should be treated as a slow query.
Throughout the book there are mentions of the EXPLAIN command so you will get comfortable enough with assessing the query-level performance of your database.
Using MySQL in software development
Coming from PHP background of the LAMP fame, I quite enjoyed reading simple and easy to follow instructions on getting started – it certainly added structure to my knowledge. There are mentions of both MySQL and MySQLi interfaces and even explanation of their main differences.
Perl is introduced with DBI framework in mind, and provided examples cover the complete set of database management operations, including more complex topics like answer sets.
Session management and even implementation of DB-backed user logins are explained along with lots of smaller things which you would likely find useful in your web app development.
Full source code of a web app is given at the end of the book so if your style is to skip to that part and just tinker with the code and LAMP setup until you get it working – this section is for you.
Summary for the Learning MySQL book
Learning MySQL is a great introduction for the highly popular MySQL database server. If you have some knowledge and simply want to add structure to it – you will like the book. If you are quite new to MySQL – you will like it a lot.