- 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
