mysqldump 的方式来搭建master-->slave 的复制架构

时间:2023-12-18 14:39:50

1、master 上要满足的最小条件:

  1、server_id 已经设置成了一个非0值

  2、log_bin 配置好binlog

2、slave 上要满足的最小条件

  1、server_id 已经设置成了一个非0值,而且这个上值还不能与master 上的值一样

3、在master 上执行flush tables with read lock;

mysql> flush tables with read lock;
Query OK, rows affected (0.04 sec) mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin. | | | | |
+------------------+----------+--------------+------------------+-------------------+

4、用mysqldump 备份整个实例:

mysqldump -h127.0.0. -uroot --all-database --master-data --single-transaction --routines --events -->/tmp/dbdump.sql

5、备份完成后执行unlock tables;

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.04 sec) mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec) mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec) mysql>

6、传输备份文件(/tmp/dbdump.sql)到slave 所在的主机

scp /tmp/dbdump.sql 192.168.80.129:/tmp/dbdump.sql

7、在salve 实例上应用备份文件

mysql </tmp/dbump.sql

8、执行change master to('repl'@%' 是一个在master 已经创建的用户grant replication slave ,replication client on *.* to repl@'%' identified by 'replication')

change master to
master_host='192.168.80.128',
master_port=3306,
master_log_file='mysql-bin.000002',
master_log_pos=120,
master_user='repl',
master_password='replication'; start slave;

9、查看复制的状态是否正常:

mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 120
Relay_Log_File: standby-relay-bin.000005
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes