[root@localhost ~]# cat >> /etc/yum.repos.d/MariaDB.repo << EOF
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF

[root@localhost ~]# yum install MariaDB-server MariaDB-client MariaDB-devel

[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# systemctl enable mariadb

[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
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):  #<--直接回车，表示空密码
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n
 ... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the 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.

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] Y
 - 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] 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@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.4.12-MariaDB MariaDB Server

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

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)

MariaDB [(none)]> 

[root@localhost ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[root@localhost ~]# cat /etc/my.cnf.d/server.cnf  | egrep -v "^#|^$"
[server]
[mysqld]
[galera]
[embedded]
[mariadb]
[mariadb-10.4]

[root@localhost ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq 
[root@localhost ~]# echo noop > /sys/block/sda/queue/scheduler
[root@localhost ~]# cat /sys/block/sda/queue/scheduler
[noop] deadline cfq 

[root@localhost ~]# echo "mysql soft nofile 65535" >> /etc/security/limits.conf
[root@localhost ~]# echo "mysql hard nofile 65535" >> /etc/security/limits.conf
#在新的ssh会话执行（打开新的ssh连接）
[root@localhost ~]# ulimit -Sn
65536
[root@localhost ~]# ulimit -Hn
65536
[root@localhost ~]# echo "mysql soft core unlimited" >> /etc/security/limits.conf
[root@localhost ~]# echo "mysql hard core unlimited" >> /etc/security/limits.conf
#在新的ssh会话执行（打开新的ssh连接）
[root@localhost ~]# su - mysql -s /bin/bash  -c "ulimit -Sc"
unlimited
[root@localhost ~]# su - mysql -s /bin/bash  -c "ulimit -Hc"
unlimited

[root@localhost ~]# sysctl vm.swappiness=0
vm.swappiness = 0
[root@localhost ~]# echo "vm.swappiness = 0" >> /etc/sysctl.conf 

[mysqld]
open_files_limit = 65535
back_log=3000     #连接堆栈大小，能放入堆栈中未被处理的请求数量
skip-name-resolve #关闭dns解析
skip-external-locking  #不使用系统锁定，要使用myisamchk,必须关闭服务器
memlock  #将mysqld 进程锁定在内存中
lower_case_table_names = 1 #不区分大小写
query_cache_type=1  #查询缓存  (0 = off、1 = on、2 = demand)查询缓存会存储一个select查询的文本与被传送到客户端的相应结果。如果之后接收到一个相同的查询，服务器会从查询缓存中检索结果，而不是再次分析和执行这个同样的查询。如果你的环境中写操作很少，读操作频繁，那么打开query_cache_type=1，会对性能有明显提升。如果写操作频繁，则应该关闭它（query_cache_type=0）。
query_cache_size = 64M #缓存select语句和结果集大小的参数。
performance_schema=0 #收集数据库服务器性能参数
net_read_timeout=3600 
net_write_timeout=3600
key_buffer_size = 32M     #这个参数用来缓存MyISAM存储引擎的索引参数
max_allowed_packet = 128M #通信缓冲大小
table_open_cache = 1024     #MySQL每打开一个表，都会读入一些数据到table_open_cache缓存中，当MySQL在这个缓存中找不到相应信息时，才会去盘上读取。默认值64，假定系统有200个并发连接，则需将此参数设置为200*N(N为每个连接所需的文件描述符数目)；当把table_open_cache设置为很大，如果系统处理不了那么多文件描述符，那么就会出现客户端失效，连接不上
sort_buffer_size = 12M # 在表进行order by和group by排序操作时，由于排序的字段没有索引，会出现Using filesort，为了提高性能，可用此参数加每个线程分配的缓冲区大小。默认为256KB，这个参数不要设置过大，一般在128～256KB即可。另外，一般出现Using filesort的时候，要通过增加索来解决
read_buffer_size = 8M #该参数用于表的顺序扫描，表示每个线程分配的缓冲区大小。比如在进行全表扫描时，MySQL会按照数据的存储顺序依次读取数据块，每次读取的数据块首先会暂存在read_buffer_size中，当buffer空间被写满或者全部数据读取结束后，再将buffer中的数据返回给上层调用者，以提高效率。默认为128K
read_rnd_buffer_size = 32M    #该参数用于表的随机读取，表示每个线程分配的缓冲区大小。比如，按照一个非索引字段做order by排序操作时，就利用这个缓冲区来暂存读取的数据。默认为256KB，
myisam_sort_buffer_size = 32M  #当对MyISAM表执行repair table或创建索引时，用以缓存排序索引；设置太小时可能会遇到” myisam_sort_buffer_size is too small”
thread_cache_size = 120                         #线程池，线程缓存。用来缓存空闲的线程，以至于不被销毁，如果线程缓存在的空闲线程，需要新建立新连接，则会优先调用线程池中的缓存，很快就能响应连接请求。每建立一个连接，都需要一个线程与之匹配。

join_buffer_size = 8M                           #Join操作使用内存
bulk_insert_buffer_size = 32M                   #批量插入数据缓存大小
delay_key_write=ON                              #在表关闭之前，将对表的update操作指跟新数据到磁盘，而不更新索引到磁盘，把对索引的更改录在内存。这样MyISAM表可以使索引更新更快。在关闭表的时候一起更新索引到磁盘
delayed_insert_limit=4000
delayed_insert_timeout=600
delayed_queue_size=4000
max_connections=1500                            #最大连接（用户）数。每个连接MySQL的用户均算作一个连接
max_connect_errors=30                           #最大失败连接限制
interactive_timeout=600                         #服务器关闭交互式连接前等待活动的秒数
wait_timeout=3600                               #服务器关闭非交互连接之前等待活动的秒数
slow_query_log                                  #慢查询记录日志
long_query_time = 0.1                           #慢查询记录时间  0.1秒
slow_query_log_file=/opt/local/mysql/logs/slow_query.log            #慢查询日志路径
log_slow_verbosity=query_plan   
log-bin=/var/lib/mysql/mysql-bin
log-slave-updates #从master取得并执行的二进制日志写入自己的二进制日志文件中
replicate-ignore-db=mysql                       #不同步的表
binlog_format=row                               ##如果启用了这一行，则会使得binlog更大，但是最安全。binlog 格式 分别为 row=行格式 丶 mixed=混合格式 丶 STATEMENT=SQL语句复制模式
event_scheduler=1                               #计划任务 事件调度器
server-id= 1   

#InnoDB配置
innodb_file_per_table=1 #InnoDB 提供了更灵活的方式，它把每个数据库的信息保存在一个 .ibd 数据文件中。每个 .idb 文件代表它自己的表空间。通过这样的方式可以更快地完成类似 “TRUNCATE” 的数据库操作，当删除或截断一个数据库表时，你也可以回收未使用的空间。这样配置的另一个好处是你可以将某些数据库表放在一个单独的存储设备。这可以大大提升你磁盘的 I/O 负载。
innodb_fast_shutdown=0

innodb_buffer_pool_size = 90000M     #这个参数是InnoDB存储引擎的核心参数，默认为128M，这个参数要设置为物理内存的60%～70%。
innodb_buffer_pool_instances = 4
innodb_log_file_size = 512M  #事务日志文件写操作缓存区的最大长度(默认设置是1MB)。
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 2 #这个选项决定着什么时候把日志信息写入日志文件以及什么时候把这些文件物理地写(术语称为”同步”)到硬盘上。设置值0的意思是每隔一秒写一次日 志并进行 同步，这可以减少硬盘写操作次数，但可能造成数据丢失; 设置值1(设置设置)的意思是在每执行完一条COMMIT命令就写一次日志并进行同步，这可以防止数据丢失，但硬盘写操作可能会很频繁; 设置值2是一般折衷的办法，即每执行完一条COMMIT命令写次日志，每隔一秒进行一次同步。
innodb_lock_wait_timeout = 3  #如果某个事务在等待n秒(s)后还没有获得所需要的资源，就使用ROLLBACK命令放弃这个事务。这项设置对于发现和处未能被InnoDB数据表驱动 程序识别出来的死锁条件有着重要的意义。这个选项的默认设置是50s。
innodb_rollback_on_timeout = on
innodb_flush_method=O_DIRECT
transaction-isolation=READ-COMMITTED
innodb_thread_concurrency=0
innodb_io_capacity=800
innodb_purge_threads=1
innodb_open_files=65535
innodb_autoinc_lock_mode=2
innodb_read_io_threads = 8
innodb_write_io_threads = 12
innodb_stats_on_metadata = 0
#使用线程池处理连接
thread_handling=pool-of-threads
thread_pool_oversubscribe=30
thread_pool_size=64
thread_pool_idle_timeout=7200
thread_pool_max_threads=2000
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[isamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M


[mysqlhotcopy]
interactive-timeout