文档章节

MySQL主从介绍&准备工作&配置主&配置从&测试主从同步

影夜Linux
 影夜Linux
发布于 2018/07/06 00:55
字数 2888
阅读 103
收藏 25

17.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主从的原理图

其中Msater是主,Slave是从

MySQL主从的作用

  • 数据备份,主机器宕机,从机器还能随时对web提供服务
  • 从库可以作为网站的读取数据库,从而减轻主库的压力。

注:mysql主从,是有方向性的,写数据,必须从主机器开始;如果不依照原理会导致数据紊乱

17.2 准备工作

MySQL主从准备工作的内容

准备两台机器,每台机器安装msyql服务,并启动mysql服务

MySQL服务安装流程

1.下载安装包(二进制免编译)-->2.解压压缩包-->3.初始化-->4.修改配置文件-->5.复制启动脚本-->6.修改启动脚本-->7.启动mysql-->8.开机启动mysql

下载安装包(二进制免编译)

首先下载二进制免编译的包,下载到/usr/local/src/目录下

解压压缩包

解压完之后,把解压出来的目录放到 /usr/local/mysql/ 目录下

注:首先检查 /usr/local/mysql/ 目录是否存在,若是这个目录存在,首先把这个目录改个名字,或者把目录下的内容删除,然后把解压出来的目录放到 /usr/local/mysql/ 目录下面

初始化

cd /usr/local/mysql/
./scripts/mysql_install_db --user=mysql --datadir=/data/mysql

注:其中的--user=mysql 需要提前创建mysql用户;初始化成功的标志就是两个OK,或者用 echo $? 检查是否初始化成功

修改配置文件

vim /etc/my.cnf
定义 datadir=/data/mysql     
定义 socket=/tmp/mysql.sock

复制启动脚本

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

修改启动脚本

vim /etc/init.d/mysqld //对以下两行进行指定路径

指定basedir的路径 /usr/local/mysql  
指定datadir的路径 /data/mysql

启动mysql

/etc/init.d/mysql start

如果启动失败,可以去查看错误日志;

查看 /data/mysql 目录下的文件,默认属主、属组,如果不是mysql的,启动时会因无法写入数据而不能启动mysql,需要改变属主和属组

chomd mysql:mysql /data/mysql

开机启动mysql

chkconfig mysqld on

详细配置

MySQL的安装

17.3 配置主

修改配置文件

vim /etc/my.cnf
增加server-id=5和log_bin=lemlinux1

重启MySQL服务

[root@linux-5 ~]# /etc/init.d/mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

查看数据库文件

[root@linux-5 ~]# cd /data/mysql/
[root@linux-5 mysql]# ls -lt
总用量 110676
-rw-rw----. 1 mysql mysql 50331648 7月   5 23:59 ib_logfile0
-rw-rw----. 1 mysql mysql 12582912 7月   5 23:59 ibdata1
-rw-rw----. 1 mysql mysql    54024 7月   5 23:59 linux-5.err
-rw-rw----. 1 mysql mysql        5 7月   5 23:59 linux-5.pid
-rw-rw----. 1 mysql mysql       19 7月   5 23:59 lemlinux1.index
-rw-rw----. 1 mysql mysql      120 7月   5 23:59 lemlinux1.000001
drwx------. 2 mysql mysql     4096 7月   4 23:34 zrlog
-rw-rw----. 1 mysql mysql       56 5月  24 19:58 auto.cnf
drwx------. 2 mysql mysql     4096 5月  24 18:35 mysql
drwx------. 2 mysql mysql     4096 5月  24 18:35 performance_schema
-rw-rw----. 1 mysql mysql 50331648 5月  24 18:35 ib_logfile1
drwx------. 2 mysql mysql        6 5月  24 18:35 test

其中.index;.000001这些文件是实现MySQL主从的根本

.index 索引页,这个文件是必须要有的

.000001 这个是二进制日志文件,会持续生成2、3、4等等(这个文件是实现主从配置的根本,没有这个文件根本没有办法完成主从)

准备测试数据

把zrlog库备份并恢复成lem库,作为测试数据

mysqldump -uroot zrlog > /tmp/lem.sql   //zrlog数据库备份
mysql -uroot -e "create database lem"   //创建新库lem
mysql -uroot lem < /tmp/lem.sql         //将zrlog数据库备份数据导入lem库中

再次查看数据库文件

[root@linux-5 mysql]# ls -lt
总用量 110688
-rw-rw----. 1 mysql mysql 50331648 7月   6 00:09 ib_logfile0
-rw-rw----. 1 mysql mysql 12582912 7月   6 00:09 ibdata1
-rw-rw----. 1 mysql mysql    10152 7月   6 00:08 lemlinux1.000001
drwx------. 2 mysql mysql     4096 7月   6 00:08 lem
-rw-rw----. 1 mysql mysql    54024 7月   5 23:59 linux-5.err
-rw-rw----. 1 mysql mysql        5 7月   5 23:59 linux-5.pid
-rw-rw----. 1 mysql mysql       19 7月   5 23:59 lemlinux1.index
drwx------. 2 mysql mysql     4096 7月   4 23:34 zrlog
-rw-rw----. 1 mysql mysql       56 5月  24 19:58 auto.cnf
drwx------. 2 mysql mysql     4096 5月  24 18:35 mysql
drwx------. 2 mysql mysql     4096 5月  24 18:35 performance_schema
-rw-rw----. 1 mysql mysql 50331648 5月  24 18:35 ib_logfile1
drwx------. 2 mysql mysql        6 5月  24 18:35 test

可以看到lemlinux1.000001二进制文件是有增加的,lemlinux1.000001增长的大小是和zrlog这个数据库备份的大小保持一致的,lemlinux1.000001文件里完整的记录了数据库的创建的库,创建的表,以及表里的内容,通过完整的lemlinux1.000001文件也可以恢复数据库的数据

创建用作同步数据的用户

在MySQL主从中,从数据库需要到主数据库上同步日志,这个过程需要认证,因此我们需要在主库上创建一个用户用于从库登录同步日志。

grant replication slave on *.* to 'repl'@192.168.88.10(从库IP) identified by '123456';

锁定数据库表

锁定表,目的是不让表继续写,因为一会需要做从机器配置,需要进行一个同步,让两台机器同步,保证两台机器的数据一致,同步才不会出错

flush tables with read lock;

查看binlog文件

查看一下binlog的文件和大小,并记住binlog的filename


mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| lemlinux1.000001 |    10362 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

数据同步

查看/data/mysql/下有哪些库,主上有哪些库,从上也得有哪些库,同步这些库,就意味着这些数据都要备份过去

[root@linux-5 mysql]# ls /data/mysql/
auto.cnf  ib_logfile0  lem               lemlinux1.index  linux-5.pid  performance_schema  zrlog
ibdata1   ib_logfile1  lemlinux1.000001  linux-5.err      mysql        test

备份数据库,除了mysql库,因为mysql库里面有账号密码,从上备份的时候不可能把所有权限复制过去,所以mysql不需要备份

mysqldump -uroot test > /tmp/lem2.sql

在主从同步时把主上/tmp/目录下 .sql文件都拷贝到从上去

17.4 配置从

修改从库的配置文件

vim /etc/server
增加server-id = 10

从库不需要生成二进制日志文件,只有主库需要

重启数据库

[root@linux-10 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

拷贝主数据库的备份文件

[root@linux-10 ~]# scp 192.168.88.5:/tmp/*.sql /tmp/
Warning: Permanently added '192.168.88.5' (ECDSA) to the list of known hosts.
root@192.168.88.5's password: 
lem2.sql                                        100% 1258     1.0MB/s   00:00    
lem.sql                                         100% 9867     4.3MB/s   00:00

创建与主库对应的数据库

mysql> create database lem;
Query OK, 1 row affected (0.00 sec)

mysql> create database zrlog;
Query OK, 1 row affected (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

数据恢复

root@linux-10 ~]# mysql -uroot -p12345678 zrlog < /tmp/lem.sql
Warning: Using a password on the command line interface can be insecure.
[root@linux-10 ~]# mysql -uroot -p12345678 lem < /tmp/lem.sql
Warning: Using a password on the command line interface can be insecure.
[root@linux-10 ~]# mysql -uroot -p12345678 test < /tmp/lem2.sql
Warning: Using a password on the command line interface can be insecure.

实现主从

stop slave;   //停止同步
change master to master_host='192.168.88.5', master_user='repl', master_password='123456', master_log_file='lemlinux1.000001', master_log_pos=10362;

master_host='192.168.88.5',指定主机器host

master_user='repl',指定主机器用户

master_password='123456',指定主机器密码

master_log_file='lemlinux1.000001',指定binlog文件名

master_log_pos=10362,指定binlog文件大小

master_log_file和master_log_pos是由主库show master status

也可以指定主机器的port,因为在生产环境中,也会有人更改mysql的默认端口 master_port=端口号,如果是默认端口则无需添加

开启同步

start slave;

查看主从是否成功

mysql> show slave status\G             //\G后不用加分号,本身就是结束符
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.88.5
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: lemlinux1.000003
          Read_Master_Log_Pos: 120
               Relay_Log_File: linux-10-relay-bin.000005
                Relay_Log_Pos: 283
        Relay_Master_Log_File: lemlinux1.000003
             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:

成功的标志是 Slave_IO_Running和Slave_SQL_Running两项是否均为yes

还需要关注

Seconds_Behind_Master: 0  //为主从延迟的时间

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

解锁主数据库表

unlock tables;

17.5 测试主从同步

主从同步的配置参数

主从中有一些配置参数可以使数据库同步时更加精确,具有目的性。

主服务器

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=   //如aming.%, 支持通配符%  指定同步靠谱的匹配  忽略表

使用前4条参数时会有一定的局限性,例如:有一个临时表,写的数据非常快,数据也大,每天都需要删除或者更新,那么就不需要每天去做同步,并且在面对联合查找时,可能会造成数据不完整的情况,因此在设置主从配置参数时,尽量使用后两条。

测试主从

清除主库中测试库user表中数据

mysql> use lem
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_lem |
+---------------+
| comment       |
| link          |
| log           |
| lognav        |
| plugin        |
| tag           |
| type          |
| user          |
| website       |
+---------------+
9 rows in set (0.00 sec)

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

mysql> select * from website;
+--------+---------------------+----------------------------------------------------------------------+--------+
| siteId | name                | value                                                                | remark |
+--------+---------------------+----------------------------------------------------------------------+--------+
|      1 | rows                | 10                                                                   | NULL   |
|      2 | template            | /include/templates/default                                           | NULL   |
|      3 | autoUpgradeVersion  | 86400                                                                | NULL   |
|      4 | pseudo_staticStatus | false                                                                | NULL   |
|      5 | title               | lemzrlog                                                             | NULL   |
|      6 | second_title        | lem                                                                  | NULL   |
|      7 | home                | http://192.168.88.5/zrlog/                                           | NULL   |
|      8 | zrlogSqlVersion     | 4                                                                    | NULL   |
|      9 | plugin_core_db_key  | {"pluginInfoMap":{},"setting":{"disableAutoDownloadLostFile":false}} | NULL   |
+--------+---------------------+----------------------------------------------------------------------+--------+
9 rows in set (0.00 sec)

mysql> truncate table website;
Query OK, 0 rows affected (0.01 sec)
mysql> select count(*) from website;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

查看从库

mysql> use lem
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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

从库的数据也被删除,说明主从配置成功

若是误操作了,比如在从机器误删除了,再去主上删除相同的数据,就会有可能导致主从失败

这时在从机器上 start slave;

然后在start slave;

再来查看show slave status\G

若是还是失败,则只能重新做主从了

重新主从

在主机器的数据库上 show mater status; 查看文件大小

然后在从机器上先stop slave;

然后直接change master to master_host='192.168.88.5', master_user='repl', master_password='123456', master_log_file='lemlinux1.000001', master_log_pos=10362;

因为基本还没做什么操作的,数据还是一致的,直接改下数据大小就行

然后在从机器上 start slave;

再来查看 show slave status\G 看是否为两个Yes,若还是报错只能重新从头做主从了

© 著作权归作者所有

影夜Linux
粉丝 4
博文 76
码字总数 127168
作品 0
杭州
运维
私信 提问
加载中

评论(2)

影夜Linux
影夜Linux 博主

引用来自“20PursueZ”的评论

没写完?
昨天差一点内容,今天写全了
2
20PursueZ
没写完?
17.1 MySQL主从介绍 17.2 准备工作 17.3 配置主 17.4 配置从 17.5 测试主从同步

17.1 MySQL主从介绍 17.2 准备工作 主和从上分别安装mysql, 数据库版本最好一致. 17.3 配置主 17.4 配置从 17.5 测试主从同步

夜归人8421
2018/04/01
32
0
Mysql主从复制

Mysql主从复制 背景: Mysql可以实现主从复制,在学习了Mysql主从复制后,将一些如何主从复制过程记录下来,供以后复习使用。 准备: 在做Mysql的主从复制前需要做一些准备工作: 1、同步时间...

657188918
2017/11/13
0
0
2018-4-2 15周1次课 MySQL主从配置

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

alexis7gunner
2018/04/02
0
0
安装Mysql服务器,一主一从(主的写,从的读)

准备工作 准备两台机器,每台机器都安装msyql,并启动mysql服务; 安装步骤可参考:mysql安装 如果mysql启动失败,可以去查看错误日志,会发现 /data/mysql 目录下的文件的属主属组不是mysql...

Zhouliang6
2018/07/30
38
1
2018-4-2 Linux学习笔记

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

XiaoluHuang
01/07
0
0

没有更多内容

加载失败,请刷新页面

加载更多

SSH安全加强两步走

从 OpenSSH 6.2 开始已经支持 SSH 多因素认证,本文就来讲讲如何在 OpenSSH 下启用该特性。 OpenSSH 6.2 以后的版本多了一个配置项 AuthenticationMethods。该配置项可以让 OpenSSH 同时指定...

xiangyunyan
39分钟前
4
0
C或C++不是C/C++

http://www.voidcn.com/article/p-mucdruqa-ws.html

shzwork
今天
6
0
OSChina 周六乱弹 —— 如何将梳子卖给和尚

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 @for_ :划水五分钟,专注两小时。分享Various Artists的单曲《贝多芬第8号钢琴奏鸣曲悲伤的第三乐章》: 《贝多芬第8号钢琴奏鸣曲悲伤的第三乐...

小小编辑
今天
305
8
ES5

什么是ES5:比普通js运行要求更加严格的模式 为什么:js语言本身有很多广受诟病的缺陷 如何:在当前作用域的顶部添加:"use strict" 要求: 1、禁止给未声明的变量赋值 2、静默失败升级为错误...

wytao1995
今天
7
0
c++ 内联函数调用快的原因

见图片分析

天王盖地虎626
今天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部