MySQL in a Nutshell [13]
The first two statements here delete any anonymous users from the user and db tables in the database called mysql—that’s where the privileges or grant tables are stored. The last line resets the server privileges to reflect these changes.
The next step regarding users is to set up at least one user for general use. It’s best not to use the root user for general database management. When you set up a new user, you should consider which privileges to allow her. If you want to set up a user who can view only data, you should enter something like the following from the mysql client:
GRANT SELECT ON *.* TO 'kerry'@'localhost' IDENTIFIED BY 'beck123';
In this line, the user is kerry from the localhost and her password is beck123. If you want to give a user more than viewing privileges, you should add additional privileges to the SELECT privilege, separated by commas. To give a user all privileges, replace SELECT with ALL. Here’s another example using the ALL flag:
GRANT ALL ON db1.* TO 'kerry'@'localhost' IDENTIFIED BY 'beck123';
In this example, the user kerry has all basic privileges, but only for the db1 database and only when logged in from the localhost, not remotely. This statement adds the user kerry to the table user in the mysql database, if there is already a row for her in it, but with no privileges. It will also add a row to the db table in the mysql database indicating that kerry has all privileges for the db1 database. See the explanation of GRANT in Chapter 4 for more options.
If you have any existing MySQL datafiles from another system, you can copy the actual files to the directory where MySQL data is stored on your server—but this is not a recommended method. If you do this, be sure to change the ownership of the files to the mysql user and mysql group with the chown system command after you copy them to the appropriate directory. If your existing datafiles are dump files created by the mysqldump utility, see the explanation regarding that utility in Chapter 16. If your data needs to be converted from a text file, see the explanation of the LOAD DATA INFILE statement in Chapter 6. You probably should also check the online documentation (http://dev.mysql.com/doc/mysql/en/Upgrade.html) on upgrading from a previous version to a current one, especially if you are migrating across major versions. If you have existing data, always upgrade one release at a time. Don’t skip any or you may have problems with tables, passwords, or any applications you’ve developed.
With the MySQL installation software downloaded and installed and all of the binary files and data in their places and properly set, MySQL is now ready to use. For an introduction to using MySQL, see the next chapter.
Chapter 3. MySQL Basics
Although the bulk of this new edition of MySQL in a Nutshell contains reference information, which you can read in small segments as needed, this chapter presents a basic MySQL tutorial. It explains how to log in to the MySQL server through the mysql client, create a database, create tables within a database, and enter and manipulate data in tables.
This tutorial does not cover MySQL in depth. Instead, it’s more of a sampler; it’s meant to show you what’s possible and to get you thinking about how to approach tasks in MySQL.
The mysql Client
There are various methods of interacting with the MySQL server to develop or work with a MySQL database. The most basic interface that you can use is the mysql client. With it, you can interact with the server from either the command line or within an interface environment.
If MySQL was installed properly on your server, mysql should be available for use. If not, see Chapter 2. On Unix-based systems, you can type whereis mysql. Windows, Macintosh, and other GUI-type systems have a program location utility for finding a program. If you used the default installation method, the mysql program probably resides at /usr/local/mysql/bin/mysql. On Unix systems, if /usr/local/mysql/bin/ is in your default path (the PATH environment variable), you can specify mysql without