MySQL op Viggin-mysql dual Master (master-master) + half sync (semisync Repl

MySQL--MariaDB--Percona-server

Mysql:oracle,
Commutity: Community Edition 5.5 5.6 5.7 8.0
MariaDB:
5.5 10.x
Percona:
Percona-server
InnoDB-XtraDB
Xtrabackup
Percona-tools:

Storage Engine:
Engine: Also known as table type, table-level concept, it is not recommended to use a different engine on a table in the same library;
CREATE TABLE ... ENGINE storage_engine_name ...
SHOW TABLE STATUS

                Common storage Engine: SHOW ENGINES;                MyISAM, Aria, InnoDB, Mrg_myisam, CSV, Blackhole,... Innodb:innobase percona-xtradb,supports transactions, Row-level locking, and foreign key                                            s data is stored in the table space: (1) data and indexes for all tables of InnoDB in all databases are stored in the same table space;                            Tablespace file: DataDir defined directory, file Ibdata1,ibdata2 ...  (2) Innodb_file_per_table=on, which means that each table uses a separate tablespace file; Data files (data and indexes, stored in database directories) for each table are stored in a dedicated tablespace file and stored in the database directory: TABLENAME.IBD table structure definition: In the database directory, TABLENAME.FRM transaction type Storage engine, suitable for scenarios where things are demanding, but more suitable for handling large amounts of short-term transactions; MVCC (Mutil Version Concurrency Control) supports high concurrency, supports four isolation levels, and the default level is Repetable-read (  Can be reread-phantom reading);                     Gap lock to prevent Phantom reads: (MVCC multiple versioning is the solution to the Phantom reading problem) using a clustered index (primary key index); support "adaptive hash Index"; lock granularity: Row-level lock, GAP Lock Summary: Data storage: Table space; Concurrency: MVCC, Gap Lock, row-level lock index: Clustered index, secondary index; performance: read-ahead operation, memory data buffering, memory index cache, Adaptive hash index, insert operationBuffer; Backup: Support hot-standby; SHOW ENGINE INNODB STATUS; 

Myisam:-> Aria

        Support for full-text indexing (fulltext index), compression, spatial functions (GIS), transaction lock granularity not supported: table-level lock failure cannot guarantee table security Recovery applicable scenario: read-only or read-less scenarios, smaller tables ( Ensure short recovery time after a crash); files: Three files per table, stored in the database directory tbl_name.frm: tabular definition; tbl_name. MYD: Data files; tbl_name.            MYI: index file; attributes: Locking and Concurrency: table-level lock; FIX: Manual or automatic repair, but may lose data; index: nonclustered index; delay index update;                        Table compression, display lock use: 1) lock TABLES lock TABLES tb1_name read|write ...            UNLOCK TABLES;                  2) FLUSH TABLES; FLUSH TABLES Tb1_name,....                        [with READ LOCK];            UNLOCK TABLES; 3) SELECT cluase [for UPDATE | LOCK in SHARE MODE] Transaction: Transaction: A set of atomic SQL queries, or a separate unit of work consisting of one or more SQL statements: Transaction Day LOG: Innodb_log_files_in_group Innod                                   B_log_group_home_dir          Innodb_log_file_size innodb_mirrored_log_groups acid Measurement Test: A:automicity, atomicity, all operations in the entire transaction are either successfully executed or rolled back after failure; c:consistency, consistency, data                            The library should always move from one consistent state to another; I:isolation, isolation, whether an action made by a firm can be visible to other transactions before committing, and in the purpose of guaranteeing concurrent operations, there are multiple levels of isolation;            D:durability, persistence; Once a transaction is submitted, its changes are permanently saved; automatic commit; single statement transaction mysql > SELECT @ @autocommit; MySQL > SET @ @session. autocommit=0;                        Close the current session auto-Commit manual control transaction: Startup: Start TRANSACTION commit: Commit rollback: ROLLBACK transaction support savepoints:                        savepoint identifier ROLLBACK [work] to [savepoint] Identifier RELEASE savepoint Identifier TRANSACTION ISOLATION level: Full understanding read-uncommitied: READ UNCOMMITTED--dirty read problem Read-co Mmitied: Read COMMITTED--non-repeatable read; Repeatable-read: repeatability--and magic read; default Level SerializabLE: serialization; MySQL > SELECT @ @session. tx_isolation;    MySQL > SHOW ENGINE innodb STATUS;                                    MySQL User and Rights Management user account: [email protected] User: account name;                                    Host: Which client hosts the account can request to create a connection thread;%: Any character of any length;                        _: any single character; Skip_name_resolve=on rename: RENAME USER         RENAME USER Old_user to New_user [, Old_user to New_user] ...                        Delete users: Drop user ' username ' @ ' host ' [, ' username1 ' @ ' host '] ...            FLUSH privileges;                        Modify user password: 1) SET PASSWORD [for ' username ' @ ' host '] =password (' string ');                        2) UPDATE mysql.user SET password=password (' string ') WHERE user= ' username ' and host= ' host '; 3) mysqladmin-uusername-hhost-p password ' New_paSS ' 4) FLUSH privileges; 

To forget the Administrator password solution:
1) When starting the mysqld process, use the--skip-grant-tables and--skip-networking options
Centos 7:mariadb.service
Centos 6:/etc/init.d/mysqld
2) Modify the administrator password through the update command;
3) Start the mysqld process in the normal way;

查看授权： SHOW  GRANTS;                SHOW  GRANTS  [FOR  ‘username‘@‘host‘ ];取消授权： REVOKE       REVOKE   priv_type  on   priv_level   FROM ‘user‘@‘host‘ ...             REVOKE  ALL PRIVILEGES ,GRNAT OPTION  FROM user..

Query cache-Related server variables:
Query_cache_limit: Maximum query result that can be cached; (maximum of single statement result set size)
Statements with a larger result set: Display using Sql_no_cache to avoid first caching and then moving out;
Query_cache_min_res_unit: The minimum allocation unit of the memory cache block; cache too small query result assembly wasted memory space;
Smaller values reduce space waste, but can result in more frequent memory allocations and recycling operations, while larger values can lead to wasted space;
Query_cache_size: The total available size of the query cache space, in bytes, which must be an integer multiple of 1024;
Query_cache_type: The caching function is enabled or not;
On: Enabled; OFF: Disabled
DEMAND: Cache On Demand, caching only query results with Sql_cache in SELECT statements;
Query_cache_wlock_invalidate: If a table is locked by another connection, the query results can still be returned from the query cache; The default is OFF, which means yes;

State variables:

            mysql>SHOW GLOBAL STATUS LIKE ‘Qcache%‘;        命中率： Qcache_hits/Com_select

MySQL log:

  1) binary logs are used to record statements or changes resulting from changes in data or potential for data changes, or they may be mixed; function: Replay Binlog_format = (statement| row|                     MIXED) STATEMENT: statement; row: line; MIXED: mixed; View a list of binary log files: SHOW master|        BINARY LOGS;        View binary log files currently in use: Show MASTER STATUS; Viewing events in binary log files: Show BINLOG events [in ' Log_name '] [from pos][limit [offset,] row_count] server variable : Log_bin=/path/to/bin_log_file | OFF Session.sql_log_bin={on |                        OFF} Controls whether the "write" action statement in a session is recorded in a binary log file; max_binlog_size= SYNC_BINLOG={1|0}: The default is 0, at this time MySQL performance is the best, but also the most dangerous, in the event of a crash, there is memory cache statement information will be lost, 1 is the most secure and slowest, almost the binary memory cache information with the disk in real-time synchronization flush; can be defined n times, Flush cache to disk after each transaction number of transactions Mysqlbinlog: Client program Yyyy-mm-dd HH:MM:SS--start-datetime=--sto P-datetime=-j,--start-position=--stop-position=--user,--host,--password 

Relay LOG: Events that are logged from the server and synchronized from the binary log file of the primary server;

Transaction log: Transactional storage engine InnoDB the log file used to guarantee transaction characteristics
Redo Log
Undo Log

Backup policy:
Xtrabackup:
Full Amount + Difference +binlog
Full Volume + incremental +binlog
Mysqldump
Full Volume +binlog

LVM2-based backup:
Prerequisite: The data file and transaction log are required to be on the same logical volume:
1) Request lock all tables:
MySQL > FLUSH TABLES with READ LOCK;
2) Log binary file event location:
mysql > FLUSH LOGS;
MySQL > SHOW MASTER STATUS;
Mysql-e ' SHOW MASTER STATUS; ' >>/path/to/some_pos_file

             3)创建快照卷                 lvcreate  -s  -L 100M  -p r  -n SNAP-NAME  /dev/VG-name/lv-name                4) 释放锁                    mysql > UNLOCK  TABLES;                5) 挂载快照卷，并执行备份，备份完成后删除快照卷；                6) 周期性备份二进制日志；

Xtrabackup:

        备份 --> 应用日志  --> 还原                        应用日志： --apply-log                        还原： --copy-back        完全备份： 完全+binlog(总结):                     备份： 完全+增量+binlog...                     准备：                                        innobackupex   --apply-log  --redo-only  BASEDIR                                        innobackupex   --apply-log   --redo-only  BASEDIR  --incremental-dir=INCREMENTTAL-DIR                        恢复：                                        innobackupex    --copy-back   BASEDIR         备份单库：                         --databases;    总结：                    mysqldump+binlog                    lvm2+cp/tar+binlog                    xtrabackup(innodb)+binlog

Experiment "MySQL master-slave replication Architecture and advanced":

MySQL dual master (master-master) + semi-sync (semisync Replication)

First, the environment
Host name host IP

Mysqla 172.18.252.221

MYSQB 172.18.252.222

Operating system: CentOS 6.5 2.6.32-431.el6.x86_64

MySQL version mysql-community-server-5.7.5-0.6.m15.el6.x86_64

Second, the structure

The 1.mysqlA and MYSQLB are primary and standby, i.e. the dual master architecture Master-master

MySQL op Viggin-mysql dual Master (master-master) + half sync (semisync Repl