将mysql的data目录移走方法

如移动到”/home/mysql/data”,我的mysql是装在/usr/local/mysql下的

1. 将/usr/local/mysql/data移动到/home/mysql/data

mv /usr/local/mysql/data /home/mysql/data
2. 修改启动文件

vi /usr/local/mysql/support-files/mysql.server
修改如下行,指定datadir

datadir=/home/mysql/data
3. 修改配置文件

vi /etc/my.cnf
在[mysqld]下添加一行,指定datadir

datadir=/home/mysql/data
4. 重新启动mysql

/usr/local/mysql/support-files/mysql.server start

CentOS 7安装MariaDB 10详解以及相关配置

CentOS 7安装MariaDB 10详解以及相关配置

第一步:添加 MariaDB yum 仓库

首先在CentOS操作系统中/etc/yum.repos.d/目录下添加 MariaDB 的YUM配置文件MariaDB.repo文件。

vi /etc/yum.repos.d/MariaDB.repo

在该文件中添加以下内容保存:

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

第二步:安装 MariaDB

通过yum命令轻松安装 MariaDB。

yum install MariaDB-server MariaDB-client -y

MariaDB 安装完毕后,立即启动数据库服务守护进程。

systemctl start mariadb

设置 MariaDB 在操作系统重启后自动启动服务。

systemctl enable mariadb

查看 MariaDB 服务当前状态。

systemctl status mariadb

第三步:对 MariaDB 进行安全配置

通过以下命令进行安全配置,根据实际情况用Y/N回复以下问题:设置 MariaDB 的 root 账户密码,删除匿名用户,禁用 root 远程登录,删除测试数据库,重新加载权限表。

mysql_secure_installation

本人全都是选择了Y,然后按回车。

在配置完数据库的安全配置后,可以通过以下命令查看版本,确认 MariaDB已安装成功。

mysql –version

可以通过 MariaDB 命令行登录,然后对数据库进行sql查询操作。

mysql -u root -p

第四步:为 MariaDB 配置远程访问权限

在第三步中如果禁用 root 远程登录选择 Y 的话就不能在别的电脑通过navicat等工具连接到数据库,这时就需要给对应的 MariaDB 账户分配权限,允许使用该账户远程连接到MariaDB。可以输入以下命令查看账号信息:

select User, host from mysql.user;

root账户中的host项是localhost表示该账号只能进行本地登录,我们需要修改权限,输入命令:

GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;

修改权限。%表示针对所有IP,password表示将用这个密码登录root用户,如果想只让某个IP段的主机连接,可以修改为:

GRANT ALL PRIVILEGES ON *.* TO ‘root’@’192.168.71.%’ IDENTIFIED BY ‘my-new-password’ WITH GRANT OPTION;

最后别忘了:

FLUSH PRIVILEGES;

保存更改后,再看看用户账号信息:

这个时候发现相比之前多了一项,它的host项是%,这个时候说明配置成功了,我们可以用该账号进行远程访问了。

第五步:CentOS 7 开放防火墙端口

在第四步后如果还是不能远程连上数据库的话应该就是3306端口被防火墙拦截了,这时我们就需要关闭防火墙或者开放防火墙端口。

关闭防火墙:

systemctl stop firewalld.service #停止firewall

systemctl disable firewalld.service #禁止firewall开机启动

开放防火墙端口,开启后要重启防火墙:

firewall-cmd –zone=public –add-port=3306/tcp –permanent

firewall-cmd –reload

第六步:设置数据库字母大小写不敏感

vi /etc/my.cnf.d/server.cnf

在[mysqld]下加上】

lower_case_table_names=1

默认是等于0的,即大小写敏感。改成1就OK了。如果之前已经建了数据库要把之前建立的数据库删除,重建才生效。

第七步:设置MariaDB数据库默认编码

MariaDB的默认编码是latin1,插入中文会乱码,因此需要将编码改为utf8。

1.登录,使用以下命令查看当前使用的字符集,应该有好几个不是utf8格式。

SHOW VARIABLES LIKE ‘character%’;

2.修改的配置文件

vi /etc/my.cnf.d/client.cnf

在[client]字段里加入

default-character-set=utf8

vi /etc/my.cnf.d/server.cnf

在[mysqld]字段里加入

character-set-server=utf83.重启 MariaDB 配置生效。

systemctl restart mariadb

centos 7 mariadb安装

1、安装MariaDB

安装命令

yum -y install mariadb mariadb-server
安装完成MariaDB,首先启动MariaDB

systemctl start mariadb
设置开机启动

systemctl enable mariadb
接下来进行MariaDB的相关简单配置

mysql_secure_installation
首先是设置密码,会提示先输入密码

Enter current password for root (enter for none):<–初次运行直接回车 设置密码 Set root password? [Y/n] <– 是否设置root用户密码,输入y并回车或直接回车 New password: <– 设置root用户的密码 Re-enter new password: <– 再输入一次你设置的密码 其他配置 Remove anonymous users? [Y/n] <– 是否删除匿名用户,回车 Disallow root login remotely? [Y/n] <–是否禁止root远程登录,回车, Remove test database and access to it? [Y/n] <– 是否删除test数据库,回车 Reload privilege tables now? [Y/n] <– 是否重新加载权限表,回车 初始化MariaDB完成,接下来测试登录 mysql -uroot -ppassword 完成。 [root@localhost ~]# rpm -qa | grep mysql [root@localhost ~]# rpm -qa | grep mariadb mariadb-libs-5.5.56-2.el7.x86_64 [root@localhost ~]# find / -name mariadb-libs-5.5.56-2.el7.x86_64 find: ‘/run/user/1000/gvfs’: 权限不够 [root@localhost ~]# find . -name mariadb-libs-5.5.56-2.el7.x86_64 [root@localhost ~]# [root@localhost ~]# yum -y install mariadb mariadb-server 已加载插件:fastestmirror, langpacks Could not retrieve mirrorlist http://mirrorlist.centos.org/?release=7&arch=x86_64&repo=os&infra=stock error was 12: Timeout on http://mirrorlist.centos.org/?release=7&arch=x86_64&repo=os&infra=stock: (28, 'Operation too slow. Less than 1000 bytes/sec transferred the last 30 seconds') base | 3.6 kB 00:00:00 Could not retrieve mirrorlist http://mirrorlist.centos.org/?release=7&arch=x86_64&repo=extras&infra=stock error was 12: Timeout on http://mirrorlist.centos.org/?release=7&arch=x86_64&repo=extras&infra=stock: (28, 'Operation too slow. Less than 1000 bytes/sec transferred the last 30 seconds') extras | 3.4 kB 00:00:00 Could not retrieve mirrorlist http://mirrorlist.centos.org/?release=7&arch=x86_64&repo=updates&infra=stock error was 12: Timeout on http://mirrorlist.centos.org/?release=7&arch=x86_64&repo=updates&infra=stock: (28, 'Operation too slow. Less than 1000 bytes/sec transferred the last 30 seconds') updates | 3.4 kB 00:00:00 Loading mirror speeds from cached hostfile * base: mirrors.aliyun.com * extras: mirrors.aliyun.com * updates: mirrors.aliyun.com 正在解决依赖关系 --> 正在检查事务
—> 软件包 mariadb.x86_64.1.5.5.56-2.el7 将被 安装
—> 软件包 mariadb-server.x86_64.1.5.5.56-2.el7 将被 安装
–> 正在处理依赖关系 perl-DBI,它被软件包 1:mariadb-server-5.5.56-2.el7.x86_64 需要
–> 正在处理依赖关系 perl-DBD-MySQL,它被软件包 1:mariadb-server-5.5.56-2.el7.x86_64 需要
–> 正在处理依赖关系 perl(Data::Dumper),它被软件包 1:mariadb-server-5.5.56-2.el7.x86_64 需要
–> 正在处理依赖关系 perl(DBI),它被软件包 1:mariadb-server-5.5.56-2.el7.x86_64 需要
–> 正在检查事务
—> 软件包 perl-DBD-MySQL.x86_64.0.4.023-5.el7 将被 安装
—> 软件包 perl-DBI.x86_64.0.1.627-4.el7 将被 安装
–> 正在处理依赖关系 perl(RPC::PlServer) >= 0.2001,它被软件包 perl-DBI-1.627-4.el7.x86_64 需要
–> 正在处理依赖关系 perl(RPC::PlClient) >= 0.2000,它被软件包 perl-DBI-1.627-4.el7.x86_64 需要
—> 软件包 perl-Data-Dumper.x86_64.0.2.145-3.el7 将被 安装
–> 正在检查事务
—> 软件包 perl-PlRPC.noarch.0.0.2020-14.el7 将被 安装
–> 正在处理依赖关系 perl(Net::Daemon) >= 0.13,它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要
–> 正在处理依赖关系 perl(Net::Daemon::Test),它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要
–> 正在处理依赖关系 perl(Net::Daemon::Log),它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要
–> 正在处理依赖关系 perl(Compress::Zlib),它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要
–> 正在检查事务
—> 软件包 perl-IO-Compress.noarch.0.2.061-2.el7 将被 安装
–> 正在处理依赖关系 perl(Compress::Raw::Zlib) >= 2.061,它被软件包 perl-IO-Compress-2.061-2.el7.noarch 需要
–> 正在处理依赖关系 perl(Compress::Raw::Bzip2) >= 2.061,它被软件包 perl-IO-Compress-2.061-2.el7.noarch 需要
—> 软件包 perl-Net-Daemon.noarch.0.0.48-5.el7 将被 安装
–> 正在检查事务
—> 软件包 perl-Compress-Raw-Bzip2.x86_64.0.2.061-3.el7 将被 安装
—> 软件包 perl-Compress-Raw-Zlib.x86_64.1.2.061-4.el7 将被 安装
–> 解决依赖关系完成

依赖关系解决

========================================================================================
Package 架构 版本 源 大小
========================================================================================
正在安装:
mariadb x86_64 1:5.5.56-2.el7 base 8.7 M
mariadb-server x86_64 1:5.5.56-2.el7 base 11 M
为依赖而安装:
perl-Compress-Raw-Bzip2 x86_64 2.061-3.el7 base 32 k
perl-Compress-Raw-Zlib x86_64 1:2.061-4.el7 base 57 k
perl-DBD-MySQL x86_64 4.023-5.el7 base 140 k
perl-DBI x86_64 1.627-4.el7 base 802 k
perl-Data-Dumper x86_64 2.145-3.el7 base 47 k
perl-IO-Compress noarch 2.061-2.el7 base 260 k
perl-Net-Daemon noarch 0.48-5.el7 base 51 k
perl-PlRPC noarch 0.2020-14.el7 base 36 k

事务概要
========================================================================================
安装 2 软件包 (+8 依赖软件包)

总下载量:21 M
安装大小:110 M
Downloading packages:
警告:/var/cache/yum/x86_64/7/base/packages/mariadb-server-5.5.56-2.el7.x86_64.rpm: 头V3 RSA/SHA256 Signature, 密钥 ID f4a80eb5: NOKEY
mariadb-server-5.5.56-2.el7.x86_64.rpm 的公钥尚未安装
(1/10): mariadb-server-5.5.56-2.el7.x86_64.rpm | 11 MB 00:00:02
(2/10): mariadb-5.5.56-2.el7.x86_64.rpm | 8.7 MB 00:00:02
(3/10): perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm | 32 kB 00:00:00
(4/10): perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm | 57 kB 00:00:00
(5/10): perl-DBD-MySQL-4.023-5.el7.x86_64.rpm | 140 kB 00:00:00
(6/10): perl-Data-Dumper-2.145-3.el7.x86_64.rpm | 47 kB 00:00:00
(7/10): perl-IO-Compress-2.061-2.el7.noarch.rpm | 260 kB 00:00:00
(8/10): perl-Net-Daemon-0.48-5.el7.noarch.rpm | 51 kB 00:00:00
(9/10): perl-PlRPC-0.2020-14.el7.noarch.rpm | 36 kB 00:00:00
(10/10): perl-DBI-1.627-4.el7.x86_64.rpm | 802 kB 00:00:00
—————————————————————————————-
总计 8.1 MB/s | 21 MB 00:02
从 file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7 检索密钥
导入 GPG key 0xF4A80EB5:
用户ID : “CentOS-7 Key (CentOS 7 Official Signing Key)
指纹 : 6341 ab27 53d7 8a78 a7c2 7bb1 24c6 a8a7 f4a8 0eb5
软件包 : centos-release-7-4.1708.el7.centos.x86_64 (@anaconda)
来自 : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在安装 : perl-Data-Dumper-2.145-3.el7.x86_64 1/10
正在安装 : 1:mariadb-5.5.56-2.el7.x86_64 2/10
正在安装 : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 3/10
正在安装 : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 4/10
正在安装 : perl-IO-Compress-2.061-2.el7.noarch 5/10
正在安装 : perl-Net-Daemon-0.48-5.el7.noarch 6/10
正在安装 : perl-PlRPC-0.2020-14.el7.noarch 7/10
正在安装 : perl-DBI-1.627-4.el7.x86_64 8/10
正在安装 : perl-DBD-MySQL-4.023-5.el7.x86_64 9/10
正在安装 : 1:mariadb-server-5.5.56-2.el7.x86_64 10/10
验证中 : perl-DBI-1.627-4.el7.x86_64 1/10
验证中 : perl-Net-Daemon-0.48-5.el7.noarch 2/10
验证中 : perl-Data-Dumper-2.145-3.el7.x86_64 3/10
验证中 : perl-PlRPC-0.2020-14.el7.noarch 4/10
验证中 : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 5/10
验证中 : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 6/10
验证中 : 1:mariadb-server-5.5.56-2.el7.x86_64 7/10
验证中 : perl-IO-Compress-2.061-2.el7.noarch 8/10
验证中 : perl-DBD-MySQL-4.023-5.el7.x86_64 9/10
验证中 : 1:mariadb-5.5.56-2.el7.x86_64 10/10

已安装:
mariadb.x86_64 1:5.5.56-2.el7 mariadb-server.x86_64 1:5.5.56-2.el7

作为依赖被安装:
perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7
perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7
perl-DBD-MySQL.x86_64 0:4.023-5.el7
perl-DBI.x86_64 0:1.627-4.el7
perl-Data-Dumper.x86_64 0:2.145-3.el7
perl-IO-Compress.noarch 0:2.061-2.el7
perl-Net-Daemon.noarch 0:0.48-5.el7
perl-PlRPC.noarch 0:0.2020-14.el7

完毕!
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# systemctl enable maiadb
Failed to execute operation: No such file or directory
[root@localhost ~]# systemctl enable mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
[root@localhost ~]# 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.

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.

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]
… Success!

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]
– Dropping test database…
… Success!
– Removing privileges on test database…
… Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]
… 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@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>

2、配置MariaDB的字符集

文件/etc/my.cnf

vi /etc/my.cnf
在[mysqld]标签下添加

init_connect=’SET collation_connection = utf8_unicode_ci’
init_connect=’SET NAMES utf8′
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
文件/etc/my.cnf.d/client.cnf

vi /etc/my.cnf.d/client.cnf
在[client]中添加

default-character-set=utf8
文件/etc/my.cnf.d/mysql-clients.cnf

vi /etc/my.cnf.d/mysql-clients.cnf
在[mysql]中添加

default-character-set=utf8
全部配置完成,重启mariadb

systemctl restart mariadb
之后进入MariaDB查看字符集

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> show variables like “%character%”;show variables like “%collation%”;
+————————–+—————————-+
| 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.01 sec)

+———————-+——————-+
| Variable_name | Value |
+———————-+——————-+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+———————-+——————-+
3 rows in set (0.00 sec)