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


create database

mysql> create database DATABASENAME;

empty all databases

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

delete database

mysql> drop database DATABASENAME;

show databases

mysql> show databases;

select database


Show tables

mysql> show tables;

Show tables layout

mysql> show columns from TABLE;

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



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='%';

Error: Memory allocation error: 1038 Out of sort memory, consider increasing server sort buffer size

Solution: sort_buffer_size=512k in mysql.cnf

positive Bewertung({{pro_count}})
Rate Post:
{{percentage}} % positive
negative Bewertung({{con_count}})

THANK YOU for your review!

Publication: 2022-10-07 from Bernhard | Übersetzung Deutsch |🔔 | Comments:0

VI Editor - Commands | Linux | Install Ubuntu Server and Docker Snap vs. APT

Top articles in this section

Fan control Linux Debian: Fancontrol

After I installed a fan in my NAS, it always ran at top speed. In the BIOS I could set the speed, but the automatic mode did not work properly. Using the service fancontrol the speed of the fan can be linked to any sensor and any threshold and controlled automatically.

Debian or Ubuntu kernel update or change- current kernel

The currently available Linux kernel has the version: 6.6.4 (found: 2023-12-04). Source: The Linux distributions, as an example Debian or Ubuntu used new kernel versions only with some delay, accordingly the used kernel versions are usually somewhat older. Which kernel is active on a Linux system can be read out with the following command:

Linux folder used space - Bash TreeSize

To find out which folder needs how much space, the command “du” can be used on Debian. For an even better overview there is the commandline tool ncdu, which can analyze all folders similar to TreeSize.

Questions / Comments

By continuing to browse the site, you agree to our use of cookies. More Details