vlambda博客
学习文章列表

1.操作环境：

主库：Centos7.5，MySQL8.0.20-本机多实例3306端口

从库：Centos7.5，MySQL8.0.20-本机多实例3308端口

2.在主库上导出表结构

[root@centos-7 etc]# mysqldump -S /tmp/mysql3306.sock --master-data=2 --single-transaction --set-gtid-purged=OFF -uroot -p -d -A> /data/backup/database_`date '+%m-%d-%Y'`.sql

3.查看下备份的文件,备份成功

[root@centos-7 backup]# ls -lh

总用量 52K

-rw-r--r--  1 root root 1.9K 7月   9 09:01 database_07-09-2020.sql

-rw-r--r--  1 root root  38K 7月  16 11:26 database_07-16-2020.sql

-rw-r--r--  1 root root    0 7月  16 11:25 database_.sql

drwxr-x--- 10 root root 4.0K 7月  15 21:53 db3306_full

drwxr-x---  9 root root 4.0K 7月  15 21:53 db3306_incr1

drwxr-xr-x  2 root root    6 7月   9 11:37 mydumper

drwxr-xr-x  4 root root   51 6月  29 17:16 mysql8

4.把表结构的存储引擎改为blackhole，直接VIM更改

[root@centos-7 backup]# vim database_07-16-2020.sql

:%s/InnoDB/blackhole/g

wq保存退出

5.启动一个新节点（从库），默认引擎为blackhole

mysql> show variables like '%default_storage%';

+------------------------+-----------+

| Variable_name          | Value     |

+------------------------+-----------+

| default_storage_engine | BLACKHOLE |

+------------------------+-----------+

1 row in set (0.00 sec)

6.导入之前备份的只有表结构的备份文件

mysql> source /data/backup/database_07-16-2020.sql;

6.1验证看下导入的表是否有数据

mysql> use test_clone;

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 * from test;

Empty set (0.00 sec)

6.2主库上的数据情况：

mysql> select * from test;

+----+--------+

| id | name   |

+----+--------+

|  1 | wangxu |

+----+--------+

1 row in set (0.00 sec)

7.从库开启复制，我开启了GTID模式

mysql> change master to master_host='10.211.55.5',

    -> master_port=3306,

    -> master_user='repl',

    -> master_password='XXXXX',

    -> master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.211.55.5

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000019

          Read_Master_Log_Pos: 196

               Relay_Log_File: centos-7-relay-bin.000002

                Relay_Log_Pos: 418

        Relay_Master_Log_File: mysql-bin.000007

             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: 1050

                   Last_Error: Error 'Table 'student' already exists' on query. Default database: 'school'. Query: 'create table student (id smallint unsigned not null auto_increment primary key,name varchar(50))'

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 523

              Relay_Log_Space: 8006

              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: 0

                Last_IO_Error: 

               Last_SQL_Errno: 1050

               Last_SQL_Error: Error 'Table 'student' already exists' on query. Default database: 'school'. Query: 'create table student (id smallint unsigned not null auto_increment primary key,name varchar(50))'

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 1013306

                  Master_UUID: 75bac4cf-bc63-11ea-9b6f-001c424412ff

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: 

           Master_Retry_Count: 86400

                  Master_Bind: 

      Last_IO_Error_Timestamp: 

     Last_SQL_Error_Timestamp: 200716 14:48:00

               Master_SSL_Crl: 

           Master_SSL_Crlpath: 

           Retrieved_Gtid_Set: 75bac4cf-bc63-11ea-9b6f-001c424412ff:3-8

            Executed_Gtid_Set: 75bac4cf-bc63-11ea-9b6f-001c424412ff:1-2

                Auto_Position: 1

         Replicate_Rewrite_DB: 

                 Channel_Name: 

           Master_TLS_Version: 

       Master_public_key_path: 

        Get_master_public_key: 0

            Network_Namespace: 

1 row in set (0.00 sec)

发现遇到了错误（非GTID环境此类错误的解决办法，1.stop slave，2.set global sql_slave_skip_counter=1，3.start slave），我们记录下执行的位置Executed_Gtid_Set: 75bac4cf-bc63-11ea-9b6f-001c424412ff:1-2，我们去设置下GTID_PURGED跳过这个错误，在设置之前我们需要重置 slave上的 master和slave，reset master的主要目的是使gtid_executed为空，如果不为空会报错。reset slave的目的是为了重新change master to加载设置的GTID_PURGED信息，否则直接start slave还会从之前错误的位置执行。

这里不能简单的使用change master to来切换,这样做表面上不会报错,但是实际上slave并不会更新,服务器会参考show slave status\G中的Executed_Gtid_Set参数来获取数据

mysql> reset master;

Query OK, 0 rows affected (0.01 sec)

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> reset slave;

Query OK, 0 rows affected (0.01 sec)

在之前记录的错误位置Executed_Gtid_Set: 75bac4cf-bc63-11ea-9b6f-001c424412ff:1-2基础上加1

mysql> set global gtid_purged='75bac4cf-bc63-11ea-9b6f-001c424412ff:1-3';

Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host='10.211.55.5',

    -> master_port=3306,

    -> master_user='repl',

    -> master_password='Root!2020',

    -> master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

如果仍然还有此类问题，就重复以上步骤，直到跳过全部错误。

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.211.55.5

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000019

          Read_Master_Log_Pos: 196

               Relay_Log_File: centos-7-relay-bin.000002

                Relay_Log_Pos: 371

        Relay_Master_Log_File: mysql-bin.000019

             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: 196

              Relay_Log_Space: 583

              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: 1013306

                  Master_UUID: 75bac4cf-bc63-11ea-9b6f-001c424412ff

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           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: 75bac4cf-bc63-11ea-9b6f-001c424412ff:1-8

                Auto_Position: 1

         Replicate_Rewrite_DB: 

                 Channel_Name: 

           Master_TLS_Version: 

       Master_public_key_path: 

        Get_master_public_key: 0

            Network_Namespace: 

1 row in set (0.00 sec)

我这里的问题都跳过了，从show slave status来看主从复制成功了。

8.验证下实际效果

8.1主库：

插入一条数据，再去从库对应表上看是否有数据（正常不会有），再看看从库上的binlog是否记录的这条记录（正常会有）。

mysql> insert into test values (null,'111111111111111111111');

Query OK, 1 row affected (0.01 sec)

mysql> select * from test;

+----+-----------------------+

| id | name                  |

+----+-----------------------+

|  1 | wangxu                |

|  2 | 111111111111111111111 |

+----+-----------------------+

2 rows in set (0.00 sec)

8.2从库：

8.2.1查看是否有数据

mysql> select * from test;

Empty set (0.00 sec)

8.2.2查看下GTID是否有增加

mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.211.55.5

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000019

          Read_Master_Log_Pos: 511

               Relay_Log_File: centos-7-relay-bin.000002

                Relay_Log_Pos: 686

        Relay_Master_Log_File: mysql-bin.000019

             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: 511

              Relay_Log_Space: 898

              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: 1013306

                  Master_UUID: 75bac4cf-bc63-11ea-9b6f-001c424412ff

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           Master_Retry_Count: 86400

                  Master_Bind: 

      Last_IO_Error_Timestamp: 

     Last_SQL_Error_Timestamp: 

               Master_SSL_Crl: 

           Master_SSL_Crlpath: 

           Retrieved_Gtid_Set: 75bac4cf-bc63-11ea-9b6f-001c424412ff:9

            Executed_Gtid_Set: 75bac4cf-bc63-11ea-9b6f-001c424412ff:1-9

                Auto_Position: 1

         Replicate_Rewrite_DB: 

                 Channel_Name: 

           Master_TLS_Version: 

       Master_public_key_path: 

        Get_master_public_key: 0

            Network_Namespace: 

1 row in set (0.01 sec)

8.2.3查看下Binlog内是否有主库插入数据的记录

[root@centos-7 logs]# ls

mysql-bin.000001  mysql-bin.index  mysql_error.log

[root@centos-7 logs]# mysqlbinlog -v --base64-output=decode-rows mysql-bin.000001

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#200716 14:55:32 server id 1013308  end_log_pos 125 CRC32 0x4affeb79   Start: binlog v 4, server v 8.0.20 created 200716 14:55:32 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

# at 125

#200716 14:55:32 server id 1013308  end_log_pos 156 CRC32 0x411a5e6d Previous-GTIDs

# [empty]

# at 156

#200716 15:21:22 server id 1013306  end_log_pos 242 CRC32 0xd738df39 GTID    last_committed=0       sequence_number=1       rbr_only=yesoriginal_committed_timestamp=1594884082888517immediate_commit_timestamp=1594884082900274  transaction_length=317

/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

# original_commit_timestamp=1594884082888517 (2020-07-16 15:21:22.888517 CST)

# immediate_commit_timestamp=1594884082900274 (2020-07-16 15:21:22.900274 CST)

/*!80001 SET @@session.original_commit_timestamp=1594884082888517*//*!*/;

SET @@SESSION.GTID_NEXT= '75bac4cf-bc63-11ea-9b6f-001c424412ff:9'/*!*/;

# at 242

#200716 15:21:22 server id 1013306  end_log_pos 318 CRC32 0x52edc91e Query    thread_id=8  exec_time=0error_code=0

SET TIMESTAMP=1594884082/*!*/;

SET @@session.pseudo_thread_id=8/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=524288/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8mb4 *//*!*/;

SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

/*!80005 SET @@session.default_collation_for_utf8mb4=255*//*!*/;

BEGIN

/*!*/;

# at 318

#200716 15:21:22 server id 1013306  end_log_pos 382 CRC32 0x8b35ea1a Table_map: `test_clone`.`test` mapped to number 267

# at 382

#200716 15:21:22 server id 1013306  end_log_pos 442 CRC32 0xdbb7c9ca Write_rows: table id 267 flags: STMT_END_F

### INSERT INTO `test_clone`.`test`

### SET

###   @1=2

###   @2='111111111111111111111'

# at 442

#200716 15:21:22 server id 1013306  end_log_pos 473 CRC32 0x5f0be0c1 Xid = 489

COMMIT/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

验证结论：主库上数据增加上了，从库上的表无数据，GTID正常加1，Bilog也增加了这条记录，代表搭建成功了，完成！