Common MySQL / MariaDB Commands

Log into mysql client:

General Linux

# mysql -u username -p
 Enter password: ****
 mysql> _

Plesk – Linux:

mysql -uadmin -p`cat /etc/psa/.psa.shadow `

Plesk – Windows: (Untested)

C:\Program Files\Parallels\Plesk\admin\bin>dbclient.exe --direct-sql --sql="select * from databaseservers where type='mysql' and host='localhost'"

WHM / cPanel

mysql -uroot

If this does not work, you might try using the root password or you may have to ask for it.

 

Backup a Database

To create a backup of a single database, run the following from the command line:

mysqldump -u username -p [password] database > database.sql

 

To create a backup of all databases, run the following from the command line:

mysqldump -u username -p [password] –-all-databases > filename

 

Restore a Database

To restore a single database:

mysql -u username -p [password] database < database.sql

* If the database already exists, you may need to edit the .sql file and comment out ( — [add 2 hyphens before] ) the ‘CREATE DATABASE database;’ line near the top of the file

If this does not work, try remove the database first:

mysql -u user -p [password]

DROP DATABASE database;
CREATE DATABASE database;
EXIT

Now try again

 

Restore a table

mysqlimport -u user -p [password] database databasetable.sql

Create a new database

mysql> CREATE DATABASE database_name;

Create Users

To create a new user on a database:

mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

Note: keep all single quotes exactly as shown.

To create a new Super User for all databases:

mysql>  GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

 

MySql Query Commands

USE DATABASE dbname;

ALTER TABLE $table DROP COLUMN $fieldName";

ALTER TABLE $table CHANGE COLUMN $oldFieldName $newFieldName;

ALTER TABLE $table ADD COLUMN $newFieldName TINYINT UNSIGNED NOT NULL AFTER $existingFieldName;

ALTER TABLE $table ADD INDEX $indexName($fieldName);

UPDATE $tableName SET $column1="value1", $column2="value2" WHERE $columnA="valueA" AND $columnB="valueB";

INSERT INTO $tableName ($column1, $column2) VALUES ($value1, $value2);

Export a MySql query data to a .csv file

{insert full query here} INTO OUTFILE '/tmp/querydata.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Import a .csv file to a table

mysqlimport --ignore-lines=1 \
            --fields-terminated-by=, \
            --columns='ID,Name,Phone,Address' \
            --local \
            -u root -p \
            Database \
            /path/to/tableName.csv

For a tab delimiter, use: –fields-terminated-by=’\t’

MySQL Engines

Use the following code to see which database engines are allowed. Some of these engines include:

mysql> SHOW ENGINES;

MyISAM Default engine as of MySQL 3.23 with great performance
MEMORY Hash based, stored in memory, useful for temporary tables
InnoDB Supports transactions, row-level locking, and foreign keys
BerkeleyDB Supports transactions and page-level locking
BLACKHOLE /dev/null storage engine (anything you write to it disappears)
EXAMPLE Example storage engine
ARCHIVE Archive storage engine
CSV CSV storage engine
ndbcluster Clustered, fault-tolerant, memory-based tables
FEDERATED Federated MySQL storage engine
MRG_MYISAM Collection of identical MyISAM tables
ISAM Obsolete storage engine

MySql Version

mysql --version

Or, you will see the version as soon as you log in.

.