MySQL (MariaDB)

The 825gen2 includes MariaDB, but the service may not be enabled to start automatically.

card825gen2:~$ mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Now mysqld starts up upon boot

card825gen2:~$ mysql --version
mysql  Ver 15.1 Distrib 10.7.4-MariaDB, for Linux (aarch64) using  EditLine wrapper

root@imx8mq-var-dart:~# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.7.4-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>


MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.002 sec)

MariaDB [(none)]> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [information_schema]> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| ALL_PLUGINS                           |
| APPLICABLE_ROLES                      |
| CHARACTER_SETS                        |
| CHECK_CONSTRAINTS                     |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENABLED_ROLES                         |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_CACHES                            |
| KEY_COLUMN_USAGE                      |
| OPTIMIZER_TRACE                       |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| SYSTEM_VARIABLES                      |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| GEOMETRY_COLUMNS                      |
| SPATIAL_REF_SYS                       |
| CLIENT_STATISTICS                     |
| INDEX_STATISTICS                      |
| user_variables                        |
| TABLE_STATISTICS                      |
| USER_STATISTICS                       |
+---------------------------------------+
44 rows in set (0.002 sec)

MariaDB [information_schema]>


MariaDB [information_schema]> use test;
Database changed
MariaDB [test]> show tables;
Empty set (0.001 sec)


MariaDB [test]> create table test_tbl(
    -> tbl_id INT NOT NULL AUTO_INCREMENT,
    -> title VARCHAR(100) NOT NULL,
    -> description VARCHAR(200) NOT NULL,
    -> PRIMARY KEY(tbl_id)
    -> );
Query OK, 0 rows affected (0.011 sec)


MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test_tbl       |
+----------------+
1 row in set (0.002 sec)


MariaDB [test]> select * from test_tbl;
Empty set (0.007 sec)

MariaDB [test]> insert into test_tbl (title,description) values ('title1', 'desc1');
Query OK, 1 row affected (0.002 sec)

MariaDB [test]> insert into test_tbl (title,description) values ('Moby Dick', 'A whale of a story');
Query OK, 1 row affected (0.002 sec)

MariaDB [test]> select * from test_tbl;
+--------+-----------+--------------------+
| tbl_id | title     | description        |
+--------+-----------+--------------------+
|      1 | title1    | desc1              |
|      2 | Moby Dick | A whale of a story |
+--------+-----------+--------------------+
2 rows in set (0.002 sec)

Create MySQL user to access database

CREATE USER 'dbuser'@'localhost' IDENTIFIED BY '81440';
flush privileges;

Allow user to access database 'test' from any IP address.

GRANT ALL PRIVILEGES ON test.* TO 'dbuser'@'%' IDENTIFIED BY '81440';
flush privileges;

Allow remote connection

nano /etc/my.cnf

[client]
#password                                       = password
port                                            = 3306
socket                                          = /var/lib/mysql/mysql.sock

[mysqld_safe]

[mysqld]
user                                            = mysql
port                                            = 3306
socket                                          = /var/lib/mysql/mysql.sock
pid-file                                        = /var/lib/mysql/mysqld.pid
log-error                                       = /var/log/mysqld.log
basedir                                         = /usr
datadir                                         = /var/lib/mysql
skip-external-locking
skip-networking
ignore-builtin-innodb
default-storage-engine                          = myisam
bind-address                                    = localhost

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Change bind-address from localhost (some files may show 127.0.0.1) to 0.0.0.0 to allow connections from any IP.

Insert # symbol in front of skip-networking to comment this out.

Ctrl-X and Y to save file.

Restart MySQL

sudo systemctl restart mysqld

Verify that MySQL is listening on port 3306