Linux connect mysql server

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 my MySQL Database server using command line and php

Task: Use command mysql command line client

The syntax is:
mysql -u DBUSER -h DBSERVERNAME_OR_IP -p
Or
mysql -u user_name -h mysql_server_ip_address_here -p db_name_here
Make sure you replace username vivek and hostname localhost as per your setup:
$ mysql -u vivek -h localhost -p
Supply the password when prompted for password. You should get the mysql> or MariaDB prompt as follows:

How to connect to MySQL from the command line

How to list all databases

You need to type SQL command. For example, to list database, run:
mysql> show databases ;
Sample outputs:

How to access specific database named nibashportal

The syntax is:
mysql> use nibashportal ;

How to list all tables in nibashportal database

The syntax is:
mysql> use nibashportal ;
mysql> show tables ;

Sample outputs:

To describe table wp_users, run:
mysql> desc wp_users ;
Sample outputs:

To list all stored data in wp_users table, run:
mysql> select * from wp_users ;
OR
mysql> select ID,user_login,user_status,display_name from wp_users ;
Sample outputs:

To exit simply type the:
mysql> exit ;
Sample outputs:

For more info read the mysql command line man page:
$ man mysql

Task: Use PHP to connect to MySQL

Type the following PHP code in a file named test.php:

Make sure you replace USERNAME and PASSWORD with your database user name and password. Also, replace TABLE and DATABASE with the valid table and database names from your database.

• No ads and tracking
• In-depth guides for developers and sysadmins at Opensourceflare✨
• Join my Patreon to support independent content creators and start reading latest guides:
  • How to set up Redis sentinel cluster on Ubuntu or Debian Linux
  • How To Set Up SSH Keys With YubiKey as two-factor authentication (U2F/FIDO2)
  • How to set up Mariadb Galera cluster on Ubuntu or Debian Linux
  • A podman tutorial for beginners – part I (run Linux containers without Docker and in daemonless mode)
  • How to protect Linux against rogue USB devices using USBGuard

Join Patreon ➔

Upload the PHP file and type url https://your-domain-name-here/myscript.php

🐧 Get the latest tutorials on Linux, Open Source & DevOps via

Category	List of Unix and Linux commands
Documentation	help • mandb • man • pinfo
Disk space analyzers	df • duf • ncdu • pydf
File Management	cat • cp • less • mkdir • more • tree
Firewall	Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04
Linux Desktop Apps	Skype • Spotify • VLC 3
Modern utilities	bat • exa
Network Utilities	NetHogs • 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 • glances • gtop • jobs • killall • kill • pidof • pstree • pwdx • time • vtop
Searching	ag • grep • whereis • which
Shell builtins	compgen • echo • printf
Text processing	cut • rev
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

Comments on this entry are closed.

hi i am trying to connect to my sql with this code

but i get an erorr of exceeding the time or long period more than 60 sec

would u help me

Hi sr,
with the age of this post i guess you would have solved this by now but for others:

looking at the code in the example ‘select * from table’ and a loop printing out each value on the screen theres a good chance that the script is taking too long to execute (default is 60 seconds) for others wanting to test connections i’d suggest putting on a limit eg.
SELECT * FROM table LIMIT 50;
Then that will limit mysql to only pull the first 50 rows

Begin Quote: but i get an erorr of exceeding the time or long period more than 60 sec. End Quote.
You need to edit php.ini

Other than displaying the value twice, this worked great for me! Thanks

i didn’t get the solution of my problem. actually i’ve created database on my own system and i want to connect it to my web host server so that every body who visits my site can access data from that database. is it possible? what could i have to do for that?

Thanks! Worked perfectly.

Hi thanks for your code it really works. But i got an error when i try to use my own ip.
The error says, “Host ‘comp.local’ is not allowed to connect to this MySQL server”. Any ideas?

Thanks in advance

Hi I already figured it out. I got the error because the host is set to localhost and not my local ip..

Thank you for this.

Thank you soo much for this absolute life saver! i’m currently converting our old site to PHP (after using HTML with CSS for so long) it got abit time consuming trying to flick through books which conveniently miss the PRINT function *palms own face*. I can’t believe after hours of chewing through my code trying to decipher the simple way to produce the results it was something as small as that. 10 thumbs up!

In response to feedback ‘3’ of this thread (and for anyone reading this who gets the same result). The only way i could remove the duplicate results after fetching an array query was to take the PRINT out of the code and put it into an ECHO command by adding an extra variable.(i used $range for this example but you can use anything)

Then later in the code to produce the results from the query i put the ECHO in a element:

not really related to much to the topic and i know there might be other easier ways but i thought it might help people who are new to PHP like me 🙂

edit to previous post :

$link = mysql_connect(“localhost”, “USERNAME”, “PASSWORD”);

Источник

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:

Источник

Linux connect mysql server

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 must also 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.

Источник