You can connect to your MySQL/MariaDB database server using the mysql command line client or using programming language such as PHP or perl. This pages shos how to connect to MySQL from the command line using the mysql on a Linux/Unix like system.
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 list all databases
You need to type SQL command. For example, to list database, run:
mysql> show databases;
Sample outputs:
+--------------------+ | Database | +--------------------+ | nixnewsletter | | nixwikibashportal | | nixscbzcms | | nixscbzfaq | | nixsnixtipsclean | | information_schema | | mysql | | performance_schema | | nibashportal | +--------------------+ 9 rows in set (0.00 sec)
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:
+------------------------+ | Tables_in_nibashportal | +------------------------+ | wp_commentmeta | | wp_comments | | wp_links | | wp_options | | wp_postmeta | | wp_posts | | wp_term_relationships | | wp_term_taxonomy | | wp_termmeta | | wp_terms | | wp_thesis_backups | | wp_thesis_terms | | wp_usermeta | | wp_users | | wp_wdpv_post_votes | | wp_wp_rp_tags | +------------------------+ 16 rows in set (0.00 sec)
To describe table wp_users, run:
mysql> desc wp_users;
Sample outputs:
+---------------------+---------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+---------------------+------+-----+---------------------+----------------+ | ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | user_login | varchar(60) | NO | MUL | | | | user_pass | varchar(255) | NO | | | | | user_nicename | varchar(50) | NO | MUL | | | | user_email | varchar(100) | NO | MUL | | | | user_url | varchar(100) | NO | | | | | user_registered | datetime | NO | | 0000-00-00 00:00:00 | | | user_activation_key | varchar(255) | NO | | | | | user_status | int(11) | NO | | 0 | | | display_name | varchar(250) | NO | | | | +---------------------+---------------------+------+-----+---------------------+----------------+ 10 rows in set (0.00 sec)
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:
+----+------------+-------------+--------------+ | ID | user_login | user_status | display_name | +----+------------+-------------+--------------+ | 1 | vivek | 0 | Vivek Gite | +----+------------+-------------+--------------+ 1 row in set (0.00 sec)
To exit simply type the:
mysql> exit;
Sample outputs:
Bye
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:
<?php //php7 tested //replace db, user and password $link = mysqli_connect("localhost", "my_user", "my_password", "my_db"); // did we connected? if (!$link) { echo "Error: Unable to connect to MySQL." . PHP_EOL; echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL; echo "Debugging error: " . mysqli_connect_error() . PHP_EOL; exit; } // run some query $query = "SELECT * FROM table"; $result = mysql_query($query); while ($line = mysql_fetch_array($result)) { foreach ($line as $value) { print "$value\n"; } } mysqli_close($link); ?>
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.
Upload the PHP file and type url https://your-domain-name-here/myscript.php
🐧 Get the latest tutorials on Linux, Open Source & DevOps via:
- RSS feed or Weekly email newsletter
- Share on Twitter • Facebook • 16 comments... add one ↓
Category | List of Unix and Linux commands |
---|---|
File Management | cat |
Firewall | Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04 |
Network Utilities | 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 • jobs • killall • kill • pidof • pstree • pwdx • time |
Searching | grep • whereis • which |
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 |
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..
Cheers
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)
e.g-
<?php
$link = mysql_connect("localhost", "USERNAME", "HOST");
mysql_select_db("DATABASE");
$query = "SELECT * FROM TABLE";
$result = mysql_query($query);
$range = "";
while ($line=mysql_fetch_array($result))
{
foreach ($line as $value)
{
//$range .= $value;
}
$range .= "$value”;
mysql_close($link);
}
?>
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”);
sorry
how to update and insert and edit the data
Thank you……
I want to connect mysql database with php trough cmd or command line please show this way for my
Thanks! Worked perfectly. great