mysql 5.7 主主配置文件_MySQL 5.7主主复制配置

傷城~ 2022-10-27 02:17 119阅读 0赞

一、主机规划

角色　　主机名　　　　IP地址

主库　　master　　192.168.0.130

从库　　slave 　     192.168.0.140

二、安装 MySQL 5.7

1.卸载两台主机mysql旧的相关软件包

\# rpm -qa | grep -i mysql

\# yum remove  -y mysql-\*

2.在两台主机安装MySQL 5.7

\# wget http://dev.mysql.com/downloads/repo/yum/mysql57-community-release-el6-8.noarch.rpm

\# rpm -ivh mysql57-community-release-el6-8.noarch.rpm

\# yum -y install mysql.x86\_64 mysql-server.x86\_64 mysql-devel.x86\_64

3.在两台主机初始化MySQL 5.7

\# mkdir /data/mysql

\# chown -R mysql:mysql /data/mysql

\# mysqld --initialize-insecure --user=mysql --datadir=/data/mysql

4.在master主机的/etc/my.cnf中配置以下内容

\[mysqld\]

datadir=/data/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

server-id = 1

auto-increment-increment = 2

auto-increment-offset = 1

explicit\_defaults\_for\_timestamp = 1

character\_set\_server=utf8

interactive\_timeout = 57600

log-bin = master-bin

log-bin-index=master-bin.index

expire-logs-days = 100

replicate-do-db  = test                \#需要同步的数据库

binlog-ignore-db  = mysql　　　\#需要忽略的数据库

binlog-ignore-db  = information\_schema

slave-skip-errors=all

log-slave-updates

symbolic-links=0

skip-name-resolve

5.在slave主机的/etc/my.cnf中配置以下内容

\[mysqld\]

datadir=/data/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

server-id = 2

auto-increment-increment = 2

auto-increment-offset = 2

character\_set\_server=utf8

interactive\_timeout = 57600

log-bin =masterl-bin

expire-logs-days = 100

replicate-do-db = test                  \#需要同步的数据库

binlog-ignore-db = mysql

binlog-ignore-db = information\_schema

slave-skip-errors=all

log-slave-updates

symbolic-links=0

skip-name-resolve

5.在master、slave主机的重启MySQL5.7服务

\# service mysqld restart

6.在master主机创建数据库和表并插入数据

\# mysql -uroot -p

mysql > create database test;

mysql > use test;

mysql > create table test(id int,name varchar(100));

mysql > insert into wclwcw value (1,'tom');

7.互相授权

7.1 在主机master授权

grant replication slave on \*.\* to 'repl'@'192.168.0.140' identified by 'mysql';

flush privileges;

7.2 在主机master授权

grant replication slave on \*.\* to 'repl'@'192.168.0.130' identified by 'mysql';

flush privileges;

8、互告bin-log信息

8.1 在master主机上

mysql > show master status;

\+------------------+----------+--------------+--------------------------+

\+------------------+----------+--------------+--------------------------+

\+------------------+----------+--------------+--------------------------+

8.2 在主机slave上

mysql> show master status;

\+------------------+----------+--------------+--------------------------+

\+------------------+----------+--------------+--------------------------+

\+------------------+----------+--------------+--------------------------+

8.3 在master上

mysql > change master to master\_host='192.168.0.140',master\_user='test',master\_password='mysql',master\_log\_file='mysql-bin.000008',master\_log\_pos=194;

8.4 在slave上

mysql > change master to master\_host='192.168.0.130',master\_user='test',master\_password='mysql',master\_log\_file='mysql-bin.000007',master\_log\_pos=1082;

9、在master、slave两服务器都执行以下命令

mysql > start slave;

10、查看状态

10.1 在主机master上

mysql> show slave status\\G

Slave\_IO\_State: Waiting for master to send event

Master\_Host: 192.168.0.140

Master\_User: zz

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: mysql-bin.000002

Read\_Master\_Log\_Pos: 778

Relay\_Log\_File: template-relay-bin.000002

Relay\_Log\_Pos: 780

Relay\_Master\_Log\_File: mysql-bin.000002

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

Replicate\_Do\_DB: wang

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

Relay\_Log\_Space: 990

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

Master\_UUID: e6a7bb8f-4fe6-11e6-abd0-fa163e5cb863

Master\_Info\_File: /home/mysql/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:

10.2 在主机slave上

mysql> show slave status \\G；

Slave\_IO\_State: Waiting for master to send event

Master\_Host: 192.168.1.2

Master\_User: zz

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: mysql-bin.000002

Read\_Master\_Log\_Pos: 769

Relay\_Log\_File: template-relay-bin.000002

Relay\_Log\_Pos: 484

Relay\_Master\_Log\_File: mysql-bin.000002

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

Replicate\_Do\_DB: wang

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

Relay\_Log\_Space: 694

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: 10824007-4fe4-11e6-a4ff-fa163ea94ff8

Master\_Info\_File: /home/mysql/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\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

说明已经配置成功了，同时查看主机2中是否已经有主机1中的数据库

其他

同步数据

用test做的实验，导出将test.sql文件从1.4服务器拷贝到1.2服务器

备份数据前先锁表，保证数据一致性

mysql> FLUSH TABLES WITH READ LOCK;

\# mysqldump -uroot -p123456 test> /tmp/test.sql;

mysql> UNLOCK TABLES;

scp /tmp/test.sql root@192.168.1.2:/tmp

在搭建mysql master-slave复制环境时，一切都正常，但是在slave上执行：show slave status\\G; 时，出现下面的状况：

Slave\_IO\_Running: Connecting

Slave\_SQL\_Running: Yes

mysql 5.7 主主配置文件_MySQL 5.7主主复制配置

发表评论取消回复

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

相关阅读

相关 Docker：Mysql主主复制

相关 mysql主主复制架构_搭建MySQL的主从、半同步、主主复制架构

相关 mysql 5.7 主主配置文件_MySQL 5.7主主复制配置

相关 keepalive 配合mysql主主复制

相关 MySQL 主主同步配置步骤

相关 MySQL 数据库主主复制的配置

相关 MySQL主主配置

相关 mysql主主同步的配置

相关 MariaDB/Mysql的主主复制(主主互备)部署(Docker)

相关 MySQL 主主配置