SQL

Structured Query Language is a standard to access databases. See https://www.w3schools.com/sql/default.asp

There are 2 major GPL implementations:

  1. Mysql that is client server based and suitable for networks and multi users

  2. SQLite that is targeted for embedded applications, easy to go and has all database in a single file. It is widely used, since android makes use of it.

SQLite

SQLite http://www.sqlite.org/is ready to be used and needs no administrative server/client configurations. It is ready to use (the 3 is important otherwise the incompatible version 2 is used):

sqlite3 my.db

then the sqlite> prompt pops up and commands can be entered to create the table adr:

CREATE TABLE adr(first VARCHAR(20), family VARCHAR(20));

INSERT INTO adr VALUES('Urs','Lindegger');

SELECT * FROM adr; the * stands for the column names, and means all columns, alternatively a list of column names separated by , can be applied

If some column got forgotten add it with

ALTER TABLE adr ADD COLUMN email VARCHAR(20);

SQLite is not case sensitive and follows human readable syntax, however to distinguish what is SQL and what are user terms upper and lowercase is commonly used to style the commands.

.exit, .quit .q or Ctrl + D exits and the file my.db is left behind containing the SQLite database.

Alternatively it can be worked without entering the interactive prompt by adding the command to the command line:

sqlite3 my.db ".dump" will dump the database to the screen. or to be more efficient, type all commands in a file and run .tables will show all tables in the database

Note

SQLite understands additional commands that have names starting with a dot and written in lowercase as .tables

sqlite3 my.db < mysqlcommands.sql

Many bindings to programming language exist as c and python (where it is even part of the standard library. pysqlite is an alternative).

There are also gui's as sqliteman, sqlitebrowser or just use gda-browser the database browser that might come with gnome (emerge libgda).

SQLite has triggers that can be configured to create events when something gets modified.

MySQL

Due to license issues MySQL got forked itno MariaDB.

It is a key component in LAMP web severs.

Login to the sql server is required to select the database among the databases to be used.

MySQL can be operated via console. On OpenRC first start the sql server:

/etc/init.d/mysql start or on systemd service mysql start

Login as guest (press enter to not give a password)

mysql -u guest -h localhost -p

Or as root (press enter to not give a password, note that you can do this also without being Linux root)

mysql -u root -h localhost -p

Since this looks scary give it a password:

/usr/bin/mysqladmin -u root -h localhost password 'new-password'

Accessing the database

on Gentoo /etc/mysql/mariadb.d/50-distro_server.cnf or Raspberry PI OS /etc/mysql/mariadb.conf.d/50-server.cnf

bind-address            = 127.0.0.1

to

bind-address            = 0.0.0.0

Database users

To see the known users

SELECT User,Host FROM mysql.user;

As the result shows, the users are linked to a Host (as localhost) accessing from an other host will fail.

CREATE USER '<username>'@'<ip_address>' IDENTIFIED BY '<password>';

<ip_address> can be:

  • a number as 192.168.1.8

  • a string as localhost

  • the wildchart character % to allow all addresses

  • or 192.168.1.% to allow access from local network

FLUSH PRIVILEGES;

mysql -u <username> -h <ip_address> -p

to see the privileges

SHOW GRANTS FOR '<username>'@'<ip_address>';

GRANT ALL PRIVILEGES ON <database>.* TO '<username>'@'<ip_address>';

DROP USER '<username>'@'<ip_address>'

Exporting and Importing databases

Databases can be exported as <name>.sql or zipped as <name>.sql.zip looking into those files shows that it contains sql code. So importing means just run this code. Export and import can be done via phpmyadmin. For the import an empty database of the same name must be created.

On command line to export the db

mysqldump -u <username> -p <database_name> > <data-dump>.sql

For a backup there might be good to zip it and add a date info:

mysqldump --user=<user> --password=<password> --dump-date <db name> |gzip > <path>/MySQLDBBK$(date +%Y_%m_%d__%H_%M_%S$%M%S).sql.gz

to import log in as mysql -u <username> -p or as root mysql

create a new empty db CREATE DATABASE <database>;

exit the db

If zipped then gunzip <data-dump>.sql.gz to get <data-dump>.sql

an mysql -u <username> -p <database> < <data-dump>.sql or as root mysql <database> < <data-dump>.sql

Since this is a new database the users might not have access so grant the permissions

SQL Commands

Some commands

SHOW DATABASES;

CREATE DATABASE <my database>;

USE <my database>;

SHOW TABLES;

CREATE TABLE <name of table> ( <name of 1 column> VARCHAR(128), <name of 2 column> VARCHAR(128), <name of 3 column> VARCHAR(128));

DESCRIBE <name of table>;

DROP DATABASE <my database>; to delete the database and loose everything

and finally quit to get out

To get a web application that runs in a web browser: emerge phpmyadmin to have a gui. Read the post install instructions, how to create the config file:

mkdir /var/www/localhost/htdocs/phpmyadmin/config

chown apache:apache /var/www/localhost/htdocs/phpmyadmin/config

then go to http://localhost//phpmyadmin/scripts/setup.php

cp /var/www/localhost/htdocs/phpmyadmin/config/config.inc.php /var/www/localhost/htdocs/phpmyadmin/config.inc.php

rm -rf /var/www/localhost/htdocs/phpmyadmin/config

To work with it use: http://localhost/phpmyadmin/index.php

See https://wiki.gentoo.org/wiki/MySQL/Startup_Guide.

The config files are in /etc/mysql per default the data is in /var/lib/mysql.

To export to do a backup

mysqldump db_name > backup-file.sql

or for backups on the same machine use the faster way:

mysqlhotcopy


Linurs startpage