Posts Tagged ‘MySQL’

MySQL – Using 100% of CPU

July 20th, 2010

Sometimes you’ll notice that MySQL is using 100% or more of the CPU (more if you have multiple cores).

If you have a php based website take a look at the php.ini and see if the following is on or off:

[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent = Off

If it is ON, turn it off and restart apache and mysql. It will most likely resolve the issue. If it does not take a look at the slow logs and see if you can identify a table whos queries are taking longer then usual. To turn on this feature visit the following for instructions :

MySQL turn on Slow Logs

Once you Identify the table/tables that are slowing things down, you will most likely need to re-index the table to optimize the query. This should resolve the 100%+ issue.

if you found this helpful feel free to donate to:
ZEC: t1NQp1UuqQbmnXzazbLTSreS2AbaZpRBuTM
LTC: LZyNF1qkBUA7XFz83m5xwzGgmmj1owQn9d
BTC: 1PY95KFPTEJTR7f2NnSgaB6xB9pwDJkcJz

MySQL – Listen to localhost and network connections

March 26th, 2010

With MySQL you sometimes need to listen to localhost and network connections.

To allow it to do this is fairly simple.

Edit the file

"/etc/mysql/my.cnf"

and find the line bind-address. It should look like :

bind-address = 127.0.0.1  <-- this allows only localhost connections

To allow all types of connections, localhost, network and general, change it to:

bind-address = 0.0.0.0

If you want it to only connect from the local network

bind-address = 192.168.1.123

(or whatever the local address is )

to connect from the internet only, bind to the external IP.

You will still need to make sure that the user still has the proper permissions to connect to the mysql DB

if you found this helpful feel free to donate to:
ZEC: t1NQp1UuqQbmnXzazbLTSreS2AbaZpRBuTM
LTC: LZyNF1qkBUA7XFz83m5xwzGgmmj1owQn9d
BTC: 1PY95KFPTEJTR7f2NnSgaB6xB9pwDJkcJz

MySQL – Allow connections from other IP or local network

March 11th, 2010

To allow a connection to a MySQL db for a user from another source then localhost you would need to run the following:

GRANT ALL PRIVILEGES ON databasename TO username@'192.168.1.101'
 IDENTIFIED BY 'password';

Replace the required information with what you need( ie databasename, username, ip and password) but this will grant access to the db from that IP.

It is possible to use a wildcard for the IP OR domain (192.168.1.101 can be blah.sogoth.com or sogoth.com etc…). The wildcard is %. So the combination would be

192.168.1.% or 192.168.%.%

for a domain it is

%.sogoth.com or %.sogoth.% etc....

Once added you then need to run:

FLUSH PRIVILEGES;

This will allow the new user to connect to the proper DB.

if you found this helpful feel free to donate to:
ZEC: t1NQp1UuqQbmnXzazbLTSreS2AbaZpRBuTM
LTC: LZyNF1qkBUA7XFz83m5xwzGgmmj1owQn9d
BTC: 1PY95KFPTEJTR7f2NnSgaB6xB9pwDJkcJz