Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

The title of this post was the most bugging error for me while working with MySQL on Linux. I installed MySQL Server 5.6 on my Ubuntu system manually. Then I installed and configured MySQL Workbench. Then I restarted my system. When I tried to connect to MySQL Server through command line, I got the error,

Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

This means the service MySQL is not running on system and it has to be started. So I tried starting the server with the following command,

shell> sudo /usr/local/mysql/support-files/mysql.server start

Then I got a new error,

couldn't find mysql server (/usr/bin/mysqld_safe)

I understood that it is looking a wrong directory for mysqld_safe because I installed in another location. This can be set in the file my.cnf. It is located in the folder /etc/mysql/. I went to that location and changed the base directory and data directory locations in that file and attempted to start the server, but it was of no use.

I surfed a lot to find a solution for this problem but no blog suggested me the right one. May be, my search was wrong. Some blogs suggested to check whether the directory mysqld exists in the location /var/run/ so that the socket file mysqld.sock can be created while starting the server. Following this suggestion, I created a directory in the location /var/run/ with the name mysqld. Then I tried to start the service and it resulted in a new error,

Starting MySQL... ERROR! The server quit without updating PID file

I didn't know what to do with this error. I searched online for its solution. Many blogs suggested me to kill the process with the name mysql but it didn't help me. Getting frustrated, I removed and re-installed MySQL multiple times on my system. But nothing was fruitful.

Then I observed something. When I installed just MySQL Server, there was no file with name my.cnf in the directory /etc/mysql. It was created only when I installed MySQL Workbench. Before it was installed, the socket mysql.sock was created in the directory /tmp. So, as a last trail, I deleted the file my.cnf from the directory /etc/mysql/ using the following commands, as a root user,

shell> cd /etc/mysql/

shell> rm my.cnf

Then I tried starting MySQL Service with this command,

shell> sudo /usr/local/mysql/support-files/mysql.server start

Surprisingly, MySQL Service got started and I could connected to it through command line. The socket was created in the directory /tmp. Then I found the location of pid file by issuing the following query in the mysql command prompt,

mysql> show variables where variable_name like '%pid%';

Now my MySQL Server is working fine when connected through command line and MySQL Workbench too. Now I can start or stop the server and can play with it! :)

Installing & Troubleshooting MySQL 5.6 Enterprise on Linux

Though newer versions of Linux Operating Systems came up with GUI to help their users, Linux Commands have their own importance. One of such scenarios which I've undergone few days back is installing MySQL on Linux.

I had to work with MySQL on Linux Platform. The operating system given to me was Ubuntu 14.04 LTS. On Ubuntu, MySQL can be installed from the source with a single line command,

shell> sudo apt-get install mysql-server5.6

But I was asked to install MySQL Server 5.6.19 Commercial on the Ubuntu system. Then I downloaded it and tried to install. Though it comes with install instructions, it's difficult to understand the commands for a Windows user like me. Later surfing through internet helped me with the information from different blogs and documents. Thanking all of them, I'm compiling them in this post. In this post, I'll show you how to install MySQL Server 5.6 from a tar file downloaded. 

tar means Tape Archive. It was used in older days of UNIX where files are stored on tapes.

Installing MySQL Server 5.6

Download MySQL Server 5.6 from Oracle Website. As I specified about tar file, download the tar file which is compatible to your OS (32 bit or 64 bit).

After the download gets completed, unzip it.

Open Terminal and switch to Super User account with the following command,

shell> sudo su -

Authenticate yourself by providing your password and switch to root account.

Now copy the extracted .tar file to a location like /usr/local with the following command,

shell> cp <location of the file>/<Name of the file>.tar.gz /usr/local

After doing this, create a group with name mysql and a user mysql in that group. Note that naming the group and user mysql is not a compulsion, it's a convention. You can name them anything.

shell> groupadd mysql

shell> useradd -r -g mysql mysql

Here option -r specifies that a system account is created whose password never expires. Option -g specifies the initial group to which the user belongs to. There must exist a group which is already created.

Now go the location where the tar file is copied and it has to be unzipped to carry out the remaining part of installation. Run the following commands in a sequence,

shell> cd /usr/local

This takes you to the directory /usr/local.

shell> tar zxvf <Name of the tar file>.tar.gz

This command unzips the tar file. The option zxvf means z(unzipping), x(extract), v(print file names verbosely), f(the following argument is the name of file). The tar command is used to create, modify and access files of tar archive.

shell> ln -s <Name of the folder extracted from tar> mysql

In this command, ln means link. It means a link is created between source folder and target folder. This link should be created with an option. Here the link -s specifies that a symbolic link is created between the source folder (extracted tar folder) and the target folder (mysql).

Now a target folder is created with name mysql. Open that folder,

shell> cd mysql

shell> chown -R mysql .

In the above command, chown means Change Ownership. Here the ownership of folder mysql is given to the user mysql. The option -R specifies that this user performs operations on the folder recursively. Do not forget to run the command along with dot(.) at the end.

shell> chgrp -R mysql .

In this command, chgrp means Change Group. This means the folder mysql is now changed its group to mysql. As said above, the option -R specifies that group mysql performs operations on the folder recursively. This command is said to be the sister command of chown. Do not forget to run the command along with dot(.) at the end.

shell> scripts/mysql_install_db -user=mysql

If you observe the directory mysql, there exists a file with name mysql_install_db which contains a script. To install mysql, this should be executed. In the above command, it is executed with the privileges of user mysql.

If you're installing MySQL Server on your machine for the first time, here comes an error that a library libaio1 cannot be found. You need to install this library by using the following command,

sudo apt-get install libaio1

After this library gets installed, run the previous command again which starts installing MySQL on your system.

shell> chown -R root .

This command changes the ownership of the folder to root account.

shell> chown -R mysql data

This command changes the ownership of the folder data to user mysql.

shell> bin/mysqld_safe --user=mysql &

Here mysqld is the service of mysql. The '&' at the end specifies that the command before it should be run in background. The whole command means, the service mysqld present in bin folder must be run with the user mysql in the background.

Next comes an optional command which copies configuration file to a location in /etc folder,

shell> cp support-files/mysql.server /etc/init.d/mysql.server

Now MySQL Server is installed in your system

Connecting to MySQL Server 5.6

To connect to the above installed MySQL Server, 

Open Terminal and switch to root account by authenticating yourself,

shell> sudo su -

Go to the location where MySQL was installed,

shell> cd /usr/local/mysql/bin

Run the executable mysql with the command 

shell> ./mysql

This takes you to mysql command line. To check, run the following query which shows the databases present in the server,

mysql> show databases;

Troubleshooting MySQL Server on Ubuntu

MySQL is easier to install on Ubuntu and annoys that much too. Sometimes when machine is restarted or under any situation, you cannot get connected to the MySQL Server installed on your machine. When you try to get connected, it shows the following error,

Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

This means server is not running at local machine unless you didn't do any changes in the configuration file. So, to start the service again, run the following command,

shell> sudo /usr/local/mysql/support-files/mysql.server start

To stop the server, replace start with stop in above command,

shell> sudo /usr/local/mysql/support-files/mysql.server stop

To restart the server, replace start with restart,

shell> sudo /usr/local/mysql/support-files/mysql.server restart

Now play with your MySQL Server on Linux. Hereby I thank all the bloggers who helped me with their educative posts.

ACID Properties in RDBMS

If there exists something with name Database then it should some properties. First was Codd's Rules. Then it should be in such a way that it undergoes Normalization. But these two come under creation segment of database mainly. After database is designed, data modifications are done obviously. Such data modifications in RDBMS are done through Transactions. Every update, insert etc are considered as a transaction. These transactions ensure the consistency of your database. Every transaction should adhere to some properties called ACID properties. ACID is an acronym for Atomicity, Consistency, Isolation and Durability. In this post, we'll see about ACID.

Atomicity

A Transaction should be atomic in nature. This means, multiple changes to the database are executed as a single unit by transaction. If a transaction executes 'n' number of changes to database then all should be committed if everything goes right, otherwise no single change should be committed if anything goes wrong in the transaction. If one change is committed and remaining are not committed then such transaction is not said to be atomic because this results in inconsistent state of database. The ability of committing or rolling back a transaction is achieved by Atomicity.

Consistency

Every transaction should leave the database in a consistent state after its completion (A transaction is said to be completed if it is committed or rolled back). The best scenario to explain this property is given in Microsoft SQL Server 2012. I'm reproducing the same scenario here.

Assume there are two tables Table1 and Table2. If Column1 of Table1 is used as foreign key in Table2 then there shouldn't be a scenario where a transaction cannot update column1 in Table1 with a value but it updates the same column in Table2 with the same value. This results in inconsistency of database as there is a violation of Foreign Key.

When a transaction starts, it can take the database into inconsistent state but at its completion it should bring database back to consistent state.

Isolation

The word ISOLATION itself states that a transaction should be independent from other transactions. One transaction should not affect another transaction. RDBMS like SQL Server has different isolation levels like READ COMMITTED,READ UNCOMMITTED, SERIALIZABLE etc. By default database resides in level READ COMMITTED. This means if a transaction is making changes to data then another transaction cannot even read that data until the previous transaction gets completed. Transactions impose locks at table level, row level to ensure that no other transaction can red or modify the data that is being changed by them. Other RDBMS like Oracle, MySQL have locking mechanism imposed on tables.

Durability

Durability is a property which ensures the changes made by a committed transaction reside stable in the database. This can be achieved by writing the changes to a stable disk. For this, there exists a Transaction Log which records every transaction made in the database. Changes made are recorded in Transaction Log before writing them to the disk.