How to Edit the MySQL my.cnf File Updated on August 16, 2021 by InMotion Hosting Contributor 2 Minutes, 11 Seconds to Read In this guide, we’ll show you how to make changes to your database settings in the MySQL my.cnf file. For example, you may need to increase the number of max_connections or the query_cache_size value for your server. To do this, you simply log in the server via SSH and edit the my.cnf file using an editor such as Nano. This article will explain the steps to view and edit your my.cnf file. We’ll also show you how to view your MySQL variables through SSH then how to access them in phpMyAdmin, where they can be edited. You will need root access to our VPS or Dedicated server to follow this guide. How to View Mysql Settings in the my.cnf FileEditing the Mysql my.cnf FileViewing the Mysql Variables Through ShellViewing MySQL Variables in phpMyAdmin Scalable VPS Infrastructure, Fully Managed When shared hosting can't handle your traffic, VPS delivers dedicated resources that scale with demand. Our team manages the technical complexity while you manage your business. NVMe Storage High-Availability Ironclad Security Premium Support VPS Hosting Don’t have time to read the article. Watch our video tutorial here. How to View Mysql Settings in the my.cnf File Login to your server via SSHTo view the MySQL my.cnf settings type the following:cat /etc/my.cnfThe contents of the my.cnf file will display similar to the snapshot above. 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.cnfThere 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=100To increase the max_connections to 110 change it to the following.max_connections=110Enter Ctrl + O to “WriteOut” or save the settings.Then Ctrl + X to exit.Restart MySQL by typing the following.service mysql restartNow 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 variablesThe variables will display similar to the snapshot above. Viewing MySQL Variables in phpMyAdmin Log into your phpMyAdmin.Click the Variables tab at the top right. Then PhpMyAdmin will list all the variables and their settings on your server.If you want to edit the values, you should edit the my.cnf file as described above. Now you know how to edit your database settings in the MySQL my.cnf file and view database variables. For more guides check out our full section on Working with Databases. Share this Article IC InMotion Hosting Contributor Content Writer InMotion Hosting contributors are highly knowledgeable individuals who create relevant content on new trends and troubleshooting techniques to help you achieve your online goals! More Articles by InMotion Hosting Related Articles Database Optimization: Tips Using MySQL Tuner How to Check and Repair a Database in phpMyAdmin Create a blank database How to Create a MySQL Database Using CLI & cPanel How to Connect to a Database with MySQL Workbench Setting up a Remote MySQL Database Connection MySQL 1064 Error: You have an error in your SQL syntax Using MySQLi to INSERT Data into a Database How to get PostgreSQL on a VPS / Dedicated Server What is MariaDB?
Hello – the location of the .cnf file for MySQL is not always in the “MySQL” folder. It can be referenced/located like $MYSQL_HOME/my.cnf [datadir]/my.cnf, or ~/.my.cnf.
Using the Terminal through my channel, I increased the max_allowed_packet, entered CTRL-O, entered CTRL-X — nothing happened. Clicked RETURN on my keyboard and get an error: error writing /etc/my.cnf: permission denied. Am I not allowed to increase the allowed packets? I was increasing them because I have this error in my error log: PHP Warning: Error while sending QUERY packet. … wp-includes/wp-db.php on line 2030
Hello, and thanks for contacting us. You may need to do so in WHM as root but you are able to change PHP.ini settings. You need to check file permissions to ensure nothing odd changed.
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
-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: mysqladmin variables 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
