不正经

这个屌丝很懒，什么也没留下！

热门标签

mysql gtid rpm安装_error 3081 (hy000): this operation cannot be perfo

作者：不正经 | 2024-02-28 17:10:35

踩

error 3081 (hy000): this operation cannot be performed with running replicat

注意:

负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志，这个日志记载着需要传输给从服务器的各种修改动作。因此，主服务器必须激活二进制日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限。

1,确认是否存在mysql用户

yum安装finger

root@n3:~# yum install finger

查看是否存在mysql用户

root@n3:~# finger mysql

Directory: /var/lib/mysql Shell: /bin/bash

Never logged in.

No mail.

No Plan.

说明存在。若

root@n3:~# finger as

finger: as: no such user.

说明不存在as用户

2，检查mysql默认使用的3306端口是否被占用

[root@oracle_test ~]# yum install netstat

[root@oracle_test ~]# netstat -nat |grep 3306

[root@oracle_test ~]# netstat -nat |grep LISTEN

tcp 0 0 0.0.0.0:6379 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:6010 0.0.0.0:* LISTEN

tcp 0 0 :::6379 :::* LISTEN

tcp 0 0 :::13775 :::* LISTEN

tcp 0 0 :::42065 :::* LISTEN

tcp 0 0 :::1521 :::* LISTEN

tcp 0 0 :::22 :::* LISTEN

tcp 0 0 ::1:25 :::* LISTEN

tcp 0 0 ::1:6010 :::* LISTEN

说明没有被占用

3，安装一系列步骤

[ root@oracle_test ~]# mkdir -p /u01/app/software

[ root@oracle_test ~]# cd /u01/app/software/

[ root@oracle_test software]# ls

mysql-5.7.11-1.el6.x86_64.rpm-bundle.tar

[ root@oracle_test software]# tar -xvf mysql-5.7.11-1.el6.x86_64.rpm-bundle.tar

[ root@oracle_test software]# chown -R mysql:mysql mysql-*

[ root@oracle_test software]# ll

total 858408

-rw-r--r-- 1 mysql mysql 439500800 May 17 02:44 mysql-5.7.11-1.el6.x86_64.rpm-bundle.tar

-rw-r--r-- 1 mysql mysql 24176228 Feb 2 17:31 mysql-community-client-5.7.11-1.el6.x86_64.rpm

-rw-r--r-- 1 mysql mysql 333480 Feb 2 17:31 mysql-community-common-5.7.11-1.el6.x86_64.rpm

-rw-r--r-- 1 mysql mysql 3854020 Feb 2 17:31 mysql-community-devel-5.7.11-1.el6.x86_64.rpm

-rw-r--r-- 1 mysql mysql 38247128 Feb 2 17:31 mysql-community-embedded-5.7.11-1.el6.x86_64.rpm

-rw-r--r-- 1 mysql mysql 130605584 Feb 2 17:32 mysql-community-embedded-devel-5.7.11-1.el6.x86_64.rpm

-rw-r--r-- 1 mysql mysql 2237704 Feb 2 17:32 mysql-community-libs-5.7.11-1.el6.x86_64.rpm

-rw-r--r-- 1 mysql mysql 1722544 Feb 2 17:32 mysql-community-libs-compat-5.7.11-1.el6.x86_64.rpm

-rw-r--r-- 1 mysql mysql 141256272 Feb 2 17:33 mysql-community-server-5.7.11-1.el6.x86_64.rpm

-rw-r--r-- 1 mysql mysql 97051252 Feb 2 17:34 mysql-community-test-5.7.11-1.el6.x86_64.rpm

4，rpm方式安装

查看CentOS自带MySQL 5.1组件并卸载

[ root@oracle_test software]# rpm -qa | grep -i mysql

mysql-libs-5.1.73-5.el6_6.x86_64

mysql-community-common-5.7.11-1.el6.x86_64

卸载MySQL 5.1组件

[root@oracle_test software]# rpm -e --nodeps mysql-libs-5.1.73-5.el6_6.x86_64

安装依赖（MySQL 5.5并不需要这个依赖，但是5.7必须要，否则server组件无法安装成功）

yum -y install numactl

按顺序安装

[ root@oracle_test software]# rpm -ivh mysql-community-common-5.7.11-1.el6.x86_64.rpm

warning: mysql-community-common-5.7.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing... ########################################### [100%]

1:mysql-community-common ########################################### [100%]

也可以

[ root@oracle_test software]# rpm -ivh mysql-community-common-5.7.11-1.el6.x86_64.rpm --force

[ root@oracle_test software]# rpm -ivh mysql-community-libs-5.7.11-1.el6.x86_64.rpm

warning: mysql-community-libs-5.7.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing... ########################################### [100%]

1:mysql-community-libs ########################################### [100%]

[ root@oracle_test software]# rpm -ivh mysql-community-client-5.7.11-1.el6.x86_64.rpm

warning: mysql-community-client-5.7.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing... ########################################### [100%]

1:mysql-community-client ########################################### [100%]

[ root@oracle_test software]# rpm -ivh mysql-community-server-5.7.11-1.el6.x86_64.rpm

warning: mysql-community-server-5.7.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing... ########################################### [100%]

1:mysql-community-server ########################################### [100%]

若出现

[ root@T4 software]# rpm -ivh mysql-community-server-5.7.11-1.el6.x86_64.rpm

warning: mysql-community-server-5.7.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

error: Failed dependencies:

/usr/bin/perl is needed by mysql-community-server-5.7.11-1.el6.x86_64

libnuma.so.1()(64bit) is needed by mysql-community-server-5.7.11-1.el6.x86_64

perl(File::Path) is needed by mysql-community-server-5.7.11-1.el6.x86_64

perl(Getopt::Long) is needed by mysql-community-server-5.7.11-1.el6.x86_64

perl(POSIX) is needed by mysql-community-server-5.7.11-1.el6.x86_64

perl(strict) is needed by mysql-community-server-5.7.11-1.el6.x86_64

是因为系统缺少perl和libnuma.so*

[ root@T4 software]# yum install libnuma.so*

[ root@T4 software]# yum install perl

安装即可

启动MySQL（下面的命令请执行两次。非常奇怪，不知为什么第一次一定失败，第二次却一定成功）

[ root@oracle_test software]# service mysqld start

Initializing MySQL database: [ OK ]

Installing validate password plugin: [ OK ]

Starting mysqld: [ OK ]

[ root@oracle_test software]# service mysqld start

Starting mysqld: [ OK ]

获得MySQL初始密码

[ root@oracle_test software]# grep 'temporary password' /var/log/mysqld.log

2016-05-16T19:55:52.577402Z 1 [Note] A temporary password is generated for root@localhost: qDsrapO9W:8q

使用上一步获得的密码登录MySQL，并修改初始密码

[ root@oracle_test software]# mysql -uroot -p'qDsrapO9W:8q'

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.7.11

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>

修改密码为，Password$1

mysql> alter user 'root'@'localhost' identified by 'Password$1';

Query OK, 0 rows affected (0.00 sec)

之后还可以放开防火墙、设定自启动、增强MySQL安全设置等。

iptables -I INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

/etc/init.d/iptables save

chkconfig mysqld on

mysql_secure_installation

主从GTID模式安装

主库

[ root@oracle_test software]# vi /etc/my.cnf

添加

binlog-format = ROW

log-bin = master-bin

log-bin-index = master-bin.index

log-slave-updates = true 此种模式从库日志才能被ogg捕捉

gtid-mode = on

enforce-gtid-consistency = true

master-info-repository = TABLE

relay-log-info-repository = TABLE

sync-master-info = 1

slave-parallel-workers = 2

binlog-checksum = CRC32

master-verify-checksum = 1

slave-sql-verify-checksum = 1

binlog-rows-query-log_events = 1

report-host = 172.16.57.53

server-id = 1

关于以上参数的解释

GTID的作用

根据GTID可以知道事务最初是在哪个实例上提交的

GTID的存在方便了Replication的Failover

重启数据库

[ root@oracle_test software]# service mysqld restart

Stopping mysqld: [ OK ]

Starting mysqld: [ OK ]

查看gtid信息:

[ root@oracle_test software]# mysql -uroot -p'Password$1'

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.11-log MySQL Community Server (GPL)

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> show global variables like '%GTID%';

+----------------------------------+-------+

| Variable_name | Value |

+----------------------------------+-------+

| binlog_gtid_simple_recovery | ON |

| enforce_gtid_consistency | ON |

| gtid_executed | |

| gtid_executed_compression_period | 1000 |

| gtid_mode | ON |

| gtid_owned | |

| gtid_purged | |

| session_track_gtids | OFF |

+----------------------------------+-------+

8 rows in set (0.00 sec)

主服务器配置同步复制帐号

mysql> grant replication slave on *.* to 'repl'@'%' identified by 'Password$1';

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

从库

[ root@T4 software]# vi /etc/my.cnf

添加

binlog-format = ROW

log-bin = mysql-bin

relay-log = slave-relay-bin

relay-log-index = slave-relay-bin.index

log-slave-updates = true

gtid-mode = on

enforce-gtid-consistency = true

master-info-repository = TABLE

relay-log-info-repository = TABLE

sync-master-info = 1

slave-parallel-workers = 2

binlog-checksum = CRC32

master-verify-checksum = 1

slave-sql-verify-checksum = 1

binlog-rows-query-log_events = 1

report-host = 172.16.57.57

server-id = 11

重启数据库:

[ root@T4 software]# service mysqld restart

停止 mysqld： [确定]

初始化 MySQL 数据库： [确定]

Installing validate password plugin: [确定]

正在启动 mysqld： [确定]

[ root@T4 software]# service mysqld start

正在启动 mysqld： [确定]

查看gtid状态:

[ root@T4 software]# mysql -uroot -p'Password$1'

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.7.11-log MySQL Community Server (GPL)

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> show global variables like '%GTID%';

+----------------------------------+--------------------------------------------+

| Variable_name | Value |

+----------------------------------+--------------------------------------------+

| binlog_gtid_simple_recovery | ON |

| enforce_gtid_consistency | ON |

| gtid_executed | ebd02802-1b2a-11e6-a990-52540034e4ef:1-135 |

| gtid_executed_compression_period | 1000 |

| gtid_mode | ON |

| gtid_owned | |

| gtid_purged | |

| session_track_gtids | OFF |

+----------------------------------+--------------------------------------------+

8 rows in set (0.00 sec)

连接主Mysql,配置主从

(1) 连接主数据库

在从库运行

mysql> CHANGE MASTER TO MASTER_HOST='172.16.57.53',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='Password$1',MASTER_AUTO_POSITION=1;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

若出现

mysql> CHANGE MASTER TO MASTER_HOST='172.16.57.53',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='Password$1',MASTER_AUTO_POSITION=1;

ERROR 3081 (HY000): This operation cannot be performed with running replication threads; run STOP SLAVE FOR CHANNEL '' first

解决方法：

1.停掉slave服务

mysql> stop slave;

Query OK, 0 rows affected (2.01 sec)

2.重置slave服务

mysql> reset slave;

Query OK, 0 rows affected (0.01 sec)

开启服务

mysql> start slave ;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.57.53

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000001

Read_Master_Log_Pos: 589

Relay_Log_File: slave-relay-bin.000002

Relay_Log_Pos: 804

Relay_Master_Log_File: master-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

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: 589

Relay_Log_Space: 1011

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: 0

Master_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: 31cf20aa-1ba0-11e6-af7a-52540032a247

Master_Info_File: mysql.slave_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: 31cf20aa-1ba0-11e6-af7a-52540032a247:1-2

Executed_Gtid_Set: 31cf20aa-1ba0-11e6-af7a-52540032a247:1-2,

ebd02802-1b2a-11e6-a990-52540034e4ef:1-135

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

ERROR:

No query specified

说明成功

验证同步情况

(1) 主数据库创建一个数据库

mysql> create database fafa;

Query OK, 1 row affected (0.00 sec)

mysql> show master status\G;

*************************** 1. row ***************************

File: master-bin.000001

Position: 748

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: 31cf20aa-1ba0-11e6-af7a-52540032a247:1-3

1 row in set (0.00 sec)

ERROR:

No query specified

查看所建的表是否存在

主库

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| fafa |

| mysql |

| performance_schema |

| sys |

+--------------------+

5 rows in set (0.00 sec)

从库

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| fafa |

| mysql |

| performance_schema |

| sys |

+--------------------+

5 rows in set (0.00 sec)

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/不正经/article/detail/160415