mysql数据库主从复制解析及应用

今天看了两篇关于Mysql数据库主从复制的文章，两篇文章各有优缺点，在此就本人看来https://blog.csdn.net/qq_33200967/article/details/80939040这篇文章更适合初次接触mysql主从复制技术的程序员，https://www.cnblogs.com/alinuxer/p/9890462.html这篇文章则更加详细，也使得该文章内容看起来需要更深入的了解（至少本人是这么认为的，水平有限，还请包涵），下面就两篇文章做个总结。

一、Mysql数据库主从复制概述

主从复制实需要达到的一个效果是主数据库（master）数据发生变化（包括数据变化以及表结构变化）后，从数据库（slave）也随着一起变化的这样一个目的。数据来讲，我们等于随时有一个数据库的备份，数据更安全。从性能来讲，能够实现读写分离，当从数据库（salve）发生锁表现象也不会影响主数据库写入操作

二、主从复制原理

 

以上图为例：mysql主从复制主要三个线程在工作

1、master数据库启动bin的二进制日志，这样就会有一个Dump线程开始工作，这个线程回持续将master数据库的写入操作都记录到bin日志中。

2、slave数据库启动I/O线程，改线程主要是将master数据库的bin二进制文件读取到本地并写入中继日志

3、最后slave数据库启动SQL线程，将中继日志中的事件重复执行，保证master数据库和slave数据库数据一致

三、主从复制入门操作

1、

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

主机名	任务角色	数据库
node2	192.168.204.122	master    MySQL
node3	192.168.204.123	slave    MySQL

安装好数据库以后操作如下

1 service iptables stop

查看mysql是否安装了

1 rpm -pa|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

添加配置：

default-character-set=utf-8  #设置编码方式

low_case_table_name-=  #不区分大小写

修改好的配置文件如下

[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
 

service mysqld start    启动mysql服务

chkconfig mysqld on   设置mysql开机自启

chkconfig --list | grep mysqld   查看是否成功添加到开机服务中了

mysqladmin -u root password 'root'  设置数据库密码

登录数据库设置开启远程连接

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

mysql> FLUSH PRIVILEGES;

下面重头戏来了：主从复制配置

master数据库要做的事

1. 开启bin二进制日志
2. 配置唯一的server-id
3. 获得master数据库二进制文件名和位置
4. 创建一个master和slave通信的用户账号，注意：这个账号需要开启远程访问

开启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;   #获取bin日志信息，在配置slave数据库回用到

正常情况输出如下

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

mysql> grant replication slave on *.* to 'slave'@'192.168.159.131' identified by '123456';

//ip填写从服务器ip，slave是创建的mysql用户名

Query OK, 0 rows affected (0.00 sec)

mysql> flush tables with read lock;       //锁定数据库，此时不允许更改任何数据

slave数据库配置：

1. 配置唯一的server-id
2. 使用master数据库设置的用户账号读取master数据库的bin二进制日志
3. 启动slave服务

配置server-id，编辑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

#不带任何参数，表示同事启动I/O线程和SQL线程。I/O线程从主数据库读取bin二进制日志，并存储到relat log中继日志文件中，SQL线程读取中继日志，解析后，在从库重放。      停止I/O线程和SQL线程  stop slave

mysql--> start slave;

正常情况下输出以下内容

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
 

可以通过这些信息查看配置信息是否正确，其中最重要的是以下两条信息

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

不难看出主从复制核心就是依赖这两个线程，必须保证这两个线程才能完成主从复制效果

最后将master数据库解锁

[root@localhost /]# mysql -uroot -p123456

mysql> unlock tables;

至此，主从复制的入门操作完成。

总结

mysql主从复制的核心是binlog二进制文件，一定要确保该文件生效，以下命令也可以查看是否生效

[root@centos ~]# mysql -uroot -p'123456' -S /data/3306/mysql.sock -e "show variables like 'log_bin';"    #  可以看到 binlog 功能生效。这个命令前提是master数据库的my.cnf文件设置的bin文件为：log-bin = /data/3306/mysql-bin也就是同一目录才有效

| Variable_name | Value |

| log_bin             | ON     |

master数据库需要创建一个拥有远程访问权限的账户，这里可用以下命令进行创建

[root@centos ~]# mysql -uroot -p'123456' -S /data/3306/mysql.sock  #  登录 3306。

mysql> grant replication slave on *.* to 'rep'@'192.168.136.%' identified by '123456';

mysql> flush privileges ;

提示：replication slave 为 mysql 同步的必须权限，此处不要授权 all。

//ip填写从服务器ip，slave是创建的mysql用户名

参考资料
https://www.cnblogs.com/gl-developer/p/6170423.html
https://blog.csdn.net/daicooper/article/details/79905660
https://blog.csdn.net/darkangel1228/article/details/80004222
https://blog.csdn.net/darkangel1228/article/details/80003967