wiki:Docs/825gen2/Dev/Database/MySQL

MySQL (MariaDB)

The 825 Gen2 includes MariaDB. By default a new 825 may not have the service enabled to start automatically.

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

Use the "Setup Menu" "Setup Services" to turn on MySQL.

Now mysqld starts up upon boot

card825gen2:~$ mysql --version
mysql  Ver 15.1 Distrib 10.7.4-MariaDB, for Linux (aarch64) using  EditLine wrapper
card825gen2:~$ sudo 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;

To exit mysql type "quit"

MariaDB [(none)]> quit
Bye

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

From PC on same network as 825 check connection to the database using MySQL Workbench

Create a connection specify IP address of 825. Username dbuser and password 81440

Client library

Legacy 825 apps may be static linked to libmysqlclient in the workspace: Include paths: "${workspace_loc:/libmysqlclient/inc}" Libraries: mysqlclient Library seach path: "${workspace_loc:./libmysqlclient/Release}"

New ARM64 825 apps should use the library already present on the 825.

Last modified 8 weeks ago Last modified on 09/19/24 07:15:11

Attachments (1)

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.