数据库丝滑迁移

数据库丝滑迁移
host1指源数据库，host2指迁移目标库主机(从库)
host1
1.备份
./innobackupex --defaults-file="/etc/my.cnf" --user="admin" --password="admin" --host="localhost" --port=3306 --socket="/tmp/mysql.sock" --no-version-check  --use-memory=8GB --rsync --tmpdir=/tmp "/home/ful"
2.压缩传输
cd /home/ful
time tar -zcvf innoback.tar.gz /home/ful/*
scp innoback.tar.gz 192.168.110.147:/home

host2
3.软件安装
(1)mysql安装
解压安装包
tar -xf Percona-Server-5.6.27-rel76.0-Linux.x86_64.ssl101.tar.gz
mv Percona-Server-5.6.27-rel76.0-Linux.x86_64.ssl101 /usr/local/
创建用户
useradd -M -s /sbin/nologin mysql
编辑实例配置文件 -- 注意设置server_id及启用binlog(主)
vi /root/my.cnf
创建数据目录
mkdir /data

(2)xtrabackup安装
rpm -ivh epel-release-6-8.noarch.rpm 
yum install
yum list | grep percona
yum install percona-xtrabackup-24

4.环境变量设置
echo "export PATH=$PATH:/usr/local/Percona-Server-5.6.27-rel76.0-Linux.x86_64.ssl101/bin">>/etc/profile
source /etc/profile

5.解压文件
cd /home
time tar -xvf innoback.tar.gz

6.数据库恢复
innobackupex --defaults-file=/root/my.cnf --apply-log /home/home/ful/2017-01-06_16-53-47/
innobackupex --defaults-file=/root/my.cnf --copy-back /home/home/ful/2017-01-06_16-53-47/

7.修改数据目录权限,将配置文件移到数据目录下
chown -R mysql:mysql /data
mv /root/my.cnf /data/my.cnf

8.启动数据
cd /usr/local/Percona-Server-5.6.27-rel76.0-Linux.x86_64.ssl101
./bin/mysqld_safe --defaults-file=/data/my.cnf &

host1
9.搭建主从  ()
主库创建复制账户并授予权限
grant replication slave,replication client on *.* to 'repl_user'@'192.168.110.%' identified by 'replpwd';
flush privileges;

host2
找到备份logfile及pos点
# cat xtrabackup_binlog_info 
mysql-bin.000036 148638080

备库执行change master to
change master to master_host='192.168.110.135',master_port=3306,master_user='repl_user',master_password='replpwd',master_log_file='mysql-bin.000036',master_log_pos=148638080;start slave;
show slave status\G

==============================================================================
问题解决:
1.slave_io_thread:Connecting

# service iptables stop
# telnet 192.168.110.135 3306
Trying 192.168.110.135...
Connected to 192.168.110.135.
Escape character is '^]'.

2.server_id
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Misconfigured master - server_id was not set'
主库没有设置server_id

反复倒腾的命令
mysqladmin -uadmin -p shutdown
cd /usr/local/Percona-Server-5.6.27-rel76.0-Linux.x86_64.ssl101
./bin/mysqld_safe --defaults-file=/etc/my.cnf &