Wednesday, December 4, 2013

Installing mysql database on a unix/linux box

There are a few tricks involved with installing mysql on a unix machine. There is the installation part and then the part where you need to connect to the mysql database server.

PS. This article assumes that the unix machine has internet access.

Step 1

Download and install mysql database server.
This command will download mysql server from the internet and install it.
If you have root access:
 yum install mysql-server  
If you do not have root access:
 sudo yum install mysql-server  

Step 2

Start the mysql database server as a unix service.
If you have root access:
 service mysqld start  
If you do not have root access:
 sudo service mysqld start  

Step 3

Check the status of the mysql database server.
If you have root access:
 service mysqld status  
If you do not have root access:
 sudo service mysqld status  

Step 4

Check that you can connect to the mysql database from the same machine.
 shell>mysql -u root  
Then run a simple query:
 SHOW DATABASES;  

Step 5

Secure your mysql database server.
Run this shell command on the server:
 shell>mysql_secure_installation  
This is an important script. It allows you to set the root password which we need to do and other tasks like removing anonymous access.
You can re-perform step 4 but this time remember to supply your password:
 shell>mysql -u root -p<password>  

Step 6

Check that you can connect to the mysql database from the same machine.
 shell>mysql -u root -p<password>  
Then run a this query:
 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'  
   IDENTIFIED BY PASSWORD 'your_password'  
   WITH GRANT OPTION;  
The query allows remote access from any location where the user is root and the password. You can replace % with a hostname or ip address. You can also use it as a wildcard.
Examples:
'%.sampldomain.com'
'192.168.10.%'
Remember to flush privileges:
 FLUSH PRIVILEGES;  

Step 7

Use mysql workbench to connect to the mysql database server.

Additional Notes

Shut Down the mysql database server.
If you have root access:
 service mysqld stop  
If you do not have root access:
 sudo service mysqld stop  

Leave a comment if you feel that there is anything that I have left out.