MariaDB Setup on Debian 7

MariaDB是一个下拉更换为MySQL,并努力成为数据库专业人员寻找一个强大的,可扩展的,可靠的SQL Server的合乎逻辑的选择。本指南将帮助初学者上手MariaDB在Debian7(喘息)的Linode。

本指南中所需要的步骤需要root权限。一定要运行下面的步骤以root身份或使用sudo的前缀。有关权限的详细信息,请参阅我们的用户和组指导。

先决条件

执行以下命令,以确保您系统的软件包数据库是最新的,所有已安装的软件是否为最新版本上运行:

 

安装MariaDB

在本节中,您将安装MariaDB,并设置密码MariaDB root用户。

首先,导入GPG键,以便APT可以验证它下载包的完整性:

 

      1. Sample output:

接下来,找到MariaDB库这是最接近你的Linode数据中心的位置,使用MariaDB的仓库配置工具。随着存储库配置工具,选择Debian的,那么Debian的7(喘息),然后5.5,然后选择镜像。

 

      您选择的MariaDB库添加到您的sources.list文件。打开您的/etc/apt/sources.list文件进行编辑:
  1. Add your chosen repository to the bottom of the file:

    /etc/apt/sources.list

    Remember to choose the repository nearest to your server location.

  2. Retrieve the information APT needs to install MariaDB:

  3. Install MariaDB:

    系统将提示您设置为MariaDB root用户密码:

    选择一个强密码来保护您的服务器。

    安装后,Debian的将启动MariaDB服务器,并设置在重新启动时自动启动该服务。
    使用MariaDB

    在本节中,您将学习如何连接到MariaDB和执行基本的SQL命令。

    与MariaDB交互的标准工具是MySQL的客户端程序。上手,发出以下命令连接到MariaDB作为根用户:

  4. 当出现提示时,输入您在安装MariaDB你设置root的密码。你会看到类似以下的输出:

    Note the (none) text in the MariaDB prompt. It will be used to display the current working database. Since you haven’t selected any database yet, it is displayed as (none).

  5. Let’s try to create a sample database, which we’ll later populate with data. Type the following commands to create a database named testdb, which is owned by a new user testuser. These commands also set the password secretpassword for the new user:

    The final line logs out the root user from MariaDB.

  6. Log in to MariaDB as testuser:

  7. Use the database testdb:

  8. Create a new table and populate it with some data:

  9. Verify that the new data has been inserted:

    Which should show this output:

  10. Exit the MariaDB client by typing:

For more information about SQL commands, you might want to check the SQL Commands page on the MariaDB Knowledge Base. To view MariaDB’s command list from within the client, type:

Output:

配置MariaDB

要配置MariaDB运行你想要的方式,编辑/etc/mysql/my.cnf文件。此文件控制大多数服务器系统变量,您通常会在希望默认离开。

每当你做出改变/etc/mysql/my.cnf,重新启动服务器发出以下命令:

1

服务重新启动mysql的

保护MariaDB服务器

我们建议您通过执行以下命令,保护您的MariaDB服务器:

1

mysql_secure_installation

你会被要求更改root密码,删除匿名用户,禁用本地主机以外的root登录,删除匿名用户,并删除测试数据库。建议你回答Y所有的问题。查看下面的示例输出:

 

Do not be concerned about the find_mysql_client: not found message. This is a known bug as described in this MariaDB mailing list. Also, unlike MySQL, MariaDB does not install a test database by default, so you can ignore this error message:

In short, neither warning is a problem.

远程用户连接

让我们来看看如何让以前创建的用户,testuser的,连接到远程MariaDB(默认情况下,MariaDB将允许从本地主机只连接)。

打开MariaDB服务器到互联网使得它不太安全。如果你需要从什么地方比其他本地主机连接时,请确保你实施的防火墙规则只允许从特定IP地址的连接。

首先,我们需要从授予为testuser的用户的远程主机的用户连接。登录到MariaDB为根:

 

  1. Allow testuser to connect from remote hosts:
  2. Configure MariaDB to listen to all network interfaces. Open the /etc/mysql/my.cnf file:
  3. Edit the bind-address variable to listen to all network interfaces:
    /etc/mysql/my.cnf
  4. Restart the server:
  5. Test your connection from your local computer to your MariaDB server, replacing testuser with your username, and example.com with your domain or IP address:

如果登录成功,你应该看到MariaDB欢迎消息和shell提示符。
调整MariaDB

MySQL的调谐器是连接MariaDB的运行实例,并提供了基于工作负载配置建议的有用工具。你应该让你的MariaDB实例上运行的调谐器之前运行至少24个小时。该实例已经运行的时间越长,效果越好咨询调谐器将提供。

安装MySQL调谐器发出以下命令:

 

Run MySQL tuner with the following command:

Below is some sample output:

Pay attention to the output, especially the recommendations at the end. It will point you towards which variables you should adjust in the [mysqld] section of your /etc/mysql/my.cnf file.

How to Reset MariaDB’s root Password

If you forget your root password, you can easily reset it by following the instructions below:

  1. Stop the MariaDB server:
  2. Start the server with the skip-grant-tables setting so you can log in to MariaDB without the password:
  3. Now you can connect to the MariaDB server as root without a password:
  4. Within the MariaDB client, issue the following commands to reset the password for the root user and log out:
  5. Restart the MariaDB server:
  6. Connect to the MariaDB server using your new password: