澳门正规赌博十大网站-澳门游戏网站
做最好的网站

MySQL主从同步,同步错误Query

mysql> slave stop;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    120
Current database: *** NONE ***

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show slave statusG;                             
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.8.199
                  Master_User: rep1
                  Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: binlog.000018
      Read_Master_Log_Pos: 632099171
              Relay_Log_File: relaylog.000044
              Relay_Log_Pos: 167930769
     Relay_Master_Log_File: binlog.000018
          Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
           Replicate_Do_DB: 
      Replicate_Ignore_DB: mysql
        Replicate_Do_Table: 
   Replicate_Ignore_Table: discuz.cdb_sessions
 Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                  Last_Errno: 0
                  Last_Error: 
              Skip_Counter: 0
    Exec_Master_Log_Pos: 622943196
         Relay_Log_Space: 177087347
           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: 7954
Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
            Last_IO_Error: 
          Last_SQL_Errno: 0
          Last_SQL_Error: 
1 row in set (0.00 sec)

ERROR: 
No query specified

在主的mysql : 里面查询 Desc club.question_del; 

设置从库为只读形式

mysql> show global variables like "%read_only%";
 ----------------------- ------- 
| Variable_name         | Value |
 ----------------------- ------- 
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
 ----------------------- ------- 
5 rows in set (0.03 sec)

mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like "%read_only%";
 ----------------------- ------- 
| Variable_name         | Value |
 ----------------------- ------- 
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
 ----------------------- ------- 
5 rows in set (0.00 sec)

赶尽杀绝办法:

由于主服务器运行了一段时间,产生了二进制文件,而slave是从log.000001伊始读取的,删除主机二进制文件,包涵log.index文件。

估摸,在master上创办三个test库,slave会同步创立相应的test库!MySQL主从同步安插成功!

指鹿为马再现步骤:

 

MySQL主从同步

环境:
master IP : 10.83.3.102
slave IP : 10.83.3.103

Distributor ID: Debian
Description: Debian GNU/Linux 9.3 (stretch)
Release: 9.3
Codename: stretch

mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_MySQL主从同步,同步错误Query。64) using EditLine wrapper

在slave上:

MySQL主从同步,同步错误Query。slave stop;
change master to master_host='IP地址',master_user='club',master_password='mima ',master_log_file='mysqld-bin.000048',MASTER_MySQL主从同步,同步错误Query。MySQL主从同步,同步错误Query。LOG_MySQL主从同步,同步错误Query。POS=396549485;
MySQL主从同步,同步错误Query。注:master_log_file='mysqld-bin.000048',MASTER_LOG_POS=396549485;是从主的上边查出 来的 :show master statusG;

始建主从同步的mysql user

root@newbie-unknown85882:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> create user 'slave'@'10.83.3.103' identified by 'slavemima';
Query OK, 0 rows affected (0.01 sec)
#创建slave用户,并指定该用户只能在10.83.3.103上登录。

mysql> grant replication slave on *.* to 'slave'@'10.83.3.103';
Query OK, 0 rows affected (0.01 sec)
#为slave赋予replication slave权限。

mysql> 

mysql master/slave 同步错误Query partially completed on the master (error on master: 1317)

 

为了使主库与从库的多寡保持一致,先为MySQL插足读锁,使其变为只读。

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

在master上执行:

ges can be found in the MySQL error log 

勘误了不当后继续测试:

在master上操作

mysql> show master status;
 ------------------ ---------- -------------- ------------------ ------------------------------------------ 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
 ------------------ ---------- -------------- ------------------ ------------------------------------------ 
| mysql-bin.000007 |      359 |              |                  | 43425f23-2760-11e8-a564-52540035ed32:1-5 |
 ------------------ ---------- -------------- ------------------ ------------------------------------------ 
1 row in set (0.00 sec)

由master上搜查缴获的音讯得知,master replication log的地方和Position和大家在此以前查的不平等了,那么要以今后的为准。

在slave上操作

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to
    -> master_host='10.83.3.102',
    -> master_user='slave',
    -> master_password='slavemima',
    -> master_log_file='mysql-bin.000007',
    -> master_log_pos=359;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
 -------------------- 
| Database           |
 -------------------- 
| information_schema |
| mysql              |
| newbie             |
| performance_schema |
| sys                |
 -------------------- 
5 rows in set (0.00 sec)

今昔有所的布署都已产生且不易,接下去就是最要害的测试,同步测试。

在master上操作

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
 -------------------- 
| Database           |
 -------------------- 
| information_schema |
| mysql              |
| newbie             |
| performance_schema |
| sys                |
| test               |
 -------------------- 
6 rows in set (0.01 sec)

在slave上操作

mysql> show databases;
 -------------------- 
| Database           |
 -------------------- 
| information_schema |
| mysql              |
| newbie             |
| performance_schema |
| sys                |
| test               |
 -------------------- 
6 rows in set (0.01 sec)
mysql> select count(1) from test2;
 ---------- 
| count(1) |
 ---------- 
| 0 |

mysql> show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.8.199
                  Master_User: rep1
                  Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: binlog.000018
      Read_Master_Log_Pos: 631625681
              Relay_Log_File: relaylog.000044
              Relay_Log_Pos: 167918085
     Relay_Master_Log_File: binlog.000018
          Slave_IO_Running: Yes
        Slave_SQL_Running: No
           Replicate_Do_DB: 
      Replicate_Ignore_DB: mysql
        Replicate_Do_Table: 
   Replicate_Ignore_Table: discuz.cdb_sessions
 Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                Last_Errno: 1317
                 Last_Error: Query partially completed on the master (error on master: 1317) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: 'update ucgh_userlogin set firstlogin=(select min(logdate) from ucgh_loginlogs1 where ucgh_loginlogs1.uid=ucgh_userlogin.uid and ucgh_loginlogs1.serverid=ucgh_userlogin.serverid) where ucgh_userlogin.firstlogin='0000-00-00''
                 Skip_Counter: 0
       Exec_Master_Log_Pos: 622930512
            Relay_Log_Space: 176613857
              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: NULL
Master_SSL_Verify_Server_Cert: No
              Last_IO_Errno: 0
              Last_IO_Error: 
            Last_SQL_Errno: 1317
            Last_SQL_Error: Query partially completed on the master (error on master: 1317) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: 'update ucgh_userlogin set firstlogin=(select min(logdate) from ucgh_loginlogs1 where ucgh_loginlogs1.uid=ucgh_userlogin.uid and ucgh_loginlogs1.serverid=ucgh_userlogin.serverid) where ucgh_userlogin.firstlogin='0000-00-00''
1 row in set (0.00 sec)

ERROR: 
No query specified

3、错误提示如下

导入master DB的dbdump.sql文件,使master-slave数据一致

root@newbie-unknown85883:~# systemctl restart mysql
root@newbie-unknown85883:~# mysql -u root -p < /tmp/dbdump.sql 
Enter password: 
mysql> delete from test2;
Query OK, 419431 rows affected (0.00 sec)

mysql> insert into test2 select * from test_no_myisam;
Ctrl-C -- sending "KILL QUERY 10" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql>
mysql> select count(1) from test2;
 ---------- 
| count(1) |
 ---------- 
| 919888 |
 ---------- 
1 row in set (0.00 sec)

竭泽而渔办法

使slave与master创立连接,从而实现MySQL主从同步

root@newbie-unknown85883:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> change master to
    -> master_host='10.83.3.102',
    -> master_user='slave',
    -> master_password='slavemima',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=617;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> 
#master_log_file='mysql-bin.000001'和master_log_pos=617的值是从master上 show master status得到的。

Mysql > set global sql_slave_skip_counter =1 ;

退换配置文件

root@newbie-unknown85882:~# vim /etc/mysql/mysql.conf.d/mysqld.cnf
#在配置文件里添加:
bind-address = 10.83.3.102 #master ip
#bind-address是MySQL用来监听某个单独的TCP/IP连接,只能绑定一个IP地址,被绑定的IP地址可以映射多个网络接口。这里的配置也可以写成bind-address = 0.0.0.0,表示接受所有IPv4地址。
server-id = 1 #在master-slave架构中,每台机器节点都需要有唯一的server-id
log_bin = /var/log/mysql/mysql-bin.log #开启binlog
root@newbie-unknown85882:~# systemctl restart mysql

 

后天MySQL主从同步已经配备好了,可是测试的进程中或者会油但是生各类难题产生不能够共同数据。那时候须求查阅配置文件是还是不是科学,查看错误日志获取主要新闻以便消除难题。

分别在master和slave上执行show master status;和show slave statusG;

mysql> show master status;
 ------------------ ---------- -------------- ------------------ ------------------------------------------ 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
 ------------------ ---------- -------------- ------------------ ------------------------------------------ 
| mysql-bin.000007 |      194 |              |                  | 43425f23-2760-11e8-a564-52540035ed32:1-4 |
 ------------------ ---------- -------------- ------------------ ------------------------------------------ 

mysql> show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.83.3.102
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 672
               Relay_Log_File: newbie-unknown85883-relay-bin.000002
                Relay_Log_Pos: 633
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: No
            Slave_SQL_Running: Yes

当Slave_IO_Running或者Slave_SQL_Running展现非“Yes”状态的时,表明配置有错误,供给查阅配置或然不当日志。

cat /var/log/mysql/error.log
2018-03-16T11:20:00.929581Z 7 [ERROR] Slave I/O for channel '': The replication receiver thread cannot start because the master has GTID_MODE = ON and this server has GTID_MODE = OFF. Error_code: 1593

本身是在slave上查看错误日志的,由this server has GTID_MODE = OFF可得知slave的GTID未有拉开,那时候需求转移配置文件开启GTID。

什么是GTID?
GTID(Global Transaction ID)是对此三个已交给业务的数码,并且是八个大局唯一的编号。它的法定概念如下:GTID = source_id :transaction_id每七个 GTID 代表贰个数据库事务。在地方的概念中,source_id 表示实践专门的学业的主库uuid(server_uuid),transaction_id 是贰个从 1 起先的自增计数,表示在这一个主库上实践的第 n 个业务。MySQL 只要保险每台数据库的 server_uuid 全局唯一,以及每台数据库生成的 transaction_id 本身唯一,就能够确认保障 GTID 的大局唯一性。

马到功成推行后三番五次运营 reset slave;

确保master和slave都开启GTID事务

root@newbie-unknown85883:~# vim /etc/mysql/mysql.conf.d/mysqld.cnf
#在配置文件中添加:
gtid_mode = on
enforce_gtid_consistency = 1


mysql> show variables like '%gtid_mode%';
 --------------- ------- 
| Variable_name | Value |
 --------------- ------- 
| gtid_mode     | ON    |
 --------------- ------- 
1 row in set (0.00 sec)

消除方案是:运营命令 stop slave;

在master上操作

 这些荒唐正是IO 进程没连接上  ,想艺术连接上把 把与主的POS 号和文书一定要对,然后再次加载下多少。具体步骤:

测试:

load data from master;

分别在master和slave上放通3306端口

#先在master上操作
root@newbie-unknown85882:~# vim /etc/network/iptables.up.rules 
# Generated by iptables-save v1.6.0 on Thu Feb  8 09:48:16 2018
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [31:3088]
-A INPUT -s 10.83.3.104/32 -p tcp -m tcp --dport 3000 -j ACCEPT
-A INPUT -s 10.83.3.103/32 -p tcp -m tcp --dport 3000 -j ACCEPT
-A INPUT -s 10.83.3.11/32 -p tcp -j ACCEPT
-A INPUT -s 192.168.0.0/16 -p tcp -m multiport --dports 8888,8889,8890,9500,9999,35000 -j ACCEPT
-A INPUT -s 172.16.0.0/12 -p tcp -m multiport --dports 8888,8889,8890,9500,9999,35000 -j ACCEPT
-A INPUT -s 10.0.0.0/8 -p tcp -m multiport --dports 8888,8889,8890,9500,9999,35000,3306 -j ACCEPT #在这里添加3306
-A INPUT -s 127.0.0.0/8 -p tcp -m multiport --dports 8888,8889,8890,9500,9999,35000 -j ACCEPT
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -s 127.0.0.1/32 -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -p tcp -m tcp --dport 32200 -j ACCEPT
-A INPUT -j DROP
COMMIT
# Completed on Thu Feb  8 09:48:16 2018
root@newbie-unknown85882:~# iptables-restore</etc/network/iptables.up.rules
#然后在slave上也执行同样的操作

root@newbie-unknown85883:~# telnet 10.83.3.102 3306
Trying 10.83.3.102...
Connected to 10.83.3.102.
Escape character is '^]'.
N
5.7.21-log
          X?CuSD�<m   yDi9 mysql_native_password
^CConnection closed by foreign host.
#在slave上测试,能连通master的3306端口。

 2、现身谬误提示

将dbdump.sql文件复制到slave

root@newbie-unknown85882:~# sz dbdump.sql
rz
 zmodem trl C ȡ

  100%     768 KB  768 KB/s 00:00:01       0 Errors
#由于我使用的两台机之前做了一些防火墙规则,现在用不了scp传输文件。可以选择用lrzsz工具将文件下载到本地,然后再上传到相应的地方。
root@newbie-unknown85882:~# scp dbdump.sql root@10.83.3.103:/tmp
ssh: connect to host 10.83.3.103 port 22: Connection refused
lost connection
root@newbie-unknown85882:~# apt-get install -y lrzsz
正在读取软件包列表... 完成
正在分析软件包的依赖关系树       
正在读取状态信息... 完成       
lrzsz 已经是最新版 (0.12.21-8)。
升级了 0 个软件包,新安装了 0 个软件包,要卸载 0 个软件包,有 86 个软件包未被升级。

root@newbie-unknown85883:/tmp# rz
rz waiting to receive.
 zmodem trl C ȡ

  100%     768 KB  768 KB/s 00:00:01       0 Errors
#已把dbdump.sql文件上传到slave。
root@newbie-unknown85883:/tmp# ls
dbdump.sql  hwcheck  systemd-private-a03ad6a9e7364c728d51dc0473e45e7c-apache2.service-eWAliD

以此错误就说club.question_del 表里面未有qdir这些字段 变成的拉长就能够了~!

在slave上操作

master.info::记录了Mysql主服务器上的日记文件和笔录位置、连接的密码。 

为MySQL加读锁

在 错误的从服务器上施行 : alter table question_del add qdir varchar(30) not null;

转移配置文件

root@newbie-unknown85883:~# vim /etc/mysql/mysql.conf.d/mysqld.cnf
#在配置文件里添加:
bind-address = 10.83.3.103 #slave ip
server-id = 2 #在master-slave架构中,每台机器节点都需要有唯一的server-id
log_bin = /var/log/mysql/mysql-bin.log #开启binlog
root@newbie-unknown85883:~# systemctl restart mysql

 7、错误提醒如下

解开master DB的读锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

从服务器上剔除掉全数的二进制日志文件,包罗二个数量目录下的master.info文件和hostname-relay-bin开始的文书。

将master DB中存活的数据导出

root@newbie-unknown85882:~# mysqldump -u root -p --all-databases --master-data > dbdump.sql
Enter password: 
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
root@newbie-unknown85882:~# ls
adduser.sh   backend2.py         cloudinit.sh  __pycache__  公共  视频  文档  音乐
backend1.py  cloudinit-Linux.sh  dbdump.sql    sarfile      模板  图片  下载  桌面

 

记录master replication log的岗位,稍后会用到

mysql> show master status;
 ------------------ ---------- -------------- ------------------ ------------------------------------------ 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
 ------------------ ---------- -------------- ------------------ ------------------------------------------ 
| mysql-bin.000001 |      617 |              |                  | 43425f23-2760-11e8-a564-52540035ed32:1-2 |
 ------------------ ---------- -------------- ------------------ ------------------------------------------ 
1 row in set (0.00 sec)

6、错误提醒如下

本文由澳门正规赌博十大网站发布于澳门游戏网站,转载请注明出处:MySQL主从同步,同步错误Query