Key Points

References

...

https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-rest-api-setup.html

mysql-router-rest-api-dev.mysql-61nbspA Simple MySQL Router REST API Guide.pdf

...

https://www.tarynpivots.com/post/how-to-rotate-rows-into-
columns-in-mysql/

mysql-pivot-table-from-case-stmt-x1.pdf

...

Procedure and generation examples

...

https://drive.google.com/open?id=0BxqKQGV-b4WQMlBYa041VFdQdTg

C:\Users\Jim Mason\Google Drive\_ptp\work\grails\_gdev\gtest1a.groovy

gtest1a.groovy

gtest6.groovy

...

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-overview.html

...

Key Points

References

Key Features

Key Concepts

MySQL is an open-source, high performance database server with a variety of connectivity options.

It combines multiple database server types into a single package:

• RDB -Relational Database Server
• NoSQL - JSON document server that can store, update, query using SQL ( similar to Mongoose with MongoDB )
• Wide Column Tables - used in high performance query operations for aggregate reporting over denormalized data
• Geo location - When geolocation info stored in JSON docs, geolocation queries can be executed

The MySQL workbench provides an easy admin / developer toolset to view, manage the MySQL server, schedule and run batch jobs, view performance and more.

Almost any SQL client can connect to MySQL server ( eg Squirrel, and more )

Many open-source, visual reporting tools can use MySQL ( BIRT, Grafana, PowerBI and more )

MySQL install 

https://dev.mysql.com/

...

MySQL Connector/J Guide

MySQL Connector/J X DevAPI Reference

...

https://dev.mysql.com/doc/connector-net/en/

https://dev.mysql.com/doc/dev/connector-net/8.0/html/connector-net-reference.htm

MySQL .Net  Connector Guide

...

downloads/

Windows install

MySQL Php Driver install

https://dev.mysql.com/

...

downloads/connector/php-

...

https://logz.io/blog/relational-database-comparison/

compare-mysql-logz.io-Sqlite vs MySQL vs PostgreSQL A Comparison of
Relational Databases.pdf

compare-mysql-logz-Sqlite vs MySQL vs PostgreSQL A Comparison of Relational Databases.pdf

...

https://blog.panoply.io/postgresql-vs.-mysql

...

Postgres vs MySQL 

many db comparison posts

...

Postgres vs MySQL - Jim comparison

I'll give the tutorial credit for factual accuracy - that's good.
If you look at the listed limitations for MySQL, most of those are correct.
Not skilled in Postgres but cover DB2, SQL Server, Oracle, Mongo. Couchdb, Derby and more. Maybe the most attractive feature is the data types compared to MySQL.

As a data architect using MySQL, 75% of those limitations are easily solved using MySQL: recursion, materialized views, external procedures, application data replays, automated database change management, automated event streams etc. It has full ACID compliance using InnoDB and scales well for big data projects: 3.2 billion rows written by a single server over 19 hours on an older version of MySQL.

...

Key Features

Key Concepts

MySQL is an open-source, high performance database server with a variety of connectivity options.

It combines multiple database server types into a single package:

• RDB -Relational Database Server
• NoSQL - JSON document server that can store, update, query using SQL ( similar to Mongoose with MongoDB )
• Wide Column Tables - used in high performance query operations for aggregate reporting over denormalized data
• Geo location - When geolocation info stored in JSON docs, geolocation queries can be executed

The MySQL workbench provides an easy admin / developer toolset to view, manage the MySQL server, schedule and run batch jobs, view performance and more.

Almost any SQL client can connect to MySQL server ( eg Squirrel, and more )

Many open-source, visual reporting tools can use MySQL ( BIRT, Grafana, PowerBI and more )mysqlnd/

MACOS  - Linux install

download community edition package

install for all users

for v8x, use old password encryption vs higher encryption

set root password 

Syn#

decide if MySQL server should start automatically or manually

Image Added

Image Added

Image Added

Image Added

Troubleshoot failed server startup

https://dev.mysql.com/doc/refman/8.0/en/starting-server-troubleshooting.html

Log files are located in the data directory (typically C:\Program Files\MySQL\MySQL Server 8.0\data on Windows, /usr/local/mysql/data for a Unix/Linux binary distribution, and /usr/local/var for a Unix/Linux source distribution). Look in the data directory for files with names of the form host_name.err and host_name.log, where host_name is the name of your server host. Then examine the last few lines of these files. Use tail to display them:

shell> tail host_name.err
shell> tail host_name.log

jim-macbook:/ jimmason$ cd /usr/local/mysql
jim-macbook:mysql jimmason$ ls -l
total 552
-rw-r--r--   1 root    wheel   276551 Jul  1 03:53 LICENSE
-rw-r--r--   1 root    wheel      666 Jul  1 03:53 README
drwxr-xr-x  35 root    wheel     1120 Aug 29 22:45 bin
drwxr-x---  27 _mysql  _mysql     864 Aug 29 23:42 data
drwxr-xr-x   5 root    wheel      160 Jul  1 05:42 docs
drwxr-xr-x  16 root    wheel      512 Jul  1 05:42 include
drwxr-x---   2 _mysql  _mysql      64 Aug 29 22:53 keyring
drwxr-xr-x  17 root    wheel      544 Aug 29 22:45 lib
drwxr-xr-x   4 root    wheel      128 Jul  1 05:42 man
drwxr-xr-x  34 root    wheel     1088 Jul  1 05:42 share
drwxr-xr-x   5 root    wheel      160 Jul  1 05:42 support-files

tailing log shows security error running mysqld as root user

2021-08-30T03:48:11.926750Z 0 [ERROR] [MY-010123] [Server] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

https://stackoverflow.com/questions/41995912/macos-cant-start-mysql-server

A sample my.cnf config file

my.cnf:

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock
datadir = /usr/local/mysql-5.7.17-macos10.12-x86_64/data
tmpdir = /tmp

MySQL admin

MySQLD default login

...

https://www.a2hosting.com/kb/developer-corner/mysql/reset-mysql-root-password-mysql-root-password

https://tableplus.com/blog/2018/11/what-is-the-default-username-password-in-mysql.html

Access server environment to reset

You must have root access on the server to reset MySQL's root password.

if remote, login with ssh

You must run the commands in the following steps as the root user. Therefore, you can either log in directly as the root user (which is not recommended for security reasons), or use the su or sudo commands to run the commands as the root user.

Stop the MySQL service or daemon first

service mysql stop
or 
mysqld stop

...

mysql client login to validate

mysql -u jemadm -pmypasswordp

mypassword

select version();

exit;

-- console.log(`user Syn$ logged in`);

...

var mysqlx = require('@mysql/xdevapi');

// Connect to server on localhost
mysqlx
  .getSession({
    user: 'user',
    password: 'password',
    host: 'localhost',
    port: '33060'
  })
  .then(function (session) {
    var db = session.getSchema('test');
    // Use the collection 'my_collection'
    var myColl = db.getCollection('my_collection');
    // Specify which document to find with Collection.find() and
    // fetch it from the database with .execute()
    return myColl
      .find('name like :param')
      .limit(1)
      .bind('param', 'L%')
      .execute(function (doc) {
        console.log(doc);
      });
  })
  .catch(function (err) {
    // Handle error
  });

Java ex

import com.mysql.cj.xdevapi.*;

// Connect to server on localhost
Session mySession = new SessionFactory().getSession("mysqlx://localhost:33060/test?user=user&password=password");

Schema myDb = mySession.getSchema("test");

// Use the collection 'my_collection'
Collection myColl = myDb.getCollection("my_collection");

// Specify 

...

which 

...

document to 

...

import com.mysql.cj.xdevapi.*;

// Connect to server on localhost
Session mySession = new SessionFactory().getSession("mysqlx://localhost:33060/test?user=user&password=password");

Schema myDb = mySession.getSchema("test");

// Use the collection 'my_collection'
Collection myColl = myDb.getCollection("my_collection");

// Specify which document to find with Collection.find() and
// fetch it from the database with .execute()
DocResult myDocs = myColl.find("name like :param").limit(1).bind("param", "L%").execute();

// Print document
System.out.println(myDocs.fetchOne());

mySession.close();

Using Grab for Groovy to load JDBC driver

https://gist.github.com/jlmelville/57af8e92a2211189178a

// MySQL

@Grab(group='com.mysqll', module='postgresql', version='9.3-1101-jdbc41')

def url = "jdbc:postgresql://${hostname}:${port}/${db}"

def driver = "org.postgresql.Driver"

def validationQuery = "select 1"

// Postgres

@Grab(group='org.postgresql', module='postgresql', version='9.3-1101-jdbc41')

def url = "jdbc:postgresql://${hostname}:${port}/${db}"

def driver = "org.postgresql.Driver"

def validationQuery = "select 1"

MySQL .Net Connector

https://dev.mysql.com/doc/connector-net/en/connector-net-connections.html

Troubleshooting .Net Connector

https://dev.mysql.com/doc/connector-net/en/connector-net-connections-errors.html

MySQL ODBC Connector

https://dev.mysql.com/doc/refman/8.0/en/connector-odbc-info.html

Manage MySQL Query Load with Kafka

https://stackshare.io/pinterest/using-kafka-to-throttle-qps-on-mysql-shards-in-bulk-write-apis

mysql-kafka-load-balancer-stackshare.io-Using Kafka to Throttle QPS on MySQL Shards in Bulk Write APIs.pdf

To support high QPS from internal clients at the same time, all requests from them should be stored temporarily in the platform and processed at a controlled speed. This is where Kafka makes a good fit for these purposes.

1. Kafka can handle very high qps write and read.
2. Kafka is a reliable distributed message storage system to buffer batch requests so that requests are processed at a controlled rate.
3. Kafka can leverage the re-balancing of load and manage consumers automatically.
4. Each partition is assigned to one consumer exclusively (in the same consumer group) and the consumer can process requests with good rate-limiting.
5. Requests in all partitions are processed by different consumer processors simultaneously so that throughput is very high.

Image Removed

MariaDB compare to MySQL DB - 2020 

https://www.eversql.com/mariadb-vs-mysql/#:~:text=MariaDB%20Server%20is%20licensed%20as,not%20the%20case%20with%20MySQL.

MariaDB vs MySQL Key Differences Comparing MySQL 80 with MariaDB 105.pdf gdrv

The goal of this article is to evaluate and highlight the main similarities and differences between the MySQL Server Database and the MariaDB Server Database. We’ll look into performance aspects, security, main features, and list all aspects which need to be considered before choosing the right database for your requirements.

For MySQL, we can see names such as Facebook, Github, YouTube, Twitter, PayPal, Nokia, Spotify, Netflix and more.

For MariaDB, we can see names such as Redhat, DBS, Suse, Ubuntu, 1&1, Ingenico, Gaming Innovation Group, BlaBla Cars and more.

Oracle DB compare to MySQL DB 

https://docs.oracle.com/cd/E12151_01/doc.150/e12155/oracle_mysql_compared.htm#CHDIIBJH

Oracle and MySQL Compared.pdf gdrv

MySQL Procedures Tutorial - W3C

https://www.w3resource.com/mysql/mysql-procedure.php

MySQL External Language Procedures - OReilly

https://docs.huihoo.com/mysql/2008/A-Tour-of-External-Language-Stored-Procedures-for-MySQL.pdf

mysql-external-language-stored-procs-2008.pdf

MySQL External Procedure RFC

https://dev.mysql.com/worklog/task/?id=820

Potential Value Opportunities

...

find with Collection.find() and
// fetch it from the database with .execute()
DocResult myDocs = myColl.find("name like :param").limit(1).bind("param", "L%").execute();

// Print document
System.out.println(myDocs.fetchOne());

mySession.close();

Using Grab for Groovy to load JDBC driver

https://gist.github.com/jlmelville/57af8e92a2211189178a

// MySQL

@Grab(group='com.mysqll', module='postgresql', version='9.3-1101-jdbc41')

def url = "jdbc:postgresql://${hostname}:${port}/${db}"

def driver = "org.postgresql.Driver"

def validationQuery = "select 1"

// Postgres

@Grab(group='org.postgresql', module='postgresql', version='9.3-1101-jdbc41')

def url = "jdbc:postgresql://${hostname}:${port}/${db}"

def driver = "org.postgresql.Driver"

def validationQuery = "select 1"

MySQL .Net Connector

https://dev.mysql.com/doc/connector-net/en/connector-net-connections.html

Troubleshooting .Net Connector

https://dev.mysql.com/doc/connector-net/en/connector-net-connections-errors.html

MySQL ODBC Connector

https://dev.mysql.com/doc/refman/8.0/en/connector-odbc-info.html

Manage MySQL Query Load with Kafka

https://stackshare.io/pinterest/using-kafka-to-throttle-qps-on-mysql-shards-in-bulk-write-apis

mysql-kafka-load-balancer-stackshare.io-Using Kafka to Throttle QPS on MySQL Shards in Bulk Write APIs.pdf

To support high QPS from internal clients at the same time, all requests from them should be stored temporarily in the platform and processed at a controlled speed. This is where Kafka makes a good fit for these purposes.

1. Kafka can handle very high qps write and read.
2. Kafka is a reliable distributed message storage system to buffer batch requests so that requests are processed at a controlled rate.
3. Kafka can leverage the re-balancing of load and manage consumers automatically.
4. Each partition is assigned to one consumer exclusively (in the same consumer group) and the consumer can process requests with good rate-limiting.
5. Requests in all partitions are processed by different consumer processors simultaneously so that throughput is very high.

Image Added

MariaDB compare to MySQL DB - 2020 

https://www.eversql.com/mariadb-vs-mysql/#:~:text=MariaDB%20Server%20is%20licensed%20as,not%20the%20case%20with%20MySQL.

MariaDB vs MySQL Key Differences Comparing MySQL 80 with MariaDB 105.pdf gdrv

The goal of this article is to evaluate and highlight the main similarities and differences between the MySQL Server Database and the MariaDB Server Database. We’ll look into performance aspects, security, main features, and list all aspects which need to be considered before choosing the right database for your requirements.

For MySQL, we can see names such as Facebook, Github, YouTube, Twitter, PayPal, Nokia, Spotify, Netflix and more.

For MariaDB, we can see names such as Redhat, DBS, Suse, Ubuntu, 1&1, Ingenico, Gaming Innovation Group, BlaBla Cars and more.

Oracle DB compare to MySQL DB 

https://docs.oracle.com/cd/E12151_01/doc.150/e12155/oracle_mysql_compared.htm#CHDIIBJH

Oracle and MySQL Compared.pdf gdrv

MySQL Procedures Tutorial - W3C

https://www.w3resource.com/mysql/mysql-procedure.php

MySQL External Language Procedures - OReilly

https://docs.huihoo.com/mysql/2008/A-Tour-of-External-Language-Stored-Procedures-for-MySQL.pdf

mysql-external-language-stored-procs-2008.pdf

MySQL External Procedure RFC

https://dev.mysql.com/worklog/task/?id=820

Potential Value Opportunities

Potential Challenges

Manual uninstall of MySQL - MACOS

https://community.jaspersoft.com/wiki/uninstall-mysql-mac-os-x

Auto-scale MySQL using Write Master - Read Slave replicas

...

The conditional logic of the CASE expression works hand in hand with the aggregate function to only get a total of the prod_name that you want in each column. Since we have 3 products, then you’d write 3 sum(case... expressions for each column. Here is a demo on SQL Fiddle. This query will give a result of:

MySQL procedure example

...

MySQL procedure example

Create a procedure with a parm and a result set

-- prepared stmt example 
set @aproduct = 'Pants';
prepare stmt1 from '
select p.* from products as p
where p.prod_name like ?';
execute stmt1 using @aproduct;

deallocate prepare stmt1;

-- procedure example - normally use create definer for security by roles
delimiter ;;
CREATE PROCEDURE `zListProducts`(IN increment INT, OUT acounter INT)
DETERMINISTIC
-- SQL SECURITY INVOKER
BEGIN
DECLARE counter INT DEFAULT 0;
WHILE counter < 10 DO
SET counter = counter + increment;
END WHILE;
SET acounter = counter;
END;;
delimiter ;

Call a procedure with a parm and a result set

...

Call a procedure with a parm and a result set

...

SET @increment = 2;
SET @acounter = 22;
select @increment;

CALL `zListProducts`(@increment, @acounter);

select @acounter;

MySQL dynamic statements using SQL varialbles @myvar etc

Temporal DB Tables in MySQL

https://en.wikipedia.org/wiki/Temporal_database

A temporal database stores data relating to time instances. It offers temporal data types and stores information relating to past, present and future time. Temporal databases could be uni-temporal, bi-temporal or tri-temporal.

More specifically the temporal aspects usually include valid time, transaction time or decision time.

• Valid time is the time period during which a fact is true in the real world.
• Transaction time is the time at which a fact was recorded in the database.
• Decision time is the time at which the decision was made about the fact.

MariaDB of a temporal table with application and system time

If your application needs to utilize both system-versioning and application-time periods, you can combine them in bitemporal tables. The earlier instructions for maintaining each type of temporal data applies.

CREATE TABLE coupons_new (
   id INT UNSIGNED,
   name VARCHAR(255),
   date_start DATE,
   date_end DATE,
   PERIOD FOR valid_period(date_start, date_end)
) WITH SYSTEM VERSIONING;

This creates a table with two time periods, the arbitrary one based on the two DATE columns for application-time periods, and the internal SYSTEM_TIME for system-versioning.

You may find this useful in applications where you need an audit trail for data change while still needing to perform operations dependent on application-time periods.

Step-by-step guide for Example

...