Skip to main content

Running and Shutting down MySQL Server:

First check if your MySQL server is running or not. You can use the following command to check this:
 ps -ef | grep mysqld
If your MySql is running, then you will see mysqld process listed out in your result. If server is not running, then you can start it by using the following command:
root@host# cd /usr/bin
./safe_mysqld &
Now, if you want to shut down an already running MySQL server, then you can do it by using the following command:
root@host# cd /usr/bin
./mysqladmin -u root -p shutdown
Enter password: ******

Setting Up a MySQL User Account:

For adding a new user to MySQL, you just need to add a new entry to user table in database mysql.
Below is an example of adding new user guest with SELECT, INSERT and UPDATE privileges with the password guest123; the SQL query is:
root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> INSERT INTO user 
          (host, user, password, 
           select_priv, insert_priv, update_priv) 
           VALUES ('localhost', 'guest', 
           PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
| host      | user    | password         |
+-----------+---------+------------------+
| localhost | guest | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)
When adding a new user, remember to encrypt the new password using PASSWORD() function provided by MySQL. As you can see in the above example the password mypass is encrypted to 6f8c114b58f2ce9e.
Notice the FLUSH PRIVILEGES statement. This tells the server to reload the grant tables. If you don't use it, then you won't be able to connect to mysql using the new user account at least until the server is rebooted.
You can also specify other privileges to a new user by setting the values of following columns in user table to 'Y' when executing the INSERT query or you can update them later using UPDATE query.
  • Select_priv
  • Insert_priv
  • Update_priv
  • Delete_priv
  • Create_priv
  • Drop_priv
  • Reload_priv
  • Shutdown_priv
  • Process_priv
  • File_priv
  • Grant_priv
  • References_priv
  • Index_priv
  • Alter_priv
Another way of adding user account is by using GRANT SQL command; following example will add user zara with password zara123 for a particular database called TUTORIALS.
root@host# mysql -u root -p password;
Enter password:*******
mysql> use mysql;
Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON TUTORIALS.*
    -> TO 'zara'@'localhost'
    -> IDENTIFIED BY 'zara123';
This will also create an entry in mysql database table called user.
NOTE: MySQL does not terminate a command until you give a semi colon (;) at the end of SQL command.

The /etc/my.cnf File Configuration:

Most of the cases, you should not touch this file. By default, it will have the following entries:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Here, you can specify a different directory for error log, otherwise you should not change any entry in this table.

Administrative MySQL Command:

Here is the list of important MySQL commands, which you will use time to time to work with MySQL database:
  • USE Databasename : This will be used to select a particular database in MySQL workarea.
  • SHOW DATABASES: Lists the databases that are accessible by the MySQL DBMS.
  • SHOW TABLES: Shows the tables in the database once a database has been selected with the use command.
  • SHOW COLUMNS FROM tablename: Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults, and other information for a table.
  • SHOW INDEX FROM tablename: Presents the details of all indexes on the table, including the PRIMARY KEY.
  • SHOW TABLE STATUS LIKE tablename\G: Reports details of the MySQL DBMS performance and statistics.

Comments

Popular posts from this blog

Android operating system is a stack of software components which is roughly divided into five sections and four main layers as shown below in the architecture diagram. Linux kernel At the bottom of the layers is Linux - Linux 3.6 with approximately 115 patches. This provides a level of abstraction between the device hardware and it contains all the essential hardware drivers like camera, keypad, display etc. Also, the kernel handles all the things that Linux is really good at such as networking and a vast array of device drivers, which take the pain out of interfacing to peripheral hardware. Libraries On top of Linux kernel there is a set of libraries including open-source Web browser engine WebKit, well known library libc, SQLite database which is a useful repository for storage and sharing of application data, libraries to play and record audio and video, SSL libraries responsible for Internet security etc. Android Libraries This category encompasses those Java-based li
C is a general-purpose, procedural, imperative computer programming language developed in 1972 by Dennis M. Ritchie at the Bell Telephone Laboratories to develop the UNIX operating system. C is the most widely used computer language. It keeps fluctuating at number one scale of popularity along with Java programming language, which is also equally popular and most widely used among modern software programmers. Audience This tutorial is designed for software programmers with a need to understand the C programming language starting from scratch. This tutorial will give you enough understanding on C programming language from where you can take yourself to higher level of expertise. Prerequisites Before proceeding with this tutorial, you should have a basic understanding of Computer Programming terminologies. A basic understanding of any of the programming languages will help you in understanding the C programming concepts and move fast on the learning track. Execute
C is a general-purpose, high-level language that was originally developed by Dennis M. Ritchie to develop the UNIX operating system at Bell Labs. C was originally first implemented on the DEC PDP-11 computer in 1972. In 1978, Brian Kernighan and Dennis Ritchie produced the first publicly available description of C, now known as the K&R standard. The UNIX operating system, the C compiler, and essentially all UNIX application programs have been written in C. C has now become a widely used professional language for various reasons − Easy to learn Structured language It produces efficient programs It can handle low-level activities It can be compiled on a variety of computer platforms Facts about C C was invented to write an operating system called UNIX. C is a successor of B language which was introduced around the early 1970s. The language was formalized in 1988 by the American National Standard Institute (ANSI). The UNIX OS was totally written in C. Today C