The following notes are based primarily on using MySQL directly.
==================================================
# just in case you forget the root password:
1. root_prompt> /usr/local/mysql/bin/safe_mysqld --skip-grant-tables &
2. root_prompt> /usr/local/mysql
3. mysql_root> USE mysql;
4. mysql_root> UPDATE user SET password=PASSWORD('.......') WHERE user='root' AND host='localhost';
3. mysql_root> FLUSH PRIVILEGES;
5. stop and restart MySQL server.
==================================================
http://dev.mysql.com/doc/mysql/en/grant.html
http://www.mysql.com/documentation/mysql/bychapter/manual_Privilege_system.html
# list all current users found in this mysql database
mysql_prompt> SELECT Host, User FROM mysql.user;
# to get rid of anonymous access (not necessary)
mysql_root> DELETE FROM user WHERE user = "";
# or
mysql_root> DELETE FROM mysql.user WHERE Host='localhost' AND User='';
# nuke existing user
mysql_root> DELETE FROM user WHERE user = "username_to_delete";
# replace root info to grant complete access
mysql_root> REPLACE INTO user ( Host, User, Password,
Select_priv, Insert_priv, Update_priv,
Delete_priv, Create_priv, Drop_priv,
Reload_priv, Shutdown_priv, Process_priv, File_priv,
Grant_priv, References_priv, Index_priv, Alter_priv )
VALUES ( 'localhost', 'root', password( '.......' ),
'Y', 'Y', 'Y',
'Y', 'Y', 'Y',
'Y', 'Y', 'Y', 'Y',
'Y', 'Y', 'Y', 'Y' );
mysql_root> REPLACE INTO user ( Host, User, Password,
Select_priv, Insert_priv, Update_priv,
Delete_priv, Create_priv, Drop_priv,
Reload_priv, Shutdown_priv, Process_priv, File_priv,
Grant_priv, References_priv, Index_priv, Alter_priv )
VALUES ( 'localhost.localdomain', 'root', password( '.......' ),
'Y', 'Y', 'Y',
'Y', 'Y', 'Y',
'Y', 'Y', 'Y', 'Y',
'Y', 'Y', 'Y', 'Y' );
==================================================
# to quickly fill in the database
root_prompt> mysqladmin [options] create sampleDBname
root_prompt> mysql mysql < createusers.sql
root_prompt> mysql sampleDBname < createtable.sql
prompt> mysql -u sampleDBuser -p sampleDBname < create_another_table.sql
Enter password:
prompt> mysql -u sampleDBuser -p sampleDBname < fill_in_table.sql
Enter password:
root_prompt> mysql sampleDBname < etc_etc_etc.sql
# ........................................
# ( note: REPLACE will INSERT if no entry matches )
# where createusers.sql might contain:
REPLACE INTO user ( host, user, password )
VALUES (
'localhost',
'sampleDBuser',
password( 'sampleDBuserPassword' )
);
REPLACE INTO db ( host, db, user,
select_priv, insert_priv, update_priv,
delete_priv, create_priv, drop_priv )
VALUES (
'localhost',
'sampleDBname',
'sampleDBuser',
'Y', 'Y', 'Y',
'Y', 'Y', 'Y'
);
# ........................................
# ( note: there is no "REPLACE" equivalent with "CREATE" TABLE )
# where createtable.sql might contain:
CONNECT sampleDBname;
# nuke existing table
DROP TABLE sampleDBtable;
CREATE TABLE sampleDBtable (
# database id
id int(11) not null auto_increment,
# sample db items
title varchar(20),
name varchar(25) not null,
yrs1 int(4), # begining term year
yrs2 int(4), # ending yerm year
notes text,
# database control stuff
primary key (id)
);
==================================================
# to do this manually:
mysql_root> CREATE DATABASE sampleDBname;
mysql_root> REPLACE INTO user ( host, user, password )
VALUES (
'localhost',
'sampleDBuser',
password( 'sampleDBuserPassword' )
);
mysql_root> REPLACE INTO db ( host, db, user,
select_priv, insert_priv, update_priv,
delete_priv, create_priv, drop_priv )
VALUES (
'localhost',
'sampleDBname',
'sampleDBuser',
'Y', 'Y', 'Y',
'Y', 'Y', 'Y'
);
mysql_root> FLUSH PRIVILEGES;
mysql_prompt> DROP sampleDBname sampleDBtable;
# -- or --
mysql_prompt> USE sampleDBname;
mysql_prompt> DROP TABLE sampleDBtable;
mysql_prompt> USE sampleDBname;
mysql_prompt> CREATE TABLE sampleDBtable ( ... see createtable.sql above ... );
==================================================
# remote access examples:
1: Set up the 'host' table (assuming it has not yet been set up)
mysql_root> INSERT INTO host ( host, db,
Select_priv, Insert_priv, Update_priv,
Delete_priv, Create_priv, Drop_priv)
# with these example entries:
VALUES ('localhost','mydb','Y', 'Y', 'Y', 'Y', 'Y', 'Y');
VALUES ('devshed','mydb','Y', 'Y', 'Y', 'Y', 'Y', 'Y');
VALUES ('localhost','%','Y', 'Y', 'Y', 'Y', 'Y', 'Y');
VALUES ('somedomain','%','Y', 'Y', 'Y', 'Y', 'Y', 'Y');
2: Update the 'user' table, granting access to a new host+user combination.
mysql_root> INSERT INTO user (host,user,password)
# with these example entries:
VALUES('localhost','dario',password('mamamia'));
VALUES('www.devshed.com','dario',password('mamamia'));
Step 3: Update the 'db' table.
mysql_root> INSERT INTO db ( host, db, user,
Select_priv, Insert_priv, Update_priv,
Delete_priv, Create_priv, Drop_priv)
# with these example entries:
VALUES ('localhost','pasta','dario','Y','Y','Y','Y','Y','Y');
VALUES ('%','chicken','dario','Y','Y','Y','Y','Y','Y');
==================================================
http://www.devshed.com/Server_Side/MySQL/Administration/print
root_prompt> mysqlshow
+--------------+
| Databases |
+--------------+
| mysql |
+--------------+
root_promt> mysqlshow mysql
Database: mysql
+----------+
| Tables |
+----------+
| db |
| host |
| user |
+----------+
root_promt> mysql -e "SELECT host,db,user FROM db" mysql
+------+----------+------+
| host | db | user |
+------+----------+------+
| % | test | |
| % | test_% | |
+------+----------+------+
# ........................................
http://www.devshed.com/Server_Side/MySQL/Speak/Speak1/print
mysql_prompt> SHOW DATABASES;
+----------+
| Database |
+----------+
| library |
| mysql |
| test |
+----------+
mysql_prompt> USE mysql;
Database changed
mysql_prompt> SHOW TABLES;
+----------+
| Tables |
+----------+
| db |
| host |
| user |
+----------+
mysql_prompt> SELECT host,db,user FROM db;
+------+----------+------+
| host | db | user |
+------+----------+------+
| % | test | |
| % | test_% | |
+------+----------+------+
==================================================
Basic Security tips:
1. Above all, take a moment to update the root privileges, and give it a password!
Someone managing to get on the localhost can enter simply by typing:
mysql -u root mysql, if a password is not established.
2. Assign passwords for every user.
3. If the user doesn't have a specific need for it, don't assign permission to use File_priv.
This gives the user the ability to write files to the MySQL server. In addition, don't
give permission to the process_priv, unless there is a good reason for doing so.
4. Don't use '%' within the host names. It allows a user to enter that server from any outside
host. In addition, don't use wildcards (i.e. devshed%) It would theoretically allow someone
to use http://devshed.badcracker.com to aid them in entering the server.
==================================================
# the following two are the same
mysql_prompt> SHOW KEYS FROM tableItem;
mysql_prompt> SHOW INDEX FROM sampleDBtable FROM sampleDBname;
# to list all the entries from the table
# the following two are the same
mysql_prompt> SHOW COLUMNS FROM sampleDBtable;
mysql_prompt> SHOW FIELDS FROM sampleDBtable;
# narrow a search
mysql_prompt> SHOW FIELDS FROM sampleDBtable LIKE '%yyy';
# SEARCH
mysql_prompt> SELECT * FROM sampleDBtable WHERE tableItem LIKE '%zzz';
# multiple columns
mysql_prompt> SELECT tableItem,tableItem2 FROM sampleDBtable;
# list unique entries
mysql_prompt> SELECT DISTINCT tableItem FROM sampleDBtable;
==================================================