文章目录
- 前言
- 一、资源准备
- 1.源码下载
- 2.依赖命令安装
- 2.1 安装依赖包
- 2.2 安装高版本gcc
- 2.3 安装高版本cmake
- 二、编译安装mysql
- 1.解压mysql-boost-8.0.16安装包
- 2.执行编译命令
- 3.执行make命令
- 4.执行make install 命令
- 5.编译安装完成后结果检查
- 三、初始化mysql
- 1. 创建数据相关目录及编辑my.cnf文件
- 2.创建用户并对相关目录授权
- 3.拷贝mysql3306.server文件并修改、授权
- 4.设置自启动并进行初始化操作
- 四、管理mysql
- 1.配置systemd管理mysql
- 2.添加快捷命令至profile文件中
- 五、mysql8.0使用验证
- 1.使用初始化时配置的root密码登录验证
- 2. 创建数据库验证
- 3.查看数据库用户及引擎验证
- 总结
前言
因为现在生产和预发等环境使用的mysql版本是5.7.32,比较老旧,同时在每次漏扫时也会遇到不同的漏洞,有时因为版本低不好修复。因此,内部开会沟通升级改造mysql至8.0版本。但因为两者的差异,不敢随意更新变动,因此,申请环境在搭建一个单节点的8.0版本mysql,让研发和测试、架构组等同学分别验证业务在高版本上的可行性。所以本篇文章以源码搭建mysql-8.0.16版本为示例。仅供参考!!!
一、资源准备
1.源码下载
此处使用的是包含boost的源码包。这样就不用单独在下载mysql依赖的boost库文件
[root@python1 packages]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-8.0.16.tar.gz
2.依赖命令安装
注意:按以下步骤操作,请服务器尽可能通外网,需要下载依赖的组件
2.1 安装依赖包
[root@python1 servers]# yum install -y vim rsync lrzsz expect make bison gcc gcc-c++ bc libaio libaio-devel zlib zlib-devel \nc python python-devel ncurses-devel sysstat perl-DBI perl-DBD-mysql perl-Time-HiRes perl-ExtUtils-CBuilder \perl-CPAN perl-Parallel-ForkManager perl-Config-IniFiles MySQL-python.x86_64 perl-Config-Tiny \perl-Log-Dispatch pigz trickle iftop dos2unix libarchive python-argparse libev numactl numactl-devel \libconfuse apr python-paramiko tmpwatch python-setuptools cmake3 net-tools
2.2 安装高版本gcc
[root@python1 servers]# yum -y install centos-release-scl devtoolset-11-gcc devtoolset-11-gcc-c++ devtoolset-11-binutils
#启用 devtoolset-11 软件集和,此时使用 g++ --version 以及 gcc --version 命令就会发现我们的 gcc g++ 已经完成了升级
[root@python1 servers]#scl enable devtoolset-11 bash # 但是scl命令的作用只是暂时的,如果要长久生效,我们就需要写入到配置文件中:
[root@python1 servers]# echo "source /opt/rh/devtoolset-11/enable" >>/etc/profile[root@python1 servers]# gcc --version
gcc (GCC) 11.2.1 20220127 (Red Hat 11.2.1-9)
Copyright (C) 2021 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.[root@python1 servers]# g++ --version
g++ (GCC) 11.2.1 20220127 (Red Hat 11.2.1-9)
Copyright (C) 2021 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE
当执行完上述命令后,再次检查如下文件,如下图所示即可
[root@python1 servers]# vim /opt/rh/devtoolset-11/enable
踩坑点1
注意事项:如果试图通过以下操作替换 gcc/g++ 来实现永久生效,那肯定是不行的,一定会在make操作时遇见报错[root@python1 servers]# mv /usr/bin/gcc /usr/bin/gcc-4.8.5[root@python1 servers]# sudo ln -s /opt/rh/devtoolset-11/root/bin/gcc /usr/bin/gcc[root@python1 servers]#mv /usr/bin/g++ /usr/bin/g++-4.8.5[root@python1 servers]# sudo ln -s /opt/rh/devtoolset-11/root/bin/g++ /usr/bin/g++原因:编译器确实是新版的,但是对应的头文件和库文件还都是原先老版本,一些新版的C++语法自然就不会支持。第一次安装mysql-8.0时就踩到了此坑c++: error: unrecognized command line option '-std=c++14'make: *** [all] Error 2
2.3 安装高版本cmake
Cmake需要3.5.1版本或则更高版本。静等编译安装完成
[root@python1 servers]# mkdir /usr/local/cmake && cd /usr/local/cmake
[root@python1 cmake]# wget https://github.com/Kitware/CMake/releases/download/v3.14.5/cmake-3.14.5.tar.Z
[root@python1 cmake]# tar -xf cmake-3.14.5.tar.Z
[root@python1 cmake]# cd cmake/cmake-3.14.5 && ./bootstrap
[root@python1 cmake-3.14.5]# make && make install
#安装完成并检查
[root@python1 cmake-3.14.5]# cmake --version
cmake version 3.14.5
CMake suite maintained and supported by Kitware (kitware.com/cmake).
至此,环境和依赖准备完毕,下一步开始编译部署
二、编译安装mysql
1.解压mysql-boost-8.0.16安装包
[root@python1 packages]# tar xf mysql-boost-8.0.16.tar.gz
2.执行编译命令
[root@python1 packages]# cd mysql-8.0.16
[root@python1 mysql-8.0.16]# cmake . -DCMAKE_INSTALL_PREFIX=/export/servers/app/mysql-8.0.16 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULTCOLLATION=utf8_bin \
-DEXTRA_CHARSETS=all \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DWITH_BOOST=/packages/mysql-8.0.16/boost/boost_1_69_0
以上参数解释和常用参数如下
-DCMAKE_INSTALL_PREFIX=/export/servers/app/mysql-8.0.16 指向mysql安装目录
-DMYSQL_DATADIR=/var/lib/mysql 指向mysql数据文件目录(/var/lib/mysql)
-DSYSCONFDIR=/etc/mysql 指向mysql配置文件目录(/etc/mysql)【默认/etc,可以不指派】
-DINSTALL_SBINDIR=sbin 指向可执行文件目录(prefix/sbin)
-DINSTALL_PLUGINDIR=lib/mysql/plugin 指向插件目录(prefix/lib/mysql/plugin)
-DINSTALL_MANDIR=share/man 指向man文档目录(prefix/share/man)
-DINSTALL_SHAREDIR=share 指向aclocal/mysql.m4安装目录(prefix/share)
-DINSTALL_LIBDIR=lib/mysql 指向对象代码库目录(prefix/lib/mysql)
-DINSTALL_INCLUDEDIR=include/mysql 指向头文件目录(prefix/include/mysql)
-DINSTALL_INFODIR=share/info 指向info文档存放目录(prefix/share/info)
(prefix官方推荐设为/usr)-DMYSQL_USER=mysql 指定mysql运行用户(默认为mysql)
-DMYSQL_TCP_PORT=3306 指定mysql监听的TCP端口为3306
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock 指定mysql.sock文件路径
-DENABLED_LOCAL_INFILE=1 启用本地数据导入支持
-DENABLE_PROFILING=0 禁用Profiling分析(默认为开启)
-DEXTRA_CHARSETS=all 启用所有的字符集类型(默认为all)
-DWITH_EXTRA_CHARSETS=all 支持额外的字符集
-DDEFAULT_CHARSET=utf8 指定默认的字符集为utf8
-DDEFAULT_COLLATION=utf8_general_ci 设定默认排序规则/校验字符集(utf8_general_ci快速/utf8_unicode_ci准确)
-DWITH_DEBUG=0 禁用debug调试(默认为禁用)
-DWITH_COMMENT='string' 一个关于编译环境的描述性注释
-DWITH_EMBEDDED_SERVER=1 编译嵌入式服务器支持
-DWITH_BOOST=/usr/local/boost boost拓展库的路径
-DSYSCONFDIR=/data/mysqldata/3306 配置文件my.cnf所在目录路径
-DWITH_BIG_TABLES=1 将临时表存储在磁盘上
踩坑点2
如果在执行cmake命令后,遇见以下报错的解决方法如下
报错:Please do not build in-source. Out-of source builds are highly recommended: you can have multiple builds for the same source, and there isan easy way to do cleanup, simply remove the build directory (note that'make clean' or 'make distclean' does *not* work)You *can* force in-source build by invoking cmake with-DFORCE_INSOURCE_BUILD=1
解决方法:
[root@python1 mysql-8.0.16]# cmake . -DCMAKE_INSTALL_PREFIX=/export/servers/app/mysql-8.0.16 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULTCOLLATION=utf8_bin \
-DEXTRA_CHARSETS=all \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DWITH_BOOST=/packages/mysql-8.0.16/boost/boost_1_69_0 \
-DFORCE_INSOURCE_BUILD=1 #将提示的这个参数也添加进去
3.执行make命令
根据服务器配置高低,执行时间也不一样
[root@python1 mysql-8.0.16]# make -j 4 #-j表示使用几个cpu同时编译,这个根据自身服务器的配置来写。
4.执行make install 命令
[root@python1 mysql-8.0.16]# make install
步骤三和步骤四我没遇到相关问题,就是大概得等20-30分钟左右,编译安装速度根据服务器情况而定
5.编译安装完成后结果检查
至此,mysql8.0.16版本编译安装完成,接下来,初始化8.0.16版本的数据库
三、初始化mysql
1. 创建数据相关目录及编辑my.cnf文件
创建数据存放目录、日志目录(存放错误日志、慢日志文件)、binlog日志目录等等
[root@python1 mysql-8.0.16]# mkdir -p /export/servers/data/my3306/{data,log,binlog,iblog,ibdata,tmp,run}
my.cnf配置文件如下所示。仅供参考,标注修改和注释的部分100%可以使用,其它参数的配置根据自身环境进行调整即可
[root@python1 mysql-8.0.16]# vim /export/servers/data/my3306/my.cnf
[client]
port = 3306 #修改
socket = /export/servers/data/my3306/run/mysqld.sock #修改[mysqld]
basedir = /export/servers/app/mysql-8.0.16 #修改
port = 3306 #修改
socket = /export/servers/data/my3306/run/mysqld.sock #修改
datadir = /export/servers/data/my3306/data #修改
pid_file = /export/servers/data/my3306/run/mysqld.pid #修改
bind_address = 0.0.0.0lower_case_table_names = 1
collation_server = utf8_bin
character_set_server = utf8skip_name_resolve
skip_external_locking
explicit_defaults_for_timestamp=1
log_bin_trust_function_creators = 1tmp_table_size = 24M
tmpdir = /export/servers/data/my3306/tmp #修改
back_log = 1024max_connections = 1000
#max_user_connections = 80
max_connect_errors = 2000table_open_cache = 20480
table_definition_cache = 20480
table_open_cache_instances = 16
open_files_limit = 655340
binlog_cache_size = 512K
bulk_insert_buffer_size = 64M
ft_min_word_len = 4
join_buffer_size = 6M
key_buffer_size = 32M
max_allowed_packet = 1024M
max_heap_table_size = 32M
myisam_repair_threads = 1
myisam_sort_buffer_size = 24M
read_buffer_size = 2M
read_rnd_buffer_size = 3M
sort_buffer_size = 4M
thread_cache_size = 512
thread_stack = 192K
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES# log
general_log = off
general_log_file = /export/servers/data/my3306/log/general-log.log
log_error_verbosity = 3
log_error = /export/servers/data/my3306/log/mysqld-err.log
log_timestamps = SYSTEM
slow_query_log = on
slow_query_log_file = /export/servers/data/my3306/log/mysqld-slow.log
long_query_time = 2
log_slow_admin_statements = on
log_slow_slave_statements = on
binlog_rows_query_log_events = on# replication
read_only=0
super_read_only=0
log_slave_updates
skip_slave_start
slave_compressed_protocol = off
transaction_write_set_extraction = off
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = ON
binlog_checksum = CRC32
#binlog_group_commit_sync_delay = 50
#binlog_group_commit_sync_no_delay_count =100binlog_format = row
binlog_row_image = full
max_binlog_size = 500M
expire_logs_days = 7
gtid_mode = on
enforce_gtid_consistency = on
gtid_executed_compression_period = 1000
log-bin = /export/servers/data/my3306/binlog/mysql-bin
relay_log = /export/servers/data/my3306/binlog/mysqld-relay-bin
master_info_repository = table
relay_log_info_repository = table
server-id = 1303306 #修改
slave_net_timeout = 60
#slave_checkpoint_group = 512
#slave_checkpoint_period = 300
slave_load_tmpdir = /export/servers/data/my3306/tmp
slave_parallel_workers = 4
slave_pending_jobs_size_max = 128M
sync_binlog = 1report_port = 3306
report_host = 192.168.56.129 #修改为自身ip或者注释掉即可default_storage_engine = InnoDB
disabled_storage_engines = "MyISAM,MEMORY"
# innodb
innodb_print_all_deadlocks
innodb_autoextend_increment = 64
innodb_buffer_pool_dump_at_shutdown = on
innodb_buffer_pool_load_at_startup = on
innodb_buffer_pool_instances = 4
innodb_buffer_pool_size = 4G #修改,占服务器内存的60% ~~70%即可
innodb_data_file_path = ibdata1:64M:autoextend
innodb_data_home_dir = /export/servers/data/my3306/ibdata
innodb_fast_shutdown = 1
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 0
#innodb_io_capacity = 3000
innodb_io_capacity_max = 1000
innodb_lock_wait_timeout = 50
innodb_log_buffer_size = 32M
innodb_log_file_size = 500M
innodb_log_files_in_group = 4
innodb_log_group_home_dir = /export/servers/data/my3306/iblog
innodb_max_dirty_pages_pct = 75
innodb_online_alter_log_max_size = 1342177280
innodb_open_files = 64535
innodb_purge_threads = 4
#innodb_read_io_threads = 16
innodb_sort_buffer_size = 4M
innodb_thread_concurrency = 0
innodb_undo_directory = /export/servers/data/my3306/iblog
#innodb_undo_logs = 128 #这个需要注释掉,因为mysql8,0版本已经取消掉了该参数
innodb_undo_tablespaces = 2
#innodb_write_io_threads = 16
#innodb_file_format = Barracuda #这个需要注释掉,因为mysql8,0版本已经取消掉了该参数
#innodb_file_format_max = Barracuda #这个需要注释掉,因为mysql8,0版本已经取消掉了该参数
transaction_isolation = READ-COMMITTED
secure_file_priv =# plugin
plugin_load = rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so
# Semi_sync
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_slave_enabled = 1
rpl_semi_sync_master_timeout = 1000
rpl_semi_sync_master_wait_point = AFTER_SYNC
rpl_semi_sync_master_wait_no_slave = off
rpl_semi_sync_master_wait_for_slave_count = 1[mysqldump]
quick
max_allowed_packet = 256M[mysql]
no-auto-rehash
prompt=\\u@\\v \\d \\r:\\m:\\s>
default-character-set=utf8
帮助手册: 给大家提供一个查询mysql不同版本之间的配置参数变化网站,该网站可以清晰的对比出你想要查询的mysql不同版本之间的参数变化
https://mysql-params.tmtms.net/variable/?vers=5.7.32,5.7.22,8.0.16&plugin=true
如下所示
2.创建用户并对相关目录授权
[root@python1 mysql-8.0.16]# /usr/sbin/groupadd myinstall
[root@python1 mysql-8.0.16]# /usr/sbin/groupadd dba
[root@python1 mysql-8.0.16]# /usr/sbin/useradd -c "Mysql software owner" -g myinstall -G myinstall,dba mysql -s /sbin/nologin
[root@python1 mysql-8.0.16]# chmod 755 /home/mysql && chown -R mysql:myinstall /export/servers/data/my3306 (对数据目录授权)
3.拷贝mysql3306.server文件并修改、授权
[root@python1 mysql-8.0.16]# cp /export/servers/data/my3306/support-files/mysql.server /etc/init.d/mysql3306.server
[root@python1 mysql-8.0.16]# vim /etc/init.d/mysql3306.server
sleep 1
user=mysql
basedir=/export/servers/app/mysql-8.0.16
bindir=$basedir/bin
datadir=/export/servers/data/my3306/data
mysql_data_basedir="/export/servers/data"
mycnf=$mysql_data_basedir/my3306/my.cnflockdir='/var/lock/subsys'
lock_file_path="$lockdir/$user"service_startup_timeout=900lsb_functions="/lib/lsb/init-functions"
if test -f $lsb_functions ; then. $lsb_functions
elselog_success_msg(){echo " SUCCESS! $@"}log_failure_msg(){echo " ERROR! $@"}
fiPATH="/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin"
export PATHmode=$1 # start or stopcase `echo "testing\c"`,`echo -n testing` in*c*,-n*) echo_n= echo_c= ;;*c*,*) echo_n=-n echo_c= ;;*) echo_n= echo_c='\c' ;;
esacwait_for_pid () {verb="$1" # created | removedpid="$2" # process ID of the program operating on the pid-filepid_file_path="$3" # path to the PID file.i=0avoid_race_condition="by checking again"while test $i -ne $service_startup_timeout ; docase "$verb" in'created')# wait for a PID-file to pop into existence.test -s "$pid_file_path" && i='' && break;;'removed')# wait for this PID-file to disappeartest ! -s "$pid_file_path" && i='' && break;;*)echo "wait_for_pid () usage: wait_for_pid created|removed pid pid_file_path"exit 1;;esac# if server isn't running, then pid-file will never be updatedif test -n "$pid"; thenif kill -0 "$pid" 2>/dev/null; then: # the server still runselse# The server may have exited between the last pid-file check and now.if test -n "$avoid_race_condition"; thenavoid_race_condition=""continue # Check again.fi# there's nothing that will affect the file.log_failure_msg "The server quit without updating PID file ($pid_file_path)."return 1 # not waiting any more.fifiecho $echo_n ".$echo_c"i=`expr $i + 1`sleep 1doneif test -z "$i" ; thenlog_success_msgreturn 0elselog_failure_msgreturn 1fi
}bin_print_defualt=$bindir/my_print_defaults
mysqld_pid_file_path=`$bin_print_defualt --defaults-file=$mycnf mysqld | grep pid | cut -d= -f2`
if [ -z $mysqld_pid_file_path ];
thendatadir=`$bin_print_defualt --defaults-file=$mycnf mysqld | grep datadir | cut -d= -f2`mysqld_pid_file_path=$datadir/$user.pid
ficase "$mode" in'start')# Start daemon# Safeguard (relative paths, core dumps..)cd $basedirecho $echo_n "Starting MySQL"if test -x $bindir/mysqld_safethen# Give extra arguments to mysqld with the my.cnf file. This script# may be overwritten at next upgrade.$bindir/mysqld_safe --defaults-file=$mycnf --user=$user >/dev/null 2>&1 &wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?# Make lock for RedHat / SuSEif test -w "$lockdir"thentouch "$lock_file_path"fiexit $return_valueelselog_failure_msg "Couldn't find MySQL server ($bindir/mysqld_safe)"fi;;'stop')# Stop daemon. We use a signal here to avoid having to know the# root password.if test -s "$mysqld_pid_file_path"thenmysqld_pid=`cat "$mysqld_pid_file_path"`if (kill -0 $mysqld_pid 2>/dev/null)thenecho $echo_n "Shutting down MySQL"$bindir/mysqladmin --defaults-file=$mycnf -uroot -p shutdown# mysqld should remove the pid file when it exits, so wait for it.wait_for_pid removed "$mysqld_pid" "$mysqld_pid_file_path"; return_value=$?elselog_failure_msg "MySQL server process #$mysqld_pid is not running!"rm "$mysqld_pid_file_path"fi# Delete lock for RedHat / SuSEif test -f "$lock_file_path"thenrm -f "$lock_file_path"fiexit $return_valueelselog_failure_msg "MySQL server PID file could not be found!"fi;;'restart')# Stop the service and regardless of whether it was# running or not, start it again.if $0 stop ; then$0 startelselog_failure_msg "Failed to stop running server, so refusing to try to start."exit 1fi;;'reload'|'force-reload')if test -s "$mysqld_pid_file_path" ; thenread mysqld_pid < "$mysqld_pid_file_path"kill -HUP $mysqld_pid && log_success_msg "Reloading service MySQL"touch "$mysqld_pid_file_path"elselog_failure_msg "MySQL PID file could not be found!"exit 1fi;;'status')# First, check to see if pid file existsif test -s "$mysqld_pid_file_path" ; thenread mysqld_pid < "$mysqld_pid_file_path"if kill -0 $mysqld_pid 2>/dev/null ; thenlog_success_msg "MySQL running ($mysqld_pid)"exit 0elselog_failure_msg "MySQL is not running, but PID file exists"exit 1fielse# Try to find appropriate mysqld processmysqld_pid=`pidof $libexecdir/mysqld`if test -z $mysqld_pid ; thenif test -f "$lock_file_path" ; thenlog_failure_msg "MySQL is not running, but lock file ($lock_file_path) exists"exit 2filog_failure_msg "MySQL is not running"exit 3elselog_failure_msg "MySQL is running but PID file could not be found"exit 4fifi;;*)# usagebasename=`basename "$0"`echo "Usage: $basename {start|stop|restart|reload|force-reload|status} [ MySQL server options ]"exit 1;;
esacexit 0
[root@python1 mysql-8.0.16]# chmod 755 /etc/init.d/mysql3306.server
4.设置自启动并进行初始化操作
[root@python1 mysql-8.0.16]# chkconfig mysql3306.server on
无密码初始化并启动mysql
[root@python1 mysql-8.0.16]# /export/servers/app/mysql-8.0.16/bin/mysqld --defaults-file=/export/servers/data/my3306/my.cnf --user=mysql --initialize-insecure[root@python1 mysql-8.0.16]# /etc/init.d/mysql3306.server start
踩坑3
如果在启动mysql时报如下错误:Starting MySQL. ERROR! The server quit without updating PID file (/var/lib/mysql/localhost.localdomain.pid).排查方法:1、查看mysql的log目录中的mysqld-err.log日志文件2、查看文件中的ERROR关键错误,大部分情况是因为配置文件参数不符合mysql8.0版本,按照提示注释掉即可3、如果不是配置文件问题,则检查数据目录权限及my.cnf文件权限
更改root密码并创建普通用户并授权
#登录mysql
[root@python1 mysql-8.0.16]# /export/servers/app/mysql-8.0.16/bin/mysql -uroot -S /export/servers/data/my3306/run/mysqld.sock
#设置权限
mysql> set global super_read_only=0;
mysql> set global read_only=0;
注意事项: mysql8.0版本已经不支持password() 函数了,因此不能使用老方法update user set authentication_string=password('123456') where user='root';更改密码,会报语法错误
#设置root账密
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxxx';
注意事项: 新版本的 MySQL 8.x 版本已经将创建账户和赋权的方式分开导致以上的命令在 MySQL 8.x 上执行报语法错误。因此不能在使用RANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;语句来完成此操作
#创建普通用户
# 创建账户
mysql> CREATE USER '用户名'@'访问主机' IDENTIFIED BY '密码';# 为创建的账户赋权
mysql> GRANT '权限列表' ON '数据库' TO '用户名'@'访问主机';
mysql> GRANT ALL ON *.* TO 'root'@'%';# 刷新
FLUSH PRIVILEGES;
至此,初始化mysql8.0操作完成
四、管理mysql
1.配置systemd管理mysql
[root@python1 mysql-8.0.16]# mysql_softdir=/export/servers/app
[root@python1 mysql-8.0.16]# mysql_cnf=/export/servers/data/my3306/run/my.cnf
[root@python1 mysql-8.0.16]# mysql_port=3306
[root@python1 mysql-8.0.16]# cat <<EOF >/etc/systemd/system/mysql3306.service
[Unit]
Description=Deamon of mysql 3306
After=syslog.target[Service]
LimitNOFILE=102400Type=forking
ExecStart=/etc/init.d/mysql3306.server start
ExecStop=$mysql_softdir/mysql-8.0.16/bin/mysqladmin --defaults-file=$mysql_cnf -uroot shutdown
ExecReload=/etc/init.d/mysql$mysql_port.server restart
Restart=always[Install]
WantedBy=multi-user.target
EOF
[root@python1 mysql-8.0.16]# chmod 755 /etc/systemd/system/mysql3306.service
[root@python1 mysql-8.0.16]# systemctl daemon-reload
[root@python1 mysql-8.0.16]# systemctl restart mysql3306.service
检查是否生效
2.添加快捷命令至profile文件中
[root@python1 mysql-8.0.16]# vim /etc/profile
...
export MYSQL_HOME=/export/servers/app/mysql-8.0.16
export PATH=$MYSQL_HOME/bin:$PATH
[root@python1 mysql-8.0.16]# source /etc/profile #加载配置
五、mysql8.0使用验证
1.使用初始化时配置的root密码登录验证
2. 创建数据库验证
#测试在mysql8.0中创建一个test库
[root@python1 mysql-8.0.16]# mysql -uroot -p -S /export/servers/data/my3306/run/mysqld.sock
mysql> create database ops;
Query OK, 1 row affected (0.01 sec)
3.查看数据库用户及引擎验证
mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
大体功能使用已验证没有问题,其他可能存在部分语法差异问题,在使用时注意修改即可,至此,mysql-8.0.16通过源码部署安装完成
踩坑四
当时编译安装过程中有一个报错,但是没有留存报错问题,解决方法是需要打开swap空间,到时自行留意相关报错
总结
本篇文章,从安装mysql-8.0.16源码开始,经历两遍部署成功,其中,有两个错误当时未保留记录,如果后续我有遇到则会更新到此篇博客中。通过源码部署的方式,更加了解了数据库8.0版本的部署过程,以及语法方面的差异,因此,完成部署仅仅只是一个开端,后续快马加鞭学习8.0相关的操作,强化自身!!!源码部署过程根据服务器自身硬件资源,资源小,会很慢,有时也会有其他报错,建议搞个4c8g服务器。