• Make sure MySQL 8.0 is already installed.

GTID restore

TODO: This needs to be tested. On a Slave in normal MySQL replication (or in a cluster), the GTID may be messed up. You may have to reset it. On the slave, the gitd_executed must be greater than gtid_purged on the master. If that is the case, reset the slave to to its gtid_excuted and gtid_purged of the thread you want.

• On the master, execute "SHOW VARIABLES WHERE Variable_name in ('gtid_executed', 'gtid_purged');"
• On the slave, same thing "SHOW VARIABLES WHERE Variable_name in ('gtid_executed', 'gtid_purged');"
• Now you can do one of two things, erase replication on slave and reset it to 0 or set it the the point it left off. First execute "reset master" to erase the gtid positions. This is different than "reset slave".
  • NOTE: Test this first option. Reset to 0 and it should figure it out, as
    SET GLOBAL gtid_executed='0';
    SET GLOBAL gtid_purged = '< set to the previous value on slave';
  • or reset the 'gtid_executed', 'gtid_purged' as
    SET GLOBAL gtid_purged="previous value or value to set as"
    SET GLOBAL gtid_purged="previous value"
• Then do "start slave".

General Notes 1

• 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.

    • https://www.mysqltutorial.org/mysql-generated-columns/#:~:text=MySQL%20provides%20two%20types%20of,above%20is%20a%20virtual%20column.

    • Stored means values are stored on insert. Virtual means on the fly.

      • Stored columns take up diskspace.

  • https://www.percona.com/blog/mysql-8-0-functional-indexes/

  • 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.

      • http://mysqlblog.fivefarmers.com/2012/08/16/understanding-mysql_config_editors-security-aspects/

    • 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

    • Data Dictionary Tables

• Grant System Tables

  • metadata about database objects. 

• Object Information System Tables

  • components, loadable functions, and server-side plugins

• Log System Tables

  • general_log and slow_log unless defined.

• Server-Side Help System Tables

• Time Zone System Tables

• Replication System Tables

• Optimizer System Tables

• Miscellaneous System Tables