cx_oracle主备服务器,使用xtrabackup 配置主从服务器

╰+攻爆jí腚メ 2022-10-21 01:09 143阅读 0赞

1、备份主机数据

innobackupex –user=root –password=xifenfei –defaults-file=/etc/my.cnf –stream /tmp/mysql 2>/tmp/mysql.log | gzip> /tmp/data/mysql.tar.gz

2、查看/tmp/mysql.log，记录下当前日志位置

innobackupex: MySQL binlog position: filename ‘mysql-bin.000018′, position 107

3、备份文件恢复

scp /tmp/data/mysql.tar.gz root@192.168.1.2:/tmp/

mkdir /tmp/mysql

cd /tmp/mysql

tar izxvf /tmp/mysql.tar.gz

–恢复数据库

innobackupex –apply-log –user=root –password=xifenfei /tmp/mysql

–复制到my.cnf中指定地方

innobackupex –copy-back –user=root –password=xifenfei /tmp/mysql

chmod -R mysql.mysql /var/lib/mysql

4、修改my.cnf文件

主服务器：

server-id=1

innodb\_flush\_log\_at\_trx\_commit=1

sync-binlog=1

从服务器：

server-id=2

relay-log=/var/lib/mysql/replicate

relay-log-index=/var/lib/mysql/replicate.index

read-only

7、添加复制用户(主数据库上)

GRANT REPLICATION SLAVE ON \*.\*

TO ‘repl’@’192.168.1.2′ IDENTIFIED BY ‘xifenfei’;

8、配置从数据库

CHANGE MASTER TO

MASTER\_HOST=’192.168.1.4′,

MASTER\_USER=’repl’,

MASTER\_PASSWORD=’xifenfei’,

MASTER\_LOG\_FILE=’mysql-bin.000018′,

MASTER\_LOG\_POS=107;

start slave;

9、查看主从是否正常

SHOW SLAVE STATUS\\G

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

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

Master\_Host: 192.168.1.4

Master\_User: repl

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: mysql-bin.000022

Read\_Master\_Log\_Pos: 1185

Relay\_Log\_File: replicate.000007

Relay\_Log\_Pos: 588

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

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

Relay\_Log\_Space: 1627

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

SHOW MASTER STATUS\\G

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

File: mysql-bin.000022

Position: 1185

Binlog\_Do\_DB:

Binlog\_Ignore\_DB:

1 row in set (0.00 sec)

SHOW PROCESSLIST\\G

–从服务器进程

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Id: 22

User: system user

Host:

db: NULL

Command: Connect

Time: 1136

State: Waiting for master to send event

Info: NULL

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 3. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Id: 23

User: system user

Host:

db: NULL

Command: Connect

Time: 1676370

State: Slave has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

–主服务器进程

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

Id: 14

User: repl

Host: 192.168.1.2:34594

db: NULL

Command: Binlog Dump

Time: 1207

State: Master has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

10、管理从服务器

start slave;

reset slave;

stop slave;

cx_oracle主备服务器,使用xtrabackup 配置主从服务器

发表评论取消回复

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

相关阅读

相关 PostgreSQL教程：主从（主备）故障切换

相关 redis主从配置完毕为啥在主服务器中检测不到从服务器

相关 mysql 双主热备主从复制

相关 mysql从服务器配置_mysql主从服务器配置基础教程

相关 cx_oracle主备服务器,使用xtrabackup 配置主从服务器

相关配置mysql5.5主从服务器

相关 Nginx 服务器主配置文件详解

相关阿里云服务器主从数据库配置

相关 MySQL主从介绍，配置主服务器，配置从服务器，实现主从同步

相关 mysql 服务器主从同步配置