基于Centos 安装 MySQL

一、准备好Jar包

1. 查看glibc的版本

getconf GNU_LIBC_VERSION

2. 下载对应jar包

2.1 Mysql 官网： https://www.mysql.com/

2.2 找到要下载版本，类型

二、安装MySQL

以下命令切换至 root

1. 解压安装包

# 我这里是放到 /usr/local 下
# 1、
cd /usr/local/ 
# 2、
tar -xvf mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz
# 3、
mv mysql-8.0.28-linux-glibc2.12-x86_64/* mysql/

1.1 创建用户和组

/usr/sbin/groupadd -g 2000 mysql
/usr/sbin/useradd -r -g mysql -s /bin/false -u 2000 mysql

1.2 建立安装目录

再次提示：以 root 用户建立安装目录、介质目录、数据文件目录并修改目录的权限！！！

mkdir -p /usr/local/mysql
mkdir -p /mysql/tool
mkdir -p /data/mysql/3306/
mkdir -p /mysql/log/binlog
mkdir -p /mysql/log/relay
chown -R mysql:mysql /mysql
chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /data/mysql/3306/
chmod 755 -R /mysql

2. 配置环境变量

# 1、
cat >> ~/.bash_profile <<EOF
export MYSQL_HOME="/usr/local/mysql"
export PATH="\$PATH:\$MYSQL_HOME/bin"
EOF
# 2、
source ~/.bash_profile
# 3、
which mysql
# 执行3后输出  /usr/local/mysql/bin/mysql

3. 配置参数文件

# 1、程序员好习惯  先备份
cp /etc/my.cnf /etc/my.cnf.bak

# 2、编写
[mysql]
port = 3306
socket = /data/mysql/3306/mysql.sock
default_character_set = utf8mb4
prompt = [\\u@\\h] [\\d]>\\_
[client] 
# CLIENT # 
port = 3306
socket = /data/mysql/3306/mysql.sock
default_character_set = utf8mb4
[mysqld]
# basic settings #
user = mysql
port = 3306
socket = /data/mysql/3306/mysql.sock
server_id = 2233306 #不同实例之间不能相同
datadir = /data/mysql/3306/
basedir = /usr/local/mysql
pid-file = /data/mysql/3306/mysql.pid
log-error = /mysql/log/mysql-error.log
max-allowed-packet = 128M
max_connections = 1000
table_open_cache = 4096
authentication_policy = mysql_native_password
collation_server = utf8mb4_general_ci
thread_stack = 512K
max_prepared_stmt_count = 20000
max_connect_errors = 65535
wait_timeout = 300
interactive_timeout = 600
connect_timeout = 10
net_read_timeout = 1200
net_write_timeout = 1200
max_allowed_packet = 128M
slow_query_log = ON
slow_query_log_file = /mysql/log/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 10
read_buffer_size = 4M
read_rnd_buffer_size = 4M
sort_buffer_size = 8M
join_buffer_size = 8M
tmp_table_size = 32M
max_heap_table_size = 32M
key_buffer_size = 4M
#max_execution_time = 1200
lock_wait_timeout = 60
lower_case_table_names = 1
thread_cache_size = 64
log_timestamps = SYSTEM
event_scheduler = OFF
secure_file_priv = /data/mysql/3306/
# binlog #
binlog_expire_logs_seconds = 604800 ##8.0 binlog 保留时间,单位为秒,7 天
sync_binlog = 1
log_bin = /mysql/log/binlog/mysql-bin
max_binlog_size = 500M
binlog_cache_size = 2M
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_format = ROW
binlog_group_commit_sync_delay = 100
binlog_group_commit_sync_no_delay_count = 10
# INNODB #
default_storage_engine = InnoDB
transaction_isolation = READ-COMMITTED
innodb_data_home_dir = /data/mysql/3306/
innodb_data_file_path = ibdata1:256M:autoextend
innodb_log_file_size = 1G
innodb_log_files_in_group = 4
innodb_log_group_home_dir = /data/mysql/3306/
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 1000
innodb_io_capacity_max = 4000
innodb_buffer_pool_size = 2G ##根据机器配置进行修改,建议设置为
OS_MEM 的 50%-70%,最大不超过 80%
innodb_log_buffer_size = 512M
innodb_max_dirty_pages_pct = 85
innodb_lock_wait_timeout = 10
innodb_open_files = 5000
innodb_page_cleaners = 4
innodb_sort_buffer_size = 64M
innodb_print_all_deadlocks = 1
innodb_rollback_on_timeout = ON
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_autoinc_lock_mode = 1
innodb_online_alter_log_max_size = 4G
innodb_max_undo_log_size = 50M
innodb_undo_directory = /data/mysql/3306
innodb_temp_data_file_path = 
ibtmp1:12M;ibtmp2:128M:autoextend:max:500M
##### P_S ####
performance-schema = 1
performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'
performance_schema_instrument = '%wait%=on'
performance-schema-instrument = 'wait/lock/metadata/sql/mdl=on'
[mysqldump]
max_allowed_packet = 128M
default_character_set = utf8mb4

#上述有备注的参数需按照实际进行修改。

4. 初始化数据库

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql &

初始化较慢，可多按几下回车键，有以下提示后在继续操作。

[1]+ 完成 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --
initialize --user=mysql

5. 首次启动数据库

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

启动后检查数据库日志错误日志，查看启动过程信息。

# 1、查看初始密码
cat /mysql/log/mysql-error.log |grep password
# 2、登录MySQL   
mysql -h localhost -uroot -p
# 3、输入初始密码
> 
# 4、修改密码  (By后面引号内是新密码)
alter user 'root'@'localhost' identified with mysql_native_password by 'rootpasswd';
# 5、分别执行以下命令
flush privileges;
exit;

可以使用新密码重新登录，验证下，这毕竟是刻在程序员中的强迫症，哈哈哈哈！

6.配置MySQL服务 (service) – (配置自启动)

# 依次执行每行命令
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld

• 编辑服务文件，修改以下内容

sed -i '45,48 s#basedir=#basedir=/usr/local/mysql#' /etc/init.d/mysqld
sed -i '45,48 s#datadir=#datadir=/data/mysql/3306#' /etc/init.d/mysqld
sed -i "55,58 s#lockdir='/var/lock/subsys'#lockdir='/data/mysql/3306'#" /etc/init.d/mysqld
sed -i '61,65 s#mysqld_pid_file_path=#mysqld_pid_file_path=/data/mysql/3306/mysql.pid#' /etc/init.d/mysqld

• 检查是否配好

cat /etc/init.d/mysqld |grep -E "^basedir=|^datadir=|^mysqld_pid_file_path=|^lockdir="

# 出现以下内容就 OK
basedir=/usr/local/mysql
datadir=/data/mysql/3306
lockdir='/data/mysql/3306'
mysqld_pid_file_path=/data/mysql/3306/mysql.pid

7. 重启数据库

# 依次执行
service mysqld stop
service mysqld start

8.配置MySQL服务（systemd）

• 配置启动脚本root管理员

# 执行
cat >/etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf 
LimitNOFILE = 65535
EOF

• 重启数据库

# 如果先配置了 SERVICE,并已经启动,可以先关闭然后用 SYSTEMD 来进行启动
service mysqId stop
# 依次执行
systemctl daemon-reload
systemctl enable mysqld
systemctl stop mysqld
systemctl start mysqld
systemctl status mysqld

三、开通远程访问

默认是不能远程连接，会报错的

1. 开通远程连接

# 1.先root登录
mysql -u root -p
# 2.先切换至mysql库
use mysql;
# 3.允许远程登录
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'youpassword' WITH GRANT OPTION;
# 4.重新加载授权表
FLUSH PRIVILEGES;
# 5.使用工具(navicat等)，root账号，测试是否连通

可在菜鸟教程查看 命令 详解：https://www.runoob.com/mysql/mysql-administration.html

四、创建新的账号，密码

1. 创建用户

# 1.先切换至mysql库
use mysql;
# 2.创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'PASSWORD';

属性	含义
username	新账号name
host	指定可访问ip，如果指定所有ip都能访问，可将其设为通配符%即可
password	登录密码，如果密码为空则无需密码

2. 为用户授权

grant privileges on datebase.tablename to 'username'@'host';

属性	含义
privileges	用户的操作权限，如 SELECT，INSERT，UPDATE等，如果授予所有权限则使用ALL
database	如果不指定数据库，直接 * . * 即可，如果指定数据库但不指定表名，则 database.* 即可
username	登录用户名
host	给予授权的主机ip，不指定就使用%

5、升级版本

何为小版本：比如现在8.0.36—8.2.0 都算是小版本；
何为大版本： 由原来的5.7- 8.0.36就是大版本。体现在系统兼容性上，帐户安全，设置持久化等多方面。

1. 小版本

# 1. 需要停服务升级，停库，停库，停库！！！
systemctl stop mysqld
systemctl status mysqld
# 2. 保证下jar的cglib版本和系统是一致的，可以查看最前内容
getconf GNU_LIBC_VERSION
# 3. 找到原来的mysql目录，进行备份
MV mysql mysql.bak
# 4. 解压新的jar
# 5. 重启启动，检测是否正常

2. 大版本