MariaDB - HackMD

# 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; ```  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' ```