MySQL数据库实现主从复制

前言

我们用的在这篇文章《在CentOS上使用Nginx和Tomcat搭建高可用高并发网站》使用的只有一个MySQL数据库。

  • 从安全角度来说这是非常不安全的,比如这个数据库服务器磁盘突然损坏了,里面的数据全部丢失了。这种情况如果一开始只是部署一个数据库的话就非常危险了,这表明我们要丢失全部数据,而数据对网站来说是最最重要的,所以我们要保证数据的安全。
  • 从性能上来说,我们在《CentOS下安装和使用Mycat实现分布式数据库》这篇文章中介绍了分布式数据库,性能虽然比单个数据库的性能要好。但是当使用分布式数据库时,数据量还是很大,在查询数据时,可能会变得非常慢,导致锁表,一旦锁表就无法写入数据,就会影响其他用户的写入数据的操做。所以就需要读写分离,主(master)数据库负责写入数据,从(slave)数据库负责查询数据,就算从(slave)数据库在查询数据时出现了锁表,也不会影响到主(master)数据库的的写入操作,最多也是从(slave)数据库的数据更新的慢一些。基于这种情况,就出现了主从复制这个技术。

主从复制:就是有两个数据库服务器,一个是主(master)数据库服务器,另一个是从(slave)数据库服务器。当主(master)数据库有数据写入,包括插入、删除、修改,都会在从(slave)数据库上操作一次。这样的操作下,主从(slave)数据库的数据都是一样的,就相当于时刻在做数据备份,就算主(master)数据库的数据全部丢失了,还有从(slave)数据库的数据,我们就可以把从(slave)数据库的数据导出来进行数据恢复。

主从复制原理主要有三个线程不断在工作:

  1. 主(master)数据库启动bin二进制日志,这样会有一个Dump线程,这个线程是把主(master)数据库的写入操作都会记录到这个bin的二进制文件中。
  2. 然后从(slave)数据库会启动一个I/O线程,这个线程主要是把主(master)数据库的bin二进制文件读取到本地,并写入到中继日志(Relay log)文件中。
  3. 最后从(slave)数据库其他SQL线程,把中继日志(Relay log)文件中的事件再执行一遍,更新从(slave)数据库的数据,保持主从数据一致。

安装MySQL

要实现主从复制就需要两台数据库服务器,可以参考这篇文章《在CentOS上使用Nginx和Tomcat搭建高可用高并发网站》安装两个CentOS虚拟机。它们的信息如下:

主机名

IP地址

任务角色

数据库

node2

192.168.204.122

master

MySQL

node3

192.168.204.123

slave

MySQL

然后我们在node2和node3机器上安装MySQL数据库。操作如下:

首先关闭防火墙,方便之后的操作。

service iptables stop

我们可以使用以下命令查看MySQL是否安装了:

rpm -qa | grep mysql

应该会输出一下日志:

[root@localhost ~]# rpm -qa | grep mysql
mysql-libs-5.1.71-1.el6.x86_64

然后我们可以先移除这个MySQL,重新安装一个:

yum -y remove mysql-libs-5.1.71-1.el6.x86_64

移除之前的MySQL之后,可以重新安装MySQL:

yum -y install mysql-server mysql mysql-devel

最后再查看安装情况:

rpm -qa | grep mysql

正常的应该会输出以下信息:

mysql-5.1.73-8.el6_8.x86_64
mysql-libs-5.1.73-8.el6_8.x86_64
mysql-server-5.1.73-8.el6_8.x86_64
mysql-devel-5.1.73-8.el6_8.x86_64

安装完成之后,我们可以对MySQL数据库进行一些配置:

vim /etc/my.cnf

主要也是在[mysqld]下加上下面两行代码,主要是设置编码方式和不区分字母大小写。

default-character-set=utf8    # 设置编码方式
lower_case_table_names=1      # 不区分字母大小写

修改之后的配置文件如下:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

default-character-set=utf8    # 设置编码方式
lower_case_table_names=1      # 不区分字母大小写

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

配置完成之后,我们直接启动MySQL:

service mysqld start

然后我们可以把MySQL服务添加到开机自动启,这样就不用每次都启动了。

chkconfig mysqld on

我们可以使用以下的命令查看是否成功添加到开机服务中了。

chkconfig --list | grep mysqld

设置MySQL数据库的密码:

mysqladmin -u root password 'root'

登录数据库,输入该命令之后还有输入数据库的密码,这个密码就是上面设置的root:

mysql -u root -p

为了让Mycat可以连接MySQL数据库,我们还要设置数据库支持远程连接,在登录数据库之后输入以下两条命令:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

配置主从复制

主(master)数据库主要做以下几件事情:

  • 开启bin二进制日志;
  • 配置唯一的server-id;
  • 获得主(master)数据库二进制日志文件名及位置;
  • 创建一个用于slave和master通信的用户账号,这个可以使用我们已经设置了的远程访问账号。

开启bin二进制日志和配置server-id都是在配置文件/etc/my.cnf中进行设置。在[mysqld]下加上下面两行代码,完成这两个配置:

log-bin=mysql-bin  # 开启二进制日志
server-id=1        # 设置server-id

除了配置这些重要的信息,我们还可以配置其他的信息,比如不要复制哪些数据库,或者是只是复制哪些数据库。配置如下:

# 不同步哪些数据库  
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema

# 只同步哪些数据库,除此之外,其他不同步  
# binlog-do-db = game 

整个配置完成之后是以下这个样子的:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

default-character-set=utf8   # 设置编码方式
lower_case_table_names=1     # 不区分字母大小写

log-bin=mysql-bin  # 开启二进制日志
server-id=1        # 设置server-id

# 不同步哪些数据库  
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema

# 只同步哪些数据库,除此之外,其他不同步  
# binlog-do-db = game  


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

配置完成之后,还要重启一下数据库:

service mysqld restart

现在执行第三步, 获得主(master)数据库二进制日志文件名及位置,首先登陆数据库:

[root@node2 ~]# mysql -u root -p

然后输入以下命令获取这些信息:

mysql> show master status;

正常情况下是输出以下信息,其中File和Position都是我们在设置从(slave)数据库的时候用到的:

+------------------+----------+--------------+-------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB              |
+------------------+----------+--------------+-------------------------------+
| mysql-bin.000003 |      106 |              | mysql,test,information_schema |
+------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)

从(slave)数据库配置:

  • 配置唯一的server-id;
  • 使用主(master)数据库设置的用户账号读取主(master)数据库的bin二进制日志;
  • 启用slave服务;

首先配置server-id,编辑/etc/my.cnf文件。在[mysqld]下加上下面一行代码,这个server-id不要跟之前的主(master)数据库服务器获取其他数据库一样,要唯一的:

server-id=2   # 设置server-id,必须唯一

同样要重启一下数据库:

service mysqld restart

然后登录到数据库,进行以下配置。首先是配置主(master)数据库的IP地址,用户命,登录密码,刚才在主(master)数据库中查到的bin二进制文件的名称和所在的位置。

mysql> change master to 
    -> master_host='192.168.204.122',
    -> master_user='root',
    -> master_password='root',
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=106;

配置完成之后就启动slave,命令如下:

mysql> start slave;

然后可以查看配置结果,使用以下命令查看:

mysql> show slave status\G;

正常情况下是输出以下内容的:

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.204.122
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 407
              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_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec)

ERROR: 
No query specified

可以通过这些输出信息查看配置信息是否正确,其中最重要的是以下这两条信息,必须两个都是Yse才可以使用,这两个就是从(slave)数据库的两个读取和执行SQL语句的线程。

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

到这里就已经配置完成了,我们接下来可以测试一下配置效果了。

测试配置

为了方便测试,笔者使用NavicatForMySQL连接数据库,读者可以使用自己熟悉的连接工具,或者是直接在命令行上直接操作。

首先是连接主(master)数据库:

在主(master)数据库中创建一个yeyupiaoling数据库:

并在这个数据库中创建一张user表:

然后我们在这张表中添加以下数据:

然后连接从(slave)数据库:

可以发现从(slave)数据库中已经创建了一个yeyupiaoling数据库,同时也有了一张user表,该表的数据库都是一样的。

从上面的测试来看,我们的数据是已经保证了主从数据一致了。也表命令了我门只要对主(master)数据库进行写入操作,从(slave)数据库就会作相应的操作,保证主从(slave)数据库数据一致。但我们对从(slave)数据库进行写入操作时,是不会对主(master)数据库进行改动的,在实际的情况下也不应该对从(slave)数据库进行写入操作。笔者就不在这里进行展示了,有兴趣的读者可以尝试一下。

参考资料

  1. https://www.cnblogs.com/gl-developer/p/6170423.html
  2. https://blog.csdn.net/daicooper/article/details/79905660
  3. https://blog.csdn.net/darkangel1228/article/details/80004222
  4. https://blog.csdn.net/darkangel1228/article/details/80003967

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券