Useful linux commands for DBA with syntax, examples, definitions, and descriptions.
Linux commands relevant for a database administrator (DBA), along with their syntax, examples, definitions, and descriptions.
psql (PostgreSQL interactive terminal):
Definition: To connect to a PostgreSQL database and provide an interactive terminal for executing SQL commands.
Description: Psql is a command-line interface for PostgreSQL databases, allowing DBAs to interact with databases, execute queries, and manage database objects.
Syntax: psql [options] [dbname [username]]
Example: psql -U myuser mydb
mysql (MySQL command-line client):
Definition: To connect to a MySQL database server and provide an interactive command-line interface.
Description: MySQL is a command-line client for MySQL databases, enabling DBAs to execute SQL statements and manage MySQL server instances.
Syntax: mysql [options] [database]
Example: mysql -u myuser -p mydb
sqlplus (Oracle SQLPlus client):
Definition: To connect to an Oracle database and provide an interactive SQLPlus interface.
Description: Sqlplus is a command-line tool for Oracle databases, allowing DBAs to execute SQL and PL/SQL statements, administer databases, and manage Oracle objects.
Syntax: sqlplus [username[/password]@[database]]
Example: sqlplus sys/password@mydb as sysdba
pg_dump (PostgreSQL database backup tool):
Definition: To create a text file containing SQL commands for dumping a PostgreSQL database.
Description: Pg_dump is used to backup databases by dumping the database schema and data into a text file.
Syntax: pg_dump [options] [dbname]
Example: pg_dump -U myuser mydb > backup.sql
mysqldump (MySQL database backup tool):
Definition: To generate a SQL dump of a MySQL database into a text file.
Description: Mysqldump is a utility for creating backups of MySQL databases by dumping SQL statements that can recreate the database structure and data.
Syntax: mysqldump [options] [database]
Example: mysqldump -u myuser -p mydb > backup.sql
expdp (Oracle Data Pump export):
Definition: To export data and metadata from Oracle databases using Oracle Data Pump.
Description: Expdp is used to perform logical backups of Oracle databases, exporting database objects and data into a binary file.
Syntax: expdp [username/password]@[database] [options]
Example: expdp sys/password@mydb schemas=myuser directory=dpump dumpfile=mydump.dmp
pg_restore (PostgreSQL database restore tool):
Definition: To restore a PostgreSQL database from a backup created by pg_dump.
Description: Pg_restore is used to restore PostgreSQL databases from backups created by pg_dump, rebuilding the database objects and data.
Syntax: pg_restore [options] [filename]
Example: pg_restore -U myuser -d mydb backup.dump
mysqladmin (MySQL administration tool):
Definition: To perform administrative tasks such as starting and stopping MySQL server, monitoring server status, and changing passwords.
Description: Mysqladmin is a command-line administration utility for managing MySQL server instances and performing various administrative tasks.
Syntax: mysqladmin [options] command [command-option]
Example: mysqladmin -u root -p password newpassword
sqlldr (Oracle SQL Loader):
Definition: To load data from external files into Oracle database tables.
Description: Sqlldr is a utility for loading data from external files into Oracle database tables using control files that define the data loading process.
Syntax: sqlldr [username/password]@[database] [options]
Example: sqlldr scott/tiger@mydb control=loader.ctl data=load.dat
pg_ctl (PostgreSQL control utility):
Definition: To control the PostgreSQL database server daemon.
Description: Pg_ctl is used to start, stop, restart, reload, or check the status of the PostgreSQL database server daemon.
Syntax: pg_ctl [options] start|stop|restart|reload|status
Example: pg_ctl -D /path/to/data start
tnsping (Oracle TNS ping utility):
Definition: To test connectivity to Oracle databases specified by TNS service name.
Description: Tnsping is used to test connectivity to Oracle databases using Oracle Net Services and TNS service names.
Syntax: tnsping [TNS_service_name]
Example: tnsping mydb
pg_controldata (PostgreSQL control data extractor):
Definition: To display control information of a PostgreSQL database cluster.
Description: Pg_controldata is used to display control information such as system ID, WAL segment size, and timeline ID of a PostgreSQL database cluster.
Syntax: pg_controldata [options] [DATADIR]
Example: pg_controldata /var/lib/pgsql/12/data
mysqlcheck (MySQL table maintenance utility):
Definition: To check, repair, and optimize MySQL tables for all databases.
Description: Mysqlcheck is used to check, repair, analyze, and optimize MySQL tables, ensuring data integrity and improving performance.
Syntax: ‘mysqlcheck [options]’
Example:‘mysqlcheck -u root -p –auto-repair –optimize –all-databases’
impdp (Oracle Data Pump import):
Definition: To import data and metadata into Oracle databases using Oracle Data Pump.
Description: Impdp is used to perform data and metadata imports into Oracle databases from Data Pump export files.
Syntax: impdp [username/password]@[database] [options]
Example: impdp sys/password@mydb schemas=myuser directory=dpump dumpfile=mydump.dmp
pg_basebackup (PostgreSQL base backup tool):
Definition: To create a base backup of a PostgreSQL database cluster.
Description: Pg_basebackup is used to create a base backup of a PostgreSQL database cluster, which can be used for database replication and disaster recovery.
Syntax: pg_basebackup [options]
Example: pg_basebackup -U replication –pgdata=/var/lib/pgsql/12/data –format=plain –wal-method=stream
mysql_secure_installation (MySQL security configuration):
Definition: To secure MySQL installation by configuring passwords, removing anonymous users, and disabling remote root login.
Description: Mysql_secure_installation is used to secure a MySQL installation by setting passwords for root and removing insecure default settings.
Syntax: mysql_secure_installation
Example: mysql_secure_installation
pg_resetwal (PostgreSQL reset WAL utility):
Definition: To reset the Write-Ahead Log (WAL) and other control information of a PostgreSQL database cluster.
Description: Pg_resetwal is used to reset the Write-Ahead Log (WAL) and other control information of a PostgreSQL database cluster, typically after a crash or corruption.
Syntax: pg_resetwal [options] [DATADIR]
Example: pg_resetwal /var/lib/pgsql/12/data
mysqlbinlog (MySQL binary log utility):
Definition: To read and display MySQL binary log files.
Description: Mysqlbinlog is used to read and display MySQL binary log files, which contain changes to MySQL database contents.
Syntax: mysqlbinlog [options] [log_file]
Example: mysqlbinlog /var/log/mysql/mysql-bin.000001
pg_dumpall (PostgreSQL global data dump tool):
Definition: To create a SQL script containing the entire contents of a PostgreSQL database cluster.
Description: Pg_dumpall is used to create a SQL script containing the entire contents of a PostgreSQL database cluster, including all databases, users, and permissions.
Syntax: pg_dumpall [options]
Example: pg_dumpall -U myuser > global_dump.sql
mysql_upgrade (MySQL upgrade utility):
Definition: To check, upgrade, and repair MySQL tables after upgrading MySQL server.
Description: Mysql_upgrade is used to upgrade MySQL system tables to the latest version after upgrading MySQL server.
Syntax: mysql_upgrade [options]
Example: mysql_upgrade -u root -p
mysqlshow (MySQL database display utility):
Definition: To display information about MySQL databases and their tables.
Description: Mysqlshow is used to display information about MySQL databases, including their tables, indexes, and privileges.
Syntax: mysqlshow [options] [database]
Example: mysqlshow -u myuser -p mydb
psql (PostgreSQL interactive terminal):
Definition: To connect to a PostgreSQL database and provide an interactive terminal for executing SQL commands.
Description: Psql is a command-line interface for PostgreSQL databases, allowing DBAs to interact with databases, execute queries, and manage database objects.
Syntax: psql [options] [dbname [username]]
Example: psql -U myuser mydb
sqlcmd (SQL Server command-line client):
Definition: To connect to a SQL Server database and execute SQL commands.
Description: Sqlcmd is a command-line utility for SQL Server databases, allowing DBAs to execute T-SQL commands and scripts from the command line.
Syntax: sqlcmd [options]
Example: sqlcmd -S localhost -U sa -P mypassword -Q “SELECT * FROM mytable”
mysqladmin (MySQL administration tool):
Definition: To perform administrative tasks such as starting and stopping MySQL server, monitoring server status, and changing passwords.
Description: Mysqladmin is a command-line administration utility for managing MySQL server instances and performing various administrative tasks.
Syntax: mysqladmin [options]
Example: mysqladmin -u root -p password newpassword
pg_stat_statements (PostgreSQL statistics extension):
Definition: To provide information about SQL statement execution statistics in PostgreSQL.
Description: Pg_stat_statements is a PostgreSQL extension that collects and displays statistics about SQL statement execution, helping DBAs analyze and optimize database performance.
Syntax: pg_stat_statements
Example: SELECT * FROM pg_stat_statements;
mysqlbinlog (MySQL binary log utility):
Definition: To read and display MySQL binary log files.
Description: Mysqlbinlog is used to read and display MySQL binary log files, which contain changes to MySQL database contents.
Syntax: mysqlbinlog [options] [log_file]
Example: mysqlbinlog /var/log/mysql/mysql-bin.000001
db2 (IBM DB2 command-line utility):
Definition: To connect to an IBM DB2 database and execute commands.
Description: Db2 is a command-line utility for IBM DB2 databases, allowing DBAs to connect to databases, execute SQL statements, and perform administrative tasks.
Syntax: db2 [options] [command]
Example: db2 connect to mydb user using mypassword
pg_isready (PostgreSQL connection status checker):
Definition: To check the status of a PostgreSQL database server.
Description: Pg_isready is used to check the status of a PostgreSQL database server, indicating whether it is accepting connections.
Syntax: pg_isready [options]
Example: pg_isready -h localhost -p 5432 -U myuser
These Useful linux commands for DBA cover various aspects of database administration, including management, monitoring, backup, restore, and connection to different relational database management systems (RDBMS) such as PostgreSQL, MySQL, Oracle, SQL Server, and IBM DB2.