如何启用对MariaDB服务器的远程访问？

编辑my.cnf文件，运行：

# vi /usr/local/etc/my.cnf

确保" skip-networking"行已注释(或删除行)，
并在" [mysqld]"部分添加以下行：

bind-address=服务器ip

重启服务器：

# service mysql-server restart

更新防火墙配置文件pf.conf：

## allows mysql client from 192.168.1.200 ##
pass in on $ext_if proto tcp from 192.168.1.200 to any port 3306  flags S/SA synproxy state

重启pf防火墙。

然后测试是否可以远程连接数据库

mysql -h 192.168.1.5 -u USER -p DB

如何设置局域网用户对现有数据库的访问权限？

允许192.168.1.200的用户bar访问数据库foo：

MariaDB [mysql]> update db set Host='192.168.1.200' where Db='foo';
MariaDB [mysql]> update user set Host='192.168.1.200' where user='bar';

如何在FreeBSD pf防火墙中打开端口

在pf.conf文件中添加以下规则：

pass in on $ext_if proto tcp from any to any port 3306

或只允许从192.168.1.10访问数据库：

pass in on $ext_if proto tcp from 192.168.1.10 to any port 3306

如何为MariaDB设置root用户密码？

安装MariaDB后，应该为root用户创建密码：

# mysqladmin -S /var/db/mysql/mysql.sock -u root password YOURSECUREPASSWORD

不过最好使用下面的命令进行MariaDB安全性设置：

# ln -s /var/db/mysql/mysql.sock /tmp/
# /usr/local/bin/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.
 
Set 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.
 
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.
 
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...
 
All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.
 
Thanks for using MariaDB!

如何创建MariaDB数据库和用户？

首先，以root用户身份登录：

mysql -u root -p mysql

创建一个名为foo的新mysql数据库。在mysql>提示符下执行以下命令：

MariaDB [mysql]>  CREATE DATABASE foo;

为名为foo的数据库创建一个名为user1的新用户，并密码为password1234：

MariaDB [mysql]>  GRANT ALL ON foo.* TO user1@localhost IDENTIFIED BY 'password1234';

如何使用user1帐户连接到MariaDB数据库foo？

用户user1可以使用以下shell命令连接到foo数据库：

$ mysql -u user1 -p foo

或者

$ mysql -u user1 -h your-mysql-server-host-name-here -p foo

如何连接到MariaDB服务器？

语法为：

mysql
mysql -u user -p
mysql -h db-hostname-here -u user-name-here -p

更新ports

确保ports已安装并且是最新的。

# portsnap fetch update && portupgrade -a

如何在系统启动时启动MariaDB？

执行sysrc命令以在系统启动时启用MaraiDB服务器服务：

# sysrc mysql_enable=YES
# sysrc mysql_pidfile=/var/db/mysql/mysql.pid
# sysrc mysql_optfile=/usr/local/etc/my.cnf

MariaDB服务器配置文件？

MariaDB包含了下面配置参考文件：

# ls -l /usr/local/share/mysql/my*.cnf

输出示例：

-rw-r--r--  1 root  wheel   4898 Nov 26 12:56 /usr/local/share/mysql/my-huge.cnf
-rw-r--r--  1 root  wheel  20418 Nov 26 12:56 /usr/local/share/mysql/my-innodb-heavy-4G.cnf
-rw-r--r--  1 root  wheel   4885 Nov 26 12:56 /usr/local/share/mysql/my-large.cnf
-rw-r--r--  1 root  wheel   4898 Nov 26 12:56 /usr/local/share/mysql/my-medium.cnf
-rw-r--r--  1 root  wheel   2824 Nov 26 12:56 /usr/local/share/mysql/my-small.cnf

我们可以将其中一个复制到/usr/local/etc/下：

# vi /usr/local/etc/my.cnf

FreeBSD安装MariaDB服务器

要安装ports，请执行并确保您在配置中选中要关闭的内容：

# cd /usr/ports/databases/mariadb103-server/
# make install clean

或者，使用pkg命令添加二进制软件包：

# pkg install databases/mariadb103-server

如何在FreeBSD 10/11上启动/停止/重启MariaDB？

启动MariaDB服务器

# service mysql-server start

停止MariaDB服务器

# service mysql-server stop

重启MariaDB服务器

# service mysql-server restart

查看MariaDB服务器状态

# service mysql-server status

或者通过脚本执行相应操作：

/usr/local/etc/rc.d/mysql-server start
/usr/local/etc/rc.d/mysql-server stop
/usr/local/etc/rc.d/mysql-server restart
/usr/local/etc/rc.d/mysql-server status

只安装MariaDB客户端

MariaDB客户端将自动安装。
现在，您应该检查以下选项：

[X] THREADSAFE  Build thread-safe client
[X] SSL         Activate SSL support (yassl)

如果要在另一台FreeBSD服务器MariaDB客户端，执行下面命令：

# cd /usr/ports/databases/mariadb103-client
# make install clean

或者

# pkg install databases/mariadb103-client

查找MariaDB服务器版本

运行pkg命令：

# pkg search mariadb

如何在基于FreeBSD Unix的系统上安装MariaDB数据库服务器？
如何在FreeBSD 10/11 Unix服务器上安装MariaDB数据库？