记录分享两个 MariaDB 导入大量记录 MySQL 备份错误的解决

昨天晚上明月给三个网站做迁移,在导出导入MySQL数据库到新服务器上 MariaDB 的时候折腾了好长的时间,碰到的错误也是一个接着一个的,虽然一个一个都解决了,但是明月感觉还是很有必要记录分享一下的。

未分类

首先给大家先说明一下具体的导入导出场景,三个网站分别在不同的阿里云虚拟主机上,以为业务发展需要三个网站都需要HTTPS支持和更加稳定的服务器,新服务器选的是阿里云ECS的,配置还是很不错哦的,所以直接上的是Linux+Nginx+PHP+MariaDB这样的LNMP生产环境。

导入MySQL数据报错:1064

问题就出在虚拟主机上导出的MySQL备份.sql文件上,在新服务器上导入总是提示“ERROR 1064 (42000): You have an error in your SQL syntax1”这样的错误,检查了好久才发现是虚拟主机上导出的.sql文件都是 UTF+BOM 格式的文件,这就造成直接在phpMyAdmin里导入就会出现无法识别.sql里的编码的问题,MySQL提示的也就是“语法问题”了。

解决办法

解决其实很简单,就是将这个导出的.sql文件使用纯文本编辑软件打开以 UTF 无签名的格式再保存覆盖一下即可正常的导入了。

这个问题目前明月就在阿里云虚拟主机上碰到过,这个可能跟阿里云虚拟主机默认数据库管理用的是 DMS 的缘故,使用phpMyAdmin估计就没有这个问题了。

导入MySQL数据报错:1153终止导入

这个错误一般都是造成数据导入不完整,因为是错误造成“终止导入”了。报错“1153 - Got a packet bigger than 'max_allowed_packet' bytes”。一般都是因为当MySQL客户端或mysqld服务器收到大于max_allowed_packet字节的信息包时,将发出“信息包过大”错误,并关闭连接。对于某些客户端,如果通信信息包过大,在执行查询期间,可能会遇到“丢失与MySQL服务器的连接”错误。

客户端和服务器均有自己的max_allowed_packet变量,因此,如你打算处理大的信息包,必须增加客户端和服务器上的该变量。一般情况下,服务器默认max-allowed-packet为1MB。

解决办法

其中net_buffer_length参数也会影响到这个错误的出现,所以要跟max_allowed_packet一起来修改,可以通过编辑MySQL的配置文件my.cnf(具体位置默认是/etc/my.cnf),修改里面对应的参数值就可以了,然后保存退出重启MySQL。

但是明月这样竟然没有效果,没有办法只能是使用下面的MySQL命令行来设置这两参数了,如下:

mysql -u root -p
set global net_buffer_length=1000000; 
set global max_allowed_packet=1000000000;
quit

这样再导入就可以顺利完成了,完美的导入数据!造成这个错误一般都是因为导入的数据量过大,超出了max_allowed_packet和net_buffer_length的默认值触发的,所以在导入大量记录的MySQL数据的时候一定要提前调整好这两个参数的赋值以备不测,一般只要数据记录超过2万条以后一定要注意提前修改默认赋值。

最后再吐槽一下LNMP1.5里集成的acme.sh脚本,问题实在是太多了,使用DNS API模式申请证书都是提示“无效主机域名”,前前后后测试了N遍都是这个问题,我自己的服务器上使用的是 GitHub 上的acme.sh就完全没有问题,三个站点的Let’s Encrypt证书全部一次就申请成功,无解中……。

mysql中操作事务时要注意的事项

操作事务是个很普遍的过程,凡时对数据库的修改有相互依赖都应该使用事务解决。操作事务时要注意以下几点:

1、尽量使用Exception捕获MYSQL的异常

所以第一步:要设置PDO的异常模式为PDO::ERRMODE_EXCEPTION,PDO::ATTR_ERRMODE,有以下三个值:

  • PDO::ERRMODE_SILENT: 默认模式,不主动报错,需要主动以 $pdo->errorInfo()的形式获取错误信息。
  • PDO::ERRMODE_WARNING: 引发 E_WARNING 错误,主动报错
  • PDO::ERRMODE_EXCEPTION: 主动抛出 exceptions 异常,需要以try{}cath(){}输出错误信息。

我们在连接MYSQL时使用以下:

$pdo=new pdo("mysql:host=localhost;dbname=dbname","user","****", array(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION));

将上面这句代码写在try catch中,捕获异常,注意PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION必须写在new pdo中。不然mysql连接错误的信息是捕获不到的。catch中的类限制为PDOException,如果有命名空间注意在前面加上顶级命名空间,不然也捕获不了异常的。

2、MYSQL的事务开始前先关闭AUTOCOMMIT

$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 0);
$rs = $pdo->beginTransaction();

注意beginTransaction是有返回值的,在调用事务beginTransaction时最好判断其返回值,如果本身返回为false,则后面就不用再回滚。

3、操作事务及回滚

切记在所有的return之前或者其它结束事务之前都记得调用rollBack方法,rollBack方法中也是有返回值的,最好判断返回值,并重试2次回滚,以防止一次回滚发生异常,导致事务锁住。

4、执行开启自动提交

$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 1);

此步非常关键,不管是执行commit还是执行rollBack都务必执行此步,自动提交的意义是你执行的每一句SQL是否直接执行MYSQL.默认的MYSQL就是自动提交的,即你执行一条SQL语句,它就更改到数据库,如果MYSQL不是自动提交,则你执行的MYSQL语句就全部卡在那里等待执行。

事务出现异常时最常出现的现象就是前端浏览器涉及到被锁表或行的请求都处在等待中,最后可能报错mysql innodb Lock wait timeout exceeded; try restarting transaction问题,此时:

第一:可通过SHOW ENGINE INNODB STATUSG;查询报错信息,这个信息量太大了,不好排查。

第二:使用select * from information_schema.innodb_trx G; 查询运行的任务列表。

mysql> select * from information_schema.innodb_trx G;                  
*************************** 1. row ***************************
                    trx_id: 1429E713A
                 trx_state: RUNNING
               trx_started: 2018-04-02 16:25:31
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 23698460
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 376
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
mysql> select trx_id,trx_started,trx_state,trx_mysql_thread_id,trx_rows_locked from information_schema.innodb_trx;     
+-----------+---------------------+-----------+---------------------+-----------------+
| trx_id    | trx_started         | trx_state | trx_mysql_thread_id | trx_rows_locked |
+-----------+---------------------+-----------+---------------------+-----------------+
| 1429E713A | 2018-04-02 16:25:31 | RUNNING   |            23698460 |               0 |
| 1429E7138 | 2018-04-02 16:25:31 | RUNNING   |            23698458 |               0 |
| 1429E7136 | 2018-04-02 16:25:31 | RUNNING   |            23697458 |               0 |
| 1429E6734 | 2018-04-02 16:14:39 | RUNNING   |            23698477 |               1 |
+-----------+---------------------+-----------+---------------------+-----------------+
4 rows in set (0.00 sec)
#可以通过trx_mysql_thread_id值查询processlist查看导致异常的mysql连接
select * from information_schema.processlist where db='dbname' and id =23698477;

第三,可以排查mysql的bin日志:MYSQL日志文件: show variables like general_log_file;

mysql>  show variables like 'general_log_file';
+------------------+-------------------------------+
| Variable_name    | Value                         |
+------------------+-------------------------------+
| general_log_file | /opt/data/mysql/localhost.log |
+------------------+-------------------------------+
1 row in set (0.00 sec)

找到日志文件,直接查看binlog文件可能有乱码。

/opt/modules/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS  mysql-bin.000098

但上面的这个命令只能将已有的日志文件解码展示,不区分里面的数据库,如果日志文件很大,就歇菜了。下面的命令可以指定数据库开始时间:

/opt/modules/mysql/bin/mysqlbinlog --start-datetime='2018-04-02 10:25:00' -d baotoutiao mysql-bin.000098

但上面的命令都不够好,下面这个最佳。可以实现模拟tail -f的功能。并且可指定数据库,非常好用。

export D=$(date +"%Y-%m-%d %H:%M:%S" --date="1 minutes ago"); watch "/opt/modules/mysql/bin/mysqlbinlog --start-datetime="$D" -d dbname --base64-output=decode-rows -v mysql-bin.000098|tail -n 50"

MySQL下perror工具查看System Error Code信息

在MySQL数据库的维护过程中,我们有时候会在MySQL的错误日志文件中看到一些关于Operating system error的错误信息,例如在MySQL的错误日志里面,有时候会看到关于

InnoDB: Operating system error number 0.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.


InnoDB: Operating system error number 5.
...........................................

那么这些System Error Code在Linux下分别代表什么意思呢?其实如果遇到错误日志里有这些对应的代码,使用MySQL自带的命令工具perror查看一下具体信息即可。非常简单方便。

[root@DB-Server ~]# perror 2;
OS error code   2:  No such file or directory
[root@DB-Server ~]# perror 3;
OS error code   3:  No such process
[root@DB-Server ~]# perror 4;
OS error code   4:  Interrupted system call
[root@DB-Server ~]# perror 5;
OS error code   5:  Input/output error
[root@DB-Server ~]#

如果你要查看一个范围的Operating system error,可以使用下面命令

[root@DB-Server ~]# perror `seq 0 10`
OS error code   0:  Success
OS error code   1:  Operation not permitted
OS error code   2:  No such file or directory
OS error code   3:  No such process
OS error code   4:  Interrupted system call
OS error code   5:  Input/output error
OS error code   6:  No such device or address
OS error code   7:  Argument list too long
OS error code   8:  Exec format error
OS error code   9:  Bad file descriptor
OS error code  10:  No child processes
[root@DB-Server ~]#

如果是集群,那么可以shell> perror –ndb errorcode查看, 下面表格是关于Linux平台下通用的System Error Code列表,如下所示,完全没有必要硬性记住。遇到查看即可。

未分类

如果是Windows平台,那么也可以使用下面命令查看具体的System Error Code信息,另外,Windows平台下的System Error Code也远比Linux平台下要多,遇到了这些System Error Code,使用命令查看即可。

perror.exe 2


perror `perl -e "print join ' ',1..100"`

linux MariaDB(MySQL)数据库更改用户权限

平时维护MariaDB(MySQL)数据库服务器,难免会用到一些常用的命令,MariaDB数据库长时间不出问题,有些sql语句就会忘记,之前也没有记载,今天没事就记录下,也共享给大家一块看看,有不足之处还望谅解。

本文操作适用于MariaDB所有版本,适用于MySQL5.2以上版本
本文生产环境Centos7.3 64位 ,MariaDB server 10.2.5

MariaDB 赋予用户权限命令的简单格式可概括为:

grant 权限 on 数据库对象 to 用户;

# mysql -u root -p //登录数据库
 Enter password:
 MariaDB [(none)]> show databases; //查看当前数据库中所有数据库
 MariaDB [(none)]> create database renwole; //新建数据库名为“renwole”
 MariaDB [renwoleBD]> show tables; //显示某个数据库中的表文件
 MariaDB [(none)]> select version(),current_date; //查看数据库版本和当前日期
 MariaDB [(none)]> drop database renwole; //删除renwole数据库
 MariaDB [renwoleDB]> desc 表名称; //查看数据库表结构
 MariaDB [(none)]> show variables like '%dir%'; //查看数据库存储路径
 MariaDB [(none)]> show grants; 查看当前用户权限
 MariaDB [(none)]> show grants for root@'localhost'; //查看用户权限

下面就MariaDB数据库实例讲解,例如:

创建添加一个renwole用户,密码为renwole123

# mysql -u root -p //登录数据库
 Enter password:
 MariaDB [(none)]> insert into mysql.user(Host,User,Password) values("localhost","renwole",password("renwole123"));

新建一个renwoleDB数据库,并授权用户renwole拥有该数据库的所有权限。

MariaDB [(none)]> create database renwoleDB; //新建
 MariaDB [(none)]> grant all privileges on renwoleDB.* to renwole@% identified by 'renwole123'; //授权,关键字 “privileges” 可以省略。
 MariaDB [(none)]> flush privileges; //刷新用户权限

如果想给一个用户查询、插入、更新、删除数据库中的所有表数据权利,可以这样来写:

MariaDB [(none)]> grant select on on renwoleDB.* to renwole@’%’ //查询
 MariaDB [(none)]> grant insert on on renwoleDB.* to renwole@’%’ //插入
 MariaDB [(none)]> grant update on on renwoleDB.* to renwole@’%’ //更新
 MariaDB [(none)]> grant delete on on renwoleDB.* to renwole@’%’ //删除

或者,用一个语句命令替代:

MariaDB [(none)]> grant select,insert,delete,update on renwoleDB.* to renwole@’%’ identified by 'renwole123';

如果想查看所有Mysql用户的权限,代码如下;

MariaDB [(none)]> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

如果想删除一个用户及权限,可以这样写;

MariaDB [(none)]> drop user renwole@localhost;

如果想修改一个用户密码;

MariaDB [(none)]> update mysql.user set password=password('New-password') where User="renwole" and Host="%";
 MariaDB [(none)]> flush privileges;

添加高级root用户整个mysql服务器权限

grant all on *.* to root@'%' identified by 'Password';

注意:【identified by】这个句子可以顺带设置密码,如果不指定该用户口令不变。

撤销已经赋予给MariaDB用户的权限。
revoke 跟 grant 的语法相似,只需要把关键字 “to” 换成 “from” 即可,例如:

grant all on *.* to renwole@%;
revoke all on *.* from renwole@%;

MariaDB数据库的 grant、revoke 用户权限注意事项;grant, revoke 用户权限后,该用户只有重新连接 MySQL 数据库,权限才能生效。如果想让授权的用户,也可以将这些权限 grant 给其他用户,需要选项 “grant option“,例如:

MariaDB [(none)]> grant select on renwoleDB.* to renwole@% with grant option;

这个特性一般用不到。实际中,数据库权限最好由root用户来统一管理。

注意:有时候renwole@’%’授权任意主机连接的时候需要加单引号,但有时又不需要。

在Kubernetes上运行高可用的WordPress和MySQL

WordPress是用于编辑和发布Web内容的主流平台。在本教程中,我将逐步介绍如何使用Kubernetes来构建高可用性(HA)WordPress部署。

WordPress由两个主要组件组成:WordPress PHP服务器和用于存储用户信息、帖子和网站数据的数据库。我们需要让整个应用程序中这两个组件在高可用的同时都具备容错能力。

在硬件和地址发生变化的时候,运行高可用服务可能会很困难:非常难维护。借助Kubernetes以及其强大的网络组件,我们可以部署高可用的WordPress站点和MySQL数据库,而无需(几乎无需)输入单个IP地址。

在本教程中,我将向你展示如何在Kubernetes中创建存储类、服务、配置映射和集合,如何运行高可用MySQL,以及如何将高可用WordPress集群挂载到数据库服务上。如果你还没有Kubernetes集群,你可以在Amazon、Google或者Azure上轻松找到并且启动它们,或者在任意的服务器上使用Rancher Kubernetes Engine (RKE)

架构概述

现在我来简要介绍一下我们将要使用的技术及其功能:
WordPress应用程序文件的存储:具有GCE持久性磁盘备份的NFS存储
数据库集群:带有用于奇偶校验的xtrabackup的MySQL
应用程序级别:挂载到NFS存储的WordPress DockerHub映像
负载均衡和网络:基于Kubernetes的负载均衡器和服务网络

该体系架构如下所示:

未分类

在K8s中创建存储类、服务和配置映射

在Kubernetes中,状态集提供了一种定义pod初始化顺序的方法。我们将使用一个有状态的MySQL集合,因为它能确保我们的数据节点有足够的时间在启动时复制先前pods中的记录。我们配置这个状态集的方式可以让MySQL主机在其他附属机器之前先启动,因此当我们扩展时,可以直接从主机将克隆发送到附属机器上。

首先,我们需要创建一个持久卷存储类和配置映射,以根据需要应用主从配置。我们使用持久卷,避免数据库中的数据受限于集群中任何特定的pods。这种方式可以避免数据库在MySQL主机pod丢失的情况下丢失数据,当主机pod丢失时,它可以重新连接到带xtrabackup的附属机器,并将数据从附属机器拷贝到主机中。MySQL的复制负责主机-附属的复制,而xtrabackup负责附属-主机的复制。

要动态分配持久卷,我们使用GCE持久磁盘创建存储类。不过,Kubernetes提供了各种持久性卷的存储方案:

# storage-class.yamlkind: StorageClassapiVersion: storage.k8s.io/v1metadata:
 name: slowprovisioner: kubernetes.io/gce-pdparameters:
 type: pd-standard  zone: us-central1-a

创建类,并且使用指令:$ kubectl create -f storage-class.yaml部署它。

接下来,我们将创建configmap,它指定了一些在MySQL配置文件中设置的变量。这些不同的配置由pod本身选择有关,但它们也为我们提供了一种便捷的方式来管理潜在的配置变量。

创建名为mysql-configmap.yaml的YAML文件来处理配置,如下:

# mysql-configmap.yamlapiVersion: v1kind: ConfigMapmetadata:
 name: mysql  labels:
   app: mysqldata:
 master.cnf: |    # Apply this config only on the master.
   [mysqld]
   log-bin
   skip-host-cache
   skip-name-resolve  slave.cnf: |    # Apply this config only on slaves.
   [mysqld]
   skip-host-cache
   skip-name-resolve

创建configmap并使用指令:$ kubectl create -f mysql-configmap.yaml
来部署它。

接下来我们要设置服务以便MySQL pods可以互相通信,并且我们的WordPress pod可以使用mysql-services.yaml与MySQL通信。这也为MySQL服务启动了服务负载均衡器。

# mysql-services.yaml# Headless service for stable DNS entries of StatefulSet members.apiVersion: v1kind: Servicemetadata:
 name: mysql  labels:
   app: mysqlspec:
 ports:
 - name: mysql    port: 3306  clusterIP: None  selector:
   app: mysql

通过此服务声明,我们就为实现一个多写入、多读取的MySQL实例集群奠定了基础。这种配置是必要的,每个WordPress实例都可能写入数据库,所以每个节点都必须准备好读写。

执行命令 $ kubectl create -f mysql-services.yaml来创建上述的服务。

到这为止,我们创建了卷声明存储类,它将持久磁盘交给所有请求它们的容器,我们配置了configmap,在MySQL配置文件中设置了一些变量,并且我们配置了一个网络层服务,负责对MySQL服务器请求的负载均衡。上面说的这些只是准备有状态集的框架, MySQL服务器实际在哪里运行,我们接下来将继续探讨。

配置有状态集的MySQL

本节中,我们将编写一个YAML配置文件应用于使用了状态集的MySQL实例。

我们先定义我们的状态集:

1、创建三个pods并将它们注册到MySQL服务上。
2、按照下列模版定义每个pod:

  • 为主机MySQL服务器创建初始化容器,命名为init-mysql.
  • 给这个容器使用mysql:5.7镜像
  • 运行一个bash脚本来启动xtrabackup
  • 为配置文件和configmap挂载两个新卷

3、为主机MySQL服务器创建初始化容器,命名为clone-mysql.

  • 为该容器使用Google Cloud Registry的xtrabackup:1.0镜像
  • 运行bash脚本来克隆上一个同级的现有xtrabackups
  • 为数据和配置文件挂在两个新卷
  • 该容器有效地托管克隆的数据,便于新的附属容器可以获取它

4、为附属MySQL服务器创建基本容器

  • 创建一个MySQL附属容器,配置它连接到MySQL主机
  • 创建附属xtrabackup容器,配置它连接到xtrabackup主机

5、创建一个卷声明模板来描述每个卷,每个卷是一个10GB的持久磁盘
下面的配置文件定义了MySQL集群的主节点和附属节点的行为,提供了运行附属客户端的bash配置,并确保在克隆之前主节点能够正常运行。附属节点和主节点分别获得他们自己的10GB卷,这是他们在我们之前定义的持久卷存储类中请求的。

apiVersion: apps/v1beta1kind: StatefulSetmetadata:
 name: mysqlspec:
 selector:
   matchLabels:
     app: mysql  serviceName: mysql  replicas: 3  template:
   metadata:
     labels:
       app: mysql    spec:
     initContainers:
     - name: init-mysql        image: mysql:5.7        command:
       - bash        - "-c"
       - |
         set -ex          # Generate mysql server-id from pod ordinal index.
         [[ `hostname` =~ -([0-9]+)$ ]] || exit 1
         ordinal=${BASH_REMATCH[1]}
         echo [mysqld] > /mnt/conf.d/server-id.cnf          # Add an offset to avoid reserved server-id=0 value.
         echo server-id=$((100 + $ordinal)) >> /mnt/conf.d/server-id.cnf          # Copy appropriate conf.d files from config-map to emptyDir.
         if [[ $ordinal -eq 0 ]]; then
           cp /mnt/config-map/master.cnf /mnt/conf.d/
         else
           cp /mnt/config-map/slave.cnf /mnt/conf.d/
         fi        volumeMounts:
       - name: conf          mountPath: /mnt/conf.d        - name: config-map          mountPath: /mnt/config-map      - name: clone-mysql        image: gcr.io/google-samples/xtrabackup:1.0        command:
       - bash        - "-c"
       - |
         set -ex          # Skip the clone if data already exists.
         [[ -d /var/lib/mysql/mysql ]] && exit 0          # Skip the clone on master (ordinal index 0).
         [[ `hostname` =~ -([0-9]+)$ ]] || exit 1
         ordinal=${BASH_REMATCH[1]}
         [[ $ordinal -eq 0 ]] && exit 0          # Clone data from previous peer.
         ncat --recv-only mysql-$(($ordinal-1)).mysql 3307 | xbstream -x -C /var/lib/mysql          # Prepare the backup.
         xtrabackup --prepare --target-dir=/var/lib/mysql        volumeMounts:
       - name: data          mountPath: /var/lib/mysql          subPath: mysql        - name: conf          mountPath: /etc/mysql/conf.d      containers:
     - name: mysql        image: mysql:5.7        env:
       - name: MYSQL_ALLOW_EMPTY_PASSWORD          value: "1"
       ports:
       - name: mysql          containerPort: 3306        volumeMounts:
       - name: data          mountPath: /var/lib/mysql          subPath: mysql        - name: conf          mountPath: /etc/mysql/conf.d        resources:
         requests:
           cpu: 500m            memory: 1Gi        livenessProbe:
         exec:
           command: ["mysqladmin", "ping"]
         initialDelaySeconds: 30          periodSeconds: 10          timeoutSeconds: 5        readinessProbe:
         exec:
           # Check we can execute queries over TCP (skip-networking is off).
           command: ["mysql", "-h", "127.0.0.1", "-e", "SELECT 1"]
         initialDelaySeconds: 5          periodSeconds: 2          timeoutSeconds: 1      - name: xtrabackup        image: gcr.io/google-samples/xtrabackup:1.0        ports:
       - name: xtrabackup          containerPort: 3307        command:
       - bash        - "-c"
       - |
         set -ex
         cd /var/lib/mysql          # Determine binlog position of cloned data, if any.
         if [[ -f xtrabackup_slave_info ]]; then            # XtraBackup already generated a partial "CHANGE MASTER TO" query
           # because we're cloning from an existing slave.
           mv xtrabackup_slave_info change_master_to.sql.in            # Ignore xtrabackup_binlog_info in this case (it's useless).
           rm -f xtrabackup_binlog_info
         elif [[ -f xtrabackup_binlog_info ]]; then            # We're cloning directly from master. Parse binlog position.
           [[ `cat xtrabackup_binlog_info` =~ ^(.*?)[[:space:]]+(.*?)$ ]] || exit 1
           rm xtrabackup_binlog_info
           echo "CHANGE MASTER TO MASTER_LOG_FILE='${BASH_REMATCH[1]}',                  MASTER_LOG_POS=${BASH_REMATCH[2]}" > change_master_to.sql.in
         fi          # Check if we need to complete a clone by starting replication.
         if [[ -f change_master_to.sql.in ]]; then
           echo "Waiting for mysqld to be ready (accepting connections)"
           until mysql -h 127.0.0.1 -e "SELECT 1"; do sleep 1; done

           echo "Initializing replication from clone position"
           # In case of container restart, attempt this at-most-once.
           mv change_master_to.sql.in change_master_to.sql.orig
           mysql -h 127.0.0.1 <<EOF
         $(<change_master_to.sql.orig),
           MASTER_HOST='mysql-0.mysql',
           MASTER_USER='root',
           MASTER_PASSWORD='',
           MASTER_CONNECT_RETRY=10;
         START SLAVE;
         EOF
         fi          # Start a server to send backups when requested by peers.
         exec ncat --listen --keep-open --send-only --max-conns=1 3307 -c             "xtrabackup --backup --slave-info --stream=xbstream --host=127.0.0.1 --user=root"
       volumeMounts:
       - name: data          mountPath: /var/lib/mysql          subPath: mysql
       - name: conf          mountPath: /etc/mysql/conf.d        resources:
         requests:
           cpu: 100m            memory: 100Mi      volumes:
     - name: conf        emptyDir: {}
     - name: config-map        configMap:
         name: mysql  volumeClaimTemplates:
 - metadata:
     name: data    spec:
     accessModes: ["ReadWriteOnce"]
     resources:
       requests:
         storage: 10Gi

将该文件存为mysql-statefulset.yaml,输入kubectl=”” create=”” -f=”” mysql-statefulset.yaml并让kubernetes部署你的数据库。
现在当你调用$=”” kubectl=”” get=”” pods,你应该看到3个pods启动或者准备好,其中每个pod上都有两个容器。主节点pod表示为mysql-0,而附属的pods为mysql-1和mysql-2.让pods执行几分钟来确保xtrabackup服务在pod之间正确同步,然后进行wordpress的部署。
您可以检查单个容器的日志来确认没有错误消息抛出。 查看日志的命令为$=”” logs=”” <container_name=””>

主节点xtrabackup容器应显示来自附属的两个连接,并且日志中不应该出现任何错误。

部署高可用的WordPress

整个过程的最后一步是将我们的WordPress pods部署到集群上。为此我们希望为WordPress的服务和部署进行定义。

为了让WordPress实现高可用,我们希望每个容器运行时都是完全可替换的,这意味着我们可以终止一个,启动另一个而不需要对数据或服务可用性进行修改。我们也希望能够容忍至少一个容器的失误,有一个冗余的容器负责处理slack。

WordPress将重要的站点相关数据存储在应用程序目录/var/www/html中。对于要为同一站点提供服务的两个WordPress实例,该文件夹必须包含相同的数据。

当运行高可用WordPress时,我们需要在实例之间共享/var/www/html文件夹,因此我们定义一个NGS服务作为这些卷的挂载点。
下面是设置NFS服务的配置,我提供了纯英文的版本:

# nfs.yaml# Define the persistent volume claimapiVersion: v1kind: PersistentVolumeClaimmetadata:
name: nfs  labels:
  demo: nfs  annotations:
  volume.alpha.kubernetes.io/storage-class: anyspec:
accessModes: [ "ReadWriteOnce" ]
resources:
  requests:
    storage: 200Gi---# Define the Replication ControllerapiVersion: v1kind: ReplicationControllermetadata:
name: nfs-serverspec:
replicas: 1  selector:
  role: nfs-server  template:
  metadata:
    labels:
      role: nfs-server    spec:
    containers:
    - name: nfs-server        image: gcr.io/google_containers/volume-nfs:0.8        ports:
        - name: nfs            containerPort: 2049          - name: mountd            containerPort: 20048          - name: rpcbind            containerPort: 111        securityContext:
        privileged: true        volumeMounts:
        - mountPath: /exports            name: nfs-pvc      volumes:
      - name: nfs-pvc          persistentVolumeClaim:
          claimName: nfs---# Define the Servicekind: ServiceapiVersion: v1metadata:
name: nfs-serverspec:
ports:
  - name: nfs      port: 2049    - name: mountd      port: 20048    - name: rpcbind      port: 111  selector:
  role: nfs-server

使用指令$ kubectl create -f nfs.yaml部署NFS服务。现在,我们需要运行$ kubectl describe services nfs-server获得IP地址,这在后面会用到。

注意:将来,我们可以使用服务名称讲这些绑定在一起,但现在你需要对IP地址进行硬编码。

# wordpress.yamlapiVersion: v1kind: Servicemetadata:
 name: wordpress  labels:
   app: wordpressspec:
 ports:
   - port: 80  selector:
   app: wordpress    tier: frontend  type: LoadBalancer---apiVersion: v1kind: PersistentVolumemetadata:
 name: nfsspec:
 capacity:
   storage: 20G  accessModes:
   - ReadWriteMany  nfs:
   # FIXME: use the right IP
   server: <ip of="" the="" nfs="" service="">    path: "/"---apiVersion: v1kind: PersistentVolumeClaimmetadata:
 name: nfsspec:
 accessModes:
   - ReadWriteMany  storageClassName: ""
 resources:
   requests:
     storage: 20G---apiVersion: apps/v1beta1 # for versions before 1.8.0 use apps/v1beta1kind: Deploymentmetadata:
 name: wordpress  labels:
   app: wordpressspec:
 selector:
   matchLabels:
     app: wordpress      tier: frontend  strategy:
   type: Recreate  template:
   metadata:
     labels:
       app: wordpress        tier: frontend    spec:
     containers:
     - image: wordpress:4.9-apache        name: wordpress        env:
       - name: WORDPRESS_DB_HOST          value: mysql        - name: WORDPRESS_DB_PASSWORD          value: ""
       ports:
       - containerPort: 80          name: wordpress        volumeMounts:
       - name: wordpress-persistent-storage          mountPath: /var/www/html      volumes:
     - name: wordpress-persistent-storage        persistentVolumeClaim:
           claimName: nfs

我们现在创建了一个持久卷声明,和我们之前创建的NFS服务建立映射,然后将卷附加到WordPress pod上,即/var/www/html根目录,这也是WordPress安装的地方。这里保留了集群中WordPress pods的所有安装和环境。有了这些配置,我们就可以对任何WordPress节点进行启动和拆除,而数据能够留下来。因为NFS服务需要不断使用物理卷,该卷将保留下来,并且不会被回收或错误分配。

使用指令$ kubectl create -f wordpress.yaml部署WordPress实例。默认部署只会运行一个WordPress实例,可以使用指令$ kubectl scale –replicas= deployment/wordpress扩展WordPress实例数量。

要获得WordPress服务负载均衡器的地址,你需要输入$ kubectl get services wordpress
并从结果中获取EXTERNAL-IP字段来导航到WordPress。

弹性测试

OK,现在我们已经部署好了服务,那我们来拆除一下它们,看看我们的高可用架构如何处理这些混乱。在这种部署方式中,唯一剩下的单点故障就是NFS服务(原因总结在文末结论中)。你应该能够测试其他任何的服务来了解应用程序是如何响应的。现在我已经启动了WordPress服务的三个副本,以及MySQL服务中的一个主两个附属节点。

首先,我们先kill掉其他而只留下一个WordPress节点,来看看应用如何响应:$ kubectl scale –replicas=1 deployment/wordpress现在我们应该看到WordPress部署的pod数量有所下降。$ kubectl get pods应该能看到WordPress pods的运行变成了1/1。

点击WordPress服务IP,我们将看到与之前一样的站点和数据库。如果要扩展复原,可以使用$ kubectl scale –replicas=3 deployment/wordpress再一次,我们可以看到数据包留在了三个实例中。

下面测试MySQL的状态集,我们使用指令缩小备份的数量:$ kubectl scale statefulsets mysql –replicas=1我们会看到两个附属从该实例中丢失,如果主节点在此时丢失,它所保存的数据将保存在GCE持久磁盘上。不过就必须手动从磁盘恢复数据。

如果所有三个MySQL节点都关闭了,当新节点出现时就无法复制。但是,如果一个主节点发生故障,一个新的主节点就会自动启动,并且通过xtrabackup重新配置来自附属节点的数据。因此,在运行生产数据库时,我不建议以小于3的复制系数来运行。在结论段中,我们会谈谈针对有状态数据有什么更好的解决方案,因为Kubernetes并非真正是为状态设计的。

结论和建议

到现在为止,你已经完成了在Kubernetes构建并部署高可用WordPress和MySQL的安装!

不过尽管取得了这样的效果,你的研究之旅可能还远没有结束。可能你还没注意到,我们的安装仍然存在着单点故障:NFS服务器在WordPress pods之间共享/var/www/html目录。这项服务代表了单点故障,因为如果它没有运行,在使用它的pods上html目录就会丢失。教程中我们为服务器选择了非常稳定的镜像,可以在生产环境中使用,但对于真正的生产部署,你可以考虑使用GlusterFS对WordPress实例共享的目录开启多读多写。

这个过程涉及在Kubernetes上运行分布式存储集群,实际上这不是Kubernetes构建的,因此尽管它运行良好,但不是长期部署的理想选择。

对于数据库,我个人建议使用托管的关系数据库服务来托管MySQL实例,因为无论是Google的CloudSQL还是AWS的RDS,它们都以更合理的价格提供高可用和冗余处理,并且不需担心数据的完整性。Kuberntes并不是围绕有状态的应用程序设计的,任何建立在其中的状态更多都是事后考虑。目前有大量的解决方案可以在选择数据库服务时提供所需的保证。

也就是说,上面介绍的是一种理想的流程,由Kubernetes教程、web中找到的例子创建一个有关联的现实的Kubernetes例子,并且包含了Kubernetes 1.8.x中所有的新特性。

我希望通过这份指南,你能在部署WordPress和MySQL时获得一些惊喜的体验,当然,更希望你的运行一切正常。

Docker的MySQL官方镜像设置时区

在 Docker Hub 中的 MySQL 官方镜像中,时区是使用了世界标准时间(UTC)。因为在中国使用,所以需要把时区改成东八区的。

方法1

1、查看当前时区

date -R

2、修改设置时区。先输入 tzselect 命令,然后根据提示,通过输入选项前面的数字来确定选项。我的例子,先选择 Asia ,再选择 Hong Kong。香港和中国采用了同样的东八区时间。

3、复制相应的时区文件,替换原来的时区文件。命令如下:

/usr/share/zoneinfo/Asia# cp Hong_Kong /etc/localtime

我们需要的时区文件在目录 /usr/share/zoneinfo/Asia 下。文件名字是 Hong_Kong。把它拷贝覆盖 /etc/localtime 文件。

4、重新输入命令 date -R,就可以看到修改时区后的中国时间了。

方法2

我自己找到了一种不用 tzselect 的方法。
目录/usr/share/zoneinfo/Asia下有各个亚洲地区的时区的文件。查看这个目录下的内容:

/usr/share/zoneinfo/Asia# ls -hl

lrwxrwxrwx 1 root root    6 Jul  6 02:15 Shanghai -> ../PRC
lrwxrwxrwx 1 root root   12 Jul  6 02:15 Singapore -> ../Singapore

从查询结果可以知道,上海的时区文件实际上是个软连接文件。连接到了目录 /usr/share/zoneinfo/ 下的PRC文件。
直接进行拷贝:

cp /usr/share/zoneinfo/PRC /etc/localtime

然后这样就可以了。

Docker-MySql — 使用mysqldump 命令备份导出mysql容器中的结构数据

1. 查看当前启动的mysql运行容器

docker ps   

2. 使用以下命令备份导出数据库中的所有表结构和数据

docker exec -it  mysql mysqldump -uroot -p123456 paas_portal > /cloud/sql/paas_portal.sql  

3.只导数据不导结构

mysqldump -t 数据库名 -uroot -p > xxx.sql 
docker exec -it mysql mysqldump -t -uroot -p123456 paas_portal >/cloud/sql/paas_portal_dml.sql  

4. 只导结构不导数据

mysqldump --opt -d 数据库名 -u root -p > xxx.sql 
docker exec -it mysql mysqldump  --opt -d   -uroot -p123456 paas_portal >/cloud/sql/paas_portal_ddl.sql   

5. 导出特定表的结构

mysqldump -uroot -p -B 数据库名 --table 表名 > xxx.sql
docker exec -it mysql mysqldump -uroot -p -B paas_portal --table user > user.sql  

这是工作中用到的,记录下来,以供学习参考

MySQL 5.7并发复制和mysqldump相互阻塞引起的复制延迟

本来MySQL BINLOG和SHOW PROCESSLIST命令属于八竿子打不着的两个事务,但在最近故障排查中,发现主库和从库已经存在很严重的复制延迟,但从库上显示slave_behind_master值为0,复制SQL线程与备份线程之间相互阻塞,但未报死锁。

在从库上执行SHOW PROCESSLIST发现复制的SQL线程等待锁,而等待SQL的WHERE条件竟然是类似于WHERE C1=’ABC’ AND C2>’2018-03-01′ AND C2<‘2018-03-26’ 这种个范围查询,第一时间想到就是怎么是个基于STATEMENT的复制,不科学啊,我们生产环境统一使用基于ROW格式的复制,难道研发私自修改回话级别的复制格式?

使用MySQL Binlog导出日志一看:

未分类

发现真错怪研发同事啦,rbr_only=yes说明基于ROW格式进行复制,“SET TRANSACTION ISOLATION LEVEL READ COMMITTED”也是基于行格式复制的典型特征之一,last_committed和sequence_number用于MySQL 5.7版本中的并发复制,row_query后跟的是在主库上执行的原始SQL,也就是我们在从库SHOW PROCESSLIST中看到的SQL,但实际上从库执行的还是BINLOG部分,该BINLOG可以直接可以直接直接在从库上执行,也可以解析成一行行的数据DML操作,BINLOG部分如下:

未分类

==========================================================================================================

另外一个很有意思的问题,如果在从库上运行mysqldump进行备份,且从库上使用并行复制,会导致备份和复制相互阻塞:

未分类

在上面的阻塞中,多个SQL线程与备份线程相互之间阻塞,且MySQL无法有效检测出死锁环路而触发死锁的回滚机制,导致复制线程和备份作业相互hang住,需要DBA进行干预(取消备份或停止复制),在复制SQL线程被hang住期间,复制的IO线程仍可以正常工作接受到主库的Binlog信息,但slave_behind_master并不会随之增大,如果仅通过监控slave_behind_master值来判断主从复制延迟,则会导致延迟监控存在严重漏洞,因此在监控复制延迟时,除监控slave_behind_master值外,还需要监控主库binlog位置点和从库执行的binlog位置点。

MySQL数据库INNODB表损坏修复处理过程分享

##状况描述

突然收到MySQL报警,从库的数据库挂了,一直在不停的重启,打开错误日志,发现有张表坏了。innodb表损坏不能通过repair table 等修复myisam的命令操作。现在记录下解决过程,下次遇到就不会这么手忙脚乱了。

处理过程

一遇到报警之后,直接打开错误日志,里面的信息:

InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 30506. InnoDB: You may have to recover from a backup. 130509 20:33:48 InnoDB: Page dump in ascii and hex (16384 bytes): ##很多十六进制的代码 …… …… InnoDB: End of page dump 130509 20:37:34 InnoDB: Page checksum 1958578898, prior-to-4.0.14-form checksum 3765017239 InnoDB: stored checksum 3904709694, prior-to-4.0.14-form stored checksum 3765017239 InnoDB: Page lsn 5 614270220, low 4 bytes of lsn at page end 614270220 InnoDB: Page number (if stored to page already) 30506, InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 19 InnoDB: Page may be an index page where index id is 54 InnoDB: (index "PRIMARY" of table "maitem"."email_status") InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 30506. InnoDB: You may have to recover from a backup. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. InnoDB: If the corrupt page is an index page InnoDB: you can also try to fix the corruption InnoDB: by dumping, dropping, and reimporting InnoDB: the corrupt table. You can use CHECK InnoDB: TABLE to scan your table for corruption. InnoDB: See also http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. InnoDB: A new raw disk partition was initialized or InnoDB: innodb_force_recovery is on: we do not allow InnoDB: database modifications by the user. Shut down InnoDB: mysqld and edit my.cnf so that newraw is replaced InnoDB: with raw, and innodb_force_... is removed. 130509 20:39:35 [Warning] Invalid (old?) table or database name '#sql2-19c4-5'

从错误日志里面很清楚的知道哪里出现了问题,该怎么处理。这时候数据库隔几s就重启,所以差不多可以说你是访问不了数据库的。所以马上想到要修复innodb表了。以前在Performance的blog上看过类似文章。

当时想到的是在修复之前保证数据库正常,不是这么异常的无休止的重启。

所以就修改了配置文件的一个参数:innodb_force_recovery

innodb_force_recovery 会影响整个InnoDB存储引擎的恢复状况。默认为0,表示当需要恢复时执行所有的

innodb_force_recovery可以设置为1-6,大的数字包含前面所有数字的影响。当设置参数值大于0后,可以对表进行

select,create,drop操作,但insert,update或者delete这类操作是不允许的。

  • (SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
    (SRV_FORCE_NO_BACKGROUND):阻止主线程的运行,如主线程需要执行full purge操作,会导致crash。
  • (SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
  • (SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
  • (SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志,InnoDB存储引擎会将未提交的事务视为已提交。
  • (SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。

因为错误日志里面提示出现了坏页,导致数据库崩溃,所以这里把innodb_force_recovery 设置为1,忽略检查到的坏页。重启数据库之后,正常了,没有出现上面的错误信息。找到错误信息出现的表:
(index "PRIMARY" of table "maitem"."email_status")

数据页面的主键索引(clustered key index)被损坏。这种情况和数据的二级索引(secondary indexes)被损坏相比要糟很多,因为后者可以通过使用OPTIMIZE TABLE命令来修复,但这和更难以恢复的表格目录(table dictionary)被破坏的情况来说要好一些。

操作步骤

因为被破坏的地方只在索引的部分,所以当使用innodb_force_recovery = 1运行InnoDB时,操作如下:

执行check,repair table 都无效

alter table email_status engine =myisam; #也报错了,因为模式是innodb_force_recovery =1。

ERROR 1025 (HY000): Error on rename of '...' to '....' (errno: -1)

1. 建立一张表:

mysql> create table email_status_bak #和原表结构一样,只是把INNODB改成了MYISAM。

2. 把数据导进去 :

mysql>  insert into email_status_bak select * from email_status;

3. 删除掉原表:

mysql>  drop table email_status;

4. 注释掉innodb_force_recovery 之后,重启服务。

5. 重命名:

mysql> rename table edm_email_status_bak to email_status;

6. 最后该回存储引擎

mysql> alter table edm_email_status engine = innodb;

总结

这里的一个重要知识点就是 对 innodb_force_recovery 参数的理解了,要是遇到数据损坏甚至是其他的损坏。可能上面的方法不行了,需要尝试另一个方法:insert into tb select * from ta limit X;甚至是dump出去,再load回来。

Linux下MySQL定时自动完整备份(mysqldump+crontab)

一、前言

1、本文目标

  • 实现数据库全量备份
  • 实现定时执行备份
  • 实现定时清理7天之前的备份

2、环境信息

未分类

二、备份

  • 目录准备
mkdir /mysql
mkdir /mysql/backup
mkdir /mysql/backup/scripts
mkdir /mysql/backup/files
mkdir /mysql/backup/logs
  • 备份脚本

新建脚本

vi /mysql/backup/scripts/backup_full.sh

脚本内容

#!/bin/bash

#备份目录
BACKUP_ROOT=/mysql/backup
BACKUP_FILEDIR=$BACKUP_ROOT/files
BACKUP_LOGDIR=$BACKUP_ROOT/logs

#当前日期
DATE=$(date +%Y%m%d)

######备份######

#查询所有数据库
#-uroot -p123456表示使用root账号执行命令,且root账号的密码为:123456
DATABASES=$(mysql -uroot -p123456 -e "show databases" | grep -Ev "Database|sys|information_schema")
#DATABASES=$(mysql -uroot -p123456 -e "SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('sys','mysql','information_schema','performance_schema');" | grep -v "SCHEMA_NAME","ken.io") 
echo $DATABASES
#循环数据库进行备份
for db in $DATABASES
do
echo
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz BEGIN----------
mysqldump -uroot -pRoot@1024 --default-character-set=utf8 -q --lock-all-tables --flush-logs -E -R --triggers -B ${db} | gzip > $BACKUP_FILEDIR/${db}_$DATE.sql.gz
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz COMPLETE----------
echo
done

echo "done"

备份测试&验证

#执行备份脚本
sh /mysql/backup/scripts/backup_full.sh

#查看备份文件
ll /mysql/backup/files -h

#解压指定文件({file}自己替换成备份的文件)
gunzip /mysql/backup/files/{file}

三、定时任务

  • 安装crontab
yum install -y crontab

1、定时执行MySQL完整备份

  • 创建定时备份任务
#添加定时任务
crontab -e

#每天凌晨3点执行
00 3 * * * sh /mysql/backup/scripts/backup_full.sh

#查看定时任务
crontab -l

2、定时清理7天以前的备份

  • 创建文件清理脚本
#创建脚本文件
vi /mysql/backup/scripts/backup_full_clean.sh

#写入以下内容
#!/bin/bash
find /mysql/backup/files -mtime +7 -name "*.gz" -exec rm -rf {} ;

创建定时清理任务

#添加定时任务
crontab -e

#每天凌晨1点执行
00 1 * * * sh /mysql/backup/scripts/backup_full_clean.sh

#查看定时任务
crontab -l

四、备注

1、crontab命令示例

未分类

2、本文参考

  • https://blog.csdn.net/zmcyu/article/details/75353245
  • http://linuxtools-rst.readthedocs.io/zh_CN/latest/tool/crontab.html

本文由 ken.io 创作,采用CC BY 3.0 CN协议 进行许可。 可自由转载、引用、甚至修改,但需署名作者且注明出处。