This is my old site that I'm keeping up for historical purposes and is no longer updated. You probably want to see my new site.

  ·Backup and restore
  ·Creating a user and their own database
  ·Changing passwords
  ·Changing administrator password
  ·Disabling network access
  ·Flushing Initial Database and Users
  ·Deleting users
  ·Notes

Backup and restore

Backing up tables: 

mysqldump -p -c -Q databasename tablename1 tablename2 ... > whatever.sql

Restoring tables: 

mysql -p databasename < whatever.sql

Creating a user and their own database 

CREATE DATABASE dbname;
GRANT USAGE ON dbname.* TO username@'%';
GRANT ALL ON dbname.* TO username@'%';
SET PASSWORD FOR username@'%' = PASSWORD('blah');
FLUSH PRIVILEGES;

Changing passwords 

SET PASSWORD = PASSWORD('blah');

Changing administrator password 

mysql -u root
mysql> SET PASSWORD FOR root@localhost = PASSWORD('new_password');

Disabling network access

In my.cnf, add the keyword skip-networking and the line bind-address=127.0.0.1 (for any applications that still require TCP networking) to the [mysqld] section.

Flushing Initial Database and Users 

drop database test;
use mysql;
delete from db;
delete from user where not (host="localhost" and user="root");
flush privileges;

Deleting users 

REVOKE ALL PRIVILEGES ON *.* FROM username@'%';
REVOKE GRANT OPTION ON *.* FROM username@'%';
DELETE FROM mysql.user WHERE (User='username' and Host='%');

Notes

  • For specifying hosts users may access from, '%' means from any host. These have precedence over 'localhost'.
Backlinks:
Linux Notes