bash: /home/myuser/.ssh/authorized_keys: No such file or directory

Question: When I type in cat ~/.ssh/authorized_keys to a RHEL 7 server I get the following message: cat: /root/.ssh/authorized_keys: No such file or directory I'm going through the process of setting up a SSH to create a connection between BART and client Potgresql servers - to backup Postgresql databases. How can I fix this issue? Answer:You need to create the ssh directory for the first time and create the authorized keys file. Create the .ssh directory. First check if actually already exists mkdir ~/.ssh Set the right permissions for the new .ssh directory. Very important step chmod 700 ~/.ssh Create... Read more →

How to load csv data with quotes and ENCLOSED BY into MariaDB table

Question : I have a csv file , with 1 million rows and would like to import into a MariaDB table.I'm importing successfully but having a problem with a row where the "," is within a double quote. Example row: GenerationX,Rufus,Johnny,"Chat hype, Power crazy" i.e instead of getting 4 columns , I'm getting 5 , because the comma within the 4th column is splitting the data Could you give me an example of how to load the data and maintain the data integrity ? Answer: When loading data into a MariaDB table the optional : ENCLOSED BY can help you.... Read more →

MariaDB /tmp file filling with MAD file

Question: I've received an alert from a RHEL server with MariaDB installed. The alert refers to a MAD file filling the /tmp drive . What is going on? and how can I fix the mysql MAD file filling the /tmp drive? Answer: This is a common problem when under certain circumstances - CREATE & ALTER statements executed on InnoDB tables create a temporary table in mysql's tmpdir. To find out the current value check my.cnf or use SHOW VARIABLES LIKE "TMPDIR During the data load phase MariaDB creates a temporary table in /tmp - which consequently fills the /tmp The... Read more →

MariaDB Cheatsheet

--connect to a MariaDB through command line mysql --user=myuser --password=mypw --port=myportnumber --host=myservername --MariaDB port number used SHOW GLOBAL VARIABLES LIKE 'PORT'; --backup mysqldump -u myun -p database_name > /mariadb/backups/mydb171117.sql Note: you'll be prompted for the password --list indexes SHOW INDEX FROM mytable; --drop index DROP idx1 ON MYTABLE --list tables show tables; --list views SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW'; --list databases show databases; --list functions SHOW FUNCTIOn STATUS; --show function DDL SHOW CREATE FUNCTION func_name; --drop database drop database db1; --show columns show columns FROM MYTABLE; --get index information SHOW INDEX FROM MYTABLE; mysqlshow -k db_name... Read more →

How to know PostgreSQL is running or installed

Question: How do I know if PostgreSQL is running or installed on a Linux server ? Answer: There are some ways to check whether PostgreSQL is running or installed, but it will depend on how PostgreSQL was installed . It pays to ask the user , how PostgreSQL was installed. Some different methods of PostgreSQL installation & configuration could include: -Installed from rpm packages and started by init script -Installed \ running but the installation is not associated to the default PATH or even the default port -Installed from rpm packages but not set to start As a starter these... Read more →

Notes on MongoDB BI Connector

These are my notes on installing the MongoDB BI Connector. The MongoDB Connector for Business Intelligence (BI) supports users to generate SQL queries against MongoDB databases. Typical client programs include Qlik,Tableau and Excel Details will depend on the overall architecture required. Typically I install MongoDB Connector on a dedicated server - to support wider usage ==Place the executable in /usr/bin mongodrdl mongosqld mongotranslate ==Place the cfg file in /usr/bin/mongobi - mongosqld.conf ===To start it from the command line mongosqld --mongo-uri "mongodb://MY_SERVER:27017" --auth --mongo-username MY_USER --mongo-password MY_PASSWORD --mongo-authenticationSource MY_AUTH_DB ===To start mongosqld with a configuration file (see example of basic configuration... Read more →

Top 10 Yoda tips for the Linux command line

Master these Linux commands and you will feel the Force or become the Linux Yoda . (or even just your job done more efficiently) I don't know anything about myprogram - how can I find out? 10 "info myprogram" or 9 "man myprogram" How can I find myprogram ? 8 "locate -i foo" or 7 "whereis foo" How can I view myprogram ? 6 "less foo" or 5 "emacs foo" How can I view the program's help ? This applies to most programs 4 myprogram "-h" or "--help" . How can I navigate further into a directory? 3 "cd ./mydirectory"... Read more →

How to use pgbench for PostgreSQL benchmark testing

Pgbench is used for Postgresql benchmark testing. Pgbench iterates through a bunch of SQL statements . There are various configurables such as sessions (single or multiple) and clients. The pgbench approach is based on TPC-B . The TPC-B benchmark focuses on benchmarking as opposed to OLTP type testing. The measurement is based on transactions per second. In other words - you'll define how many transactions to execute per client and this will result in a rate per second. There is a default test framework – which involves 5 SELECT , UPDATE, INSERT commands per transaction- there are options to customise... Read more →

How to clear Postgresql database cache

Question: In SQL Server there are two commands which clear down the data and query caches - DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS. Are there similar commands in PostgreSQL hosted on Linux? I want to clear down datasets from memory cache to allow query performance tuning. Answer: If you'd like to view what is in the buffer cache - pg_buffercache will present to you what is in the cache. Of course , pg_buffercache needs to be installed. It's a complicated data set and requires some knowledge to interpret. Unfortunately - there isn't a straightforward way of clearing down the database cache... Read more →

How to calculate the PostgreSQL database size

Question : How can I calculate the size of a PostgreSQL database using psql?I want to use the command line and execute a sql statement for various checks? Answer: An easy way to derive the Postgresql database size is to use the pg_database_size() function. The pg_database_size() reads the database name and returns the database size in bytes. For example : SELECT pg_database_size('my_db_name') This returns in bytes - which unless you enjoy calculating byte sizes - there is another function you can use which will make these numbers human readable - pg_size_pretty() Combining these two functions will return the database size... Read more →