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

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, 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 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

4、用mysqldump 备份整个实例：

mysqldump -h127.0.0.1 -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