【mysql】主从同步

概述

mysql主从配置可以大大提高数据库可用性，减少系统压力，是简单版本的读写分离。步骤：主库配置–>主库备份（加锁、记录位置、解锁）–>从库配置–>从库恢复

主库配置

主库配置（开启binlog）
my.cnf

server-id = 1
#开启mysql binlog功能
log-bin=/data/mysql/arch_log/mysql-bin
expire_logs_days=10
max_binlog_size=200M
binlog_do_db=tmp_syn
binlog_ignore_db=mysql
#binlog记录内容的方式，记录被操作的每一行
binlog_format = ROW
#对于binlog_format = ROW模式时，减少记录日志的内容，只记录受影响的列
binlog_row_image = full

备注：my.cnf文件变更需要重启数据库

同步账号配置
create user repl@‘%’ identified by ‘###’;
grant replication slave on . to repl@‘%’ identified by ‘###’;
flush privileges;
show master status;

记录binlog文件名称及位置

主库添加测试数据

初始化一些数据

CREATE TABLE IF NOT EXISTS student
(
    stu_id         INT(10)     NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '学号',
    stu_name       VARCHAR(20) NOT NULL COMMENT '姓名',
    stu_sex        VARCHAR(4) COMMENT '性别',
    stu_birth      YEAR COMMENT '出生年份',
    stu_department VARCHAR(20) NOT NULL COMMENT '院系',
    stu_address    VARCHAR(50) COMMENT '家庭住址'
);

INSERT INTO student(stu_name,stu_sex,stu_birth,stu_department,stu_address)  VALUES
('张老大','男','1985','计算机系','北京市海区'),
('张老二','男','1986','中文系','北京市昌平区'),
('张三','女','1990','中文系','湖南省永州市'),
('李四','男','1990','英语系','辽宁省阜新市'),
('王五','女','1991','英语系','福建省厦门市'),
('王六','男','1988','计算机系','湖南衡阳市')

主库备份

./mysqldump -u root --password=****** tmp_syn –master-data=2 –single-transaction >/data/tmp_syn.sql

全库大数据量备份可用
innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=backup --password=---- --socket=/tmp/mysql.sock /backup/full
其余均一致

从库配置

从库配置
my.cnf

server-id = 2
#开启mysql binlog功能
log-bin=/data/mysql/arch_log/mysql-bin
expire_logs_days=10
max_binlog_size=200M
binlog_do_db=tmp_syn
binlog_ignore_db=mysql
#binlog记录内容的方式，记录被操作的每一行
binlog_format = ROW
#对于binlog_format = ROW模式时，减少记录日志的内容，只记录受影响的列
binlog_row_image = full
#master status and connection information输出到表mysql.slave_master_info中
master_info_repository = TABLE
#the slave‘s position in the relay logs输出到表mysql.slave_relay_log_info中
relay_log_info_repository = TABLE
#作为从库时生效,想进行级联复制，则需要此参数
log_slave_updates
slave_skip_errors=ddl_exist_errors

备注：my.cnf文件变更需要重启数据库

从库手动创建数据库
create database tmp_syn;
恢复数据
(1).mysql -uroot -p
(2)source /data/tmp_syn.sql

创建slave

change master to master_host=‘10.216.70.23’,master_user=‘repl’,master_password=‘—’,master_port=3306,master_log_file=‘mysql-bin.014361’,master_log_pos=154;

注意：
Slave_IO_Runing yes
Slave_SQL_Runing yes
都必须为yes则配置成功

异常可通过跳过处理