Max_connection in mysql

Hi,

I have rented a dedicated server(linux+mysql+php+phpmyadmin+directadmin) and this is the first time that work with linux,on this server i have 2 databaces,please let know that how can check the default setting for max_connection on these dbs and how can change it?
MOSTAGHASSIAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Zephyr ICTCloud ArchitectCommented:
Hey MOSTA,

You can run
show variables like "max_connections";

Open in new window

in the MySQL CLi or via phpMyAdmin
That should give your something like this:
+-----------------+-----------------+
| Variable_name   | Value |
+-----------------+-----------------+
| max_connections | 250   |
+-----------------+-----------------+

Open in new window


You can change this value in the /etc/my.cnf file or via the command:

set global max_connections = 300;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MOSTAGHASSIAuthor Commented:
Hi;

Thanks, the first command i must run it on root of ssh?
MOSTAGHASSIAuthor Commented:
I think i must run it in phpmyadmin,but how?
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Zephyr ICTCloud ArchitectCommented:
You can run it either in the mysql command line (mysql -u user -p <dbname>) or in phpMyAdmin.

In phpMyAdmin click on the database you want to query, just choose any table for now, in the right window select SQL, you should be able to paste queries there, just type/paste the query from above.
MOSTAGHASSIAuthor Commented:
Ok,i try in phpmyadmin,but this max_connection is for whole of db,why i must a table? when i select my db can i run the command in sql box?
MOSTAGHASSIAuthor Commented:
I run it for db and the result is:


Variable_name
Value
max_connections
151
Dave BaldwinFixer of ProblemsCommented:
On my copy of phpMyAdmin, 'max connections' is listed under Variables which is one of the buttons at the top of the first screen.
MOSTAGHASSIAuthor Commented:
Each table has max_connection for itself?
Dave BaldwinFixer of ProblemsCommented:
No, that is for the whole server.
Dave BaldwinFixer of ProblemsCommented:
MOSTAGHASSIAuthor Commented:
spravtek, for changing its deafult to 300 i run your command (set global max_connections = 300;)in sql box but got this error below:

#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation
Zephyr ICTCloud ArchitectCommented:
Yes, you might need to use the CLI then, login with your root account (if this account has superuser rights on the MySQL installation that is)...

mysql -u root -p

Open in new window

Zephyr ICTCloud ArchitectCommented:
Also, the 300 was just an example, you'll have to make sure that setting is the correct one for you ... This depends on the amount of RAM your system has and how many is used per connection.

Some more info on this here
MOSTAGHASSIAuthor Commented:
ok ,but please let me know about your command:

mysql -u root -p

-u is username of what?
-p is password  of what?

at this time im in root of ssh:
[root@Server ~]#
Zephyr ICTCloud ArchitectCommented:
username of the user that has global rights on MySQL, usually this is the root user.
The password is the password of this user, it usually is a separate password than that of the system root user...
MOSTAGHASSIAuthor Commented:
I used in command line :

mysql -u root -p        (-u is jamsid  which has created 2 databases and -p its pass)

there are lot of ouput like:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
The following groups are read: mysql client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.


now,how can access to the first db and how can change the max_connection?
Zephyr ICTCloud ArchitectCommented:
The max_connection is a global setting, you don't need to set this for each database.

What is the result of the command
show variables like "max_connections";

Open in new window

?
MOSTAGHASSIAuthor Commented:
The result is :


[root@Server ~]# show variables like "max_connections";
-bash: show: command not found
Zephyr ICTCloud ArchitectCommented:
MOSTA, you're not logged in mysql, you need to be logged in mysql before running that command!

1. Log into mysql: mysql -u jamsid -p <enter>
2. once logged in, run the command:show variables like "max_connections"; <enter>
3. Change the max_connections with: set global max_connections = 300; <enter>
4. Exit mysql by typing "exit"

Via phpMyAdmin, are you logged in with jamsid or root? Probably a user that doesn't have the necessary rights.
MOSTAGHASSIAuthor Commented:
I could connect to mysql>

and see the number of connections (151) but for changing the number the result is:


mysql> set global max_connections = 300;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation



--In phpmyadmin also i enter as user jamsid that have created 2 dbs but when run the command for changing the number the error is:

#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation
Zephyr ICTCloud ArchitectCommented:
You'll have to login with root user ... It has either the same password as the root for system or another one ... Do you know the password?
mysql -u root -p 

Open in new window

MOSTAGHASSIAuthor Commented:
spravtek,i entered in Directadmin and saw the Privileges for jamsid on dbs then see in 'modify privileges' all parameters are 'yes' but 'Grant' is no is this the problem? do i change it to 'yes' ?
Zephyr ICTCloud ArchitectCommented:
You can change it, it should be no problem, but I can't say it will be enough, is there an option to make the user a superuser for mysql?

Anyway, just enable it and test again...
MOSTAGHASSIAuthor Commented:
I changed and tested but there is the same error,in Directadmin i enter as 'admin' then see that  the 'jamsid' is in  'list admin user' there is also another option-->'Create Administrator'
Zephyr ICTCloud ArchitectCommented:
I think your best bet, especially regarding global settings, is to use the command line way of configuring this ... Not phpMyAdmin or Directadmin

Isn't this working? Can you not login with root user in mysql?
MOSTAGHASSIAuthor Commented:
i was in root in ssh then the result is:

 
[root@Server ~]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

for password i entered the password of root for ssh and another time the password of 'admin' for going to Direcadmin but for both the same error as above.

for going to mysql> it accept only 'jamsid' user that has created the 2 dbs.
Zephyr ICTCloud ArchitectCommented:
But with jamsid you don't have enough rights, you also had an error with access denied ... So there is probably a root user configured with a different password than the the ssh root user... Didn't you get this from the provider?

With admin for Directadmin you also don't have the necessary rights right?

You could try to login into mysql with this admin user:

mysql -u admin -p <enter>
MOSTAGHASSIAuthor Commented:
i checked the  mysql -u admin -p <enter>  but the same error.

I have gotten 2 password from my provider:

1- the pass for entering to ssh as root.

2-the pass for entering to Directadmin as admin.
MOSTAGHASSIAuthor Commented:
in phpmyadmin above  i cannot see the privileges tab,do you have this tab on your phpmyadmin?
Zephyr ICTCloud ArchitectCommented:
Strange ... Creating databases is possible since your jamsid user has the necessary rights, but it doesn't have enough rights to set global settings.

You could try resetting the root password for mysql ...

Another option is to change the setting in /etc/my.cnf or /etc/mysql/my.cnf
Check what is in the file with "cat /etc/my.cnf" or "cat /etc/mysql/my.cnf"
Zephyr ICTCloud ArchitectCommented:
On phpMyAdmin this is only possible per database I think ... I see the privileges tab for each database.

But you can run the global command on any database, just select one and click on "query" then run the command: show variables like "max_connections";

Or try to change it, probably will error out again, but worth a shot.
Zephyr ICTCloud ArchitectCommented:
Sorry, there is a way to add/change users in phpMyAdmin, my eyes are getting tired I guess...

Click on the house symbol, then click on users, pick a user and make sure "SUPER" is selected under Administration for this user, then log out/in again and try the command again.
MOSTAGHASSIAuthor Commented:
Sorry,i tired you.

I cannot see users when go to home.

If you like this is not emergency we can continue tomorrow.
Zephyr ICTCloud ArchitectCommented:
You probably do not have the necessary rights to change this, I see no other option than to make sure you have a user that can change this. Maybe try contacting your provider to ask them if MySQL was set up with a root user and if they have the password for it.

We can continue tomorrow, no problem! Thanks!
MOSTAGHASSIAuthor Commented:
Thanks, i really appreciate you.
Zephyr ICTCloud ArchitectCommented:
No problem, glad to help ;-)
MOSTAGHASSIAuthor Commented:
Hi spravtek;

I contacted my provider and they say that this change must be done in ssh with changing in /etc/my.cnf


-[mysqld]
-max_connections=500

and the restart mysqld

-service mysqld restart
MOSTAGHASSIAuthor Commented:
Of course i don't know how can i access to that file and make change.
Zephyr ICTCloud ArchitectCommented:
I've mentioned this a few answers up ;-)


Another option is to change the setting in /etc/my.cnf or /etc/mysql/my.cnf
Check what is in the file with "cat /etc/my.cnf" or "cat /etc/mysql/my.cnf"

Now, to actually edit the file you either use "vi" or "nano", vi will probably be installed and nano probably not, it basically comes down to:

1. open the file like this: vi /etc/my.cnf
2. scroll (use arrow keys) down to where you need to change the setting
3. type "i" on your keyboard to enable editing
4. change the number
5. press "esc" on your keyboard and type "wq" to write the changes and exit the file.
MOSTAGHASSIAuthor Commented:
Thanks,by first command for opening the file i get this:

[root@Server ~]# vi/etc/my.cnf
-bash: vi/etc/my.cnf: No such file or directory
Zephyr ICTCloud ArchitectCommented:
There should be a space between "vi" and /etc/my.cnf
If that still doesn't work, try to find the file in /etc/mysql/my.cnf .

If you succesfully changed the file you should also restart mysql ... Forgot to mention that.
MOSTAGHASSIAuthor Commented:
the result:
~
~
~
~
~
~
~
~
~
"/etc/my.cnf" [New File]
Zephyr ICTCloud ArchitectCommented:
That means the file is not in that place ...

Go to my first mentioning of the files and do that, like this:

cat /etc/mysql/my.cnf

Open in new window


Tell me the result

Also, close this file by first clicking "esc" then type "q!"
MOSTAGHASSIAuthor Commented:
[root@Server ~]# cat /etc/mysql/my.cnf
cat: /etc/mysql/my.cnf: No such file or directory
Zephyr ICTCloud ArchitectCommented:
Did you type it like that with the ":" or is this just because of typing it here? Because the command is without the ":"

Try a few more:
cat $MYSQL_HOME/my.cnf

Open in new window

cat ~/.my.cnf

Open in new window


We need to find the file first ...
Zephyr ICTCloud ArchitectCommented:
Or try this:

mysql --help | grep "Default options" -A 1

Open in new window

MOSTAGHASSIAuthor Commented:
i typed without ':'

and try the another commands.
MOSTAGHASSIAuthor Commented:
the sesult for all commands:


[root@Server ~]# cat $MYSQL_HOME/my.cnf
cat: /my.cnf: No such file or directory

[root@Server ~]# cat ~/.my.cnf
cat: /root/.my.cnf: No such file or directory

[root@Server ~]# mysql --help | grep "Default options" -A 1
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
Zephyr ICTCloud ArchitectCommented:
Hard to find...

Ok, let's try here:

Cat /usr/etc/my.cnf
MOSTAGHASSIAuthor Commented:
NO, not found,it seems that command has problem,


[root@Server ~]# Cat /usr/etc/my.cnf
-bash: Cat: command not found
Zephyr ICTCloud ArchitectCommented:
Change the capital C to a small c and try again

cat /usr/etc/my.cnf

I'm on mobile now so sometimes small errors in writing are possible
MOSTAGHASSIAuthor Commented:
[root@Server ~]# cat /usr/etc/my.cnf
cat: /usr/etc/my.cnf: No such file or directory
Zephyr ICTCloud ArchitectCommented:
Try this:

Find / -name my.cnf
MOSTAGHASSIAuthor Commented:
successful

i changed F to f

the result:

[root@Server ~]# find / -name my.cnf
/usr/local/directadmin/conf/my.cnf
Zephyr ICTCloud ArchitectCommented:
Ok, strange but let's check this file:

cat <directory we found>
MOSTAGHASSIAuthor Commented:
[root@Server ~]# cat <directory we found>
-bash: syntax error near unexpected token `newline'
Zephyr ICTCloud ArchitectCommented:
replace directory we found with the result from the find command

I cant copy past it right now
Zephyr ICTCloud ArchitectCommented:
replace directory we found with the result from the find command

I cant copy past it right now
MOSTAGHASSIAuthor Commented:
[root@Server ~]# cat </usr/local/directadmin/conf/my.cnf>
-bash: syntax error near unexpected token `newline'
MOSTAGHASSIAuthor Commented:
[root@Server ~]# cat /usr/local/directadmin/conf/my.cnf
[client]
user=da_admin
password=************
MOSTAGHASSIAuthor Commented:
is it possible that we have several my.cnf  file?
Zephyr ICTCloud ArchitectCommented:
If there were more my.cnf files then the find command should of found them ...

You can try adding this to the my.cnf file in /usr/local/directadmin/conf/  place it under what is already there.

[mysqld]
max_connections=500

Open in new window

MOSTAGHASSIAuthor Commented:
[root@Server ~]# /usr/local/directadmin/conf/
-bash: /usr/local/directadmin/conf/: is a directory
Zephyr ICTCloud ArchitectCommented:
like before with the vi command remember

vi /usr/local/directadmin/conf/my.cnf

Open in new window

MOSTAGHASSIAuthor Commented:
[client]
user=da_admin
password=*****
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"/usr/local/directadmin/conf/my.cnf" 3L, 41C
MOSTAGHASSIAuthor Commented:
spravtek,i make you tired ,please let me that contact with provider because they belive that my.cnf file is in etc/my.cnf maybe there is a problem in this regard,thanks.
Zephyr ICTCloud ArchitectCommented:
Ok, ... You can, no problem...

If you still have issues let me know!
MOSTAGHASSIAuthor Commented:
I appreciate realy  from your efforts in this qustion,i close it but when they solve it i announce you .
Zephyr ICTCloud ArchitectCommented:
Ok, sounds good ... I believe we were close though ;-)

Good luck!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux

From novice to tech pro — start learning today.