2、 从库参数调整
-- 调整 my.cnf 参数
[root@mysql01 ~]# cat /etc/my.cnf
[root@mysql03 mysql]# cat /etc/my.cnf
[client]
password = oracle
port = 3306
socket = /data/mysql/mysql.sock
[mysqld]
server-id=26
port = 3306
socket = /data/mysql/mysql.sock
character_set_server = utf8
character_set_client = utf8
collation-server=utf8_general_ci
lower_case_table_names = 1
max_connections = 1000
datadir = /data/mysql
log_bin = /data/mysql/binarylog/binlog
log_bin_index = /data/mysql/binarylog/binlog
relay-log = /data/mysql/relaylog/relay
relay-log-index = /data/mysql/relaylog/relay
relay_log_purge = on
read-only = 1
[mysql]
default-character-set = utf8
说明：从库必须配置的参数
server-id、log_bin、relay-log、read-only
3、 主库备份
-- 主库备份目录
[root@mysql02 full]# pwd
/xtrabackup/full
-- 主库 innobackupex 备份
[root@mysql02 ~]# innobackupex --user=root --password=oracle --port=3606 /xtrabackup/full/
170611 23:55:25 Backup created in directory '/xtrabackup/full/2017-06-11_23-55-20/'
MySQL binlog position: filename 'binlog.000017', position '980'
....
170611 23:55:25 completed OK!
-- 查看备份 binlog编号 与 截止 position
[root@mysql02 2017-06-11_23-55-20]# cat xtrabackup_binlog_info
binlog.000017 980
4、 从库创建与主库相同的备份目录
[root@mysql01 ~]# mkdir -p /xtrabackup/full
[root@mysql01 ~]# chown -R mysql:mysql /xtrabackup/full/
5、 主库将备份 scp 到从库
[root@mysql02 full]# pwd
/xtrabackup/full
[root@mysql02 full]# scp -r 2017-06-11_23-55-20 10.219.24.26:/xtrabackup/full
6、 从库查看scp过来的备份
[root@mysql03 ~]# cd /xtrabackup/full/2017-06-11_23-55-20/
[root@mysql03 2017-06-11_23-55-20]# ll
total 12328
-rw-r-----. 1 root root 419 Jun 11 23:57 backup-my.cnf
-rw-r-----. 1 root root 12582912 Jun 11 23:57 ibdata1
drwxr-x---. 2 root root 4096 Jun 11 23:57 mm_repl
drwxr-x---. 2 root root 4096 Jun 11 23:57 mysql
drwxr-x---. 2 root root 4096 Jun 11 23:57 performance_schema
drwxr-x---. 2 root root 4096 Jun 11 23:57 repl
drwxr-x---. 2 root root 4096 Jun 11 23:57 test
-rw-r-----. 1 root root 18 Jun 11 23:57 xtrabackup_binlog_info
-rw-r-----. 1 root root 113 Jun 11 23:57 xtrabackup_checkpoints
-rw-r-----. 1 root root 482 Jun 11 23:57 xtrabackup_info
-rw-r-----. 1 root root 2560 Jun 11 23:57 xtrabackup_logfile
7、 主库创建同步用户
mysql> GRANT replication slave ON *.* TO 'slave26'@'%' IDENTIFIED BY 'oracle';
Query OK, 0 rows affected (0.05 sec)
8、 从库恢复主库数据
-- 从库将原有datadir文件夹重命名到新位置，并创建原文件夹
[root@mysql01 ~]# mv /data/mysql /data/mysqlbak
[root@mysql01 ~]# mkdir -p /data/mysql
-- innobackupex apply-log
[root@mysql01 ~]# innobackupex --defaults-file=/etc/my.cnf --apply-log --user=oracle \
--password=oracle --port=3606 /xtrabackup/full/2017-06-11_23-55-20/
-- innobackupex copy 恢复的文件到原来的数据位置
[root@mysql01 mysql]# innobackupex --defaults-file=/etc/my.cnf --user=root \
--copy-back /xtrabackup/full/2017-06-11_23-55-20/
170610 18:25:11 completed OK!
-- 创建binlog目录与 relaylog 目录并赋权
[root@mysql01 ~]# mkdir -p /data/mysql/binarylog
[root@mysql01 ~]# mkdir -p /data/mysql/relaylog/
[root@mysql01 mysql]# chown -R mysql:mysql /data/mysql
9、 从库配置与检测
-- 从库启动
[root@mysql01 mysql]# mysqld_safe --defaults-file=/etc/my.cnf &
-- 从库指定与主库同步的基本信息
mysql>
change master to
master_host='10.219.24.25',
master_port=3306,
master_user='slave26',
master_password='oracle',
master_log_file='binlog.000017',
master_log_pos=980;
Query OK, 0 rows affected, 2 warnings (0.04 sec)