MySQL/MariaDB二进制安装

本文说明MySQL/MariaDB二进制安装的过程

mysql和mariadb的安装方式基本一致,唯一初始化方式有点不一样

1.规划：

数据文件存储位置                /data/mysql/data
日志文件存储位置                /data/logs/mysql
binlog文件存储位置             /data/mysql

缓存目录位置                      /dta/tmp

我这里规划都是放在 /data目录下是因为挂载了2T硬盘，方便存储

你们可以视情况而定

2.创建目录和授权：

[root@Mike_Node-1 ~]# mkdir -p /data/mysql/data
[root@Mike_Node-1 ~]# mkdir -p /data/logs/mysql
[root@Mike_Node-1 ~]# mkdir -p /data/logs/tmp
[root@Mike_Node-1 ~]# useradd mysql
[root@Mike_Node-1 ~]# chown -R mysql:mysql /data/mysql
[root@Mike_Node-1 ~]# chown -R mysql:mysql /data/logs
[root@Mike_Node-1 ~]# chown -R mysql:mysql /data/tmp
[root@Mike_Node-1 ~]# chmod -R 775 /data/mysql
[root@Mike_Node-1 ~]# chmod -R 775 /data/logs
[root@Mike_Node-1 ~]# chmod -R 775 /data/tmp

创建数据库目录和赋予数据库普通用户权限

3.文件下载

[root@Mike_Node-1 ~]# yum search libaio
[root@Mike_Node-1 ~]# yum install libaio

上面的意思是检测相关信息和安装依赖包

[root@Mike_Node-1 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz                               ###MySQL下载

[root@Mike_Node-1 ~]# wget https://mirrors.ustc.edu.cn/mariadb//mariadb-10.3.27/bintar-linux-x86_64/mariadb-10.3.27-linux-x86_64.tar.gz                ####MariaDB下载

上面分别是MySQL5.7版本和MariaDB10.3版本的下载地址，可以根据安装需求来下载

4.解压文件

[root@Mike_Node-1 ~]# tar zxvf mariadb-10.3.24-linux-x86_64.tar.gz -C /usr/local/
[root@Mike_Node-1 ~]# cd /usr/local/
[root@Mike_Node-1 ~]# mv mariadb-10.3.24-linux-x86_64 mysql

这里已解压 MariaDB 为例

下载完解压到  /usr/local/ 目录下，再重命名为 mysql 目录，Mysql同样的操作

5.设置mysql的配置文件

[root@Mike_Node-1 ~]# vim /etc/my.cnf

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
user = mysql
datadir = /data/mysql/data
pid-file = /data/mysql/mysql.pid
server-id = 50
relay_log =/data/logs/mysql/mysql_relay.log
bind-address = 0.0.0.0

tmpdir=/data/tmp
#当数据库是大量存储时建议指导TMP目录，否则做全表操作时临时空间会不足

init_connect ='SET NAMES utf8'
character-set-server = utf8
skip-name-resolve
back_log = 300

max_connections = 8019
max_connect_errors = 1024000
open_files_limit = 65535
table_open_cache = 2048
max_allowed_packet = 50M
max_heap_table_size = 512M
tmp_table_size = 256M

read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 512M
thread_cache_size = 128

query_cache_type = 2
query_cache_size = 128M
query_cache_limit = 5M
thread_stack = 192k
ft_min_word_len = 4

log_bin = /data/mysql/mysql_bin.log
binlog_format = ROW
expire_logs_days = 7

log_error = /data/logs/mysql/mysql_error.log
slow_query_log = 1
long_query_time = 1
log_slow_verbosity=query_plan
slow_query_log_file = /data/logs/mysql/mysql_slow.log
performance_schema = 0

skip-external-locking #跳过外部锁定，避免external locking

bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

###InnoDB###
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 2048M
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 10
innodb_autoinc_lock_mode = 2
innodb_doublewrite = 1
###New ADD
innodb_rollback_on_timeout = 1
innodb_force_recovery=0
interactive_timeout = 28800
wait_timeout = 120

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

上面是配置文件，可以根据自己的参数自行修改

6.初始化数据库

[root@Mike_Node-1 ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql/data --user=mysql
这是MariaDB初始化方式，初始化完会有OK提示

[root@Mike_Node-1 ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data
这是MySQL初始化方式，初始化完，密码在日志中查看MySQL5.7（/data/logs/mysql/中）


Installing MariaDB/MySQL system tables in '/data/mysql/data' ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system


PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

'/usr/local/mysql/bin/mysqladmin' -u root password 'new-password'
'/usr/local/mysql/bin/mysqladmin' -u root -h 127.0.0.1
100.100.0.164 password 'new-password'

Alternatively you can run:
'/usr/local/mysql/bin/mysql_secure_installation'

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/usr/local/mysql' ; /usr/local/mysql/bin/mysqld_safe --datadir='/data/mysql/data'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/local/mysql/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/

上面是初始化 MariaDB数据库的方式提示有OK就可以了~

MySQL的话初始化差不多，初始化完密码会在日志中，而MariaDB则空密码

7.设置启动文件：

[root@Mike_Node-1 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

8.启动数据库：

[root@Mike_Node-1 ~]# /etc/init.d/mysql start
Starting mysql (via systemctl): [ OK ]

9.配置环境变量

[root@Mike_Node-1 ~]# vim /etc/profile

####在最后面添加
export MYSQL_HOME=/usr/local/mysql
export PATH=$MYSQL_HOME/bin:$PATH

[root@Mike_Node-1 ~]# source /etc/profile

10.登录数据库

[root@Mike_Node-1 ~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 10
Server version: 10.3.24-MariaDB-log MariaDB Server

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

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

MariaDB [(none)]> 
MariaDB [(none)]> set password =password('abc-123');                                                            #####修改root密码为 abc-123
Query OK, 0 rows affected (0.001 sec) 

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> grant all privileges on *.* to root@'%' identified by 'abc-123' with grant option;            ####修改远程root用户权限和密码  abc-123
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]>

这是mariadb登录方式

mysql的话初始化会有密码保存日志文件里，你需要到里面查看使用密码登录

MySQL密码登录   mysql -uroot -p 就可以了

11.再次登录MySQL

[root@Mike_Node-1 ~]# mysql -uroot -p

然后就可以通过 Navicat客户端登录到数据库方便操作

安装到此结束

本文分享完毕，感谢支持点赞~~