MySQL-8.0布署
下载软件
https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz
解压软件
tar xvf mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz -C /usr/local/ mv /usr/local/mysql-8.0.35-linux-glibc2.17-x86_64 /usr/local/mysql-8.0.35
安装依赖包
yum install libaio net-tools -y
准备工作
创建用户
groupadd mysql
useradd -g mysql mysql -s /sbin/nologin
创建所需的目录
mkdir -p /usr/local/mysql-8.0.35/{data,log,binlog,tmp}
配置环境变量
cat << 'CAT_END' >> /etc/profile export MYSQL_HOME=/usr/local/mysql-8.0.35 export PATH=$MYSQL_HOME/bin:$PATH CAT_END source /etc/profile
准备配置文件
cat << 'CAT_END' >>/etc/my.cnf [client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] ##########encoding################### character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci init_connect='SET NAMES utf8mb4' ##################################### lower_case_table_names = 1 user = mysql server_id = 1 port = 3306 default-time-zone = '+08:00' enforce_gtid_consistency = OFF gtid_mode = OFF binlog_checksum = none default_authentication_plugin = mysql_native_password datadir = /usr/local/mysql-8.0.35/data pid-file = /usr/local/mysql-8.0.35/tmp/mysqld.pid socket = /usr/local/mysql-8.0.35/tmp/mysqld.sock tmpdir = /usr/local/mysql-8.0.35/tmp/ skip-name-resolve = ON open_files_limit = 65535 table_open_cache = 2000 #################innodb######################## innodb_data_home_dir = /usr/local/mysql-8.0.35/data innodb_data_file_path = ibdata1:512M;ibdata2:512M:autoextend innodb_buffer_pool_size = 12000M innodb_flush_log_at_trx_commit = 1 innodb_io_capacity = 600 innodb_lock_wait_timeout = 120 innodb_log_buffer_size = 8M innodb_log_file_size = 200M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 85 innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_thread_concurrency = 32 innodb_file_per_table innodb_rollback_on_timeout innodb_undo_directory = /usr/local/mysql-8.0.35/data innodb_log_group_home_dir = /usr/local/mysql-8.0.35/data ###################session########################### join_buffer_size = 8M key_buffer_size = 256M bulk_insert_buffer_size = 8M max_heap_table_size = 96M tmp_table_size = 96M read_buffer_size = 8M sort_buffer_size = 2M max_allowed_packet = 64M read_rnd_buffer_size = 32M ############log set################### log-error = /usr/local/mysql-8.0.35/log/mysqld.err log-bin = /usr/local/mysql-8.0.35/binlog/binlog log_bin_index = /usr/local/mysql-8.0.35/binlog/binlog.index max_binlog_size = 500M slow_query_log_file = /usr/local/mysql-8.0.35/log/slow.log slow_query_log = 1 long_query_time = 10 log_queries_not_using_indexes = ON log_throttle_queries_not_using_indexes = 10 log_slow_admin_statements = ON log_output = FILE,TABLE master_info_file = /usr/local/mysql-8.0.35/binlog/master.info CAT_END
设置安装的MySQL目录为mysql用户
chown -R mysql.mysql /usr/local/mysql-8.0.35
初始化数据库
mysqld --defaults-file=/etc/my.cnf --datadir=/usr/local/mysql-8.0.35/data --initialize-insecure --user=root
修改系统自带启动脚本
默认配置文件【默认配置文件/etc/my.cnf】
]# vi /usr/local/mysql-8.0.35/support-files/mysql.server # 修改如下3个参数即可 basedir=/usr/local/mysql-8.0.35 datadir=/usr/local/mysql-8.0.35/data mysqld_pid_file_path=/usr/local/mysql-8.0.35/tmp/mysqld.pid
自定义的配置文件修改脚本
]# vi /usr/local/mysql-8.0.35/support-files/mysql.server # 修改如下3个参数即可 basedir=/usr/local/mysql-8.0.35 datadir=/usr/local/mysql-8.0.35/data mysqld_pid_file_path=/usr/local/mysql-8.0.35/tmp/mysqld.pid case "$mode" in 'start') ... # 这里需要加上--defaults-file才可以 $bindir/mysqld_safe --defaults-file=/usr/local/mysql-8.0.35/conf/my.cnf --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null & wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?
使用mysql用户运行脚本
sudo -u mysql /usr/local/mysql-8.0.35/mysql.server start
检查启动端口
[root@localhost ~]# netstat -tunlp | grep mysql tcp6 0 0 :::3306 :::* LISTEN 24791/mysqld tcp6 0 0 :::33060 :::* LISTEN 24791/mysqld
3306和33060端口号区别
MySQL服务使用不同的端口号来进行数据库服务，两个端口号之间有很大的区别。关系型数据库管理系统 MySQL 支持多种端口号，其中一个常见的是3306和33060端口号。这两个概念相似，所以有时会被混淆，但实际上它们间有很大的不同。
3306端口号是MySQL客户端连接的标准端口号，不管是本地还是远程。它用来接受连接请求，并进行客户端和数据库中的后端数据传输。MySQL服务器使用3306端口号来处理收到的连接，并将它们发送到数据库中，与数据库进行通信。
相比之下，33060端口号用于MySQL的通信。它支持MySQL的安全连接，可以通过SSL协议加密传输数据，保障数据的安全传输。MySQL会在启动时创建两个连接端口：33060端口号，用于安全连接；另一个为3306，用于非安全连接。
3306端口号只使用TCP协议进行通信，所以传输的数据不安全，而33060端口号使用SSL协议进行通信，可以确保数据的安全性。如果要实现MySQL的安全连接，就必须使用33060端口号，其写法如下：
mysqld_safe --ssl-ca=/etc/ca.pem --ssl-cert=/etc/cert.pem --ssl-key=/etc/key.pem --port=33060 总而言之，3306和33060端口号的最大区别在于它们使用的不同的协议，3306端口号使用TCP协议进行通信，而33060端口号使用SSL协议进行通信。
因此，为了保证MySQL数据的安全性，建议使用33060端口号进行安全连接，并使用SSL加密传输数据。
基本操作
修改ROOT密码
alter user 'root'@'localhost' identified by 'root'
查看当前的用户
select host,user,authentication_string from mysql.user;
创建用户并授权
create user 'test'@'%' identified by 'test'; grant all privileges on *.* to 'test'@'%' with grant option;
报错
错误1
提示
mysql> grant all on *.* to test@'%'; ERROR 1785 (HY000): Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement
transactions, and never in the same statement as updates to transactional tables.
解决方法
[root@localhost ~]# vi /etc/my.cnf [mysqld] ... enforce_gtid_consistency = ON gtid_mode = ON ...
错误2
提示
mysql> grant all privileges on *.* to 'test'@'%' with grant option; ERROR 1726 (HY000): Storage engine 'MyISAM' does not support system tables. [mysql.db]
解决方法
# 停止MySQL /usr/local/mysql-8.0.35/mysql.server stop # 执行 mysqld --skip-grant-tables --user=mysql --upgrade=FORCE & # 停止进程 pkill mysql # 启动MySQL /usr/local/mysql-8.0.35/mysql.server start
参考文章：https://www.cnblogs.com/kevingrace/p/10482469.html
