Mysql client connect options

It might happen that you don’t have access to MySQL via phpmyadmin or some other user friendly tool. Then what usually comes to help is the console mysql client. Bellow are listed the most important options when connecting to mysql server:

  • -u , –user=name  – the login user
  • -p , –-password[=name] – the login password to be used. If no password is given, then the user will be prompted to enter one
  • -h , –host=name – the mysql host. Defaults to localhost if not given
  • -D , –database=name – which database to select
  • -P , –port=”port number” – use this port if mysql is not using the default one
  • –protocol=name – the protocol to be used for this connection – tcp, socket, pipe or memory

Few examples. Connecting to mysql running on the same server with username “test” and password “testpass” (3 different variations, all doing the same):

mysql -u test -ptestpass
mysql -utest -ptestpass
mysql --user=test --password=testpass

Note that when using the short option -p (for password) there is no space between the option and the actual password! If you don’t want to give the password in the command line, then just don’t add and you’ll be prompted to type it:

mysql -u test -p
Enter password:

When MySQL is running on different host, 10.0.0.10 for instance, use -h (2 different variations, doing the same):

mysql -u test -ptestpass -h 10.0.0.10
mysql -utest -ptestpass -h10.0.0.10

If want to select some database up on connecting, mysite for instance, then use -D (2 different variations, doing the same):

mysql -u test -ptestpass -h 10.0.0.10 -D mysite
mysql -utest -ptestpass -h10.0.0.10 -Dmysite

When mysql is running on different port than the default one, 12345 for instance, then use -P:

mysql -u test -ptestpass -h 10.0.0.10 -P 12345
mysql -utest -ptestpass -h10.0.0.10 -P12345
mysql --user=test --password=testpass --host=10.0.0.10 --port=12345

And so on! If you need the full options list, then consult the mysql client help:

mysql --help