MySQL单机部署

1 介绍

1.1 概述

单机部署，适用于非关键业务或者测试使用。

1.2 软件包

2 部署步骤

2.1 卸载系统自带的MySQL

在所有服务器上执行：

[root@MYSQL /]# rpm -qa|grep mysql

如果有输出结果，先进行卸载：

[root@MYSQL /]# rpm -e --nodeps xxx(上面的输出结果)

2.2 单机版安装

2.2.1 上传、安装软件包

将mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz复制到服务器的/usr/local/目录下，然后解压。解压完成后删除压缩包，并重命名解压出来的目录。

[root@MYSQL ~]# cd /usr/local/
[root@MYSQL local]# tar -xvf mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
mysql-8.0.18-linux-glibc2.12-x86_64/bin/
mysql-8.0.18-linux-glibc2.12-x86_64/bin/myisam_ftdump
……
mysql-8.0.18-linux-glibc2.12-x86_64/share/install_rewriter.sql
mysql-8.0.18-linux-glibc2.12-x86_64/share/uninstall_rewriter.sql
[root@MYSQL local]# rm -f mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
[root@MYSQL local]# mv mysql-8.0.18-linux-glibc2.12-x86_64/ mysql

2.2.2 创建mysql用户组和用户

[root@MYSQL local]# groupadd mysql
[root@MYSQL local]# useradd -g mysql mysql

2.2.3 创建数据目录、socket目录并给相关目录赋权

[root@MYSQL local]# mkdir -p /var/lib/mysql
[root@MYSQL local]# mkdir -p /data/mysql/data
[root@MYSQL local]# chown -R mysql:mysql /var/lib/mysql/
[root@MYSQL local]# chown -R mysql:mysql /usr/local/mysql/
[root@MYSQL local]# chown -R mysql:mysql /data/mysql/data/
[root@MYSQL local]# chmod -R 755 /var/lib/mysql/
[root@MYSQL local]# chmod -R 755 /usr/local/mysql/
[root@MYSQL local]# chmod -R 755 /data/mysql/data/

2.2.4 初始化mysql服务

1、该步骤会在消息打印中输出默认的root密码。此密码在首次登录时需要输入，如果丢失，可以先清空datadir，重新进行初始化。
注意保存输出的默认密码。可以使用XShell保存日志文件的方式记录输出。
2、如果报错“error while loading shared libraries: libaio.so.”，先安装libaio-0.3.109-13.el7.x86_64.rpm。

libaio-0.3.109-13.el7.x86_64.rpm下载地址：
https://download.csdn.net/download/Zhuge_Dan/12558796

[root@MYSQL local]# rpm -qa|grep libaio
libaio-0.3.109-13.el7.x86_64
[root@MYSQL local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql/data/
2020-06-02T02:12:10.385762Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-06-02T02:12:10.385911Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.18) initializing of server in progress as process 8907
2020-06-02T02:12:14.429872Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: jyBybJg3%z>l

注意：保存此处默认的root密码：jyBybJg3%z>l

2.2.5 注册mysql服务和命令

[root@MYSQL local]# cp mysql/support-files/mysql.server /etc/rc.d/init.d/mysql
[root@MYSQL local]# chmod +x /etc/rc.d/init.d/mysql
[root@MYSQL local]# ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
[root@MYSQL local]# ln -s /usr/local/mysql/bin/mysqldump /usr/bin/mysqldump
[root@MYSQL local]# chmod +x mysql/bin/*

2.2.6 创建mysql主配置文件

[root@MYSQL local]# vi /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/data
socket=/var/lib/mysql/mysql.sock
user=mysql
#此参数可以控制服务端口监听在哪个网卡上
#bind_address=127.0.0.1
port=3306
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:5G
skip-character-set-client-handshake
skip-host-cache
skip-name-resolve
collation_server=utf8mb4_general_ci
character_set_server=utf8mb4
init-connect=SET NAMES utf8mb4
default-time-zone='+08:00'
# Disabling symbolic-links is recommended to prevent assorted security risks
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
max_connections=3000
thread_cache_size=64
wait_timeout=28800
tmp_table_size=16M
max_heap_table_size=8M
interactive_timeout=28800
thread_stack=192K
join_buffer_size = 4M
key_buffer_size = 16M
bulk_insert_buffer_size = 8M
read_buffer_size = 8M
sort_buffer_size = 2M
max_allowed_packet = 64M
read_rnd_buffer_size = 32M
group_concat_max_len=102400
#设置为可用物理内存70%左右
innodb_buffer_pool_size = 6G
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 35
innodb_lock_wait_timeout = 1500
event_scheduler=ON
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
log_bin_trust_function_creators=ON
#binlog保留多长时间
binlog_expire_logs_seconds=2592000
[mysqladmin]
socket=/var/lib/mysql/mysql.sock
[mysql]
socket=/var/lib/mysql/mysql.sock
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqldump]
socket=/var/lib/mysql/mysql.sock
[client]
socket=/var/lib/mysql/mysql.sock
default-character-set=utf8mb4 

2.2.7 启动mysql服务

CentOS 7.6.1810版本提示：

[root@MYSQL local]# service mysql restart
 ERROR! MySQL server PID file could not be found!
Starting MySQL..... SUCCESS!

CentOS 6.10版本提示：

[root@MYSQL local]# service mysql restart
MySQL server PID file could not be found!                  [FAILED]
Starting MySQL.....                                        [  OK  ]

2.2.8 修改root密码并赋权

[root@MYSQL local]# mysql -uroot -p
Enter password:						#密码为第4步输出的内容，如果已经丢失，需要重新进行初始化
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.18

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

#重置root@localhost密码。密码根据需要调整。
mysql> ALTER USER USER() IDENTIFIED BY 'atenorth';
Query OK, 0 rows affected (0.01 sec)

#创建外部访问的root账户，为了兼容旧版程序，密码校验方式使用本地密码方式（根据需要，此步骤可以省略）
mysql> CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'atenorth' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;						
Query OK, 0 rows affected (0.00 sec)

#添加应用程序用户
mysql> CREATE USER 'Raikon'@'%' IDENTIFIED WITH mysql_native_password BY 'atenorth' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE Contacts character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)

#把Contacts库的所有表，分配给Raikon用户all权限（根据需要调整），注意去掉WITH GRANT OPTION，即不给应用程序用户分配“分配权限”的权限。
mysql> GRANT ALL ON Contacts.* TO 'Raikon'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

2.2.9 加入开机自启

[root@MYSQL local]# chkconfig mysql on