MySQL搭建主从复制--使用xtrabackup备份进行恢复（一）
方式一：使用xtrabackup备份搭建
目前实例mysql3306和MySQL3307的数据如下
mysql3306> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gtid |
| mysql |
| performance_schema |
| sys |
| test |
| world |
+--------------------+
mysql3307> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
1.备份mysql3306数据库
innobackupex --user=root --password='' -S /tmp/mysql.sock --no-timestamp /data/backup/full
(--no-timestamp 该选项会使得备份文件直接备份在/data/backup/full下，如果不加该参数，会直接在full目录下创建一个以时间命名的目录，存放全备文件)
备份后的文件如下：
[root@localhost full]# ll
total 12344
-rw-r-----. 1 root root 487 Feb 18 21:50 backup-my.cnf
drwxr-x---. 2 root root 4096 Feb 18 21:50 gtid
-rw-r-----. 1 root root 391 Feb 18 21:50 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Feb 18 21:50 ibdata1
drwxr-x---. 2 root root 4096 Feb 18 21:50 mysql
drwxr-x---. 2 root root 4096 Feb 18 21:50 performance_schema
drwxr-x---. 2 root root 12288 Feb 18 21:50 sys
drwxr-x---. 2 root root 4096 Feb 18 21:50 test
drwxr-x---. 2 root root 4096 Feb 18 21:50 world
-rw-r-----. 1 root root 62 Feb 18 21:50 xtrabackup_binlog_info （备份时刻的binlog位置）
-rw-r-----. 1 root root 113 Feb 18 21:50 xtrabackup_checkpoints
[root@localhost full]# cat xtrabackup_checkpoints
backup_type = full-backuped 备份类型为全备
from_lsn = 0 上次备份到的LSN号(对于全备就是从0开始)
to_lsn = 3634657 备份开始时间(ckpt)点数据页的LSN
last_lsn = 3634666 份结束后，redo日志最终的LSN
compact = 0
recover_binlog_info = 0
（1）备份时刻，立即将已经commit过的，内存中的数据页刷新到磁盘(CKPT).开始备份数据，数据文件的LSN会停留在to_lsn位置。
（2）备份时刻有可能会有其他的数据写入，已备走的数据文件就不会再发生变化了。
（3）在备份过程中，备份软件会一直监控着redo的undo，如果一旦有变化会将日志也一并备走，并记录LSN到last_lsn。
从to_lsn ----》last_lsn 就是，备份过程中产生的数据变化.
-rw-r-----. 1 root root 577 Feb 18 21:50 xtrabackup_info
-rw-r-----. 1 root root 2560 Feb 18 21:50 xtrabackup_logfile
2.导出创建库表的语句
[root@localhost full]# mysqldump -uroot -p -S /tmp/mysql.sock -B world gtid test --no-data > /data/3307/data/createtable.sql
检查该文件，导出的只有建表和建库语句，无实际数据
--no-data No row information. (不导出数据，有时我们仅仅需要导出表结构，也就是建表语句就行了)
3.在mysql3307中source该文件，将表和库在从库中创建好
4.将目标库中的表空间删除
mysql> select concat('alter table ',table_schema,'.',table_name,' discard tablespace;') from information_schema.tables where table_schema in ('gtid','test','world') into outfile '/data/3307/data/discard.sql';
Query OK, 4 rows affected (0.00 sec)
mysql> source /data/3307/data/discard.sql;
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ()//有外键约束
Query OK, 0 rows affected (0.00 sec)
mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /data/3307/data/discard.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from world.city;
ERROR 1814 (HY000): Tablespace has been discarded for table 'city'//表空间以为删除
5.将备份中的ibd文件全部拷贝到从库data路径
[root@localhost full]# cp gtid/*ibd /data/3307/data/gtid/
cp: cannot stat ‘gtid/*ibd’: No such file or directory
[root@localhost full]#
[root@localhost full]# cp world/*ibd /data/3307/data/world/
[root@localhost full]# cp test/*ibd /data/3307/data/test/
[root@localhost full]# cd /data/3307/data/test
[root@localhost test]# ll
total 112
-rw-r-----. 1 mysql mysql 65 Feb 18 22:03 db.opt
-rw-r-----. 1 mysql mysql 8586 Feb 18 22:03 t4.frm
-rw-r-----. 1 root root 98304 Feb 18 22:08 t4.ibd
修改下权限
chown -R mysql.mysql *
6.将表空间inport
mysql> select concat('alter table ',table_schema,'.',table_name,' import tablespace;') from information_schema.tables where table_schema in ('gtid','test','world') into outfile '/data/3307/data/import.sql';
Query OK, 4 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.02 sec)
Query OK, 0 rows affected, 1 warning (0.02 sec)
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected, 1 warning (0.02 sec)
查看下数据
mysql> select count(*) from world.city;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+
mysql> select count(*) from test.t4;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
7.主节点创建用户
grant replication slave on . to repl@'IP' identified by '123';
8.change master to搭建复制关系
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.1.38',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000018',
-> MASTER_LOG_POS=194,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
9.主库创建表，模拟备份后的数据变化
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t4 |
+----------------+
1 row in set (0.00 sec)
mysql> create table t1 (id int,name varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values (1,'aa'),(2,'bb'),(3,'cc');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
10.启动备库
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.38
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000018
Read_Master_Log_Pos: 649 //跟show master status的position一致
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 775
Relay_Master_Log_File: mysql-bin.000018
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: 649
Relay_Log_Space: 986
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: 6
Master_UUID: 09dd0d62-8b86-11ec-bd6f-000c29245cff
Master_Info_File: /data/3307/data/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: 09dd0d62-8b86-11ec-bd6f-000c29245cff:8-9
Executed_Gtid_Set: 09dd0d62-8b86-11ec-bd6f-000c29245cff:6-9,
42178851-8ff5-11ec-a8dd-000c29245cff:1-25,
95524dfa-8ff0-11ec-801f-000c29245cff:1
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
10.查看从库数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gtid |
| mysql |
| performance_schema |
| sys |
| test |
| world |
+--------------------+
7 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| t4 |
+----------------+
2 rows in set (0.00 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
+------+------+
3 rows in set (0.00 sec)
简单的主从搭建完成
分类:
MySQL
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· .NET Core 中如何实现缓存的预热？
· 三行代码完成国际化适配，妙~啊~
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？