GRANT ALL PRIVILEGES ON db_name.* to 'new_user'@'hostname' IDENTIFIED BY 'password';

Simple bash script to remove duplicate rows from a table:

#!/bin/bash
echo "Remove duplicate records from tbl_duplicates"
mysql -uusername -ppassword database -e "DELETE n1 FROM tbl_duplicates n1, tbl_duplicates n2 WHERE n1.col_id > n2.col_id AND n1.col_duplicate = n2.col_duplicate;"

Terms mentioned:

tbl_duplicates – table to remove duplicates from
col_id – column ID
col_duplicates – a column which contains duplicate data
> – this keeps the row with the lowest id

If you are having an issue with a slow MySQL connection to a remote database then perhaps the following can help.

Essentially my website was taking 7-8 seconds to load and the Apache logs weren’t giving me much to go on. Using a website page test tool such as http://www.webpagetest.org I could see that the site itself was performing well but there was a 7 second server wait time.

This lead my on to the assumption that the MySQL connection was causing the issue. To test, I simply created a new database locally and updated the config files. What do you know…the website loads instantly. This confirmed my assumption.

So I set about figuring out why the MySQL connection was so slow even though the remote server was within the same network. The fix is very simple:

Edit the hosts file on the remote MySQL server:

vim /etc/hosts

Add in the following line:

xxx.xxx.xxx.xxx original.host.com original 

* xxx being the IP address

The original host being the server you are connecting from. Go back and check your website hopefully everything is back to full speed.

The main reason for adding the entry to the hosts file is due to not having reverse DNS set up. Once the entry has been added manually this will do the lookup manually rather than querying the DNS.

More useful information can be found here: http://etwiki.cpanel.net/twiki/bin/view/11_32/WHMDocs/SetupRemoteMySQL