标签：MariaDB

MariaDB/MySQL中的变量

在MySQL/MariaDB中有好几种变量类型：用户自定义变量、系统变量、一般的临时变量(即本地变量，或称为局部变量)。

1. 用户变量

用户变量是基于会话的，也是基于用户的，所以我觉得称之为会话变量更合适，但会话变量一般用来表示系统会话变量(后面会说明)，所以还是称之为用户变量好了。

只有本用户才能引用自身的用户变量，其他用户无法引用，且当用户退出会话时，用户变量自动销毁。

用户变量使用”@”开头，用户变量可以直接赋值，无需事先声明。在引用未赋值的用户变量时，该变量值为null。

有以下三种方式设置用户变量：

  • set语句，此时可以使用”=”或者”:=”操作符;
  • select语句，此时只能使用”:=”格式赋值，因为除了set语句中，”=”都会被视为比较操作符。;
  • select … into var_name from TABLE语句，此时要求select语句只能返回标量值，即单行数据。因此为了保险，select into var_name的时候，应尽量结合limit语句限制输出。
set @a1=1,@a2=3,@a3:=2;
select @a4:=@a1+@a3;
select 33,'abc' into @a5,@a6 from dual;

查看变量值可以使用select语句。

MariaDB [test]> select @a1,@a2,@a3,@a4,@a5,@a6,@a7;
+------+------+------+------+------+------+------+
| @a1  | @a2  | @a3  | @a4  | @a5  | @a6  | @a7  |
+------+------+------+------+------+------+------+
|    1 |    2 |    3 |    4 |    5 | abc  | NULL |
+------+------+------+------+------+------+------+

在mariadb 10.2.6中，引入了一张系统架构表information_schema.USER_VARIABLES，该表中记录了当前用户当前会话定义的用户变量信息。该信息架构表在mysql中没有。

MariaDB [test]> SELECT * FROM information_schema.USER_VARIABLES;           
+---------------+----------------+---------------+--------------------+
| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_TYPE | CHARACTER_SET_NAME |
+---------------+----------------+---------------+--------------------+
| a6            | abc            | VARCHAR       | utf8               |
| i             | 2              | INT           | utf8               |
| a5            | 33             | INT           | utf8               |
| a1            | 1              | INT           | utf8               |
| a4            | 3              | INT           | utf8               |
| a2            | 3              | INT           | utf8               |
| a3            | 2              | INT           | utf8               |
+---------------+----------------+---------------+--------------------+

2. 系统变量

在MySQL/mariadb中维护两种系统变量：全局系统变量和会话系统变量。系统变量是用来设置MySQL服务运行属性和状态的。

全局系统变量使用global或者”@@global.”关键字来设置。会话系统变量使用session或者”@@session.”关键字来设置，其中session可以替换为Local，它们是同义词。如果省略这些关键字，则默认为session系统变量。设置global系统变量要求具有super权限。

-- 设置全局系统变量
set global sort_buffer_size=32M;
set @@global.sort_buffer_size=32M;
-- 设置会话系统变量
set session sort_buffer_size=32M;
set @@session.sort_buffer_size=32M;
set sort_buffer_size=32M;
-- 查看全局系统变量值
select @@global.sort_buffer_size;
show global variables like "sort_buffer%";
-- 查看会话系统变量，不能使用select sort_buffer_size
select @@session.sort_buffer_size;
select @@sort_buffer_size;
show [session] variables like "sort_buffer%";
-- 一次性设置多个变量，包括会话变量、全局变量以及用户变量
SET @x = 1, SESSION sql_mode = '';
SET GLOBAL sort_buffer_size = 1000000, SESSION sort_buffer_size = 1000000;
SET @@global.sort_buffer_size = 1000000, @@local.sort_buffer_size = 1000000;
SET GLOBAL max_connections = 1000, sort_buffer_size = 1000000;

全局系统变量对全局有效，当有新的会话打开时，新会话会继承全局系统变量的值，所以设置全局系统变量之后新打开的会话都会继承设置后的值。设置全局系统变量对已经打开的连接无效，但是其他已经打开的连接可以查看到设置后的全局系统变量值。

系统变量按照是否允许在运行时修改，还分为动态变量和静态变量。能在运行过程中修改的变量称为动态变量，只能在数据库实例关闭状态下修改的变量称为静态变量或只读变量。动态变量使用set修改。如果在数据库实例运行状态下修改静态变量，则会给出错误。如：

set @@innodb_undo_tablespaces=3;
ERROR 1238 (HY000): Variable 'innodb_undo_tablespaces' is a read only variable

系统变量除了可以在运行中的环境下设置，还可以在配置文件中或者mysqld/mysqld_safe这样的命令行中设置，甚至mysql客户端命令行也可以传递。在配置文件中设置系统变量时，下划线或者短横线都允许，它们表示同一个意思。例如下面的两行配置是等价的：

innodb_file_per_table=1
innodb-file-per-table=1

3. 局部变量

局部变量也称为本地变量，只能在begin…and语句块中生效。它不像用户变量，本地变量必须使用declare事先声明，所以declare也必须在begin…end中使用。

局部变量无论是声明还是调用的时候都不需要任何多余的符号(即不需要@符号)，直接使用其名称var_name即可。

使用declare声明变量，可以一次性声明多个同类型的变量，需要时可有直接为其指定默认值，不指定时默认为null。

decalre var_name,... type [default value];

使用set为变量赋值。MySQL/mariadb中set支持一次性赋值多个变量。

在begin…end中的set是一般set语句的扩展版本，它既可以设置系统变量、用户变量，也可以设置此处的本地变量。

set var_name=expr,[var_name=expr1,...]

或者使用select…into语句从表中获取值来赋值给变量，但是这样的赋值行为要求表的返回结果必须是单列且单行的标量结果。例如下面的语句将col的列值赋值给var_name变量。

select col into var_name from table_name;

因为局部变量只能在begin…end中使用，所以此处使用存储过程的例子来演示。

DROP PROCEDURE IF EXISTS haha;
DELIMITER $$
CREATE PROCEDURE haha()
BEGIN
    DECLARE a INT;
    SET a=1;
    SET @i:=2;
    SELECT a,@i;
END$$
DELIMITER ;

CALL haha();
     a        @i
------  --------
     1         2

在MySQL中，begin…end只能定义在存储程序中，所以declare也只能定义在存储程序内。但在mariadb中，begin…end是允许定义在存储程序(存储函数，存储过程，触发器，事件)之外的，所以decalre也算是能够定义在存储程序之外吧。需要定义在存储程序之外时，使用 begin not atomic 关键字即可。例如：

delimiter $$ 
begin not atomic
    declare a int;
    set a=3;
    select a;
end$$

3.1 declare锚定其他对象的数据类型

在mariadb 10.3中(注意版本号，目前10.3版本还在测试中)，declare语句允许在存储程序中使用TYPE OF和ROW TYPE OF 关键字基于表或游标来锚定数据类型。在mysql中不支持数据类型的锚定功能。

例如：

DECLARE tmp TYPE OF t1.a; -- 基于表t1中的a列获取数据类型
DECLARE rec1 ROW TYPE OF t1; -- 锚定表t1中行数据类型
DECLARE rec2 ROW TYPE OF cur1; -- 基于游标cur1获取行数据类型

通过其他对象来锚定本地变量的数据类型时，如果对象的数据类型改变，则本地数据类型也随之改变。这在某些时候非常有利于维护存储程序。

在定义存储程序时，不会检查declare锚定的对象是否存在。但在调用存储程序时，会先检查锚定对象是否存在。

当declare语句的锚定是基于表对象(不是游标)时，在调用存储程序的瞬间就会检查锚定的表是否存在，然后立刻声明该变量。因此:

  • (1).带有锚定功能的decalre语句可以定义在存储程序的任意位置；
  • (2).在存储程序中删除锚定的表对象，或者修改了锚定的表结构，都不会改变存储程序调用时声明的变量类型；
  • (3).所有带锚定功能的declare都是在存储程序调用之初被赋值的。

当declare语句的锚定是基于游标对象时，变量的数据类型是在执行变量声明语句时才获取到的。数据类型仅只锚定一次，之后不再改变。如果游标中的ROW TYPE OF变量是定义在一个循环之中，则数据类型在循环的开头就已经获取，且之后的循环不再改变。

示例：

create table t1(a int,b char(20));
drop procedure if exists haha;
delimiter $$
create procedure haha()
begin
    declare x type of t1.a;
    set x=1;
    select x;
end$$
delimiter ;

call haha();

如何在Ubuntu上安装MySQL / MariaDB

本教程面向Ubuntu服务器，适用于Ubuntu的任何LTS版本，包括Ubuntu 14.04，Ubuntu 16.04，Ubuntu 18.04，甚至非LTS版本（如Ubuntu 17.10和其他基于Ubuntu的发行版）。我们在Ubuntu 16.04服务器上测试了这个。

在我们开始安装MySQL / MariaDB之前

一些要求和其他说明：

当涉及LAMP堆栈中的基本用法时，MySQL和MariaDB几乎完全相同。大多数命令是相同的，即使安装也是如此。选择一个并将其安装到您的LAMP堆栈中，我们将包含两者的说明。

你需要一个Ubuntu服务器来运行MySQL/MariaDB。

您需要root用户或具有sudo访问权限的用户访问服务器。以下命令全部由root用户执行，所以我们不必为每个命令添加’sudo’。如果您使用非root用户，则可能必须执行此操作。
如果您使用的是Windows，则需要启用SSH，如果您使用Ubuntu或MobaXterm等SSH客户端。

MySQL / MariaDB可能已经安装在您的服务器上。您可以通过输入“mysql”或“mariadb”来检查它们是否已安装，并且您应该根据输出结果来了解它们。

现在就是这样。 我们来看看我们的教程。

如何在Ubuntu上安装MySQL

我们将从MySQL开始。 如果您想安装MariaDB，请跳至MariaDB说明。

更新Ubuntu
首先，像往常一样，在你做任何事之前，通过运行以下命令来更新你的Ubuntu服务器：

apt-get update && apt-get upgrade

安装MySQL
然后，通过运行以下命令安装MySQL：

apt-get install mysql-server

该命令将安装MySQL服务器和客户端。 你会得到一个提示，为你的root用户输入一个密码。

而已。 MySQL已安装。 现在，您需要保护并配置它。

安全的MySQL
你应该运行mysql_secure_installation脚本来帮助你保护你的MySQL。

使用以下命令启动脚本：

mysql_secure_installation

并回应提示。 您可以使用每个提示的默认响应。

优化MySQL（仅限高级用户）
要优化你的MySQL，你可以使用MySQLTuner脚本。 它不会为你做所有的工作。 该脚本仅向您提供如何改进和优化MySQL的建议。

使用以下命令下载并运行脚本：

curl -L http://mysqltuner.pl/ | perl

并检查建议。 做一些研究和使用谷歌每个建议。 如果你不知道自己在做什么，请联系其他人，让他们为你做，或者跳过这个。

你也可以使用mysqlcheck来修复你的数据库。 您可以使用单个命令修复所有数据库：

mysqlcheck -A –auto-repair -u root -p

还有其他的优化可以在你的服务器和数据库上完成，如果你想进一步优化MySQL，你也可以自己做一些研究。

如何在Ubuntu上安装MariaDB

现在来看我们的MariaDB安装说明。

更新Ubuntu
首先，更新你的Ubuntu服务器：

apt-get update && apt-get upgrade

添加MariaDB存储库
在您可以安装MariaDB之前，您需要添加MariaDB存储库。

根据您的Ubuntu发行版，您可能需要运行不同的命令，因此请前往官方的MariaDB存储库页面，选择您的发行版并选择离您的服务器位置最近的镜像。 然后，复制你在页面上得到的命令。 我们使用Ubuntu 16.04.1并选择了美国镜像，因此我们将运行以下命令来添加存储库：

apt-get install software-properties-common
apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
add-apt-repository 'deb [arch=amd64,i386,ppc64el] https://mirrors.evowise.com/mariadb/repo/10.1/ubuntu xenial main'

接下来，您需要再次更新您的软件包列表：

apt-get update

并转到安装MariaDB。

安装MariaDB
添加MariaDB存储库后，可以通过运行以下命令来安装它：

apt-get install mariadb-server

就是这样。 你已经在服务器上安装了MariaDB。

保护MariaDB
这与MySQL的过程相同。 使用以下命令运行安全脚本：

mysql_secure_installation

并按照提示进行操作。 您可以输入每个提示的默认值。 当然，使用强密码。

优化MariaDB（仅限高级用户）
同样，与MySQL相同，您可以使用MySQLTuner检查您的MariaDB并获取有关如何改进它的建议。 它不会为你做所有的工作。 该脚本仅向您提供如何改进和优化MariaDB的建议。

运行脚本：

curl -L http://mysqltuner.pl/ | perl

并检查建议。 做一些研究和使用谷歌每个建议。 如果你不知道自己在做什么，请联系其他人，让他们为你做，或者跳过这个。

Mysqlcheck也适用于MariaDB，因此要一次优化所有MariaDB数据库，请运行以下命令：

mysqlcheck -A --auto-repair -u root -p

您可以在服务器和数据库上进行其他优化，因此如果您想进一步优化MariaDB，请自行研究.

如何在 MariaDB 中配置主从复制？

未分类

复制是用于为我们的数据库创建多个副本，这些副本可以在其它数据库上用于运行查询，像一些非常繁重的查询可能会影响主数据库服务器的性能，或者我们可以使用它来做数据冗余，或者兼具以上两个目的。我们可以将这个过程自动化，即主服务器到从服务器的复制过程自动进行。执行备份而不影响在主服务器上的写操作。

在我们前面的教程中，我们已经学习了如何安装与配置MariaDB，也学习了 管理 MariaDB 的一些基础命令。现在我们来学习，如何在 MariaDB 服务器上配置一个主从复制。
复制是用于为我们的数据库创建多个副本，这些副本可以在其它数据库上用于运行查询，像一些非常繁重的查询可能会影响主数据库服务器的性能，或者我们可以使用它来做数据冗余，或者兼具以上两个目的。我们可以将这个过程自动化，即主服务器到从服务器的复制过程自动进行。执行备份而不影响在主服务器上的写操作。

因此，我们现在去配置我们的主-从复制，它需要两台安装了 MariaDB 的机器。它们的 IP 地址如下：

  • 主服务器 – 192.168.1.120 主机名 – master.ltechlab.com
  • 从服务器 – 192.168.1.130 主机名 – slave.ltechlab.com

MariaDB 安装到这些机器上之后，我们继续进行本教程。如果你需要安装和配置 MariaDB 的教程，请查看以前的教程。

第 1 步 – 主服务器配置

我们现在进入到 MariaDB 中的一个命名为 important 的数据库，它将被复制到我们的从服务器。为开始这个过程，我们编辑名为 /etc/my.cnf 的文件，它是 MariaDB 的配置文件。

$ vi /etc/my.cnf

在这个文件中找到 [mysqld] 节，然后输入如下内容：

[mysqld]
log-bin
server_id=1
replicate-do-db=important
bind-address=192.168.1.120

保存并退出这个文件。完成之后，需要重启 MariaDB 服务。

$ systemctl restart mariadb

接下来，我们登入我们的主服务器上的 Mariadb 实例。

$ mysql -u root -p

在它上面创建一个命名为 slaveuser 的为主从复制使用的新用户，然后运行如下的命令为它分配所需要的权限：

STOP SLAVE;
GRANT REPLICATION SLAVE ON *.* TO  'slaveuser'@'%' IDENTIFIED BY 'iamslave';
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

注意： 我们配置主从复制需要 MASTER_LOG_FILE 和 MASTER_LOG_POS 的值，它可以通过 show master status 来获得，因此，你一定要确保你记下了它们的值。

这些命令运行完成之后，输入 exit 退出这个会话。

第 2 步 – 创建一个数据库备份，并将它移动到从服务器上

现在，我们需要去为我们的数据库 important 创建一个备份，可以使用 mysqldump 命令去备份。

$ mysqldump -u root -p important > important_backup.sql

备份完成后，我们需要重新登录到 MariaDB 数据库，并解锁我们的表。

$ mysql -u root -p
$ UNLOCK TABLES;

然后退出这个会话。现在，我们移动我们刚才的备份到从服务器上，它的 IP 地址是：192.168.1.130。
在主服务器上的配置已经完成了，现在，我们开始配置从服务器。

第 3 步：配置从服务器

我们再次去编辑（从服务器上的） /etc/my.cnf 文件，找到配置文件中的 [mysqld]节，然后输入如下内容：

[mysqld]
server-id = 2
replicate-do-db=important
[ …]

现在，我们恢复我们主数据库的备份到从服务器的 MariaDB 上，运行如下命令：

$ mysql -u root -p < /data/ important_backup.sql

当这个恢复过程结束之后，我们将通过登入到从服务器上的 MariaDB，为数据库 important 上的用户 ‘slaveuser’ 授权。

$ mysql -u root -p
GRANT ALL PRIVILEGES ON important.* TO 'slaveuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

接下来，为了这个变化生效，重启 MariaDB。

$ systemctl restart mariadb

第 4 步：启动复制

记住，我们需要 MASTER_LOG_FILE 和 MASTER_LOG_POS 变量的值，它可以通过在主服务器上运行 SHOW MASTER STATUS 获得。现在登入到从服务器上的 MariaDB，然后通过运行下列命令，告诉我们的从服务器它应该去哪里找主服务器。

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST= '192.168.1.110′, MASTER_USER='slaveuser', MASTER_PASSWORD='iamslave', MASTER_LOG_FILE='mariadb-bin.000001′, MASTER_LOG_POS=460;
SLAVE START;
SHOW SLAVE STATUSG;

注意： 请根据你的机器的具体情况来改变主服务器的配置。

第 5 步：测试复制

我们将在我们的主服务器上创建一个新表来测试主从复制是否正常工作。因此，登入到主服务器上的 MariaDB。

$ mysql -u root -p

选择数据库为 important：

use important;

在这个数据库上创建一个名为 test 的表：

create table test (c int);

然后在这个表中插入一些数据：

insert into test (c) value (1);

检索刚才插入的值是否存在：

select * from test;

你将会看到刚才你插入的值已经在这个新建的表中了。
现在，我们登入到从服务器的数据库中，查看主从复制是否正常工作。

$ mysql -u root -p
$ use important;
$ select * from test;

你可以看到与前面在主服务器上的命令输出是一样的。因此，说明我们的主从服务工作正常，没有发生任何问题。

我们的教程结束了，请在下面的评论框中留下你的查询/问题。

Mysql/Mariadb备份(xtrabackup)还原实战

一、概述

之前的文章说到mysql的安装与mysql的基本使用;本文是后续补充,主要说明针对mysql或mariadb的备份与还原;众所周知,数据是重中之重,因此平时对企业数据需要做备份,当数据系统崩溃,数据丢失异常时,才能依据备份文件进行恢复!

本次的环境:
CentOS7.4_x64 , mysql5.7.21, xtrabackup

mysql的安装配置可参考之前系列文章;只补充相关配置项的开启;以及xtrabackup安装使用;
用到的演示数据导入mysql数据库

[root@db ~]# mysql -uroot -predhat < testdb.sql
或
mysql> source   testdb.sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| example            |
| mysql              |
| performance_schema |
| study              |
| sys                |
以上study即为测试数据库包含以下测试表

mysql> show tables;
+-----------------+
| Tables_in_study |
+-----------------+
| class           |
| course          |
| part            |
| score           |
| student         |
| tb31            |
| tb32            |
| teacher         |
| test1           |
| test2           |
| user_info       |
+-----------------+

测试数据库及数据表准备完成,在进行数据的备份与恢复前,我们先简单了解下数据库备份与恢复的相关概念原理;

关于数据库的备份与还原

为什么备份？
主要是为了灾难恢复如:硬件故障（冗余）、软件故障（bug）、自然灾害、黑客攻击、误操作、以及测试需要导出数据等;
还原或叫恢复时即基于以往的备份文件;

备份类型

全量备份、增量备份、差异备份：
完全备份: 备份数据的副本(某时间点);
增量备份：仅备份自上一次完全备份或 增量备份以来变量的那部数据；
差异备份：仅备份自上一次完全备份以来变量的那部数据；

物理备份、逻辑备份：
物理备份：复制数据文件进行的备份；
逻辑备份：从数据库导出数据另存在一个或多个文件中；

根据数据服务是否在线：
热备：读写操作均可进行的状态下所做的备份；
温备：可读但不可写状态下进行的备份；
冷备：读写操作均不可进行的状态下所做的备份
以上的各备份类型备份执行时只能备份数据在备份时的状态,如想要恢复数据库崩溃那一刻的状态,需要打开binary log功能,需要基于备份的数据+binary log来恢复到数据崩溃前一刻的状态;
备份的工具有mysqldump(温备,不适合大型数据的在线备份),xtrabackup(支持对InnoDB热备,开源专业的备份数据,支持mysql/mariadb)本文将通过mysqldump与xtrabackup来说明数据的备份与恢复(异地);

无论那种工具备份,在恢复时均要binary log才能恢复到崩溃前的状态;因此需要配置数据库开启binary log功能;以下能mysql5.7.21

#cat /usr/local/mysql/etc/my.cnf
server-id       = 1
log_bin         = /data1/mysqldb/mysql-bin.log

二、mysqldump备份与恢复

mysqldump使用说明

单进程逻辑备份、完全备份、部分备份；

Usage: 
mysqldump [OPTIONS] database [tables]
         OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
         OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

mysqldump mydb：表级别备份(还原时库需要存在)
mysqldump --databases mydb：库级别备份(库不在会自行创建库)

MyISAM存储引擎：支持温备，备份时要锁定表；
         -x, --lock-all-tables：锁定所有库的所有表，读锁；
         -l, --lock-tables：锁定指定库所有表；

InnoDB存储引擎：支持温备和热备；
         -x, --lock-all-tables：锁定所有库的所有表，读锁；
         -l, --lock-tables：锁定指定库所有表；
        --single-transaction：创建一个事务，基于此快照执行备份；
                -R, --routines：存储过程和存储函数；
                --triggers      触发器
                -E, --events      事件

                 --master-data[=#]
                        1：记录为CHANGE MASTER TO语句，此语句不被注释；
                        2：记录为CHANGE MASTER TO语句，此语句被注释；

                --flush-logs：锁定表完成后，即进行日志刷新操作(重新生成binlog日志)；

基于mysqldump备份study数据库

热备,备份存储过程和存储函数,事件,并记得下事件位置;(便于从binlog中的位置开始恢复到故障前)
#mysqldump -uroot -predhat --single-transaction  -R -E --triggers --master-data=2 --databases study >/home/san/studydb.sql

说明:

less studydb.sql

会看到以下内容

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=154;

这就是–master-data=2 选项作用,注释了,binary log 点在154

模拟备份后数据修改操作

修改前的:
mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   1 | san   |   20 | 男     |        1 |
|   2 | dong  |   29 | 男     |        2 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  10 | e     |   22 | 男     |        3 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
+-----+-------+------+--------+----------+
11 rows in set (0.00 sec)
增加一条:
mysql> insert into user_info values(13,'hi',18,'男',4);
Query OK, 1 row affected (0.03 sec)
删除一条:
mysql> delete  from user_info where nid=1;
Query OK, 1 row affected (0.01 sec)
最终在上次备份后user_info数据如下:
mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   2 | dong  |   29 | 男     |        2 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  10 | e     |   22 | 男     |        3 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
|  13 | hi    |   18 | 男     |        4 |
+-----+-------+------+--------+----------+
11 rows in set (0.00 sec)
可以看出少了一条,加了一条;

模拟数据库损坏并恢复study数据库

关闭mysql并到数据目录删除study数据库;

假设发现study数据已经丢失了;

数据库运行正常;查看binlog位置
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       815 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |       177 |
| mysql-bin.000004 |   1890875 |
| mysql-bin.000005 |       725 |
+------------------+-----------+
记住这里最后一个binlog文件及位置是mysql-bin.000005   725
结合上面备份文件中的-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=154; 可以分析出备份时位置是154而数据库丢失前是725
因此我们恢复study数据库里需要恢复上次的全备+加mysql-bin.000005中的154-725内容;

模拟study丢失过程(传说溃的删库路)
[root@db mysqldb]# service stop mysqld
[root@db mysqldb]# pwd
/data1/mysqldb
[root@db mysqldb]# rm -rf  study/
启动数据库
[root@db mysqldb]# service stop mysqld
登录数据库并查看发现study数据库已经丢失了

还原数据库

mysql -uroot -predhat < studydb.sql
mysql> show databases;
可发现已经恢复;但是之前完整备份的到崩溃前的修改不见了;如下:
mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   1 | san   |   20 | 男     |        1 |
|   2 | dong  |   29 | 男     |        2 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  10 | e     |   22 | 男     |        3 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
+-----+-------+------+--------+----------+
11 rows in set (0.00 sec)

结合binlog恢复:

从binlog上导出sql文件
[root@db mysqldb]# mysqlbinlog mysql-bin.000005 >/root/binlog.sql
登录mysql恢复
恢复过程中临时关闭binlog记录
mysql> set @@session.sql_log_bin=OFF;
mysql> source binlog.sql;
Query OK, 0 rows affected (0.00 sec

mysql> set @@session.sql_log_bin=ON;
mysql> use study; 
mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   2 | dong  |   29 | 男     |        2 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  10 | e     |   22 | 男     |        3 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
|  13 | hi    |   18 | 男     |        4 |
+-----+-------+------+--------+----------+
11 rows in set (0.00 sec)

可以看出study数据库已经恢复到崩溃损坏前的状态;另外完全 可以新准备一台数据库服务器;把sql转移到新机器上恢复;前提数据配置参数需要一样;

三、xtrabackup备份与恢复

xtrabackup简介

xtrabackup是Percona一款开源工具,支持innodb,Xtradb(mariadb)引擎数据库的热备;
对MyISAM：温备，不支持增量备份;InnoDB：热备，增量；
物理备份，速率快、可靠；备份完成后自动校验备份结果集是否可用；还原速度快
功能介绍与Innobackup(mysql企业版收费)对比参考官网
所数据库引擎请使用innodb引擎

xtrabackup安装与使用说明

安装
[官方下载地址](https://www.percona.com/downloads/XtraBackup/LATEST/)
本次使用percona-xtrabackup-24-2.4.8-1
[root@db ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.8/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm
[root@db ~]# yum install ./percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm -y

**获取帮助与使用:**
可以通过man  xtrabackup 获取详细使用说明与实例
Usage: 
 innobackupex [--defaults-file=#] --backup | innobackupex [--defaults-file=#] --prepare] [OPTIONS]
 备份用到的主要选项:
    --defaults-file=     #mysql或mariadb配置文件
        --user=                  #备份时使用的用户(对备份的数据库有备份权限)
        --password=         #备份用户密码
        -H | --host=            #localhost或远程主机

**恢复时到的主要选项:**
        --apply-log         #分析获取binary log文件生成backup_binlog_info文件
        ---copy-back      #基于backup_binlog_info等文件恢复
注:innobackupex是xtrabackup的软件链接;

xtrabackup全备与恢复:

注意:备份时数据库是在线状态;恢复时需要离线并且mysql数据目录为空;
备份:

创建备份目录 
mkdir -pv /data/backup
创建备份授权账号root(可以是其他用户最小权限)
mysql> GRANT ALL ON *.* TO 'root'@'127.0.0.1' identified by "redhat";
Query OK, 0 rows affected, 1 warning (0.00 sec)

[root@db mysqldb]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --user=root --password=redhat  --host=127.0.0.1 /data/backup
看到类似如下信息表示备份成功:
xtrabackup: Transaction log of lsn (7701576) to (7701585) was copied.
180401 11:52:35 completed OK!
同时在/data/backup目录中产生以时间为目录的备份目录
[root@db backup]# ll /data/backup/
drwxr-x--- 14 root root 4096 4月   1 11:52 2018-04-01_11-52-29

备份后对数据库study 中的表进行修改

删除student表
mysql> drop table student;
Query OK, 0 rows affected (0.04 sec)
往user_info表中插入两行
mysql> insert into user_info values(1,"san",18,"男",4),(14,"Hello",28,"女",2);
Query OK, 1 row affected (0.00 sec)

模拟数据库崩溃

注意binlog文件备份好;如果binglog和数据目录在一起
[root@db backup]# service mysqld stop
[root@db backup]# rm -rf /data1/mysqldb/*

恢复数据:

切换到备份数据目录
[root@db backup]# cd /data/backup/2018-04-01_11-52-29
事务回滚不提交
[root@db 2018-04-01_11-52-29]# innobackupex  --defaults-file=/usr/local/mysql/etc/my.cnf --apply-log ./
类似以下提示表示完成:
InnoDB: Shutdown completed; log sequence number 7702056
180401 12:13:40 completed OK!

数据还原

由于centos7默认有/etc/my.cnf文件
因此需要重命名my.cnf或移除以免影响恢复;
[root@db 2018-04-01_11-52-29]# innobackupex  --defaults-file=/usr/local/mysql/etc/my.cnf --copy-back ./
类似以下提示表示恢复完成:
180401 12:16:15 [01]        ...done
180401 12:16:15 completed OK!

恢复binlog中信息

查看全备中的binlog信息(文件和位置)

[root@db backup]# cat /data/backup/2018-04-01_11-52-29/xtrabackup_binlog_info
mysql-bin.000008        14775
由引可知在上次全备时的binglog文件是mysql-bin.000008位置为14775
获取binlog信息
[root@db backup]# mysqlbinlog -j 14775 mysql-bin.000008 >/data/backup/binlog.sql
还原binlog中的内容(全备后的修改数据内容)
切换到mysql数据目录(/data1/mysqldb)并修改权限
[root@db mysqldb]# cd /data1/mysqldb
[root@db mysqldb]# chown mysql.mysql *  -R
启动mysql
[root@db mysqldb]# service mysqld start

登录数据库并导入binlog.sql

mysql> source /data/backup/binlog.sql
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   1 | san   |   18 | 男     |        4 |
|   2 | dong  |   29 | 男     |        2 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  10 | e     |   22 | 男     |        3 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
|  13 | hi    |   18 | 男     |        4 |
|  14 | Hello |   28 | 女     |        2 |
+-----+-------+------+--------+----------+
13 rows in set (0.00 sec)

xtrabackup 增量备份与恢复

备份流程:
首次增量备份是基于完整备份后做的增量备份 ,后面的增量备份将基于前一次增量备份;
恢复流程:
合并完整备份事务 –>再合并第一次增量的事务–>….最后一次增量备份 +binlog日志

完整备份:

[root@db ~# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --user=root --password=redhat  --host=127.0.0.1 /data/backup
提示类似如下信息完成 :
xtrabackup: Transaction log of lsn (7802468) to (7802477) was copied.
180401 13:13:13 completed OK!
[root@db ~# ll /data/backup
2018-04-01_13-13-10    ######完整备份目录

模拟数据库的修改操作

删除第10行并新增一行
mysql> delete from user_info where nid=10;
Query OK, 1 row affected (0.01 sec)

mysql> insert into user_info value(15,'hehe',22,'男',1);
Query OK, 1 row affected (0.01 sec)

第一次增量备份

[root@db ~# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --incremental --user=root --password=redhat  --host=127.0.0.1 /data/backup/ --incremental-basedir=/data/backup/2018-04-01_13-13-10/
提示类似如下信息完成 :
xtrabackup: Transaction log of lsn (7803424) to (7803433) was copied.
180401 13:17:26 completed OK!

再次模拟数据库的修改操作

mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   1 | san   |   18 | 男     |        4 |
|   2 | dong  |   29 | 男     |        2 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
|  13 | hi    |   18 | 男     |        4 |
|  14 | Hello |   28 | 女     |        2 |
|  15 | hehe  |   22 | 男     |        1 |
+-----+-------+------+--------+----------+
13 rows in set (0.01 sec)
插入一行再删除一行
mysql> insert into user_info value(16,'haha',21,'女',3);
Query OK, 1 row affected (0.01 sec)

mysql> delete from user_info where nid=2;
Query OK, 1 row affected (0.01 sec)
mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   1 | san   |   18 | 男     |        4 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
|  13 | hi    |   18 | 男     |        4 |
|  14 | Hello |   28 | 女     |        2 |
|  15 | hehe  |   22 | 男     |        1 |
|  16 | haha  |   21 | 女     |        3 |
+-----+-------+------+--------+----------+
13 rows in set (0.00 sec)

第二次增量备份:

[root@db backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --incremental --user=root --password=redhat  --host=127.0.0.1 /data/backup/ --incremental-basedir=/data/backup/2018-04-01_13-17-21/
注意:这里的 --incremental-basedir=/data/backup/2018-04-01_13-17-21/ 是上一次增量备份 产生的备份 目录 
如果基于第一次完整备份 则成为差异备份

找出最近一次增量备份的binlog文件及信息

cd /data/backup/2018-04-01_13-21-56
[root@db 2018-04-01_13-21-56]# cat xtrabackup_binlog_info 
mysql-bin.000001    17452
备份 mysql-bin.000001 到/data/backup中
[root@db backup]# cd /data/backup
[root@db backup]# cp /data1/mysqldb/mysql-bin.000001 .
[root@db backup]# mysqlbinlog mysql-bin.000001 >binlog.sql

模拟数据库崩溃数据丢失

[root@db backup]# service mysqld stop
[root@db backup]# rm -rf /data1/mysqldb/*

数据恢复

[root@n1 backup]# ls
2018-04-01_13-13-10     2018-04-01_13-17-21    2018-04-01_13-21-56     binlog.sql    mysql-bin.000001

依次是完全整备份 ,第一次和第二次增量备份 目录 ,以及备份出来的binlog文件与binlog.sql
恢复过程:
首先对第1个(完整备份)合并只提交事务不回滚 再把第2个目录合并提交事务不回滚到第一个,再把第3个合并到第1个中;最后做一次回滚，再做统一事务提交;最后再加binlog恢复

完整备份 的事务合并
[root@db backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --apply-log --redo-only 2018-04-01_13-13-10/
合并第一次增量事务
[root@db backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --apply-log --redo-only 2018-04-01_13-13-10/ --incremental-dir=2018-04-01_13-17-21/
合并第二次增量事务
[root@db backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --apply-log --redo-only 2018-04-01_13-13-10/ --incremental-dir=2018-04-01_13-21-56/
合并所有的事务
[root@db backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf  --apply-log 2018-04-01_13-13-10/
提交还原事务
[root@db backup] innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf  --copy-back 2018-04-01_13-13-10/

修改还原数据权限与启动数据库:

[root@db backup]chown mysq.mysql /data1/mysqldb -R
[root@db backup] systemctl start mysqld

binlog事务恢复

mysql> source /data/backup/binlog.sql
mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name | age | gender | part_nid |
+-----+-------+------+--------+----------+
| 1 | san | 18 | 男 | 4 |
| 4 | Ling | 28 | 男 | 4 |
| 5 | ling | 28 | 男 | 3 |
| 6 | dong | 30 | 男 | 1 |
| 7 | b | 11 | 女 | 1 |
| 8 | c | 12 | 女 | 1 |
| 9 | d | 18 | 女 | 4 |
| 11 | f | 23 | 男 | 2 |
| 12 | dongy | 22 | 男 | 1 |
| 13 | hi | 18 | 男 | 4 |
| 14 | Hello | 28 | 女 | 2 |
| 15 | hehe | 22 | 男 | 1 |
| 16 | haha | 21 | 女 | 3 |
+-----+-------+------+--------+----------+
13 rows in set (0.00 sec)

到此增量备份 与恢复 已经 完成!

总结

日常数据库的备份是十分有必要的,而且不管用什么方法恢复,开启binary log十分重要,否则恢复不完整;binary log最好不要和数据目录一起,另外建议数据目录和binary log所在目录不要放在同一块物理磁盘;同时需要计划备份并实现异地备份;这样出现删库跑或崩溃数据丢失时就不怕了!本文很多步骤,可能存在遗漏之处,如有错误之处,欢迎指点。

记录分享两个 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证书全部一次就申请成功，无解中……。

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@’%’授权任意主机连接的时候需要加单引号，但有时又不需要。

记录一次失败的 Upgrade MySQL to MariaDB 经历

摘要

每次“折腾”其实都是有不少收获的，比如这次的折腾虽然以失败告终，但是至少也算是体验了一番Upgrade MySQL to MariaDB的过程，对MariaDB也算是有了一个初步的认识了。有条件还是要尽早将MySQL替换为MariaDB最好，至于说数据的导入、导出完全不用担心“转换”的，它们之间的兼容性那真的是“刚刚的”，毕竟是出自同一个创始人之手的开源数据库嘛！

其实这次升级 MySQL 是个很偶然的决定，主要就是看到了 wooCommerce 插件升级的时候联想到了最近总是发现在使用了 wooCommerce 插件后数据库总有不正常错误出现，从 wooCommerce 插件的支持情况来看可能是因为 MySQL 版本低于 5.6 造成的，所以就想升级 MySQL 的打算了，受制于服务器只有 1G 内存一直没有实现，还以为 MariaDB 对内存没有这个要求呢！

未分类

想到就要行动起来，因为使用的是军哥的 LNMP1.5 测试版，在 upgrade.sh 脚本里就自带了 Upgrade MySQL to MariaDB 的选项，就直接用脚本升级了，数据库太大了，编译安装耗时近一个多小时。结果是彻底失败，MariaDB 里竟然没有任何数据表，PhpMyAdmin 里也是彻底乱了套，界面错位，无法正常操作。折腾了两个多小时才发现是 ngx_lua_waf 拦截造成的，无奈暂时关闭 WAF。这时候站点已经都无法访问了。没有办法只能请出阿里云的镜像回滚恢复了。

恢复正常后还是不甘心呀！所以继续努力继续折腾，这次提前把数据库都导出来以备不时之需，关闭 WAF 防火墙重新来过，近一个小时的等待后终于成功了，这次很完美，数据表依然是丢失的，还好编译前有备份，立马导入数据，又发现 MariaDB 的 root 密码竟然无效了，只能重置数据库密码了。终于恢复网站访问了！么么哒！！！

未分类

等等，有点儿不对劲儿，服务器控制台终端好卡的感觉，一看负载，我去一直在“飙升”直至网站打开出现 503 错误，负载还在持续飙升一路到 50 多了！我去，这也太猛了吧！难道是因为 MariaDB 没有优化所致？于是又对 MariaDB 的配置文件进行了一番研究调整了一些参数优化了一番，重新载入 MariaDB 后，CPU 的负载依然是 90%以上，这时候突然想起来 MySQL 5.6 以后的版本对服务器内存有要求至少是 2G 以上，难道 MariaDB 也有这个要求，MariaDB 的版本我选的是 10.2.12 版，百度、谷姐一番后基本上可以确定就是服务器物理内存太小造成的了！哎，无语了，看来升级到 MariaDB 也是没有办法的，只能是镜像回滚恢复了。

至此，这次 Upgrade MySQL to MariaDB 的折腾还是以失败而告终了，服务器配置太低是主要原因，虽然 MariaDB 那么的诱人，但至少目前来看是无福消受了！我说为啥阿里云 ECS 1G1 核的卖的这么便宜，原来又是“套路”呀！唉，真的是彻底的服了！

不过，每次“折腾”其实都是有不少收获的，比如这次的折腾虽然以失败告终，但是至少也算是体验了一番 Upgrade MySQL to MariaDB 的过程，对 MariaDB 也算是有了一个初步的认识了。有条件还是要尽早将 MySQL 替换为 MariaDB 最好，至于说数据的导入、导出完全不用担心“转换”的，它们之间的兼容性那真的是“刚刚的”，毕竟是出自同一个创始人之手的开源数据库嘛！

MariaDB/MySQL配置文件my.cnf解读

MariaDB/MySQL的默认设置性能非常差，仅仅起一个功能测试的作用，不能用在生产环境中，因此要对一些参数进行调整优化。当然，对配置文件各参数的调整需要根据实际环境，不同时期不同数量级的数据进行性能优化。

未分类

MySQL/MariaDB 配置文件位于 /etc/my.cnf。 每次更改此文件后你需要重启 MySQL 服务，以使更改生效。本文的参数配置基于MariaDB 10.2，硬件内存4G。文中一些选项值的设置只是推荐值，不要盲目的接受这些建议。每个 MySQL 设置都是不同的，在进行任何更改之前需要慎重考虑。

基本结构

[client]
port        = 3306 #客户端默认连接端口
socket      = /tmp/mysql.sock  #用于本地连接的socket套接字

[mysqld]  # 服务端基本配置
port        = 3306 # mysql监听端口
socket      = /tmp/mysql.sock #为MySQL客户端程序和服务器之间的本地通讯指定一个套接字文件
user    = mariadb # mysql启动用户
basedir = /usr/local/mariadb # 安装目录
datadir = /data/mysql # 数据库数据文件存放目录
log_error = /data/mysql/mariadb.err #记录错误日志文件
pid-file = /data/mysql/mariadb.pid  #pid所在的目录
skip-external-locking  #不使用系统锁定，要使用myisamchk,必须关闭服务器
...

my.cnf配置文件以方括号如[client]区分模块作用域，其中[client]表示客户端配置，[mysqld]是服务端配置。以上几个选项是最基础的，每个选项有简单说明，其中有关路径的配置是基于本站文章：CentOS7下源码编译安装MariaDB 10.2和Linux下使用二进制格式安装MariaDB的安装路径来配置的，所以在配置前可以先按照两文先安装好MairaDB。

选项配置及说明

key_buffer_size = 32M 这个参数用来缓存MyISAM存储引擎的索引参数。MySQL5.5默认为InnoDB存储引擎，所以这个参数可以设置小点，64MB即可。

max_allowed_packet = 1M 允许最大接收数据包的大小，防止服务器发送过大的数据包。可以设置为16MB或者更大，但设置的太大也可能有危险。

table_open_cache = 128 #MySQL每打开一个表，都会读入一些数据到table_open_cache缓存中，当MySQL在这个缓存中找不到相应信息时，才会去磁盘上读取。默认值64，假定系统有200个并发连接，则需将此参数设置为200*N(N为每个连接所需的文件描述符数目)；当把table_open_cache设置为很大时，如果系统处理不了那么多文件描述符，那么就会出现客户端失效，连接不上。

sort_buffer_size = 768K 在表进行order by和group by排序操作时，由于排序的字段没有索引，会出现Using filesort，为了提高性能，可用此参数增加每个线程分配的缓冲区大小。默认为256KB，这个参数不要设置过大，一般在128～256KB即可。另外，一般出现Using filesort的时候，要通过增加索引来解决。

net_buffer_length = 8K 包消息缓冲区初始化为net_buffer_length字节，但需要时可以增长到max_allowed_packet字节。

read_buffer_size = 768K 该参数用于表的顺序扫描，表示每个线程分配的缓冲区大小。比如在进行全表扫描时，MySQL会按照数据的存储顺序依次读取数据块，每次读取的数据块首先会暂存在read_buffer_size中，当buffer空间被写满或者全部数据读取结束后，再将buffer中的数据返回给上层调用者，以提高效率。默认为128K，这个参数不要设置过大，一般在128～256之间。

read_rnd_buffer_size = 512K 该参数用于表的随机读取，表示每个线程分配的缓冲区大小。比如，按照一个非索引字段做order by排序操作时，就会利用这个缓冲区来暂存读取的数据。默认为256KB，这个参数不要设置过大，一般在128～512KB。

myisam_sort_buffer_size = 8M 当对MyISAM表执行repair table或创建索引时，用以缓存排序索引；设置太小时可能会遇到” myisam_sort_buffer_size is too small”

thread_cache_size = 16 线程池，线程缓存。用来缓存空闲的线程，以至于不被销毁，如果线程缓存在的空闲线程，需要重新建立新连接，则会优先调用线程池中的缓存，很快就能响应连接请求。每建立一个连接，都需要一个线程与之匹配。

query_cache_size = 16M 缓存select语句和结果集大小的参数。查询缓存会存储一个select查询的文本与被传送到客户端的相应结果。如果之后接收到一个相同的查询，服务器会从查询缓存中检索结果，而不是再次分析和执行这个同样的查询。如果你的环境中写操作很少，读操作频繁，那么打开query_cache_type=1，会对性能有明显提升。如果写操作频繁，则应该关闭它（query_cache_type=0）。

tmp_table_size = 32M 临时HEAP数据表的最大长度(默认设置是32M); 超过这个长度的临时数据表将被转换为MyISAM数据表并存入一个临时文件。

explicit_defaults_for_timestamp = true 是否显示默认时间戳

#skip-networking

max_connections = 500 该参数用来设置最大连接数，告诉你当前你的服务器允许多少并发连接。默认为100，一般设置为512-1000即可。请记住，太多的连接会导致内存的使用量过高并且会锁住你的 MySQL 服务器。一般小网站需要 100-200 的连接数，而较大可能需要 500-800 甚至更多。这里的值很大程度上取决于你 MySQL/MariaDB 的使用情况。

max_connect_errors = 100 如果有时网络抽风，或者应用配置错误，或者其他原因导致客户端短时间内不断的尝试连接，客户端可能会被列入黑名单，然后将无法连接，直到再次刷新主机缓存。这个选项默认值太小了，可以考虑设的足够大（如果你的服务器配置够强大的话）。

open_files_limit = 65535 mysql打开最大文件数

log-bin=mysql-bin 这些路径相对于datadir

binlog_format=mixed 日志格式

server-id = 1 给服务器分配一个独一无二的ID编号; n的取值范围是1~2的32次方启用二进制日志功能。在复制数据同步的时候会用到，Helloweba后面会有文章介绍。

expire_logs_days = 10 启用二进制日志后，保留日志的天数。服务器会自动清理指定天数前的日志文件，如果不设置则会导致服务器空间耗尽。一般设置为7～14天。

default_storage_engine = InnoDB 新数据表的默认存储引擎(默认设置是MyISAM)。这项设置还可以通过–default-table-type选项来设置。

innodb_file_per_table = 1 InnoDB 提供了更灵活的方式，它把每个数据库的信息保存在一个 .ibd 数据文件中。每个 .idb 文件代表它自己的表空间。通过这样的方式可以更快地完成类似 “TRUNCATE” 的数据库操作，当删除或截断一个数据库表时，你也可以回收未使用的空间。这样配置的另一个好处是你可以将某些数据库表放在一个单独的存储设备。这可以大大提升你磁盘的 I/O 负载。

innodb_data_home_dir = /data/mysql InnoDB主目录，所有与InnoDB数据表有关的目录或文件路径都相对于这个路径。在默认的情况下，这个主目录就是MySQL的数据目录。

innodb_data_file_path = ibdata1:10M:autoextend 用来容纳InnoDB为数据表的表空间: 可能涉及一个以上的文件; 每一个表空间文件的最大长度都必须以字节(B)、兆字节(MB)或千兆字节(GB)为单位给出; 表空间文件的名字必须以分号隔开; 最后一个表空间文件还可以带一个autoextend属性和一个最大长度(max:n)。

innodb_log_group_home_dir = /data/mysql 用来存放InnoDB日志文件的目录路径(如ib_logfile0、ib_logfile1等)。在默认的情况下，InnoDB驱动程序将使用 MySQL数据目录作为自己保存日志文件的位置。

innodb_buffer_pool_size = 128M 这个参数是InnoDB存储引擎的核心参数，默认为128KB，这个参数要设置为物理内存的60%～70%。

innodb_log_file_size = 32M 事务日志文件写操作缓存区的最大长度(默认设置是1MB)。

innodb_log_buffer_size = 8M 事务日志所使用的缓存区。InnoDB在写事务日志的时候，为了提高性能，先将信息写入Innodb Log Buffer中，当满足innodb_flush_log_trx_commit参数所设置的相应条件（或者日志缓冲区写满）时，再将日志写到文件（或者同步到磁盘）中。可以通过innodb_log_buffer_size参数设置其可以使用的最大内存空间。默认是8MB，一般为16～64MB即可。

innodb_flush_log_at_trx_commit = 1 这个选项决定着什么时候把日志信息写入日志文件以及什么时候把这些文件物理地写(术语称为”同步”)到硬盘上。设置值0的意思是每隔一秒写一次日 志并进行 同步，这可以减少硬盘写操作次数，但可能造成数据丢失; 设置值1(设置设置)的意思是在每执行完一条COMMIT命令就写一次日志并进行同步，这可以防止数据丢失，但硬盘写操作可能会很频繁; 设置值2是一般折衷的办法，即每执行完一条COMMIT命令写一次日志，每隔一秒进行一次同步。

innodb_lock_wait_timeout = 50 如果某个事务在等待n秒(s)后还没有获得所需要的资源，就使用ROLLBACK命令放弃这个事务。这项设置对于发现和处理未能被InnoDB数据表驱动 程序识别出来的死锁条件有着重要的意义。这个选项的默认设置是50s。

示例：内存4G的my.cnf配置

[client]
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
user    = mariadb
basedir = /usr/local/mariadb
datadir = /data/mysql
log_error = /data/mysql/mariadb.err
pid-file = /data/mysql/mariadb.pid
skip-external-locking
key_buffer_size = 64M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
net_buffer_length = 8K
read_buffer_size = 1M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 16M
thread_cache_size = 32
query_cache_size = 32M
tmp_table_size = 64M

explicit_defaults_for_timestamp = true
#skip-networking
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id   = 1
expire_logs_days = 10

default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_data_home_dir = /data/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/mysql
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 1M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

使用sysbench测试mariadb性能的经验总结

github地址: https://github.com/akopytov/sysbench
我测试用的系统: CentOS Linux release 7.2.1511 (Core)
sysbench版本用了最新的, 1.1.0
过程中也搜了很多教程, 所以重复的就不多说, 主要说一下教程中没看到的或不够清楚的

1. 安装

安装可以用yum也可以自己下下来编译, 我两种都尝试了一下, 自己编译注意看情况指定–with-mysql-includes和 –with-mysql-libs, 比如:

./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib

如果是上面这种方式编译安装的, 将会只带mysql驱动, 跑测试的时候就不用指定 –db-driver

如果是yum安装的, 默认除了mysql还会带一个PostgreSQL的驱动, 就得指定 –db-driver=mysql, 否则会报错提示没指定驱动啥的

带了哪些驱动可以从sysbench –help里看到

2. 跑测试

勤看帮助。

sysbench –help的最上面是通用选项, 主要用到的比如线程数(–threads), 限制跑多久(–time), 跑多少次事件(–events)
sysbench –help的最后, 可以看到自带的测试方式有这些, 这就是–test=后面可以带的参数

Compiled-in tests:
  fileio - File I/O test
  cpu - CPU performance test
  memory - Memory functions speed test
  threads - Threads subsystem performance test
  mutex - Mutex performance test

基础用法(help, prepare, run, cleanup)参考github页面

想测数据库的, 一开始可能会有点懵逼, 因为这里没看到测试数据库的参数, 网上教程大都是旧的, 按那些填也没用, 而github主页上好像也没找到说明?

我也蛋疼了好一会儿, –help和github主页看了无数遍, 终于才在github主页最上面瞄到这个:

未分类

我tm也是醉了, 这里一般都当作介绍区域看一眼就跳过了, 谁会想到唯一能找到使用方式的线索在这里???

所以搜一下这个oltp_.lua吧, find / -name oltp_.lua, 会列出一堆lua脚本, 选择你要测的直接复制完整路径作为参数传给–test, 然后其他一堆参数该填的填, 就可以跑起来了

要注意的主要是这个参数–rand-type=, 这里官网给了可选的范围: {uniform, gaussian, special, pareto, zipfian}, 其他几个还能勉强看出是一些数学分布, uniform和special完全不知道是干什么的, uniform只知道有个统一的意思, 并不能确定, 搜也搜不到, 于是提了个issue问了一下:

未分类

好吧原来uniform就是离散均匀分布, 简单来说就是普通随机, special如作者所说，是不科学的，但它又是默认值……（还能说啥，前面的白测了_(:з」∠)
(简单对比了一下uniform和special, special测出来的性能大致是uniform的1.7倍)

3. 各lua脚本的区别

虽然没写过lua, 不过打开脚本看看基本能看出来, 我只测了下面三个(下列为后缀), 它们分别干的事是:

•  insert:, 即为纯insert
•  read_write 包括:  select (range, sum, order, distinct) ,  update,  update(non-index),  delete
•  read_only: select (range, sum, order, distinct)

--range_selects 参数决定select是否是仅单点 (只使用where=,而不使用上面括号中条件)

4. 其他

注意帮助最后一句写的See ‘sysbench help’ for a list of options for each test.
所以要看参数的话就是 sysbench xx.lua help, 可以看到参数用来设置测试用的表数量, 表大小之类的

自己撸了个python脚本测insert(主要是为了测别的), 用了sqlalchemy, 性能和测试脚本差距比较大…

我是三台虚拟机做了galera分布式集群的, 实测发现单节点跑测试时其他节点cpu也会飚起来, 所以就不贴结果了, 可能参考性不够强.

编译安装MariaDB

环境centOS6.9 MariaDB10.1.8

1、环境准备

yum -y install ncurses-devel 
yum -y install gcc-c++

2、介质准备

mariadb10.1.8，cmake3.8.2

3、安装cmake

tar xfz cmake-3.8.2.tar.gz
    cd cmake-3.8.2
    ./bootstrap
    make
    make install
    cmake --version

4、创建用户和组、数据目录

groupadd mysql
useradd -r -g mysql -s /sbin/nologin mysql
mkdir -p /home/data/mariadbdata
chown -R mysql:mysql /home/data/mariadbdata

5、解压、编译安装

tar xfz mariadb-10.1.8.tar.gz
cd mariadb-10.1.8

cmake . -DCMAKE_INSTALL_PREFIX=/home/opt/mariadb -DMYSQL_DATADIR=/home/date/mariadbdata -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci

make
make install

6、安装数据库，配置my.cnf

cp -pv /home/opt/mariadb/support-files/my-innodb-heavy-4G.cnf /home/opt/mariadb/my.cnf
scripts/mysql_install_db --defaults-file=${INSTALL_DIR}/my.cnf --user=mysql --basedir=/home/opt/mariadb --datadir=/home/date/mariadbdata

7、配成Linux服务(名称mariadb)

cp -pv ${INSTALL_DIR}/support-files/mysql.server /etc/init.d/mariadb
chkconfig --add /etc/init.d/mariadb
chkconfig --list mariadb
service mariadb start