2.设置从库
1).修改从库my.cnf
| server-id = 43 #主从可1对多 从各id不能相同 |
2)将主库的快照灌入从库
| root@10.1.1.43:tmp# cat all.sql | mysql -uroot -p |
3)在从库上设置同步.查看从库状态.
| mysql> change master to master_host='10.1.1.45', master_user='repl',master_password='replpass',master_log_file='45.000064',master_log_pos=98; Query OK, 0 rows affected (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.1.1.45 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: 45.000064 Read_Master_Log_Pos: 98 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 228 Relay_Master_Log_File: 45.000064 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: 98 Relay_Log_Space: 228 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 1 row in set (0.00 sec) ERROR: No query specified |
测试OK
当然这只是最简单的配置
还有很多参数可根据环境需求变化.
比如
replicate-do-db=test 过滤拉主日志到从只需要这个库和下面的表 replicate-wild-do-table=test.dave replicate-wild-do-table=test.davehemysql数据库同步跳过临时错误
| stop slave; set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; (事务类型,可能需要执行几次) start slave; stop slave IO_THREAD //此线程把master段的日志写到本地 start slave IO_THREAD stop slave SQL_THREAD //此线程把写到本地的日志应用于数据库 start slave SQL_THREAD |
Slave_IO_Running: No错误
由于主库的主机192.168.1.1宕机,再次启来后，从库192.168.71.1连接主库发现报错. Slave_IO_Running: No
| root@192.168.71.1:~# mysql -uroot -p --socket=/opt/mysql/3399/3399.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 452723 Server version: 5.0.51a-24+lenny2 (Debian) Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.1.1 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: 99.000302 Read_Master_Log_Pos: 165112917 Relay_Log_File: 3399-relay-bin.000013 Relay_Log_Pos: 165113047 Relay_Master_Log_File: 99.000302 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql 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: 165112917 Relay_Log_Space: 165113047 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: NULL 1 row in set (0.00 sec) |
