master@localhost[(none)]> grant replication slave on *.* to 'repl'@'192.168.1.177' identified by 'xxx'; Query OK, 0 rows affected (0.01 sec) --全局读锁 master@localhost[(none)]> flush tables with read lock; Query OK, 0 rows affected (0.02 sec) master@localhost[(none)]> system pwd /data/inst3406 --获取master binlog位置 master@localhost[(none)]> show master status; +--------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+-------------------+ | inst3406bin.000001 | 2169 | | | | +--------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) --使用mysqldump导出实例 master@localhost[(none)]> system mysqldump -uroot -pxxx -S /tmp/mysql3406.sock --routines --all-databases --opt >alldb.sql master@localhost[(none)]> system ls alldb.sql data3406 --解锁 master@localhost[(none)]> unlock tables; master@localhost[(none)]> exit --从库上面导入dump [mysql@app inst3406]$ mysql -uroot -pxxx -S /tmp/mysql3506.sock <alldb.sql --从库上设置主库的相关信息(host,port等等) [mysql@app inst3506]$ mysqls slave@localhost[(none)]> change master to -> MASTER_HOST='192.168.1.177', -> MASTER_USER='repl', -> MASTER_PASSWORD='xxx', -> MASTER_PORT=3406, -> MASTER_LOG_FILE='inst3406bin.000001', -> MASTER_LOG_POS=2169; Query OK, 0 rows affected, 2 warnings (0.01 sec) --启动slave slave@localhost[(none)]> start slave;
部分从库
master@localhost[(none)]> grant replication slave on *.* to 'repl'@'192.168.1.177' identified by 'xxx'; Query OK, 0 rows affected (0.01 sec) --全局读锁 master@localhost[(none)]> flush tables with read lock; Query OK, 0 rows affected (0.02 sec) master@localhost[(none)]> system pwd /data/inst3406 --获取master binlog位置 master@localhost[(none)]> show master status; +--------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+-------------------+ | inst3406bin.000001 | 2169 | | | | +--------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) --使用mysqldump导出实例 master@localhost[(none)]> system mysqldump -uroot -pxxx -S /tmp/mysql3406.sock --routines --all-databases --opt >alldb.sql master@localhost[(none)]> system ls alldb.sql data3406 --解锁 master@localhost[(none)]> unlock tables; master@localhost[(none)]> exit --从库上面导入dump [mysql@app inst3406]$ mysql -uroot -pxxx -S /tmp/mysql3506.sock <alldb.sql --从库上设置主库的相关信息(host,port等等) [mysql@app inst3506]$ mysqls slave@localhost[(none)]> change master to -> MASTER_HOST='192.168.1.177', -> MASTER_USER='repl', -> MASTER_PASSWORD='xxx', -> MASTER_PORT=3406, -> MASTER_LOG_FILE='inst3406bin.000001', -> MASTER_LOG_POS=2169; Query OK, 0 rows affected, 2 warnings (0.01 sec) --启动slave slave@localhost[(none)]> start slave;