MySQL部署

1、卸载mariadb

rpm -qi mariadb-libs

yum remove mysql-libs -y

2、查看操作系统内核版本及硬件架构

uname -a

3、查看glibc版本

ldd --version

4、下载mysql压缩包

wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.35-linux-glibc2.17-x86_64.tar

5、解压到/mnt目录

yum install -y tar

tar -xvf mysql-8.0.35-linux-glibc2.17-x86_64.tar -C /usr/local

cd /usr/local

tar -xf mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz

mv mysql-8.0.35-linux-glibc2.17-x86_64 mysql

6、创建数据目录

mkdir -p /mnt/data/mysql8_data/mysql

7、创建用户组、用户及授权

groupadd mysql

useradd -g mysql mysql

chown -R mysql.mysql /usr/local/mysql
chown -R mysql.mysql /mnt/data/mysql8_data

8、创建my.cnf文件

      touch /etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
port=13306
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
server_id=100
binlog-ignore-db=sys
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062

datadir=/mnt/data/mysql8_data/mysql
log-bin=/mnt/data/mysql8_data/log-bin
socket=/usr/local/mysql/mysql.sock

log-error=/mnt/data/mysql8_data/mysqld.log
pid-file=/mnt/data/mysql8_data/mysqld.pid

character-set-server=utf8mb4
lower_case_table_names=1
autocommit =1

skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 100M
table_open_cache = 1024
sort_buffer_size = 4M
net_buffer_length = 8K
read_buffer_size = 4M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 64M
thread_cache_size = 128
 
tmp_table_size = 128M
explicit_defaults_for_timestamp = true
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
 
binlog_format=mixed
 
binlog_expire_logs_seconds =864000

default_storage_engine = InnoDB
innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
transaction-isolation=READ-COMMITTED
[mysqldump]
quick
max_allowed_packet = 16M
 
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M
 
[mysqlhotcopy]
interactive-timeout=2880000
wait_timeout=2880000
 

9、 初始化基础信息得到临时密码

cd /usr/local/mysql/bin

./mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/mnt/data/mysql8_data/mysql/ --initialize

vi /mnt/data/mysql8_data/mysqld.log

10、 添加mysqld服务到系统

cd /usr/local/mysql

cp -a ./support-files/mysql.server /etc/init.d/mysql

chmod +x /etc/init.d/mysql

chkconfig --add mysql

service mysql start

service mysql status

11、 将mysql命令添加到系统指令

ln -s /usr/local/mysql/bin/mysql /usr/bin

ln -s /usr/local/mysql/mysql.sock /tmp/mysql.sock

12、 登录mysql，密码使用之前随机生成的密码

mysql -uroot -p

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'ron999';

flush privileges;

use mysql;

update user set host='%' where user='root';

flush privileges;

13、MySQL导出数据库

mysqlpump -u root -p -P13306 --exclude-databases=mysql,sys,information_schema,performance_schema --all-databases --result-file=/mnt/backup.sql

14、MySQL导入数据库

nohup mysql -u root -p -P13306 < backup.sql > import.log 2>&1 &