一、主庫操作
1、全庫導出
四、備庫操作
1、在從庫配置連接主庫,執行以下命令CHANGE MASTER TO MASTER_HOST='192.168.1.6', MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_PORT=3306, MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=529372, MASTER_CONNECT_RETRY=10;
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.6 Master_User: repl Master_Port: 3306 Connect_Retry: 10 Master_Log_File: binlog.000001 Read_Master_Log_Pos: 657284 Relay_Log_File: relaylog.000002 Relay_Log_Pos: 128192 Relay_Master_Log_File: binlog.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes 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: 657284 Relay_Log_Space: 128358 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 Master_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: 101 Master_UUID: c811e75a-d875-11e4-8bb1-b083fede6c94 Master_Info_File: /u01/my3306/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 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: Auto_Position: 0 1 row in set (0.00 sec) ERROR: No query specified
mysql> select * from t1; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> insert into t1 values(2); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec)
mysql> select * from t1; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec)
mysql> show variables like '%autocommit%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> show variables like '%innodb_flush_metho%'; +---------------------+----------+ | Variable_name | Value | +---------------------+----------+ | innodb_flush_method | O_DIRECT | +---------------------+----------+ 1 row in set (0.00 sec) mysql> show variables like '%sync_binlog%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sync_binlog | 100 | +---------------+-------+ 1 row in set (0.00 sec)