In short: To work with databases from the command line: connect over SSH, type mysql to log in, then show databases; to list databases, use DBNAME; to select one, and show tables; to list its tables. To create a database and user: CREATE DATABASE, then CREATE USER, then GRANT ALL PRIVILEGES, then FLUSH PRIVILEGES.
You can manage databases directly from the shell, without phpMyAdmin, using the mysql client. First connect to your server over SSH (see how to connect to your server over SSH).
Browse databases #
Log in to MySQL:
mysql
- List databases:
show databases; - Select a database:
use DBNAME; - List its tables:
show tables; - List a table’s columns:
show columns from TABLENAME; - List the tables of another database:
show tables from DBNAME; - Leave MySQL:
exit
Create a database and a user #
Log in as root and enter the MySQL root password:
mysql -u root -p
CREATE DATABASE DBNAME; CREATE USER 'DBUSER'@'localhost' IDENTIFIED BY 'DBPASS'; GRANT ALL PRIVILEGES ON DBNAME.* TO 'DBUSER'@'localhost'; FLUSH PRIVILEGES; exit
Replace DBNAME, DBUSER and DBPASS with your own values, and use a strong password.
Note: on cPanel servers, create databases and users from cPanel (MySQL Databases) so they are linked to the right account and included in its backups. To import a large .sql file, see how to import a MySQL database over SSH.
Related service: Hyyat Host server management handles database administration on your server, with 24/7 support.
