
mysql -u DBUsername -p
show databases;
MOJO:  use db_nsaffiliates
show tables;
MariaDB [db_nsaffiliates]> show tables;
+---------------------------+
| Tables_in_db_DBBName |
+---------------------------+
| tableone            |
| tabletwo      |
| tablethree             |
+---------------------------+


describe tableone;
describe tabletwo;
describe tablethree;
 
select * from tableone;
 
THEN:
SELECT * FROM tabletwo WHERE ColumName > ValueYouAreSearchingFor;



 insert into  peterfevernakedsword (peterfID,nsID) values ('33,'89');


mariadb-dump --user=nsaffiliates --password --databases db_nsaffiliates --single-transaction > db_nsaffiliates_02_05_24.sql


UPDATE peterfevernakedsword SET nsID = 505492 WHERE peterfID = 3129023 AND nsID = 0;

UPDATE peterfevernakedsword SET nsID = 505522 WHERE peterfID = 3129047 AND nsID = 0;


UPDATE peterfevernakedsword SET nsID = 505524 WHERE peterfID = 3129053 AND nsID = 0;


UPDATE peterfevernakedsword SET nsID = 505539 WHERE peterfID = 3129061 AND nsID = 0;

UPDATE peterfevernakedsword SET nsID = 505620 WHERE peterfID = 3129136 AND nsID = 0;

https://www.inmotionhosting.com/support/website/mariadb-view-databases/

When you are working with MariaDB to manage databases in cPanel, you will be working with the same interfaces as you did when working with MySQL. MariaDB is made to be a drop-in replacement for MySQL. In this tutorial, we will be showing a few commands using SSH to view databases with MariaDB.

For those who prefer to work on the command line, this article will instruct you on how to list databases or view databases with MariaDB via the command line interface (or, CLI). For many database admins, this is the preferred way to access MariaDB databases. For a graphical interface, you can use PhpMyAdmin.

How To List Databases in MariaDB

First, log into your server via SSH. Then, you will use the same command to log into MariaDB as you would with MySQL:

mysql -u <username> -p

You will be prompted for a password. Note: be sure to replace <username> with your actual cPanel username or, if on VPS, the root user is also available to you.

You will now see your normal command prompt has turned into the MariaDB prompt:

To properly list your databases, or show databases, you will just need to run the command:

SHOW DATABASES;

How To Use MariaDB To Work On a Database

You likely have a specific database in mind that you want to work with, in which case you will need to tell MariaDB which database you want to work on:

USE <databasename>;

Of course, replace <databasename> with the actual name of the database you want to use. If done correctly, you will see a notice that you have changed your database, and the MariaDB prompt will now print the name of the database in brackets:

Database changed MariaDB [<databasename>]>

How to View Tables in a MariaDB Database

To view the tables of a selected database, you can use the “show” command as follows:

SHOW tables;

How to Show All The Records in a Table in MariaDB

At this point, you have instructed MariaDB to show all databases, selected a database, viewed tables, and now it’s time to show all of the records recorded in a certain table, which you can do with a “select” statement:

SELECT * FROM <databasetablename>

And be sure to replace <databasetablename> with the name of the table. This command will show all records.

Note: that in order to view database specifics, you will need to have selected a database with the USE command (see above). Then you can use the “describe” command to get more data about this table:

DESCRIBE <databasetablename>;

Well done! You know how to view databases and tables in MariaDB right from the command line interface.

How To Use MySQL / MariaDB From Command Line

Tags: MySQL

While tools like phpMyAdmin interact with MySQL / MariaDB databases very easily, sometimes one must access the database directly from the command line. This article will touch on getting into a database and some common tasks but will not provide a full education on SQL syntax, database management, or other high-level topics. The examples in this guide are for CentOS 7 and MariaDB as included in our WordPress VPS image but should work on our cPanel VPSes, LAMP stack, and others. This page assumes you have Connected to Your Server with SSH.

prompts indicate something that should be entered from the Bash command line, > prompts are inside MySQL itself.

Common MySQL Tasks Done Through Command Line

To log in to the database as the root user, use the following command:

mysql -u root -p

Enter the root password.

Reset Your MySQL password

On CentOS 7:

systemctl stop mariadb
mysqld_safe --skip-grant-tables --skip-networking &
MySQL -u root

plaintext use mysql; update user set password=PASSWORD("InsertPasswordHere") where User='root'; where 'InsertPasswordHere' is a real password flush privileges; exit

systemctl stop mariadb
systemctl start mariadb

(Other systemd-based Linux distros may have similar commands depending on whether they run actual MySQL or MariaDB; other init systems will be different)

Once you run a the command below and enter your password, you will be presented with a prompt that tells you the program really running (MariaDB) and the database is used:

mysql -u root -p 
MariaDB [(none)]>

List Your Databases

Issue the show databases; command, as seen below, to see all databases. An example is shown below:

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| MySQL |
| performance_schema |
| tutorials |
+--------------------+
4 rows in set (0.00 sec)

Switch Database With The 'use ' command:

MariaDB [(none)]> use tutorials;
Database changed
MariaDB [tutorials]>

The 'show ' command is also used to list the tables in a database:

MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |

Always Take a Backup Before Making ANY Changes

Use mysqldump to make a backup of your database before continuing with this guide is strongly recommended.

mysqldump database name > databasebackup.sql

Replace the database name with your actual database name and database backup with the name of the file you would like to create and end it with .sql as the type of file to save your database. This will allow you to restore MySQL Databases Using mysqldump from this backup file at any time.

We recommend that you run this command from a directory that is not publicly accessible so that your database cannot be downloaded from your account without logging into the command line or FTP. Make sure to change your directory to /root or /home or another location in your file system that requires proper credentials.

Example: Resetting the WordPress Admin password

See the above instructions on how to take a backup of your database before proceeding.

Step One: You must know what database, username, and password are being used by the WordPress installation. These are in wp-config.php in the root directory of your WordPress installation as DB_NAME, DB_USER, and DB_PASSWORD:

// ** MySQL settings ** //
/** The name of the database for WordPress */
define( 'DB_NAME', 'appdb' );
/** MySQL database username */
define( 'DB_USER', 'appdb_user' );
/** MySQL database password */
define( 'DB_PASSWORD', '...' );

Step Two: With this information, you can adapt the instructions from How to Reset WordPress Admin Password and do the same from the command line:

mysql -u appdb_user -p
Enter password:
Welcome to the MariaDB monitor. Commands end with; or \g

…..

MariaDB [(none)]>

Step Three: Switch to the appdb database:

MariaDB [(none)]> use appdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Step Four: and show the tables:

MariaDB [appdb]> show tables;
+-----------------------+
| Tables_in_appdb    |
+-----------------------+
| wp_commentmeta  |
| wp_comments         |
| wp_links |
| wp_options |
| wp_postmeta |
| wp_posts |
| wp_term_relationships |
| wp_term_taxonomy |
| wp_termmeta |
| wp_terms |
| wp_usermeta |
| wp_users |
+-----------------------+
12 rows in set (0.00 sec)
MariaDB [appdb]>

Step Five: Then, we can SELECT user_login and user_pass from the wp_users table to see what row we will be updating:

MariaDB [appdb]> SELECT user_login,user_pass FROM wp_users;
+--------------+------------------------------------+
| user_login | user_pass |
+--------------+------------------------------------+
| hstwnd_admin | $P$BMCbERthYHPMJK.do2SUcxjAy567jo0 |
+--------------+------------------------------------+
1 row in set (0.00 sec)

Step Six: Which lets us set the new password with

MariaDB [appdb]> UPDATE wp_users SET user_pass=MD5('newpassword') WHERE user_login = 'hstwnd_admin';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Step Seven: And we can see the new password hash with the same SELECT again

MariaDB [appdb]> SELECT user_login,user_pass FROM wp_users;
+--------------+----------------------------------+
| user_login | user_pass |
+--------------+----------------------------------+
| hstwnd_admin | 5e9d11a14ad1c8dd77e98ef9b53fd1ba |
+--------------+----------------------------------+
1 row in set (0.00 sec)

To leave, we run 'quit'

MariaDB [appdb]> quit
Bye

That's it! Your new admin password is set.

TABBBBBBBBBBBBBBBBBBBBBBBB

Manage MySQL databases from the command line

Minutes

MySQL is one of the most popular relational database management systems. There are a variety of ways to manage a MySQL server – countless frontends including PhpMyAdmin, MySQL Workbench and even Adminer. However, clearly the most efficient way to manage a MySQL server is directly through command line.

The mysql command-line tool comes with the MySQL DBMS and is a simple SQL shell solution with input line editing capabilities. It supports both non-interactive and interactive use. In case of interactive use query results are presented in an ASCII-table format. In situation of non-interactive use the result is presented in tab-separated format. The output format can be changed using command options.

First you will need have to access your server via SSH in case of Linux. To connect to Windows server you need to use Remote Desktop. It is also possible to access your MySQL database via direct connection.

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

Once the command line is available, enter the following, where USERNAME is your database username:

`mysql -u USERNAME -p“`

You’ll be prompted for a password, enter your password. You should then see the “mysql>” prompt.
You can see a list of databases by typing in this command:

show databases;

To perform database tasks you need to tell MySQL which database you want to use. To access a particular database, type the following command, where DBNAME is the database you want to access:

use *DBNAME*;

Once you’ve run this command you have access to your database. Next, you can execute queries, get a listing of MySQL tables and much more. By the way, typing “help” will get you a list of commands you can use, while typing “\q” will get you out of MySQL.

Adding new users and creating a new database

You can add users and databases when you are logged in as the root user in MySQL. To log in as root, and create new users and databases, follow these steps:

Log into MySQL with root privileges using the following command:

mysql -u root -p

You’ll be prompted for your root password, fill it in and press return to proceed.
Creating a database is straight forward, you just need the following command. When entering it, replace username with your user you would like to add. Do that with password too. This is the command you need to enter:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

Note that the command we listed above will give the new user you create blanket permissions. Instead of granting all privileges you can limit the user. One example is the SELECT permission. To limit the user to permissions as specified by SELECT only you need to enter the following command:

GRANT SELECT ON *.* TO 'username'@'localhost';

Type \q to exit MySQL so that you can log in with the user that you have just created.
It’s simple to log in to MySQL with your new user, just enter the login command we specified previously, typing the new user’s name instead of Username:

mysql -u username -p

The user we create have all privileges assigned to them, including the ability to create a database. Create a database with the following command, using the name of your database instead of DBNAME.

CREATE DATABASE dbname;

Want to start using this newly created database? Run the following command, again replacing DBNAME with the name of your database:

USE dbname;

MySQL now knows which database you want to work with. You can create a database table by running the following command, for example:

CREATE TABLE example ( id smallint unsigned not null auto_increment, name varchar(50) not null, constraint pk_example primary key (id) );`
`INSERT INTO example ( id, name ) VALUES ( null, 'Sample data' );

Scripting in SQL

You don’t need to run every single SQL command one command at a time, as in our previous example. Instead you can execute several commands all in one go by making use of a SQL script file.

This is how you use a SQL script file to create a new database, and to populate your new database:

Just like we did in the previous example we start by creating a new user for your database. These are the commands you need:

mysql -u root -p`
`GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';`
`\q

Next, create a new file on your computer, and call it sql. You can use any text editor you like to use, as long as the file is stored in plain text.
Copy and paste the following into your text file:

CREATE DATABASE dbname;`
`USE dbname;`
`CREATE TABLE tablename ( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_example primary key (id) );`
`INSERT INTO tablename ( id, name ) VALUES ( null, 'Sample data' );

Before you save your file, change dbname into the name of your database and tablename into the name of the table you want to add to your new database.

Remember, you can modify this script to make as many tables as you like, and even to create multiple databases all in one go. Note that our example creates a very basic table: you might want to add more complex requirements by expanding on the script.

Save the changes to your file and close your text editor.
Processing your script is easy. Type the following command in, replacing username with the name of the user you created in the first step.

mysql -u username -p < example.sql

Note that MySQL will execute commands in a script one line at a time, in other words one statement at a time. In our example file, once MySQL has finished executing the entire file you will notice that a new database and a new table is created. Finally, the table will contain any data that you have specified in the INSERT statement.

Deleting a specific table, or an entire database

Performing a table deletion is not hard. Just type this command once you are logged into MySQL, using the name of the table you want to delete instead of tablename.

DROP TABLE tablename;

Note that to execute this command you need to have already selected which database you are working on via the use command.

Deleting a complete database is simple too. Just execute this command at the MySQL prompt, again replacing dbname with the name of the database you want to remove:

DROP DATABASE dbname;

Beware: MySQL will immediately remove the database when you submit that command, it does not ask for any type of confirmation: the database is permanently removed, including all of its contents.

Deleting a database user

You can view a full list of every database user by running the following command:

SELECT user FROM mysql.user GROUP BY user;

Deleting a single user is simple, just run the following command, but insert the name of the user you want to delete in the place of username:

DELETE FROM mysql.user WHERE user = 'username';

CLI w/ Maria DB

How To List Databases in MariaDB

How To Use MariaDB To Work On a Database

How to View Tables in a MariaDB Database

How to Show All The Records in a Table in MariaDB

How To Use MySQL / MariaDB From Command Line

prompts indicate something that should be entered from the Bash command line, > prompts are inside MySQL itself.

Common MySQL Tasks Done Through Command Line

Reset Your MySQL password

List Your Databases

Switch Database With The 'use ' command:

The 'show ' command is also used to list the tables in a database:

Always Take a Backup Before Making ANY Changes

Example: Resetting the WordPress Admin password

Manage MySQL databases from the command line

Adding new users and creating a new database

Scripting in SQL

Deleting a specific table, or an entire database

Deleting a database user

Image Editor - Bitmap PIXIE
Palleon | Palleon Documentation

Image Editor - Vector

How To List Databases in MariaDB

How To Use MariaDB To Work On a Database

How to View Tables in a MariaDB Database

How to Show All The Records in a Table in MariaDB

How To Use MySQL / MariaDB From Command Line

prompts indicate something that should be entered from the Bash command line, > prompts are inside MySQL itself.

Common MySQL Tasks Done Through Command Line

Login To MySQL Database

Reset Your MySQL password

List Your Databases

Switch Database With The 'use ' command:

The 'show ' command is also used to list the tables in a database:

Always Take a Backup Before Making ANY Changes

Example: Resetting the WordPress Admin password

Manage MySQL databases from the command line

Adding new users and creating a new database

Scripting in SQL

Deleting a specific table, or an entire database

Deleting a database user

Image Editor - Bitmap PIXIE Palleon | Palleon Documentation

Image Editor - Vector

Image Editor - Bitmap PIXIE
Palleon | Palleon Documentation