MySQL commands in Linux: connection, database, backup

Here are the main commands for managing a MySQL database in the terminal.

MySQL Client

If the MySQL command is not available on Debian, it can be installed with the following command

sudo apt-get install mysql-client

Connect to the database

mysql -h HOSTNAME -u myUsername -pMYPASSWORD

(this is not a typo: there is no space after -p)

after the connection is established, the terminal will say

mysql>

create database

mysql> create database DATABASENAME;

empty all databases

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('DBNAME');
mysql> SET FOREIGN_KEY_CHECKS = 1;

delete database

mysql> drop database DATABASENAME;

show databases

mysql> show databases;

select database

mysql> use DATENBANKNAME;

Show tables

mysql> show tables;

Show tables layout

mysql> show columns from TABLE;

TABLE means a table that was previously displayed with "show tables;".

Query

mysql> SELECT * FROM TABLE;

TABLE means a table that was previously displayed with"show tables;".

Database Backup

mysqldump --user=myUsername --password=myPASSWORD -h HOST Databasename > '/path/database.sql.gz'

Database restore from sql.gz

gunzip < '/path/database.sql.gz' | mysql -u myUsername -pMYPASSWORD DBNAME

Linux root password reset 16.04LTS

sudo service mysql stop
sudo mkdir /var/run/mysqld
sudo chown mysql: /var/run/mysqld
sudo mysqld_safe --skip-grant-tables --skip-networking
mysql -u root mysql
UPDATE mysql.user SET authentication_string=PASSWORD('root'), plugin='mysql_native_password' WHERE User='root' AND Host='%';
EXIT;