How do I access MySQL server from the shell prompt (command line)?
MySQL software includes mysql client. It is a text-based client for mysqld, a SQL-based relational database server. It works interactive and non-interactive mode.
mysql Client Syntax
mysql -u {mysql-user} -p {mysql-password} -h {mysql-server}
Where,
- -u {mysql-user} : Specify MySQL user name. Use root only when connecting to local system.
- -p {mysql-password}: Specify password, Employ the specified password when connecting to the database server. If a password is not supplied, it will be requested interactively.
- -h {mysql-server}: Connect to the specified host (remote or local)
For example remote connect to MySQL server called mysql10.nixcraft.in and user vivek:
$ mysql -u vivek -h mysql10.nixcraft.in -p
Sample outputs:
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version: 4.1.15-Debian_1-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
You can type an sql statement at mysql> prompt. In this example, you will list tables from the demo database, run;
USE demo; SHOW TABLES;
Sample session:
mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31855130 Server version: 5.0.77 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use nqod; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_nqod | +----------------+ | quotes | | quotes_meta | +----------------+ 2 rows in set (0.00 sec) mysql> \q Bye
After typing an SQL statement, end it with “;” and press [Enter] key. To exit type quit or \q:
quit
OR
\q
Sample session:
mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31853999 Server version: 5.0.77 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> quit Bye
Batch Mode
You can execute SQL statements in a script file (batch file) as follows:
mysql database_name < input.script.sql > output.file mysql -u user -p'password' database_name < input.script.sql > output.file
Recommend readings:
Type the following command to mysql command man page:
man mysql
🐧 Get the latest tutorials on Linux, Open Source & DevOps via:
- RSS feed or Weekly email newsletter
- Share on Twitter • Facebook • 10 comments... add one ↓
Category | List of Unix and Linux commands |
---|---|
File Management | cat |
Firewall | Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04 |
Network Utilities | dig • host • ip • nmap |
OpenVPN | CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04 |
Package Manager | apk • apt |
Processes Management | bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time |
Searching | grep • whereis • which |
User Information | groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w |
WireGuard VPN | Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04 |
Thanks it was really useful command
Simple Yet Informative
Hello thanks, Is posible execute this command with bat file? Because I need executed automatically.
Thanks have a nice days.
nice tutorial….
This isn’t working for me :( I type in mysql etc etc… and it doesn’t do anything, it just goes to the next ~]# line. guhhhhh I really want to setup remote mysql management, it’s been something I’ve wanted to do for a long time now. ;__;
Thank you very much, it is very useful.
short and sweet… a right pointer to the beginners….
Thanks a lot buddy.. :D
i cant go to mysql
why does the mysql -u root doesn’t work???
“mysql -u root -p” and hit enter, that should work