MySQL主从配置

原创
2018/06/29 10:01
阅读数 79

1. MySQL主从介绍:

MySQL主从介绍:

MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后,在A上写数据,另外一台B也会跟着写数据,两者数据实时同步的

MySQL主从是基于binlog的,主上须开启binlog才能进行主从。

主从过程大致有3个步骤:

1)主将更改操作记录到binlog里

2)从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里

3)从根据relaylog里面的sql语句按顺序执行

主上有一个log dump线程,用来和从的I/O线程传递binlog

从上有两个线程,其中I/O线程用来同步主的binlog并生成relaylog,另外一个SQL线程用来把relaylog里面的sql语句落地

mysql主从原理:

 

应用场景:

  • 只做备份使用,不进行读操作;
  • 当主的压力比较大是,从也做读的操作;
  • 从不能有写的操作,因为数据的同步是从主到从写入;

2. 配置主:

准备工作:

  • 两台机器都安装好mysql;
  • 一台机器作为主,一台作为从;

主mysql上配置:

修改my.cnf,增加server-id=64和log_bin=learnlinux //server-id he log_bin 可以自定义。

另外还有两个参数可以选择性地使用:

  • binlog-do-db=databasename1,databasename2
  • binlog-ignore-db=databasename1,databasename2

其中,binlog-do-db=定义需要复制到数据库,多个数据库之间用英文逗号分隔,binlog-ignore-db=定义不需要复制的数据库,这两个参数用其中一个即可。

设置log_bin=learnlinux后会在/data/mysql下生成learnlinux开头的文件:

修改完配置文件后,启动或者重启mysqld服务

/etc/init.d/mysqld restart

把mysql库备份并恢复成gavin库,作为测试数据:

mysqldump -uroot -pgavin mysql >/tmp/mysql.sql

mysql -uroot -pgavin -e "create database gavin"

mysql -uroot gavin < /tmp/mysql.sql

创建用作同步数据的用户:

grant replication slave on *.* to 'repl'@slave_ip identified by 'password';

  • 这里的replication slave为用户的权限,repl是为从(slave)端设置的访问主(master)端的用户,也就是要完成主从复制的用户
  • replication slave常用于建立复制时所需要用到的用户权限,也就是slave server必须被master server授权具有该权限的用户,才能通过该用户复制。并且"show slave hosts"这条命令和replication slave权限有关,否则执行时会报错:ERROR 1227 (42000): Access denied; you need (at least one of) the REPLICATION SLAVE privilege(s) for this operation

flush tables with read lock;

  • 该操作将锁定数据库写操作(暂锁定数据库写操作,保证数据同步的一致性)

show master status;

  • 查看master的状态,这些数据是要记录的,一会儿要在slave端用到

备份数据库:

cd /data/mysql

  • 切换到mysql数据存放目录

ls

  • 查看目录文件
  • 其中,蓝色的目录表示mysql的库;

备份现有的库,由于mysql库中存有用户的权限等信息,暂不备份复制到从上

  • mysqldump -uroot -pgavin gavin > /tmp/gavin.sql
  • mysqldump -uroot -pgavin zrlog > /tmp/zrlog.sql

后续会把备份的数据库文件传到slave上;

3. 配置从:

查看my.cnf,配置server-id=132,server-id和主不一样

  • vim /etc/my.cnf //修改server-id=132

修改完配置文件后,启动或者重启mysqld服务:

  • /etc/init.d/mysqld restart

把主上gavin、zrlog库同步到从上:

  • 可以先创建gavin、zrlog库
  • 然后把主上的/tmp/gavin.sql和/tmp/zrlog.sql拷贝到从上,然后导入对应的库

slave上配置:

mysql -uroot -pgavin

  • 进入mysql

stop slave;

  • 先暂停slave

change master to master_host='', master_user='repl', master_password='', master_log_file='', master_log_pos=xx,

  • change master这个命令打完逗号后也可以按回车,直到你打分号才算结束。
  • master_host 表示主的ip
  • master_user 主上创建的用来同步的用户
  • master_password 用来同步账户的密码
  • master_log_file 是master上show master status; 查看到的File值
  • master_log_pos 是master上show master status; 查看到的Position值

start slave;

  • 启动slave

还要到主上执行:

unlock tables

  • 解除数据库写的锁定

查看主从配置是否成功:

在slave端查看slave状态

show slave status\G

确认以下两项参数都为yes,如下所示:

  • Slave_IO_Running: Yes
  • Slave_SQL_Running: Yes

还需要关注的地方有:

  • Seconds_Behind_Master: 0 //为主从复制延迟的时间
  • Master_SSL_Verify_Server_Cert: No
  • Last_IO_Errno: 0
  • Last_IO_Error:
  • Last_SQL_Errno: 0
  • Last_SQL_Error:

如果主从不正常了需要看这里的error信息。

4. 测试主从同步:

my.cnf中几个配置参数:

主服务器上:

binlog-do-db=      //仅同步指定的库

binlog-ignore-db= //忽略指定库

从服务器上:

  • replicate_do_db= //指定只同步的库
  • replicate_ignore_db= //忽略同步指定库
  • replicate_do_table= //同步指定的表
  • replicate_ignore_table= //忽略同步指定的表

以上几个存在一个问题,即sql语句中有配合查询时,只要有这些表就不执行了;一般仅用以下两种就可以了:

  • replicate_wild_do_table=   //针对指定的表都同步,如aming.%, 支持通配符%
  • replicate_wild_ignore_table= //只针对指定的表忽略同步

测试主从:

主上操作和从上步骤对比

  1. mysql -uroot gavin -p
  2. use gavin;
  3. show tables;
  4. select count(*) from user;
  5. truncate table user;

到从上操作和主上步骤对比

  1. mysql -uroot gavin -p
  2. use gavin;
  3. show tables;
  4. select count(*) from user;
  5. select count(*) from user;

主上继续drop table user;

从上查看user表

主从报错时:

  • slave上不要执行增、删、修改操作,会引起同步问题
  • 同步出错时可以stop slave;start slave 看看是否会恢复正常。
  • 如恢复不了,重新做下主从reset slave命令重置slave设置。它是直接删除master.info和relay-log.info文件,并删除所有的relay log,然后重新生成一个新的relay log,即使relay log中还有SQL没有被SQL线程apply完。

 

 

展开阅读全文
打赏
0
1 收藏
分享
加载中
更多评论
打赏
0 评论
1 收藏
0
分享
返回顶部
顶部