MariaDB(10.1)安装与配置

MariaDB安装与配置

这里选用的是mariadb 10.1 对应 mysql 5.7版本

一、配置安装源

1、编辑以下文件

vim /etc/yum.repos.d/Mariadb.repo

2、将以下内容放置在上面文件中

# MariaDB 10.1 CentOS repository list - created 2016-12-01 03:36 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

二、安装与启动

1、安装

可能下载的速度有点慢

yum install -y MariaDB-server MariaDB-client

2、启动服务

systemctl start mariadb
systemctl enable mariadb

3、配置（本人的机器拒绝这玩意，而是直接使用的默认的密码,直接跳转到第三步）

mysql_secure_installation

具体设置

#由于一开始安装MariaDB数据库后, root用户默认密码为空, 所以只需要按Enter键
Enter current password for root (enter for none):

#是否设置root用户的新密码
Set root password? [Y/n] y

#录入新密码
New password:

#确认新密码
Re-enter new password:

#是否删除匿名用户,生产环境建议删除
Remove anonymous users? [Y/n] y

#是否禁止root远程登录,根据自己的需求选择
Disallow root login remotely? [Y/n] n

#是否删除test数据库
Remove test database and access to it? [Y/n] y

#是否重新加载权限表
Reload privilege tables now? [Y/n] y

3、让防火墙放行3306端口

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

4、修改密码

本人机器安装完之后默认使用的是root密码

# 进入mysql表
use mysql
update user set password=password('newpassword') where user='root';
flush privileges;
# 不进入mysql表
SET password for 'root'@'localhost'=password('newpassword');  
# 或者使用 mysqladmin修改
msyqladmin -uroot -poldpassword password newpassword

5、修改远程登录权限

# 首先进入 mysql 
mysql -u root -p
# 输入你的密码
# 添加权限
Grant all on *.* to 'root'@'%' identified by 'root用户的密码' with grant option;
#重新载入
flush privileges;

mysql赋权操作：

GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘123456’ WITH GRANT OPTION;
flush privileges;

• GRANT：赋权命令
• ALL PRIVILEGES：当前用户的所有权限
• ON：介词
• *.*：当前用户对所有数据库和表的相应操作权限
• TO：介词
• ’root’@’%’：权限赋给root用户，所有ip都能连接
• IDENTIFIED BY ‘123456’：连接时输入密码，密码为123456
• WITH GRANT OPTION：允许级联赋权

---

三、修改相关配置

1、查看默认的字符集编码

show variables like 'character%';

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

2、修改配置文件

①、修改服务器字符集编码

这里没有采用使用语句进行修改，也希望对配置文件的操作也多多增加

vim /etc/my.cnf.d/server.cnf 

[mysqld]
default-storage-engine = innodb
innodb_file_per_table
max_connections = 4096
collation-server = utf8_general_ci
character-set-server = utf8

②、修改客户端字符集编码

 vim /etc/my.cnf.d/client.cnf 

[client]
default-character-set=utf8

③、重启 MariaDB

systemctl restart mariadb

④、重新登录并验证字符集编码

show variables like 'character%';

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

ok,修改成功

3、针对已经存在的库修改其字符集编码

参考文章：https://www.cnblogs.com/baolin2200/p/7285335.html

修改已存在的库字符集为utf8mb4 
# 修改库：    
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# 修改表:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 修改一列:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4、设置最大连接数

set global max_connections = 300;

5、优化

可参考： https://www.cnblogs.com/zhongjicainiao/p/8982960.html