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、针对已经存在的库修改其字符集编码
修改已存在的库字符集为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、优化
ZGC说：有什么能比不给牛吃草，还挤牛奶更快乐的事情呢？
