Các lệnh quản lý MYSQL qua SSH

[Admin]

How to Delete a MySQL Database via the Terminal


First, fire up MySQL in your terminal and login as an administrator account with:

mysql -u root -p

You will be prompted for your root password before being granted access to the MySQL monitor.

Now to grab a list of available databases on the system. Running:

SHOW DATABASES;

Will result in quite a list of available databases being drawn on the screen (note, it only lists the databases your user account has privileges to see). Once you have located the database you wish to delete in this list we can proceed:

DROP DATABASE IF EXISTS mydatabase;

This will now delete the mydatabase database from the MySQL server if it exists. If not, the drop database statement is simply ignored.


Creating a MySql database and username



To create a user account from a MySQL server instance, we need to make use of a CREATE USER call to the mysql.user table.
First, fire up MySQL in your terminal and login as an administrator account with:

[COLOR=#110000]
mysql [COLOR=#660033]-u[/COLOR] root [COLOR=#660033]-p[/COLOR]
[/COLOR]

You will be prompted for your root password before being granted access to the MySQL monitor.
Now to grab a list of available user accounts on the system. Running:

[COLOR=#110000]
[COLOR=#993333][B]SELECT[/B][/COLOR] [COLOR=#993333][B]USER[/B][/COLOR][COLOR=#66CC66],[/COLOR]host [COLOR=#993333][B]FROM[/B][/COLOR] mysql[COLOR=#66CC66].[/COLOR][COLOR=#993333][B]USER[/B][/COLOR];
[/COLOR]

If the user account that you wish to create doesn’t appear in that list, you are good to go. To create a new user account run the following query:

[COLOR=#110000]
[COLOR=#993333][B]CREATE[/B][/COLOR] [COLOR=#993333][B]USER[/B][/COLOR] [COLOR=#FF0000]'daivietp'[/COLOR]@[COLOR=#FF0000]'localhost'[/COLOR] [COLOR=#993333][B]IDENTIFIED[/B][/COLOR] [COLOR=#993333][B]BY[/B][/COLOR] [COLOR=#FF0000]'password-for-new-account'[/COLOR];
[/COLOR]

If you omit the @’localhost’ section, the user account will be generated with a host value of ‘%’. If you omit the identified by section, the user account will be created without a password (which is of course quite insecure).
Of course, this user isn’t linked to any databases yet, but this is simple to rectify with a:

[COLOR=#110000]
[COLOR=#993333][B]GRANT[/B][/COLOR] [COLOR=#993333][B]ALL[/B][/COLOR] [COLOR=#993333][B]ON[/B][/COLOR] mydatabase[COLOR=#66CC66].*[/COLOR] [COLOR=#993333][B]TO[/B][/COLOR] useraccount@localhost
[/COLOR]

Create the database

It is time to actually create the database you will be using for your website. To do this, first choose a good name (we'll use the name gallery as an example), and type:

create database daivietp_vbb;

…and press enter. You will then see the following appear:

Query OK, 1 row affected (0.16 sec)

This is good, and confirms that the database has been added.

Create a database user and password

Now, create a username and a password for your new database. The password should be very secure.

Let's say that the username will be 'ryan' and the password will be 'GanD1do'. You will then type the following:

grant all privileges on daivietp_vbb.* to 'daivietp'@'localhost' identified by "GanD1do";