MySQL Administration

1. Start a terminal session and become the superuser. (Type su at the prompt and then enter the root password.)

2. Now we'll access the MySQL server. Type:

mysql -u root -p [-h host]

The system prompts for the MySQL root password.
Once we are successfully logged in, the system prints a welcome message and displays the mysql prompt ... something like

Welcome to the MySQL monitor. Commands end with ; or \g.
Type 'help' for help.
mysql>

3. Now we are ready for creating the database. Issue the command: (Note: The command ends with a semi-colon.)

CREATE DATABASE [dbname] ;

4. Create an account

CREATE USER [username]@localhost IDENTIFIED BY '[password]';

5. An important point to note is that this database is created by the root and so will not be accessible to any other user unless permitted by the root. Thus, in order to use this database from other account, We have to set the permissions by issuing the following command:

GRANT ALL ON [dbname].* TO [username]@localhost;
GRANT SELECT ON [dbname].[table_name] TO [username]@localhost;

6. Close the mysql session by typing quit at the prompt. Exit from superuser and come back to your account. (Type exit).

Execute MySQL statements from a text file

If the file is at e.g /sql/myscript.sql, we can load it into the [dbname] database using the following command from the command line:

mysql -u [username] -p [dbname] < /sql/myscript.sql

Executing the file from within the MySQL command line shell. mysql>

source /sql/myscript.sql

Apply any changes on settings related to privileges without restarting MySQL

mysql -u username -p password -e "flush privileges";