8.31任务

最新推荐文章于 2024-10-08 15:41:48 发布

chenglichu0862

最新推荐文章于 2024-10-08 15:41:48 发布

阅读量85

点赞数

文章标签：数据库运维

原文链接：https://my.oschina.net/u/3866688/blog/1944055

版权

17.1 MySQL主从介绍

MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步。

MySQL主从是基于binlog的，主上须开启binlog才能进行主从。

主从过程大致有三个步骤，

主将更改操作记录到binlog里
从将主的binlog时间（sql语句）按顺序执行
从根据relaylog（中继日志）里面的sql语句按顺序执行

主上有一个log dump线程，用来和从的I/O线程传递binlog

从上有两个线程，其中I/O线程用用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地

当master有写入进来的时候，log dump会和slave的I/O通信，然后写入Relaylog，之后SQL线程读写Relaylog。

场景用于数据备份，而且slave数据库可以提供读取权限，做负载均衡。

17.2 准备工作

准备至少两台虚拟机，两个虚拟机都需要安装mysql并运行。仔细检查不同主机的UUID需要设置为不同。

17.3 配置主

修改my.cnf，在[mysqld]下添加两行

server-id=101
log_bin=master101

重启一下mysql

[root@localhost: ~]# ls /mysql/data/ -lt
total 110740
-rw-rw---- 1 mysql mysql 50331648 Sep  4 17:08 ib_logfile0
-rw-rw---- 1 mysql mysql 12582912 Sep  4 17:08 ibdata1
-rw-rw---- 1 mysql mysql    97101 Sep  4 17:08 localhost.localdomain.err
-rw-rw---- 1 mysql mysql        5 Sep  4 17:08 localhost.localdomain.pid
-rw-rw---- 1 mysql mysql       38 Sep  4 17:08 master101.index
-rw-rw---- 1 mysql mysql      120 Sep  4 17:08 master101.000002
-rw-rw---- 1 mysql mysql      120 Sep  4 17:08 master101.000001
-rw-rw---- 1 mysql mysql    15218 Sep  4 17:04 localhost1.err
-rw-rw---- 1 mysql mysql        5 Sep  4 17:04 localhost1.pid
drwx------ 2 mysql mysql      324 Sep  3 15:57 zrlog
drwx------ 2 mysql mysql     4096 Aug 24 16:00 mysql2
drwx------ 2 mysql mysql       48 Aug 24 15:42 db1
-rw-rw---- 1 mysql mysql       56 Aug 24 14:17 auto.cnf
drwx------ 2 mysql mysql     4096 Aug 24 14:09 mysql
drwx------ 2 mysql mysql     4096 Aug 24 14:09 performance_schema
-rw-rw---- 1 mysql mysql 50331648 Aug 24 14:09 ib_logfile1
drwx------ 2 mysql mysql        6 Aug 24 14:09 test

新生成的以master101为前缀的文件是非常重要的，实现主从就靠这些文件了。

准备一些数据实验。

[root@localhost: ~]# mysqldump -uroot -p123r zrlog > /tmp/zrlog.sql
Warning: Using a password on the command line interface can be insecure.
[root@localhost: ~]# mysql -uroot -p123r -e "create database master_test"
Warning: Using a password on the command line interface can be insecure.
[root@localhost: ~]# mysql -uroot -p123r master_test < /tmp/zrlog.sql 
Warning: Using a password on the command line interface can be insecure.

把zrlog库备份一份给master_test，然后其实bin_log里面就会包含所有的改动信息了，我们也可以通过bin_log恢复数据库的数据。前提是bin_log要完整。

创建一个应用于主从的用户，

[root@localhost: ~]# mysql -uroot -p123r
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.41-log Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

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

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| master101.000002 |    11192 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

flush锁表，然后记住这个file和position。

主从同步不需要同步mysql，因为上面存了用户以及密码，不可能同步所有权限，安装生成即可。

有的同学，遇到主从不能正常同步，提示uuid相同的错误。这是因为克隆机器导致。

17.4 配置从

同样，添加一行在my.cnf,作为从是不需要bin_log的

server-id=102

重启服务，此时仅仅增加server_id在文件夹中是不会改变的。

[root@localhost: ~]# ls /mysql/data/
auto.cnf  db1  ibdata1  ib_logfile0  ib_logfile1  localhost.localdomain.err  localhost.localdomain.pid  mysql  mysql2  performance_schema  test

把主的备份数据拷贝过来

[root@localhost: ~]# scp 192.168.127.101:/tmp/*.sql /tmp/

恢复数据库，如果配置前数据库不一致，会有一些很严峻的问题。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| master_test        |
| mysql              |
| mysql2             |
| performance_schema |
| test               |
| zrlog              |
+--------------------+

[root@localhost: ~]# mysql -uroot -p123r zrlog < /tmp/zrlog.sql 
Warning: Using a password on the command line interface can be insecure.
[root@localhost: ~]# mysql -uroot -p123r master_test < /tmp/master.sql 
Warning: Using a password on the command line interface can be insecure.

最关键的配置就在于配置master_host等一系列的参数。

[root@localhost: ~]# mysql -uroot -p123r
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.41 Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> change master to master_host='192.168.127.101', master_user='repl', master_password='password', master_log_file='master101.000002', master_log_pos=11192;
Query OK, 0 rows affected, 2 warnings (0.11 sec)

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

这里的端口默认3306。

查看一下主从信息

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.127.101
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master101.000002
          Read_Master_Log_Pos: 11192
               Relay_Log_File: localhost-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master101.000002
             Slave_IO_Running: No
            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: 11192
              Relay_Log_Space: 120
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1593
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 101
                  Master_UUID: 
             Master_Info_File: /mysql/data/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: 180905 10:39:13
     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)

ERROR: 
No query specified

错误显示：Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs;

删掉/mysql/data/auto.cnf里面的uuid，重启服务

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

看到这两条变为Yes就说明已经成功了。

最后不要忘了在主上解锁

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

17.5 测试主从同步

下面介绍几个参数，写在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=

但是当我忽略某个数据库的时候，一些联合查询也不会被记录下来，会导致中继日志不完整，所以单独表的忽略我们一般不去使用，而是使用参数replicate_wild_ignore_table。从上常用的就是最后两个参数。

测试：

MASTER:

mysql> use db1;
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> 
mysql> show tables
    -> ;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

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

SLAVE:

mysql> use db1;
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> use db1;
Database changed
mysql> select count(*) t1;
+----+
| t1 |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql>

mysql> drop table t1;
Query OK, 0 rows affected (0.14 sec)



-》》》》



mysql> show tables;
Empty set (0.00 sec)

注意：一定不要在从上做删除等操作，否则出现错误主从要重新做（只做change master即可）。

转载于:https://my.oschina.net/u/3866688/blog/1944055

chenglichu0862

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫