CODEWITHSUNDEEP
mysqldatabaseauthenticationcommand-lineconnection
Leahcim
Leahcim

Reputation: 42019

How to connect to MySQL from the command line

How can you connect to MySQL from the command line in a Mac? (i.e. show me the code)

I'm doing a PHP/SQL tutorial, but it starts by assuming you're already in MySQL.

Upvotes: 397

Views: 1250362

Answers (17)

arafat877
arafat877

Reputation: 27

with powershell under windows 10, go to your mysql directory and the /bin folder, then type :

./mysql --host=localhost --user=root --password= zoo_nft_db

I have mysql isntalled in :

E:\WAMPP_Last\mysql\bin

Upvotes: -1

kelvinmacharia254
kelvinmacharia254

Reputation: 167

The various commands shared here is matter of utilizing the flags(options) available to connect to certain state or quick authentication without password prompt. Let me sample a few ways to connect to my sql.

  1. Connecting from the terminal

Option 1:

mysql -u root -p : This with connect to user called root, -p flag will prompt for a password.

Option 2:

mysql -u root -p<PASSWORD> : Here you enter the password directly into the command and after execution the server connects quick without password prompt.

The rest of the options will basically be centred on this two where you can use -h flag to include host among other configuration.

  1. Connecting from the mysql shell First thing is to ensure you are in sql mode, use:

\sql to switch to sql mode from JS or Python mode.

To connect to mysql server, use:

\connect username@hostname e.g. \connect root@localhost

Upvotes: 1

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

For example, you can log in without a password prompt by setting a password(e.g., banana) to -p or --password= as shown below. *Don't put any space just after -p or --password= because there is error:

mysql -u john -pbanana

Or:

mysql -u john --password=banana

Or, you can log in without a password prompt by setting a password(e.g., banana) to MYSQL_PWD= as shown below. *The doc says MYSQL_PWD is deprecated as of MySQL 8.0; expect it to be removed in a future version of MySQL.:

MYSQL_PWD=banana mysql -u john

Or on Windows, you can set the user john and the password banana under [client] in my.ini as shown below. *My answer explains [client] and my answer explains where my.ini is located on Windows and my answer explains how to log in by setting only the password banana under [client] in my.ini:

# "my.ini"

[client]
user="john"
password="banana"

Then, you can log in by setting my.ini's location to --defaults-file= or --defaults-extra-file= as shown below:

mysql --defaults-file='C:\ProgramData\MySQL\MySQL Server 8.0\my.ini'

Or:

mysql --defaults-extra-file='C:\ProgramData\MySQL\MySQL Server 8.0\my.ini'

*Not setting my.ini's location to --defaults-file= or --defaults-extra-file= gets error as shown below:

mysql
ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)

Upvotes: 3

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

Everyone can log in to MySQL. *A password is needed and my answer explains how to log in to MySQL without a password prompt:

mysql -u root -p
Enter password: *****

Upvotes: 7

kate sarant
kate sarant

Reputation: 31

if you use no password for entering the mysql.server

mysql -u root -h 127.0.0.1 -P 'port'

Upvotes: 1

Naveen Kumar
Naveen Kumar

Reputation: 1067

Use below command to do the login to remote mysql server

mysql -u property_wlive  -h 127.0.0.1 -P 3306 property_plive -p

Upvotes: 8

Cyril N.
Cyril N.

Reputation: 39889

Oddly enough, despite there being a lot of (similar) answers, no one suggested this:

You can create a .my.cnf file in your $HOME folder, which contains:

[client]
host=127.0.0.1
port=3306
database=google
user=root
password=root

And you'll only have to do

$> mysql

To connect to that database.

A few key notes to take into consideration :

  • Storing the password in that file is not a good idea. At worst, please do a chmod 400 .my.cnf. But best is to store the password elsewhere. Other threads on StackOverflow offer great answers for that.
  • You can customize the data in that file, and leave the rest to you. For instance, removing the database line allow you to do mysql another-db-name

Upvotes: 4

Noah Gary
Noah Gary

Reputation: 960

Short, sweet, and complete: (and also secure)

mysql -u <username> -h <hostname> -P <port> <database> -p

This will

  1. Connect you to a remote database (including port)
  2. Not store your password in your .bash_history

Upvotes: 19

Anis KCHAOU
Anis KCHAOU

Reputation: 1104

Those steps worked for me with Windows 10

  1. go to MySQL installation directory then access to bin directory (mysql.exe must be showed in list of files)
  2. open cmd in the same location
  3. run mysql -u [username] -p (don't need to add -p if you didn't have set a password) then press enter

Upvotes: 1

Sandeep Kumar
Sandeep Kumar

Reputation: 41

In my case, it worked with the following command on Mac.

After you run MySQL Shell and you have seen the following:

mysql-js>

Firstly, you should:

mysql-js>\sql

Second step:

MySQL  SQL > \c --mysql username@host

Then finally provide the password as prompted

Upvotes: 3

Scorpioooooon21
Scorpioooooon21

Reputation: 623

Sometimes you may need to add -P for port:

mysql -u USERNAME -pPASSWORD -h HOSTNAME -P PORTNUMBER DATABASE;

Upvotes: 5

Nishant
Nishant

Reputation: 55876

See here http://dev.mysql.com/doc/refman/5.0/en/connecting.html

mysql -u USERNAME -pPASSWORD -h HOSTNAMEORIP DATABASENAME

The options above means:

-u: username
-p: password (**no space between -p and the password text**)
-h: host
last one is name of the database that you wanted to connect.

Look into the link, it's detailed there!

As already mentioned by Rick, you can avoid passing the password as the part of the command by not passing the password like this:

mysql -u USERNAME -h HOSTNAMEORIP DATABASENAME -p

People editing this answer: PLEASE DONOT ADD A SPACE between -p and PASSWORD

Upvotes: 664

Anand Tiwari
Anand Tiwari

Reputation: 77

This worked for me ::-

mysql --host=hostNameorIp --user=username --password=password

or

mysql --host=hostNameorIp --user=username --password=password database_name

Upvotes: 2

Name Surname
Name Surname

Reputation: 379

After you run MySQL Shell and you have seen following:

mysql-js>

Firstly, you should:

mysql-js>\sql

Secondly:

 mysql-sql>\connect username@servername (root@localhost)

And finally:

Enter password:*********

Upvotes: 26

arshovon
arshovon

Reputation: 13661

One way to connect to MySQL directly using proper MySQL username and password is:

mysql --user=root --password=mypass

Here, 

root is the MySQL username
mypass is the MySQL user password

This is useful if you have a blank password.

For example, if you have MySQL user called root with an empty password, just use 

mysql --user=root --password=

Upvotes: 6

Rick
Rick

Reputation: 1981

Best practice would be to mysql -u root -p. Then MySQL will prompt for password after you hit enter.

Upvotes: 198

Swamy
Swamy

Reputation: 400

Use the following command to get connected to your MySQL database

mysql -u USERNAME -h HOSTNAME -p

Upvotes: 13

Related Questions