Table of Contents |
---|
Key Points
References
Table of Contents |
---|
Key Points
References
Reference_description_with_linked_URLs_______________________ | Notes______________________________________________________________ |
---|---|
https://www.mysql.com/ | |
https://www.mysql.com/products/enterprise/mysql-datasheet.en.pdf | |
https://dev.mysql.com/doc/ | |
https://dev.mysql.com/downloads/ | |
https://dev.mysql.com/ | |
https://dev.mysql.com/doc/refman/8.0/en/upgrade-paths.html | MySQL upgrade paths from GA release A to release B on Windows, Linux |
m MySQL Workbench | m MySQL Workbench |
https://dev.mysql.com/downloads/ | MySQL community edition |
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 | MySQL router for REST API app access |
https://www.mysql.com/products/enterprise/document_store.html | MySQL NoSQL documents |
https://www.w3resource.com/slides/index.php | MySQL overview concepts |
https://www.edureka.co/blog/mysql-tutorial/ | MySQL Tutorial - SQL but little on operations - very good, quick |
https://www3.ntu.edu.sg/home/ehchua/programming/sql/MySQL_Beginner.html | MySQL Tutorial - basic examples - ntu.edu |
https://serverfault.com/questions/361691/ways-to-auto-scale- mysql-servers | Scale MySQL w Write Master, Distribution Master, 5 Read Slaves |
https://www.oreilly.com/library/view/mysql-reference-manual/0596002653/ch04s06.html | MySQL language localization support - O'Reilly |
https://dev.mysql.com/doc/mysql-g11n-excerpt/8.0/en/charset.html | MySQL character sets |
https://www.tarynpivots.com/post/how-to-rotate-rows-into- | Case statement creates a Pivot Table can we generate a generic pivot function using a query result table as input |
mysql-upsert-model-chartio.com-How to INSERT If Row Does Not Exist UPSERT in MySQL.pdf | Upsert model in mysql |
Procedure and generation examples | |
https://drive.google.com/open?id=0BxqKQGV-b4WQMlBYa041VFdQdTg C:\Users\Jim Mason\Google Drive\_ptp\work\grails\_gdev\gtest1a.groovy | Groovy scripts for MySQL |
MySQL Connectors | |
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-overview.html | MySQL Connector/J Guide MySQL Connector/J X DevAPI Reference |
https://dev.mysql.com/doc/x-devapi-userguide/en/ | XDevAPI User Guide |
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 MySQL .Net Connector X DevAPI Reference |
https://dev.mysql.com/doc/connector-odbc/en/ | MySQL ODBC Connector Guide ( for pbi ? ) |
https://dev.mysql.com/doc/dev/connector-nodejs/8.0/ | MySQL Nodejs Connector Guide |
https://dev.mysql.com/doc/connector-python/en/ | MySQL Python Connector Guide |
MySQL comparisons | |
https://logz.io/blog/relational-database-comparison/ compare-mysql-logz.io-Sqlite vs MySQL vs PostgreSQL A Comparison of compare-mysql-logz-Sqlite vs MySQL vs PostgreSQL A Comparison of Relational Databases.pdf | MySQL Postgresql Sqlite - 2020 |
https://www.geeksforgeeks.org/difference-between-mysql-and-postgresql/ | Postgres = full SQL w external procs MySQL has work arounds for recursion, java procs as table requests |
https://blog.panoply.io/postgresql-vs.-mysql | Postgres vs MySQL many db comparison posts |
https://www.postgresqltutorial.com/postgresql-vs-mysql/ | Postgres vs MySQL |
https://www.guru99.com/postgresql-vs-mysql-difference.html | Postgres vs MySQL - Guru 99 some facts listed are not necessarily current on MySQL now. Skipped the ability in MySQL to address issues my review raises. |
Postgres vs MySQL - Jim comparison I'll give the tutorial credit for factual accuracy - that's good. 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. | |
https://stackshare.io/pinterest/using-kafka-to-throttle-qps-on-mysql- shards-in-bulk-write-apis | Use Kafka to manage qps on MySQL shards |
Performance Solutions | |
Scale MySQL with partitions for Nodejs | |
...
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
...
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
...
- If we don’t face any error, then REPLACE would behave as regular INSERT command.
- If a duplicate record exists, then REPLACE would first delete it and perform the INSERT subsequently.
Let’s run our previous test done in #1 again here and observes its result.
*/
-- Replacing duplicate record REPLACE INTO BLOGPOSTs ( postId, postTitle, postPublished ) VALUES (5, 'Python Tutorial', '2019-08-04'); -- Print all rows
MySQL Pivot table example
mysql-pivot-table-from-case-stmt-x1.pdf
sample data
...
- .
Let’s run our previous test done in #1 again here and observes its result.
*/
-- Replacing duplicate record REPLACE INTO BLOGPOSTs ( postId, postTitle, postPublished ) VALUES (5, 'Python Tutorial', '2019-08-04'); -- Print all rows
MySQL Pivot table example
mysql-pivot-table-from-case-stmt-x1.pdf
sample data
insert into sales (prod_id, rep_id, sale_date, quantity)
values
(1, 1, '2013-05-16', 20),
(1, 1, '2013-06-19', 2),
(2, 1, '2013-07-03', 5),
(3, 1, '2013-08-22', 27),
(3, 2, '2013-06-27', 500),
(3, 2, '2013-01-07', 150),
(1, 2, '2013-05-01', 89),
(2, 2, '2013-02-14', 23),
(1, 13, '2013-0501-1629', 2019),
(13, 13, '2013-03-06-19', 213),
(2, 13, '2013-0704-0318', 51),
(32, 13, '2013-08-2203', 2778),
(32, 23, '2013-0607-2722', 500)69);
join sales to reps and products table
select
r.rep_name,
(3p.prod_name,
2, '2013-01-07', 150),
(1, 2, '2013-05-01', 89),
(2, 2, '2013-02-14', 23),
(1, 3, '2013-01-29', 19),
(3, 3, '2013-03-06', 13),
(2, 3, '2013-04-18', 1),
(2, 3, '2013-08-03', 78),
(2, 3, '2013-07-22', 69);
join sales to reps and products table
select
r.rep_name,
p.prod_name,
s.sale_date,
s.quantity
s.sale_date,
s.quantity
from reps r
inner join sales s
on r.rep_id = s.rep_id
inner join products p
on s.prod_id = p.prod_id
But what if we want to see the reps in separate rows with the total number of products sold in each column. This is where we need to implement the missing PIVOT
function, so we’ll use the aggregate function SUM
with conditional logic instead.
|
|
But what if we want to see the reps in separate rows with the total number of products sold in each column. This is where we need to implement the missing PIVOT
function, so we’ll use the aggregate function SUM
with conditional logic instead.
|
|
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:
REP_NAME | SHOES | PANTS | SHIRT |
---|---|---|---|
Joe | 19 | 148 | 13 |
John | 22 | 5 | 27 |
Sally | 89 | 23 | 650 |
MySQL procedure example
Create a procedure with a parm and a result set
...
Call a procedure with a parm and a result set
...
|
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:
REP_NAME | SHOES | PANTS | SHIRT |
---|---|---|---|
Joe | 19 | 148 | 13 |
John | 22 | 5 | 27 |
Sally | 89 | 23 | 650 |
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
SET @increment = 2;
SET @acounter = 22;
select @increment;
CALL `zListProducts`(@increment, @acounter);
select @acounter;
MySQL dynamic statements using SQL varialbles @myvar etc
MySQL binary change log
you can setup a process to read the binary change log that is used for replication
the process can publish selected changes as needed
How to track database changes in MySQL?
- Start tracking: CALL tracking. START_TRACKING("db","table");
- List tracked tables: CALL tracking. LIST_TRACKING();
- Stop tracking: CALL tracking. STOP_TRACKING("db","table");
Learn MySQL: An overview of MySQL Binary Logs - 2021.
The concept of MySQL binary logs and SQL Server transaction logs are the same. The binary logs contain information about the data changes that occurred on the MySQL instance. The binary logs contain all SQL statements, which update the data (INSERT, UPDATE DELETE) and the DDL statements (Create database object, drop database objects, truncate table) within the database. It contains the time taken to execute a statement that creates and updates the data. The SQL statement that is used to update the data are stored in the EVENT form. The EVENTs describe the global changes that occurred on the MySQL Server during any operations. It helps us to reproduce the change of the global state that had happened on the MySQL Server.
The purpose of the MySQL binary logs is the following:
- The binary logs are used for master-slave replication
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
...