Category Archives: how-to

how-to, postgresql

Connecting to a PostgreSQL server remotely through pgAdmin

It is a 3 step process to connect to a PostgreSQL server remotely through pgAdmin3.

Note: These steps are tested on Ubuntu 16.04 and PostgreSQL 8.4.

  1. You have to make PostgreSQL listening for remote incoming TCP connections because the default settings allow to listen only for connections on the loopback interface. To be able to reach the server remotely you have to add the following line into the file /etc/postgresql/8.4/main/postgresql.conf:

    listen_addresses = ‘*’

  2. PostgreSQL by default refuses all connections it receives from any remote address, you have to relax these rules by adding this line to /etc/postgresql/8.4/main/pg_hba.conf:

    host all all 0.0.0.0/0 md5

    This is an access control rule that let anybody login in from any address if he can provide a valid password (the md5 keyword). You can use needed network/mask instead of 0.0.0.0/0 .

  3. When you have applied these modifications to your configuration files you need to restart PostgreSQL server. Now it is possible to login to your server remotely, using the username and password.

To start PostgresSQL server you would do sudo /etc/init.d/postgresql stop and sudo /etc/init.d/postgresql start.

 

Standard
how-to, MySql

Recover MySQL root Password

It is only common to forget the database password. However, we can recover MySQL database server password following five easy steps.

  1. First step is to stop the MySQL server process.
    $> sudo /etc/init.d/mysql stop
  2. Start the MySQL (mysqld) server/daemon process with the --skip-grant-tables option so that it will not prompt for the password.
    $> sudo mysqld_safe --skip-grant-tables &
  3. Then, connect to mysql server as the root user.
    $> mysql -u root
  4. Now that we’re in as the root user, setup new mysql root account password i.e. reset mysql password.
    5. mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit
  5. Finally, exit and restart the MySQL server. 
    $> sudo /etc/init.d/mysql stop
$> sudo /etc/init.d/mysql start
$> mysql -u root -p

With this we would’ve successfully changed the password.
 

Standard