复制相关的文件: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_name | Value |
init_slave | |
log_slave_updates | OFF |
rpl_semi_sync_master_wait_no_slave | ON |
slave_compressed_protocol | OFF |
slave_exec_mode | STRICT |
slave_load_tmpdir | /tmp |
slave_max_allowed_packet | 1073741824 |
slave_net_timeout | 3600 |
slave_skip_errors | OFF |
slave_sql_verify_checksum | ON |
slave_transaction_retries | 10 |
slave_type_conversions | |
sql_slave_skip_counter | 0 |
13 rows in set (0.00 sec)
MariaDB [(none)]>
从服务器是否落后与主服务器:MariaDB [(none)]> show slave statusG
Seconds_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