Allow remote access to MySQL server from VPS

Today I have job for backup mysql to other server that specialize on mysql backup online. The job is easy but I think it worth to share for someone out there who want to do same thing icon smile Allow remote access to MySQL server from VPS

I’ll use my vps on DigitalOcean and MySQL backup service from mysqlbackuponline.

DigitalOcean

On DO side the steps are :

1. Create database

2. Grant “select” access to the database you want to backup (don’t forget to flush privileges). ex :

grant select on dbname.* to username@’126.220.63.%’ identified by ‘password’;

3. Open firewall access to mysql port, like this :

iptables -A INPUT -i eth0 -p tcp –destination-port 3306 -j ACCEPT

Check if the rule is exist :

# iptables -L -n

Chain INPUT (policy ACCEPT)
target     prot opt source               destination
ACCEPT     tcp  –  0.0.0.0/0            0.0.0.0/0           tcp dpt:3306

4. Edit /etc/mysql/my.cnf or /etc/my.cnf (position depend on your server operating system)

Make sure “bind-address” section is right. For me comment the line is enough.

5. Restart MySQL server

Mysqlbackuponline

On Mysqlbackuponline side the steps are :

1. Login to mysqlbackuponline

2. Click on + Add New MySQL server

3. Enter all requested information

4. Test Connection

Make sure connection work well “Test connection succeeded”.

backup db Allow remote access to MySQL server from VPS

Test run the job :

11:27:43 AM
Started by a user
11:27:43 AM
Environment version 1.0.0.199
11:27:45 AM
Starting MySql backup…
11:27:45 AM
Using mode (MySQLTcpIpServer)
11:27:45 AM
Creating a backup of the database: backupdb
11:27:47 AM
Server version : 5.0.95
11:30:55 AM
Archived in 0.02 sec
11:30:55 AM
Archive size 66794 bytes
11:30:55 AM
The backup was saved successfully
11:30:55 AM
Backup state was saved (0.02 sec)
11:30:55 AM
Sending email…
11:30:56 AM
Email have been sent to alamster@gmail.com
11:30:56 AM
Job finished (Success)

Great service from mysqlbackuponline. Time to schedule it for daily backup.

How to set name server (ns) of your web hosting to a domain

You already buy a domain but forgot how to set name server or you switch to new hosting or you just bought a complete website and need to transfer to your hosting company then you need to know how to set name server.

Name Server Format

If you bought a hosting package (shared hosting or reseller hosting) then you should get email with details. Pay attention to these section :

(example below from certifiedhosting)

NEW ACCOUNT INFO
Domain:yourdomain.com
Username: username
Password: uniquepassword
Hosting Package: GEEK
Server IP: 205.196.23.70
Nameserver 1: DNS1.ICERTIFIED.NET
Nameserver 2: DNS2.ICERTIFIED.NET

Nameserver 1 and nameserver 2 is your friend.

Login to your Domain Registrar

I use namecheap as example. The interface is clean and easy to understand.

Click on “View” link near “number of domains in your account”.

set dns namecheap 1 How to set name server (ns) of your web hosting to a domain

Click Domain name followed by click “Transfer DNS to a Webhost”.

set dns namecheap 2 How to set name server (ns) of your web hosting to a domain

Choose “Specify Custom DNS Servers” and enter value of DNS Servers.

If your web hosting package list till 4 dns servers then you must enter all servers.

Click Save Changes.

Checking DNS Change

You can use intodns.com to track the change you made.

set dns namecheap 3 How to set name server (ns) of your web hosting to a domain

In general any changes to name server will be affected to all internet zone take time (3 days in average, no need to worry if you can not see anything during that moment).

 

Find out specific file (sql file) in vps

2014 is here and happy new year everyone icon smile Find out specific file (sql file) in vps . As new year come I want to do ‘clean up’ for some vps and get rid from unused or old file that no longer useful on my vps server. Since the content handle by many side I can not delete many file and only able to delete sql file. The problem is I can not remember where I put the file?

Linux come with ‘find’ command and I can concentrate on “public_html” folder :

$ find public_html -name \*.sql -print
public_html/polling/dbdbd.sql
public_html/polling/pollingdb.sql
public_html/polling/dbdb/bukudb2.sql
public_html/polling/dbdb/bukudb.sql
public_html/home/cometchat/plugins/filetransfer/uploads/bpk.sql
public_html/reservasi/Addons/dbReservasi.sql
public_html/adminphp/scripts/upgrade_tables_mysql_4_1_2+.sql
public_html/adminphp/scripts/create_tables.sql
public_html/adminphp/scripts/create_tables_mysql_4_1_2+.sql
public_html/Hanz/elfinder-2.0-rc1/php/MySQLStorage.sql
public_html/hanz2/fig/figdb2.sql
public_html/hanz2/fig_old/figdb.sql

Wow, at least I know that garbage is exist.

Time to work now.

P.S : other useful command

$ncdu

ncdu 1.7 ~ Use the arrow keys to navigate, press ? for help
— /home/working/public_html ——————————————————
/..
5.4GiB [##########]  public_html2014.tar.gz
2.9GiB [#####     ] /picture
2.2GiB [####      ]/cover                                                                                                                    292.9MiB [          ] /Hanz
246.3MiB [          ] /hanz2

ncdu : NCurses Disk Usage