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