Archive for the ‘MySQL’ category

Mysql – Allow remote connections and create a user

November 21st, 2013

To enable logins from other locations you need to:

1) ssh to the server and edit the my.cnf (located in /etc/my.cnf or /etc/mysql/my.cnf
2) Add the line “bind-address=0.0.0.0”. This allows for localhost and remote connections.
3) Create or Modify a user to have permissions to connect to the server. Assuming the user already exists, connect to the mysql server as an admininistrator. Then type one of/or all of the following (depending on need)

GRANT ALL ON *.* TO username@'%';
GRANT ALL ON *.* TO username@'localhost';
GRANT ALL ON *.* TO username@'X.X.X.X';

Were % is ALL source IP address’s, localhost is only from the localhost, X.X.X.X is allow user from this source IP address.

This grants that user access to ALL databases on the server. To limit access to only certain databases then replace *.* with

databases.nameofdatabase.

You might also need to create/modify the user:

create user username@'X.X.X.X' IDENTIFIED BY 'userpassword';
create user username@'%' IDENTIFIED BY 'userpassword';
create user username@'localhost' IDENTIFIED BY 'userpassword';

Note: while each user is the same name and password, each is a separate entry in the mysql.users table because of the source of the connection for the user.

4) Make sure the firewall allows for remote connection to mysql on port 3306.

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

Linux – Mysql dump script that can be used with cronjob

October 8th, 2010

The following is a script that will log into mysql and make dumbs of the db’s while placing it in a dated folder.

The first part is the script that will create the folder, we will call it mkdatedir.sh

#!/bin/bash

if [ $1 ] ;  then
dir="$1";
else
dir="."
fi

export DATE=`date +%d:%m.%H:%M`
echo "$dir/$DATE"
mkdir "$dir/$DATE"

place this script in your desired folder. We will put it for now in “/var/lib/mysql/backupscripts/mkdatedir.sh”.

Once done create the backup script itself. We will call it “dumpdbs.sh”

The code is:

#!/bin/bash

# backup database script .

export MYSQL_PWD=PUTMYSQLADMINROOTPASSWORDHERE
#Location to dump backup, go into it
cd /var/lib/mysql/dumps
#calling on the creation directory script, create folder
export DATE=`/var/lib/mysql/backupscripts/mkdatedir.sh`
#go into folder
cd $DATE

for i in `mysql -u admin -e 'show databases'|grep -v Database` ; do
/bin/nice -n 10 mysqldump -u admin --opt $i | gzip -6 -> "$i.dump.gz";
done

Notice we used admin as the login here, you may need to use root. Once you tested and made sure this works. You can create a cron to backup on a regular basis.

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

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

Plesk – phpMyAdmin file not found 8443/domains/databases/phpMyAdmin/export.php

June 24th, 2010

If you try and export a DB in plesk and get the error message:

File not Found 
can’t find the file at https://xx.xx.xx.xx:8443/domains/databases/phpMyAdmin/export.php.

Then chances are that the memory_limit for php is too low for the operation. The simple solution is to SSH into the server and do the following:

Go to : /usr/local/psa/admin/conf/
Edit : php.ini
and increase memory_limit, a good level is 512MB 
restart plesk control panel (/etc/init.d/psa stop/start)

Once you restart the plesk control panel test the export once more. It should now work

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