How to change MySQL port

In this post we will see how to change MySQL instance port.
By default MySQL will be listening to port 3306. From security perspective it’s not a good idea to run your instance with default port. To change the port all you have to do is edit the configuration file.

Windows -> my.ini
Linux -> my.cnf

How to locate your configuration file?

In windows
-Open services.msc console. Look for your MySQL service
-Right click MySQL service and open ‘Properties’.
-Under ‘Path to execute’ you can find the my.ini location

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MySQL56

-Open the configuration file and search for port, by default its 3306

-I have changed it to 3333.

You have to restart MySQL service to make the changes effective.
Let’s check if MySQL instance is listening to 3333 port. To check the port you can use below command from MySQL client.

SHOW VARIABLES WHERE VARIABLE_NAME LIKE 'PORT';

In Linux

Configuration will be located in /etc/mysql/my.cnf. You have to open and follow the same process.

Note: Take backup of configuration file before making any changes

Cheers,
Naveen

How to check MySQL version in Windows

In this post I will show you different ways to get the version of currently installed MySQL in your machine.

-Using mysqladmin, run the below command (highlighted in red) in cmd

D:\>mysqladmin -u root -p version

-using MySQL client

-Using MySQL service properties (path in service properties in taken installation directory).

-Using MySQL version query

Select @@version (or) Select version()

 

Cheers,
Naveen

How to open MySQL session from any location in Windows

In this blog post I will show you how to open a MySQL session from any location using command prompt.
By default if you try to open a session, you will get below message.

The same command works from the bin directory. In my case path is <C:\Program Files\MySQL\MySQL Server 5.6\bin>.

The solution for this is simple, just go ahead and add bin directory path in system variable path.  Follow the below steps to make the entry

1) Go to ‘Computer’ properties and Select ‘Advanced system settings’.
2) Navigate to ‘Advanced’ tab and select ‘Environment Variables’.

3) Under system variables, look for ‘Path’ variable and click ‘edit’.

4) Now at the end of existing variable value use a semicolon (;) and without leaving space paste the bin directory path with a backslash (\) at the end.

I have highlighted (in red) the path below which was pasted in my case.

…..Doctrine extensions for PHP\;C:\Program Files\MySQL\MySQL Server 5.6\bin\

That’s it we are done. By adding this path we will be able to connect from any location. Open command prompt and now you can run MySQL session from any location. I tried it from D drive this time and it worked :-)

Hope this helps

Cheers,
Naveen