ubuntu18.04安装、使用、远程访问mariadb

柴英光

2023-12-01

1、安装mariadb服务器

~$sudo apt install mariadb-server

2、链接、测试

 ~$sudo mysql -u root -p

测试结果：

~/Desktop$ sudo mysql -u root -p
[sudo] password for kyun: 
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 41
Server version: 10.1.43-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]>

3、设置数据库服务器的安全策略

 ~$sudo mysql_secure_installation

配置过程：

~/Desktop$ sudo mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.
# 修改root密码
Change the root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
# 删除匿名用户
Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
# 禁止root远程登录， 不要开放root远程登录权限
Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
# 删除test数据库和它的权限
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...
# 完成上述步骤后，我们的MariaDB的安装就是安全的了
All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

4、启动&重启关闭

查看状态

sudo systemctl status mysql

如：

~/Desktop$ sudo systemctl status mysql
[sudo] password for kyun: 
● mariadb.service - MariaDB 10.1.43 database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: 
   Active: active (running) since Sat 2019-11-30 00:12:38 CST; 16h ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
 Main PID: 2401 (mysqld)
   Status: "Taking your SQL requests now..."
    Tasks: 28 (limit: 4915)
   CGroup: /system.slice/mariadb.service
           └─2401 /usr/sbin/mysqld

启动

sudo systemctl start mysql

重启

sudo systemctl restart mysql

关闭

 sudo systemctl stop mysql

注：在mariadb数据库中，mysql的名称也可以用mariadb名称来代替。

5、配置

~$sudo mysql -u root -p

创建远程登录用户
格式：create user ‘用户名’@’%’ identified by ‘密码’;

MariaDB [(none)]> create user 'kyunwong'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

授权（如果权限不足）

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'kyunwong'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

查看监听

~$ netstat -an | grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN

如果显示监听端口是127.0.0.1:3306，那么其他计算机将无法连接到数据库。

修改配置文件，使其能够通过远程来访问

vim /etc/mysql/mariadb.conf.d/50-server.cnf

注释掉这一行bind-address = 127.0.0.1，如下图所示：

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1

50-server.cnf这个文件，配置了数据库服务端的端口、数据库文件目录、临时目录等重要信息。配置文件修改后，都必须重启服务才能生效。

重启mariadb，再查看端口监听状态

sudo systemctl restart mysql
kyun@kyun-HP-348-G3:/etc/mysql/mariadb.conf.d$ netstat -an | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN

现在显示监听端口是:::3306，那么其他计算机就可以连接到数据库了。

远程访问

直接登录
账号kyunwong，-p表示密码，但后面不用跟上密码，在提示中输入密码即可。

~$ sudo mysql -h localhost -u kyunwong -p

远程登录
请继续阅读，在后面的部分会给出。

异常问题

Access denied for user ‘kyunwong’@‘localhost’

解决方法：

停止服务

~$ sudo systemctl stop mariadb

进入安全模式，并设置为后台进程

~$ sudo mysqld_safe --skip-grant-tables &

登陆数据库

~$ mysql -u root

修改mysql数据库的user表

# 查询用户
MariaDB [(none)]> select Host,User,plugin from mysql.user where user='root';
# 重置加密模式
MariaDB [(none)]> update mysql.user set plugin='mysql_native_password';
# 重置密码
MariaDB [(none)]> update mysql.user set password=PASSWORD("newpassword") where user='root';
# 刷新权限信息
MariaDB [(none)]> flush privileges;
# 安装unix_socket
MariaDB [(none)]> install plugin unix_socket soname 'auth_socket';
MariaDB [(none)]> exit

杀掉所有数据库相关的进程，重新启动

 ~$ sudo kill $(pgrep mysql)

重新启动服务
数据库服务的名称可以使用mariadb或mysql。

~$ sudo service mariadb start
或
~$ sudo service mysql start
或
~$ sudo systemctl start mysql
或
~$ sudo systemctl start mariadb

再重新登录

~$ sudo mysql -u kyunwong -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.1.43-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

使用数据库

显示数据库

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.08 sec)

创建数据库

MariaDB [(none)]> create database wong_web_db
    -> ;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| wong_web_db        |
+--------------------+
4 rows in set (0.00 sec)

进入创建好的数据库，并查看现有的表，刚创建的数据库是没有任何表的

MariaDB [(none)]> use wong_web_db;
Database changed
MariaDB [wong_web_db]> show tables;
Empty set (0.00 sec)

创建表

MariaDB [wong_web_db]> create table user(id int(10) auto_increment primary key,name varchar(32) not null);
Query OK, 0 rows affected (0.32 sec)


MariaDB [wong_web_db]> show tables;
+-----------------------+
| Tables_in_wong_web_db |
+-----------------------+
| user                  |
+-----------------------+
1 row in set (0.00 sec)

向表中插入一些数据

MariaDB [wong_web_db]> insert into user(name) values("fish");
Query OK, 1 row affected (0.09 sec)

查询

MariaDB [wong_web_db]> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | fish |
+----+------+
1 row in set (0.00 sec)

更新

MariaDB [wong_web_db]> update user set name = "BigBigWorld";
Query OK, 1 row affected (0.52 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [wong_web_db]> select * from user;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | BigBigWorld |
+----+-------------+
1 row in set (0.00 sec)

删除

MariaDB [wong_web_db]> delete from user where name="BigBigWorld";
Query OK, 1 row affected (0.10 sec)

MariaDB [wong_web_db]> select * from user;
Empty set (0.00 sec)

在远程计算机上连接此数据库

方法：

mysql -u 用户名 -p  -h IP地址 -P 端口号 -D 数据库名字

示例：
（1）远程计算机上，安装访问mariadb的客户端

apt-get install mariadb-client

如：

root@f11e41905714:/# apt-get install mariadb-client

（2）使用客户端连接数据库

root@f11e41905714:/# mysql -u kyunwong -p  -h 172.17.0.1 -P 3306 -D wong_web_db
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.1.43-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [wong_web_db]> show tables;  
+-----------------------+
| Tables_in_wong_web_db |
+-----------------------+
| user                  |
+-----------------------+
1 row in set (0.001 sec)

MariaDB [wong_web_db]> select * from user;
Empty set (0.001 sec)

MariaDB [wong_web_db]> insert into user(name) values("hello world");
Query OK, 1 row affected (0.309 sec)

MariaDB [wong_web_db]> select * from user;
+----+-------------+
| id | name        |
+----+-------------+
|  2 | hello world |
+----+-------------+
1 row in set (0.001 sec)

MariaDB [wong_web_db]>

以上就是ubuntu18.04安装、使用、远程访问mariadb的所有内容。

谢谢阅读！