Preface
Goal: Setting-up LAMP stack with Artix OpenRC. Configure MariaDB.
This part, discuss about SQL statement.
Getting into Prompt
To get in to the prompt, you just need to issue this one command.
❯ mariadbWelcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.11.2-MariaDB Artix Linux
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)]>
Show Databases
As a regular user we only see this two table.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.001 sec)
Show Grants
The privilege is also limited.
The output is a long table. My terminal won’t fit, so I crop a fullscreen screenshot instead.
MariaDB [(none)]> show grants;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for @localhost |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ``@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, EVENT, TRIGGER, DELETE HISTORY ON `test`.* TO PUBLIC |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, EVENT, TRIGGER, DELETE HISTORY ON `test\_%`.* TO PUBLIC |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.000 sec)
Terminal Reset
You can reset the terminal using external shell command .
MariaDB [(none)]> \! reset
Exit Prompt
You can quit anytime.
MariaDB [(none)]> exit
Bye
Get in as Root
As a root or superadmin, you can also get into the prompt.
❯ sudo mariadb -u root -p
Enter password: (leave blank)
MariaDB [(none)]> 
Show Databases
With root, we have more tables.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| #mysql50#.cache |
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.001 sec)
Show Grants
With root you have more privileges.
The output is a long table. My terminal won’t fit, so I crop a fullscreen screenshot instead.
MariaDB [(none)]> show grants;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, EVENT, TRIGGER, DELETE HISTORY ON `test`.* TO PUBLIC |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, EVENT, TRIGGER, DELETE HISTORY ON `test\_%`.* TO PUBLIC |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.000 sec)
Set Password
The reason to rewrite article.
To setup maridb later, such as for use with phpmyadmin, we need to set password.
❯ sudo mariadb -u root -p
Enter password: (leave blank)
MariaDB [(none)]> SET PASSWORD FOR 'root'@localhost = PASSWORD('blewah');
Query OK, 0 rows affected (0.002 sec)
The way mariadb manage password has changed after some time.
Please do not use old blog reference.
Get Insight
Consider this prompt as your new playground. But first we need an example data right?
For data that you need to change, you can create your own table. Or use test table. But if you only need to see some stuff, you can use any table.
Use Database
Consider use the mysql table.
MariaDB [(none)]> use mysql;
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
As the database changed, we can continue.
Show Tables
I need to see what is inside this database.
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
| event |
| func |
| general_log |
| global_priv |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| roles_mapping |
| servers |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| transaction_registry |
| user |
+---------------------------+
31 rows in set (0.001 sec)
Wow, it is huge.
Describe Table
Consider have a look at the user table.
MariaDB [mysql]> describe user;
+------------------------+---------------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+----------+-------+
| Host | char(255) | NO | | | |
| User | char(128) | NO | | | |
| Password | longtext | YES | | NULL | |
| Select_priv | varchar(1) | YES | | NULL | |
| Insert_priv | varchar(1) | YES | | NULL | |
| Update_priv | varchar(1) | YES | | NULL | |
| Delete_priv | varchar(1) | YES | | NULL | |
| Create_priv | varchar(1) | YES | | NULL | |
| Drop_priv | varchar(1) | YES | | NULL | |
| Reload_priv | varchar(1) | YES | | NULL | |
| Shutdown_priv | varchar(1) | YES | | NULL | |
| Process_priv | varchar(1) | YES | | NULL | |
| File_priv | varchar(1) | YES | | NULL | |
| Grant_priv | varchar(1) | YES | | NULL | |
| References_priv | varchar(1) | YES | | NULL | |
| Index_priv | varchar(1) | YES | | NULL | |
| Alter_priv | varchar(1) | YES | | NULL | |
| Show_db_priv | varchar(1) | YES | | NULL | |
| Super_priv | varchar(1) | YES | | NULL | |
| Create_tmp_table_priv | varchar(1) | YES | | NULL | |
| Lock_tables_priv | varchar(1) | YES | | NULL | |
| Execute_priv | varchar(1) | YES | | NULL | |
| Repl_slave_priv | varchar(1) | YES | | NULL | |
| Repl_client_priv | varchar(1) | YES | | NULL | |
| Create_view_priv | varchar(1) | YES | | NULL | |
| Show_view_priv | varchar(1) | YES | | NULL | |
| Create_routine_priv | varchar(1) | YES | | NULL | |
| Alter_routine_priv | varchar(1) | YES | | NULL | |
| Create_user_priv | varchar(1) | YES | | NULL | |
| Event_priv | varchar(1) | YES | | NULL | |
| Trigger_priv | varchar(1) | YES | | NULL | |
| Create_tablespace_priv | varchar(1) | YES | | NULL | |
| Delete_history_priv | varchar(1) | YES | | NULL | |
| ssl_type | varchar(9) | YES | | NULL | |
| ssl_cipher | longtext | NO | | | |
| x509_issuer | longtext | NO | | | |
| x509_subject | longtext | NO | | | |
| max_questions | bigint(20) unsigned | NO | | 0 | |
| max_updates | bigint(20) unsigned | NO | | 0 | |
| max_connections | bigint(20) unsigned | NO | | 0 | |
| max_user_connections | bigint(21) | NO | | 0 | |
| plugin | longtext | NO | | | |
| authentication_string | longtext | NO | | | |
| password_expired | varchar(1) | NO | | | |
| is_role | varchar(1) | YES | | NULL | |
| default_role | longtext | NO | | | |
| max_statement_time | decimal(12,6) | NO | | 0.000000 | |
+------------------------+---------------------+------+-----+----------+-------+
47 rows in set (0.003 sec)
Talking about SQL would take several days. I have a lot of fun changing thing with my own database. And it deserve its own article series. What I should do now is shut my mouth up. Stop talking about this SQL stuff.
I think that’s all for now. We need to moved on to other topic.
What is Next 🤔?
One service, and another. After the database, comes the webserver,
Consider continue reading [ LAMP - Apache Setup ].
Thank you for reading.