Mysql Server Password

Login to mysql server for the first time without a password

root@ubuntu:~# mysql –u root

Set a new password for root in mysql server

 root@ubuntu:~# mysqladmin -u root  password ‘secret_password’

login to mysql with a password

root@ubuntu:~# mysql –u root –p

Enter the password when prompted

Manage database in mysql server

List all the databases on the mysql server

mysql> show databases;

To create a new database in mysql

mysql> create database database_name;

To delete a database from mysql

mysql> drop database database_name;

Users and privileges management in mysql server

Create a user testuser with permission to connect to the server from local host using a password ‘helloworld’ in mysql

mysql> grant usage on *.* to testuser@localhost identified by ‘helloworld’;

And finally we grant all privileges on the database_name database to this user in mysql

mysql> grant all privileges on database_name.* to testuser@localhost ;

Show the list of users in mysql

mysql> select user,host from mysql.user;

To find the privilege(s) granted to a particular MySQL account:

mysql> show grants for ‘root’@’localhost’;

To find privilege of the current MySQL account (account logged in with):

mysql> show grants;

database backup and restoration in mysql server

To backup a mysql database

root@ubuntu:~# mysqldump -u root -p database_name > dumpfilename.sql

to restore mysql database, the database must exist in mysql

root@ubuntu:~# mysql -u root -p databse_name < dumpfilename.sql