本文共 2681 字，大约阅读时间需要 8 分钟。

MySQL主从配置

实验场景

master服务器IP地址为192.168.189.145，端口为3307；slave服务器IP地址为192.168.189.146，端口为3307；请确保master和slave服务器上的MySQL版本一致。

master服务器配置

在/etc/my.cnf文件中添加以下配置：

[mysqld]server_id=1log_bin=/mydata/mysql-bin.loglog_bin_index=/mydata/mysql-bin.log.indexrelay_log=/mydata/mysql-relay-binrelay_log_index=/mydata/mysql-relay-bin.indexexpire_logs_days=10max_binlog_size=100Mlog_slave_updates=1auto_increment_increment=2auto_increment_offset=1max_connections=1500lower_case_table_names=1port=3307sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

2. 配置MySQL用户

执行以下命令创建复制所需的用户：

mysql> grant replication slave on . to replication@192.168.1.146 identified by '123456';重启MySQL服务后，查看master服务器的binlog文件：

mysql> show master status\G

此时应看到master的binlog文件为mysql-bin.000002，binlog的位置为1080。

slave服务器配置

3. 配置slave服务器的MySQL配置文件

在/etc/my.cnf文件中添加以下配置：

[mysqld]server_id=2log_bin=/mydata/mysql-bin.loglog_bin_index=/mydata/mysql-bin.log.indexrelay_log=/mydata/mysql-relay-binrelay_log_index=/mydata/mysql-relay-bin.indexexpire_logs_days=10max_binlog_size=100Mlog_slave_updates=1auto_increment_increment=2auto_increment_offset=1max_connections=1500lower_case_table_names=1port=3307sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

2. 配置MySQL用户并设置主从关系

执行以下命令设置主从关系：

mysql> change master to master_host='192.168.189.145',mysql> master_user='replication',mysql> master_password='123456',mysql> master_log_file='mysql-bin.000002',mysql> master_log_pos=1080,mysql> master_port=3307;

启动slave服务器的复制服务：

mysql> start slave;

验证主从状态：

mysql> show slave status\G

此时应看到以下信息：

Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.189.145Master_User: replicationMaster_Port: 3307Connect_Retry: 60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos: 1080Relay_Log_File: mysql-relay-bin.000002Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 1080Relay_Log_Space: 1416Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: 381465c0-6a51-11e4-bd77-000c29c83728Master_Info_File: /mydata/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update itMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0