Series: Administering MySQL from the command line

How to install MariaDB on Alpine Linux

Alpine Linux is a minimalist distribution with a focus on security and simplicity.

MariaDB is one of the most popular database servers in the world, created by the original MySQL developers and initially conceived as a direct and improved MYSQL replacement. MariaDB is fast, scalable, and robust, with a rich ecosystem of storage engines, plugins, and other tools that make it versatile and flexible in different scenarios. MariaDB is developed as free software under the GPL license. The latest versions of MariaDB also include GIS and JSON functions.

How to install MySQL 8.0 in Debian

MySQL

MySQL is a fast, efficient, secure, stable, easy-to-use, multi-platform, multi-threaded, multi-user, and well-documented relational database manager/server. Currently Oracle guides the development of MySQL, however the community version is available under the GPL license so it is still free software.

MySQL 8.0:

  • Add a new caching_sha2_password authentication plugin is available. Like the sha256_password plugin, caching_sha2_password implements SHA-256 password hashing, but uses caching to address latency issues at connect time.
  • Supports roles, which are named collections of privileges. Roles can be created and dropped. Roles can have privileges granted to and revoked from them. Roles can be granted to and revoked from user accounts.
  • Incorporates the concept of user account categories, with system and regular users distinguished according to whether they have the SYSTEM_USER privilege
  • Maintains information about password history, enabling restrictions on reuse of previous passwords also it is possible to configure user accounts such that too many consecutive login failures due to incorrect passwords cause temporary account locking.
  • Add the new authentication mechanism: caching_sha2_password that implements the SHA-256 encryption algorithm (like the sha256_password mechanism) but using a cache to solve latency problems during the connection process.
  • Incorporates several improvements to the InnoDB engine and the JSON data type.

MySQL 8.0, change root password

MySQL

When you install MySQL 8.0 in Ubuntu 20.04 the root has enabled the auth_socket plugin by default

How to install MySQL 8.0 in Ubuntu 20.04

MySQL

MySQL is a fast, efficient, secure, stable, easy-to-use, cross-platform (GNU / Linux, * BSD, Windows, …), multi-threaded, multi-user and well-documented relational database server. It is currently developed under the guidance of Oracle, however the community version is available under the GPL (a free software license).

MySQL 8.0 supports roles, which are named collections of privileges. Roles can be created and dropped also add the concept of user account categories and now maintains information about password history, enabling restrictions on reuse of previous passwords, for example a DB administrator can configure user accounts such that too many consecutive login failures due to incorrect passwords cause temporary account locking. Several improvements to the InnoDB engine and the JSON data type.

How to install MySQL 5.7 on CentOS 7

MySQL

MySQL is a relational database server with a great reputation for being fast, efficient, safe, stable, easy to use, multiplatform (GNU/Linux, * BSD, Windows,...) and well documented. The current MySQL development process is under Oracle's umbrella but the community version is released as free software and you can use under GPL terms.

MySQL - Execute SQL script

MySQL

Many times we need to run a SQL script and we do not have graphical tools such as: phpMyAdmin or Adminer so we need to use the command line and use the mysql shell and follow one of the following methods:

Create / modify / delete tables in MySQL

One of the frequent tasks during the project development process is the change of the structure of the database. This guide aims to show how to create, modify and delete tables from a MySQL database. However the sentence involved in these operations are extensive and complex and cover all options would be cumbersome and impractical. For more information refer to the official site of MySQL

Disable innodb engine in MySQL

MySQL

Show MySQL storage engines

To see the available/enabled MySQL storage engines first you must connect to your mysql server

$ mysql - u root - p - h SERVER

Then run

Character set available in MySQL

MySQL

MySQL offers several types of charset (CHARACTER_SET), thereby satisfying the most varied projects.

See character set supported by the MySQL Server
> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------
| Charset  | Description                 | Default collation   
+----------+-----------------------------+--------------------
| big5     | Big5 Traditional Chinese    | big5_chinese_ci    
| dec8     | DEC West European           | dec8_swedish_ci    
| cp850    | DOS West European           | cp850_general_ci   
| hp8      | HP West European            | hp8_english_ci     
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci   
| latin1   | cp1252 West European        | latin1_swedish_ci  
| latin2   | ISO 8859-2 Central European | latin2_general_ci  
| swe7     | 7bit Swedish                | swe7_swedish_ci    
| ascii    | US ASCII                    | ascii_general_ci   
....
See available COLLATION for a CHARACTER SET
> SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS 
WHERE CHARACTER_SET_NAME = 'utf8';
+--------------------+
| COLLATION_NAME     |
+--------------------+
| utf8_general_ci    |
| utf8_bin           |
| utf8_unicode_ci    |
| utf8_icelandic_ci  |
| utf8_latvian_ci    |
| utf8_romanian_ci   |
| utf8_slovenian_ci  |
...
See COLLATION, CHARACTER_SET for the columns in a particular table

Here we take as an example the BD: Sakila

> SELECT COLUMN_NAME,COLLATION_NAME, CHARACTER_SET_NAME FROM 
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'actor' AND 
TABLE_SCHEMA = 'sakila';
+-------------+-----------------+--------------------+
| COLUMN_NAME | COLLATION_NAME  | CHARACTER_SET_NAME |
+-------------+-----------------+--------------------+
| actor_id    | NULL            | NULL               |
| first_name  | utf8_general_ci | utf8               |
| last_name   | utf8_general_ci | utf8               |
| last_update | NULL            | NULL               |
+-------------+-----------------+--------------------+

Recommended reading

- F. van der lan, Rick. SQL for MySQL Developers, Part III, Chapter 22.