mysql 主从复制

复制原理

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

主配置文件：

[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 ###开启日志文件

配置说明:# 
不同步哪些数据库  
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

授予从机权限

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

指定ip

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

从配置文件

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 ###开启日志文件

连接到主服务器

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

连接从服务器

同步主服务器配置

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;

配置说明

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 …

双主双从配置

主配置文件

[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

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

主从复制语句备份

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;