记录mysql主主备份实现方法

最新推荐文章于 2024-08-28 13:29:21 发布

Mr__fang

最新推荐文章于 2024-08-28 13:29:21 发布

阅读量1.8k

点赞数

分类专栏： mysql linux 文章标签： mysql linux

本文链接：https://blog.csdn.net/mr__fang/article/details/8692523

版权

mysql 同时被 2 个专栏收录

36 篇文章 0 订阅

订阅专栏

linux

19 篇文章 0 订阅

订阅专栏

运行mysql：mysqld_multi --defaults-extra-file=/etc/my.cnf start

添加用户：GRANT ALL ON *.* TO root@"%" IDENTIFIED BY "123456"

FLUSH PRIVILEGES;

添加my.cnf至中

//注意不能重复

server-id = 1 //******************此项不能重复*************************
log-bin=mysql-bin
binlog-ignore-db = mysql

binlog_format=mixed
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%

修改mysql主从关系：

change master to master_host='192.168.44.128',master_port=3306,master_user='root',master_password='123456',master_log_pos=107, master_log_file='mysql-bin.000006', master_connect_retry=5;


change master to master_host='192.168.44.129',master_port=3306,master_user='root',master_password='123456',master_log_pos=107, master_log_file='mysql-bin.000006', master_connect_retry=5;

change master to master_host='127.0.0.1',master_user='root', master_password='123456',master_port=3307, master_log_file='mysql-bin.000006',master_log_pos=114, master_connect_retry=5;change master to master_host='127.0.0.1',master_user='root', master_password='123456',master_port=3307, master_log_file='mysql-bin.000006',master_log_pos=114, master_connect_retry=5;change master to master_host='127.0.0.1',master_user='root', master_password='123456',master_port=3307, master_log_file='mysql-bin.000006',master_log_pos=114, master_connect_retry=5;

//注意端口和IP

master_log_pos的值

show master status\G 查看

//注意关闭防火墙

service iptables stop

chkconfig iptables off

//重启mysql服务

用show slave status/G;看一下从服务器的同步情况
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果都是yes，那代表已经在同步
往表里面写点数据测试一下看是否同步成功，如果不成功，绝对不是你的RP问题，再检查一下操作步骤！