ubuntu16.04下安装mariadb

灰太狼 2022-08-23 09:55 282阅读 0赞

1 安装

# apt-get install mariadb-server python-pymysql

安装的时候会要求输入 **root**密码。

有时需要更新本地的仓库:

> sudo apt-get update

2 从命令行中进入MariaDB:

mysql -uroot -p

3 从命令行连接到MariaDB

root@zhai:~# mysql -uroot -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 42
    Server version: 10.0.25-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04
    
    Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> Ctrl-C -- exit!
    Aborted

4 查看数据库是否安装成功

能查看到表说明安装成功。

5 MariaDB 服务启动与停止

$ sudo /etc/init.d/mysql stop  
$ sudo /etc/init.d/mysql start

重启：

# service mysql restart

6 MySQL 设置默认字符集

打开`/etc/mysql/my.cnf`文件，为\[mysqld\]添加如下设置：

[mysqld]
    collation-server = utf8_unicode_ci
    init-connect='SET NAMES utf8'
    character-set-server = utf8

然后重启 MySQL。

7 加强MariaDB安全设置

MariaDB安装成功后，执行指令`mysql_secure_installation`加强MariaDB的安全设置：

root@zhai:~# 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'.
    
    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'. Thisensures that someone cannot guess 
    at the root password from the network.
    //是否禁止 root 账户远程登录？
    Disallow root login remotely? [Y/n] y ... Success!By default, MariaDB comes 
    with a database named 'test' that anyone canaccess. This is also intended 
    only for testing, and should be removedbefore moving into a production environment.
    //是否删除 MariaDB 默认创建的 test 数据库，并删除所有对 test 数据库的权限设置？
    Remove test database and access to it? [Y/n] y - Dropping test database...
    ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't 
    exist ... Failed! Not critical, keep moving... - Removing privileges on test 
    database... ... Success!Reloading the privilege tables will ensure that all 
    changes made so farwill 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 MariaDBinstallation should now be secure.
    Thanks for using MariaDB!

7 设置MariaDB允许远程访问

7.1 如果Ubuntu有设置防火墙或者iptables规则的话,请自行打开

7.2 3306端口是不是没有打开？

使用nestat命令查看3306端口状态：

~\# netstat -an | grep 3306

tcp0  0 127.0.0.1:3306  0.0.0.0:\*   LISTEN

从结果可以看出3306端口只是在IP 127.0.0.1上监听，所以拒绝了其他IP的访问。

解决方法：修改/etc/mysql/my.cnf文件。打开文件，找到下面内容：

\# 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

把上面这一行注释掉或者把127.0.0.1换成合适的IP，建议注释掉。

重新启动后，重新使用netstat检测：

~\# netstat -an | grep 3306  
tcp0  0 0.0.0.0:33060.0.0.0:\*   LISTEN

7.3 现在使用下面命令测试：

~\# mysql -h 192.168.0.101 -u root -p  
Enter password:  
ERROR 1130 (00000): Host 'Ubuntu-Fvlo.Server' is not allowed to connect to this MySQL server

结果出乎意料，还是不行。

解决方法：原来还需要把用户权限分配各远程用户。

登录到mysql服务器，使用grant命令分配权限

mysql> grant all on \*.\* to 你的用户名如root@'%' identified by '你的密码';

完成后使用mysql命令连接，提示成功，为了确保正确可以再远程登陆测试一下。

参考：

fedora 22 MariaDB：[http://www.linuxdiyf.com/linux/15398.html][http_www.linuxdiyf.com_linux_15398.html]

在Linux中怎样将MySQL迁移到MariaDB上：[http://www.linuxdiyf.com/linux/14127.html][http_www.linuxdiyf.com_linux_14127.html]

15个有用的MySQL/MariaDB性能调整和优化技巧：[http://www.linuxdiyf.com/linux/12780.html][http_www.linuxdiyf.com_linux_12780.html]

Linux有问必答：如何检查MariaDB服务端版本：[http://www.linuxdiyf.com/linux/13554.html][http_www.linuxdiyf.com_linux_13554.html]

ubuntu15.04安装mariadb10.0.19：[http://www.linuxdiyf.com/linux/12325.htm][http_www.linuxdiyf.com_linux_12325.htm]

[http_www.linuxdiyf.com_linux_15398.html]: http://www.linuxdiyf.com/linux/15398.html
[http_www.linuxdiyf.com_linux_14127.html]: http://www.linuxdiyf.com/linux/14127.html
[http_www.linuxdiyf.com_linux_12780.html]: http://www.linuxdiyf.com/linux/12780.html
[http_www.linuxdiyf.com_linux_13554.html]: http://www.linuxdiyf.com/linux/13554.html
[http_www.linuxdiyf.com_linux_12325.htm]: http://www.linuxdiyf.com/linux/12325.html