mysql主从复制

热备份与恢复

xtrabackup手册：https://www.percona.com/doc/percona-xtrabackup/2.4/installation/yum_repo.html

主热备份

xtrabackup --defaults-file=/usr/local/mysql/my.cnf --user=root --password=root --port=3306 --backup --target-dir=/home/vagrant/code/data/ytest

从恢复备份

停止mysql:systemctl stop mysql.service

xtrabackup --defaults-file=/usr/local/mysql/my.cnf --copy-back --target-dir=/home/vagrant/code/data/ytest

chown -R vagran:vagrant /data/mysql/

systemctl restart mysql.service

主从复制实现方式

主mysql

CREATE USER 'slave'@'192.168.81.%' IDENTIFIED WITH mysql_native_password BY 'slave';

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.81.%';

开启bin-log日记

主要就是下配置文件中添加如下配置

[mysqld]
log-bin=mysql-bin
server-id=1

从mysql节点

[mysqld]

server-id = 2
relay_log = /data/mysql/mysql-relay-bin
relay_log-index = /data/mysql/mysql-relay-bin.index
log_slave_updates = 1
read_only = 1

指定主节点的ip，端口，用户

change master to master_host='192.168.56.161',master_port=3306,master_user='slave',master_password='slave',master_log_file='binlog.000017',master_log_pos=969;

启动从节点

start slave;

查看状态

show slave status ;

以下为成功：

Slave_IO_Running: Connecting
Slave_SQL_Running: Yes

使用的是冷备份文件恢复的实例

mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to ......

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)