Others

Facts

MySQL has hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact column limit depends on several factors: The maximum row size for a table constrains the number (and possibly size) of columns because the total length of all columns cannot exceed this size

CREATE SCHEMA is a synonym for CREATE DATABASE as of MySQL 5.0.2

https://dev.mysql.com/doc/refman/5.7/en/innodb-limits.html

A table can contain a maximum of 64 secondary indexes

MySQL Unique Index with NULL values

mysql-unique-index-with-nulls

Dealing with MySQL nulls and unique constraint

mysql client

brew install mysql-client

echo 'export PATH="/usr/local/opt/mysql-client/bin:$PATH"' >> ~/.bash_profile

source ~/.bash_profile
apt-get install -y default-mysql-client
mysql -u root -p kollectai -h localhost

mysql -h host -D database -u developer -p password

mysql -u developer -p password -h host -D database

mysql --user=user --host=host --database=database --password=password -A

show databases;

use mysql;

show tables;

select * from mysql.user;

GRANT ALL PRIVILEGES ON *.* TO 'root';

cobar

Cobar is a proxy for sharding databases and tables, compatible with MySQL protocol and MySQL SQL grama, underlying storage only support MySQL for support foreground business more simple, stable, efficient and safety.

Sharding: You can add new MySQL instance as your business grows.
High availability: Both Cobar server and underlying MySQL is clustered, business will not suffer with single node fail.
Compatible with MySQL protocol: Use Cobar as MySQL. You can replace MySQL with Cobar to power your application.

https://github.com/alibaba/cobar

InnoDB

InnoDB is a storage engine for the database management system MySQL. Since the release of MySQL 5.5.5 in 2010, it replaced MyISAM as MySQL's default table type. It provides the standard ACID-compliant transaction features, along with foreign key support (Declarative Referential Integrity). It is included as standard in most binaries distributed by MySQL AB, the exception being some OEM versions

mariadb

MariaDB is a community-developed, commercially supported fork of the MySQL relational database management system(RDBMS), intended to remain free and open-source software under the GNU General Public License. Development is led by some of the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle Corporation in 2009.

MariaDB intended to maintain high compatibility with MySQL, ensuring a drop-in replacement capability with library binary parity and exact matching with MySQL APIs and commands. However, new features diverge more.It includes new storage engines like Aria, ColumnStore, and MyRocks.

Its lead developer/CTO is Michael "Monty" Widenius, one of the founders of MySQL AB and the founder of Monty Program AB. On 16 January 2008, MySQL AB announced that it had agreed to be acquired by Sun Microsystems for approximately $1 billion. The acquisition completed on 26 February 2008. Sun was then bought the following year by Oracle Corporation. MariaDB is named after Monty's younger daughter, Maria. (MySQL is named after his other daughter, My.)

MySQL 5 vs MySQL 8

https://mysqlserverteam.com/whats-new-in-mysql-8-0-generally-available

Upgrade Checker - https://mysqlserverteam.com/upgrading-to-mysql-8-0-here-is-what-you-need-to-know

MySQL 8.0 brought enormous changes and modifications that were pushed by the Oracle MySQL Team.

Physical files have been changed. For instance, .frm,.TRG, .TRN, and.par no longer exist.
Tons of new features have been added such as CTE(Common Table Expressions), Window Functions, Invisible Indexes, regexp (or Regular Expression)--the latter has been changed and now provides full Unicode support and is multibyte safe.
Data dictionary has also changed. It's now incorporated with a transactional data dictionary that stores information about database objects. Unlike previous versions, dictionary data was stored in metadata files and non-transactional tables.
Security has been improved with the new addition of caching_sha2_password which is now the default authentication replacing mysql_native_password and offers more flexibility but tightened security which must use either a secure connection or an unencrypted connection that supports password exchange using an RSA key pair.
The benchmark results reveals that there has been an impressive improvement, not only on managing read workloads, but also on a high read/write workload comparing to MySQL 5.7.
https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts
Resource Groups
- https://dev.mysql.com/doc/refman/5.7/en/user-resources.html
- https://dzone.com/articles/mysql-8-load-fine-tuning-with-resource-groups

Important

Removed Query Cache
Dual Passwords

Debugging / Errors

MySQL "Got an error reading communication packet"

Resolve the communication packets error in Amazon RDS for MySQL | AWS re:Post

ProxySQL

ProxySQL is a high performance, high availability, protocol aware proxy for MySQL and forks (like Percona Server and MariaDB). All the while getting the unlimited freedom that comes with a GPL license.

Its development is driven by the lack of open source proxies that provide high performance.

ProxySQL is a high-performance SQL proxy. ProxySQL runs as a daemon watched by a monitoring process. The process monitors the daemon and restarts it in case of a crash to minimize downtime.

The daemon accepts incoming traffic from MySQL clients and forwards it to backend MySQL servers.

The proxy is designed to run continuously without needing to be restarted. Most configuration can be done at runtime using queries similar to SQL statements. These include runtime parameters, server grouping, and traffic-related settings.

GitHub - sysown/proxysql: High-performance MySQL proxy with a GPL license.

ProxySQL - A High Performance Open Source MySQL Proxy

Load balancing with ProxySQL - Percona XtraDB Cluster