记录实践MySQL主从复制(Master-Slave)的实现流程!

技术分享 2018年09月16日 阅读 386 豆浆大叔

对于发展很小的公司来说,单台服务器支撑整个公司的运转是常见的事情。但是随着用户量的增加,随之而来的数据量也会跟着增加,这就导致更多地用户会使用查询select我们的数据库,从而导致性能急剧的下降。例如,查询缓慢,用户等待时间长等等,高并发,大数据,负载均衡甚至是集群,这些方案都是小公司不会采用的,一方面成本极高,需要运维维护,这已经抵n个程序猿的工资了,所以一般不会采用这种模式。

仔细的分析一下,这种情况,其实更多地是来源于数据库mysql的承受能力变大,一方面需要读操作,另一方面有需要增删改操作,那么多人同时请求肯定受不了啦!
有人说使用缓存,但是再想想mysql的缓存是有限制的,并不是一味的去缓存就可以,又有人说使用redis缓存,存储在内存中,然而事实上运转的时候redis缓存并不能全部给你缓存,有些数据需要实时的查看,而且redis缓存过多,导致cpu直接上涨,严重的话,机器直接运转停止。

说了这么的多废话,无非是为下文做一些铺垫,不让大家一味的光想着做负载均衡啊,集群啊,没有那么高的流量 并发量 就不需要这么搞了,浪费资源,浪费RMB哦。好了,下面开始了。。。。

mysql主从复制的解析描述

主从复制是指一台服务器充当主数据库服务器(master),另一台或多台服务器充当从数据库服务器(slave),主服务器中的数据自动复制到从服务器之中。对于多级复制,数据库服务器即可充当主机,也可充当从机。MySQL主从复制的基础是主服务器对数据库修改记录二进制日志,从服务器通过主服务器的二进制日志自动执行更新。
请注意:当你进行复制时,所有对复制中的表的更新必须在主服务器上进行。否则,你必须要小心,以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。

首先准备环境:
1 两台服务器(这里还是以虚拟机做)
① 主服务器IP:192.168.153.128
② 从服务器IP:192.168.153.129
安装的mysql(安装过程不在叙述):版本号尽可能的保持一致性 本机安装版本是:mysql5.7.21

修改主服务器的mysql配置文件

1.本机的主服务器mysql配置文件地址在:etc/my.cnf

[mysqld]
#开启二进制日志
log-bin=mysql-bin 
#设置server-id
server-id=1 
#关闭名称解析(不必须项)
skip-name-resolve

创建从服务器登录主服务器的账号

上一步修改mysql配置文件,先重新启动mysql
systemclt restart mysqld
#登录mysql 命令:mysql -uroot -proot123 进入
#创建用户
mysql> create user 'root1'@'192.168.153.129' identified by 'root123';
#分配权限
mysql> grant replication slave on *.* to 'root1'@'192.168.153.129';
#刷新权限
mysql>flush privileges;

效果图:

查看主服务器(master)二进制日志状态

mysql> show master status;#查看状态命令
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      948 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

效果图:

解析下参数:
① File->mysql-bin.000001 表示记录二进制文件名称
② Position->948 所在位置
③ Binlog_Do_DB 指定mysql的binlog日志记录哪个db
④ Binlog_Ignore_DB 告诉master,如果当前的数据库是db_name(就是Binlog_Ignore_DB这个对应的值),不应将更新保存到二进制日志中
⑤ Executed_Gtid_Set mysql5.7以上新增参数 具体用法 自行百度下

整理下 举个例子大家看下:
# 不同步哪些数据库  
binlog-ignore-db = mysql  
binlog-ignore-db = test  
binlog-ignore-db = information_schema  
# 只同步哪些数据库,除此之外,其他不同步  
binlog-do-db = shop

查看二进制日志状态

# 查看二进制日志状态
show binlog events \G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.21-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info: 
*************************** 3. row ***************************
   Log_name: mysql-bin.000001
        Pos: 154
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: mysql-bin.000001
        Pos: 219
 Event_type: Query
  Server_id: 1
End_log_pos: 306
       Info: flush privileges
*************************** 5. row ***************************
   Log_name: mysql-bin.000001
        Pos: 306
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 371
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 6. row ***************************
   Log_name: mysql-bin.000001
        Pos: 371
 Event_type: Query
  Server_id: 1
End_log_pos: 566
       Info: CREATE USER 'root1'@'192.168.153.129' IDENTIFIED WITH 'mysql_native_password' AS '*FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2'
*************************** 7. row ***************************
   Log_name: mysql-bin.000001
        Pos: 566
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 631
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 8. row ***************************
   Log_name: mysql-bin.000001
        Pos: 631
 Event_type: Query
  Server_id: 1
End_log_pos: 796
       Info: GRANT REPLICATION SLAVE ON *.* TO 'root1'@'192.168.153.129'
*************************** 9. row ***************************
   Log_name: mysql-bin.000001
        Pos: 796
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 861
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 10. row ***************************
   Log_name: mysql-bin.000001
        Pos: 861
 Event_type: Query
  Server_id: 1
End_log_pos: 948
       Info: flush privileges
10 rows in set (0.01 sec)
ERROR: 
No query specified

配置从服务器(slave)

同样的配置文件路径 etc/my.cnf

#配置server-id,让从服务器有唯一ID号
server-id=5   
#打开Mysql日志,日志格式为二进制
relay_log = mysql-relay-bin  #暂时未打开 
#设置只读权限
read_only = 1   #暂时未打开
#开启从服务器二进制日志
log_bin = mysql-bin #暂时未打开
#使得更新的数据写进二进制日志中
log_slave_updates = 1 #暂时未打开

PS:上面未打开选项是配置文件中可能需要用到的参数,目前只做演示主从 所以是需要使用server-id=5 这个参数就行了。

启动从服务器复制线程机制

进入从服务器的数据库中 : msyql -uroot -p**

mysql> change master to
    -> master_host='192.168.153.128', #主服务器的ip地址
    -> master_user='root1', #主服务器创建的授权用户(切记这个是给从服务器登录的账号)
    -> master_password='root123', #主服务器创建用户的密码
    -> master_log_file='mysql-bin.000001', # 主服务器的二进制文件名称
    -> master_log_pos=948;  #开始记录的位置
回车出现:
Query OK, 0 rows affected, 2 warnings (0.03 sec)

PS:上面的sql语句是从头开始复制第一个binlog,如果想从某个位置开始复制binlog,就需要在change master to时指定要开始的binlog文件名和语句在文件中的起点位置,参数为:master_log_file和master_log_pos这两个参数哦。

启动slave同步进程

 mysql>start slave;

查看状态是否已启动复制功能

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.153.128
                  Master_User: root1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 948
               Relay_Log_File: test-relay-bin.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             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: 948
              Relay_Log_Space: 526
              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: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: c2180b82-3274-11e8-8cde-000c292b6f79
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)
ERROR: 
No query specified
mysql>

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

这两项必须为Yes才是成功,截图出现问题 没有截图(抱歉)。

如果使用了复制的虚拟机, mysql服务的uuid可能会一样Slave_IO_Runing=No, 需要把从服务器mysql目录下的auto.cnf删掉然后重启mysql服务。
例如我从服务器的mysql地址是:/var/lib/mysql 切换到下面删除auto.cnf即可:

测试

在主服务器(master)上,进入数据库:

#查看所有数据库
show databases;
#创建一个新数据库
create database shop;

然后看下新一个数据库结果:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)
mysql> create database shop;
Query OK, 1 row affected (0.01 sec)

现在已经主服务器(master)上已经创建成功了shop数据库,现在去从服务器(slave)看下:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shop               |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

大家明显的已经看到了从服务器有这个数据库了吧。

我们可以再看下新建一张表插入一条数据看下:
主库新建表:

mysql> use shop;
Database changed
mysql> create table user(
    -> uid int unsigned auto_increment primary key,
    -> ^C
mysql> create table user(
    -> uid int(11) unsigned auto_increment primary key,
    -> username varchar(64) not null
    -> );
Query OK, 0 rows affected (0.04 sec)
mysql> show columns from user;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| uid      | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(64)      | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
2 rows in set (0.04 sec)

这个时候从库已经产生了该user表。

主库往user表插入一条信息:

mysql> insert into user(username)values('zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+-----+----------+
| uid | username |
+-----+----------+
|   1 | zhangsan |
+-----+----------+
1 row in set (0.00 sec)

这个时候从库就出现这条数据了。

好了到这里基本上已经OK了,如果有细节未处理,大家就可以留言补充!

豆浆大叔 豆浆大叔 资深PHP工程师@某一线大厂

写了 264486 字,被 3 人关注,共写了 75 篇笔记

(已更名,豆浆大叔)有理想的码农,不应该只探究人性的懒惰面,而是积极的去探索人生道路上的荆棘坎坷,努力提升自己完善自己!
推荐文章:
  • 大数据领域Flink 与 Spark之间的区别?

    学而不思则罔 思而不学则殆,2020年砥砺前行!前言大家都知道已经2020年了,也到了新的一年。作为一个主营电商的公司,年底都会很忙。所以最近的更新进度也停滞不前,本来准备大侃PHP设计模式的,但是因...

    豆浆大叔 16天前 3 吐槽 55 围观 技术分享
  • php如何实现钩子与实践案例

    前言学而不思则罔,思而不学则殆。30则而立,头顶正则脱光!昨天晚上,突然想起了PHP中的钩子如何使用?说实话,像dz,wordpress,TP,CI框架都已经集成了Hook钩子,尽管我不怎么使用框架以...

    豆浆大叔 28天前 0 吐槽 41 围观 技术分享
  • Linux无法显示ip地址的解决办法

    今天想趁着有时间,用虚拟机调试一下lua脚本和其他的功能,结果启动虚拟机使用xshell连接不上,然后使用终端查看IP地址无法查看到,记录一下排查错误流程。查看IP地址使用ip addr 或者 ifc...

    豆浆大叔 1个月前 0 吐槽 87 围观 技术分享
  • 高并发性能指标QPS,TPS,RT,并发数,吞吐量是指什么?

    QPS,每秒查询QPS:Queries Per Second意思是“每秒查询率”,是一台服务器每秒能够相应的查询次数,是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。互联网中,作为域名系...

    豆浆大叔 1个月前 0 吐槽 163 围观 技术分享
  • 分享一些PHP常用的小算法

    下面分享一些最常见的算法,用PHP如何实现,拓展下知识面。冒泡排序function bubble_sort($arr) { $n=count($arr); for($i=0;$i<$n-1;$...

    我也庸俗 2个月前 0 吐槽 84 围观 技术分享
表情
  • [:821l1001:]
  • [:821l1002:]
  • [:821l1003:]
  • [:821l1004:]
  • [:821l1005:]
  • [:821l1006:]
  • [:821l1007:]
  • [:821l1008:]
  • [:821l1009:]
  • [:821l1010:]
  • [:821l1011:]
  • [:821l1012:]
  • [:821l1013:]
  • [:821l1014:]
  • [:821l1015:]
  • [:821l1016:]
  • [:821l1017:]
  • [:821l1018:]
  • [:821l1019:]
  • [:821l1020:]
  • [:821l1021:]
  • [:821l1022:]
  • [:821l1023:]
  • [:821l1024:]
  • [:821l1025:]
  • [:821l1026:]
  • [:821l1027:]
  • [:821l1028:]
  • [:821l1029:]
  • [:821l1030:]
  • [:821l1031:]
  • [:821l1032:]
  • [:821l1033:]
  • [:821l1034:]
  • [:821l1035:]
  • [:821l1036:]
  • [:821l1037:]
  • [:821l1038:]
  • [:821l1039:]
  • [:821l1040:]
  • [:821l1041:]
  • [:821l1042:]
  • [:821l1043:]
  • [:821l1044:]
  • [:821l1045:]
  • [:821l1046:]
  • [:821l1047:]
  • [:821l1048:]
  • [:821l1049:]
  • [:anger:]
  • [:applause:]
  • [:awkward:]
  • [:brokenheart:]
  • [:clown:]
  • [:confused:]
  • [:decline:]
  • [:diggingmouth:]
  • [:eyebrows:]
  • [:grinning:]
  • [:haha:]
  • [:ill:]
  • [:kiss:]
  • [:lascivious:]
  • [:laugh:]
  • [:love:]
  • [:lovely:]
  • [:rhinorrhea:]
  • [:smile:]
  • [:solid:]
  • [:strong:]
  • [:sweat:]
  • [:tearcollapse:]
  • [:tongue:]
  • [:uncomfortable:]
  • [:weak:]
  • [:worry:]
Tips:支持Markdown语法

0 个评论

抢座沙发~~~~
资深PHP工程师 @ 某一线大厂

登录

第三方账号登录:
GitHub
微信
微博