Linux下安装MariaDB官方文档:https://mariadb.com/kb/en/library/yum/ 

5.7版本的官方介绍:https://www.w3cschool.cn/mysql/t9mdhf21.html

一、介绍
CentOS 6 或早期的版本中提供的是 MySQL 的服务器/客户端安装包,但 CentOS 7 已使用了 MariaDB 替代了默认的 MySQL。MariaDB数据库管理系统是MySQL的一个分支,主要由开源社区在维护,采用GPL授权许可 MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品。

我们为了需要,还是要在系统中安装MySQL,而且MySQL安装完成之后可以直接覆盖掉MariaDB。

查看MariaDB: 

rpm -qa | grep mariadb
注:这里我们不用卸载,等会覆盖安装即可。 

二、安装
1 . 下载并安装MySQL官方的 Yum Repository https://dev.mysql.com/downloads/repo/yum/

[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

注:上面下载的版本为 5.7.25

  1. 直接用yum安装

[root@localhost ~]# yum -y install mysql57-community-release-el7-10.noarch.rpm

  1. 安装MySQL服务器

[root@localhost ~]# yum -y install mysql-community-server

 安装完成后就会覆盖掉之前的mariadb

三、对mysql 进行设置 
1. 启动MySQL

[root@localhost ~]#  service mysqld start

 2. 查看 MySQL状态

[root@localhost ~]# service mysqld status

 

  1. 在日志文件中找到MySQLroot用户的密码

[root@localhost ~]# grep "password" /var/log/mysqld.log

  1. 登录

[root@localhost ~]# mysql -u root -puykmo,U1+=Jw

  1. 修改密码

新密码设置的时候如果设置的过于简单会报错,你要对数据库进行操作必须重新设置密码才可以。

原因是因为MySQL有密码设置的规范,具体是与validate_password_policy的值有关:

设置密码可以设置的很简单:

mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=1;

 设置密码:

mysql> SET PASSWORD=PASSWORD('修改为你的密码');

  1. 卸载掉Yum Repository

因为安装了Yum Repository,以后每次yum操作都会自动更新,需要把这个卸载掉。

退出mysql:

mysql> quit;

卸载: 

[root@localhost ~]# yum -y remove mysql57-community-release-el7-10.noarch

三、设置mysql 远程访问 
1.显示数据库

mysql>show databases;

2.使用mysql数据库

mysql>use mysql;

4.展示mysql数据库中的所有表

mysql>show tables;

5.展示user表的结构

mysql>desc user;

6.查询user表

mysql>select User, Host from user;

注:host 表中都是只允许本机登录,我们需要修改任何机器都可以登录。

7.修改user表,把Host表内容修改为%

mysql>update user set host='%' where host='localhost';

 

8.删除root用户的其他host 

mysql>delete from user where not (User='root' and Host='%');

9.查看是否成功 

mysql>select User, Host from user;

 

 注:这里的删除语句的写法根据实际的情况,只留下我们修改的这一条记录,如果把所有的记录都删除了需要通过跳过权限检查重新设置密码等。 

10.刷新

mysql>flush privileges;

四、配置
[root@iZwz91s3dutue9omnmpihwZ mysql]# cat /etc/my.cnf

For advice on how to change settings please see

http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]

Remove leading # and set to the amount of RAM for the most important data

cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

innodb_buffer_pool_size = 128M

Remove leading # to turn on a very important data integrity option: logging

changes to the binary log between backups.

log_bin

Remove leading # to set options mainly useful for reporting servers.

The server defaults are faster for transactions and fast SELECTs.

Adjust sizes as needed, experiment to find the optimal values.

join_buffer_size = 128M

sort_buffer_size = 2M

read_rnd_buffer_size = 2M

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 增加如下信息,创建数据库的时候就不出现乱码了

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
 

 直接替换

[client]
port=3306
socket=/var/lib/mysql/mysql.sock

1、添加客户端字符集

default-character-set=utf8

[mysqld]
port=3306

2、增加

character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci

socket=/var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512

排序缓冲区的大小

sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 8
log-bin=mysql-bin
server-id = 1

datadir=/var/lib/mysql
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

3、增加

default-character-set=utf8

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
 5.1.73 有一个配置模板 /usr/share/mysql/my-huge.cnf ,但是在  5.7.25 我没有找该模板。

Example MySQL config file for very large systems.

This is for a large system with memory of 1G-2G where the system runs mainly

MySQL.

MySQL programs look for option files in a set of

locations which depend on the deployment platform.

You can copy this option file to one of those

locations. For information about these locations, see:

http://dev.mysql.com/doc/mysql/en/option-files.html

In this file, you can use all long options that a program supports.

If you want to know which options a program supports, run the program

with the "--help" option.

The following options will be passed to all MySQL clients

[client]

password = your_password

port = 3306
socket = /var/lib/mysql/mysql.sock

Here follows entries for some specific programs

The MySQL server

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M

Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8

Don't listen on a TCP/IP port at all. This can be a security enhancement,

if all processes that need to connect to mysqld run on the same host.

All interaction with mysqld must be made via Unix sockets or named pipes.

Note that using this option without enabling named pipes on Windows

(via the "enable-named-pipe" option) will render mysqld useless!

skip-networking

Replication Master Server (default)

binary logging is required for replication

log-bin=mysql-bin

required unique id between 1 and 2^32 - 1

defaults to 1 if master-host is not set

but will not function as a master if omitted

server-id = 1

Replication Slave (comment out master section to use this)

To configure this host as a replication slave, you can choose between

two methods :

1) Use the CHANGE MASTER TO command (fully described in our manual) -

the syntax is:

CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,

MASTER_USER=, MASTER_PASSWORD= ;

where you replace , , by quoted strings and

by the master's port number (3306 by default).

Example:

CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,

MASTER_USER='joe', MASTER_PASSWORD='secret';

OR

2) Set the variables below. However, in case you choose this method, then

start replication for the first time (even unsuccessfully, for example

if you mistyped the password in master-password and the slave fails to

connect), the slave will create a master.info file, and any later

change in this file to the variables' values below will be ignored and

overridden by the content of the master.info file, unless you shutdown

the slave server, delete master.info and restart the slaver server.

For that reason, you may want to leave the lines below untouched

(commented) and instead use CHANGE MASTER TO (see above)

required unique id between 2 and 2^32 - 1

(and different from the master)

defaults to 2 if master-host is set

but will not function as a slave if omitted

server-id = 2

The replication master for this slave - required

master-host =

The username the slave will use for authentication when connecting

to the master - required

master-user =

The password the slave will authenticate with when connecting to

the master - required

master-password =

The port the master is listening on.

optional - defaults to 3306

master-port =

binary logging - not required for slaves, but recommended

log-bin=mysql-bin

binary logging format - mixed recommended

binlog_format=mixed

Uncomment the following if you are using InnoDB tables

innodb_data_home_dir = /var/lib/mysql

innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

innodb_log_group_home_dir = /var/lib/mysql

You can set .._buffer_pool_size up to 50 - 80 %

of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 384M

innodb_additional_mem_pool_size = 20M

Set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 100M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

Remove the next comment character if you are not familiar with SQL

safe-updates

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
参考:https://www.cnblogs.com/bigbrotherer/p/7241845.html