mysql加入新的从节点怎么配置_添加MySQL 5.6 从节点 Slave

mysql版本：5.6.36xtraback版本：version 2.4.61、修改主节点MySQL配置文件，并重启MySQLcat /usr/local/mysql/my.cnf[mysqld]datadir = /var/lib/mysqlsocket = /tmp/mysql.sockbinlog-format=ROWlog-slave-updates=truegtid-mode=onenf

被ldy取笑

919人浏览 · 2021-02-02 06:09:52

被ldy取笑 · 2021-02-02 06:09:52 发布

mysql版本：5.6.36

xtraback版本：version 2.4.6

1、修改主节点MySQL配置文件，并重启MySQL

cat /usr/local/mysql/my.cnf

[mysqld]

datadir = /var/lib/mysql

socket = /tmp/mysql.sock

binlog-format=ROW

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

log-bin=mysql-bin

expire_logs_days=2

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-port=3306

port=3306

server_id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

2、使用xtrabackup全备MySQL，并将备份集拷贝到从节点

innobackupex --defaults-file=/usr/local/mysql/my.cnf --slave-info --user=root --password=mg@Xa9382cs%acd --socket=/tmp/mysql.sock /root

3、修改从节点MySQL配置文件，并重启MySQL

cat /usr/local/mysql/my.cnf

[mysqld]

datadir = /var/lib/mysql

socket = /tmp/mysql.sock

relay-log = relay-log

relay-log-index = relay-log.index

binlog-format=ROW

log-bin=mysql-bin

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-port=3306

port=3306

server_id = 10

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

4、在从节点使用xtrabackup恢复数据库(命令手动输入，不能直接复制)

innobackupex --defautls-file=/usr/local/mysql/my.cnf --apply-log --redo-only /root/2017-07-10_16-54-29

innobackupex --defautls-file=/usr/local/mysql/my.cnf --apply-log /root/2017-07-10_16-54-29

innobackupex --defautls-file=/usr/local/mysql/my.cnf --socket=/tmp/mysql.sock --copy-back /root/2017-07-10_16-54-29

5、在主节点创建复制专用账号

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'10.10.45.155' IDENTIFIED BY '123456';

mysql> flush privileges;

6、查看slave复制信息

cat /var/lib/mysql/xtrabackup_binlog_pos_innodb

mysql-bin.000003 124800463

7、将从节点添加到主节点中

mysql> change master to master_host='10.10.45.154', master_port=3306,master_user='repluser',master_password='mg@Xa9382cs%acd',master_log_file='mysql-bin.000003',master_log_pos=124800463;

mysql> start slave;

mysql> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 10.10.45.154

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 1073587699

Relay_Log_File: relay-log.000009

Relay_Log_Pos: 1073587202