Mysql主从(基于binlog)

一 安装mysql

分别给两台服务器安装mysql-server

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum repolist all | grep mysql
yum install  -y mysql-server

二 更改配置文件并启动mysql

mysql配置文件/etc/my.cnf

[client]
port                    = 3306
socket                  = /tmp/mysql3306.sock
secure_auth             = false
#default_character_set   = utf8mb4
[mysqld]
port                    = 3306
socket                  = /tmp/mysql3306.sock
datadir                 = /data/mysql3306/mysql3306
#read_only       = on

#--- GLOBAL ---#
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
log-output              = FILE
skip-character-set-client-handshake=1
log-error               = /data/mysql3306/logs/mysql-error.log
#general_log
general_log_file        = /data/mysql3306/logs/mysql.log
pid-file                = /data/mysql3306/mysql.pid
slow-query-log          = 1
slow_query_log_file     = /data/mysql3306/logs/mysql-slow.log
tmpdir                  = /tmp/
long_query_time         = 2
innodb_force_recovery   = 0
#innodb_buffer_pool_dump_at_shutdown = 1
#innodb_buffer_pool_load_at_startup = 1
#--------------#

#thread_concurrency      = 8  
thread_cache_size       = 51  
table_open_cache        = 16384
open_files_limit        = 65535
table_definition_cache  = 16384
sort_buffer_size        = 2M
join_buffer_size        = 2M
read_buffer_size        = 2M
read_rnd_buffer_size    = 8M
key_buffer_size         = 32M
bulk_insert_buffer_size = 16M
myisam_sort_buffer_size = 64M
tmp_table_size          = 32M
max_heap_table_size     = 16M 
query_cache_size       = 32MB

#gtid_mode=on
#log_slave_updates=1
#enforce_gtid_consistency=1

#--- NETWORK ---#
back_log                = 103
max-connections         = 512
max_connect_errors	= 100000
max_allowed_packet      = 32M
interactive_timeout     = 600
wait_timeout            = 600
skip-external-locking
#max_user_connections    = 0
external-locking        = 1
skip-name-resolve       =1

#--- REPL ---#
server-id               = 150
sync_binlog             = 1
log-bin                 = mysql-bin
binlog_format           = row
expire_logs_days        = 10
relay-log               = relay-log
replicate-ignore-db     = test
log_slave_updates       = 1
#skip-slave-start
binlog_cache_size       = 8M
#max_binlog_cache_size  = 8M
max_binlog_size         = 1024M

#--- INNODB ---#
default_storage_engine          = InnoDB
innodb_data_file_path           = ibdata1:1024M:autoextend
innodb_buffer_pool_size         = 64M
innodb_buffer_pool_instances    = 5
innodb_additional_mem_pool_size = 16M
innodb_log_files_in_group       = 2
innodb_log_file_size            = 256MB
innodb_log_buffer_size          = 16M
innodb_flush_log_at_trx_commit  = 2
innodb_lock_wait_timeout        = 30
innodb_flush_method             = O_DIRECT
innodb_max_dirty_pages_pct      = 75
innodb_io_capacity              = 200
innodb_thread_concurrency       = 32
innodb_open_files               = 65535
innodb_file_per_table           = 1
transaction_isolation           = REPEATABLE-READ
innodb_locks_unsafe_for_binlog  = 0
#innodb_purge_thread             = 4
[mysqldump]
quick
max_allowed_packet = 32M

[mysql]
auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
#default_character_set=utf8mb4
[mysqlhotcopy]
interactive-timeout

mkdir -p  /data1/mysql3306/{mysql3306,logs}
chown  -R mysql:mysql /data
systemctl  start mysql
systemctl  enable mysql

三 配置主从关系

在master 执行

mysql -uroot -pxxx -hxxx
create user 'repl'@'192.168.160.%' identified by '123456';#创建账号用于从节点来复制
grant replication slave on *.* to 'repl'@'192.168.160.%';#对上面账号进行授权
show master status

记录binlog 以及position
在slave上执行

CHANGE MASTER TO MASTER_HOST='192.168.160.105',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=120;

然后执行

show slave status\G