MySQL

What is MySql ?

 

Its a fast and easy to use Relational Database Management System(RDBMS) being used for many small and large scale businesses.

Database is an application that stores a collection of data. Nowadays we mostly use RDBMS to store and manage large volumes of data. This is called Relational Database, since all data are stored in tables and and different tables are related using relations like primary keys or foreign keys.

 

 

Installing MySQL Using binaries

 

•  First download the required package as a tar file

• Then uncompress it at the place where you desire to make the mysql installation directory
• 
  Execute the following commands creating a user and a group called mysql

shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
shell> bin/mysqld_safe --user=mysql &

• here I chose /usr/local as my installation directory 

• Be careful when giving permissions to the created mysql user as the root

 Problem...?

Above mentioned steps didn't make my mysql demaen up. terminal struck in  the middle
the below error was given when the service was tried to started

test@hsenid-OptiPlex-3020:/usr/local/mysql$ service mysql.server start
Failed to start mysql.server.service: Unit mysql.server.service failed to load: No such file or directory.

 

      Solution :)

what is mysqld...?

http://dev.mysql.com/doc/refman/5.7/en/mysqld.html


it is the main program that does all the work in the mysql installation process.

it manages access to MySql data data directory which stores mysql databases and tables and also log files and status files.

Then I found that there are several methods of starting the mysql server

1. invoking mysqld directly
2.  invoking mysqld_safe which tries to determine proper options for mysqld http://dev.mysql.com/doc/refman/5.7/en/mysqld-safe.htmlOn linux systems that support systemd, can use it to control the server
3. http://dev.mysql.com/doc/refman/5.7/en/server-management-using-systemd.html
4.  On systems that uses System V-style run directories (such systems provides a standard process for controlling which programs init launches and configuration files for SysV init are located in the /etc/rc.d/ directory.) invoke mysql.server  http://dev.mysql.com/doc/refman/5.7/en/mysql-server.html

• I used the last method. 

• There I implemented the final steps

shell> cp mysql.server /etc/init.d/mysql
shell> chmod +x /etc/init.d/mysql

• then move to etc folder

shell> cd /etc
shell> ln -s rc.d/init.d   //this didn't work for me sice I had init.d file directly in etc

                             so,create a symbolic link pointing to rc.d/init.d to init.d

• Now the system is installed. It is needed to activate it. Use following in linux

shell> chkconfig --add mysql

if required...

shell> chkconfig --level 345 mysql on 

•   In Ubuntu my machine chkconfig didn't work. So the related command for that in ubuntu was foung as update-rc.d  http://askubuntu.com/questions/2263/chkconfig-alternative-for-ubuntu-server

 update-rc.d mysql default  => to start



• Then run mysql

 bin/mysql -u root -p
 

• following might be a useful link

http://coolestguidesontheplanet.com/start-stop-mysql-from-the-command-line-terminal-osx-linux/ 

MySQL from the Command Line

• to see whether mysql runs properly 

 test@hsenid-OptiPlex-3020:~$ sudo netstat -tap | grep mysql

tcp6       0      0 [::]:mysql              [::]:*                  LISTEN      813/mysql

• securing mysql accounts

https://dev.mysql.com/doc/refman/5.6/en/default-privileges.html

• check the status of mysql service 

/etc/init.d/mysql.server status

service mysql.server status

• Configure MySQL to launch at any place without going to the bin directory 

add the following to  the bashrc file

MYSQL_HOME=/usr/local/mysql
export PATH=$JAVA_HOME/bin:$ANT_HOME/bin:$MAVEN_HOME/bin:$CATALINA_HOME/bin:$MONGODB_HOME/bin:$MYSQL_HOME/bin:$PATH

and source the bash file 

•  Find the port, hostname & username of mysql 

start mysql

mysql> select user();
+--------+
| user() |
+--------+
| test@  |
+--------+



mysql> SHOW VARIABLES WHERE Variable_name = 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES WHERE Variable_name = 'hostname';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| hostname      | hsenid |
+---------------+--------+
1 row in set (0.00 sec)
 

• To change the port in mysql

  check the chosen port is pure =>  sudo netstat -tanp| grep 3337
  
  
   Edit etc/mysql/my.cnf file  

 MySQL GUI Tools

•  Workbench => suitable(current version 6.3)Navicat for Mysql

• Sequel Pro => suitable (comes with 5 extra languages)

• HeidiSQL => proj discontinuied since 2010

• SQLyog => only Windows

• SQL Wave => 30 day trial version

• dbForge Studio

• MyDB studio