owned this note
owned this note
Published
Linked with GitHub
# MariaDB
[TOC]
## Installation on CentOS 7
:::info
This is based on the [official blog post](https://mariadb.com/resources/blog/installing-mariadb-10-on-centos-7-rhel-7/) published on Feburary 20, 2020.
:::
The package `mariadb-server` bundled with the CentOS 7 is actually [MariaDB Server 5.5](http://mirror.centos.org/centos/7/os/x86_64/Packages/#indexlist:~:text=mariadb%2Dserver%2D5.5.65%2D1.el7.x86_64.rpm),
yet MariaDB Server has evolved to version 10.5.
As of version 10.5, MariaDB comes in two flavors -- community and enterprise.
The following commands will install the latest MariaDB Communuty Server.
```
curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash -s --
sudo yum install -y MariaDB-server
```
The [OpenStack document](https://docs.openstack.org/install-guide/environment-sql-database-rdo.html#install-and-configure-components)
mandates that `/etc/my.cnf.d/openstack.cnf` is populated with the following settings.
```
[mysqld]
bind-address = 10.0.0.11
default-storage-engine = innodb
innodb_file_per_table = on
max_connections = 4096
collation-server = utf8_general_ci
character-set-server = utf8
```
Default settings for version 10.5 is quite different from that for version 5.5, making some of the specifications above unnecessary.
- `default-storage-engine = innodb` is the [default](default-storage-engine).
- `innodb_file_per_table = on` is the [default](https://mariadb.com/kb/en/innodb-system-variables/#innodb_file_per_table).
- `max_connections` is 151 by [default](https://mariadb.com/kb/en/server-system-variables/#max_connections)
which should be set higher manually.
- `collation-server` is `latin1_swedish_ci` by [defualt](https://mariadb.com/kb/en/server-system-variables/#collation_server)
which should be set to `utf8_general_ci` manually.
- `character-set-server` is `latin1` by [default](https://mariadb.com/kb/en/server-system-variables/#character_set_server)
which should be set to `utf8` manually.
This results in setting `/etc/my.cnf.d/openstack.cnf` with the content:
```
[mysqld]
bind-address = 10.0.0.11
max_connections = 4096
collation-server = utf8_general_ci
character-set-server = utf8
```
According to [this](https://mariadb.com/kb/en/configuring-mariadb-with-option-files/#default-option-file-locations-on-linux-unix-mac),
MariaDB will read `/etc/my.cnf` upon start.
The content of `/etc/my.cnf` is:
```
#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include *.cnf from the config directory
#
!includedir /etc/my.cnf.d
```
`!` before `includedir` does not mean `not` but indicates that `includedir` is a MySQL directive, instead; leave it this way.
`!includedir /etc/my.cnf.d` tells MariaDB to also check configuration files -- filenames that end with `.cnf` -- in the directory `/etc/my.cnf.d`.
Hence, we could have named `openstack.cnf` to something else, as long as its name is different from existing files in `/etc/my.conf.d/` and ends with `.cnf`.
However, there seems to be a catch.
According to [this serverfault post](https://serverfault.com/questions/918530/mysql-includedir-not-working),
the filename should only contain one `.`.
Now we can enable and start it with systemd.
```
sudo systemctl enable mariadb.service
sudo systemctl start mariadb.service
```
This way, the MariaDB daemon *mysqld* is started by the custom user *mysql* by default.
Due to [security reasons](https://mariadb.com/kb/en/running-mysqld-as-root/),
don't normally start the daemon with root.
In other words, start the daemon with systemd.
Embarassingly, I hit the following problem when I attempted to start the service.
```
systemd[1]: Starting MariaDB 10.5.5 database server...
mariadbd[2865]: 2020-08-20 16:06:38 0 [Warning] option 'open_files_limit': unsigned value 18446744073709551615 adjusted to 4294967295
mariadbd[2865]: 2020-08-20 16:06:38 0 [Note] /usr/sbin/mariadbd (mysqld 10.5.5-MariaDB) starting as process 2865 ...
mariadbd[2865]: 2020-08-20 16:06:38 0 [Warning] Could not increase number of max_open_files to more than 16384 (request: 32547)
systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
systemd[1]: Failed to start MariaDB 10.5.5 database server.
systemd[1]: Unit mariadb.service entered failed state.
systemd[1]: mariadb.service failed.
```
```
sudo mysql_secure_installation
```
CA key and cert.
```
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca.pem
```
Server key and cert.
```
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3650 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
```
Verification
```
openssl verify -CAfile ca.pem server-cert.pem
```
To enable TLS, add the following to `/etc/my.cnf.d/server.cnf`:
```
[mariadb]
...
ssl_cert = /etc/my.cnf.d/certificates/server-cert.pem
ssl_key = /etc/my.cnf.d/certificates/server-key.pem
ssl_ca = /etc/my.cnf.d/certificates/ca.pem
```
Restart the mariadb service, and check if `have_ssl` is `YES`:
```
mysql
MariaDB [(none)]> SHOW VARIABLES LIKE 'have_ssl';
```
```
mysql
MariaDB [(none)]> SET GLOBAL require_secure_transport=1;
```
# SQL Commands
## Browsing
Show databases
```sql
SHOW DATABASES;
```
Show tables
```sql
SHOW TABLES;
SHOW TABLES FROM database_name;
SHOW TABLES IN database_name;
```
Show columns
```sql
SHOW COLUMNS FROM table_name;
SHOW COLUMNS FROM database_name.table_name;
SHOW COLUMNS FROM table_name IN database_name;
SHOW FIELDS FROM table_name;
DESCRIBE table_name;
DESC table_name;
```
Show full columns
```sql
SHOW FULL COLUMNS FROM table_name;
```
Show create table_name
```sql
SHOW CREATE TABLE table_name;
```
Show index
```sql
SHOW INDEX FROM table_name;
```
Show character sets
```sql
Show CHARACTER SET;
```
Show warnings
```sql
SHOW WARNINGS;
```
Show process list
```sql
SHOW PROCESSLIST
```
## Creating & Modifying Database
Create database
```sql
CREATE DATABASE database_name
CREATE DATABASE database_name CHARACTER SET charset_name
```
Use database
```sql
USE database_name
```
Delete database
```sql
DROP DATABASE database_name
```
Change database
```sql
ALTER DATABASE database_name CHARACTER SET charset_name
```
## Creating & Modifying Table
Create new table
```sql
CREATE TABLE table_name (column_name data_type, ...);
CREATE TABLE table_name (column_name data_type AUTO_INCREMENT NOT NULL PRIMARY KEY
) ENGINE=engine_name DEFAULT CHARSET=charset_name COLLATE=collate
```
Add new row to certain columns
```sql
INSERT INTO table_name (column_name1, column_name2) VALUES (value1, value2), (value3, value4);
```
Add new row to all columns
```sql
INSERT INTO table_name VALUES (value_list), (value_list);
```
Add new row from another row
```sql
INSERT INTO table_name(value_list) SELECT (column_list) FROM another_table WHERE condition;
```
<!--
INSERT INTO table_name SET ... = ..., ... = ...;
INSERT IGNORE INTO table_name (...) VALUES (...), (...);
INSERT (LOW_PRIORITY | HIGH_PRIORITY) INTO table_name (...) VALUES (...), (...);
```
-->
Update values in the column that match the condition
```sql
UPDATE table_name SET column1 = new_value1, column2 = new_value2 WHERE condition;
```
## Credentials
Create user
```sql
CREATE USER username;
```
Grant/Revoke privileges
```sql
GRANT privilege ON database.table TO username@host IDENTIFIED BY password
REVOKE privilege ON database.table FROM username@host
```
View users
```sql
SELECT User, Host, Password FROM mysql.user;
```
## Comment
## Others
secure_file_priv
# MySQL
```
cd
python3 -m venv day2lab2
cd day2lab2
. bin/activate
pip install --upgrade pip
```
```
pip install PyMySQL
```
```
brew install mysql
mysql_secure_installation
```
Append the following content to `/usr/local/etc/my.cnf`:
```
init_connect = 'SET collation_connection = utf8_bin'
init_connect = 'SET NAMES utf8'
character-set-server = utf8
collation-server = utf8_bin
```
```
mysqld --initialize
```
## With Docker
```
docker run --name iottalk -p 3306:3306 -d mysql/mysql-server
mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED BY '<root_password>';
SET character_set_client = utf8mb4;
SET character_set_results = utf8mb4;
SET character_set_connection = utf8mb4;
CREATE DATABASE iottalk;
CREATE USER 'mcnlab'@'%' IDENTIFIED BY 'mcnlab';
GRANT ALL PRIVILEGES ON iottalk.* TO 'mcnlab'@'%';
```
```
curl -L -k -o FarmDashboard.zip 'https://docs.google.com/uc?export=download&id=1FkNUSX981nlLHzNtYbSVOEFOECUmSfrK'
unzip FarmDashboard.zip
cd FarmDashboard
pip install -r requirements.txt
curl -L -k -o weatherSTA.zip 'https://docs.google.com/uc?export=download&id=1Q6Q6MReYNL0wANqu56nzA6jo3BLifmYq'
```
Google Drive
Gist
Clipboard
Sign in with Wallet
