VPS and Dedicated server customers with root access can change their MySQL settings in the my.cnf file. To do this, you simply log in the server and edit the my.cnf file using Nano. This article will explain the steps to view and edit your my.cnf file.
How to view MySQL settings in the my.cnf file
- Login to your server via SSH
- To view the MySQL my.cnf settings type the following:
cat /etc/my.cnf
Â
The contents of the my.cnf file will display similar to the snapshot to the right.
Editing the MySQL my.cnf file
- Login to your server via SSH
To edit the MySQL settings with nano type the following:
nano /etc/my.cnf
Note! There are several editors you can use in shell. Nano, Vim, or Emacs. This article will focus only on Nano.
Find the line to edit. For example, if you want to edit the max_connections, find the following line.
max_connections=100
To increase the max_connections to 110 change it to the following.
max_connections=110
- Enter Ctrl + O to “WriteOut” or save the settings.
- Then Ctrl + X to exit.
- Restart MySQL by typing the following.
service mysql restart
Now the max_connections is increased to 110 instead of 100.
Viewing the MySQL variables through shell.
- Login to your server via SSH
Type the following command.
mysqladmin variables
The variables will display similar to the snapshot to the right.
Viewing MySQL variables in PhpMyAdmin
- Log into your phpMyAdmin
Click the Variables tab at the top right.
PhpMyAdmin will list all the variables and there settings on your server..
nice tutorial sir.. i like that
I use:
mysqladmin -u db_user -pPASSWORD variables;
Hi,
I have made the bind address as 0.0.0.0 and can also see that my security group in AWS EC2 instance has outbound access for port 3306. Still i get connection failure with db. Any probable reasons plz ?
Regards,
Anurag
Hello Anurag,
Thank you for contacting us. I recommend contacting Amazon, so they can help you review the server logs for records of connections, or failures.
Thank you,
John-Paul
Nice article.Thanks! I done for my mysql 🙂
-bash and command not found error is coming when i m using this command in the ssh console plz help for this and thank you in advance 🙂
Hello vivek,
While looking at the commands, I did notice a typo in one of them. It should read as below:
I have made the change on the article to display correctly. It was wrong for a time, it seems. Thank you for finding that!
If that is not the command giving you the error please let us know which one is giving you trouble as I do not see any others that should give an error.
Kindest Regards,
Scott M