MySQL主从同步及数据备份实践

主从同步原理

主从同步原理更多细节请参考《高性能MySQL读书摘要》系列文章。

主数据库基本配置

[mysqld]
port= 3306
character_set_server = utf8
collation-server = utf8_general_ci
lower_case_table_names=1

log-error=/mysql/log/mysqld_error.log
basedir=/mysql
datadir=/mysql/data
socket=/mysql/mysql.sock
max_connections = 500
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

slow_query_log_file = /mysql/log/slow_query.log
slow_query_log = 1
long_query_time=3
log_queries_not_using_indexes=1
max_allowed_packet=20M

## Use for innodb
innodb_buffer_pool_size = 2048M
innodb_log_file_size = 128M
innodb_log_buffer_size = 16M

## Use for user-defined functions when bin-log is enabled.
log_bin_trust_function_creators=1

# 唯一标识，主库从库不能重复
server-id=223
# 开启日志
log-bin=/mysql/binlogs/mysql-bin
# 日志记录的格式
binlog_format=row
expire_logs_days=7
master_info_repository = TABLE

## GTID Mode
gtid_mode=ON
log_slave_updates=1
enforce_gtid_consistency=1


## Use for Slave
relay_log_info_repository = TABLE
relay_log_recovery = ON


[html] view plain copy

[client]
port = 3306
default-character-set=utf8
socket=/mysql/mysql.sock

[mysql]
default-character-set = utf8

[mysqld_safe]
log-error=/mysql/log/mysqld_error.log
pid-file=/mysql/mysqld.pid
secure_file_priv=/var

[mysqldump]
quick
max_allowed_packet = 500M
user = "backup"
password = "backupdata"

从库配置信息

[client]
#host            = localhost
#user            = backup
#password       = backupdata
port            = 3306
socket          = /tmp/mysql.sock

[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
datadir = /www/server/data
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 100G
table_open_cache = 128
sort_buffer_size = 768K
net_buffer_length = 4K
read_buffer_size = 768K
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 8M
thread_cache_size = 16
query_cache_size = 16M
tmp_table_size = 32M
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

explicit_defaults_for_timestamp = true
#skip-name-resolve
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

#log-bin=mysql-bin
binlog_format=mixed
#server-id = 1
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
early-plugin-load = ""

server-id = 2
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

innodb_data_home_dir = /www/server/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/server/data
innodb_buffer_pool_size = 128M
innodb_log_file_size = 64M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 1
innodb_write_io_threads = 1

[mysqldump]
quick
max_allowed_packet = 500M
user = "backup"
password = "backupdata"

[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 32M
sort_buffer_size = 768K
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

备份脚本

#!/bin/bash
#删除10天前数据

find /data/backup -mtime +10 -name "*.*" -exec rm -rf {} \;

/usr/bin/mysqldump -ubackup --opt  -hlocalhost -P3306  -p"backupdata" --single-transaction shop | gzip  > /data/backup/shop_`date +%Y%m%d`.sql.gz
#mysqldump -u backup --opt  -h localhost -P 3306  -p "backupdata" --single-transaction shop | gzip  > /data/backup/shop_`date +%Y%m%d`.sql.gz

备份数据样式

导入数据