linux之MySQL环境搭建，同时配置远程登录

MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。

此外,可以使用 MariaDB 代替，MariaDB 数据库管理系统是 MySQL 的一个分支，主要由开源社区在维护，采用 GPL 授权许可。开发这个分支的原因之一是：甲骨文公司收购了 MySQL 后，有将 MySQL 闭源的潜在风险，因此社区采用分支的方式来避开这个风险。
MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。

windows 环境可以用HeidiSQL进行测试。 https://downloads.mariadb.org/mariadb/
数据库操作，详见https://www.runoob.com/mysql/mysql-tutorial.html

• 安装nariadb

sudo apt-get install mariadb-server

如果安装失败，可能因为之前安装过，但没有卸载干净导致，重新卸载完全再重新安装。

• 完全清除mysql

首先删除mysql:

sudo apt-get remove mysql-*
sudo apt-get remove mariadb-*

然后清理残留的数据

dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P

它会跳出一个对话框，你选择yes就好了

• 安装成功后，配置文件如下

eric@eric-PC:/etc/mysql$ tree
.
├── conf.d
│   ├── mysql.cnf
│   └── mysqldump.cnf                                                                                                                                                                                   
├── debian.cnf                                                                                                                                                                                          
├── debian-start                                                                                                                                                                                        
├── mariadb.cnf                                                                                                                                                                                         
├── mariadb.conf.d                                                                                                                                                                                      
│   ├── 50-client.cnf
│   ├── 50-mysql-clients.cnf
│   ├── 50-mysqld_safe.cnf
│   └── 50-server.cnf
├── my.cnf -> /etc/alternatives/my.cnf
└── my.cnf.fallback

2 directories, 11 files

• 启动，停止，重启服务

service mysql start
service mysql stop
service mysql restart

• 首次登录，用命令修改密码

 mysqladmin -u root password "1234";

• 或者sudo模式无密码登录，再修改密码

首次sudo 无密码登录

eric@eric-PC:/$ sudo mysql
请输入密码
[sudo] eric 的密码：
验证成功
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 41
Server version: 10.3.23-MariaDB-0+deb10u1 Debian 20

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)]> 

查询用户

MariaDB [(none)]> 
MariaDB [(none)]> select User, host from mysql.user;
+------+-----------+
| User | host      |
+------+-----------+
| root | localhost |
+------+-----------+
1 row in set (0.000 sec)
MariaDB [(none)]> 

对root用户，设置密码1234，并立刷新后退出

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '1234' WITH GRANT OPTION;
Query OK, 0 rows affected (0.001 sec)

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

测试非sudo 密码登录

eric@eric-PC:/$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 44
Server version: 10.3.23-MariaDB-0+deb10u1 Debian 20

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)]>

登录成功!

• 同时也可以运行数据库初始化脚本，进行设置

eric@eric-PC:/$ 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.
#键入数据库root用户密码（非linux用户密码），首次无密码直接回车
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] n
 ... skipping.

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] n
 ... skipping.

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] n
 ... skipping.

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!

• 远程登录设置
  检查3306端口绑定，如果出现以下提示，127.0.0.1:3306，则说明只绑定到了本地端口

eric@eric-PC:/$ netstat -an | grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN     
eric@eric-PC:/$ 

修改文件/etc/mysql/mariadb.conf.d/vim 50-server.cnf

eric@eric-PC:/etc/mysql/mariadb.conf.d$ sudo vim 50-server.cnf

屏蔽bind-address = 127.0.0.1

#bind-address            = 127.0.0.1

重启服务，再查看3306端口绑定。

eric@eric-PC:/$ service mysql restart
eric@eric-PC:/$ 
eric@eric-PC:/$ netstat -an | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN     
eric@eric-PC:/$ 

查询用户登录权限，root | localhost，说明root用户，只能本地登录

MariaDB [(none)]> select User, host from mysql.user;
+------+-----------+
| User | host      |
+------+-----------+
| root | localhost |
+------+-----------+
1 row in set (0.001 sec)

修改root用户远程登录权限，‘root’@‘192.168.16.%‘表示只允许192.168.16.xx地址域访问，‘root’@’%’，则允许所有ip域访问， 同时访问密码设置为1234

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

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

MariaDB [(none)]>

远程登录测试

eric@eric-PC:/$ mysql -h 192.168.16.107 -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.3.23-MariaDB-0+deb10u1 Debian 20

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)]>

登录成功

• windows环境远程登录
  

---