MariaDB多线程复制SQL thread和GTID 一、简单主从模式配置步骤环境如下：db:mariadb-10.0.10-linux-x86_64.tar.gzos:centos6.71、配置主从节点的服务配置文件1.1、配置master节点：[mysqld] binlog-format=ROW log-bin=master-bin log-slave-updates=true #gtid-mode=on #enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-threads=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 server-id=1 report-port=3306 port=3306 datadir=/mydata/data socket=/tmp/mysql.sock report-host=master.linuxea [root@mysql-slave mysql]# hostname master.linuxea [root@mysql-slave mysql]# cat /etc/hosts 192.168.0.100 master.linuxea 192.168.0.101 slave.linuxea MariaDB [(none)]> show global variables like '%gtid%';Variable_nameValuegtid_binlog_pos gtid_binlog_state gtid_current_pos gtid_domain_id0gtid_ignore_duplicatesOFFgtid_slave_pos gtid_strict_modeOFF7 rows in set (0.02 sec)创建数据库和表MariaDB [(none)]> create database linuxea; Query OK, 1 row affected (0.01 sec) MariaDB [(none)]> create table linuxea.t5(Name CHAR(30)); Query OK, 0 rows affected (0.03 sec) MariaDB [(none)]> 查看MariaDB [(none)]> show binlog events in 'master-bin.000001';Log_namePosEvent_typeServer_idEnd_log_posInfomaster-bin.0000014Format_desc1248Server ver: 10.0.10-MariaDB-log, Binlog ver: 4master-bin.000001248Gtid_list1277[]master-bin.000001277Binlog_checkpoint1321master-bin.000001master-bin.000001321Gtid1363GTID 0-1-1master-bin.000001363Query1456create database linuxeamaster-bin.000001456Gtid1498GTID 0-1-2master-bin.000001498Query1599create table linuxea.t5(Name CHAR(30))7 rows in set (0.01 sec)MariaDB [(none)]> 1.2、配置slave节点：[root@slave mysql]#·hostname slave.linuxea[mysqld] binlog-format=ROW log-slave-updates=true #gtid-mode=on #enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-threads=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 server-id=11 report-port=3306 port=3306 log-bin=mysql-bin.log datadir=/mydata/data socket=/tmp/mysql.sock report-host=slave.linuxea MariaDB [(none)]> show global variables like '%gtid%';Variable_nameValuegtid_binlog_pos0-200-2129gtid_binlog_state0-200-2129gtid_current_pos0-200-2129gtid_domain_id0gtid_ignore_duplicatesOFFgtid_slave_pos gtid_strict_modeOFF7 rows in set (0.00 sec)MariaDB [(none)]> 2、创建复制用户MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'user'@'192.168.0.%' IDENTIFIED BY 'pass'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> 3、为备节点提供初始数据集锁定主表，备份主节点上的数据，将其还原至从节点；如果没有启用GTID，在备份时需要在master上使用show master status命令查看二进制日志文件名称及事件位置，以便后面启动slave节点时使用。由于在master上有数据，则手动备份后倒入到从即可！[root@mysql-slave mysql]# mysqldump --all-databases --lock-all-tables --flush-logs --master-data=2 > /tmp/all.sql 复制到从数据库[root@mysql-slave mysql]# scp /tmp/all.sql root@192.168.0.101:/tmp/ root@192.168.0.101's password: all.sql 100% 511KB 510.6KB/s 00:00 [root@mysql-slave mysql]# 复制完成后导入即可[root@slave mysql]# mysql < /tmp/all.sql [root@slave mysql]# mysql -e "show databases;" +--------------------+ | Database | +--------------------+ | information_schema | | linuxea | | mysql | | performance_schema | | test | +--------------------+ [root@slave mysql]# 4、启动从节点的复制线程1，打开备份文件查看复制位置[root@slave mysql]# vim /tmp/all.sql-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000003', MASTER_LOG_POS=395; MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='user', MASTER_PASSWORD='pass', MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=395;Query OK, 0 rows affected (0.05 sec)启动复制进程：MariaDB [(none)]> start slave;2，如果启用了GTID功能，则使用如下命令：Gtid_IO_Pos: 0-300-39mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='user', MASTER_PASSWORD='pass', MASTER_USE_GTID=current_pos;如：MariaDB [(none)]> stop slave;Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='user', MASTER_PASSWORD='pass', MASTER_USE_GTID=current_pos;Query OK, 0 rows affected (0.07 sec)MariaDB [(none)]> start slave;Query OK, 0 rows affected (0.02 sec)MariaDB [(none)]> show slave status\G 1. row ** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.100 Master_User: user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 8879 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 730 Relay_Master_Log_File: master-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes 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: 8879 Relay_Log_Space: 1031 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: 0Master_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: 300 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Current_Pos ` Gtid_IO_Pos: 0-300-39`1 row in set (0.00 sec)MariaDB [(none)]> 验证GTID：MariaDB [(none)]> show global variables like '%gtid%'; +------------------------+---------------------+ | Variable_name | Value | +------------------------+---------------------+ | gtid_binlog_pos | 0-300-39 | | gtid_binlog_state | 0-200-2257,0-300-39 | | gtid_current_pos | 0-300-39 | | gtid_domain_id | 0 | | gtid_ignore_duplicates | OFF | | gtid_slave_pos | 0-300-39 | | gtid_strict_mode | OFF | +------------------------+---------------------+ 7 rows in set (0.00 sec) MariaDB [(none)]> 验证多线程：在主库导入数据可在从使用以下进行查看验证[root@slave mysql]# watch -n .5 "mysql -e 'show slave statusG'"[root@slave mysql]# watch -n .5 "mysql -e 'show processlistG'"双线程已经打开！MariaDB [(none)]> show global variables like '%parallel%';Variable_nameValueslave_domain_parallel_threads0slave_parallel_max_queued131072`slave_parallel_threads2 `3 rows in set (0.00 sec)MariaDB [(none)]> SHOW PROCESSLIST;IdUserHostdbCommandTimeStateInfoProgress3system user NULLConnect22Waiting for work from SQL thread NULL0.0004system user NULLConnect22 Waiting for work from SQL thread NULL0.0005system user NULLConnect22Slave has read all relay log; waiting for the slave I/O thread to update itNULL0.0006system user NULLConnect22Waiting for master to send eventNULL0.0008rootlocalhostNULLQuery0initSHOW PROCESSLIST0.0005 rows in set (0.00 sec)MariaDB [(none)]> mysql GTID和mariadb不同如下：MariaDB GTID:如上中应用MariaDB-10，需要做的修改：1、不支持的参数：gtid-mode=on enforce-gtid-consistency=true 2、修改的参数：slave-parallel-workers参数修改为slave-parallel-threads3、连接至主服务使用的命令：一个新的参数：MASTER_USER_GTID={current_pos|slave_pos|no}CHANGE MASTER TO master_host="127.0.0.1", master_port=3310, master_user="root", master_use_gtid=current_pos;

mysql5.6的GTID介绍 mysql5.6 借助GTID提升，5.6支持多线程复制（多数据库同时复制才有意义，仅仅复制一个则没有意义）通常，由于读取较大，主负责数据的写入，从负责的读取，可以有多从当主节点down后，自动fileover新的主服务器（将从提升到主），继续提供服务但是，如果在进行fileover过程中，主数据写入一半，从数据库未完全得到完整的数据，这种情况下，从的数据则比如落后与主数据库架设在主服务器上存在5个数据，从服务器只复制了4个，另一个从服务器复制了3个，此刻，无论将从服务器任何一个提升主数据的都需要进行比较，并且得到最新的数据GTID快速提升从未主时，必然写在二进制中，在事务之前被写入，而从服务器在复制时也会将GTID及相关事务数据一并做复制从节点在复制这些数据后，并不会在本地生成新的GTID，而是直接保存源服务器的GTID信息，这样便可以保证事件多次传送，仍然能够保存事件位置mysqlreplicate： 快速调入一个从节点，并且成为GTID中的从节点mysqlrplcheck: 简单的校验，在ha性能时能够检查节点，那些更易用，更完整的提省为主节点mysqlrplshow：显示发现拓扑结构mysqlfailover：能够实现，手动或自动实现故障转移，将从节点提升为主节点mysqlrpladmin: 实现管理调度

MariaDB复制和pt-table-checksum 复制相关的文件：master.info：用于保存从服务器链接主服务器所需要的信息，每行一个值relay-log.info：文本文件，保存复制位置，包括二进制日志和中继日志的文件及位置为了复制的安全性：sync_master_info = 1 sync_relay_log = 1 sync_relay_log_info = 1 基于行基于语句复制：基于语句： 数据量小，易于查看，实用性较强 有些语句也无法做精确复制：无法对使用了触发器，存储过程等代码的应用实现精确复制；基于行：能够精确完成有着触发器，存储等代码场景中的复制，能完成几乎对所有的复制功能，并且较少的cpu占用率但是，无法判断执行了什么样的sql语句评估主从服务表中数据是否一致：pt-table-checksum通常次命令会在主服务器运行，检查从服务器是否一致，如果不一致，则手动同步1，重新备份主服务器数据，并且在从服务器导入数据2，pt-table-sync同步，高效同步，但是在数据量大时候，相当消耗时间用法举例：假设10.55.55.55是主库，10.73.73.73是它的从库，端口在3306。先校验： PTDEBUG=1 ./pt-table-checksum --user=user --password=pass --host=10.55.55.55 --port=3306 --databases=elink --tables=my_cms_10 --recursion-method=processlist根据校验结果，只修复10.73.73.73从库与主库不一致的地方：PTDEBUG=1 ./pt-table-sync --execute --replicate percona.checksums --sync-to-master h=10.73.73.73,P=3306,u=user,p=pass修复后，再重新校验一次。执行第一步的语句即可。检查修复结果： 登陆到10.73.73.73，执行如下sql语句返回若为空，则说明修复成功： select * from percona.checksums where master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)为了提高复制时的数据安全性，在主服务器上设定(对系统性能有严重的损耗，一般设置为2)：sync_binlog = 1 innodb_flush_log_at_trx_commit = 1 设置为1，对系统性能有严重的损耗,；一般设置为2，如果为2，则主服务器崩溃依然有可能导致从服务器无法获取到全部的二进制日志事件*从服务器意崩溃时，使用pt-slave-start命令来启动*主服务器崩溃导致二进制日志没写完，从则需要忽略或者使用pt-table重新同步在从使用如下参数忽略：sql_slave_skip_counter = 1如：sql_slave_skip_counter 0 设置为1，忽略损耗的MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%slave%';Variable_nameValueinit_slave log_slave_updatesOFFrpl_semi_sync_master_wait_no_slaveONslave_compressed_protocolOFFslave_exec_modeSTRICTslave_load_tmpdir/tmpslave_max_allowed_packet1073741824slave_net_timeout3600slave_skip_errorsOFFslave_sql_verify_checksumONslave_transaction_retries10slave_type_conversions sql_slave_skip_counter013 rows in set (0.00 sec)MariaDB [(none)]> 从服务器是否落后与主服务器：MariaDB [(none)]> show slave statusGSeconds_Behind_Master: NULL NULL则线程未启动 Seconds_Behind_Master: 0 0则没有延迟这个数值如果只是在波动，而不是在变大，则问题不大，如果一旦数值变大，则手动介入安装包：percona-toolkit-2.2.4-1.noarch.rpmpt-summary 查看系统性能[root@mysql-slave local]# pt-summary # Percona Toolkit System Summary Report ###################### Date | 2016-02-15 05:50:26 UTC (local TZ: PST -0800) Hostname | mysql-slave Uptime | 10 days, 22:53, 2 users, load average: 0.00, 0.00, 0.00 System | VMware, Inc.; VMware Virtual Platform; vNone (Other) Service Tag | VMware-56 4d 08 c9 78 a4 5b 25-a5 cb 55 d1 96 68 3c f5 Platform | Linux Release | CentOS release 6.6 (Final) Kernel | 2.6.32-573.3.1.el6.x86_64 Architecture | CPU = 64-bit, OS = 64-bit Threading | NPTL 2.12 Compiler | GNU CC version 4.4.7 20120313 (Red Hat 4.4.7-16). SELinux | Permissive Virtualized | VMWare # Processor ################################################## Processors | physical = 1, cores = 1, virtual = 1, hyperthreading = no Speeds | 1x3092.889 Models | 1xIntel(R) Core(TM) i5-4440 CPU @ 3.10GHz Caches | 1x6144 KB # Memory ##################################################### Total | 981.2M Free | 260.3M Used | physical = 720.9M, swap allocated = 1.9G, swap used = 0.0, virtual = 720.9M Buffers | 154.9M Caches | 284.2M Dirty | 364 kB UsedRSS | 205.0M Swappiness | 60 DirtyPolicy | 20, 10 DirtyStatus | 0, 0 Locator Size Speed Form Factor Type Type Detail ========= ======== ================= ============= ============= =========== RAM slot #0 1024 MB Unknown DIMM DRAM EDO RAM slot #10 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #11 {EMPTY} Unknown DIMM DRAM Unknown RAM slot #9 {EMPTY} Unknown DIMM DRAM Unknown # Mounted Filesystems ######################################## Filesystem Size Used Type Opts Mountpoint /dev/sda1 283M 23% ext4 rw /boot /dev/sda2 18G 36% ext4 rw / tmpfs 491M 0% tmpfs rw,rootcontext="system_u:object_r:tmpfs_t:s0" /dev/shm # Disk Schedulers And Queue Size ############################# sda | [cfq] 128 sr0 | [cfq] 128 # Disk Partioning ############################################ Device Type Start End Size ============ ==== ========== ========== ================== /dev/sda Disk 21474836480 /dev/sda1 Part 1 39 312560640 /dev/sda2 Part 39 2358 19074424320 /dev/sda3 Part 2358 2611 2080995840 # Kernel Inode State ######################################### dentry-state | 46604 38113 45 0 0 0 file-nr | 960 0 96182 inode-nr | 40658 93 # LVM Volumes ################################################ Unable to collect information # LVM Volume Groups ########################################## Unable to collect information # RAID Controller ############################################ Controller | No RAID controller detected # Network Config ############################################# Controller | Intel Corporation 82545EM Gigabit Ethernet Controller (Copper) (rev 01) FIN Timeout | 60 Port Range | 61000 # Interface Statistics ####################################### interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors ========= ========= ========== ========== ========== ========== ========== lo 6000 80 0 6000 80 0 eth2 30000000 400000 0 4000000 60000 0 # Network Devices ############################################ Device Speed Duplex ========= ========= ========= eth2 1000Mb/s Full # Network Connections ######################################## Connections from remote IP addresses 192.168.0.3 2 Connections to local IP addresses 192.168.0.100 2 Connections to top 10 local ports 22 2 States of connections ESTABLISHED 2 LISTEN 10 # Top Processes ############################################## PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 1 root 20 0 19356 1540 1228 S 0.0 0.2 0:01.43 init 2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd 3 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0 4 root 20 0 0 0 0 S 0.0 0.0 0:04.03 ksoftirqd/0 5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 stopper/0 6 root RT 0 0 0 0 S 0.0 0.0 0:01.84 watchdog/0 7 root 20 0 0 0 0 S 0.0 0.0 14:22.34 events/0 8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 events/0 9 root 20 0 0 0 0 S 0.0 0.0 0:00.00 events_long/0 # Notable Processes ########################################## PID OOM COMMAND 1710 -17 sshd # Simplified and fuzzy rounded vmstat (wait please) ########## procs ---swap-- -----io---- ---system---- --------cpu-------- r b si so bi bo ir cs us sy il wa st 1 0 0 0 0 3 25 10 0 0 100 0 0 0 0 0 0 0 0 400 800 11 20 69 0 0 0 0 0 0 0 0 60 70 0 1 99 0 0 0 0 0 0 0 0 25 60 0 0 100 0 0 0 0 0 0 0 0 30 60 0 1 99 0 0 # The End #################################################### [root@mysql-slave local]# 第三方复制解决方案: Tungsten,Galera

MariaDB复制过滤器简述 MariaDB复制过滤器1，在主服务器的二进制日志中只记录与hello.db的事件，除此之外都不记录2，主服务器的二进制日志发往从，从接收后进行数据sql过滤达到复制单表的功能如果在从服务器做过滤，主二进制日志会大量的发送到从服务器，二从服务器只需要一部分，则空间和带宽被占用如果在主服务器做过滤；一旦主服务器灾难性宕机，且因为主服务器过滤日志信息，则无法使用时间点还原，因此第二种方式（从服务器过滤）更适用主服务器只能过滤到库级别，从服务器则可以过滤到表级别Master:binlog_do_db=复制指定数据库（可以是多个如果启用do_db则所有启用do_db的库将被记录在二进制日志中，其他的都不记录binlog_ignore_db=忽略那些数据库Slave:replicate_do_db=复制指定数据库（可以是多个）replicate_ignore_db=忽略那些数据库*：do_db和ignore_db通常不一起使用，一旦启用则已白名单为准（do_db）表过滤replicate_do_table=表白名单，（需要指明库db_name.table_name）replicate_ignore_table=黑名单通配符过滤replicate_wild_do_table=白名单replicate_wild_ignore_table=黑名单通常这些配置定义在my.cnf的[mysqld中]

MariaDB从复制和半复制 Mysql复制应用：1，如何限制从服务器只读read_only=ON 限制所有用户：mysql > FLUSH TABLES WITH READ LOCK； 2,主从服务器的时间同步*/5 * * * * /usr/sbin/ntpdate cn.pool.ntp.org 3,如何主从复制时的事务安全在主服务器配置：sync_binlog=1主从复制配置：1，双放版本一直，如果不一致则主要低于从2，都从0开始复制，或者主服务器运行一段时间后，从服务器则需要备份后进行复制配置过程主服务器：1，修改server-id，2，启动二进制日志vim /etc/mysql/my.cnf server-id = 20 log-bin=/mydata/binlogs/master-bin 二进制日志位置 创建日志目录mkdir /mydata/binlogs chown mysql.mysql /mydata/binlogs 3，创建有复制权限的账号MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'user'@'192.168.0.101' IDENTIFIED BY 'pass'; 从服务器：1，改server-id,2，启用中继日志vim /etc/mysql/my.cnf 注释掉二进制日志：log-bin=/mydata/logs/slave-relay-bin server-id = 5 relay-log = /mydata/relaylogs/relay-bin 创建目录mkdir /mydata/relaylogs/ chown mysql.mysql /mydata/relaylogs/ 3，指向主服务器,3.1主服务器：MariaDB [(none)]> show master status；+-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000019 | 789 | | | +-------------------+----------+--------------+------------------+ CHANGE MASTER TO MASTER_HOST='192.168.0.101',MASTER_USER='user',MASTER_PASSWORD='pass',MASTER_LOG_FILE='master-bin.000019',MASTER_LOG_POS=789,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2; 4，启动复制线程MariaDB [(none)]> start slave; MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.101 Master_User: user Master_Port: 3306 Connect_Retry: 5 Master_Log_File: master-bin.000019 Read_Master_Log_Pos: 1603 Relay_Log_File: mysql-slave-relay-bin.000003 Relay_Log_Pos: 789 Relay_Master_Log_File: master-bin.000019 Slave_IO_Running: Yes Slave_SQL_Running: Yes 主服务器运行一段时间后，从服务器则需要备份后进行复制1,备份[root@mysql-master local]# mysqldump --all-databases --flush-logs --master-data=2 --lock-all-tables > /tmp/`date +%F-%T`.sql 2，将备份传送到从服务器[root@mysql-master local]# scp /tmp/2016-02-04-00\:34\:09.sql root@192.168.0.100:/tmp/ 3，在从服务器上确保 Slave_IO_Running: No Slave_SQL_Running: No是停止的MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.0.101 Master_User: user Master_Port: 3306 Connect_Retry: 5 Master_Log_File: master-bin.000019 Read_Master_Log_Pos: 1603 Relay_Log_File: mysql-slave-relay-bin.000003 Relay_Log_Pos: 798 Relay_Master_Log_File: master-bin.000019 Slave_IO_Running: No Slave_SQL_Running: No 4，恢复[root@mysql-slave data]# mysql < /tmp/2016-02-04-00\:34\:09.sql 5，打开/tmp/2016-02-04-00\:34\:09.sql 找到如下：-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000023', MASTER_LOG_POS=245;MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.0.101',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000023',MASTER_LOG_POS=245; Query OK, 0 rows affected (0.04 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.03 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.101 Master_User: repluser Master_Port: 3306 Connect_Retry: 5 Master_Log_File: master-bin.000023 Read_Master_Log_Pos: 245 Relay_Log_File: mysql-slave-relay-bin.000002 Relay_Log_Pos: 530 Relay_Master_Log_File: master-bin.000023 Slave_IO_Running: Yes Slave_SQL_Running: Yes 到此为止恢复完成！4，半同步复制当数据到主服务器后会同步至从服务器，从服务器同步完成后才会向返回请求主服务器启动插件[root@mysql-master local]# mysql MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; Query OK, 0 rows affected (0.09 sec) MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_semi_sync_master_enabled | OFF | | rpl_semi_sync_master_timeout | 10000 | 等待响应时间 | rpl_semi_sync_master_trace_level | 32 | 跟踪级别 | rpl_semi_sync_master_wait_no_slave | ON | +------------------------------------+-------+ 4 rows in set (0.00 sec) MariaDB [(none)]> 修改响应时间MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_timeout =4000; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 4000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +------------------------------------+-------+ 4 rows in set (0.00 sec) MariaDB [(none)]> 从服务器启动插件[root@mysql-slave plugin]# mysql MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 2 rows in set (0.01 sec) MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 2 rows in set (0.01 sec) MariaDB [(none)]> 主服务器插入数据：MariaDB [(none)]> use mydb;Database changed首次执行需要2秒，rpl_semi_sync_master_wait_no_slave | ON 所定！时间由SET GLOBAL rpl_semi_sync_master_timeout =4000;所定！MariaDB [linuxea]> CREATE TABLE t4(name char(20));Query OK, 0 rows affected (4.04 sec)MariaDB [mydb]> 关闭io进程后，在启动即可！stop slave io_thread; start slave io_thread; 在此插入即可！MariaDB [linuxea]> CREATE TABLE t1(name char(20)); Query OK, 0 rows affected (0.07 sec) MariaDB [linuxea]> CREATE TABLE t2(name char(20)); Query OK, 0 rows affected (0.02 sec) MariaDB [linuxea]> CREATE TABLE t3(name char(20)); Query OK, 0 rows affected (0.08 sec) MariaDB [linuxea]> 主服务器验证版同步是否生效：| Rpl_semi_sync_master_clients | 1 |MariaDB [linuxea]> show global status like '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 539 | | Rpl_semi_sync_master_net_wait_time | 539 | | Rpl_semi_sync_master_net_waits | 1 | | Rpl_semi_sync_master_no_times | 1 | | Rpl_semi_sync_master_no_tx | 3 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 443 | | Rpl_semi_sync_master_tx_wait_time | 443 | | Rpl_semi_sync_master_tx_waits | 1 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 1 | +--------------------------------------------+-------+ 14 rows in set (0.00 sec) MariaDB [linuxea]> 一旦某次等待超时，会自动降级为异步！

关于Mariadb复制 SLAVE :IO thread：向主服务器请求二进制日志的事件SQL thread：向中级日志读取事件并在本地执行MASTER：binlog dump:将IO thread请求的事件发送给对方工作架构：从服务器：有且只能有一个主服务器；读写分离：主从模型下，让前段分发能够识别读写，比且按需要调度至目标主机,降低耦合度调度软件：1,amoeba（变形虫） 2,mysql-proxy 双主模型 1，必须设定双方的auto_increment_increment=(定义的自动增长字段起始值)auto_increment_offset=2 2, 数据不一致 Age，Salary使用工具检测是否数据不一致 ，如果不一致则重新同步！双主模型，是分担了读请求，而写请求并没有降低，因为master同时写入一条数据的一部分！如果一台服务器性能出问题，那么两台也不行，那将涉及到分片分片当用户请求，查询条件会发到多服务器进行，将数据切割为多块数据，而前段服务器必须知道数据都存放在那些数据库上，并且知道是如何发，分担写操作，分片是必要的，但是mysql的分片是非常复杂的，在大量数据的写入情况下NOSQL就出现了！1,主从，双方版本一致性，主版本低于从版本2,复制的节点：1，主从从0开始 2，主服务运行，数据已经不小，这种情况下则备份主服务器，从服务器恢复全备，在从备份时位置复制即可

percona Xtrabackup备份压缩\单张表备份\部分备份简述 1、Xtrabackup的“流”及“备份压缩”功能Xtrabackup对备份的数据文件支持“流”功能，即可以将备份的数据通过STDOUT传输给tar程序进行归档，而不是默认的直接保存至某备份目录中。要使用此功能，仅需要使用--stream选项即可。如： 如：# innobackupex --stream=tar /backup | gzip > /backup/date +%F_%H-%M-%S.tar.gz甚至也可以使用类似如下命令将数据备份至其它服务器： 如：# innobackupex --stream=tar /backup | ssh user@www.linuxea.com "cat - > /backups/date +%F_%H-%M-%S.tar" 此外，在执行本地备份时，还可以使用--parallel选项对多个文件进行并行复制。此选项用于指定在复制时启动的线程数目。当然，在实际进行备份时要利用此功能的便利性，也需要启用innodb_file_per_table选项或共享的表空间通过innodb_data_file_path选项存储在多个ibdata文件中。对某一数据库的多个文件的复制无法利用到此功能。其简单使用方法如下：如：# innobackupex --parallel /path/to/backup同时，innobackupex备份的数据文件也可以存储至远程主机，这可以使用--remote-host选项来实现：如：# innobackupex --remote-host=root@www.magedu.com /path/IN/REMOTE/HOST/to/backup备份的注意1，数据和备份通常备份在单独的服务器上，可异机，异地备份，可周期完全备份。2，并且周期性恢复测试；3，在每次恢复后需要完全备份；4，针对不同规模级别的数据定制备份策略；5，数据库配置文件相关需要备份！6，二进制日志文件尽可能和数据不在一个磁盘，并且周期备份备份恢复的步骤1，停止mysql服务器2，记录服务器的配置和文件权限3，将数据从备份移动到mysql数据目录，其知晓方式依赖于工具4，改变配置和文件权限5，以限制的访问模块重启服务器：mysqld的--skip-network选项可以跳过网络修改my.cnf 添加` skip-networking` 如：`socket=/tmp/mysql-recovery.sock` 6，载入逻辑备份（如果有）；检查和重放二进制日志7，检查意见还原的数据8，重新以完全访问的模式重启服务器注释`my.cnf` `skip-networking` 即可 2、导入或导出单张表默认情况下，InnoDB表不能通过直接复制表文件的方式在mysql服务器之间进行移植，即便使用了innodb_file_per_table选项。而使用Xtrabackup工具可以实现此种功能，不过，此时需要“导出”表的mysql服务器启用了innodb_file_per_table选项（严格来说，是要“导出”的表在其创建之前，mysql服务器就启用了innodb_file_per_table选项），并且“导入”表的服务器同时启用了innodb_file_per_table和innodb_expand_import选项。(1)“导出”表导出表是在备份的prepare阶段进行的，因此，一旦完全备份完成，就可以在prepare过程中通过--export选项将某表导出了：如：# innobackupex --apply-log --export /path/to/backup此命令会为每个innodb表的表空间创建一个以.exp结尾的文件，这些以.exp结尾的文件则可以用于导入至其它服务器。(2)“导入”表要在mysql服务器上导入来自于其它服务器的某innodb表，需要先在当前服务器上创建一个跟原表表结构一致的表，而后才能实现将表导入：如：mysql> CREATE TABLE mytable (...) ENGINE=InnoDB;然后将此表的表空间删除：如：mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;接下来，将来自于“导出”表的服务器的mytable表的mytable.ibd和mytable.exp文件复制到当前服务器的数据目录，然后使用如下命令将其“导入”：如：mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;实例如下：1，完全备份一次[root@mysql-master mybackups]# innobackupex -user=root /mybackups/ [root@mysql-master mybackups]# ll total 4 drwxr-xr-x. 7 root root 4096 Jan 27 00:25 2016-01-27_00-25-53 [root@mysql-master mybackups]# 2，导出：[root@mysql-master mybackups]# innobackupex --apply-log --export /mybackups/2016-01-27_00-25-53/ 导出后，会出现exp文件[root@mysql-master mybackups]# ls 2016-01-27_00-25-53/hellodb/ classes.cfg coc.cfg courses.cfg db.opt Ms.ibd scores.ibd students.ibd t1.ibd teachers.ibd toc.ibd classes.exp coc.exp courses.exp Ms.cfg scores.cfg students.cfg t1.cfg teachers.cfg toc.cfg classes.frm coc.frm courses.frm Ms.exp scores.exp students.exp t1.exp teachers.exp toc.exp classes.ibd coc.ibd courses.ibd Ms.frm scores.frm students.frm t1.frm teachers.frm toc.frm [root@mysql-master mybackups]# 3，导入：需要先在当前服务器上创建一个跟原表表结构一致的表，而后才能实现将表导入：3.1为了方便查看创建表的命令，直接复制到需要导入的表中MariaDB [hellodb]> SHOW CREATE TABLE students; | Table | Create Table | | students | CREATE TABLE `students` ( `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum('F','M') NOT NULL, `ClassID` tinyint(3) unsigned DEFAULT NULL, `TeacherID` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`StuID`) ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 | MariaDB [hellodb]> 3.2创建和原表表结构一致的表MariaDB [(none)]> create database linuxea; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> use linuxea; Database changed MariaDB [linuxea]> CREATE TABLE `students` ( -> `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT, -> `Name` varchar(50) NOT NULL, -> `Age` tinyint(3) unsigned NOT NULL, -> `Gender` enum('F','M') NOT NULL, -> `ClassID` tinyint(3) unsigned DEFAULT NULL, -> `TeacherID` int(10) unsigned DEFAULT NULL, -> PRIMARY KEY (`StuID`) -> ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.04 sec) MariaDB [linuxea]> 3.3修改表，删除表空间MariaDB [linuxea]> ALTER TABLE students DISCARD TABLESPACE; Query OK, 0 rows affected (0.06 sec) MariaDB [linuxea]> 3.4复制导入的文件[root@mysql-master mybackups]# ll 2016-01-27_00-25-53/hellodb/students.* -rw-r--r--. 1 root root 640 Jan 27 00:27 2016-01-27_00-25-53/hellodb/students.cfg -rw-r--r--. 1 root root 16384 Jan 27 00:27 2016-01-27_00-25-53/hellodb/students.exp -rw-r--r--. 1 root root 8736 Jan 27 00:25 2016-01-27_00-25-53/hellodb/students.frm -rw-r-----. 1 root root 98304 Jan 27 00:25 2016-01-27_00-25-53/hellodb/students.ibd [root@mysql-master mybackups]# cp 2016-01-27_00-25-53/hellodb/students.ibd /mydata/data/linuxea1/ [root@mysql-master mybackups]# cp 2016-01-27_00-25-53/hellodb/students.exp /mydata/data/linuxea1/ [root@mysql-master mybackups]# cp 2016-01-27_00-25-53/hellodb/students.cfg /mydata/data/linuxea1/ [root@mysql-master mybackups]# chown -R mysql.mysql /mydata/data/linuxea1/ [root@mysql-master mybackups]# ll /mydata/data/linuxea1/ total 236 -rw-r--r--. 1 mysql mysql 65 Jan 26 01:46 db.opt -rw-r--r--. 1 mysql mysql 8560 Jan 26 01:46 linuxea.frm -rw-r--r--. 1 mysql mysql 98304 Jan 26 01:46 linuxea.ibd -rw-r--r--. 1 root root 16384 Jan 27 00:40 students.exp -rw-rw----. 1 mysql mysql 8736 Jan 27 00:34 students.frm -rw-r-----. 1 root root 98304 Jan 27 00:39 students.ibd [root@mysql-master mybackups]# MariaDB [linuxea]> ALTER TABLE students IMPORT TABLESPACE; MariaDB [mydb]> ALTER TABLE students IMPORT TABLESPACE; ERROR 1030 (HY000): Got error -1 from storage engine MariaDB [mydb]> 日志错误可以借鉴这篇文章：http://imysql.cn/2008_12_17_migrate_innodb_tablespace_smoothl3、使用Xtrabackup对数据库进行部分备份xtrabackup不适用于部分备份，更适用于完全备份的增量的备份和恢复，部分备份mysqldump更适用Xtrabackup也可以实现部分备份，即只备份某个或某些指定的数据库或某数据库中的某个或某些表。但要使用此功能，必须启用innodb_file_per_table选项，即每张表保存为一个独立的文件。同时，其也不支持--stream选项，即不支持将数据通过管道传输给其它程序进行处理。此外，还原部分备份跟还原全部数据的备份也有所不同，即你不能通过简单地将prepared的部分备份使用--copy-back选项直接复制回数据目录，而是要通过导入表的方向来实现还原。当然，有些情况下，部分备份也可以直接通过--copy-back进行还原，但这种方式还原而来的数据多数会产生数据不一致的问题，因此，无论如何不推荐使用这种方式。(1)创建部分备份创建部分备份的方式有三种：正则表达式(--include), 枚举表文件(--tables-file)和列出要备份的数据库(--databases)。(a)使用--include使用--include时，要求为其指定要备份的表的完整名称，即形如databasename.tablename，如：如：# innobackupex --include='^linuxea[.]tb1' /path/to/backup(b)使用--tables-file此选项的参数需要是一个文件名，此文件中每行包含一个要备份的表的完整名称；如：如：# echo -e 'linuxea.tb1\linuxea.tb2' > /tmp/tables.txt如：# innobackupex --tables-file=/tmp/tables.txt /path/to/backup(c)使用--databases此选项接受的参数为数据名，如果要指定多个数据库，彼此间需要以空格隔开；同时，在指定某数据库时，也可以只指定其中的某张表。此外，此选项也可以接受一个文件为参数，文件中每一行为一个要备份的对象。如：如：# innobackupex --databases="linuxea testdb" /path/to/backup(2)整理(preparing)部分备份prepare部分备份的过程类似于导出表的过程，要使用--export选项进行：如：# innobackupex --apply-log --export /pat/to/partial/backup此命令执行过程中，innobackupex会调用xtrabackup命令从数据字典中移除缺失的表，因此，会显示出许多关于“表不存在”类的警告信息。同时，也会显示出为备份文件中存在的表创建.exp文件的相关信息。(3)还原部分备份还原部分备份的过程跟导入表的过程相同。当然，也可以通过直接复制prepared状态的备份直接至数据目录中实现还原，不要此时要求数据目录处于一致状态。

备份数据库某些字符或某些行 备份数据库某些字符或某些行保存表中所有为M的数据例： MariaDB [hellodb]> SELECT * FROM students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.01 sec) 过滤出Gender M如：MariaDB [hellodb]> SELECT * FROM students WHERE Gender='M'; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 15 rows in set (0.00 sec) 保存到/tmp/下例：MariaDB [hellodb]> SELECT * FROM students WHERE Gender='M' INTO OUTFILE '/tmp/M.txt'; Query OK, 15 rows affected (0.01 sec) 使用cat可看到如下结果[root@mysql-master mysql]# cat /tmp/M.txt 1 Shi Zhongyu 22 M 2 3 2 Shi Potian 22 M 1 7 3 Xie Yanke 53 M 2 16 4 Ding Dian 32 M 4 4 5 Yu Yutong 26 M 3 1 6 Shi Qing 46 M 5 \N 11 Yuan Chengzhi 23 M 6 \N 13 Tian Boguang 33 M 2 \N 15 Duan Yu 19 M 4 \N 16 Xu Zhu 21 M 1 \N 17 Lin Chong 25 M 4 \N 18 Hua Rong 23 M 7 \N 23 Ma Chao 23 M 4 \N 24 Xu Xian 27 M \N \N 25 Sun Dasheng 100 M \N \N 仿照 students表结构，创建一个表叫做Ms，然后导入/tmp/M.txt例：MariaDB [hellodb]> SELECT * FROM students WHERE Gender='M' INTO OUTFILE '/tmp/M.txt'; Query OK, 15 rows affected (0.01 sec) 例：MariaDB [hellodb]> CREATE TABLE Ms LIKE students; Query OK, 0 rows affected (0.03 sec) 例：MariaDB [hellodb]> DESC Ms; +-----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------------+------+-----+---------+----------------+ | StuID | int(10) unsigned | NO | PRI | NULL | auto_increment | | Name | varchar(50) | NO | | NULL | | | Age | tinyint(3) unsigned | NO | | NULL | | | Gender | enum('F','M') | NO | | NULL | | | ClassID | tinyint(3) unsigned | YES | | NULL | | | TeacherID | int(10) unsigned | YES | | NULL | | +-----------+---------------------+------+-----+---------+----------------+ 6 rows in set (0.02 sec) 导入：导入：MariaDB [hellodb]> LOAD DATA INFILE '/tmp/M.txt' INTO TABLE Ms; Query OK, 15 rows affected (0.00 sec) Records: 15 Deleted: 0 Skipped: 0 Warnings: 0 导入完成后查看：MariaDB [hellodb]> SELECT * FROM Ms; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 15 rows in set (0.00 sec) MariaDB [hellodb]> 这样的备份比较节约空间，但是更适用于备份字段和行！

基于mysqldump二进制备份恢复 备份需要哪些文件？除了数据库数据，还有mysql配置文件，mysql代码（存储过程，存储函数，触发器） ，OS相关的 配置文件，如：crontab配置计划任务及脚本，备份工具本身也需要备份，在主从环境中和复制相关的配置，二进制日志文件，也需要备份！备份工具：mysqldump单机备份工具，对Innodb热备,MyISAM温备，Aria温备。但是mysqldump备份和恢复的过程较慢，数据过10G后，mysqldump则不建议使用mysqldumper多线程mysqldump，很难实现差异活增量备份，只能人工手动增量lvm-snapshot：快照备份，接近于热备工具：先请求全局锁，而后创建快照，快照创建完后释放全局锁 使用cp.tar等工具进行物理备份 备份和恢复速度较快，很难实现增量备份，并且请求全局需要等待一段时间，在繁忙的服务器尤其如此 物理备份工具，不会备份关系等译，仅备份表中数据、逻辑备份工具，快于mysqldumpinnobase商业备份工具，innobackup，innobackup实现innodb热备，增量备份，但是对于myisa只能温备和完全备份。xtrabackup开源的备份工具，由percona提供mysqlhotcopy：几乎冷备，接近于冷备工具备份单个库备份单个库 [root@mysql-salve ~]# mysqldump -uroot -hlocalhost linuxea > /tmp/linuxea.sql恢复单个库恢复之前，如果库本身不存在则需要事先创建：MariaDB [(none)]> create databases linuxea;[root@mysql-salve ~]# mysql < /tmp/linuxea.sql 备份所有数据库--all-databases备份所有数据库： mysqldump --all-databases > /tmp/all.sql备份挑选的库：--databases db1,db2，备份指定的多个库[root@mysql-salve ~]# mysqldump --databases linuxea123 linuxea > /tmp/linuxeaall.sql 恢复[root@mysql-salve ~]# mysql < /tmp/linuxeaall.sql mysqldump备份前需要加锁：`--lock-all-tables`:请求锁定所有表之后在备份，对MyISA温备，Innodb做温备，Aria做温备 [root@mysql-salve ~]# mysqldump --databases linuxea123 linuxea --lock-all-tables < /tmp/linuxeaall.sql 单事务单事务`--single-transaction`:能够对innodb存储引擎实现热备备份代码`--events:`事件调度器代码 `--routines`：备份存储过程和存储函数 `--triggers`：备份触发器备份时滚动日志：`--flush-logs：`备份前，请求到锁之后滚动日志复制时的同步位置标记： --master-data=[0|1|2] 0：不急了 1：记录change master 语句 2：记录为注释的CHANGE MASTER语句使用mysqldump备份请求锁：`--local-all-tables`或者使用`--singe-transaction`进行innodb热备 滚动日志：`--flush-logs` 选定要被备份的库：`--databases` 记录二进制日志文件及位置：`SHOW MASTER STATUS;`手动备份刷新所有表，将缓存数据同步至磁盘MariaDB [(none)]> FLUSH TABLES; 一，手动锁表备份11，手动请求全局锁刷新所有表，将缓存数据同步至磁盘并且加全锁！MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; 查看日志的其实位置MariaDB [(none)]> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000006 | 1125 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 2，手动备份[root@mysql-salve ~]# mysqldump --databases mysql > /tmp/mysql.sql 3，在释放锁MariaDB [(none)]> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) 二，手动锁表备份2[root@mysql-salve ~]# mysqldump --databases mysql --lock-all-tables --flush-logs > /tmp/mysql-3.sql 三，手动热备1[root@mysql-salve ~]# mysqldump --databases mysql --single-transaction --flush-logs > /tmp/mysql-3.sql 及时点还原，需要一次全备来作为下次恢复的起点时间1，查看表的存储引擎MariaDB [(none)]> SHOW TABLE STATUS FROM linuxea\G 2，全表温备份[root@mysql-salve ~]# mysqldump --databases mydb --lock-all-tables --flush-logs --master-data=2 > /opt/11.sql 1，插入数据MariaDB [(none)]> use mydb; Database changed MariaDB [mydb]> show tables; +----------------+ | Tables_in_mydb | +----------------+ | students | | t1 | | t2 | | t3 | | t4 | | t5 | | t6 | +----------------+ 7 rows in set (0.00 sec) MariaDB [mydb]> create table nettb1(NAME CHAR(30)); Query OK, 0 rows affected (0.01 sec) MariaDB [mydb]> insert into nettb1 values ('Tom'),('Jerry'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [mydb]> 删除表！MariaDB [(none)]> DROP DATABASE mydb; Query OK, 8 rows affected (0.05 sec) 在删除后进行还原到创建数据时需要断开远程链接1，找到二进制日志文件，根据上次完全备份的日志段开始进行查看[root@mysql-salve tmp]# vim /opt/11.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=245; 2，找到执行DROP DATABASE mydb;上一个at导出[root@mysql-salve tmp]# mysqlbinlog --start-position=245 /data/mysql/mysql-bin.000017# at 516 #160121 1:00:13 server id 1 end_log_pos 543 Xid = 5538 COMMIT/*!*/; # at 543 #160121 1:00:45 server id 1 end_log_pos 624 Query thread_id=58 exec_time=0 error_code=0 SET TIMESTAMP=1453366845/*!*/; DROP DATABASE mydb /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 3,保存[root@mysql-salve tmp]# mysqlbinlog --start-position=245 --stop-position=516 /data/mysql/mysql-bin.000017 > /opt/22.sql还原3,关掉二进制session（1） MariaDB [(none)]> flush logs; Query OK, 0 rows affected (0.01 sec) 关闭session MariaDB [(none)]> set session sql_log_bin=0; Query OK, 0 rows affected (0.00 sec)（2）恢复全备 MariaDB [(none)]> source /opt/11.sqlQuery OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) （3）恢复增量MariaDB [mydb]> source /opt/22.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec（4查看）MariaDB [mydb]> show tables; +----------------+ | Tables_in_mydb | +----------------+ | nettb1 | | students | | t1 | | t2 | | t3 | | t4 | | t5 | | t6 | +----------------+ 8 rows in set (0.00 sec) MariaDB [mydb]> 1 row in set (0.00 sec) 5，上线二进制日志上线二进制日志 MariaDB [mydb]> set session sql_log_bin=1; Query OK, 0 rows affected (0.00 sec) 备份策略：基于mysqldump 备份：mysqldump+二进制日志文件如：每周做完全备份，备份时滚动日志周一到周日做二进制日志备份mysqladmin flush-logs 滚动日志，复制日志恢复：完全备份+各二进制日志文件中至此刻的事件

关于MariaDB数据库备份这点事 备份和恢复（1）备份的作用。1.灾难恢复和审计2.测试，软件升级测试，恢复测试3.恢复备份类型：根据备份时，数据库是否在线a,冷备, cold backupb,热备，hot backup如果我们锁库进行备份，对于一个写操作非常繁忙的数据库来说，是不现实的，真正的热备任何业务不终止，并且能保证时间点一直！要想完成热备，只有基于事务的存储引擎才能完成c,温备 warm backuP根据备份的数据集又分为完全备份和部分备份完全备份：备份整库full backup部分备份：备份单库partial backup 根据备份的接口（直接备份数据文件还是通过mysql服务器导出）有分为物理备份和逻辑备份physical backup: 物理备份指的是直接复制或者归档文件的备份方式，这种方式完整性不高,物理备份的跨平台能力也没有逻辑的好（如innodb）logical backup: 逻辑备份指的是把数据从库中提取出来并且保存为文本文件，其中包含数据库创建模式等，这种方式与物理备份方式恢复速度慢，占据空间大，逻辑备份也无法保证浮点数的精度，因为毕竟是通过二进制转换到文本，可能无法完全保存浮点数的精度，逻辑备份恢复后，需要重新建立索引，对于一个非常大的数据表，重建索引表是非常消耗CPU资源的根据数据的数据量和数据变化而进行备份，如果备份T级别以上，裸机备份较弱备份可以备份变化的数据备份，如下：完全备份：full backup增量备份：incemental backup差异备份：diff 如图：通常我们在做备份时，可分为增量备份和差异备份，对于比较小的数据完全备份即可。备份数据选择的时间和备份的大小也有一定的注意，如下图：我们通过监控可判断系统在什么时间段不繁忙，可选择这个时间段备份，备份时间不宜过长，大数据则使用物理备份，也可以使用主从备份从库，同时，备份需要不定时的进行恢复测试！如下图：在备份时，必要考虑备份策略，方案落地将简短备份策略：选择备份方式选择备份时间考虑到恢复成本如备份成本，温备份:备份时长考虑： 锁时长： 备份负载： 容忍数据的丢失量