MySQL 利用停机冷备异地恢复并搭建主从复制

本文将从主库master停机冷备，然后在从库slave恢复并搭建主从复制。
1.主库停机冷备，数据目录为/mysqlData/data,日志目录为/mysqlLog/logs，参数文件为/etc/my.cnf

[root@master ~]# service mysql stop
Shutting down MySQL..                                      [  OK  ]
[root@master ~]# ps -ef|grep mysql
root      4820  4761  0 16:29 pts/1    00:00:00 grep mysql

--打包并传输，需要备份的文件为数据目录，redo log,undo log，ibdata，my.cnf
[root@master logs]# scp /tmp/data.tar.gz root@192.168.204.129:/tmp
root@192.168.204.129's password: 
data.tar.gz                                                                                                                         100%   51MB  50.9MB/s   00:01    
[root@master logs]# scp /etc/my.cnf root@192.168.204.129:/tmp
root@192.168.204.129's password: 
my.cnf 

--开启主库实例

2.从库利用冷备恢复

--将my.cnf放到/etc目录下，并修改server_id，避免与主库冲突
[root@slave mysqlData]# cp /tmp/my.cnf /etc/my.cnf
[root@slave mysqlData]# vi /etc/my.cnf

--解压冷备
[root@slave tmp]# tar -zxvf data.tar.gz -C /
mysqlData/data/
mysqlData/data/performance_schema/
...

--根据my.cnf创建相关目录
[root@slave data]# cat /etc/my.cnf
log-error=/mysqlLog/logs/error.log
datadir=/mysqlData/data
tmpdir=/mysqlData/tmp

[root@slave data]# mkdir -p /mysqlLog/logs
[root@slave data]# mkdir -p /mysqlData/tmp
[root@slave data]# chown mysql:mysql -R /etc/my.cnf
[root@slave data]# chown mysql:mysql -R /mysqlData/
[root@slave data]# chown mysql:mysql -R /mysqlLog/

--启动数据实例
[root@slave data]# service mysql start
Starting MySQL.2019-05-07T08:59:25.993526Z mysqld_safe error: log-error set to '/mysqlLog/logs/error.log', however file don't exists. Create writable for user 'mysql'.
The server quit without updating PID file (/usr/local/mysql[FAILED]id).
[root@slave data]# cd /mysqlLog/logs/
[root@slave logs]# ls
[root@slave logs]# touch error.log
[root@slave logs]# chown mysql:mysql -R error.log 
[root@slave logs]# service mysql start
Starting MySQL..                                           [  OK  ]
[root@slave logs]# ls
error.log  mysql-bin.000001  mysql-bin.index

3.配置主从复制

[root@master logs]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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 master status\G
*************************** 1. row ***************************
             File: mysql-bin.017726
         Position: 194
     Binlog