当前位置:首页 > 实时新闻 > 正文

MySQL 5.7.13主从同步(复制)配置

摘要: MySQL5.7.13主从同步(复制)配置最佳答案53678位专家为你答疑解惑MySQL5.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

发表评论