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

[[email protected] data]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS! 

[[email protected] ~]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS! 

 

 

 配置主 

第1步：修改配置文件

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

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

 

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

[[email protected] ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

#重启之后，/data/mysqld目录下生成了几个文件
[[email protected] ~]# cd /data/mysqld/

[[email protected] 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数据库备份
[[email protected] ~]# mysqldump -uroot mysql > /tmp/mysql.sql

#创建Anna库
[[email protected] mysqld]# mysql -uroot -e "create database Anna"

#恢复数据
[[email protected] ~]# mysql -uroot Anna < /tmp/mysql.sql

 

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

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

[[email protected] 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目录下的文件和目录备份一下

[[email protected] 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

[[email protected] mysqld]# mysqldump -uroot test > /tmp/test.sql
[[email protected] mysqld]# mysqldump -uroot bakup > /tmp/bakup.sql

 

配置从 

 

第1步：修改配置文件

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

[[email protected] ~]# vim /etc/my.cnf

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

 

#修改配置文件后，需要重启mysql服务
[[email protected] ~]# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL...... SUCCESS! 

 

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

[[email protected] ~]# scp 192.168.2.179:/tmp/*.sql /tmp/
[email protected]'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库因为已经有，不能再创建.

[[email protected] ~]# 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步：数据恢复

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

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

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

#主
[[email protected] ~]# 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

#从
[email protected] ~]# 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

[[email protected] ~]# 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