MySQL运维进阶-MySQL双主(master-master)+半同步(Semisync Repl

why

　　MySQL --> MariaDB --> Percona-Server
　　MySQL: oracle ,
　　commutity : 社区版  5.5    5.6   5.7   8.0
　　MariaDB:
　　5.5    10.x
　　Percona:
　　Percona-Server
　　InnoDB -->  XtraDB
　　Xtrabackup
　　percona-tools:
　　存储引擎：
　　引擎：也称为表类型，表级别概念，不建议在同一个库中的表上使用不同的ENGINE；
　　CREATE TABLE ...  ENGINE STORAGE_ENGINE_NAME ...
　　SHOW  TABLE  STATUS
　　

                常见的存储引擎： SHOW ENGINES;　　MyISAM,  Aria, InnoDB,  MRG_MYISAM, CSV, BLACKHOLE,...
　　

　　InnoDB : InnoBase
　　Percona-XtraDB,Supports  transactions , row-level  locking,  and foreign  keys
　　

　　数据存储于“表空间” 中：
　　（1）所有数据库中的所有类型为InnoDB的表的数据和索引存储于同一个表空间中；
　　表空间文件：datadir定义的目录中，文件 ibdata1,ibdata2...
　　(2)  innodb_file_per_table=ON,意味着每表使用单独的表空间文件;  每表的数据文件（数据和索引，存储于数据库目录）存储于自己专用的表空间文件中，并存储于数据库目录下：tablename.ibd
　　表结构的定义：在数据库目录，tablename.frm
　　

　　事务型存储引擎，适合对事物要求较高的场景中；但较适用于处理大量短期事务；
　　基于MVCC（Mutil Version Concurrency Control）支持高并发；支持四个隔离级别，默认级别为 REPETABLE-READ(可重读-幻读);  间隙锁以防止幻读：（MVCC多版本控制就是解决了幻读问题）
　　使用聚集索引（主键索引）；支持“自适应Hash索引”；
　　锁粒度： 行级锁，间隙锁
　　

　　总结：
　　数据存储：表空间；
　　并发：MVCC，间隙锁，行级锁
　　索引：聚集索引、辅助索引；
　　性能：预读操作、内存数据缓冲、内存索引缓存、自适应Hash索引、插入操作缓存区；
　　备份：支持热备；
　　SHOW   ENGINE  INNODB  STATUS；
　　

　　MyISAM：-> Aria
　　

        支持全文索引（FULLTEXT index）、压缩、空间函数（GIS）；　　不支持事务
　　锁粒度：表级锁
　　崩溃后无法保证表安全恢复
　　

　　适用场景：只读或读多写少的场景、较小的表（以保证崩溃后恢复的时间较短）；
　　

　　文件：每个表有三个文件，存储于数据库目录中
　　tbl_name.frm：表格式定义；
　　tbl_name.MYD：数据文件；
　　tbl_name.MYI：索引文件；
　　

　　特性：
　　加锁和并发：表级锁；
　　修复：手动或自动修复、但可能会丢失数据；
　　索引：非聚集索引；
　　延迟索引更新；
　　表压缩；
　　

　　
显示锁的使用：
　　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]
　　

　　
事务：
　　事务：一组原子性的SQL查询、或者是一个或多个SQL语句组成的独立工作
　　单元：
　　事务日志：
　　innodb_log_files_in_group
　　innodb_log_group_home_dir
　　innodb_log_file_size
　　innodb_mirrored_log_groups
　　ACID测试：
　　A：AUTOMICITY，原子性，整个事务中的所有操作要么全部成功执行，要么失败后回滚；
　　C：CONSISTENCY，一致性，数据库总是应该从一个一致性状态转为另一个一致性状态；
　　I：ISOLATION ,隔离性， 一个事务所做出的操作在提交之前，是否能为其他事务可见；处于保证并发操作之目的，隔离有多种级别；
　　D：DURABILITY,  持久性； 事务一旦提交，其所做出的修改会永久保存；
　　

　　
自动提交；单语句事务
　　mysql > SELECT  @@autocommit;
　　mysql > SET @@session.autocommit=0; 关闭当前会话自动提交
　　

　　
手动控制事务：
　　启动：START  TRANSACTION
　　提交：COMMIT
　　回滚：ROLLBACK
　　

　　事务支持savepoints:

　　SAVEPOINT >
　　ROLLBACK  [WORK]  TO  [SAVEPOINT] >　　RELEASE  SAVEPOINT >　　

　　
事务隔离级别：完全理解
　　READ-UNCOMMITIED :读未提交 --> 脏读问题
　　READ-COMMITIED: 读提交 -->不可重复读；
　　REPEATABLE-READ : 可重复度 --> 幻读问题；  默认级别
　　SERIALIZABLE :串行化；
　　

　　mysql > SELECT @@session.tx_isolation;
　　mysql > SHOW ENGINE  innodb  STATUS;
　　

　　MySQL用户和权限管理
　　用户账号： user@host
　　user  :  账户名称；
　　host :   此账户可通过哪些客户端主机请求创建连接线程；
　　%    ：  任意长度的任意字符；
　　_       :   任意单个字符；
　　

　　skip_name_resolve=ON
　　

　　重命名：RENAME   USER
　　RENAME  USER  old_user  TO new_user [,old_user TO new_user] ...
　　

　　删除用户：
　　DROP  USER  'username'@'host'  [,'username1'@'host']...
　　FLUSH  PRIVILEGES;
　　

　　修改用户密码：
　　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;
　　

　　忘记管理员密码的解决办法：
　　1） 启动mysqld进程时，使用 --skip-grant-tables和--skip-networking选项
　　Centos 7 : mariadb.service
　　Centos 6 : /etc/init.d/mysqld
　　2)  通过UPDATE命令修改管理员密码；
　　3)  以正常方式启动mysqld进程；
　　

查看授权： 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_limit : 能够缓存的最大查询结果；（单语句结果集大小上限）
　　有着较大结果集的语句：显示使用SQL_NO_CACHE，以避免先缓存再移出；
　　query_cache_min_res_unit :内存缓存块的最小分配单位；缓存过小的查询结果集会浪费内存空间；
　　较小的值会减少空间浪费，但是会导致更频繁的内存分配以及回收操作； 较大值的会带来空间浪费；
　　query_cache_size  :  查询缓存空间的总共可用的大小；单位是字节, 必须是1024的整数倍；
　　query_cache_type: 缓存功能启用与否；
　　ON:启用；    OFF  ：禁用
　　DEMAND:  按需缓存，仅缓存SELECT语句中带SQL_CACHE的查询结果；
　　query_cache_wlock_invalidate  :  如果某表被其他连接锁定，是否仍然可以从查询缓存中返回查询结果；默认为 OFF，表示可以；ON则表示不可以；
　　状态变量：
　　

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

　　MySQL日志：
　　

  1）二进制日志　　用于记录引起数据改变或存在引起数据改变的潜在可能性的语句或改变后的结果，也可能是二者混合；
　　功用：重放
　　

　　binlog_format=(STATEMENT|ROW|MIXED)
　　STATEMENT: 语句；
　　ROW : 行；
　　MIXED: 混编；
　　

　　查看二进制日志文件列表：
　　SHOW  MASTER|BINARY  LOGS;
　　

　　查看当前正在使用的二进制日志文件：
　　SHOW  MASTER  STATUS;
　　查看二进制日志文件中的事件：
　　SHOW BINLOG  EVENTS  [IN ‘log_name’] [FROM pos][LIMIT [offset,] row_count]
　　

　　服务器变量：
　　log_bin=/PATH/TO/BIN_LOG_FILE |OFF
　　session.sql_log_bin={ON | OFF}
　　控制某会话中的“写‘’操作语句是否会被记录到二进制日志文件中；
　　max_binlog_size=
　　sync_binlog={1|0}  ：默认是0，此时mysql性能最好，但是也是最危险的，一旦发生崩溃，存在内存缓存中的语句信息将丢失，1是最安全的也是最慢的，几乎将二进制内存缓存信息与磁盘实时同步刷新；  可以定义N次，每执行多少次事务后刷新缓存到磁盘中
　　

　　
mysqlbinlog: 客户端程序
　　YYYY-MM-DD   hh:mm:ss
　　

　　--start-datetime=
　　--stop-datetime=
　　

　　-j,  --start-position=
　　--stop-position=
　　

　　--user, --host, --password
　　

　　中继日志：从服务器上记录下来从主服务器的二进制日志文件同步过来的事件；
　　事务日志：事务型存储引擎innodb用于保证事务特性的日志文件
　　redo     log
　　undo     log
　　备份策略：
　　xtrabackup:
　　全量+差异+binlog
　　全量+增量+binlog
　　mysqldump:
　　全量+binlog
　　基于lvm2的备份：
　　前提：要求数据文件和事务日志位于同一个逻辑卷：
　　1）请求锁定所有表：
　　mysql > FLUSH   TABLES  WITH  READ  LOCK;
　　2)  记录二进制文件事件位置：
　　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
　　

　　实验【mysql主从复制架构与进阶】：
　　MySQL双主(master-master)+半同步(Semisync Replication)
　　一、环境
　　主机名         主机IP
　　mysqlA   172.18.252.221
　　mysqB    172.18.252.222
　　操作系统： CentOS 6.5     2.6.32-431.el6.x86_64
　　MySQL版本     mysql-community-server-5.7.5-0.6.m15.el6.x86_64
　　二、架构
　　1.mysqlA和mysqlB互为主备，即双主架构Master-Master.
　　2.使用semisync半同步机制，保证双主数据一致。
　　3.只有一个Master承担写操作，另一个备用，可承担读操作，配合应用实现读写分离（proxysql）
　　4.双主加上keepalived可搭建高可用MySQL集群，实现单点故障切换。
　　三、准备工作
　　1）安装MySQL5.7
　　使用yum安装，MySQL改善了半同步复制，降低了主从数据不一致的风险。
　　2)修改MySQL配置文件
　　启动MySQL之前先修改/etc/my.cnf，增加下面的配置。
　　mysqlA的配置文件：
　　server-id =1
　　log-bin=mysql-bin #打开二进制日志功能，作为主库时必须设置
　　log-slave-updates  #作为从库时，数据库的修改也会写到bin-log里
　　binlog-ignore-db = mysql
　　binlog-ignore-db = information_schema
　　binlog-ignore-db =performance_schema
　　replicate-wild-ignore-table = mysql.%
　　replicate-wild-ignore-table = information_schema.%
　　replicate-wild-ignore-table = performance_schema.%
　　expire_logs_days=5  #表示自动删除5天以前的binlog,可选
　　mysqlB的配置文件：
　　

            server-id =2　　log-bin=mysql-bin #打开二进制日志功能，作为主库时必须设置
　　log-slave-updates  #作为从库时，数据库的修改也会写到bin-log里
　　binlog-ignore-db = mysql
　　binlog-ignore-db = information_schema
　　binlog-ignore-db =performance_schema
　　replicate-wild-ignore-table = mysql.%
　　replicate-wild-ignore-table = information_schema.%
　　replicate-wild-ignore-table = performance_schema.%
　　expire_logs_days=5  #表示自动删除5天以前的binlog,可选
　　

　　3）在myslqA和mysqB上创建专门用于replicateion的账户
　　

        mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'172.18.252.%' >mysql > FLUSH PRIVILEGES;
　　

　　四、Master-Master配置
　　mysqlA和mysqlB都是刚安装好的、干净的数据库，可根据下面的步骤搭建Master-Master.
　　1)配置mysqlA为主库
　　在mysqlA上配置：
　　

                reset master;(清空master的binlog，必须慎用，可选)　　flush  tables wiht read  lock;
　　show  master  status;
　　

　　2)配置mysqlB为mysqlA的从库
　　在mysqlB上配置：
　　stop   slave;
　　CHANGE  Master  TO MASTER_HOST='172.18.252.221', MASTER_USER='repl_user',  MASTER_PASSWORD='repl',  MASTER_LOG_FILE='mysq-bin.000001',  MASTER_LOG_POS=120;
　　start  slave;
　　show slave  status\G
　　查看Slave_IO_Running和Slave_SQL_Running的状态，如果都为Yes,从库配置成功。
　　3）将mysqlB设为主库
　　在mysqlB上设置：
　　reset  master;  (慎用，生产不用)
　　flush  tables with read lock;
　　show master  status;
　　4)将mysqlA设为mysqlB的从库：
　　在mysqlA配置：
　　unlock   tables;  #先解锁，才能执行后续的写操作
　　stop  slave;
　　CHANGE  MASTER TO MASTER_HOST='172.18.252.222', MASTER_USER='repl_user', MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS='120';
　　start  slave;
　　show  slave   status\G;
　　查看Slave_IO_RUNNING和Slave_SQL_RUNNIG都为Yes则slave配置成功，最后在mysqlB上解锁   unlock  tables;
　　至此，双主已经配置完成，下面配置半同步。
　　

            【此处省略mysql基于keepalived实现高可用，参考之前的博客有详细配置实现】　　

　　五、Semisync半同步配置
　　1)加载semisync_master和semisync_slave插件
　　mysqlA和mysqlB上执行：

　　INSTALL  PLUGIN  rpl_semi_sync_master  SONAME 'semisync_master.so'
　　INSTALL  PLUGIN  rpl_semi_sync_slave  SONAME  'semisync_slave.so';
　　show  variables like  '%semi%';

　　rpl_semi_sync_master_timeout=10000
　　表示主库在某次事务中，如果等待时间超过10秒，则降级为普通模式，不再等待备用库。如果主库再次检查到备用库恢复了，则会自动再次回到semisync模式(注意：半同步是主库通过IO传送二进制日志数据到从库，得到是从库的接收响应即可而不关心从库是否完成写操作等)
　　rpl_semi_sync_master_wait_point=AFTER_SYNC
　　这个参数是MySQL5.7新增的，AFTER_SYNC工作流程：
　　1.客户端提交一个事务，master将事务写入binlog缓存并刷新到磁盘，发送到slave ,  master等待slave反馈。
　　2.slave接收master的binlog，写到本地的relaylog里。发送确认信息给master.
　　3.当接收到slave反馈，master提交事务并返回结果给客户端。这样就保证了主从数据一致。
　　2）开启semisync  master和semisync  slave
　　mysqlA和mysqlB上执行：

　　SET GLOBAL rpl_semi_sync_master_enabled=1;
　　SET GLOBAL rpl_semi_sync_master_enabled=1;
　　并修改my.cnf, 添加下面两行：

　　

                    rpl_semi_sync_master_enabled=1　　rpl_semi_sync_slave_enabled=1
　　

　　show  variable|status  like   '%semi%';
　　此时我们看到的是semi_sync_master_status是ON，而semi_sync_slave_status是OFF，下面我们启动它：

　　3）使半同步机制生效
　　mysqlA和mysqlB上执行：
　　stop  slave ;  start   slave;
　　show  status  like   '%semi%';
　　OK,我们看到semi_sync_slave_status是ON了，已经启动