In this post, I briefly cover some of the administration operations for running a MySQL database, including adding and dropping users, updating and recovering passwords, and a few options for database backup.
MySQL: Adding or Dropping a User
Adding a new user is a three-step process. First you add the user account, and then you grant privileges to whatever databases that person will be using.
1. To add a user, you use the Create User command. Log onto Mysql as root, then, at the prompt, type:
mysql>create user [name of new user];[MYSQL BUG: The documentation says you can add password onto this line, i.e. "identified by '[password]' but YOU CAN'T!!! You could spend all morning wondering why your new client can't log in because of this SNAFU.
Instead, you have to add the password in a separate command (see the section on adding passwords). So, step 2 is:
2. Add password for new user.
3. To grant database privileges to the user, you use the grant command. First you stipulate the privileges you want to grant, specify the name of the databases these privileges would apply to, and finally whom all this should apply to. Intuitively backwards.
mysql>GRANT ALL ON [databasename].* TO '[username]'@'[somehost]'; mysql>GRANT SELECT, INSERT ON *.* TO '[someuser]'@'[somehost]';Note that somehost specifies where the host is working from, either remotely (over a network) or on the terminal of the machine where mysql resides (localhost). You can also specify specific IP numbers, I think. If you leave @ off altogether, mysql assumes the user can access these privileges from anywhere.
Also note that using a database usually involves working with multiple files, all under the name of the database but with different suffixes. So that it is usually good to use a wildcard for the suffix of these files for grant privileges.
[MYSQL BUG: The '%' wildcard is deceptive, insofar as it does not grant privileges from the local host, as the documentation states. Instead, you have to grant a set of privileges especially for the localhost, i.e. '[someuser]'@'localhost'.]
Dropping a user account can be done thusly:
mysql>drop user [Name of user]
MySQL: Updating Passwords
Updating a password can be done within mysql, by root, thusly:<
mysql>use mysql;
mysql>update user set password=PASSWORD('[Password]') where user='[Name of user]';
Note, to change the password from the account you are already in, you do not need to specify user i.e.:<
mysql>set password=PASSWORD('[password]')
The second designation of PASSWORD is actually a function call, one that encrypts, or hashes the password you supply.
Also, keep in mind that, after any password updates you must flush privileges:
mysql>flush privileges;And jumping back out to the command line and restarting MySql is not a bad idea, either:
mysql> quit # /etc/init.d/mysql stop # /etc/init.d/mysql start
MySQL: Recovering a Lost Password
Locked out of MySQL? here is how you recover access: You stop MySQL. Then, you start MySQL in safe mode, then start the MySQl database itself, set the new password, flush the privileges, exit. Stop and then restart the database in normal mode:
# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start
The Geek Diary suggests going with mysqldump:
mysqldump --user=username --password=password --opt DatabaseName > database.sql
Another simple way to back up a database is to copy the files to another location. The table files have the name suffixes of *.frm, *.MYD, and *.MYI.
With MySQL 5.0 (in Ubuntu), the database files are located at /var/lib/mysql, with each database getting its own directory. Copying could be done thusly:
cp -R /var/lib/mysql/[name of database] /[where you want to put the backup copy]
NOTE: The uppercase -R after the copy (cp) command means the function copies recursively, meaning that it copies all the files and subdirectories and their files under that directory.
