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;
Error: Memory allocation error: 1038 Out of sort memory, consider increasing server sort buffer size
Solution: sort_buffer_size=512k in mysql.cnf

{{percentage}} % positive

THANK YOU for your review!
Top articles in this section
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.
The currently available Linux kernel has the version: 6.6.4 (found: 2023-12-04). Source: www.kernel.org. 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:
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.