MySQL主从介绍、准备工作、 配置主、 配置从、 测试主从同步
MySQL主从介绍、准备工作、 配置主、 配置从、 测试主从同步
Linux_Anna 发表于5个月前
MySQL主从介绍、准备工作、 配置主、 配置从、 测试主从同步
  • 发表于 5个月前
  • 阅读 10
  • 收藏 0
  • 点赞 0
  • 评论 0

【腾讯云】如何购买服务器最划算?>>>   

 MySQL主从介绍

●MySQL主从官方的叫法叫做Replication、或者叫做AB复制. 简单讲就是A和B两台机器做主从后,在A上写数据,另一台机器B也会跟着写数据,两者数据实时同步. 比如说A机器对某个表插入了一行数据,那么B机器同样也会对那个表插入一行数据.

●MySQL主从是基于binlog的,关于binlog实际上就一个文件,这个文件里记录一些日志,但这个文件是不能cat的,因为是二进制的,所以叫做binlog.
主上须开启binlog才能进行主从,刚也说到MySQL主从基于binlog,所以不开启的话,怎么能同步呢?

●主从过程有三个步骤: 1)主将更改操作记录到binlog里,ps:例如针对数据库的某个表插入一行,或者更改等,这些操作都会记录到binlog里 2)从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里(也就是从自己的日志),中文叫做中记日志. 3)从根据relaylog里面的SQL语句按顺序执行.

●在主从有三个线程,主上有一个log dump线程,用来和从的l/O线程传递binlog
从上有两个线程,其中l/O线程用来同步主的binlog并生成relaylog,另外一个SQL线程用来把relaylog里面的SQL语句执行一遍

 

MySQL主从原理图

说明:

Master是主
Slave是从

Master写操作都会记录到binlog里面,Master上log dump thread 会和 Slave上的I/O 两个线程进行相互通信.

Mater把信息读到Slave上,然后Slave上生成了relaylog.

Slave上还会有SQL的线程SQL thread, SQL thread用来把relaylog的SQL语句做一个执行.

原理很简单,就是主会把Binlog搞到从上来,从再根据binlog生成自己的一份中记日志(relaylog),然后再根据中记日志去执行相应的更改,最终达到两边数据一致.

 

主从用在什么场景下:
第1种场景:做数据的备份.
数据这么重要,在主上写了一份数据,那要再存一份数据,那我可能只是针对一台主去做读写操作,而从呢,备份的数据就单纯的备份)例如主上机器电源损坏,突然当掉,那么可以随时把从这个机器启动起来,给web或一些客户端提供服务. 另外一种场景同样也是做个备份,但是有一个功能,不仅仅能备份,web这些客户端要去从上读数据,正常情况下写数据要写在主上,读的话也从主上去读. 但现在是主压力比较大,就想在从上做一个读,那么web服务器就可以在从上读取数据,但是不能在从上去写,因为这个主从是有一个方向性的,数据一定是在主上写的,如在从上写的,那么顺序就会混乱,这是主从的一个特性.

 

 准备工作

主从配置--主上操作

192.168.2.179-01 主
192.168.2.180-02 从

主和从安装MySQL,因为主和从都已经安装好MySQL,所以不做演示.

启动MySQL

[root@root-01 data]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS! 


[root@root-02 ~]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS! 

 

 

 配置主 

第1步:修改配置文件

说明:在/etc/my.cnf配置文件,增加server_id=179  log_bin=root-01
           server_id--随意定   log_bin--开启binlog

[root@root-01 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysqld
socket=/tmp/mysql.sock
server_id=179
log_bin=root-01

 

 修改配置文件后,需要重启MySQL

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

#重启之后,/data/mysqld目录下生成了几个文件
[root@root-01 ~]# cd /data/mysqld/

[root@root-01 mysqld]# ls -lt
总用量 110644
-rw-rw----. 1 mysql mysql 50331648 8月  28 15:18 ib_logfile0
-rw-rw----. 1 mysql mysql 12582912 8月  28 15:18 ibdata1
-rw-rw----. 1 mysql mysql    26199 8月  28 15:18 root-01.err
-rw-rw----. 1 mysql mysql        6 8月  28 15:18 root-01.pid
-rw-rw----. 1 mysql mysql       17 8月  28 15:18 root-01.index
-rw-rw----. 1 mysql mysql      120 8月  28 15:18 root-01.000001

drwx------. 2 mysql mysql       45 8月  27 21:55 bakup
-rw-rw----. 1 mysql mysql       56 8月   9 13:45 auto.cnf
drwx------. 2 mysql mysql     4096 8月   9 12:27 mysql
drwx------. 2 mysql mysql     4096 8月   9 12:27 performance_schema
-rw-rw----. 1 mysql mysql 50331648 8月   9 12:27 ib_logfile1
drwx------. 2 mysql mysql        6 8月   9 12:01 test

注:root-01.index 文件索引页,是必须要有的.
       root-01.000001 文件非常重要,是实现主从的根本,若没有这个文件是无法实现主从的.


第2步:把Mysql数据库备份并恢复成Anna库,作为测试数据

#Mysql数据库备份
[root@root-01 ~]# mysqldump -uroot mysql > /tmp/mysql.sql

#创建Anna库
[root@root-01 mysqld]# mysql -uroot -e "create database Anna"

#恢复数据
[root@root-01 ~]# mysql -uroot Anna < /tmp/mysql.sql

 

第3步:创建用户作为同步数据的用户

说明:这个用户是主从同步相互用的
         从要去主上同步日志,那么需要有一个认证的,要认证肯定需要:用户,用户的权限,用户密码,所            以都需要去创建.

[root@root-01 mysqld]# mysql -uroot

mysql> grant replication slave on *.* to 'repl'@'192.168.2.180' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
 

注: 权限:replication slave
        用户:repl
        指定从的IP

 

第4步:锁表

说明:锁表目的是为了让数据不再写
           也就是说主上的数据暂停,因为一会从要把备份的数据同步一下,他们两者的数据保持一致,这样的            话才能实现同步

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

 

第5步: show master status

说明:这步的目的是为了查看Position 和 File name 

mysql> show master status;
+----------------+----------+----------------+------------------+----------------------+
| File                   | Position   | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+----------------+------------------+-----------------------+
| root-01.000001 |   653506 |                        |                            |                                    |
+----------------+----------+----------------+------------------+------------------------+
1 row in set (0.00 sec)


第6步:把/data/mysqld目录下的文件和目录备份一下

[root@root-01 mysqld]# ls
Anna      bakup    ib_logfile0  mysql               root-01.000001  root-01.index  test
auto.cnf  ibdata1  ib_logfile1  performance_schema  root-01.err     root-01.pid

[root@root-01 mysqld]# mysqldump -uroot test > /tmp/test.sql
[root@root-01 mysqld]# mysqldump -uroot bakup > /tmp/bakup.sql

 

配置从 

 

第1步:修改配置文件

说明:在/etc/my.cnf配置文件,增加server_id=180,注意server_id不能和主的server_id一样

[root@root-02 ~]# vim /etc/my.cnf

[mysqld]
#skip-grant
datadir=/data/mysqld
socket=/tmp/mysql.sock
server_id=180

 

#修改配置文件后,需要重启mysql服务
[root@root-02 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL...... SUCCESS! 

 

第2步:把主上/tmp/目录下.sql的目录拷贝到从上来

[root@root-02 ~]# scp 192.168.2.179:/tmp/*.sql /tmp/
root@192.168.2.179's password: 
bakup.sql                                                                                         100% 1834     1.8KB/s   00:00    
mysql.sql                                                                                          100%  637KB 637.3KB/s   00:00    
test.sql                                                                                              100% 1258     1.2KB/s   00:00   

 

第3步:登录mysql, 创建Anna库 和 bakup

说明:mysql 和 test库因为已经有,不能再创建.

[root@root-02 ~]# mysql -uroot -p123456

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

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

 

第4步:数据恢复

[root@root-02 ~]# mysql -uroot -p123456 bakup < /tmp/bakup.sql 
Warning: Using a password on the command line interface can be insecure.

[root@root-02 ~]# mysql -uroot -p123456 Anna < /tmp/mysql.sql 
Warning: Using a password on the command line interface can be insecure. 

第5步:检查主从数据是否一致

#主
[root@root-01 ~]# ls /data/mysqld/
Anna      bakup    ib_logfile0  mysql               root-01.000001  root-01.index  test
auto.cnf  ibdata1  ib_logfile1  performance_schema  root-01.err     root-01.pid

#从
root@root-02 ~]# ls /data/mysqld
Anna  auto.cnf  bakup  ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  root-02.err  root-02.pid  test

 

第6步:登录mysql, 停止slave

[root@root-02 ~]# mysql -uroot -p123456

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

 第7步:change master

mysql>change master to master_host='192.168.2.179',
master_user='repl', master_password='123456',
master_log_file='root-01.000001', master_log_pos=653506;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
 

说明:这一步是实现主从最关键的一步;
           master_host  -- 主的IP
           master_user  -- 主的用户
           master_password --主用户的密码
           master_log_file -- show master status 查看到的
           master_log_pos -- show master status 查看到的

 

第8步:开启slave

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
 

 

第9步:判定主从配置是否成功

说明:使用 show slave status\G 命令查看,如有以下这两个Yes,说明配置成功.
          Slave_IO_Running: Yes 
          Slave_SQL_Running: Yes

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.179
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: root-01.000001
          Read_Master_Log_Pos: 653506
               Relay_Log_File: root-02-relay-bin.000002
                Relay_Log_Pos: 281
        Relay_Master_Log_File: root-01.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 653506
              Relay_Log_Space: 456
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 179
                  Master_UUID: f44b92a0-7cc5-11e7-85d1-000c29a00f93
             Master_Info_File: /data/mysqld/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

 

第10步:去上恢复表的读写操作

说明:因为前面把表锁了

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

 

测试主从同步

测试之前,先介绍一下,几个参数

#主服务器上
binlog-do-db=   //仅同步指定的库
binlog-ignore-db=  //忽略指定库

#从服务器上
replicate_do_db=   //仅同步指定的库
replicate_ignore_db=   //忽略指定库
replicate_do_table=    //仅同步指定的表
replicate_ignore_table=   //忽略指定表
replicate_wild_do_table=    //支持通配符%,可以针对mysql库的所有表都做同步
replicate_wild_ignore_table=  //支持通配符%,可以针对mysql库的所有表都忽略

在mysql从同步忽略某个库或针对某个库的时候
建议使用 replicate_wild_do_table & replicate_wild_ignore_table 这两个参数.

搭建完主从后,测试一下主从机制是否正常


# 在主上查看Anna库下的user表数据是1行
mysql> use Anna
mysql> select count(*) user
    -> ;
+------+
| user |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


# 查看从上Anna库下的user表数据是1行

mysql> use Anna
Database changed

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

#清空主上Anna库下的user表数据
mysql> truncate table user;
Query OK, 0 rows affected (0.01 sec)

#主上查看user表就没有数据了
mysql> select * from user;
Empty set (0.00 sec)

 

# 在从上查看Anna库下的user表,现在就是0了
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

 

# 在主上把Anna库下的user表删除掉了
mysql> drop table user;
Query OK, 0 rows affected (0.00 sec)

# 去从上查看user表,显示不存在了
mysql> select * from user;
ERROR 1146 (42S02): Table 'Anna.user' doesn't exist
 

注:这就是主从同步.

 

提示:若不小心在从上删除了某个库或者某个表,那么会出现主从断开,就只能重新change master那部开始,重新开始的前提是主和从的数据是一致,从上删除了哪个库或者表,主上也删除,才能实现数据一致.

#主上的操作
步骤1:登录Mysql
步骤2:在主上锁表
步骤3:show master status;  查看Position 和 File name 

#从上的操作

步骤1:登录mysql, 停止slave
步骤2:change master
步骤3:开启slave

共有 人打赏支持
粉丝 4
博文 84
码字总数 127267
×
Linux_Anna
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: