MySQL 5.7.13主从同步（复制）配置

MySQL 5.7.13主从同步（复制）配置

最佳答案 53678位专家为你答疑解惑

MySQL 5.7.13主从同步（复制）配置

主从同步是分布式mysql数据库相当重要的配置，现在我在虚拟机上完成主从配置，mysql版本是5.7.13

主服务器的ip是192.168.19.139 副服务器的ip是192.168.19.142

1.主服务器配置(1)修改my.cnf(注意使用root)

vim /etc/my.cnfsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESlog-bin=mysqlbin-logserver_id=1basedir=/home/mysqluser=mysqldatadir=/home/mysql/dataport=3306innodb_flush_log_at_trx_commit=1sync_binlog=1log-slave-updates

(2)进入mysql安装目录下的bin，启动mysql

cd /home/mysql/bin 2 ./mysqld

(3)重开启mysql客户端，进入mysql安装目录下的bin，授权副服务器主从复制，用mysqld的好处是可以查看错误。

./mysql -uroot -p 2 mysql> grant replication slave on *.* to test@192.168.19.142 identified by '123';

(4)显示主服务器状态

mysql> show master status;+---------------------+----------+--------------+------------------+-------------------+| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+---------------------+----------+--------------+------------------+-------------------+| mysqlbin-log.000019 |    1290 |              |                  |                  |+---------------------+----------+--------------+------------------+-------------------+

2.副服务器配置(1)修改my.cnf(注意使用root)

vim /etc/my.cnfsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLEuser=mysqlserver_id=2replicate-do-db=sbfxdmaster-info-file=master.inforelay-log=relay-relay-binrelay-log-index=relay-relay-bin.indexrelay-log-info-file=relay-relay-log.info

(2)进入mysql安装目录下的bin，启动mysql

cd /home/mysql/bin 2 ./mysqld

(3)重开启mysql客户端，进入mysql安装目录下的bin，设置slave属性并开始服务

./mysql -uroot -pmysql> change master to master_host='192.168.19.139',master_port=3306, master_log_file='mysqlbin-log.000019', master_log_pos=1290, master_bind='', master_user='test',master_password='123';mysql> start slave;

(4)显示副服务器的状态

show slave status \G;

            Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.19.139                  Master_User: test                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysqlbin-log.000019          Read_Master_Log_Pos: 1290              Relay_Log_File: relay-relay-bin.000002                Relay_Log_Pos: 323        Relay_Master_Log_File: mysqlbin-log.000019            Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB: sbfxd          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: 1290              Relay_Log_Space: 530              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: 0Master_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                  Master_UUID: 05408021-5ab2-11e6-869b-000c293990c1            Master_Info_File: /home/mysql/data/master.info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates          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        Replicate_Rewrite_DB:                Channel_Name:          Master_TLS_Version:

出现两个yes表示slave可以跑了

3.注意事项：

(1)楼主的虚拟机是通过克隆产生的，所以不需要对mysql进行备份，然后在副服务器上恢复，这步对于数据库极其重要，否则就会出现如下错误:2016-08-17T03:11:27.579193Z 5 [ERROR] Slave SQL for channel '': Error executing row event: 'Table 'sbfxd.sbtable' doesn't exist', Error_code: 11462 2016-08-17T03:11:27.584268Z 5 [Warning] Slave: Table 'sbfxd.sbtable' doesn't exist Error_code: 1146

(2)如果将主服务器的属性配错了需要stop slave，然后设置好之后start slave

mysql 配置主从同步

1.环境 ：首先准备两台服务器或者虚拟机，分别安装好mysql的服务。

#mastermysql5.7 ：172.16.1.101 3306#slavemysql5.7 ：172.16.1.102 3306

mysql安装链接地址：centos7 安装mysql57

2.修改主库和从库的配置文件，并重启修改配置后的服务；

#master config[mysqld]server-id=1        # 节点ID，确保唯一# log configlog-bin=mysql-bin                   #binlog日志功能开启sync_binlog=1                          #事务变更binlog是否写入磁盘，0否1写入binlog_format=mixed              #binlog日志格式，默认statement，建议使用mixedexpire_logs_days=7                  #binlog过期清理时间max_binlog_size=100m            #binlog每个日志文件大小binlog_cache_size=4m              #binlog缓存大小max_binlog_cache_size=512m   #binlog缓存最大值binlog-ignore-db=mysql             #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接， #或者 复制这句话，写多行auto-increment-offset=1           # 自增值的偏移量auto-increment-increment=1    # 自增值的自增量slave-skip-errors=all                  #跳过从库错误

#slave config[mysqld]server-id=2log-bin=mysql-binrelay-log=mysql-relay-binreplicate-wild-ignore-table=mysql.% #忽略表replicate-wild-ignore-table=test.%replicate-wild-ignore-table=information_schema.%

重启服务：systemctl or service ，centos使用 systemctl

#mastermaster linux> systemctl restart mysqld#slaveslave linux> ssystemctl restart mysqld

3.主库创建 用户并授权

master mysql> create user repl_user identified by 'pwd_123';#密码设置失败，可以调整密码规则master mysql> set global validate_password_policy=0;

master mysql> grant replication slave on *.* to 'repl_user'@'172.16.1.102'  identified by 'pwd_123';master mysql> flush privileges;

4.查询主库状态

master mysql> show master status;

5.根据上一步查出数据，配置从库

slave mysql> CHANGE MASTER TO MASTER_HOST='172.16.1.101',  MASTER_USER='repl_user', MASTER_PASSWORD='pwd_123',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=11763,MASTER_RETRY_COUNT=60,MASTER_HEARTBEAT_PERIOD=10000; 

6.启动从库线程

slave mysql> start slave;

7.主库创建表或者变更数据，在从库验证是否同步成功！

end