mysql初始化

mysqld -nt -remove mysql

mysqld-nt -install

mysqld --initialize-insecure --user mysql

 

mysqld --install mysql

root用户拒绝连接

创建my.ini放在bin下

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\Mylargeprogram\Mysql\mysql-8.0.12-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\Mylargeprogram\Mysql\mysql-8.0.12-winx64\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

启动mysqld --console查看启动日志

登录root账号mysql -uroot(设置mysqld --initialize-insecure --user mysql后是这样登录的)

 

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;

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

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password by '123456';

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password;

select host from user where user='root';查看允许连接的主机

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

FLUSH PRIVILEGES;

添加用户

CREATE USER 'prod_zdsb'@'localhost' IDENTIFIED BY 'ProdZdsb_2019';

添加访问数据库权限

grant all privileges on prod_zdsb.* to 'prod_zdsb'@'localhost'
revoke all on *.* from prod_zdsb@localhost;

添加权限

ALL: 所有可用的权限
CREATE: 创建库、表和索引
LOCK_TABLES: 锁定表
ALTER: 修改表
DELETE: 删除表
INSERT: 插入表或列
SELECT: 检索表或列的数据
CREATE_VIEW: 创建视图
SHOW_DATABASES: 列出数据库
DROP: 删除库、表和视图

GRANT all ON *.* TO 'prod_zdsb'@'%';

//添加数据库权限

update user set host = 'localhost' where user ='root'
FLUSH PRIVILEGES;

查看锁

//1.查看当前数据库锁表的情况
SELECT * FROM information_schema.INNODB_TRX;
//2.杀掉查询结果中锁表的trx_mysql_thread_id
kill trx_mysql_thread_id
1、查询是否锁表
show OPEN TABLES where In_use > 0;
2、查询进程
show processlist
查询到相对应的进程===然后 kill id
补充：
查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

使用ip登录不了需要修改

use mysql;
select host,user from user;
update user set host='%' where user='prod_zdsb';
flush privileges;

查看mysql操作日志

 

忘记密码修改密码方法

关闭服务后运行：mysqld --console --skip-grant-tables --shared-memory

重新打开一个cmd mysql进入修改密码：update mysql.user set authentication_string=password('root') where user='root' and Host ='localhost';

show variables like '%general_log%';
set global general_log = on;
show variables like 'log_output';
set global log_output='table';
set global log_output='FILE';
select * from mysql.general_log;

 

备份数据

mysqldump -u root -p prod_zdsb>prod_zdsb.sql

恢复source prod_zdsb.sql；

或者mysql -u root -p prod_zdsb<prod_zdsb.sql