17.1-17.5 MySQL主从

最新推荐文章于 2019-05-30 18:34:00 发布

weixin_34358365

最新推荐文章于 2019-05-30 18:34:00 发布

阅读量82

点赞数

原文链接：http://blog.51cto.com/13578154/2117077

版权

17.1 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主从原理图

原理：从会把主的binlog搞到从上，从会根据binlog生成自己的一份中继日志，然后根据中继日志去执行响应的更改，最终达到数据同步一致。

主从的工作场景：

数据备份-->目的是防止主机器发生意外导致无法正常使用，从而将在主上备份到从上的数据迁移出来，从而使其继续服务运作。

数据备份且在从上读取数据-->从库作为读库，减轻主库读的压力。

17.2 准备工作

准备2台Linux服务器，分别作为主库，从库。

centos7-01主库，centos7-02从库

截图

目的是让2台机器都正常启动mysql，方便后面做实验

17.3 主从配置——配置主

设置主的参数，

1 修改my.cnf，增加server-id=128和log_bin=aminglinux1

[root@centos7-01 src]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/tmp/mysql.sock
server-id=128
log_bin=aminglinux1

参数解释：

sever-id为自定义，这里定义的是ip的后缀，方便标记

log_bin名称不能有错误，它是binlog的前缀，不然重启的时候会出现报错。

2 重启mysql

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

2.1 重启mysqld后，可以看看data产生的文件

[root@Centos7-01 mysql]# ls -lt /data/mysql/
总用量 110632
-rw-rw---- 1 mysql mysql 50331648 5月  16 21:38 ib_logfile0
-rw-rw---- 1 mysql mysql 12582912 5月  16 21:38 ibdata1
-rw-rw---- 1 mysql mysql    12337 5月  16 21:38 Centos7-01.err
-rw-rw---- 1 mysql mysql        6 5月  16 21:38 Centos7-01.pid
-rw-rw---- 1 mysql mysql      120 5月  16 21:38 aminglinux1.000001
-rw-rw---- 1 mysql mysql       21 5月  16 21:38 aminglinux1.index
-rw-rw---- 1 mysql mysql       56 5月  16 21:32 auto.cnf
drwx------ 2 mysql mysql     4096 5月  16 21:28 mysql
drwx------ 2 mysql mysql     4096 5月  16 21:28 performance_schema
-rw-rw---- 1 mysql mysql 50331648 5月  16 21:28 ib_logfile1
drwx------ 2 mysql mysql        6 5月  16 21:28 test

分析：

其中前缀aminglinux1是binlog文件

aminglinux1.index 索引文件，这个文件必须要有

aminglinux1.000001二进制文件，这个随后还会产生00002,000003....如此类推

aminglinux1这类文件是实现主从的根本，如果没有aminglinux1这类文件，主从是没办法完成的。

2.2 创建实验数据（此步骤根据实际情况来定义，可做可不做）

[root@Centos7-01 mysql]# mkdir blog
[root@Centos7-01 mysql]# touch 1.txt 2.txt 3.txt 
[root@Centos7-01 mysql]# mv 1.txt 2.txt 3.txt ./blog/
[root@Centos7-01 mysql]# mkdir zrlog1
[root@Centos7-01 mysql]# mkdir mysql2
[root@Centos7-01 mysql]# du -sh mysql2/
64Kmysql2/
[root@Centos7-01 mysql]# du -sh zrlog1/
48Kzrlog1/

2.3 在blog目录的基础上再备份一个出来

[root@Centos7-01 mysql]# mysqldump -uroot -paminglinux blog > /tmp/blog.sql

2.4 创建新库aming,并把刚刚备份的数据恢复至aming

创建新库aming

[root@Centos7-01 mysql]# mysql -uroot -paminglinux -e "create database aming"
Warning: Using a password on the command line interface can be insecure.

2.5 把刚刚备份的数据恢复至aming

[root@Centos7-01 mysql]# mysql -uroot -paminglinux aming < /tmp/blog.sql 
Warning: Using a password on the command line interface can be insecure.

2.6 以aming这个库作为主从参考对象

[root@Centos7-01 mysql]# ls -lt
总用量 110632
drwx------ 2 mysql mysql       20 5月  16 22:07 aming
-rw-rw---- 1 mysql mysql      359 5月  16 22:07 aminglinux1.000001

恢复完数据后，可以看得出aminglinux1.000001这个文件容量在慢慢增大

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

进入mysql
mysql>grant replication slave on *.* to 'repl'@192.168.189.129 identified by 'aminglinux111';

解释：

replication slave 给与从权限

*.* 所有的库，所有的表

'repl' 针对这个用户

@slave_ip 针对从的ip

identified by 'password' 密码

4 锁表，让数据停止更新（主从配置完成后，需要开启）

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

5 查看主的状态

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

记住File和Position的参数内容,稍后会用上它。

6 同步数据库

其中mysql目录不做备份，因为里面太多密码文件，涉及安全性。

[root@Centos7-01 mysql]# ls -lt
总用量 110632
drwxr-xr-x 2 root  root        45 5月  16 22:39 zrlog1
drwxr-xr-x 2 root  root        58 5月  16 22:38 mysql2
-rw-rw---- 1 mysql mysql      571 5月  16 22:23 aminglinux1.000001
drwx------ 2 mysql mysql       20 5月  16 22:07 aming
drwxr-xr-x 2 root  root        45 5月  16 21:57 blog
-rw-rw---- 1 mysql mysql 50331648 5月  16 21:38 ib_logfile0
-rw-rw---- 1 mysql mysql 12582912 5月  16 21:38 ibdata1
-rw-rw---- 1 mysql mysql    12337 5月  16 21:38 Centos7-01.err
-rw-rw---- 1 mysql mysql        6 5月  16 21:38 Centos7-01.pid
-rw-rw---- 1 mysql mysql       21 5月  16 21:38 aminglinux1.index
-rw-rw---- 1 mysql mysql       56 5月  16 21:32 auto.cnf
drwx------ 2 mysql mysql     4096 5月  16 21:28 mysql
drwx------ 2 mysql mysql     4096 5月  16 21:28 performance_schema
-rw-rw---- 1 mysql mysql 50331648 5月  16 21:28 ib_logfile1
drwx------ 2 mysql mysql        6 5月  16 21:28 test

同步数据库mysql2,zrlog1

[root@Centos7-01 mysql]# mysqldump -uroot -paminglinux mysql2 > /tmp/my2.sql
Warning: Using a password on the command line interface can be insecure.
[root@Centos7-01 mysql]# mysqldump -uroot -paminglinux zrlog1 > /tmp/zr1.sql
Warning: Using a password on the command line interface can be insecure.

17.4 主从配置——配置从

1 设置从的参数

增加server-id，不需要填写bin_log

[root@Centos7-02 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/tmp/mysql.sock
server-id=129

1.1 增加完server-id之后，重启服务

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

2 把刚刚做的主上库同步到从上

[root@Centos7-02 ~]# scp 192.168.189.128:/tmp/*.sql /tmp/
blog.sql                           100% 1258   941.3KB/s   00:00    
my2.sql                            100% 1260   963.6KB/s   00:00    
zr1.sql                            100% 1260     1.2MB/s   00:00

3 进入mysql创建4个库:aming,zrlog1,blog,mysql2.

mysql> create database aming;
Query OK, 1 row affected (0.00 sec)
mysql> create database zrlog1;
Query OK, 1 row affected (0.00 sec)
mysql> create database blog;
Query OK, 1 row affected (0.01 sec)
mysql> create database mysql2;
Query OK, 1 row affected (0.00 sec)

4 创建库后，恢复数据-->指定数据至指定的名称，

[root@Centos7-02 ~]# mysql -uroot blog < /tmp/blog.sql 
[root@Centos7-02 ~]# mysql -uroot zrlog1 < /tmp/zr1.sql 
[root@Centos7-02 ~]# mysql -uroot aming< /tmp/blog.sql 
[root@Centos7-02 ~]# mysql -uroot mysql2< /tmp/my2.sql

4.1 主和从的mysql建立的数据都要一致（针对刚刚创建的4个库）

[root@Centos7-02 mysql]# ls -lt /data/mysql/
总用量 110644
drwx------. 2 mysql mysql       20 5月  16 23:27 mysql2
drwx------. 2 mysql mysql       20 5月  16 23:25 blog
drwx------. 2 mysql mysql       20 5月  16 23:25 zrlog1
drwx------. 2 mysql mysql       20 5月  16 23:25 aming
[root@Centos7-01 mysql]# ls -lt
总用量 110632
drwxr-xr-x 2 root  root        45 5月  16 22:39 zrlog1
drwxr-xr-x 2 root  root        58 5月  16 22:38 mysql2
-rw-rw---- 1 mysql mysql      571 5月  16 22:23 aminglinux1.000001
drwx------ 2 mysql mysql       20 5月  16 22:07 aming
drwxr-xr-x 2 root  root        45 5月  16 21:57 blog

因为从上面是没有bin_log文件的所以，aminglinux1.000001只会在主上，不会出现在从上。

5 实现主从

5.1 登录mysql

mysql> stop slave;

5.2 实现主从关键一步的命令

mysql> change master to master_host='192.168.189.128', master_user='repl', master_password='aminglinux111', master_log_file='aminglinux1.000001', master_log_pos=571;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

******主上的show mater status信息

+--------------------+----------+--------------+------------------+-------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| aminglinux1.000001 |      571 |              |                  |                   |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

解释：

master_host=''主的ip

master_user='repl'主的用户

master_password=''住的用户密码

master_log_file='', 主的日志文件(show master status的file)

master_log_pos=xx,主的日日志position(show master status的position)，此处的数值不用加''符号

5.3 启动从

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

5.4 判断主从是否配置成功

mysql> show slave status;
mysql> show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.189.128
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: aminglinux1.000001
          Read_Master_Log_Pos: 571
               Relay_Log_File: Centos7-02-relay-bin.000002
                Relay_Log_Pos: 285
        Relay_Master_Log_File: aminglinux1.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: 571
              Relay_Log_Space: 463
              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: 128
                  Master_UUID: a69f92e6-590d-11e8-a239-000c2916c3f2
             Master_Info_File: /data/mysql/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)

内容解释：

特别关注这两个参数，两个Yes为成功简历主从状态，如果其中一个不是Yes都不是正确的状态。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

如果发现其中一个状态为Connecting的，请前往链接：https://blog.51cto.com/13578154/2117258 根据实际环境进行参考解决

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

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

6 到主上执行 unlock tables

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

总结从上操作：

sever-id 跟主不同
主数据同步到从上
stop,change,start 三个动作
主上执行unlock tables

17.5 测试主从同步

1 假如在从上删掉aming库，可能导致主从不一致

mysql>drop database aming;
Query OK, 0 rows affected (0.03 sec)

2 检查从的状态

mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

从状态虽然显示没出现问题，但是理论主从是不一致的，

3 再到主上drop aming库操作，

mysql> drop database aming;
Query OK, 0 rows affected (0.00 sec)

4 回到从上检查状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.189.128
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: aminglinux1.000001
          Read_Master_Log_Pos: 658
               Relay_Log_File: Centos7-02-relay-bin.000002
                Relay_Log_Pos: 285
        Relay_Master_Log_File: aminglinux1.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1008
                   Last_Error: Error 'Can't drop database 'aming'; database doesn't exist' on query. 
Default database: 'aming'. Query: 'drop database aming'

可以看到两个Yes状态其中SQL已经是No了，

而且还有错误信息：aming库不存在。-->因为在从上已经把database干掉了，所以在主上删除，

此时aming库不存在的，所以不能被删除的。

如果遇到问题，主从会马上断掉。

5 修复方法：

两种方法

5.1 第一种是：stop slave,start slave.

5.2 第二种是：重新做主从

5.2.1 在主上获取主的状态信息

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

5.2.2 从上操作

先停止slave

mysql> stop slave;

再change

mysql> change master to master_host='192.168.189.128', master_user='repl', master_password='aminglinux111', master_log_file='aminglinux1.000001', master_log_pos=658;

然后start slave

mysql> start slave;

查看slave状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.189.128
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: aminglinux1.000001
          Read_Master_Log_Pos: 658
               Relay_Log_File: Centos7-02-relay-bin.000002
                Relay_Log_Pos: 285
        Relay_Master_Log_File: aminglinux1.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

Slave_IO_Running和Slave_SQL_Running的状态都是Yes,成功再次主从同步。

总结：

假如主从同步出错了，需要进行再次做主从的前提是，需要保持数据都要一致，

如果数据不一致，需要重新做多次备份同步，然后再change master。

可以在my.cnf定义的几个配置参数

主服务器上
 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=

转载于:https://blog.51cto.com/13578154/2117077

weixin_34358365

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
17.1-17.5 MySQL主从

17.1 MySQL主从介绍MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的。MySQL主从是基于binlog的，主上须开启binlog才能进行主从。主从过程大致有3个步骤1）主将更改操作记录到binlog里2）从将主的binlog事件(sql语句)同步到从本机上并记录...
复制链接

扫一扫