Unix Advanced [Electronic resources] نسخه متنی

Administering MySQL

In day-to-day operations, a database server can usually be ignored. The server will be running, handling requests, without intervention.

However, the administrator still needs to create (or delete) users, change their passwords, modify permissions, and make regular backups. (Remember, "users" here are database users, and your database user ID might have no relation to the user ID you use to log in to the system.)

To create a user

Creating new users for your database can be something you do rarely (maybe all of your users access the database with the same user ID) or whenever you need to give someone else access to the database.

GRANT priv ON *.* TO 'user'@'host'  IDENTIFIED BY 'password';

You can use ALL PRIVILEGES for priv to grant all privileges, or you can specify one or more of the following:

• ALTER

• CREATE

• CREATE TEMPORARY TABLES

• DELETE

• DROP

• EXECUTE

• FILE

• GRANT

• INDEX

• INSERT

• LOCK TABLES

• PROCESS

• REFERENCES

• RELOAD

• REPLICATION CLIENT

• REPLICATION SLAVE

• SELECT

• SHOW DATABASES

• SHUTDOWN

• SUPER

• UPDATE

Please refer to an SQL manual for details.

You can also specify a database.table instead of *.* if the user has access only to specific databases and/or tables.

Tip

• You can also use the GRANT statement to give additional privileges to a user. Use the REVOKE statement to remove privileges.

To change a user's password

For as long as we've had passwords, users have been forgetting them. As the administrator, you're responsible for dealing with this problem.

To delete a user

Sometimes users need to have their database accounts removed, which is a little more work than creating their accounts.

Tips

• If you have MySQL 4.1.1 or later installed, you can combine steps 5 and 6 into one DROP USER command:

  
  DROP USER 'user'@'localhost'
  

• Although Fedora Core ships with MySQL 3.23.58, you can install newer versions by downloading RPMs from MySQL (www.mysql.com).

To create a database

You can create databases from the command line or with the standard SQL CREATE statement. Although SQL commands are uppercase, the <code>mysqladmin</code> command uses lowercase arguments.

Code listing 10.5. Dropping a database with the mysqladmin command.

bender:~ chrish$ mysqladmin -u root -p drop cd
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the 'cd' database [y/N] y
Database "cd" dropped

• mysqladmin -u user -p create name

  Create an empty database with the specified name. You will be prompted for user's password (because of the -p option), and user must have the CREATE privilege.

To drop a database

You can delete databases from the command line or with the standard SQL DROP statement.

• mysqladmin -u user -p drop name

  Drop the database (Code Listing 10.5) with the specified name. You will be prompted for user's password (because of the -p option), and user must have the DROP privilege.

  You will also be warned about the dangers of dropping databases, and you'll be asked if you really want to drop the database. Press Y, and then press Enter to continue with the drop.

To dump a database

To properly back up MySQL databases, you'll need to dump the data in a format that can be restored later.

• 
  mysqldump -u user -p --all-databases
   > backup_file
  

  Log in to the database as user, then dump all of the databases (and all of the tables in each database, and all of the rows in each table) to backup_file.

To restore a database

Because the mysqldump command dumps the database as a series of SQL commands, you can use the mysql client to restore your database.

• mysql -u 'user' -p < backup_file

  Restore the databases and tables in backup_file after logging in as user.

To shut down MySQL

In addition to the usual operating-system techniques for shutting down a service, MySQL's mysqladmin program can gracefully shut down the server:

• mysqladmin -u user -p shutdown

  Use the mysqladmin shutdown command to shut down the server, using the specified user ID.

  The user must have the SHUTDOWN privilege on the server. You will be prompted for user's password courtesy of the -p option.