Experiments with My SQL....

Of late I am working with My SQL server, so wanted to share my hands-on experience with blogger world.

Topics covered:
1) How to check My SQL server is up or not and how to start the server.
2) Various command to login to the My SQL server.
3) How to change/reset/recover the root/user passwords.
4) How to take the dump of a My SQL database to a file and how to import it from a file.
5) Executing queries from shell.

Start and Stop….
1) You can check whether My SQL server is up or not by simple unix command given below.
$ ps –ef|grep mysql
The command will show the mysql process running or not.

If the My SQL server is not running, it is very simple to start, execute the following commands in shell prompt.
a) $cd /etc/init.d
b) $./mysql start
The above commands make the My SQL server up.

To stop the My SQL server use
$etc/init.d/mysql stop

Logging in….
2) We can login to the server by any of the below given commands.
a) Without database argument:

 $mysql –u root –p
In the above command –u signifies the option USER (in the command it is “root”) and –p signifies the option “database to be selected” (in the command we have not passed any argument). We will receive the prompt “Enter password:” give the corresponding password. Once we logged into the server it prompts “mysql>”, which is default, we can set the prompt to our desired one by the below command
mysql> prompt demo$
Prompt will be changed as shown below.
demo$

NOTE: In this article I have used default prompt for easier understandability.

We can see what are the databases existing in the server by following query.
mysql> show databases;

+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test1 |
+--------------------+
3 rows in set (0.00 sec)
Now we can select any database by the following query.
mysql> use [databse_name];

Example:

mysql> use test1;

b) With database argument:

$mysql –u r oot –p test1
We will receive the prompt “Enter password:” give the corresponding password. Here in this command database to be selected is given at command prompt only.

c) Now we see the single line login to My SQL server:
$mysql --user=[user] --password=[password[ [databse]

Example:
$mysql --user=root --password=abc123 test1
Note: “double hyphens” (--) are required in mentioning the arguments, no spaces are allowed before and after “equal to” (=) symbol and the value passed after the password (abc123 in this example) is taken as database to selected, which is optional.

Passwords….
3) If we have not set the root password for My SQL, the server does not require a password at all to login. To set the password for the first time use the following command in shell prompt.
$mysqladmin –u root password NEWPASSWORD

If we want to change the password, use the command given below
$mysqladmin –u root –p ‘oldpassword’ password NEWPASSWORD

  The same command can be used for changing the password for any user by mentioning the user id instead of “root”. For example to change the password for user “USER1” the command is as followed.
$mysqladmin –u USER1 –p ‘oldpassword’ password NEWPASSWORD

Or we can reset the password of any user using My SQL command prompt as shown below.
a) Login to the My SQL server
$mysql – u root –p
b) Select the databse
mysql> use mysql;
c) Change the password for user USER1
mysql> update user set password=PASSWORD(“NEWPASSWORD” )where user=’USER1’;
d) Reload the priviliges
mysql> flush priviliges;
mysql> exit

Even if we forgot the root password to login to My SQL database, we can login to database without the password, in the following way.

a) Stop the My SQL server process in shell
$/etc.init.d/mysql stop
b) Start the MySQL (mysqld) server/daemon process with the --skip-grant-tables option so that it will not prompt for password
$mysqld_safe –skip-grant-tables &
c) Connect to mysql server as the root user
mysql –u root
d) Setup new root password
mysql> use mysql;
mysql> > update user set password=PASSWORD(“NEWPASSWORD” )where user=’root’;
mysql> flush priviliges;
mysql> exit
e) Exit and restart MySQL server
$/etc.init.d/mysql stop
$/etc.init.d/mysql start
  And now we can login to database with the new password we just set.

Taking the database Backup….
4) There is one simplest way to take the back up of a database by the below given command
$mysqldump –u [user] –p [databse] > backupfile.sql
Example:
$mysqldump -u root -p test1 > Mysql_backupfile.sql
By the above command we take the back up of “test1” database to a file “Mysql_backupfile.sql”.

To restore the database from a file is quite easier. Use the command below
$mysql –u [user] -p [database_to_be_restored] < Mysql_backupfile.sql
Example:
$mysql -u root -p test1 < Mysql_backupfile.sql

Executing queries from shell….
5) We can execute the insert/update queries from command prompt by using the following command.
$mysql [databse_name] < [filename] -u [user] -p
Example:
$mysql test1 <  file1 -u root -p
Here in the file “file1” we can write our desired insert/update queries. (Each query terminated by a semicolon (;) and the next query in a new line). Execution of the command, prompts for the “root” user password, enter the corresponding password, the data in the “test1” database will be altered or inserted according to the queries given.

You can refer this link or contact me for any assistance in My SQL.