mysql搭建

一时失言乱红尘 2023-02-14 05:44 74阅读 0赞

# Mariadb 介绍 #

MySQL之父Widenius先生离开了Sun之后，觉得依靠Sun/Oracle来发展MySQL，实在很不靠谱，于是决定另开分支，这个分支的名字叫做MariaDB。主要由开源社区在维护，采用GPL授权许可。  
MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。  
MariaDB跟MySQL在绝大多数方面是兼容的，对于开发者来说，几乎感觉不到任何不同。

# 基础环境 #

*  CentOS 7，详见[《IT基础设施：CentOS安装》][IT_CentOS]

# 安装过程 #

> 使用包管理器进行安装，过程非常简单，主要是在进行初始化，和远程授权访问的地方需要注意一下。

## 安装并启动服务 ##

直接使用yum安装，这样安装得到的是5.5.60版本

yum -y install mariadb mariadb-server
    systemctl start mariadb
    systemctl enable mariadb

## 防火墙放开端口 ##

> 如果不需要外部管理，可以忽略此步，因为对外开放数据库连接将增加主机的风险性。

firewall-cmd --zone=public --add-port=3306/tcp --permanent
    firewall-cmd --reload

## 初始化数据库 ##

输入`mysql_secure_installation`开始进行数据库初始化

[root@dotnet203 /]# 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.

第一步：需要输入当前的数据库root密码，如果是新安装，则是空，直接回车。

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

第二步：设置root密码

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!

第四步：允许远程登录，如果只是本机使用，不需要外部登录，可以输入n拒绝

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!

# 管理测试 #

## 本地连接 ##

输入`mysql -u root -p`，再输入刚才设置的密码，我们就进入了mariadb控制台，控制台上显示了当前的服务器版本为5.5.60，而提示符标识也显示为`MariaDB[(none)]`，有别于`MySql`

[root@dotnet203 /]# mysql -u root -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 10
    Server version: 5.5.60-MariaDB MariaDB Server
    
    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可以使用[Oracle Mysql Workbench][]进行管理，我们配置一下。

![format_png][]

image.png

点击`Test Connection`测试是否可以连上

![format_png 1][]

无法连接

回到控制台，进入`mysql`数据库查看用户配置

首先切换当前使用的数据库为`mysql`，这是一个系统数据库

MariaDB [(none)]> use mysql;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    MariaDB [mysql]>

查询`user`表中的`root`用户配置

如上面显示，发现当前root帐号是只允许本地连接的（127.0.0.1/localhost/::1均代表本机），我们需要增加一个远程连接，允许`192.168.0.0`网段的连接，密码为root。

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

![format_png 2][]

连接成功

# 数据导入导出 #

通过`mysqldump` ，我们可以很方便地进行数据的导出和导入，但由于是生成SQL方式的导出导入，不适合大数据量的操作。

导出

mysqldump -u root -prootabcd$@32 webdb>/home/webdb_20181201.sql

导入

mysql -u root -prootabcd$@32 webdb</home/webdb_20181201.sql

# PS #

## Mysql 8.0以后的用户授权 ##

CREATE USER `root`@`%` IDENTIFIED BY '123456Ba^';
    GRANT ALL ON *.* TO `root`@`%` WITH GRANT OPTION;

[IT_CentOS]: https://www.jianshu.com/p/eacce21417f5
[Oracle Mysql Workbench]: https://links.jianshu.com/go?to=https%3A%2F%2Fdev.mysql.com%2Fdownloads%2Fworkbench%2F
[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy85NTUxMTcxLTlkNTRlMzQ5MzU5MDkzMDQucG5nP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXB8aW1hZ2VWaWV3Mi8yL3cvNzg0L2Zvcm1hdC93ZWJw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy85NTUxMTcxLTNlZTIwN2NkNzgxZTljYzYucG5nP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXB8aW1hZ2VWaWV3Mi8yL3cvMzU5L2Zvcm1hdC93ZWJw?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy85NTUxMTcxLTY5OTEwYjQzNzkwNzdhODAucG5nP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXB8aW1hZ2VWaWV3Mi8yL3cvODAwL2Zvcm1hdC93ZWJw?x-oss-process=image/format,png