3. 配置数据库
参考:https://www.cnblogs.com/river2005/p/6813618.html
3.1 使用mysql_secure_installation命令进行配置设置密码,会提示先输入密码
mysql_secure_installation
如下:
Entercurrent password for root (enter for none):< #如果是初次运行直接回车
设置密码
Setroot password? [Y/n] < #是否设置root用户密码,输入y并回车或直接回车
New password: < #设置root用户的密码
Re-enter new password: < #再输入一次你设置的密码
其他配置
Removeanonymous users? [Y/n] < #是否删除匿名用户,回车
Disallowroot login remotely? [Y/n] < #是否禁止root远程登录,回车,
Removetest database and access to it? [Y/n] < #是否删除test数据库,回车
Reloadprivilege tables now? [Y/n] < #是否重新加载权限表,回车
#初始化MariaDB完成,测试登录
mysql-uroot -ppassword
完成。
3.2 配置MariaDB的字符集
查看/etc/my.cnf文件内容,其中包含一句!includedir /etc/my.cnf.d 说明在该配置文件中引入/etc/my.cnf.d 目录下的配置文件。
1. 使用vi server.cnf命令编辑server.cnf文件,在[mysqld]标签下添加
init_connect='SETcollation_connection = utf8_unicode_ci'
init_connect='SETNAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
如果/etc/my.cnf.d 目录下无server.cnf文件,则直接在/etc/my.cnf文件的[mysqld]标签下添加以上内容。
2. 文件/etc/my.cnf.d/client.cnf
vi /etc/my.cnf.d/client.cnf
在[client]中添加
default-character-set=utf8
3. 文件/etc/my.cnf.d/mysql-clients.cnf
vi /etc/my.cnf.d/mysql-clients.cnf
在[mysql]中添加
default-character-set=utf8
4. 支持大小写配置
https://mariadb.com/kb/en/library/identifier-case-sensitivity/
sudo vi /etc/my.cnf #建议使用vim命令编辑 会有高亮提示很方便 如果提示没有vim命令 则 yum install vim 即可
在[mysqld]下面添加:
lower_case_table_names= 1
重启mariadb服务即可。(注意,修改一定要在创建数据库之前,对修改之前创建的数据库无效)
5. 全部配置完成,重启mariadb
systemctl stop mariadb #关闭maridb数据库
systemctl start mariadb #开启maridb数据库
6. 之后进入MariaDB查看字符集
mysql-uroot -ppassword
mysql>show variables like "%character%";show variables like"%collation%";
显示为
4. 添加用户,设置权限
创建用户命令
MariaDB [(none)]>createuser username@localhost identified by 'password';
直接创建用户并授权的命令
mysql>grantall on *.* to username@localhost indentified by 'password';
授予外网登陆权限
mysql>grantall privileges on *.* to username@'%' identified by 'password';
授予权限并且可以授权
mysql>grantall privileges on *.* to username@'hostname' identified by 'password' withgrant option;
mysql>flush privileges;
5. 端口开放
//查看运行状态
firewall-cmd --state #正常情况会打印running
//开发端口
firewall-cmd--zone=public --add-port=3306/tcp --permanent
//重载生效刚才的端口设置
firewall-cmd --reload
以下是java中的连接测试:需要下载 mariadb-java-client.jar 包
/**
* Copyright (C), 2015-2018, XXX有限公司
* FileName: MaridbTest
* Author: yuanyuana
* Date: 2018/9/7 17:08
* Description: test
* History:
* <author> <time> <version> <desc>
* 作者姓名 修改时间 版本号 描述
*/
package com.fh;
import org.springframework.expression.spel.ast.TypeCode;
import javax.servlet.ServletException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
/**
* 〈一句话功能简述〉<br>
* 〈test〉
*
* @author yuanyuana
* @create 2018/9/7
* @since 1.0.0
*/
public class MaridbTest {// The JDBC Connector Class.
private static final String dbClassName = "org.mariadb.jdbc.Driver"; //很重要
// Connection string. emotherearth is the database the program
// is connecting to. You can include user and password after this
// by adding (say) ?user=paulr&password=paulr. Not recommended!
private static final String CONNECTION =
"jdbc:mariadb://服务器地址:端口/数据库";//很重要
public static void main(String[] args) throws
ClassNotFoundException, SQLException {
// Class.forName(xxx) loads the jdbc classes and
// creates a drivermanager class factory
Class.forName(dbClassName);
// Properties for user and password. Here the user and password are both 'paulr'
Properties p = new Properties();
p.put("user", "root");
p.put("password", "root");
// Now try to connect
Connection c = DriverManager.getConnection(CONNECTION, p);
String s = "select * from weixin_imgmsg";
PreparedStatement preparedStatement = c.prepareStatement(s);
ResultSet resultSet = preparedStatement.executeQuery();
List<String> list = new ArrayList<>();
while (resultSet.next()){
String s1 = resultSet.getString("TITLE1");
list.add(s1);
}
for (String ss:list){
System.out.print(ss);
}
c.close();
}
}
运行结果如下: