mysql -u <username> -p
Enter password:
Create database command:
--------------------------------
mysql> CREATE DATABASE <database>;
eg:
mysql> CREATE DATABASE ACCOUNTS;
We can now check for the presence of this database by typing:
mysql> SHOW DATABASES;
+-------------+
| Database |
+-------------+
| mysql |
| accounts |
+-------------+
USE Database:
-----------------
The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or until another USE statement is issued:
mysql> USE accounts;
mysql> SELECT COUNT(*) FROM mytable; # selects from db1.mytable
mysql> USE sales;
mysql> SELECT COUNT(*) FROM mytable; # selects from db2.mytable
Making a particular database current by means of the USE statement does not preclude you from accessing tables in other databases. The following example accesses the author table from the db1 database and the editor table from the db2 database:
mysql> USE accounts;
mysql> SELECT author_name,editor_name FROM author,sales.editor
-> WHERE author.editor_id = sales.editor.editor_id;
Delete / Remove database command:
--------------------------------------------
DROP DATABASE <database>
eg:
DROP DATABASE accounts;
Granting Privileges on the new database:
-----------------------------------------------
mysql> GRANT ALL PRIVILEGES ON DatabaseName.* TO Username@localhost
or
mysql> GRANT ALL PRIVILEGES ON DatabaseName.* TO Username@localhost IDENTIFIED BY 'newpassword';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON vworks.* TO newuser@localhost IDENTIFIED BY 'newpassword';
mysql> GRANT ALL PRIVILEGES ON DatabaseName.* TO Username@192.168.0.2 IDENTIFIED BY 'newpassword';
Now a user on the machine '192.168.0.2' can connect to the database. To allow a user to connect from anywhere you would use a wildcard '%'
mysql> GRANT ALL PRIVILEGES ON DatabaseName.* TO Username@localhost IDENTIFIED BY 'newpassword' WITH GRANT OPTION;
This would allow the user 'newuser' to log into the database and give their friend privileges to SELECT,INSERT,UPDATE or DELETE from the database.
REVOKING Privileges:
-------------------------
For example to REVOKE the privileges assigned to a user called 'user1':
mysql> REVOKE ALL PRIVILEGES ON DATABASENAME.* FROM user1@localhost;
Or just to remove UPDATE, INSERT and DELETE privileges to that data cannot be changed.
mysql> REVOKE INSERT,UPDATE,DELETE ON DATABASENAME.* FROM user1@localhost;
Backing Up DataBase:
-------------------------
mysqlhotcopy -u <username> -p <database> /backup/location/
Which SHOULD copy all the tables (*.frm, *.MYI, *.MYD) into the new directory - the script does require the DBI perl module though. To restore these backup files simply copy them back into your MySQL data directory.
This is my preferred method of backing up. This outputs the table structure and data in series of SQL commands stored in a text file. The simplified syntax is
mysqldump -u <username> -p <database> > file.sql
eg:
mysqldump -u user1 -p accounts > dump.sql
Restoring a DataBase from Dump:
---------------------------------------
mysqldump -u <username> -p <database> < file.sql
eg:
mysqldump -u user1 -p accounts < dump.sql