mysql主从复制

思路：主库开启binlog日志，从库读取操作日志，同步执行。
主要作用：数据库实时备份，保证数据的完整性；
缺点：影响系统一定的性能

一、环境

一台电脑，win10，在VMware安装了centos6.8
win10电脑安装了mysql5.5，centos6.8安装了mysql5.7

二、配置步骤
2.1、主机【master】my.ini文件配置

[client]
port=3306
[mysql]
default-character-set=utf8


# SERVER SECTION
[mysqld]
**server-id=1 #主机标识，比从库小**
# The TCP/IP Port the MySQL Server will listen on
port=3306

#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"

#Path to the database root
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=utf8

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# Binary Logging
**log-bin=mysql-bin
binlog-format=Row
read-only=0   #主机，读写都可以
binlog-do-db=mybatis   #需要备份数据库，多个写多行**

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections=100
query_cache_size=0
table_cache=256
tmp_table_size=18M
thread_cache_size=8

#*** MyISAM Specific options
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=35M
key_buffer_size=25M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K

#*** INNODB Specific options ***
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=47M
innodb_log_file_size=24M
innodb_thread_concurrency=10

2.2、从机【slave】my.cnf文件配置

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8 
socket=/var/lib/mysql/mysql.sock
[mysqld]
socket=/var/lib/mysql/mysql.sock
#skip-name-resolve
#设置3306端口
**server-id=2
log-bin=/usr/local/mysql/data
binlog-format=Row
replicate-do-db=mybatis     #只复制某个库**
port = 3306 
socket=/var/lib/mysql/mysql.sock
# 设置mysql的安装目录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB 
lower_case_table_names=1
max_allowed_packet=16M
[client]
socket=/var/lib/mysql/mysql.sock
[mysqldump]
socket=/var/lib/mysql/mysql.sock
[mysqladmin]
socket=/var/lib/mysql/mysql.sock
[mysqld_safe]
log-error=/usr/local/mysql/data/mysqld.log
pid-file=/usr/local/mysql/data/mysqld.pid

2.3、把主库完整备份一份，恢复到从库
2.4、在主库执行增加表操作，可以看到，从库也增加了相应的表