MySQL 数据库安装
Bitgeek 2018-12-15 tag1
# MySQL 数据库安装
# MySQL 8.0安装
百度云盘:
链接: https://pan.baidu.com/s/11lURu0nMOepXJluwpjTkpA 提取码: en4w
进入 mysql解压目录 /bin 目录中 按照下面的代码中执行 ./mysqld -install 后面会提示临时密码: !OfS1jIL3+e. 重要的事情说三遍: !OfS1jIL3+e(随机的,下面会用到) !OfS1jIL3+e(随机的,下面会用到) !OfS1jIL3+e(随机的,下面会用到) !OfS1jIL3+e(随机的,下面会用到)
./mysqld --initialize --user=mysql --console
./mysqld -install
net start mysql
mysql -uroot -p!OfS1jIL3+e. #这里就是临时的密码
set password = 'qhdswchzh';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'qhdswchzh';
FLUSH PRIVILEGES; #刷新权限
常见提示错误:
问题1: mysql Install/Remove of the Service Denied! 解决办法: cmd 管理员运行.
问题2:Navicat for MySQL 连接 Mysql 8.0.11 出现1251- Client does not support authentication protocol 错误
解决办法: 修改mysql 默认 密码规则
系统根目录增加: my.ini
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
basedir = "G:\mysql-8.0.16-winx64"
datadir = "G:\mysql-8.0.16-winx64\data\"
port = 3306
max_connections=1000
max_user_connections=500
wait_timeout=200
character-set-server=utf8
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#character-set-server=UTF-8
default-storage-engine=INNODB
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#忘记密码时使用
#skip-grant-tables
#设置协议认证方式(重点啊)
default_authentication_plugin=mysql_native_password
[mysql]
default-character-set=utf8
MYSQL 优化相关
一、mysql wait_timeout 时间优化
mysql> show variables like ‘%timeout%’;
set interactive_timeout=31536000;
set wait_timeout=31536000;
也就是说wait_timeout的最大值只允许2147483 （24天左右）
也可以修改my.cof,修改后重起mysql
打开/etc/my.cnf,在属性组mysqld下面添加参数如下：
[mysqld]
interactive_timeout=31536000
wait_timeout=31536000
二、用户创建 赋予权限
create user ‘root’@’%’ identified by ‘root’;
GRANT ALL PRIVILEGES ON www5.* TO root@"%" identified by “root”;
flush privileges;
三、自动备份mysql数据库 需要把下面得代码改成 autoback.bat 文件
@echo off
set errorlevel=0
set path_home_mysql=“D:\Program Files\MySQL\MySQL Server 5.1”
set path_bin_mysql=%path_home_mysql%bin\
set backup_dir=“E:\backup”
set backup_file=%backup_dir%mysql_backup_%date:~0,10%.sql
set database_mysql=budget
set user_mysql=root
set password_mysql=******
dir %backup_dir% || echo make dir &mkdir %backup_dir%
IF EXIST %backup_file% ( del %backup_file% &echo %backup_file% is deleted)
echo backup mysql database:%database_mysql%
%path_bin_mysql%mysqldump -u%user_mysql% -p%password_mysql% %database_mysql% > %backup_file%
echo end backup
四、 mysql 导出数据指定字符集
mysql -uroot -pqhdswchzh --default-character-set=GBK wlwzpt </wlwzpt.sql
五、修改log_bin_trust_function_creators
SET GLOBAL log_bin_trust_function_creators = 1;
六、慢查询
定义超过多少秒的查询算是慢查询，我这里定义的是2秒
long_query_time = 2
//5.0、5.1等版本配置如下选项
定义慢查询日志的路径
log-slow-queries = E:/mysqldb/mysqlslowquery.log
//5.5及以上版本配置如下选项
slow_query_log = ON
slow_query_log_file= E:/mysqldb/slow_query_log.txt
七、 Table ‘sys_dept’ was not locked with LOCK TABLES 的问题的解决
原因： 表sys_dept被锁
解决办法 ： UNLOCK TABLES; （释放锁即可 释放的是所有的表的锁）