Login to MySQL server from command line

In Windows, MySQL, by default, is installed in the folder %systemdrive%\Program Files\MySQL.В For example, if the system drive is C:, then the installation directory for MySQL server 5.5 would be C:\Program Files\MySQL\MySQL Server 5.5\

We can connect to MySQL server by running the mysql.exe located in the bin folder in the mysql installation directory.

If you just run mysql without passing any arguments, you can connect to the server with anonymous account. This works only if the anonymous account is not disabled.В When you install MySQL, by default, it enables anonymous access.

Connect to MySQL as root

You can connect to MySQL server by specifying the user name and password in the command line arguments to mysql command. To connect as root, the command would be as below.

You will be prompted for password if -p option is skipped. If MySQL service is not running then the command would fail with the below error

You can create a desktop batch file to launch MySQL client as follows.

• Create a text file and name it as mysql.bat.
• Add the following command to the mysql.bat file.
• cmd /K “C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe” -uroot -ppassword
• Change the mysql.exe path as per your MySQL server version and install directory.
• Save the file. Now onwards you can connect to server by just double clicking on the batch file.

Alternative way is to create a desktop shortcut with the target as C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe.В After creating the shortcut, open the properties and add -uroot -ppassword to the targetВ field.

The default installation of MySQL creates a shortcut inВ Start menu-> All Programs with the target as below. You can use it as the target for your desktop shortcut.

Настройка удаленного подключения к MySQL

Материал статьи предназначен для начинающих, профессионалы Хабра в большинстве не нуждаются в изложении этого вопроса. Тем не менее, те, кто делает первые шаги в работе с MySQL часто задают подобные вопросы. Статья написан для тех, кто впервые столкнулся с необходимостью установить удаленное соединение с базой данных MySQL. В статье рассказывается о сложностях, которые могут возникнуть при настройке удаленного соединения и о методах их преодоления.

Как установить соединение с удаленной базой?

Для установления удаленного соединения нужно указать параметры, характеризующие устанавливаемое подключение. Это

• —host
• —protocol
• —port

Из четырех возможных протоколов удаленное подключение позволяет только TCP/IP, поэтому первым требованием является доступность компьютера из сети по TCP/IP.
Далее необходимо добавить имя хоста (или ip адрес машины, на которой расположена база) в строке подключения:
mysql —host=host_name
Обратите внимание, что указание в качестве имени хоста значения localhost (или же отсутствие такого параметра, что то же самое, так как это дефолтное значение) приводит к обращению на локальную машину.

Учтите, что параметры имеют две формы записи: длинную и короткую. В свою очередь, если параметр имеет значение (как например, в случае хоста необходимо указать имя хоста к которому нужно подключиться), то короткая форма записи может использоваться с пробелом или без (исключением из этого правила является пароль).
Таким образом, следующие три записи являются эквивалентными:

• mysql —host=myhost.ru
• mysql -h myhost.ru
• mysql -hmyhost.ru

Как настроить соединение с удаленной базой?

В MySQL пользователь характеризуется двумя параметрами: именем и хостом, с которого он может обращаться. По умолчанию доступ разрешен только с локальной машины, т.е. для пользователя user@localhost. Права на доступ пользователям даются с помощью команды GRANT. Команда выполняется под рутом.

Например, если я хочу создать юзера, который сможет коннектиться с любого хоста с полными правами, то следует выполнить следующую команду:
GRANT ALL PRIVILEGES ON `имя_базы`.* TO myuser@% IDENTIFIED BY ‘пароль’;

Примечание. Обратите внимание, что данная команда дает доступ пользователю myuser со всех IP кроме 127.0.0.1, соответсвующего localhost.
Для пользователя myuser@localhost необходимо давать права отдельной командой GRANT.

Если вы решите обратиться к локальной машине как к удаленной, используя протокол TCP/IP, то не забывайте, что myuser@localhost и myuser@ip_собственного_компа это разные пользователи и каждому из них права нужно давать отдельной командой.

Второй пример показывает как дать право читать таблицу time_zone в базе mysql пользователю myuser с машины 192.168.0.76
с паролем mypassy:
GRANT SELECT ON mysql.time_zone TO myuser@192.168.0.76 IDENTIFIED BY ‘mypassy’;

Возникающие сложности

Если после всех действий у вас возникает ошибка
(Can’t connect to Mysql Server on ‘ваш IP'(10061)),
значит соединение блокирует firewall (windows firewall, outpost, антивирус или еще что-то).

Если возникает ошибка
(Client does not support autentification protocol request by server; consider upgrading MySQL client), клиент (то есть ваша программа) не поддерживает требуемый сервером протокол.
Эту ошибку можно исправить, установив пароль старого формата:
SET PASSWORD FOR user@host = OLD_PASSWORD(‘password’);

How to Connect to MySQL from Windows Command Line

PHP Code

1. Home
2. SysAdmin
3. How to Connect to MySQL from Windows Command Line

MySQL is a popular and open-source relational database application. Therefore, many servers make use of MySQL. The way you access the database depends on the operating system from which you are working.

This guide walks you through using the Windows Command line to connect to a MySQL database.

Outlet Servers Starting at Only $30.00/mo

• A Windows-based system with an administrator account
• A local installation of MySQL
• Notepad text editor (optional)

Starting MySQL from the Windows Command Line

Step 1: How to Open a Windows Command Prompt

Start by opening the Run command box in Windows. Use the keyboard shortcut – hold the Windows (super) key and press the letter R (Win+R).

Then, type in cmd and press Enter. This command opens the Windows command line.

A black command line interface should launch, with white text and a cursor for you to type.

Step 2: Verify MySQL is Running on Windows

Next, run the command to display a list of all the services that are currently running. Enter the following in the command prompt:

If MySQL is not on the list, you can start it using the Services panel. Enter the following command:

A new window will launch and display the list of services available on your system. Scroll down to find MySQL, and check the status column. Left-click the MySQL service to highlight it, then right-click to open a context menu. Finally, left-click on start.

Step 3: Connect to a Local MySQL Server

First, start MySQL in Windows using the following command:

Replace with the username for your MySQL installation.

Enter mysql.exe –uroot –p , and MySQL will launch using the root user.

MySQL will prompt you for your password. Enter the password from the user account you specified with the –u tag, and you’ll connect to the MySQL server.

The command prompt changes to look like this:

Change to the MySQL folder, use the cd command:

The command prompt should change to mysql> letting you know you’re currently in the MySQL folder.

To list the contents of this folder:

This lists the contents of the current folder. One of the folders will display the version number of your MySQL installation.

For example, if you’ve installed MySQL 5.5, you should see a folder named “MySQL Server 5.5”.

High-Performance Dedicated Servers Starting at Only $ 199 $90.00/mo

Step: 4 Create Windows Shortcut to Login to MySQL

1. To create a shortcut in Windows, enter the following in the command line:

2. Open the Windows Notepad text editor with the command:

Replace –uroot with –u if you have a different username, and –ppasswor d with –p .

Also, make sure you change the path from MySQL Server X.X to the actual folder location.

3. Now, press Ctrl-S to launch a Save dialog.

4. Then, type “mysql.bat” in the name field, and change the location to your desktop (or another place that’s easy to find).

This creates a small Windows executable file that you can double-click to log into MySQL using the specified username and password.

With the help of this simple guide, you should be able to connect to a MySQL database using the Windows command prompt. Once you’ve logged into the MySQL server, the commands will be the same regardless of what kind of system you’re running on.

If you are searching for an alternative, terminal-based solution, try out using PostgreSQL from command line.

Connect to mysql server from windows

To connect to the server, you usually need to provide a MySQL user name when you invoke mysql and, most likely, a password. If the server runs on a machine other than the one where you log in, you also need to specify a host name. Contact your administrator to find out what connection parameters you should use to connect (that is, what host, user name, and password to use). Once you know the proper parameters, you should be able to connect like this:

host and user represent the host name where your MySQL server is running and the user name of your MySQL account. Substitute appropriate values for your setup. The ******** represents your password; enter it when mysql displays the Enter password: prompt.

If that works, you should see some introductory information followed by a mysql> prompt:

The mysql> prompt tells you that mysql is ready for you to enter SQL statements.

If you are logging in on the same machine that MySQL is running on, you can omit the host, and simply use the following:

If, when you attempt to log in, you get an error message such as ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2) , it means that the MySQL server daemon (Unix) or service (Windows) is not running. Consult the administrator or see the section of Chapter 2, Installing and Upgrading MySQL that is appropriate to your operating system.

For help with other problems often encountered when trying to log in, see Section B.3.2, “Common Errors When Using MySQL Programs”.

Some MySQL installations permit users to connect as the anonymous (unnamed) user to the server running on the local host. If this is the case on your machine, you should be able to connect to that server by invoking mysql without any options:

After you have connected successfully, you can disconnect any time by typing QUIT (or \q ) at the mysql> prompt:

On Unix, you can also disconnect by pressing Control+D.

Most examples in the following sections assume that you are connected to the server. They indicate this by the mysql> prompt.

Connect to mysql server from windows

This section describes use of command-line options to specify how to establish connections to the MySQL server, for clients such as mysql or mysqldump . For information on establishing connections using URI-like connection strings or key-value pairs, for clients such as MySQL Shell, see Section 4.2.5, “Connecting to the Server Using URI-Like Strings or Key-Value Pairs”. For additional information if you are unable to connect, see Section 6.2.21, “Troubleshooting Problems Connecting to MySQL”.

For a client program to connect to the MySQL server, it must use the proper connection parameters, such as the name of the host where the server is running and the user name and password of your MySQL account. Each connection parameter has a default value, but you can override default values as necessary using program options specified either on the command line or in an option file.

The examples here use the mysql client program, but the principles apply to other clients such as mysqldump , mysqladmin , or mysqlshow .

This command invokes mysql without specifying any explicit connection parameters:

Because there are no parameter options, the default values apply:

The default host name is localhost . On Unix, this has a special meaning, as described later.

The default user name is ODBC on Windows or your Unix login name on Unix.

No password is sent because neither —password nor -p is given.

For mysql , the first nonoption argument is taken as the name of the default database. Because there is no such argument, mysql selects no default database.

To specify the host name and user name explicitly, as well as a password, supply appropriate options on the command line. To select a default database, add a database-name argument. Examples:

For password options, the password value is optional:

If you use a —password or -p option and specify a password value, there must be no space between —password= or -p and the password following it.

If you use —password or -p but do not specify a password value, the client program prompts you to enter the password. The password is not displayed as you enter it. This is more secure than giving the password on the command line, which might enable other users on your system to see the password line by executing a command such as ps . See Section 6.1.2.1, “End-User Guidelines for Password Security”.

To explicitly specify that there is no password and that the client program should not prompt for one, use the —skip-password option.

As just mentioned, including the password value on the command line is a security risk. To avoid this risk, specify the —password or -p option without any following password value:

When the —password or -p option is given with no password value, the client program prints a prompt and waits for you to enter the password. (In these examples, mydb is not interpreted as a password because it is separated from the preceding password option by a space.)

On some systems, the library routine that MySQL uses to prompt for a password automatically limits the password to eight characters. That limitation is a property of the system library, not MySQL. Internally, MySQL does not have any limit for the length of the password. To work around the limitation on systems affected by it, specify your password in an option file (see Section 4.2.2.2, “Using Option Files”). Another workaround is to change your MySQL password to a value that has eight or fewer characters, but that has the disadvantage that shorter passwords tend to be less secure.

Client programs determine what type of connection to make as follows:

If the host is not specified or is localhost , a connection to the local host occurs:

On Windows, the client connects using shared memory, if the server was started with the shared_memory system variable enabled to support shared-memory connections.

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs: the client connects using a Unix socket file. The —socket option or the MYSQL_UNIX_PORT environment variable may be used to specify the socket name.

On Windows, if host is . (period), or TCP/IP is not enabled and —socket is not specified or the host is empty, the client connects using a named pipe, if the server was started with the named_pipe system variable enabled to support named-pipe connections. If named-pipe connections are not supported or if the user making the connection is not a member of the Windows group specified by the named_pipe_full_access_group system variable, an error occurs.

Otherwise, the connection uses TCP/IP.

The —protocol option enables you to use a particular transport protocol even when other options normally result in use of a different protocol. That is, —protocol specifies the transport protocol explicitly and overrides the preceding rules, even for localhost .

Only connection options that are relevant to the selected transport protocol are used or checked. Other connection options are ignored. For example, with —host=localhost on Unix, the client attempts to connect to the local server using a Unix socket file, even if a —port or -P option is given to specify a TCP/IP port number.

To ensure that the client makes a TCP/IP connection to the local server, use —host or -h to specify a host name value of 127.0.0.1 (instead of localhost ), or the IP address or name of the local server. You can also specify the transport protocol explicitly, even for localhost , by using the —protocol=TCP option. Examples:

If the server is configured to accept IPv6 connections, clients can connect to the local server over IPv6 using —host=::1 . See Section 5.1.13, “IPv6 Support”.

On Windows, to force a MySQL client to use a named-pipe connection, specify the —pipe or —protocol=PIPE option, or specify . (period) as the host name. If the server was not started with the named_pipe system variable enabled to support named-pipe connections or if the user making the connection is not a member of the Windows group specified by the named_pipe_full_access_group system variable, an error occurs. Use the —socket option to specify the name of the pipe if you do not want to use the default pipe name.

Connections to remote servers use TCP/IP. This command connects to the server running on remote.example.com using the default port number (3306):

To specify a port number explicitly, use the —port or -P option:

You can specify a port number for connections to a local server, too. However, as indicated previously, connections to localhost on Unix use a socket file by default, so unless you force a TCP/IP connection as previously described, any option that specifies a port number is ignored.

For this command, the program uses a socket file on Unix and the —port option is ignored:

To cause the port number to be used, force a TCP/IP connection. For example, invoke the program in either of these ways:

For additional information about options that control how client programs establish connections to the server, see Section 4.2.3, “Command Options for Connecting to the Server”.

It is possible to specify connection parameters without entering them on the command line each time you invoke a client program:

Specify the connection parameters in the [client] section of an option file. The relevant section of the file might look like this:

Some connection parameters can be specified using environment variables. Examples:

To specify the host for mysql , use MYSQL_HOST .

On Windows, to specify the MySQL user name, use USER .

For a list of supported environment variables, see Section 4.9, “Environment Variables”.