MariaDB是一个下拉更换为MySQL,并努力成为数据库专业人员寻找一个强大的,可扩展的,可靠的SQL Server的合乎逻辑的选择。本指南将帮助初学者上手MariaDB在Debian7(喘息)的Linode。
本指南中所需要的步骤需要root权限。一定要运行下面的步骤以root身份或使用sudo的前缀。有关权限的详细信息,请参阅我们的用户和组指导。
先决条件
执行以下命令,以确保您系统的软件包数据库是最新的,所有已安装的软件是否为最新版本上运行:
|
|
安装MariaDB
在本节中,您将安装MariaDB,并设置密码MariaDB root用户。
首先,导入GPG键,以便APT可以验证它下载包的完整性:
-
111apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
Sample output:
123456123456123456Executing: gpg --ignore-time-conflict --no-options --no-default-keyring --secret-keyring /tmp/tmp.THMA4yorjI --trustdb-name /etc/apt//trustdb.gpg --keyring /etc/apt/trusted.gpg --primary-keyring /etc/apt/trusted.gpg --keyring /etc/apt/trusted.gpg.d//debian-archive-squeeze-automatic.gpg --keyring /etc/apt/trusted.gpg.d//debian-archive-squeeze-stable.gpg --keyring /etc/apt/trusted.gpg.d//debian-archive-wheezy-automatic.gpg --keyring /etc/apt/trusted.gpg.d//debian-archive-wheezy-stable.gpg --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943dbgpg: requesting key 1BB943DB from hkp server keyserver.ubuntu.comgpg: no ultimately trusted keys foundgpg: Total number processed: 1gpg: imported: 1
接下来,找到MariaDB库这是最接近你的Linode数据中心的位置,使用MariaDB的仓库配置工具。随着存储库配置工具,选择Debian的,那么Debian的7(喘息),然后5.5,然后选择镜像。
- 您选择的MariaDB库添加到您的sources.list文件。打开您的/etc/apt/sources.list文件进行编辑:
-
111nano /etc/apt/sources.list
Add your chosen repository to the bottom of the file:
- /etc/apt/sources.list
-
123123123# MariaDB 5.5 repository listdeb http://ftp.osuosl.org/pub/mariadb/repo/5.5/debian wheezy maindeb-src http://ftp.osuosl.org/pub/mariadb/repo/5.5/debian wheezy main
Remember to choose the repository nearest to your server location.
- Retrieve the information APT needs to install MariaDB:
111apt-get update
- Install MariaDB:
111apt-get install mariadb-server
系统将提示您设置为MariaDB root用户密码:
选择一个强密码来保护您的服务器。
安装后,Debian的将启动MariaDB服务器,并设置在重新启动时自动启动该服务。
使用MariaDB在本节中,您将学习如何连接到MariaDB和执行基本的SQL命令。
与MariaDB交互的标准工具是MySQL的客户端程序。上手,发出以下命令连接到MariaDB作为根用户:
-
111mysql -u root -p
当出现提示时,输入您在安装MariaDB你设置root的密码。你会看到类似以下的输出:
123456789123456789123456789Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 30Server version: 5.5.37-MariaDB-1~wheezy-log mariadb.org binary distributionCopyright (c) 2000, 2014, Oracle, Monty Program Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]>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)
. - 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:
123412341234CREATE DATABASE testdb;GRANT ALL PRIVILEGES ON testdb.* TO testuser@localhost IDENTIFIED BY 'secretpassword';FLUSH PRIVILEGES;quit
The final line logs out the root user from MariaDB.
- Log in to MariaDB as testuser:
111mysql -u testuser -p
- Use the database testdb:
111USE testdb;
- Create a new table and populate it with some data:
123412341234CREATE TABLE products (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), price DECIMAL(6,2));INSERT INTO products (name, price) VALUES ('MacBook Pro', 1600.0);INSERT INTO products (name, price) VALUES ('Dell', 850.0);INSERT INTO products (name, price) VALUES ('Acer', 775.0);
- Verify that the new data has been inserted:
111SELECT * FROM products;
Which should show this output:
123456781234567812345678+----+-------------+---------+| id | name | price |+----+-------------+---------+| 1 | MacBook Pro | 1600.00 || 2 | Dell | 850.00 || 3 | Acer | 775.00 |+----+-------------+---------+3 rows in set (0.00 sec) - Exit the MariaDB client by typing:
111quit
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:
1 1
1 ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't existIn short, neither warning is a problem.
远程用户连接
让我们来看看如何让以前创建的用户,testuser的,连接到远程MariaDB(默认情况下,MariaDB将允许从本地主机只连接)。
打开MariaDB服务器到互联网使得它不太安全。如果你需要从什么地方比其他本地主机连接时,请确保你实施的防火墙规则只允许从特定IP地址的连接。
首先,我们需要从授予为testuser的用户的远程主机的用户连接。登录到MariaDB为根:
-
111mysql -u root -p
- Allow testuser to connect from remote hosts:
123123123GRANT ALL PRIVILEGES ON testdb.* TO testuser@'%' IDENTIFIED BY 'secretpassword';FLUSH PRIVILEGES;quit
- Configure MariaDB to listen to all network interfaces. Open the
/etc/mysql/my.cnf
file:111nano /etc/mysql/my.cnf - Edit the
bind-address
variable to listen to all network interfaces:- /etc/mysql/my.cnf
-
111bind-address = 0.0.0.0
- Restart the server:
111service mysql restart
- 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:
111mysql -u testuser -h example.com -p
如果登录成功,你应该看到MariaDB欢迎消息和shell提示符。
调整MariaDB
MySQL的调谐器是连接MariaDB的运行实例,并提供了基于工作负载配置建议的有用工具。你应该让你的MariaDB实例上运行的调谐器之前运行至少24个小时。该实例已经运行的时间越长,效果越好咨询调谐器将提供。
安装MySQL调谐器发出以下命令:
1 |
1 |
|
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:
- Stop the MariaDB server:
111service mysql stop
- Start the server with the
skip-grant-tables
setting so you can log in to MariaDB without the password:111mysqld_safe --skip-grant-tables & - Now you can connect to the MariaDB server as root without a password:
111mysql -u root
- Within the MariaDB client, issue the following commands to reset the password for the root user and log out:
123412341234USE mysqlUPDATE user SET password=PASSWORD('yournewpassword') WHERE user='root';FLUSH PRIVILEGES;quit
- Restart the MariaDB server:
111service mysql restart
- Connect to the MariaDB server using your new password:
111mysql -u root -p