Mysql主从（6月28日）

17.1 MySQL主从介绍
17.2 准备工作
17.3 配置主
17.4 配置从
17.5 测试主从同步

17.1 MySQL主从介绍

MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的。
MySQL主从是基于binlog的，主上须开启binlog才能进行主从。主上有一个log dump线程，用来和从的I/O线程传递binlog。
从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog（中继日志），另外一个SQL线程用来把relaylog里面的sql语句顺序执行一次。

  • 主从工作流程
    • 1.主将更改操作记录到binlog里
    • 2.主上的log dump现成和从I/O线程通讯，并将主的binlog事件(sql语句)同步到从从机上并记录在relaylog里
    • 3.从上让SQL线程根据relaylog里面的sql语句按顺序执行

  • 在日常生产环境中使用MySQL主从分以下两种情况
    • 1.数据备份。读写都是主机器，平时只是作为备份看待，从机器在主机器宕机时候顶上。
    • 2.读写分离。主机器来写数据，从机器来读数据，可以减轻主机器上负载。

17.2 准备工作

准备两台机器，并在两台机器上安装上MySQL

步骤

1.下载二进制包到src目录下

1
2
[root@localhost ~]# cd /usr/local/src/
[root@localhost src]# wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz

2.解压包

1
[root@localhost src]# tar -zxvf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz

3.把内容移动到/usr/local/下并改名为mysql

1
[root@localhost src]# mv mysql-5.6.36-linux-glibc2.5-x86_64 /usr/local/mysql

4.创建mysql用户和datadir目录

1
2
3
[root@localhost mysql]# useradd -s /sbin/nologin mysql
[root@localhost mysql]# mkdir /data
[root@localhost mysql]# chown -R mysql:mysql /data/

5.初始化MySQL 指定用户和数据库目录

1
[root@localhost mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data

6.编辑mysql配置文件

1
2
3
4
[root@localhost mysql]# vim /etc/my.cnf

datadir=/data
socket=/tmp/mysql.sock

7.把模版启动脚本拷贝到/etc/init.d下并修改参数

1
2
3
4
5
[root@localhost mysql]# cp support-files/mysql.server  /etc/init.d/mysqld
[root@localhost mysql]# vim /etc/init.d/mysqld

basedir=/usr/local/mysql
datadir=/data

8.启动MySQL服务

1
2
3
[root@localhost mysql]# chkconfig --add mysqld
[root@localhost mysql]# chkconfig mysqld on
[root@localhost mysql]# chkconfig mysqld on

17.3 配置主

我们这里以192.168.80.104为主 192.168.80.105为从 现在在192.168.80.104上配置

步骤

1.修改MySQL配置文件my.cnf

1
[root@localhost ~]# vim /etc/my.cnf

添加下面参数

1
2
server-id=1
log_bin=test

server-id 主机器和从机器必须要不一致 log_bin定义binlog的前缀，也可以说是binlog名称

2.重启MySQL服务

1
[root@localhost ~]# service mysqld restart

为了让实验更加接近生产环境，我们在主上新建测试库，这里以kun库为例子

1
2
3
4
5
[root@localhost ~]# mysqldump -uroot -p38003800 mysql > /tmp/mysql.bak  ##把mysq内容备份一次

[root@localhost ~]# mysql -uroot -p38003800 -e "create database kun"   ##创建kun库

[root@localhost ~]# mysql -uroot -p38003800 kun < /tmp/mysql.bak   ##把mysql内容拷贝到kun中

那么现在主上就多了一个kun的库了

3.数据库上创建用于同步数据的用户

1
mysql> grant replication slave on *.* to 'bak'@192.168.80.105 identified by '38003800';

replication slave是用于复制的权限 'bak'@192.168.80.105是指定从机器上登录的bak用户

4.锁表

1
mysql> flush table with read lock;

5.查看主的binlog

1
2
3
4
5
6
7
mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| test.000001 |   655999 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

File 为binlog的名称 Position为binlog的位置

17.4 配置从

现在配置192.168.80.105从机器

步骤

1.修改MySQL配置文件my.cnf

1
2
3
[root@localhost ~]# vim /etc/my.cnf

server-id=2

从上只需要配置server-id即可 记得和主机器不一致

2.重启MySQL服务

1
[root@localhost ~]# service mysqld restart

3.把主上的数据复制到从上 让数据保持一致

1
2
3
[root@localhost ~]# scp 192.168.80.104:/tmp/mysql.bak /tmp/
root@192.168.80.104's password:
mysql.bak                                                                      100%  640KB  17.5MB/s   00:00

scp命令用于Linux之间复制文件和目录。

1
2
3
mysql> create database kun;   ##在从上的MySQL上创建kun库

[root@localhost ~]# mysql -uroot -p38003800 kun < /tmp/mysql.bak   ##把mysql内容拷贝到kun库里面来

现在主和从上的数据已经是一致了

4.先确保暂停从的复制

1
2
3
[root@localhost ~]# mysql -uroot -p38003800

 mysql> stop slave;

5.更改主从连接参数

1
mysql> change master to master_host='192.168.80.104',master_user='bak',master_password='38003800',master_log_file='test.000001',master_log_pos=655999;

master_host主机器IP master_user主机器上用户主从连接的用户 master_log_file主机器binlog的名称 master_log_pos主机器binlog的位置

6.启动从的复制

1
mysql> start slave;

7.回去主机器上开锁

1
mysql> unlock table;

8.查看从的状态 判断主从是否配置成功

1
mysql> show slave status\G

查看

1
2
 Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Slave_IO_Running表示I/O进程状态 用于和主机器通讯 出现Connecting 表示正在连接 可能是主机器上防火墙给阻断了
Slave_SQL_Running表示SQL进程状态

还需要查看

1
2
3
4
5
Seconds_Behind_Master: 0  表示为主从延迟的时间
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

除了在配置文件上使用的参数外，还有几个常用的参数

主机器配置参数

参数 含义
binlog-do-db= 表示仅同步指定的库 多个用,分开
binlog-ignore-db= 表示忽略指定库 多个用,分开

从机器配置参数

参数 含义
replicate_do_db= 表示仅同步指定的库 多个用,分开
replicate_ignore_db= 表示忽略指定库 多个用,分开
replicate_do_table= 表示仅同步指定的表 多个用,分开 一般不用
replicate_ignore_table= 表示忽略指定表 多个用,分开 一般不用
replicate_wild_do_table= 表示仅同步指定的表 支持通配符% 如aming.% 指定每个库下的所有表
replicate_wild_ignore_table= 表示忽略指定表 支持通配符%

17.5 测试主从同步

主上进入数据库 查看kun库中的db表的行数

1
2
3
4
5
6
7
8
9
10
[root@localhost ~]# mysql -uroot -p38003800
mysql> use kun;

mysql> select count(*) from db;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

从上也看看db表的行数

1
2
3
4
5
6
7
8
9
10
[root@localhost ~]# mysql -uroot -p38003800
mysql> use kun;

mysql> select count(*) from db;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

主和从上的行数量一致

再回去主上清除db表的内容

1
2
3
4
5
6
7
8
9
mysql> truncate table db;

mysql> select count(*) from db;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

此时回去从机器上看看db表的行数

1
2
3
4
5
6
7
mysql> select count(*) from db;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

从上的行数和主上一致都是显示0行

主从不一致

这里我在从上把db库删除了 会导致主从不一致。

1
2
3
4
5
6
mysql> drop table db;

mysql> show slave status\G 

             Slave_IO_Running: Yes
            Slave_SQL_Running: No

步骤

1.停止从的复制

1
mysql> stop slave;

2.保证从和主的数据一致 这里把主上的db库删除即可

1
mysql> drop table db;

3.查看主的binlog状态并在从上更改主从连接参数

1
2
3
4
5
6
7
mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| test.000001 |   656405 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1
mysql> change master to master_host='192.168.80.104',master_user='bak',master_password='38003800',master_log_file='test.000001',master_log_pos=656405;

4.启动从的复制

1
mysql> start slave;

5.查看主从是否成功

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.80.104
                  Master_User: bak
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: test.000001
          Read_Master_Log_Pos: 656405
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 278
        Relay_Master_Log_File: test.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

扫一扫，分享到微信

微信分享二维码

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

欢迎光临<br><br>本博客是用来记录学习Linux Python Shell和Perl<br>谢谢大家