mysql主从结构的实现_mysql主从复制架构及实现

清理日志：使用PURGE命令，清理之前确保数据已经备份过了

复制监控

MariaDB [(none)]> SHOW BINARY LOGS;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 483 |

| mysql-bin.000002 | 264 |

| mysql-bin.000003 | 264 |

| mysql-bin.000004 | 264 |

| mysql-bin.000005 | 990 |

| mysql-bin.000006 | 514 |

| mysql-bin.000007 | 264 |

| mysql-bin.000008 | 245 |

| mysql-bin.000009 | 245 |

+------------------+-----------+

MariaDB [(none)]> SHOW BINLOG EVENTS;

+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+

| mysql-bin.000001 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.45-MariaDB-log, Binlog ver: 4 |

| mysql-bin.000001 | 245 | Query | 1 | 315 | BEGIN |

| mysql-bin.000001 | 315 | Intvar | 1 | 343 | INSERT_ID=9 |

| mysql-bin.000001 | 343 | Query | 1 | 456 | use `S_SC_C`; INSERT INTO S (sname,sdept) VALUES ('xiao','MA') |

| mysql-bin.000001 | 456 | Xid | 1 | 483 | COMMIT /* xid=43 */ |

+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+

MariaDB [(none)]> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000009 | 245 | | |

+------------------+----------+--------------+------------------+

MariaDB [(none)]> SHOW PROCESSLIST;

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+

| Id | User | Host | db | Command | Time | State | Info | Progress |

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+

| 5 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | 0.000 |

| 6 | system user | | NULL | Connect | 3850 | Waiting for master to send event | NULL | 0.000 |

| 7 | system user | | NULL | Connect | 3806 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0.000 |

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+

MariaDB [(none)]> SHOW SLAVE STATUS\G

从服务器是否落后与主服务器，在从服务器的salve有Seconds_Behind_Master可以查看

MariaDB [(none)]> SHOW SLAVE STATUS

-> \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.182.130

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000079

Read_Master_Log_Pos: 334

Relay_Log_File: 1.000006

Relay_Log_Pos: 618

Relay_Master_Log_File: mysql-bin.000079

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: MYDB

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: 334

Relay_Log_Space: 1182

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: 1

确定主从节点数据是否一致，使用一些工具，例如percona-tools

如果数据不一致，那么就要将从节点的所有数据删除，将主节点的数据备份一份导入到从节点上，重新复制