最详细的MySQL/MariaDB二进制安装

本文详细写MySQL/MariaDB二进制安装的过程

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

1.规划：

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

缓存目录位置                      /data/mysql/tmp

2.创建目录和授权

mkdir -p /data/mysql/{data,logs,tmp}

useradd mysql -s /sbin/nologin -M

chown -R mysql:mysql /data/mysql

chmod -R 770 /data/mysql

3.安装初始化依赖

 yum install -y autoconf libaio-devel

4.准备安装文件#mariadb

wget https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-10.2.39/bintar-linux-x86_64/mariadb-10.2.39-linux-x86_64.tar.gz

#mysql
wget https://mirrors.aliyun.com/mysql/MySQL-5.7/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz

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

 解压

tar xf mariadb-10.2.39-linux-x86_64.tar.gz -C /usr/local

5.创建软连接、配置环境变量

cd /usr/local/
  
ln -s mariadb-10.2.39-linux-x86_64/ mysql
 
echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh

source /etc/profile.d/mysql.sh

6.初始化及启动文件

cd /usr/local/mysql
#初始化
scripts/mysql_install_db --datadir=/data/mysql/data--datadir=/data/mysql/data --user=mysql

这是MariaDB初始化方式，初始化完会有OK提示

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

cd support-files/
#拷贝mysql配置文件
cp my-huge.cnf /etc/my.cnf

  #拷贝启动脚本

  cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

mariadb初始化案例：

/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql/data --user=mysql
#这是MariaDB初始化方式

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/

 

配置文件

[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/mysql/logs/mysql_relay.log
bind-address = 0.0.0.0

#tmpdir=/data//mysqltmp
#当数据库是大量存储时建议指导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

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

 

7.启动MySQL

 

#方法一
service mysqld start

#方法二
/etc/init.d/mysqld start

#方法三 systemctl启动

systemctl start mysql.service

方法三：

pid文件

vim /usr/lib/systemd/system/mysql.service　

[Unit]
Description=MySQL DBMS

[Service]
LimitNOFILE=10000
Type=simple
User=mysql
Group=mysql
PIDFile=/data/mysql/mysql.pid
ExecStart=/usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql
ExecStop=/bin/kill -9 $MAINPID

[Install]
WantedBy=multi-user.target

8.2.mariadb安全初始化

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> quit
Bye

由于数据库的登陆不需要密码所以不安全，我们要安全初始化。

[root@mysql support-files]# mysql_secure_installation

Enter current password for root (enter for none): 当前数据库管理员的密码，无密码直接回车
Set root password? [Y/n] Y 设定数据库管理员密码
New password:
Re-enter new password:
Password updated successfully!
... Success!
Remove anonymous users? [Y/n] y
... Success! 移除匿名用户登陆
Disallow root login remotely? [Y/n] y
... Success! 移除管理员远程登陆
Remove test database and access to it? [Y/n] Y
... Success! 移除测试库
Reload privilege tables now? [Y/n] y
... Success! 刷新数据库

使用mysqladmin改密

mysqladmin -uroot -p password '123'