MySQL主从同步和主主同步

为什么要实现数据库主从同步?

其中Master负责写操作的负载,也就是说一切写的操作都在Master上进行,而读的操作则分摊到Slave上进行。这样一来的可以大大提高读取的效率。在一般的互联网应用中,经过一些数据调查得出结论,读/写的比例大概在 10:1左右 ,也就是说大量的数据操作是集中在读的操作,这也就是为什么我们会有多个Slave的原因。但是为什么要分离读和写呢?熟悉DB的研发人员都知道,写操作涉及到锁的问题,不管是行锁还是表锁还是块锁,都是比较降低系统执行效率的事情。我们这样的分离是把写操作集中在一个节点上,而读操作其其他的N个节点上进行,从另一个方面有效的提高了读的效率,保证了系统的高可用性。


使用主从同步的好处:
1.通过增加从服务器来提高数据库的性能,在主服务器上执行写入和更新,在从服务器上向外提供读功能,可以动态地调整从服务器的数量,从而调整整个数据库的性能。
2.提高数据安全 --因为数据已复制到从服务器,从服务器可以终止复制进程,所以,可以在从服务器上备份而不破坏主服务器相应数据。

3.在主服务器上生成实时数据,而在从服务器上分析这些数据,从而提高主服务器的性能。


主从同步机制:

主服务器数据库的每次操作都会记录在二进制日志文件mysql-bin.xxx中。从服务器的I/O线程使用专用帐号登陆到主服务器中读取该二进制文件,并将文件内容写入到自己本地的中继日志relay-log文件中。然后从服务器的SQL线程会根据中继日志中的内容执行SQL语句。

这要求两台服务器有同样的初态。

当一个slaves服务器连接master服务器时,它通知从服务器在主服务器日志中读取的最后一次成功更新的位置。slave服务器接收从那时起发生的任何更新,更新数据库内容。

主从同步使得数据可以从一个数据库服务器复制到其他服务器上,在复制数据时,一个服务器充当主服务器(master),其余的服务器充当从服务器(slave)。因为复制是异步进行的,所以从服务器不需要一直连接着主服务器,从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件,可以指定复制所有的数据库,某个数据库,甚至是某个数据库上的某个表。


一.同步初态

在进行同步前要求主从服务器有相同的初始状态,在导出主数据库数据前为数据库加锁,避免同步时发生改变,

use database_name;
tables with read lock;

把主数据库数据复制到从数据库后,在主数据库执行解锁语句

unlock tables;

完成以上操作后,主从数据库就有一样的初态了。


二.配置主从同步

主服务器192.168.100.121、从服务器192.168.100.122。其中,主服务器上已有数据。

1.配置主服务器、从服务器

如果是windows系统,找到MySQL的安装目录,先关闭mysql服务,找到my.ini这个文件,修改文件内容如下:

(如果是linux系统,找到/etc/my.cnf

log-bin=mysql-bin

server-id=1

如果是windows系统,找到MySQL的安装目录,找到my.ini这个文件,修改文件内容如下:

log-bin=mysql-bin                                 #开启二进制日志
    
server-id       = 2                               #主数据库id为1,不能相同。
replicate_wild_do_table=test.%                    #只同步test库下的表
relay_log=mysqld-relay-bin                        #记录中继日志
log-slave-updates=YES                             #从服务器同步后记录日志

修改完成后重启mysql服务。

2.在主服务器数据库中查看日志记录位置

show master status

显示如下:

File: mysql-bin.000008       #当前记录的日志
Position: 83335          #日志中记录的位置
Binlog_Do_DB:   ifactoryv2
Binlog_Ignore_DB: 
Executed_Gtid_Set: c51e4370-29af-11e8-83c2-42f2e9d8d119:1-518308
3.主服务器创建允许从服务器同步数据的账户:

在主服务器数据库执行:

grant replication slave on *.* to 'user_name'@'192.168.100.122' identified by '12345';

4.从服务器开启同步

在从服务器数据库中执行:

              change master to     
              master_host='192.168.100.121',               
              master_user='user_name',    
              master_password='12345',   
              master_log_file='mysql-bin.000008',    
              master_log_pos=83335;

配置完以上后,重启从服务器mysql服务。

5.查看从服务器是否成功开启同步

执行

show slave status

显示如下:

Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.121
                  Master_User: user_name
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 1114
               Relay_Log_File: mysqld-relay-bin.000004
                Relay_Log_Pos: 1260
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: test.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1114
              Relay_Log_Space: 1563
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_
replicate_wild_do_table=test.%
relay_log=mysqld-relay-bin
log-slave-updates=YES

Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1

其中:Slave_IO_Running和Slave_SQL_Running的状态都是YES,说明主从同步开启成功。

结果:在主服务器上面做的修改都可以同步到从服务器上,在从库上修改数据不能同步到主库上,而且会导致同步出错。你也可以在从库建立一个新的数据库,里面放些和主从不相关的测试表,并且在里面操作数据,这些没有关系,不会产生什么影响。


主主同步

在上面主从同步的基础上,只要再把122服务器设置为121服务器的主服务器就能实现双主备份。

配置主服务器的my.ini,增加以下内容:

log-bin=mysql-bin                  #开启二进制日志
server-id       = 1                #设置id
replicate_wild_do_table=test.%     #只同步test库下的表
relay_log=mysqld-relay-bin         #记录中继日志
log-slave-updates=YES              #从服务器同步后记录日志

修改完后重启MySQL服务。

主服务器创建允许从服务器同步数据的账户:

在主服务器数据库执行:

grant replication slave on *.* to 'user_name'@'192.168.100.121' identified by '12345';

查看服务器二进制日志文件名与位置

show master status

显示如下:

File: mysql-bin.000016       #当前记录的日志
Position: 75          #日志中记录的位置
Binlog_Do_DB:   ifactoryv2
Binlog_Ignore_DB: 

在从服务器数据库中执行:

              change master to     
              master_host='192.168.100.122',               
              master_user='user_name',    
              master_password='12345',   
              master_log_file='mysql-bin.000016',    
              master_log_pos=75;

配置完以上后,重启从服务器mysql服务。

最后查看是否配置成功:

show slave status
其中:Slave_IO_Running和Slave_SQL_Running的状态都是YES,说明主主同步开启成功。


没有更多推荐了,返回首页