CentOS7重装MariaDB

本想装MySQL的,但CentOS7 yum已经移除了MySQL的安装包,安装起来很麻烦,而MariaDB就是换个皮肤的MySQL,就它了!

docker 安装mysql5.7

docker run -d -p 3306:3306 --name mysql --restart=always -v /data/mysql/conf:/etc/mysql/conf.d -v /data/mysql/logs:/logs -v /data/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=test --lower_case_table_names=1 mysql:5.7

--lower_case_table_names=1 表名大小写不敏感

卸载

将之前安装的mariadb卸载掉

rpm -qa|grep mariadb

#[root@mail bin]# rpm -qa|grep mariadb
#mariadb-libs-5.5.60-1.el7_5.x86_64
#mariadb-server-5.5.60-1.el7_5.x86_64
#mariadb-5.5.60-1.el7_5.x86_64

yum remove mariadb

#[root@mail bin]# yum remove mariadb
#Removed:
#  mariadb.x86_64 1:5.5.60-1.el7_5
#
#Dependency Removed:
#  mariadb-server.x86_64 1:5.5.60-1.el7_5

#检查发现还有一个包没有被清除
rpm -qa|grep mariadb

#[root@mail bin]# rpm -qa|grep mariadb
#mariadb-libs-5.5.60-1.el7_5.x86_64

yum remove mariadb-libs-5.5.60-1.el7_5.x86_64

#[root@mail bin]# yum remove mariadb-libs-5.5.60-1.el7_5.x86_64
#Removed:
#  mariadb-libs.x86_64 1:5.5.60-1.el7_5

删除遗留目录

#清除残留文件

rm -rf /etc/my.cnf
rm -rf /var/lib/mysql/

#[root@mail bin]# rm -rf /etc/my.cnf
#[root@mail bin]# rm -rf /var/lib/mysql/

重新安装

#安装
yum install -y mariadb mariadb-server

#启动服务
systemctl start mariadb

#设置开机启动服务
systemctl enable mariadb

#[root@mail bin]# systemctl start mariadb
#[root@mail bin]# systemctl enable mariadb
#Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

设置数据库配置

#设置数据库配置
mysql_secure_installation

#设置root密码 安装首次调用按确认键跳过
#Enter current password for root (enter for none):

#是否设置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!

#禁止远程链接
#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.

#删除test数据库
#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] 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!

配置root访问权限

# 需要启动数据库
systemctl start mariadb

#用前面设置的root用户密码登陆
mysql -u root -p
#Enter password:

#MariaDB [(none)]> use mysql

#查看用户 root并没有远程链接的权限 需要设置
#MariaDB [mysql]> select user,host from user;
#+------+--------------------+
#| user | host               |
#+------+--------------------+
#| root | localhost          |
#| root | 127.0.0.1          |
#| root | ::1                |
#+------+--------------------+
#3 rows in set (0.00 sec)

#设置root远程操作权限
#XXXXX 是root密码

#MariaDB [mysql]> grant all privileges on *.* to root@"%" identified by "XXXXX";
#Query OK, 0 rows affected (0.00 sec)

#检查发现多了一条记录 host 为% 说明root可以在远程登陆操作了

#MariaDB [mysql]> select user,host from user;
#+------+--------------------+
#| user | host               |
#+------+--------------------+
#| root | localhost          |
#| root | 127.0.0.1          |
#| root | ::1                |
#| root | %                  |
#+------+--------------------+
#4 rows in set (0.00 sec)

最后使用数据库客户端链接登陆root就可以操作了

linux下mysql表名大小写敏感的问题

lower_case_table_names：这个选项不仅仅适用于表名的大小写敏感，同样适用于数据库名和表别名。
该变量取值范围有三个，分别是0、1、2.

设置成0：表名按你写的SQL大小写存储，大写就大写小写就小写，比较时大小写敏感。
设置成1：表名转小写后存储到硬盘，比较时大小写不敏感。
设置成2：表名按你写的SQL大小写存储，大写就大写小写就小写，比较时统一转小写比较。 Linux 系统无效 设置2 会变成 0

systemctl list-unit-files --type=service

show global variables like '%lower_case%';

vim /ect/my.cnf
在[mysqld]下添加如下：
[mysqld] 
lower_case_table_names=1

重启mysql
service mysqld.service restart 

设置编码