首页 编程

# 复制原理

  1. Master 将数据改变记录到二进制日志 (binary log) 中，也就是配置文件 log-bin 指定的文件，这些记录叫做二进制日志事件 (binary log events)
  2. Slave 通过 I/O 线程读取 Master 中的 binary log events 并写入到它的中继日志 (relay log)
  3. Slave 重做中继日志中的事件，把中继日志中的事件信息一条一条的在本地执行一次，完成数据在本地的存储，从而实现将改变反映到它自己的数据 (数据重放)

要求

  1. 主从服务器操作系统版本和位数一致
  2. Master 和 Slave 数据库的版本要一致
  3. Master 和 Slave 数据库中的数据要一致
  4. Master 开启二进制日志，Master 和 Slave 的 server_id 在局域网内必须唯一

# 一主一从配置

进入 conf 目录中编辑 my.cnf
主：172…18.12.1
从：172…18.12.2

主配置文件：

1
2
3
4
5
6
7
8
9
10
11
12
[client] 
port=3306 
default-character-set=utf8
[mysql] 
default-character-set=utf8
[mysqld]
character_set_server=utf8
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#配置大小写不敏感
lower_case_table_names=1
server_id=1 ###服务器id
log-bin=mysql-bin ###开启日志文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
配置说明:# 
不同步哪些数据库  
binlog-ignore-db = mysql  
binlog-ignore-db = test  
binlog-ignore-db = information_schema  
#只同步哪些数据库，除此之外，其他不同步  
binlog-do-db = order
##设置server_id，一般设置为IP,注意要唯一
server_id=1
##复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db=mysql
##开启二进制日志功能，可以随便取，最好有含义（关键就是这里了）
log-bin=mysql-bin
##为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
##主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
##二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=7
##跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
##如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

授予从机权限

1
2
3
mysql>ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
mysql>flush privileges; #刷新权限

指定 ip

1
2
3
4
mysql>CREATE USER '用户名'@'%' IDENTIFIED BY '密码'; #创建用户
mysql>GRANT REPLICATION SLAVE ON *.* TO '用户名'@'%'; #分配REPLICATION(主从复制)权限
mysql>flush privileges; #刷新权限

从配置文件

1
2
3
4
5
6
7
8
9
10
11
port=3306 
default-character-set=utf8
[mysql] 
default-character-set=utf8
[mysqld]
character_set_server=utf8
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#配置大小写不敏感
lower_case_table_names=1
server_id=2 ###服务器id 
log-bin=mysql-bin ###开启日志文件

连接到主服务器

1
2
show master status;（查询主状态信息）

连接从服务器

同步主服务器配置

1
2
change master to master_host='172.18.12.1',master_user='root',master_password='root',
master_log_file='mysql-bin.000005',master_log_pos=1188;

配置说明

1
2
master_host为主库IP，master_user,master_password为主库用户名和密码，master_log_file和master_log_pos就是上图的这个File和Position

开始同步 start slave;

检查从服务器复制功能状态 SHOW SLAVE STATUS; （Slave_IO_Running 、Slave_SQL_Running 都为 Yes 说明配置成功）

从机挂掉后重启后，可能会复制失败，可用执行 reset slave; 清空然后再 start slave; 重新配置主机时，需要先 stop slave; 再 change master to …

# 双主双从配置

主配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[client] 
port=3306 
default-character-set=utf8
[mysql] 
default-character-set=utf8
[mysqld]
character_set_server=utf8
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#配置大小写不敏感
lower_case_table_names=1
server_id=2 ###服务器id
log-bin=mysql-bin ###开启日志文件
auto_increment_increment=2
auto_increment_offset=2
binlog_format=mixed
expire_logs_days=7
##relay_log配置中继日志
relay_log=edu-mysql-relay-bin
##log_slave_updates表示slave将复制事件写进自己的二进制日志2
log_slave_updates=1
##防止改变数据(除了特殊的线程) 
read_only=1
slave_skip_errors=1062

在两个主机上都执行一次主从复制的步骤即可

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

主从复制语句备份

CREATE TABLE `user` ( 
`user_id` bigint(20) AUTO_INCREMENT,
`username` varchar(30) NOT NULL,
`password` varchar(30) DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

grant replication slave, replication client on *.* to 'root'@'172.18.12.2' identified by 'root';
grant replication slave, replication client on *.* to 'slave'@'172.18.12.3' identified by 'slave';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
flush privileges;
show master status;
change master to 
master_host='172.18.12.2', 
master_port=3306, 
master_user='root',
master_password='root',
master_log_file='mysql-bin.000003',
master_log_pos=588;
start slave;
stop slave;
show slave status;