How to add a user to PostgreSQL database
Following are the steps to add a user ‘testuser ‘ to the PostgreSQL database ‘testdb’ and grant access to that database;
First you need to make sure that the user is already existing in the server.
# grep testuser /etc/passwd
Else you need to create a user by type the following commands:
# adduser testuser
# passwd testuser
su - postgres
/usr/local/pgsql/bin/psql template1
/usr/local/pgsql/bin/psql -d template1 -U postgres (This command will result into the PostgreSQL command prompt)
Welcome to psql , the PostgreSQL interactive terminal.
template1=#
Now you need to add the user ‘testuser’ and give access to the database named ‘testdb’.
Follow the steps given below:
template1=# CREATE USER tom WITH PASSWORD ‘testPassword’;
template1=# CREATE DATABASE testdb;
template1=# GRANT ALL PRIVILEGES ON DATABASE testdb to testuser;
then quit from the template1 prompt:
Now you need to login as user ‘testuser’;
@ ~]$ su - testuser
Password:
[testuser@ ~]$ /usr/local/pgsql/bin/psql -d testdb -U testuser
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
testdb=>
Tags: database, PostgreSQL
Warning message in PhpMyAdmin after upgrading MySQL version to 5 In cPanel server.
In cPanel server after upgrading MySQL version to 5, PhpMyAdmin shows a warning message as given below
Your PHP MySQL library version 4.1.22 differs from your MySQL server version 5.0.45
CPanel’s internal PHP is not compiled with MySQL 5. That is why you are facing this issue. In order to fix this, perform the following steps.
1) Run the script /scripts/makecpphp
2) Copy the PHP binary inside /var/cpanel/3rdparty//bin/ to /usr/local/cpanel/3rdparty/bin/
That will fix the problem.
Tags: cPanel server., mySQL, phpMyAdmin
MySQL Socket Error in phpMyAdmin
While accessing phpMyAdmin, you may get the following error.
#2002 - The server is not responding (or the local MySQL server’s socket is not correctly configured)
This is due to the missing socket file in the location /tmp.
The socket path which is specified in the phpMyAdmin configuration file is /tmp/mysql.sock.
$ vi /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php
cfg['Server']['socket'] = ‘/tmp/mysql.sock’;
If mysql.sock is missing in /tmp, then create a link to the mysql.sock file in /var/lib/mysql.
$ ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
There is also another fix for this issue.
1. Open the phpMyadmin config file “config.inc.php”.
vi /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php
2.Locate the line:
$cfg['Servers'][$i]['host'] = ‘localhost’;
3.Replace ‘localhost’ with ‘127.0.0.1′ and save.
$cfg['Servers'][$i]['host'] = ‘127.0.0.1′;
Tags: mySQL, phpMyAdmin, Socket Error
MySQL: got error 28 from server handler
This error means no space left on hard disk. If you get his error, you need to check all filesystems where MySQL operates.
a) Stop mysql server
# /etc/init.d/mysql stop
OR
# /etc/init.d/mysqld stop
b) Check filesystem and /tmp directories:
$ df -h
$ cd /tmp
$ df -h /tmp
c) Remove files from /tmp to free up space:
d) Look into /var/log directory and remove or compress logs file.
e) Use myisamchk command to check and repair of ISAM table:
# cd /var/lib/mysql
# myisamchk
f) Increase disk space (add new hard disk or remove unwanted softwares)
g) Start the mysql server:
# /etc/init.d/mysql start
OR
# /etc/init.d/mysqld start
Tags: error 28, mySQL
How to change the storage engine to InnoDB
1.Edit the /etc/my.cnf file and look for this line:
and comment it out:
2.Add the following entry in the /etc/my.cnf file
default-storage_engine = InnoDB
3. Save the file, and restart mysql
/etc/rc.d/init.d/mysqld restart
4. You can use the following command to check the “storage engine” used in the server.
mysqladmin variables | grep storage_engine
You will see the storage engine as “InnoDB”.
# mysqladmin variables | grep storage_engine
| storage_engine | InnoDB
Tags: InnoDB, storage engine
