Below are some references and recipes I use for common mysql administration, configuration and permissioning tasks.

• Read these articles:

  these notes on installing mysql on openbsd
  Securing MySQL: step-by-step
  Securing MySQL
  MySQL Basic Training MySQL Basic Training
  
  
  

• Create initial data base:

  mysql_install_db --user=_mysql --ldata=/var/mysql
  

• Describe the user table used to store accounts:

  USE mysql;
  DESCRIBE user;
  

• Grant full access to root:

  GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'secret' WITH GRANT OPTION;
  flush privileges;
  exit
  

• To create a database from mysql::

  create database db_name;
  

• To create a database from the command line:

  mysqladmin -u root -p create db_name
  

• List the databases with:

  show databases;
  

• List the tables in a database:

  USE db_name;
  
  SHOW TABLES;
  

  Or:

  SELECT * FROM mysql;
  

• Type \s at the "mysql>" prompt to get details about your current connection to the sql server via mysql.
• Dump an individual db in restorable format to stdout:

  mysqldump -p --add-drop-table db_name > db_name.sql
  

• Dump all db's in restorable format to stdout:

  mysqldump -p --all-databases --add-drop-table > all_db.sql
  

• Restore your dumped db or db's with:

  mysql -p < db_name.sql
  

• Verify permissions with mysqlaccess:

  mysqlaccess -U root localhost '%' '%' -P
  

• Resetting root when forgotten:

  1. Stop the MySQL server
  2. Restart server, temporarily turning off GRANT permissions:
         mysqld_safe -skip-grant-tables
  3. Reset the password
  4. Restart MySQL server with its normal options
  

• Revoke permissions and accounts with:

  SHOW GRANTS FOR 'user'@'host';
  REVOKE {privileges} ON {db.table} FROM  'user'@'host';
  DELETE FROM mysql.user WHERE  User='user' and Host='host';
  FLUSH PRIVILEGES;
  

• Show users with anonymous access to DB:

  USE mysql;
  SELECT host, user, select_priv, grant_priv, password from user where user='';
  

• Remove default access for anonymous access to DB:

  USE mysql;
  DELETE from user where user='';
  

  Optionally remove non-local access with:

  DELETE from user where host='fqdn.for.this.host';
  

• Privileges that may be assigned to accounts:

  ALL [PRIVILEGES]  Sets all simple privileges except GRANT OPTION 
  ALTER  Allows use of ALTER TABLE 
  CREATE  Allows use of CREATE TABLE 
  DELETE  Allows use of DELETE 
  DROP  Allows use of DROP TABLE 
  FILE  Allows use of SELECT ... INTO OUTFILE and LOAD DATA INFILE 
  INDEX  Allows use of CREATE INDEX and DROP INDEX 
  INSERT  Allows use of INSERT 
  PROCESS  Allows use of SHOW FULL PROCESSLIST 
  RELOAD  Allows use of FLUSH 
  REPLICATION CLIENT  Allows the user to ask where the slave or master servers are 
  REPLICATION SLAVE  Needed for replication slaves (to read binary log events from the master) 
  SELECT  Allows use of SELECT 
  SHOW DATABASES 
  SHOW DATABASES shows all databases 
  SHUTDOWN  Allows use of mysqladmin shutdown 
  UPDATE  Allows use of UPDATE 
  USAGE  Synonym for ``no privileges'' 
  GRANT OPTION  Allows privileges to be granted 
  

• Assigning passwords to accounts with mysqladmin:

  mysqladmin -u {username} -h {host}  password 'new_password' 
  

• Assigning passwords to accounts from mysql client:

  SET PASSWORD FOR 'user'@'%' =  PASSWORD('new_password');
     or
  GRANT USAGE ON *.* TO  'user'@'localhost' IDENTIFIED BY  'new_password';
  

• Create a DB for WordPress:

  CREATE DATABASE db_name;
  GRANT ALL PRIVILEGES ON db_name.* TO "db_owner"@"localhost" IDENTIFIED BY "password";
  FLUSH PRIVILEGES;
  

-- Bruce Howard