MySQL和MariaDB 备份 主从 读写分离

小木木

　　查询缓存：
　　如何判断是否命中：
　　通过查询语句的哈希值判断：哈希值考虑的因素包括
　　查询本身、要查询的数据库、客户端使用协议版本，...
　　查询语句任何字符上的不同，都会导致缓存不能命中；
　　哪此查询可能不会被缓存？
　　查询中包含UDF、存储函数、用户自定义变量、临时表、mysql库中系统表、或者包含列级权限的表、有着不确定值的函数(Now());
　　查询缓存相关的服务器变量：
　　query_cache_min_res_unit: 查询缓存中内存块的最小分配单位；
　　较小值会减少浪费，但会导致更频繁的内存分配操作；
　　较大值会带来浪费，会导致碎片过多；
　　query_cache_limit：能够缓存的最大查询结果；
　　对于有着较大结果的查询语句，建议在SELECT中使用SQL_NO_CACHE
　　query_cache_size：查询缓存总共可用的内存空间；单位是字节，必须是1024的整数倍；
　　query_cache_type:ON, OFF, DEMAND
　　query_cache_wlock_invalidate：如果某表被其它的连接锁定，是否仍然可以从查询缓存中返回结果；默认值为OFF，表示可以在表被其它连接淘宝的场景中继续从缓存返回数据；ON则表示不允许；
　　查询相关的状态变量
　　SHOWGLOBAL STATUS LIKE 'Qcache%';
　　+-------------------------+----------+
　　|Variable_name           | Value    |
　　+-------------------------+----------+
　　|Qcache_free_blocks      | 1        |
　　|Qcache_free_memory      | 16759688 |
　　|Qcache_hits             | 0        |
　　|Qcache_inserts          | 0        |
　　|Qcache_lowmem_prunes    | 0        |
　　|Qcache_not_cached       | 0        |
　　|Qcache_queries_in_cache | 0        |
　　|Qcache_total_blocks     | 1        |
　　+-------------------------+----------+
　　缓存命中率的评估：Qcache_hits/(Qcache_hits+Com_select)
　　查询mysql运行时全局变量
　　例：查询与缓存有关的去全局变量
　　showglobal variables like '%query%'
　　select@@query_cache_type;
　　关闭缓存
　　set query_cache_type=off;
　　MySQL日志：
　　是否启用二进制日志
　　sql_log_bin=on
　　二进制日志保存位置
　　log-bin=二进制日志保存位置

　　show binary logs;

　　查看二进制日志
　　showbinlog events in 'mysql-bin.000001'\G

　　二进制日志文件的位置：

　　二进制日志保存格式
　　binlog_format=mixed混合|statement基于语句|row基于行
　　是否启用二进制日志
　　sql_log_bin=on
　　设置单个二进制日志文件大小上限
　　max_binlog_size=1073741824 [最下4k最大1G]
　　设置二进制日志缓存文件大小
　　max_binlog_cache_size=18446744073709547520[这个值不能大于max_binlog_stmt_cache_size]
　　max_binlog_stmt_cache_size=18446744073709547520
　　设置多久同步一次二进制日志文件,0表示不同步,任何正值都表示记录多少个语句后同步一次
　　sync_binlog=0
　　二进制日志的格式
　　[root@1GPC~]# mysqlbinlog mysql-bin.000001

　　MySQL的备份和恢复
　　注意的要点
　　1.可容忍丢失多少数据
　　2.需要恢复什么
　　3.持锁时长
　　4.备份过程时长
　　5.备份时cpu的负载
　　6.恢复过程时长
　　备份类型：
　　完全备份，部分备份：仅备份其中的一张表或多张表
　　完全备份，增量备份：仅备份从上次完全备份或增量备份之后变化的数据部分
　　热备份、温备份、冷备份
　　热备份：在线备份，读写不受影响
　　温备份：在线备份，读可进行，写不允许
　　冷备份：离线备份，数据库服务器离线，备份期间不能为业务提供读写服务
　　MyISAM：温备份
　　InnoDB：热备份
　　物理备份和逻辑备份
　　物理备份：直接复制数据文件进行的备份。
　　优点：基于文件进行备份的，可以直接复制。恢复数据快。
　　缺点：和存储引擎有关，MyISAM导出的数据不能导入到InnoDB中。不能跨OS。
　　逻辑备份：从数据库中"导出"数据另存而进行的备份。
　　优点：可以通过修改sql语句进行二次加工，与存储引擎无关，可以导入到任何存储引擎中；
　　缺点：无法保证数据导出和在导入或一致性，因为浮点数会取近似值。索引需重建
　　设计备份方案：
　　备份数据、二进制日志、InnoDB事务日志等日志数据、代码(存储过程和存储函数、触发器、时间调度器等)、服务器配置文件
　　完全备份+增量备份
　　建议完全备份1周一次，1天一次增量备份
　　使用mysqldump做完全备份，通过备份二进制日志实现增量备份
　　lvm2快照：几乎热备，用cp，tar做完全备份，通过备份二进制日志实现增量备份
　　xtrabackup：物理备份
　　对InnoDB：热备份，支持完全备份和增量备份
　　其他存储引擎：温备份
　　备份工具：

　　mysqldump：逻辑备份工具，支持完全备份和部分备份，不支持增量备份，若要支持需备份二进制日志，对其他存储引擎温备份，InnoDB热备份
　　C/S架构，mysqldump是Client，mysqld是SERver
　　-A：备份所有数据库例：mysqldump -A -uroot -hlocalhost -x -p > /tmp/all.sql
　　要用 -x 锁定所要备份表或用 -l 锁定备份的表[会导致数据不一致，最好不要使用]
　　--single-transaction：表示启动一个单一事务实现备份，可以实现锁定备份的表
　　-B：指定备份哪个数据库例：mysqldump -uroot -hlocalhost -x -B test -p > /tmp/test.sql
　　使用-B和不使用-B直接备份数据库是有区别的，-B会自动添加create database 数据库这条语句，不使用则需恢复时自检创建数据库
　　备份表：mysqldump -uroot -hlocalhost mysql user -p > /tmp/mysql.test
　　-C：压缩传输，但会大量占用Server的CPU
　　-E：备份指定数据库的事件调度器
　　-R：备份存储过程和存储函数
　　--triggers:备份触发器
　　--master-data[=#]
　　0 表示不记录
　　1 记录CHANGE MASTERTO语句，此语句未被注释
　　2 记录CHANGE MASTERTO语句，此语句被注释
　　例：mysqldump --master-data=2 --lock-all-tables -A > /tmp/all.1.sql
　　-F：锁定表后执行flushlogs命令
　　案例：每周日完全备份，每周一到周五增量备份
　　二进制日志文件与数据文件不应放在同一磁盘上
　　例 hellodb单个数据库：在crontable中设计周期任务
　　完全备份：mysqldump -B tpweb --flush-logs --lock-all-tables --master-data=2 -uroot -hlocalhost -p > /tmp/tpweb-`date +%F`.sql
　　在这个sql文件中会看见

　　第22行表示备份后的所有的更改将会保存在mysql-bin.000003二进制文件中
　　增量备份：基于二进制日志文件进行备份
　　mysqladmin-uroot -p flush-logs
　　这时将会产生一个新的二进制日志文件mysql-bin.000004,mysql-bin.000003二进制文则会保存自上次备份后到现在的所有更改，只需把这个文件备份到安全的位置即可
　　mysqlbinlog /data/mysql-bin.000003 > /tmp/mysql-bin.000003
　　恢复备份：
　　mysql> SET SESSION sql_log_bin=0;关闭还原时产生的二进制日志
　　mysql> SOURCE  /tmp/tpweb-2015-06-16.sql
　　…
　　mysql> SET SESSION sql_log_bin=1;恢复完成后开启二进制日志
　　不然就只能去编辑配置文件去关闭二进制日志，用命令行还原
　　1.恢复完全备份
　　mysql -uroot -hlocalhost -p < /tmp/tpweb-2015-06-16.sql
　　2.恢复增量备份
　　mysql -uroot -hlocalhost -p < /tmp/mysql-bin.000003
　　当数据未备份却进行了误操作，可通过二进制日志进行恢复
　　例：误删除了数据库
　　1.备份二进制日志文件到安全的位置上
　　mysqlbinlog /data/mysql-bin.000003 > /tmp/mysql-bin.000003
　　2.用vimmysql-bin.000003
　　找到最近一次的误操作并删除,并用mysql -uroot -hlocalhost -p < /tmp/mysql-bin.000003恢复

　　或
　　1.先用mysqlbinlog/data/mysql-bin.000003看下误操作的position 即at 后面的数字
　　mysqlbinlog --stop-position 7295 /data/mysql-bin.000003 > /tmp/mysql-bin.000003
　　2.mysql -uroot -hlocalhost-p < /tmp/mysql-bin.000003
　　cp，tar等文件系统工具：物理备份工具，冷备份，一般用于MyISAM备份，支持完全备份和部分备份
　　lvm2快照：几乎热备份，借助cp，tar等文件系统工具实现物理备份,需备份事务日志
　　ibbackup：对于InnoDB支持热备份，需向IBM缴费
　　xtrbackup:对InnoDB热备份，支持完全和增量备份
　　对MyISAM温备份，只支持完全备份
　　官网下载percona-xtrabackup-2.2.3-4982.el6.x86_64.rpm
　　yum install yum install perl-DBD-MySQL
　　yuminstall percona-xtrabackup-2.2.3-4982.el6.x86_64.rpm
　　MySQL复制
　　复制功用
　　数据分布、负载均衡、备份、高可用和故障切换、MySQL升级测试，
　　要想实现复制，需在主节点开启二进制日志
　　主从复制：
　　从服务器：
　　I/O线程：从master请求二进制日志信息，并保存至slave中继日志
　　SQL线程：从[relay log]中继日志中读取日志信息，在本地完成重放
　　默认工作在异步[async]模式
　　存在的问题：
　　1.主从数据不一致
　　2.从服务器数据落后主服务器
　　双主模型：
　　读：负载均衡
　　写：都要写入，无法均衡
　　复制时应注意的问题
　　1.主节点运行很久后要加入从节点
　　在主节点上做一个完全备份，并记录二进制日志文件及position位置
　　在从节点恢复此完全备份，并在启动复制是从记录的二进制日志文件position位置开始
　　2.如何限制从服务器只读
　　在从服务器启动read_only：但仅对非具有SUPER权限的用户有效
　　阻止所有用户：MariaDB> flush tables with read lock
　　3.如何保证主从复制时事务安全
　　在master节点上启用参数：
　　sync_binlog=on
　　若用到的为InnoDB存储引擎：
　　启用Innodb_flush_logs_at_trx_commit  表示一旦提交了事务立即同步到磁盘上
　　Innodb_support_xa=on
　　在slave节点上：
　　skip_slave_start=on  表示跳过slave自动启动
　　主节点在不考虑磁盘IO的情况下：
　　snyc_master_info=1
　　从节点在不考虑磁盘IO的情况下:
　　snyc_master_info=1
　　snyc_relay_log=1
　　sync_relay_log_info=1
　　3.为了避免从服务器数据落后主服务器，可以使用半同步复制
　　主节点：
　　安装插件，文件在/usr/local/mysql/lib/plugin下
　　MariaDB> install plugin rpl_semi_sync_master soname'semisync_master.so'
　　show global variables like '%semi%'可以查看安装后semisync的状态
　　编辑配置文件：
　　rpl_semi_sync_master_enabled =on 开启半同步
　　rpl_semi_sync_master_timeout=2000 超时时间设为2000毫秒
　　从节点：
　　安装插件
　　MariaDB> install plugin rpl_semi_sync_slave soname'semisync_slave.so'
　　show global variables like '%semi%'可以查看安装后semisync的状态
　　编辑配置文件：
　　rpl_semi_sync_slave_enabled =on 开启半同步
　　重启slave线程
　　stop slave io_thread;
　　start slave io_thread;
　　使用show global status like '%semi%' 查看状态是否启动
　　4.复制过滤器，不复制所有数据库，只复制指定的数据库
　　(1)主服务器仅向二进制日志中记录有特定数据库相关的写操作
　　问题：即时点还原将无法实现
　　binlog_do_db =                            数据库白名单
　　binlog_ignore_db =                      数据库黑名单
　　(2)从服务器的SQL_THREAD仅在中继日志中读取特定数据相关的语句并应用在本地
　　问题：会造成网络带宽和磁盘IO的浪费
　　Replicate_Do_DB:                       数据库白名单
　　Replicate_Ignore_DB:                 数据库黑名单
　　Replicate_Do_Table:
　　Replicate_Ignore_Table:
　　Replicate_Wild_Do_Table:            用通配符匹配
　　Replicate_Wild_Ignore_Table:
　　例：
　　先stop slave;
　　set global Replicate_Do_DB=tpweb;
　　在start slave;
　　5.基于SSL的复制
　　前提：支持SSL
　　用show global variables like '%ssl%';查看ssl状态，在编译时指定开启ssl
　　MariaDB[(none)]> show global variables like '%ssl%';
　　+---------------+----------+
　　|Variable_name | Value    |
　　+---------------+----------+
　　| have_openssl  |ENABLED |
　　| have_ssl      | ENABLED |
　　| ssl_ca        |     ca证书路径    |
　　| ssl_capath    |   一堆ca证书路径     |
　　| ssl_cert      |   ca给自己发的证书      |
　　| ssl_cipher   |     加密算法     |
　　| ssl_key      |     私钥     |
　　+---------------+----------+
　　(1) 主服务器配置证书和私钥，并创建一个要求必须使用SSL连接的复制账号(使用REQUIRE SSL)
　　(2)SLAVE端连接MASTER时，使用MASTER_SSL相关的选项来配置证书等信息
　　6.跟复制有关的文件
　　在从节点上的master.info用来保存主从连接时的相关信息，如账号，密码…
　　relay-log.info：保存了当前slave节点上已经复制的当前二进制日志和本地中继日志的对应关系，即position位置
　　7.复制的监控和维护
　　(1)清理日志：使用PURGE
　　(2)监控复制：
　　show master status
　　show binlog events
　　show binary logs
　　show slave status
　　(3)如何判断slave是否落后于master
　　用show slave status中的Seconds_Behind_Master: NULL来观察
　　(4)如何确定主从节点数据是否一致
　　①在创建表时启用checksum，之后可以通过show table status查看主从节点同一张表的checksum是否一致
　　②使用percona-tools中的pt-table-checksum
　　(5)数据不一致时的修复方法：
　　重新复制master节点的数据，重新同步
　　配置MySQL主从：
　　例：172.16.37.20做主节点，172.16.37.21做从节点
　　master：
　　1.启用二进制日志

　　2.设置一个在当前集群中唯一的server-id

　　3.创建一个有复制权限[REPLICATIONSLAVE,REPLICATION CLIENT]的账号
　　在mysql命令行中执行

　　MariaDB [(none)]> grant replicationclient,replication slave on *.* to 'testuser'@'172.16.%.%'>

　　slave：
　　1.启动中继日志，注释二进制日志

　　2.设置一个在当前集群中唯一的server-id

　　3.不允许client写入操作

　　4.利用有复制权限的用户账号连接至主服务器，并启动复制线程
　　MariaDB [(none)]>  change master tomaster_host='172.16.37.20',master_user='testuser',master_password='123456',master_log_file='master-bin.000001',master_log_pos=419,master_connect_retry=5,master_heartbeat_period=2;
　　master_connect_retry=5：表示每5s重连一次
　　master_heartbeat_period=2：表示每2s探测一次master的健康状态
　　在master中查出master_log_pos的值；

　　5.用show slavestatus查看slave状态，并用startslave;启动线程
　　MariaDB[(none)]> show slave status\G
　　***************************1. row ***************************
　　Slave_IO_State:
　　Master_Host: 172.16.37.21
　　Master_User: testuser
　　Master_Port: 3306
　　Connect_Retry: 5
　　Master_Log_File:master-bin.000001
　　Read_Master_Log_Pos: 419

　　>
　　>
　　>　　Slave_IO_Running: No
　　Slave_SQL_Running: No
　　Replicate_Do_DB:
　　Replicate_Ignore_DB:
　　Replicate_Do_Table:
　　Replicate_Ignore_Table:
　　Replicate_Wild_Do_Table:
　　Replicate_Wild_Ignore_Table:
　　Last_Errno: 0
　　Last_Error:
　　Skip_Counter: 0
　　Exec_Master_Log_Pos: 419

　　>　　Until_Condition: None
　　Until_Log_File:
　　Until_Log_Pos: 0
　　Master_SSL_Allowed: No
　　Master_SSL_CA_File:
　　Master_SSL_CA_Path:
　　Master_SSL_Cert:
　　Master_SSL_Cipher:
　　Master_SSL_Key:
　　Seconds_Behind_Master: NULL
　　Master_SSL_Verify_Server_Cert:No
　　Last_IO_Errno: 0
　　Last_IO_Error:
　　Last_SQL_Errno: 0
　　Last_SQL_Error:
　　Replicate_Ignore_Server_Ids:
　　Master_Server_Id: 0
　　测试：在master创建数据库，在slave上查看是否复制成功
　　双主模型：
　　互为主从：
　　1.数据可能不一致；
　　2.id有auto_increment定义一个节点使用基数id，另一节点使用偶数id，但会产生id不连贯
　　用show global variables like '%increment%';查看偏移量

　　auto_increment_offset 表示从几开始
　　auto_increment_increment表示一次增加几
　　定义一个节点使用基数id
　　set @@auto_increment_offset=1;
　　set @@auto_increment_increment=2;
　　定义一个节点使用偶数id
　　set @@auto_increment_offset=2;
　　set @@auto_increment_increment=2;
　　配置：
　　1.各自使用不同的server-id
　　172.16.37.20：

　　172.16.37.21：

　　2.都启用二进制日志和中继日志
　　172.16.37.20：

　　172.16.37.21：

　　3.解决自增字段问题
　　172.16.37.20：

　　172.16.37.21：

　　4.都授权有复制权限的账号
　　172.16.37.20/21都执行授权

　　MariaDB [(none)]> grant replicationclient,replication slave on *.* to 'testuser'@'172.16.%.%'>　　5.各自把对方指定为主服务器
　　172.16.37.20
　　先showmaster status;查看

　　再指定master为对方
　　MariaDB [(none)]> change master to master_host='172.16.37.21',master_user='testuser',master_password='123456',master_log_file='mysq2l-bin.000001',master_log_pos=424,master_connect_retry=5,master_heartbeat_period=2;
　　再show slave status\G 查看slave状态，并用startslave;启动线程
　　172.16.37.21
　　先showmaster status;查看

　　再指定master为对方
　　MariaDB [(none)]> change master to master_host='172.16.37.20',master_user='testuser',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=424,master_connect_retry=5,master_heartbeat_period=2;
　　再show slave status\G 查看slave状态，并用startslave;启动线程
　　测试：在两个节点上创建数据库，并在对方的Client上查看
　　读写分离：
　　为了实现读写分离，写操作都发往master，读都发往slave
　　读写分离器：
　　mysql-proxy 测试版-->奇虎360的二次开发版 (atlas)
　　amoeba 淘宝研发的
　　dbrelay 商业产品
　　mysql-proxy:
　　先配置主从服务器
　　master : 172.16.37.20
　　1.启用二进制日志

　　2.设置一个在当前集群中唯一的server-id

　　3.创建一个有复制权限[REPLICATIONSLAVE,REPLICATION CLIENT]的账号
　　在mysql命令行中执行

　　MariaDB [(none)]> grant replicationclient,replication slave on *.* to 'testuser'@'172.16.%.%'>

　　slave : 172.16.37.21
　　1.启动中继日志，注释二进制日志

　　2.设置一个在当前集群中唯一的server-id

　　3.不允许client写入操作

　　4.利用有复制权限的用户账号连接至主服务器，并启动复制线程
　　MariaDB [(none)]>  change master tomaster_host='172.16.37.20',master_user='testuser',master_password='123456',master_log_file='master-bin.000001',master_log_pos=419,master_connect_retry=5,master_heartbeat_period=2;
　　master_connect_retry=5：表示每5s重连一次
　　master_heartbeat_period=2：表示每2s探测一次master的健康状态
　　在master中查出master_log_pos的值；

　　5.用show slavestatus查看slave状态，并用startslave;启动线程

　　再设置登录用户grant all on *.* to 'lx'@'%'>　　配置mysql-proxy代理服务器，172.16.37.9
　　epel源：yum -yinstall mysql-proxy
　　使用官方二进制格式文件中的rw-splitting.lua脚本进行读写分离
　　配置文件：/etc/mysql-proxy.cnf
　　[mysql-proxy]
　　daemon= true
　　pid-file= /var/run/mysql-proxy.pid
　　log-file = /var/log/mysql-proxy.log  # 可以选择关闭日志，因为记录的非常详细，占用磁盘空间
　　log-level = debug
　　max-open-files= 1024
　　plugins= admin,proxy
　　user= mysql-proxy
　　#
　　#ProxyConfiguration
　　proxy-address = 0.0.0.0:3306                                          #代理服务监听的地址和端口
　　proxy-backend-addresses =172.16.100.7:3306            #支持读写操作的后端mysql
　　proxy-read-only-backend-addresses =172.16.100.8:3306        # 只读后端
　　#proxy-lua-script=
　　proxy-lua-script =/usr/lib64/mysql-proxy/lua/rw-splitting.lua #读写分离脚本，可以注释使用默认
　　#proxy-skip-profiling= true
　　#
　　#Admin Configuration
　　admin-address = 0.0.0.0:4041  # 默认管理程序监听端口
　　admin-lua-script= /usr/lib64/mysql-proxy/lua/admin.lua
　　admin-username = admin # 管理用户
　　admin-password = admin # 管理密码
　　启动服务：/etc/init.d/mysql-proxy start
　　测试：mysql -h172.16.37.9 -ulx -p123456 -e 'create database proxy-test';然后去主从服务器上查看是否创建了此database
　　复制的问题和解决方案：
　　(1) 数据损坏或丢失
　　从服务器崩溃回去找：master.info
　　最好在从节点上启动
　　snyc_master_info=1
　　snyc_binlog=1
　　但会增加IO压力
　　(2) 建议不要混合使用存储引擎，可以考虑都使用InnoDB
　　(3) 注意要使用唯一的serverid
　　(4) 复制延迟
　　重启从服务器的slave线程，要确保当前从服务器没有正在进行的请求和长连接
　　数据库切分：sharding
　　垂直切分：按表进行切分
　　水平切分：按表中的行进行的切分
　　切分条件：读集中，写离散
　　MySQL数据库的向外扩展方式：复制、拆分、sharding
　　拆分方式：
　　按功能拆分：也即按应用拆分；
　　数据分片：
　　选择切分键
　　把数据存储在哪里？
　　从哪儿读数据？
　　数据库切分框架：
　　cobar
　　gizzard
　　HibernatShards
　　HiveDB