Connect to mysql database linux

Accessing MySQL Databases from Linux

This Note gives information about accessing MySQL on Linux with the mysql terminal interface and from the MySQL Workbench application.

For information about the School’s MySQL database server and to learn about your MySQL account and database schema, see

Linux Applications for accessing MySQL

MySQL can be accessed from applications and programs on Linux. This Note describes accessing MySQL on Linux with the mysql command interface and from the MySQL Workbench application.

The mysql command

The mysql command is a terminal-based SQL command interface to MySQL.

On Linux, start mysql with the mysql command in a terminal window.

The command connects to the School’s sample MySQL database on csmysql.

Note the command and its arguments are of the form:

The arguments are

• -h followed by the server host name (csmysql.cs.cf.ac.uk)
• -u followed by the account user name (use your MySQL username)
• -p which tells mysql to prompt for a password
• database the name of the database (use your database name).

Once mysql is running, you can type SQL statements and see the output in the terminal window.

MySQL Workbench

MySQL Workbench is a modeling tool that lets you design and generate MySQL databases graphically. It can also be used as an interface to execute SQL queries.

MySQL Workbench is available on the School’s Linux Laboratory workstations.

Starting MySQL Workbench on Linux

You can start MySQL Workbench from either the Mate Graphical User Interface menu, or from the Linux terminal.

MySQL Workbench can be started from the Mate menu. Click on the Mate menu button in the bottom edge panel to open the Mate menu panel.

If the panel is showing Favourites , click on All applications to see categories of applications.

Scroll down to Programming and click over MySQL Workbench

MySQL Workbench can be invoked from the command line in a Linux terminal as well.

The terminal must be associated with a graphical display (ie the X11 DISPLAY variable must be set), like it would be if you are logged in at a workstation, or MySQL Workbench will give an error message and fail to run.

Using MySQL Workbench

When MySQL Workbench starts, the initial window is divided into three panes.

• The top left pane is for connections to databases,
• the lower left pane is for data models,
• the right side pane is a list of shortcuts to MySQL functions and information.

There is also a menu bar across the top.

To begin with, we need to configure a new connection to the database we want to use.

In the Connections pane click on the ⊕ after MySQL Connections .

In the setup window, type a name that you want to give to the connection, for example, use your username and the server name, or a description like My Database .

In the Hostname field, enter the server name csmysql.cs.cf.ac.uk .

Select Standard TCP/IP as the Connection Method .

Enter your MySQL username in the Username field and the name of your database in the Schema field.

If you click on Store in Keychain , you will be prompted for your password. Otheriwse MySQL Workbench will prompt for it when you make the connection.

Click on OK , and the an icon for the new connection appears in the workspace.

To open the connection, double-click on the icon.

After obtaining your password, an SQL editor opens in a new tab and connects to the server.

The example here shows a connection to the sample MySQL database.

The left hand pane shows the database schema , including the tables that had been previously created.

To the right is an SQL query pane where you can type SQL commands. The results of executing the commands appear below in the result pane.

To run commands in the SQL query pane, click the execute icon .

Источник

How To Connect MySQL/MariaDB Server From Command Line On Linux and Windows?

MySQL or MariaDB database servers can be managed in different ways. Generally, GUI tools are provided because of their easiness. But in some cases, a command-line connection may be required to connect and manage MySQL and MariaDB database server.

Install MySQL/MariaDB Client For Ubuntu, Debian, Kali, Mint

We will start by installing MySQL/MariaDB command-line client in deb based distributions like Ubuntu, Debian, Kali, Mint, etc. The package is named as mysql-client which we will install with the apt command like below.

Install MySQL/MariaDB Client For Fedora, CentOS, RedHat

In the rpm-based distributions like Fedora, CentOS, RedHat can be installed with the yum command like below. Also alternatively dnf can be used like below.

Connect MySQL/MariaDB Database From Command Line

If MySQL/MariaDB database is installed on the local system we can use mysql tool to connect. We can use MySQL command with the sudo command for Linux systems like Ubuntu, Debian, Fedora, Mint, CentOS, etc.

We can see that when we open the MySQL interactive shell some information about the connection and MySQL database server is provided. Every connection to the MySQL server has an ID called MySQL Connection ID. Server Version specifies the MySQL server version. Some copyright information is also provided. \h or help can be used to get help about the MySQL interactive shell or command line. \c will clear the current interactive shell page or content and provide a clean command-line.

Connect Specifying Username MySQL/MariaDB Database From Command Line

The default behavior of the MySQL command is using the current user name. We can provide the user name explicitly with the -u option and the user name. In this example, we will connect with the user name root

Connect Specifying Username and Password MySQL/MariaDB Database From Command Line

Up to now, we have connected to the local MySQL/MariaDB database server without explicitly specifying the password. If we want to connect to a remote database server we have to specify the password for the given user. We can also specify the remote server hostname or IP address with the -h option like below.

Run SQL Without Connecting Remote MySQL/MariaDB Database

mysql command provides different features like running SQL statements remotely without using the mysql shell. We can run the SQL clause with the -e option like below.

Show Databases From Command Line

If we want to list databases stored on the MySQL/MariaDB database without connecting them explicitly and using MySQL shell we can use show databases; command like below.

Specify The Default Database From Command Line

As we use the command-line tool MySQL to connect and manage MySQL/MariaDB databases we need to specify the default database name in order to run SQL queries. We will use use statement with the database name in order to specify the default database the SQL queries run.

Run SQL Script or SQL File From Command Line On Remote MySQL/MariaDB Database Server

SQL scripts and statements can be stored in the SQL script file. We can run this script files without copy and paste just providing the file and redirecting the content of the file. We have the following SQL file content which is sqlscript.sql .

We have to also specify the database name as the last parameter to the mysql command which is sys in this case.

Источник

How to connect to MySQL from the command line

This article describes how to connect to MySQL from the command line using the mysql program. You can use the mysql program as a quick and easy way to access your databases directly.

Connecting to MySQL from the Command Line

To connect to MySQL from the command line, follow these steps:

1. Log in to your A2 Hosting account using SSH.
2. At the command line, type the following command, replacing username with your username:
3. At the Enter Password prompt, type your password. When you type the correct password, the mysql> prompt appears.

To display a list of databases, type the following command at the mysql> prompt:

To access a specific database, type the following command at the mysql> prompt, replacing dbname with the name of the database that you want to access:

After you access a database, you can run SQL queries, list tables, and so on. Additionally:

• To view a list of MySQL commands, type help at the mysql> prompt.
• To exit the mysql program, type \q at the mysql> prompt.

When you run a command at the mysql> prompt, you may receive a warning message if MySQL encounters a problem. For example, you may run a query and receive a message that resembles the following:

To view the complete warning message, type the following command:

Источник

Connect to mysql database linux

Note

If you are connecting to your DB System using a VPN connection, as described in VPN Connections, or a Bastion Session, you can connect directly from your local network to your DB System using the utilities described in this chapter. You do not need to configure a Compute instance to connect from.

Connecting to the MySQL DB System with SSH and MySQL Shell

This topic describes how to ssh into a Linux Compute instance, on the same VCN as your DB System, install and use MySQL Shell to connect to your MySQL DB System’s Endpoint.

1. SSH into the compute instance from your local machine using the opc user and the public IP address of the compute instance.

Connecting to the MySQL DB System with SSH and the MySQL Client

This topic describes how to ssh into a Linux Compute instance, on the same VCN as your DB System, install and use the mysql command line client to connect to your MySQL DB System’s Endpoint.

1. SSH into the compute instance from your local machine using the opc user and the public IP address of the compute instance.

Connecting from MySQL Workbench via Compute

This topic describes how to connect to MySQL Database Service using MySQL Workbench and a Compute Instance.

Note

MySQL Workbench does not support PuTTy-generated keys. If you used PuTTy to generate your SSH key pair, you must use PuTTygen’s Conversion utility to convert your private key to the OpenSSH format for use with MySQL Workbench.

Источник

Connect to mysql database linux

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”.

Источник