Linux Tutorial | Matt Cooper - Open Source Support
Linux Tutorial | Matt Cooper - Open Source Support
MySQL Tutorial

Mysql – Create new user and assign permissions to a database

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

October 18, 2018by Matt Cooper
FacebookTwitterPinterestGoogle +Stumbleupon
Bash Scripting, MySQL Tutorial

MySQL – remove duplicate rows leaving one copy – bash scripting

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

April 9, 2014by Matt Cooper
FacebookTwitterPinterestGoogle +Stumbleupon
MySQL Troubleshooting

Remote MySQL Database – Slow Connection

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

 

 

 

August 6, 2013by Matt Cooper
FacebookTwitterPinterestGoogle +Stumbleupon

About me

Hi, I'm Matt Cooper. I started this blog to pretty much act as a brain dump area for things I learn from day to day. You can contact me at: matt@linuxtutorial.co.uk

Recent Comments

  • Andrew on Export list of Amazon EC2 Instances to CSV
  • Matt Cooper on Proxmox – add a second hard drive to node for Backups
  • karis on Proxmox – add a second hard drive to node for Backups
  • Matt Cooper on Remote MySQL Database – Slow Connection
  • Matt Cooper on Bash script to send public IP address to Email

Categories

  • AdvancedTomato
  • Amazon AWS
  • Amazon Linux
  • Amazon S3
  • Apache
  • Apache Kafka
  • Backup Tutorial
  • Bash Scripting
  • Centos Tutorial
  • CloudFlare
  • Command line Tutorial
  • CPanel Troubleshooting
  • CPanel Tutorial
  • Debian Troubleshooting
  • Debian Tutorial
  • DKIM
  • Docker
  • EC2
  • ESXi
  • Faildows
  • Google Adwords
  • Google Analytics
  • Google Chromebook
  • Google Mail
  • graylog
  • IAM
  • imapsync
  • iRedmail Tutorial
  • ISPConfig3 Tutorial
  • Java
  • ldap
  • letsencrypt
  • MyDNS
  • MySQL Troubleshooting
  • MySQL Tutorial
  • Nest Install
  • Netbeans
  • Nginx
  • Nginx Troubleshooting
  • openssl
  • PCI Compliance
  • Percona
  • PHP
  • Plex Media Server Tutorial
  • Postfix
  • Proxmox Tutorial
  • Pure FTPd
  • Resourcespace Tutorial
  • Route 53
  • Rsync Tutorial
  • Security
  • Smoothwall Troubleshooting
  • Smoothwall Tutorial
  • SSH
  • tcpdump
  • Thunderbird
  • Tomcat Troubleshooting
  • Ubuntu Tutorial
  • Uncategorized
  • Unison
  • vmware
  • vzdump
  • WHM
  • Wireshark
  • Wordpress Troubleshooting
  • WordPress Tutorial

“See, you not only have to be a good coder to create a system like Linux, you have to be a sneaky bastard too." Linus Torvalds

© 2017 copyright www.linuxtutorial.co.uk // All rights reserved