So You've Installed MySQL. Now What?
The installation instructions were scrutinized, the latest distribution was downloaded, coffee was brewed and drank and brewed again. The familiar configure , make and make install were wielded to once again produce another beautifully compiled application. Nods were exchanged, pats on the back traded, frothy capuccino toasts are proposed in succession. Yes, there is reason to celebrate in the office today, as the MySQL database server has been successfully installed.
You lounge back in your deskchair, surrounded by colleagues hailing the wisdom of you, the newly-christened MySQL administrator. If they only knew the pain and anguish swirling around in your mind right now, as you ponder the question, "So now what?".
The purpose of this tutorial is to acquaint new MySQL users with several of the key aspects of this wonderful database server. Issues regarding general server functionality, security, user and privilege administration, working with databases and tables, and data backups will all be introduced to some degree. While the reader will likely find much of this material easy to understand, keep in mind that these concepts lay much of the foundation for efficiently and properly working with the MySQL database server, in addition to implementing more complicated aspects which will be discussed in later tutorials. Therefore it is suggested that the reader take the time to not only read the tutorial, but also to actually follow along with the steps described herein, experimenting with his own MySQL installation.
The MySQL Configuration File: my.cnf
It's very likely that the first task the administrator will want to undertake is proper configuration of MySQL's configuration file. This file, entitled my.cnf , stores default startup options for both the server and for clients. Correct configuration of this file can go a long way towards optimizing MySQL, as various memory buffer settings and other valuable options can be set here.
Interestingly, the scope of this file can be set according to its location. The settings will be considered global to all MySQL servers if stored in /etc/my.cnf . It will be global to a specific server if located in the directory where the MySQL databases are stored ( /usr/local/mysql/data for a binary installation, or /usr/local/var for a source installation). Finally, its scope could be limited to a specific user if located in the home directory of the MySQL user ( ~/.my.cnf ). Keep in mind that even if MySQL does locate a my.cnf file in /etc/my.cnf (global to all MySQL servers on that machine), it will continue its search for a server-specific file, and then a user-specific file. You can think of the final configuration settings as being the result of the /etc/my.cnf, mysql-data-dir/my.cnf, and ~/.my.cnf files.
In order to aid administrator's in the proper configuration of this file, the MySQL developers have included four sample my.cnf files within the distribution. Their names are my-huge.cnf.sh, my-large.cnf.sh, my-medium.cnf.sh, and my-small.cnf.sh, and each denotes recommended configuration settings in accordance with system resource availability.
Further Reading
The MySQL Privilege Tables
Before delving into the many examples that consititute this tutorial, a brief introduction of one of the most important (and most misunderstood!) aspects of the MySQL server is in order; that is the mechanism from which MySQL secures its data and integrity: The MySQL privilege tables. The MySQL privilege tables are responsible for authenticating user access to the MySQL server, and subsequently associating those users granted access with a set of privileges. This privilege set decides what a user is capable of doing while connected to the MySQL server, controlling the user's activities on a server-wide, database, tabular and even columnar level. For example, an administrator could grant a user only enough privileges to connect to one specific MySQL database, and restrict access to all others. Furthermore, that same user might be granted only certain privileges while connected to that database, selection, insertion, and modification privileges for example. Associated with only these three privileges, that user would be denied any attempt to delete data, since the user has not been granted the deletion privilege.
Although in-depth introduction to the privilege tables is out of the scope of this article, it is important that the reader understands the very important role these tables play in securing the MySQL server. While working through the examples presented throughout the remainder of this article, keep in mind that the privilege tables are playing a role in every single query and command that takes place, ensuring that the user executing these commands/queries has proper permissions for doing so. It is also strongly recommended that the reader takes some time to review the links listed below, as each describes in great detail the underlying mechanics of these tables.
Further Reading
Next page...
|
