Run SQL query from shell prompt

-
mysql -u user -p -e 'SQL Query' database

Where,
-u : Specify mysql database user name
-p : Prompt for password
-e : Execute sql query

database : Specify database name


To list all database, enter:
$ mysql -u replicant -p -e 'show databases;'


To list count all rows, enter:
$ mysql -u replicant -p -e 'SELECT COUNT(*) FROM remote_logs'


Sample output:

Enter password:
+----------+
| count(*) |
+----------+
| 471 |
+----------+

Tell mysql to display output a page at a time, by using more or less pager:
$ mysql --pager=/usr/bin/less -u replicant -p -e 'SELECT COUNT(*) FROM remote_logs'


Redirect out to a file:
$ mysql -u replicant -p 'PassWord' -e 'SELECT COUNT(*) FROM quotes' remote_logs > sql.output.txta

To avoid password prompt just create ~/my.cnf file:

[client]
# for local server
#host=localhost
host=10.128.150.205
user=replicant
password=myPassword

[mysql]
pager=/usr/bin/less

Comments

Post a Comment

Popular posts from this blog

Mysql to CSV

-
Yesterday we have requirement to send report to our senior in excel format by retrieving data from database . Since there are only 10 rows so we can also manually create excel. But like other programmers, I am also very lazy, so that we create this script to export values from Mysql in csv format. mysql -u mysqlusername -p mysqlpass databsename -B -e "select * from \` tabalename \`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > mysql_exported_table.csv After that we fount that there are many other programming methods to perform this task.
Read more

Secure server with Firewalld

-
Recently we have issue reported by development team that there one of the backend cache server went to production without firewall. Although server was not expose to internet but it can be accessible from every IP in intranet. We decided to use firewalld for this. Main problem was once we start service it stop all access other than ssh which can be impact service accessing to our server. Also we can't run firewall-cmd command to apply rule before staring service. Fortunately   firewalld provide option to apply rules by updating config xml files.  Its makes our task easy.  Steps to be perform task Generate xml rules in test machine. Copy xml rules file in target host  update rules in XML file then start service. ## Login to any test VM $ ssh ## Verify there is no custom added rule exist $ cat /etc/firewalld/zones/public.xml <?xml version="1.0" encoding="utf-8"?> <zone> <short>Public</short> <description>For use in public...
Read more

Setup K8s cluster via kubeadm

-
Kubernetes is an open source software tool for managing containerised workloads. It enables development teams to automate the deployment, scaling and management of applications. The kubeadm tool helps bootstrap a minimum viable Kubernetes cluster that conforms to best practices. This guide will walk you through how to install Kubernetes on Ubuntu 16.04. Prerequisites 3 Linux servers running Ubuntu 16.04 Access to a user account on each system with sudo or root privileges The apt package manager, included by default Command-line/terminal window (Ctrl-Alt-T) Steps to Install Kubernetes on Ubuntu Setup repository Get the Docker gpg key: $ curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add - Add the Docker repository: $ sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu \ $(lsb_release -cs) \ stable" Get the Kubernetes gpg key: $ curl -s https://packages.cloud.google.com/apt/doc/apt-key.gpg | sudo apt-key add - Add the...
Read more