mysql 主从 数据同步

### MySQL主从数据同步知识点详解 #### 一、MySQL主从复制原理及配置步骤 **主从复制**是MySQL的一项重要特性，它允许用户将一台MySQL服务器（主服务器）的数据实时复制到另一台或多台MySQL服务器（从服务器）。这种机制不仅能够实现数据冗余和灾难恢复，还能用于负载均衡和读写分离等场景。 #### 二、配置MySQL主服务器 在进行主从复制之前，首先需要在主服务器上进行必要的配置。 1. **启用二进制日志**：这是主从复制的基础，所有的更改都会被记录在二进制日志中。 ```ini [mysqld] log-bin=mysql-bin ``` 2. **设置服务器ID**：确保每个服务器都有一个唯一的ID，以便在复制过程中正确识别。 ```sql server-id = 1 ``` 3. **忽略不需要复制的数据库**：可以通过`binlog-ignore-db`参数来指定不希望被复制的数据库。 ```ini binlog-ignore-db=mysql binlog-ignore-db=test binlog-ignore-db=information_schema ``` 4. **授予复制权限**：需要为从服务器创建一个可以用于复制的用户账户，并授予相应的权限。 ```sql GRANT REPLICATION SLAVE ON *.* TO 'sync'@'192.168.2.211' IDENTIFIED BY '123456'; FLUSH PRIVILEGES; ``` 5. **锁定表并获取状态信息**：为了确保数据的一致性，在复制过程中需要锁定表，并获取二进制日志的当前位置。 ```sql FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; ``` #### 三、备份主服务器数据 1. **打包数据**：将MySQL数据目录打包成.tar.gz格式，便于传输。 ```bash tar -czf /data/mysql/cacti.tgz /data/mysql/cacti my.cnf ``` 2. **传输数据**：使用`scp`命令将打包好的文件传输到从服务器上。 ```bash scp /data/mysql/cacti.tgz [email protected]:/data/mysql/cacti.tgz ``` 3. **解锁表**：解锁表后，主服务器上的事务可以继续进行。 ```sql UNLOCK TABLES; ``` #### 四、配置从服务器 1. **解压并恢复数据**：在从服务器上解压数据，并调整所有者和组。 ```bash tar -xvzf /data/mysql/cacti.tgz /data/mysql/cacti chown -R mysql:mysql /data/mysql/cacti ``` 2. **设置从服务器的服务器ID**：同样需要设置一个唯一的服务器ID。 ```ini server-id = 2 ``` 3. **配置连接重试时间**：定义从服务器尝试连接主服务器的重试间隔。 ```ini master-connect-retry=60 ``` 4. **指定需要复制的数据库**：可以通过`replicate-do-db`参数指定只复制特定的数据库。 ```ini replicate-do-db=test ``` 5. **启动复制进程**：在从服务器上通过SQL语句启动复制进程。 ```sql STOP SLAVE; CHANGE MASTER TO MASTER_HOST='192.168.2.67', MASTER_USER='rsync', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000047', MASTER_LOG_POS=391592414; START SLAVE; ``` #### 五、监控复制状态 通过查询`SHOW SLAVE STATUS\G`命令，可以查看从服务器的复制状态。 ```sql SHOW SLAVE STATUS\G ``` 这会返回一系列的状态信息，包括： - `Slave_IO_Running`: 表示I/O线程是否正在运行。 - `Slave_SQL_Running`: 表示SQL线程是否正在运行。 - `Master_Host`: 主服务器的IP地址。 - `Master_User`: 用于复制的用户名。 - `Master_Port`: 主服务器的端口号。 - `Master_Log_File`: 当前正在复制的二进制日志文件名。 - `Read_Master_Log_Pos`: 已经读取的二进制日志的位置。 - `Relay_Master_Log_File`: 继续复制时要使用的二进制日志文件名。 - `Relay_Log_File`: 当前使用的中继日志文件名。 - `Relay_Log_Pos`: 当前使用的中继日志的位置。 - `Replicate_Do_DB`: 指定只复制哪些数据库。 以上就是MySQL主从复制的基本配置流程，以及如何监控复制状态。通过这样的配置，可以从服务器可以实时获取主服务器的数据更新，从而实现数据的冗余和故障转移。