mysql gtid rpm安装

怼烎@ 2022-09-23 08:10 186阅读 0赞

注意:

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

1,确认是否存在mysql用户

yum安装finger

[root@n3][root_n3]:~\# yum  install finger

查看是否存在mysql用户

[root@n3][root_n3]:~\# finger mysql

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

Never logged in.

No mail.

No Plan.

说明存在。若

[root@n3][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][root_oracle_test] ~\]\# mkdir -p /u01/app/software

\[ [root@oracle\_test][root_oracle_test] ~\]\# cd /u01/app/software/

\[ [root@oracle\_test][root_oracle_test] software\]\# ls

mysql-5.7.11-1.el6.x86\_64.rpm-bundle.tar

\[ [root@oracle\_test][root_oracle_test] software\]\# tar -xvf mysql-5.7.11-1.el6.x86\_64.rpm-bundle.tar

\[ [root@oracle\_test][root_oracle_test] software\]\# chown -R mysql:mysql mysql-\*

\[ [root@oracle\_test][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][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][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][root_oracle_test] software\]\# rpm -ivh mysql-community-common-5.7.11-1.el6.x86\_64.rpm --force

\[ [root@oracle\_test][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][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][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][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][root_T4] software\]\# yum install libnuma.so\*

\[ [root@T4][root_T4] software\]\# yum install perl

安装即可

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

\[ [root@oracle\_test][root_oracle_test] software\]\# service mysqld start

Initializing MySQL database:                               \[  OK  \]

Installing validate password plugin:                       \[  OK  \]

Starting mysqld:                                           \[  OK  \]

\[ [root@oracle\_test][root_oracle_test] software\]\# service mysqld start

Starting mysqld:                                           \[  OK  \]

获得MySQL初始密码

\[ [root@oracle\_test][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][root_localhost]: qDsrapO9W:8q

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

\[ [root@oracle\_test][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][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][root_oracle_test] software\]\# service mysqld restart

Stopping mysqld:                                           \[  OK  \]

Starting mysqld:                                             \[  OK  \]

查看gtid信息:

\[ [root@oracle\_test][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][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][root_T4] software\]\# service mysqld restart

停止 mysqld：                                              \[确定\]

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

Installing validate password plugin:                       \[确定\]

正在启动 mysqld：                                          \[确定\]

\[ [root@T4][root_T4] software\]\# service mysqld start

正在启动 mysqld：                                          \[确定\]

查看gtid状态:

\[ [root@T4][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)

[root_n3]: mailto:root@n3
[root_oracle_test]: mailto:root@oracle_test
[root_T4]: mailto:root@T4
[root_localhost]: mailto:root@localhost

mysql gtid rpm安装

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 rpm包安装mysql

相关 rpm安装mysql

相关 Centos安装mysql rpm包

相关 Centos安装MySQL(RPM安装方式)

相关 mysql gtid rpm安装

相关 linux-mysql-rpm安装方式

相关安装Mysql（rpm包安装）

相关 rpm安装mysql

相关 MySQL通过RPM安装

相关 rpm安装mysql5.7