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 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. 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. |