1.1 Linux下安装mysql
mysql国内镜像地址:http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-8.0/ mysql官网地址:https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz
第一步:下载mysql安装包,也可以通过本地xftp上传到虚拟机
--跳转到下载目录
cd /usr/local
--下载mysql8.0的版本 wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz
第二步:将下载的mysql安装包解压
--解压
tar -xvf mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz
--移动
mv mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz mysql
--添加mysql组和mysql用户
--添加mysql组
groupadd mysql
--添加mysql用户
useradd -r -g mysql mysql
第四步:配置mysql配置文件与初始化mysql
--给mysql组/usr/local/mysql文件权限
chown -R mysql:mysql /usr/local/mysql
初始化mysql服务
./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
chown -R root:root /usr/local/mysql
chown -R mysql:mysql /usr/local/mysql/data
--修改/etc/my.cnf配置文件权限
chmod 755 /etc/my.cnf
以下是配置文件内容:
[mysql] default-character-set=utf8 [client] port=3306 default-character-set=utf8 socket=/usr/local/mysql/tmp/mysql.sock [mysqld] socket=/usr/local/mysql/tmp/mysql.sock port=3306 default_authentication_plugin=mysql_native_password basedir=/usr/local/mysql datadir=/usr/local/mysql/data character-set-server=utf8 default-storage-engine=InnoDb max_connections=1000 collation-server=utf8_unicode_ci init_connect='SET NAMES utf8' innodb_buffer_pool_size=64M innodb_flush_log_at_trx_commit=1 innodb_lock_wait_timeout=120 innodb_log_buffer_size=4M innodb_log_file_size=256M interactive_timeout=120 join_buffer_size=2M key_buffer_size=32M log-error=/usr/local/mysql/data/data.err log_error_verbosity=1 max_allowed_packet=256M max_heap_table_size=64M myisam_max_sort_file_size=64G myisam_sort_buffer_size=32M read_buffer_size=512kb read_rnd_buffer_size=4M log_bin=mysql-bin server-id=1 skip-external-locking=on sort_buffer_size=256kb table_open_cache=256 thread_cache_size=16 tmp_table_size=64M wait_timeout=2288800 general_log=ON general_log_file=/usr/local/mysql/data/query_log.pid slow_query_log=ON slow_query_log_file=sql-slow.log long_query_time=1 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
创建mysql.sock存储文件tmp
mkdir tmp
chmod 777 tmp
第五步:加入开机自启
--将mysql服务添加到/etc/init.d
cp support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
--添加服务
chkconfig --add mysql
--查看是否添加成功
chkconfig --list mysql
第六步:开启mysql服务并配置环境变量
--启动mysql服务
service mysql start
vi /etc/profile
profile中填写内容:
export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib
保存退出之后,运行命令:source /etc/profile
第七步:登录mysql,修改密码
mysql -uroot -p
--修改密码
alter user 'root'@'localhost' identified by 'root';
查看用户连接
添加新用户
用新创建的用户去客户端连接服务器
1.2 Navicat连接mysql
因为安装的mysql是最新的mysql8.0,他对于Navicat,mycat等工具去连接mysql会出现一个异常,这个异常是因为mysql8的密码加密方式是属于全新的加密方式,而目前的Navicat版本尚不支持这个加密方式,错误异常如下:
解决办法
- 更新Navicat客户端
- 设置mysql用户密码加密方式,具体如下:
`
mysql> select user,host,plugin from mysql.user; +------------------+-----------+-----------------------+ | user | host | plugin | +------------------+-----------+-----------------------+ | mytest| % | caching_sha2_password | | mysql.infoschema | localhost | caching_sha2_password | | mysql.session | localhost | caching_sha2_password | | mysql.sys | localhost | caching_sha2_password | | root | localhost | caching_sha2_password | +------------------+-----------+-----------------------+ 5 rows in set (0.00 sec)
mysql> alter user mytest
@%
identified with mysql_native_password by 'root'; Query OK, 0 rows affected (0.05 sec) mysql> select user,host,plugin from mysql.user; +------------------+-----------+-----------------------+ | user | host | plugin | +------------------+-----------+-----------------------+ | starsky | % | mysql_native_password | | mysql.infoschema | localhost | caching_sha2_password | | mysql.session | localhost | caching_sha2_password | | mysql.sys | localhost | caching_sha2_password | | root | localhost | caching_sha2_password | +------------------+-----------+-----------------------+ 5 rows in set (0.01 sec)
将用户加密方式由原本的caching_sha2_password方式更改为mysql_native_password;