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.

1.

mysql -u user -p

Launch the MySQL client, logging in as a user who has GRANT privileges.

2.

Enter user's password at the "Enter password" prompt, then press Enter.

The MySQL client displays the mysql> prompt.

3.

Use the GRANT statement (a standard SQL statement) to create the new user and set his or her privileges:

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

Create an account for user with the specified password when logging in from host (use % to represent any host other than localhost; to let user log in from any system, you need one GRANT with host set to localhost and one with host set to %).

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.

1.

mysql -u user -p

Launch the MySQL client, logging in as a user who has GRANT privileges.

2.

Enter user's password at the "Enter password" prompt, then press Enter.

The MySQL client displays the mysql> prompt.

3.

SET PASSWORD FOR 'user'@'host' = PASSWORD('pass');

Set the specified user's password to pass.

To delete a user

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

1.

mysql -u user -p

Launch the MySQL client, logging in as a user who has GRANT privileges.

2.

Enter user's password at the "Enter password" prompt, then press Enter.

The MySQL client displays the mysql> prompt.

3.

SHOW GRANTS FOR 'user'@'host';

List the privileges granted to user when logged in from host. These must be revoked before the user can be removed.

4.

REVOKE priv ON *.* FROM 'user'@'host';

Revoke user's privileges; priv is the privileges shown in step 3.

5.

DELETE FROM mysql.user WHERE User='user' AND Host='host';

Delete user's account.

6.

FLUSH PRIVILEGES;

Tell the MySQL server that privileges have been modified.

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.



    Unix Advanced. Visual QuickPro Guide
    Unix Advanced: Visual QuickPro Guide
    ISBN: 0321205499
    EAN: 2147483647
    Year: 2003
    Pages: 116

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net