How to Use SFTP for Secure File Transfer on Linux

SFTP is SSH File Transfer Protocol. This works over ssh protocol for secure file transfer between two remote systems. The latest OpenSSH servers have default configured SFTP server for you.

Connect to SFTP

Use sftp command line utility to connect remote sftp system. You need the sftp user and hostname or IP address of the remote host.

 sftp user@remote.host

Navigate and View Files

Navigation in directories on SFTP is as simple as the local system. Use ‘pwd’ command to check the current working directory.

sftp> pwd

Remote working directory: /

Then use ‘ls’ command to list all files and directories in the current directory.

sftp> ls

To navigate to other directories use ‘cd’ command followed by destination directory.

sftp> cd uploads

Use ‘cd ..’ to navigate to the parent directory.

sftp> cd ..

Upload files to SFTP

Now, I need to upload some files under uploads directory. So first navigate to destination directory.

sftp> cd uploads
sftp> pwd
Remote working directory: /uploads

Use ‘put’ command to upload README.md from local system to remote sftp directory.

sftp> put README.md

You can also provide the absolute path of the local file and remote directory without navigating directories.

sftp> put /var/www/README.md /uploads/20181218/

Download Files from SFTP

To download files from remote sftp directory use ‘get’ command. For example to download REMOTE_FILE.md from remote system to the current local directory.

sftp> get REMOTE_FILE.md

Similarly, you can provide an absolute path to download files.

sftp> get /uploads/REMOTE_FILE.md /var/www/

Exit from SFTP

Simply say bye to your remote SFTP server. This will disconnect the SFTP connection and return to your local shell.

sftp> bye

Alternatively, you can also use one of the following options to quite from the SFTP session. These all 3 do the same work.

sftp> quit
sftp> exit 

MySQL Interview Questions

1) What is MySQL?

MySQL is a multi-threaded, multi-user SQL database management system which has more than 11 million installations. This is the world’s second most popular and widely used open source database.

2) In which language MySQL is written?

MySQL is written in C and C++ and its SQL parser is written in yacc.

3) What is the technical specification of MySQL?

MySQL has the following technical specifications –

• Flexible structure
• High performance
• Manageable and easy to use
• Replication and high availability
• Security and storage management

4) What is the difference between MySQL and SQL?

SQL is known as standard query language. It is used to interact with the database like MySQL. MySQL is a database that stores various types of data and keeps it safe.

A PHP script is required to store and retrieve the values inside the database.

5) What is the difference between database and table?

There is a major difference between a database and a table. The differences are as follows:

• Tables are a way to represent the division of data in a database while, database is a collection of tables and data.
• Tables are used to group the data in relation with each other and create a dataset. This dataset will be used in the database. The data which are stored in the table in any form is a part of the database, but the reverse is not true.

6) Why do we use MySQL database server?

The MySQL database server is very fast, reliable and easy to use. You can easily use and modify the software. MySQL software can be downloaded free of cost from the internet.

7) What are the different tables present in MySQL?

There are many tables that remain present by default. But, MyISAM is the default database engine used in MySQL. There are five types of tables that are present:

• MyISAM
• Heap
• Merge
• INNO DB
• ISAM

8) What is the difference between CHAR and VARCHAR?

A list of differences between CHAR and VARCHAR:

• CHAR and VARCHAR types are different in storage and retrieval.
• CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and 255.
• When CHAR values are stored then they are right padded using spaces to specific length. Trailing spaces are removed when CHAR values are retrieved.

9) What is the difference between TRUNCATE and DELETE in MySQL?

The DELETE command is used to delete data from a table. It only deletes the rows of data from the table while, truncate is very dangerous command and should be used carefully because it deletes every row permanently from a table.

10) How many Triggers are possible in MySQL?

There are only six Triggers allowed to use in MySQL database.

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

11) What is heap table?

Tables that are present in memory is known as HEAP tables. When you create a heap table in MySQL, you should need to specify the TYPE as HEAP. These tables are commonly known as memory tables. They are used for high speed storage on temporary basis. They do not allow BLOB or TEXT fields.

12) What is BLOB and TEXT in MySQL?

BLOB is an acronym stands for binary large object. It is used to hold a variable amount of data.

There are four types of BLOB.

• TINYBLOB
• BLOB
• MEDIUMBLOB
• LONGBLOB

The differences among all these are the maximum length of values they can hold.

TEXT is case-insensitive BLOB. TEXT values are non-binary strings (character string). They have a character set and values are stored and compared based on the collation of the character set.

There are four types of TEXT.

• TINYTEXT
• TEXT
• MEDIUMTEXT
• LONGTEXT

13) What is a trigger in MySQL?

A trigger is a set of codes that executes in response to some events.

14) What is the difference between heap table and temporary table?

Heap tables:

Heap tables are found in memory. They are used for high speed storage on temporary basis. They do not allow BLOB or TEXT fields.

Heap tables do not support AUTO_INCREMENT.

Indexes should be NOT NULL.

Temporary tables:

The temporary tables are used to keep the temporary data. Sometimes it is very useful in cases to keep temporary data. Temporary table is deleted after current client session terminates.

Main differences:

The heap tables are shared among clients while temporary tables are not shared.

Heap tables are just another storage engine, while for temporary tables you need a special privilege (create temporary table).

15) What is the difference between FLOAT and DOUBLE?

FLOAT stores floating point numbers with accuracy up to 8 places and allocates 4 bytes, on the other hand DOUBLE stores floating point numbers with accuracy up to 18 places and allocates 8 bytes.

16) What are the advantages of MySQL in comparison to Oracle?

• MySQL is a free, fast, reliable, open source relational database while Oracle is expensive, although they have provided Oracle free edition to attract MySQL users.
• MySQL uses only just under 1 MB of RAM on your laptop while Oracle 9i installation uses 128 MB.
• MySQL is great for database enabled websites while Oracle is made for enterprises.
• MySQL is portable.

17) What are the disadvantages of MySQL?

• MySQL is not so efficient for large scale databases.
• It does not support COMMIT and STORED PROCEDURES functions version less than 5.0.
• Transactions are not handled very efficiently.

18) What is the difference between CHAR and VARCHAR?

• CHAR and VARCHAR are differ in storage and retrieval.
• CHAR column length is fixed while VARCHAR length is variable.
• The maximum no. of character CHAR data type can hold is 255 character while VARCHAR can hold up to 4000 character.
• CHAR is 50% faster than VARCHAR.
• CHAR uses static memory allocation while VARCHAR uses dynamic memory allocation.

19) What is the difference between MySQL_connect and MySQL_pconnect?

Mysql_connect:

• It opens a new connection to the database.
• Every time you need to open and close database connection, depending on the request.
• Opens page every time when it loaded.

Mysql_pconnect:

• In Mysql_pconnect, “p” stands for persistent connection so it opens the persistent connection.
• The database connection cannot be closed.
• It is more useful if your site has more traffic because there is no need to open and close connection frequently and every time when page is loaded.

20) What does “i_am_a_dummy flag” do in MySQL?

The “i_am_a_dummy flag” enables MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present.

21) How to get the current date in MySQL?

To get current date, use the following syntax:

1. SELECTCURRENT_DATE();

22) What is the security alerts while using MySQL?

Install antivirus and configure the operating system’s firewall.

Never use the MySQL Server as the UNIX root user.

Change root username and password

Restrict or disable remote access.

23) How to change a password for an existing user via Mysqladmin?

Mysqladmin -u root -p password “newpassword”.

24) What is the difference between Unix timestamps and MySQL timestamps?

Actually both Unix timestamp and MySQL timestamp are stored as 32-bit integers but MySQL timestamp is represented in readable format of YYYY-MM-DD HH:MM:SS format.

25) How to display Nth highest salary from a table in a MySQL query?

Let us take a table named employee.

To find Nth highest salary is:

1. selectdistinct(salary) from employee order by salary desc limit n-1,1

if you want to find 3rd largest salary:

1. selectdistinct(salary) from employee order by salary desc limit 2,1

26) What is MySQL default port number?

MySQL default port number is 3306.

27) What is REGEXP?

REGEXP is a pattern match using regular expression. Regular expression is a powerful way of specifying a pattern for a complex search.

28) How many columns can you create for an index?

You can create maximum of 16 indexed columns for a standard table.

29) What is the difference between NOW() and CURRENT_DATE()?

NOW() command is used to show current year, month, date with hours, minutes and seconds while CURRENT_DATE() shows the current year with month and date only.

30) What is the query to display top 20 rows?

1. SELECT* FROM table_name LIMIT 0,20;

31) Write a query to display current date and time?

If you want to display current date and time, use –

1. SELECTNOW();

If you want to display current date only, use:

1. SELECTCURRENT_DATE();

32) What is save point in MySQL?

A defined point in any transaction is known as savepoint.

SAVEPOINT is a statement in MySQL which is used to set a named transaction save point with a name of identifier.

33) What is SQLyog?

SQLyog program is the most popular GUI tool for admin. It is the most popular MySQL manager and admin tool. It combines the features of MySQL administrator, phpMyadmin and others MySQL front ends and MySQL GUI tools.

34) How do you backup a database in MySQL?

It is easy to backing up data with phpMyAdmin. Select the database you want to backup by clicking the database name in the left hand navigation bar. Then click the export button and make sure that all tables are highlighted that you want to backup. Then specify the option you want under export and save the output.

35) What are the different column comparison operators in MySQL?

The =, <>, <=, <, >=, >, <<, >>, < = >, AND, OR or LIKE operator are the comparison operators in MySQL. These operators are generally used with SELECT statement.

36) Write a query to count the number of rows of a table in MySQL.

SELECT COUNT user_id FROM users;

37) Write a query to retrieve a hundred books starting from 20th.

SELECT book_title FROM books LIMIT 20, 100;

38) Write a query to select all teams that won either 1, 3, 5 or 7 games.

SELECT team_name FROM team WHERE team_won IN (1, 3, 5, 7);

39) What is the default port of MySQL Server?

The default port of MySQL Server is 3306.

40) How is MyISAM table stored?

MyISAM table is stored on disk in three formats.

• ‘.frm’ file : storing the table definition
• ‘.MYD’ (MYData): data file
• ‘.MYI’ (MYIndex): index file

41) What is the usage of ENUMs in MySQL?

ENUMs are used to limit the possible values that go in the table:

For example:

CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’); INSERT months VALUES (‘April’).

42) What are the advantages of MyISAM over InnoDB?

MyISAM follows a conservative approach to disk space management and stores each MyISAM table in a separate file, which can be further compresses, if required. On the other hand, InnoDB stores the tables in tablespace. Its further optimization is difficult.

43) What are the differences between MySQL_fetch_array(), MySQL_fetch_object(), MySQL_fetch_row()?

Mysql_fetch_object is used to retrieve the result from the database as objects while mysql_fetch_array returns result as an array. This will allow access to the data by the field names.

For example:

Using mysql_fetch_object field can be accessed as $result->name.

Using mysql_fetch_array field can be accessed as $result->[name].

Using mysql_fetch_row($result) where $result is the result resource returned from a successful query executed using the mysql_query() function.

Example:

1. $result = mysql_query(“SELECT * from students”);
2. while($row = mysql_fetch_row($result))
3. {
4. Some statement;
5. }

44) What is the difference between mysql_connect and mysql_pconnect?

Mysql_connect() is used to open a new connection to the database while mysql_pconnect() is used to open a persistent connection to the database. It specifies that each time the page is loaded mysql_pconnect() does not open the database.

45) What is the use of mysql_close()?

Mysql_close() cannot be used to close the persistent connection. Though it can be used to close connection opened by mysql_connect().

46) What is MySQL data directory?

MySQL data directory is a place where MySQL stores its data. Each subdirectory under this data dictionary represents a MySQL database. By default the information managed my MySQL = server mysqld is stored in data directory.

47) How do you determine the location of MySQL data directory?

The default location of MySQL data directory in windows is C:\mysql\data or C:\Program Files\MySQL\MySQL Server 5.0 \data.

48) What is the usage of regular expressions in MySQL?

In MySQL, regular expressions are used in queries for searching a pattern in a string.

• * Matches 0 more instances of the string preceding it.
• + matches 1 more instances of the string preceding it.
• ? Matches 0 or 1 instances of the string preceding it.
• . Matches a single character.
• [abc] matches a or b or z
• | separates strings
• ^ anchors the match from the start.
• “.” Can be used to match any single character. “|” can be used to match either of the two strings
• REGEXP can be used to match the input characters with the database.

Example:

The following statement retrieves all rows where column employee_name contains the text 1000 (example salary):

1. Selectemployee_name
2. Fromemployee
3. Whereemployee_name REGEXP ‘1000’
4. Orderby employee_name

49) What is the usage of i-am-a-dummy flag in MySQL?

In MySQL, the i-am-a-dummy flag makes the MySQL engine to deny the UPDATE and DELETE commands unless the WHERE clause is present.

50) Which command is used to view the content of the table in MySQL?

The SELECT command is used to view the content of the table in MySQL.

How to grep a String Recursively in All Files

How do a search string recursively in the file system with grep command? 

Linux grep command is a frequently used command by most of the Linux users. Using grep you can search any string in all files available in the directory hierarchy. You will get come examples of grep command to search any string recursively in the file system.

Grep command uses following syntax to search pattern Recursively in all files available under specific directory and its sub directories.

$ grep -R "search-pattern" /path/to/search/dir

Search Pattern Recursively in Files:

Search for pattern “example.com” in all files available under /var/www/html directory.

$ grep -R "example.com" /var/www/html

Search Pattern Recursively in Specific Extension Files:

Make your search more specific, like search a string “example.com” in all files with the extension .php only. To achieve this use --include option to force grep to search for the specific file only and ignore others.

$ grep -R --include="*.php" "example.com" /var/www/html

You may also specify multiple file extensions to search

$ grep -R --include="*.php" --include="*.conf" "example.com" /var/www/html

How To Run Command In Background on Linux

A background process is the process which executes behind the scenes. These processes run without user intervention. There are various way to do it for example using & or nohup or screen command. The most popular and frequently used are & and nohup. Including of these two options this tutorial will also describes you to use of screen command.

1. Using &

On Linux/Unix based systems we can easily send any process in background by appending & in command.

$ ls -l &
$ tar czf temp.tar.gz /tmp &

In this way if you start a command in background and exited from shell, then shell will kill this background process with SIGHUP signal.

2. Using nohup

To prevent termination of background processes after shell termination use nohup command. nohup command simply use at prefix of command like. All the output generated with commands will save in nohup.out file in current directory.

$ nohup tar czf temp.tar.gz /tmp &

3. Using Screen

Screen is better option for long running processes. Using screen you can simply get the same session running on remote server even after logout from ssh. For this test you can do the following

$ screen

Now start your process in background.

$ tar czf temp.tar.gz /tmp &

Let’s detach your screen session with keyboard shortcut CTRL+a+d or ctrl+alt+d. It will return you to command line. Now exit your ssh session and again connect system through ssh and execute following command to get old screen session running.

$ screen -r