Mysql的安装、主从复制

前言

　　　最近在学习高可用性系统的相关知识，上周配置了简单的nginx负载均衡，缓解了应用服务器的压力，但是多个应用服务器用的还是同一个数据库，这样会使数据库的压力增大，效率变低。而优化数据库，除了加硬件、sql语句优化和索引之外，就是多数据源了。这里我记录一下mysql多数据源的配置：一个主库一个从库，写(增删)操作写入主库，从库同步主库的内容，读(改查)操作从从库读。

安装Mysql

　　在一个linux系统上安装mysql其实挺麻烦的，有钱的话直接买阿里云的RDS，一步到位。这里记录一下用rpm包从头安装的步骤吧。

在Mysql官网下载Mysql，用xftp工具传到linux服务器上。

Centos7有自带的mariadb数据库，和Mysql的包有冲突，需要先找到并删除。

[root@VM_0_9_redhat ~]# rpm -qa | grep -i mariadb
mariadb-libs-5.5.52-1.el7.x86_64
[root@VM_0_9_redhat ~]# rpm -e --nodeps mariadb-libs-5.5.52-1.el7.x86_64

解压

[root@VM_0_9_redhat ~]# cd /opt/mysql/
[root@VM_0_9_redhat mysql]# tar -xvf mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar 
mysql-community-libs-5.7.22-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.22-1.el7.x86_64.rpm
mysql-community-embedded-5.7.22-1.el7.x86_64.rpm
mysql-community-test-5.7.22-1.el7.x86_64.rpm
mysql-community-server-5.7.22-1.el7.x86_64.rpm
mysql-community-client-5.7.22-1.el7.x86_64.rpm
mysql-community-server-minimal-5.7.22-1.el7.x86_64.rpm
mysql-community-devel-5.7.22-1.el7.x86_64.rpm
mysql-community-common-5.7.22-1.el7.x86_64.rpm
mysql-community-minimal-debuginfo-5.7.22-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.22-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.22-1.el7.x86_64.rpm
[root@VM_0_9_redhat mysql]# 

依次执行这四个包,因为有依赖，顺序不能乱：

rpm -ivh mysql-community-common-5.7.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.22-1.el7.x86_64.rpm

执行rpm -ivh mysql-community-server-5.7.22-1.el7.x86_64.rpm时有可能会有报错缺某某依赖,缺什么装什么即可解决,解决方法：yum install numactl：

[root@VM_0_9_redhat mysql]# rpm -ivh mysql-community-server-5.7.22-1.el7.x86_64.rpm
warning: mysql-community-server-5.7.22-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
  libnuma.so.1()(64bit) is needed by mysql-community-server-5.7.22-1.el7.x86_64
  libnuma.so.1(libnuma_1.1)(64bit) is needed by mysql-community-server-5.7.22-1.el7.x86_64
  libnuma.so.1(libnuma_1.2)(64bit) is needed by mysql-community-server-5.7.22-1.el7.x86_64
[root@VM_0_9_redhat mysql]# yum  install    numactl

初始化：输入mysqld --initialize命令，执行之后会在/var/log/mysqld.log中生成root的密码：

2018-11-12T03:39:37.749332Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-11-12T03:39:46.650203Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-11-12T03:39:48.338301Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-11-12T03:39:48.713622Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 9aef7d68-e62c-11e8-8e88-52540032b1e8.
2018-11-12T03:39:48.725846Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-11-12T03:39:48.726528Z 1 [Note] A temporary password is generated for root@localhost: FTp,(t;o_6fR

最后一行的FTp,(t;o_6fR就是密码。

登陆：mysql -uroot -p，回车后输入密码，use mysql后他会提示你修改密码，按照提示修改密码即可：

mysql> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.00 sec)

用navicat远程连接测试，如果连不上赋予一下权限就好，这里用的root账号，就不用赋予权限了。

主从复制

搞了好几天我还是没弄好，明明两个线程都yes了，pos也随着master变了，可是数据库就是不变。日了。

主库

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |     1900 | laozhangdb   |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show processlist;
+----+------+----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host                 | db   | Command     | Time | State                                                         | Info             |
+----+------+----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
|  9 | root | 118.25.180.201:49142 | NULL | Binlog Dump | 9382 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 10 | root | localhost            | NULL | Query       |    0 | starting                                                      | show processlist |
+----+------+----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

从库：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 47.93.236.80
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 1900
               Relay_Log_File: VM_0_9_redhat-relay-bin.000007
                Relay_Log_Pos: 2113
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: kongdb
          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: 1900
              Relay_Log_Space: 7347
              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: ae1970bc-e727-11e8-b83b-00163e0ae49d
             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> mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  3 | system user |           | NULL | Connect | 9334 | Waiting for master to send event                       | NULL             |
|  4 | system user |           | NULL | Connect | 9290 | Slave has read all relay log; waiting for more updates | NULL             |
|  7 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

发送和接受事件的线程一直不动，主库更新时Slave_SQL线程执行，Slave_IO死活不执行,就像这样：

mysql> mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  3 | system user |           | NULL | Connect | 9334 | Waiting for master to send event                       | NULL             |
|  4 | system user |           | NULL | Connect | 9290 | Slave has read all relay log; waiting for more updates | NULL             |
|  7 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  3 | system user |           | NULL | Connect | 9503 | Waiting for master to send event                       | NULL             |
|  4 | system user |           | NULL | Connect |    7 | Slave has read all relay log; waiting for more updates | NULL             |
|  7 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

mysql> 

主库配置：

log-bin=mysql-bin   
server-id=1
binlog_do_db=laozhangdb

skip-name-resolve

从库配置：

server-id=201
replicate-do-db=kongdb
log-bin=mysql_bin

mysqld.log没报错，搞了3天了，百度谷歌便了，还是没解决，这个小破公司也没人好给我问，记录一下。。。。

很难受。。。。

——Mysql5.7.22+CentOS7.4

前言

安装Mysql

主从复制

CATALOG

FEATURED TAGS

FRIENDS