RJ博客

Mysql主从配置(两台异地机器)

本文目录

例如,我们此时有2台服务器分别为:

124.156.168.243 --- Master 香港

115.159.161.69   --- Slave   上海

1) Mysql默认是不允许远程连接的首先打开每个服务器的远程访问权限确认服务器间数据库可以互相访问。

--Master打开远程访问给Slave访问
grant all on *.* to 'root'@'115.159.161.69' identified by '密码' with grant option;
--刷新权限配置
flush privileges;

2) 修改Master(主库)配置文件默认安装的配置文件一般在 /etc/my.cnf:

vim /etc/my.cnf

在[mysqld]节点加上如下配置:

[mysqld]
server-id=1
log-bin=master-bin
log-bin-index=master-bin.index
#只同步test数据库(可选配置)
binlog-do-db=test

3) 重启Mysql服务

service mysqld restart

4) 查看并记录master的信息

mysql> show master status; 
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000008 |      120 | test         |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

这时Master库已经配置完成了接下来配置Slaver库:

1) 同样的,先配置/etc/my.cnf与Master不同的只有server-id一项

server-id=10
log-bin=master-bin
log-bin-index=master-bin.index

2) 重启Mysql服务

service mysqld restart

3) 关闭从库同步

mysql> stop slave;

4) 连接Mysql配置Master信息并开启Slave (密码按实际情况修改)

mysql> change master to master_host='124.156.168.243',master_user='root',master_password='******',master_log_file='master-bin.000008',master_log_pos=120;

5) 开启从库同步

mysql> start slave;

6) 查看slave状态:

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 124.156.168.243
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000008
          Read_Master_Log_Pos: 342
               Relay_Log_File: VM_74_206_centos-relay-bin.000005
                Relay_Log_Pos: 506
        Relay_Master_Log_File: master-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
             ..........

需要注意的是

Slave_IO_Running:  Yes
Slave_SQL_Running: Yes

两项要同时为Yes同步服务才是正常状态。可以看到我们配置有一项没成功:

Slave_IO_Running: No

查看slave的error log,发现有报错,报错信息十分明了:

[root@VM_74_206_centos /root]#tail /var/log/mysqld.log 
2019-06-30 16:02:59 17215 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Error_code: 1593

由于我的香港云服务器是克隆上海服务器的镜像MySQL server UUIDs一样启动,所以开启主从同步时会报错。

解决办法:

[root@VM_74_206_centos /root]#find / -name auto.cnf
/data/mysql/auto.cnf
[root@VM_74_206_centos /root]#cat /data/mysql/auto.cnf
[auto]
server-uuid=07adc29f-1b4d-11e6-84e2-525400e6265e

[root@VM_74_206_centos /root]#mv /data/mysql/auto.cnf /data/mysql/auto.cnf.bak
[root@VM_74_206_centos /root]#service mysqld restart
[root@VM_74_206_centos /root]#cat /data/mysql/auto.cnf
[auto]
server-uuid=2428a1bb-9b0f-11e9-95f9-525400e6265e

可以看到重启后Slave的MySQL server UUIDs已经变了,重新启动MySQL。

Slave_IO_Running:  Yes
Slave_SQL_Running: Yes

事先两台服务器已经建了test数据库现在往里面新建表插入数据测试成功Slave自带同步Master插入的数据。配置如果需要配置多个从库,添加方法和是上面的Slave完全一样的只要在配置my.cnf时,将server-id少做修改即可比如

server-id=11

另外每次重启Master数据库, 日志文件master-bin.000008 都是会变动(目前观察室是,自增+1,不要不要紧, Slave是自动识别Connecting并更新自己的配置信息

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Reconnecting after a failed master event read
                  Master_Host: 124.156.168.243
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000008
          Read_Master_Log_Pos: 342
               Relay_Log_File: VM_74_206_centos-relay-bin.000007
                Relay_Log_Pos: 506
        Relay_Master_Log_File: master-bin.000008
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes


mysql主从复制跳过复制错误

#停止复制
mysql>slave stop;
#设定跳过一个事务
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1
#重新开启复制
mysql>slave start
#这样就正常了,但是,当然还是要把数据修改上去
mysql>update tables set 。。。。。。。


其他方面需要注意:

1、slave日志记录读的pos要和master日志记录的pos一致,避免数据同步不一致 

2、如果之前master已经有数据了,可以通过dump先把数据备份到从库,然后从库再从master的pos同步数据

mysqldump -uroot -p****** --all-databases > /home/test.sql


附加:

我们一定要保证在配置主从的时候两个数据库是完全一样的

1.指定要同步的数据库,修改主配置文件

vim /etc/my.conf

    binlog-do-db=db1,db2     #用来指定需要同步的库
    binlog-ignore-db=db1,db2   #指定忽略不同步的库

或者在从上修改配置文件

vim /etc/my.conf

    replicate-do-db=db1,db2    #用来指定需要同步的库
    replicate-ignore-db=db1,db2  #指定忽略不同步的库保证主从要同步的数据库文件完全一样

在主上下载db1的数据库文件

flush tables with read lock;

#这个好像为了保持数据同步,关闭所有打开的表,并给所有的表都家加上一个只读锁,直到主执行unlock tables

mysqldump -uroot  -p****** db1 > db1.sql

远程拷贝给从,然后在从上导入给自己的数据库(保证有数据库db1)

mysql -uroot  db1 < db1.sql

然后打开主的read lock

mysql -uroot -p****** -e "unlock tables"



Refer:

https://www.jianshu.com/p/29b0fc835f27

https://www.cnblogs.com/xusx/articles/6597748.html

https://www.e-learn.cn/content/mysql/2355603  mysql主从只同步部分库或

https://www.cnblogs.com/paul8339/p/8177627.html mysql主从复制跳过错误 


相关推荐

发表评论

  • 欢迎评论

  • 123456

  • Hello, did you know that there are 241,120 internet directories in the world. These websites are what drive traffic to YOUR business. Want more traffic? Want more Sales? We can help - today. Your website nearby.wang is listed in only 91 of these directories. Get more traffic for your Global audience. Our automated system adds your website to all of the directories. You can find it here: getlisted.directory/nearby.wang Act today, and we will expedite your listings and waive the processing charge! We have a special going on. Use "FRIENDS" on checkout for a 50% discount valid today.

  • Let me just say your site is amazing! It is well put together and easy to navigate which is a plus. With such a nice layout you must attract a lot of visitors. I just wanted to give you a heads up because your site inspired me to build my own. I hope everything is going great and much success in your future. Thank and have the best of day!

  • Hello, It is with sad regret to inform you that BestLocalData.com is shutting down. We have made all our databases for sale for a once-off price. Visit our website to get the best bargain of your life. BestLocalData.com Regards, Aidan

  • You Won't Want To Miss This! 50 pcs medical surgical masks only $1.99 and N95 Mask $1.79 each. Special Offer for the next 48 Hours ONLY! Get yours here: pharmacyusa.online All the best, RJ博客 | By:finallylly | Mysql主从配置(两台异地机器)

  • It is with sad regret to inform you StarDataGroup.com is shutting down. It has been a tough year all round and we decided to go out with a bang! Any group of databases listed below is $49 or $149 for all 16 databases in this one time offer. You can purchase it at www.StarDataGroup.com and view samples. - LinkedIn Database 43,535,433 LinkedIn Records - USA B2B Companies Database 28,147,835 Companies - Forex Forex South Africa 113,550 Forex Traders Forex Australia 135,696 Forex Traders Forex UK 779,674 Forex Traders - UK Companies Database 521,303 Companies - German Databases German Companies Database: 2,209,191 Companies German Executives Database: 985,048 Executives - Australian Companies Database 1,806,596 Companies - UAE Companies Database 950,652 Companies - Affiliate Marketers Database 494,909 records - South African Databases B2B Companies Database: 1,462,227 Companies Directors Database: 758,834 Directors Healthcare Data