Basic commands

MySQL

Basic login procedure
Management
Special notes

1. Basic login procedure

Make sure the mysql server is running.

shell> ps aux|grep mysqld

You will see something like

mysql     1105  0.0  0.3 147108 29096 ?        Ssl  Aug10   0:37 /usr/sbin/mysqld

shell> mysql -u user -h host -p

Or,

shell> mysql -u user -h localhost -D dbname -p

Then, input the password.
For example,

shell> mysql -u root -h localhost -p

shell> mysql -u root -h localhost -D mysql -p

If the '-D' option is used, there is no need to run use after login.

After login, check what are the databases:

mysql> show databases;

It will show something like:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| citeseerx          |
| csx_citegraph      |
| mysql              |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

Then, choose one database to link to.

mysql> use testdb;

You will see the information similar to the following one.

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

Check what are the tables inside this database.

mysql> show tables;

You will see similar information as follows.

+------------------+
| Tables_in_testdb |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

Check the definition of a table.

mysql> desc student;

You can see the following information:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

You can test a lot of other SQL statements.
Disconnect from mysql.
```
mysql> exit;
```

2. Management

List all the users (in relation mysql.user).
```
select Host,User from mysql.user;
```
Create a new user
```
mysql> CREATE USER 'user1'@'localhost' IDENTIFIED BY 'user1password';
```
After you login with this user and you want to see how many databases by running
```
show databases;
```
You would be only see one database information_schema. In order that the new user can use some existing databases, you need to run grant commands.
Show all the available privileges.
```
mysql> show privileges;
```

Grant user some privilege.

mysql> GRANT right list on dbname.* to 'user'@'host';

e.g.,

mysql> GRANT SELECT on testdb.* to  'user1'@'localhost';

Show the privileges for a user.

SHOW GRANTS [FOR user]

E.g.,

SHOW GRANTS;

SHOW GRANTS FOR user1@localhost;

Change MYSQL password for user root
- If you have never set a root password for MySQL server, the server does not require a password at all for connecting as root. To setup root password for first time, use mysqladmin command at shell prompt as follows:
```
$mysqladmin -u root password 
```
- However, if you want to change (or update) a root password, then you need to use the following command:
```
$ mysqladmin -u root -p'oldpassword' password newpass
```
  For example, If the old password is abc, you can set the new password to 123456, enter:
```
$ mysqladmin -u root -p'abc' password '123456'
```

Change MYSQL password for other users

$mysql -u root -h localhost -p

mysql> use mysql;

mysql> update user set password=PASSWORD("NEWPASSWORD") where User='u1';

mysql> flush privileges;

mysql> quit;

3. Special notes

MySQL check version:

$mysqld --version

Server version

prazdroj[146]% mysql --version
  mysql  Ver 14.14 Distrib 5.1.57, for suse-linux-gnu (x86_64) using readline 6.1

Backup MySQL Database to a file

mysqldump -h localhost -u root -p databasename > dumpfile.sql

Example

mysqldump -h localhost -u root -p mydb > dumpfile.sql

Import dump file into MySQL DB
```
mysql -u u1 -p -D dbname < dumpfile.sql
```