环境:多实例

10.0.0.52 3306

10.0.0.52 3307

3306---->3307复制---->3309

   ---->3008复制

3306<---->3307

 

架构实践:

3306---->3307

1开启主库binlog,配置server-id

[root@db02 ~]# egrep -i"server-id|log-bin" /data/3306/my.cnf

log-bin = /data/3306/mysql-bin

server-id = 6       #注意id号不能一样，要区别

重启服务

开启主库binlog日志

vim /data/3306/my.cnf

pid-file = /data/3306/mysql.pid

log-bin = /data/3306/mysql-bin       ###将此行注释去掉，然后重新启动多实例

relay-log = /data/3306/relay-bin

"/data/3306/my.cnf" [dos]72L, 1824C written                              

[root@DB02 backup]#

从库

[root@db02 ~]# egrep -i"server-id|log-bin" /data/3307/my.cnf

#log-bin = /data/3307/mysql-bin

server-id = 7

2.主库创建用户

mysql> grant replication slave on *.* to 'rep'@'172.16.1.%'identified by 'oldboy123';

Query OK, 0 rows affected (0.04 sec)

mysql> select user,host frommysql.user;

+------+------------+

| user | host       |

+------+------------+

| root | 127.0.0.1  |

| rep | 172.16.1.% |

3.主库导出数据

按照我们讲过的内容,直接取今天00点的备份就可以.

mysql> flush table with read lock;      ####一般大部分情况不需要锁库，因为备份的时候已经有参数备份了。

Query OK, 0 rows affected (0.00 sec)

 

mysql> show master status; ####获取binlog日志关键点

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 |      120 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysqldump

cp/tar

xtrabackup

拿到位置点是关键

mysql-bin.000001   120

 

[root@db02 ~]# mysqldump -B --master-data=2 --single-transaction -S/data/3306/mysql.sock -A|gzip>/data/backup/all_$(date +%F).sql.gz

[root@db02 ~]# ls -l /data/backup/

总用量 228

-rw-r--r-- 1 root  root 178468 6月  28 11:11 all_2017-06-28.sql.gz

主库解锁:

mysql> unlock table;

Query OK, 0 rows affected (0.00 sec)

4.从库导入全备的数据

[root@db02 backup]# gzip -d all_2017-06-28.sql.gz

[root@db02 backup]# mysql -S /data/3307/mysql.sock <all_2017-06-28.sql

5.找位置点,然后change master从库

[root@db02 backup]# sed -n '22p'all_2017-06-28.sql

-- CHANGE MASTER TOMASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120;

进入数据库中，然后输入下面内容

CHANGE MASTER TO 

MASTER_HOST='172.16.1.52',         ###主数据库的IP地址

MASTER_PORT=3306,              ###主数据库的端口

MASTER_USER='rep',                ###连接主数据库的用户名

MASTER_PASSWORD='oldboy123',     ####连接主数据库的密码

MASTER_LOG_FILE=' oldboy-bin.000005',     ###主数据库上的binlog日志

MASTER_LOG_POS=223;                 ###主数据库的binlog日志关键点

mysql> start slave;     ###开启从库

Query OK, 0 rows affected (0.03 sec)

6.查看从库是否配置正确

mysql> show slave status\G

[root@db02 backup]# mysql -S/data/3307/mysql.sock -e "show slave status\G"|egrep"_Running|Behind_Master"|head -3

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

        Seconds_Behind_Master: 0

提示：一主多从类似于一主一从

本文转自写个博客骗钱博客51CTO博客，原文链接http://blog.51cto.com/dadonggg/1948362如需转载请自行联系原作者

菜鸟东哥