主庫IP:192.168.56.111 主機名:ace
備庫IP:192.168.56.123 主機名:luffy
MySQL版本:5.6
1、確保主庫的binlog打開,確保主從庫的server-id不相同
[root@ace mysql]# egrep 'log-bin|server-id' < /etc/my.cnf
log-bin=/var/lib/mysql/binlog
server-id=1
[root@luffy ~]# egrep 'log-bin|server-id' < /etc/my.cnflog-bin=/var/lib/mysql/binlogserver-id=2
注:我這里備庫的binlog也打開了,如果不做鏈式同步的話備庫binlog可不必打開。
2、在主庫創建備庫復制時使用的用戶
mysql> create user 'rep'@'%' identified by 'rep';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'rep'@'%';
Query OK, 0 rows affected (0.00 sec)在備庫進行登陸測試
[root@luffy ~]# mysql -urep -prep -h192.168.56.111
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.6.30-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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>
登陸成功。
3、主庫鎖表后進行全備
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1.aaa values (123123);
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock鎖表成功
然后備份