MySQL数据库实现主从复制

  |   0 评论   |   0 浏览   |   夜雨飘零

前言

我们用的在这篇文章《在 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

标题:MySQL数据库实现主从复制
作者:夜雨飘零
地址:https://blog.doiduoyi.com/articles/1584972836923.html

评论

发表评论