Persistent Optimizer Statistics
optimizer stats are saved across restarts
Execution plans based on transient index statistics improve precision when innodb_stats_persistent_sample_pages is increased.
Virtual columns and functional indexes
For columns, they are virtual or stored.
Stored means values are stored on insert. Virtual means on the fly.
Stored columns take up diskspace.
You can have indexes on virtual or stored columns.
ALTER
TABLE employees -
ADD
COLUMN birth_month tinyint unsigned GENERATED ALWAYS AS
(MONTH(birth_date)) VIRTUAL NOT NULL,
ADD
INDEX (birth_month);
Index takes up space for itself.
Functional-indexes – virtual indexes.
ALTER
TABLE employees -
ADD
INDEX ((MONTH(birth_date)));
Functional take no no space.
Performance
Performance for non-critical data
turn offdoubewrite
trx = 2
disabled logbin
sync_binlog=0, doesn't matter if turned off
increase buff_pool if free pages are low
Innodb_buffer_pool size to 1 GB each???? --- Verify
Tmp space
Most things temporary are created in variable “tmpdir”
innodb uses a temporary tablespace separate
MySQL Enterprise
firewall https://dev.mysql.com/doc/refman/8.0/en/firewall-reference.html
firewall_access_denied : statement rejected
firewall_access_granted : statements accepted
firewall_access_suspicious : statement suspicious in detecting mode
firewall_cached_entries : cached statements
TDE
encryption commands
ALTER TABLE t1 ENCRYPTION = 'Y';
ALTER TABLESPACE mysql ENCRYPTION = 'Y'; For new tables default
encryption occurs on disk
All network traffic is unencrypted.
MySQLbackup
Options
--only-known-file-types : only built-in engines are backed up
.ARM: ARCHIVE table metadata .
ARZ: ARCHIVE table data .
CSM: CSV table metadata
.CSV: CSV table data
.ibd: InnoDB tablespace created using the file-per-table mode .
MRG: Merge storage engine references to other tables .
MYD: MyISAM data .
MYI: MyISAM indexes
--backimage – backup is a single file
--backup-dir – status and metadata
order of backup : data, archive files, redo logs
--optimistic-busy-tables
first phase, tables not chaingare backedup, not the redo and undo logs
normal backup, data, archive, and redo
Audit
audit_log=FORCE_LOG_PERMANENT
prevents it from being unloaded
Replication
slave_parallel_type=DATABASE can result in cross database inconsistency. Use LOGICAL
slave_preserve_commit_order=1 is good. Otherwise, queries can be committed out of order with multiple slave threads.
Threads
io thread requests command from master bad puts it into relay logs
sql executes from relay logs and saves to binary
slaves seconds behind is calculated on last timestamp received from Master, and last timestamp of last query performed in SQL thread.
Proxy : Grant users to masquerade as another user.
When defined as proxy, ignore your permissions, you adopt the other account.
SELECT USER(), CURRENT_USER();
user is the account that is logged in
current_user is the proxy user
Clusterset
API https://dev.mysql.com/doc/dev/mysqlsh-api-javascript/8.0/classmysqlsh_1_1dba_1_1_dba.html
Requirements : https://dev.mysql.com/doc/refman/8.0/en/group-replication-requirements.html
each table has primary keys
server_id for each server
binary log turned on
slave log binaries
row-format
global transactions identifiers turned on
turn off binlog checksum
lower case tables and databases
dropMetadataSchema()
outer to drop all current connections and forbid new connections. This causes a full outage. Cluster must be recreated. All cluster meta purged from reachable servers,
STOP GROUP_REPLICATION; stops replication for a member, but finishes what its downloaded.
Accounts
Which two MySQL Server accounts are locked by default?
Internal accounts
user set as DEFINER for stored procedures
Backups
mysqlbackup with copy-backup option will backup files, when restored will move files to there correct destination. Mysql must be off. copy-back is used on the restore.
MysqlDump
Issues Fluises with read lock
With –single-transction, release log after getting binlog position.
Does not include performance_schema, sys, or information_schema
mysqlpump will not backup performance_schema, information_schema, sys, or ndbinfo unless specified or wildcarded
mysqldump won't dump them
Explain and explain analyze
Explain describes how data is accessed with tables an joins. If indexes are used, no of rows returned, etc.
explain analyze describes what is happening in the query and why. It is a profiling tool.
For more info: https://dev.mysql.com/blog-archive/mysql-explain-analyze/
Server
Client
Locations for passwordless login
$HOME/.my.cnf file -- put username password under [mysql]
$HOME/.mylogin.cnf file -- mysql_config_editor
/etc/my.cnf file -- global, dangerous
Connection options
https://dev.mysql.com/doc/refman/8.0/en/transport-protocols.html
TCP, SOCKET and with Windows, PIPE and MEMORY
For SSL, --required just requires SSL. Verify_ca and verify__identity validates x.509 compliance, basically outside verification.
Locks
S, shared, it can be read
X, exclusive, can't be read
Admin
Dsiable an account
ALTER USER baduser@hostname IDENTIFIED WITH mysql_no_login
ALTER USER 'user_name'@'host' ACCOUNT LOCK;
Or change the password to something you can't guess.
Or rename the account.
To persist variables, use SET PERSIST to set global variables.
innodb_fast_shutdown=0.
Most files are not needed, mysql.ibd and ibdata1 are. Ibtomp1, ib_buffer_pool, ib_logfile0, and undo are not needed Its a complete shutdown with data committed.
To enable by default secure password, default_authentication_plugin=sha256_password
GTID
You fix it
Make sure the executed gtid is AFTER the gtid purged on master. Look at gitd_purged and gtid_executed on both.
First reset slave on slave.
Changed git_executed on slave to 0 or to a value.
Start slave
To convert normal replication to gtid
Restart MySQL (master and slave) with these options enabled:--gtid_mode=ON –log-bin –log-slave-updates –enforce-gtid-consistency
On the slave, alter the MySQL master connection setting with:
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
Security
In the mysql client, set –ssl-mode to REQUIRED
Stored procedure ca prevent sql injection.
Clusterset
The MySQL router initiates by connecting to a node and configuring itself to use all nodes, potentially.
Query
Inside a transaction, if permission is denied, you don't have to abort the transaction. Get the right permissions and try again.
If you have a role defined but you have not activated it, you will not have the roles permissions. You can default to all roles on login or a specific one.
Hash join have to fit in join_buffer_size
Utilities
mysqlbinlog
To view binlog mysqlbinlog [options] log_file
ex: mysqlbinlog --base64-output=DECODE-ROWS --verbose /var/lib/mysql/binlog.000001
To get all binlogs:
mysqlbinlog --base64-output=DECODE-ROWS --verbose /var/lib/mysql/binlog.* > /tmp/all.sql
In various tests, replacing all commands from on database to another is done wrong.
INCORRRECT: mysqlbinlog --raw | sed ‘s/mydb1/mydb2/g’ | mysql
BETTER : mysqlbinlog --base64-output=DECODE-ROWS --verbose /var/lib/mysql/binlog.* | sed “s/^use 'mydb1'/use 'mydb2'/i” > all.sql
Now examine all.sql.
and then : mysqlbinlog –datebase=mydb2 | grep mysql
NOTE test this first. And compare databases on a test server before doing in production.
Mysqlpump
mysqlpump --exclude-databases=% --users
Dumps account information, in form of create user and grants.
Mysqlbinlog
for recovery, use stop-position instead of stop date. You may miss queries with stop date.
Audit
commercial in mysql but free in percona mysql
To install mysql < audit_log_filter_linux_install.sql or do whats in the script.
Roles
When a user has a grant option to grant or revoke roles to other users, the user can grant ONLY the role, not the individual permissions The role has the permissions.
TODO: role with password
You can
drop it
lock it so accounts can't use that role
be granted to accounts
List : SELECT user AS role_name From mysql.user WHERE host = '%' AND NOT LENGTH(authentication_string);
A role can be set to mandatory, which affects all users.
SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com'
Data Dictionary
Holds
table, view, and stored procedure definitions
server configuration rollback
triggers
users, user groups, privs, roles?
Physical partitions, files, backups
size of tables, indexes, and no of rows
Inoodb
innodb_directories --- TODO
use mysql_config_editor to make user/password login automatic It is questionable how secure it is.
What in the innodb tablespace?
Tables
indexes
undo : temporary is global, tablespace in files if one file per table
What is not
doublewrite 8.0.20, in files
redo – 8.0.30., in files, used to be ib_logfile0 and 1
change buffer- memory
double write duffer – 8.0.20 –-double write files
truncate now drops and remakes the table, so one file per table and truncate will free up diskspace.
SDI Serialized Dictionary Information
Meatdata is stored in serialized json form and is stored in tablespaces.
Clusterset
In the even of a group replication failure.
The cluster will shut down to preserve data consistency.
The cluster has built-in high availability and updates group_replication_ip_whitelist to remove the unreachable nodes.
Sys database
data collected by performance schema is summarized here
stored procedures to generate reports from performance schema
TODO: examples on using sys
Performance schema
TODO: show examples of performance schema
Indexes
show indexes
show indexes command
show create table
information_schema.statistics
Importing tables
MySIAM
Copy MYD, MYI, and SDI tables
use IMPORT TABLE command
Innodb
on the destination, create an empty table of same name
FLUSH TABLES t1 FOR EXPORT; on server
Copy ibd and cfg file of table to destination
import table, ALTER TABLE t1 IMPORT TABLESPACE;
Engines
transactional : NBD, InnoDB
non : ARCHIVE, MYISAM, blckhole, memory
blackhole doesn't store anything
MySQL database
metadata about database objects.
Object Information System Tables
components, loadable functions, and server-side plugins
general_log and slow_log unless defined.