alter user 'root'@'localhost' identified by '1';  10.19.160.132  sf(主)10.19.160.133  sf160133(从)

1、创建复制账号在Master节点上创建一个用于复制的用户,供Slave节点使用create user 'rpl'@'%' identified by '1'; grant replication slave on *.* to 'rpl'@'%'; commit;"root@localhost:mysql.sock  [(none)]>create user 'rpl'@'%' identified by '1'; Query OK, 0 rows affected (0.00 sec)"root@localhost:mysql.sock  [(none)]>grant replication slave on *.* to 'rpl'@'%'; Query OK, 0 rows affected (0.01 sec)"root@localhost:mysql.sock  [(none)]>commit;Query OK, 0 rows affected (0.00 sec)测试slave节点是否可以通过 rpl@'%' 连接成功mysql -urpl -p -h 10.19.160.132 -P 3306"rpl@10.19.160.132:3306  [(none)]>show databases;+--------------------+| Database           |+--------------------+| information_schema |+--------------------+1 row in set (0.00 sec)2、备份数据(1)准备测试数据(在主库)create database burn_test; use burn_test;create table test_rpl (a int not null primary key auto_increment, b int, c int, d int); insert into test_rpl values(NULL,1,2,3),(NULL,4,5,6),(NULL,7,8,9); commit; (2)备份数据mysqldump -uroot -p -B burn_test --single-transaction --master-data=1 >burn_test.sql#警告先忽略[root@sf ~]# mysqldump -uroot -p -B burn_test --single-transaction --master-data >burn_test.sqlEnter password: Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. scp到远程10.19.160.133scp burn_test.sql root@sf160133:/root从库执行下面步骤[root@sf160133 ~]# mysql -uroot -p < burn_test.sql Enter password: ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty."root@localhost:mysql.sock  [(none)]>reset master;Query OK, 0 rows affected (0.01 sec)[root@sf160133 ~]# mysql -uroot -p < burn_test.sql (3)change master 主库查看"root@localhost:mysql.sock  [burn_test]>show master status\G;*************************** 1. row ***************************             File: bin.000002         Position: 1716     Binlog_Do_DB:  Binlog_Ignore_DB: Executed_Gtid_Set: 59b96e21-875e-11e8-a589-005056add04a:1-71 row in set (0.00 sec)从库执行change master to master_host='10.19.160.132',master_user='rpl',master_password='1',master_port=3306,master_log_file='bin.000002',master_log_pos=1716; 查看从库信息:"root@localhost:mysql.sock  [burn_test]>show slave status\G;*************************** 1. row ***************************               Slave_IO_State:                   Master_Host: 10.19.160.132                  Master_User: rpl                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: bin.000002          Read_Master_Log_Pos: 1716               Relay_Log_File: relay.000001                Relay_Log_Pos: 4        Relay_Master_Log_File: bin.000002             Slave_IO_Running: No            Slave_SQL_Running: No              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: 1716              Relay_Log_Space: 154              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: NULLMaster_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: 0                  Master_UUID:              Master_Info_File: mysql.slave_master_info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State:            Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set: 59b96e21-875e-11e8-a589-005056add04a:1-7                Auto_Position: 0         Replicate_Rewrite_DB:                  Channel_Name:            Master_TLS_Version: 1 row in set (0.00 sec)开启从库start slave; "root@localhost:mysql.sock  [burn_test]>show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event  -- IO 线程的状态                  Master_Host: 10.19.160.132                  Master_User: rpl                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: bin.000002   -- IO线程读取到的文件           Read_Master_Log_Pos: 1716         -- IO线程读取文件中的位置               Relay_Log_File: relay.000002                  Relay_Log_Pos: 314        Relay_Master_Log_File: bin.000002     -- SQL线程执行到的文件              Slave_IO_Running: Yes            -- io thread 启动成功             Slave_SQL_Running: Yes            -- sql thread 启动成功               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: 1716         -- SQL线程执行到文件的位置               Relay_Log_Space: 511              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     -- Slave 落后 Master 的秒Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:              -- (IO)如果这里有信息的话,就是错误提示信息,可以用来排错                Last_SQL_Errno: 0               Last_SQL_Error:             -- (SQL)如果这里有信息的话,就是错误提示信息,可以用来排错   Replicate_Ignore_Server_Ids:              Master_Server_Id:                   Master_UUID: 59b96e21-875e-11e8-a589-005056add04a             Master_Info_File: mysql.slave_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:             Executed_Gtid_Set: 59b96e21-875e-11e8-a589-005056add04a:1-7                Auto_Position: 0         Replicate_Rewrite_DB:                  Channel_Name:            Master_TLS_Version: 1 row in set (0.00 sec)注意:Slave_IO_Running 和 Slave_SQL_Running 这两个指标都为YES ,表示目前的复制的状态是正常的。在主库或者从库上面执行show processlist;可以看到相应的进程主库:"root@localhost:mysql.sock  [burn_test]>show processlist;可以看到dump线程Binlog Dump |  366 | Master has sent all binlog to slave; waiting for more updates | NULL从库:"root@localhost:mysql.sock  [burn_test]>show processlist\G;*************************** 1. row ***************************     Id: 15   User: root   Host: localhost     db: burn_testCommand: Query   Time: 0  State: starting   Info: show processlist*************************** 2. row ***************************     Id: 17   User: system user   Host:      db: NULLCommand: Connect   Time: 716  State: Waiting for master to send event  --IO线程   Info: NULL*************************** 3. row ***************************     Id: 18   User: system user   Host:      db: NULLCommand: Connect   Time: 716  State: Slave has read all relay log; waiting for more updates --SQL线程   Info: NULL3 rows in set (0.00 sec)如果开启了并行复制 (multi-threaded slave), show processlist 中可以看到 Coordinator 线程在从库上面配置。# /etc/my.cnf # ------ 省略其他 -----slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=4(4)验证数据主库插数据,从库可以看到insert into test_rpl values(NULL,10,11,12);