MySQL密码更改
查看MySQL服务是否启动，如果没有启动使用/etc/init.d/mysqld start启动
[root@test-a ~]# ps aux | grep mysql
root 2180 0.0 0.1 115432 1724 ? S 07:33 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/usr/local/mysql/mysqld.pid
mysql 2390 0.9 17.8 1117648 180356 ? Sl 07:33 0:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/db.err --pid-file=/usr/local/mysql/mysqld.pid --socket=/usr/local/mysql/mysql.sock --port=3306
root 2490 0.0 0.0 112704 972 pts/0 R+ 07:35 0:00 grep --color=auto mysql
登录MySQL，改密码
[root@test-a ~]# mysql # 没有找到该命令，因为没有为MySQL设置环境变量
-bash: mysql: command not found
[root@test-a ~]# export PATH=$PATH:/usr/local/mysql/bin/
[root@test-a ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.23
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
[root@test-a ~]# mysqladmin -uroot -p'test111' password test222 #更改密码
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists!
# 出现这个错误，网上基本都是说没有把对应的mysql.sock放到/tmp目录下，但是我的配置里是/usr/local/mysql/mysql.sock
[root@test-a ~]# cat /etc/my.cnf
[mysql]
socket = /usr/local/mysql/mysql.sock
# The MySQL server
[mysqld]
port = 3306
socket = /usr/local/mysql/mysql.sock
#skip-grant-tables
datadir=/data/mysql
log-error=/usr/local/mysql/db.err
pid-file=/usr/local/mysql/mysqld.pid
character-set-server = utf8
# 觉得可能是配置文件的问题，就去找差异，发现自己没有client配置,重新配置，解决问题
[root@test-a ~]# vim /etc/my.cnf
[root@test-a ~]# cat /etc/my.cnf
[client]
socket = /usr/local/mysql/mysql.sock
# The MySQL server
[mysqld]
port = 3306
socket = /usr/local/mysql/mysql.sock
#skip-grant-tables
datadir=/data/mysql
log-error=/usr/local/mysql/db.err
pid-file=/usr/local/mysql/mysqld.pid
character-set-server = utf8
[mysql.server]
basedir=/usr/local/mysql
[root@test-a ~]# mysqladmin -uroot -p'test111' password test222
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
MySQL忘记root用户的密码重置
主要用到的是skip-grant-tables，在配置文件中配置该字段，重启
[root@test-a ~]# vim /etc/my.cnf
[root@test-a ~]# cat /etc/my.cnf
[client]
socket = /usr/local/mysql/mysql.sock
# The MySQL server
[mysqld]
port = 3306
socket = /usr/local/mysql/mysql.sock
skip-grant-tables
datadir=/data/mysql
log-error=/usr/local/mysql/db.err
pid-file=/usr/local/mysql/mysqld.pid
character-set-server = utf8
[mysql.server]
basedir=/usr/local/mysql
[root@test-a ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
[root@test-a ~]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> update user set authentication_string=password('test111') where user='root'; # MySQL5.7之后是authentication_string，如果之前则是password字段
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
# 或者直接使用下面的语句
mysql> alter user 'root'@'localhost' identified by 'test111';
MySQL连接方式
- mysql -uroot -p'test222' # 直接输入密码连接
- mysql -uroot -p'test111' -h127.0.0.1 -P3306 # 远程连接方式
- mysql -uroot -p'test111' -S/usr/local/mysql/mysql.sock # socket连接， 只支持本地连接
- mysql -uroot -p'test111' -e "show databases" # 连接之后通过-e执行命令
MySQL常用命令
- 列出所有库: show databases;
- 切换库: use mysql;
- 列出库里的表: show tables;
- 查看表里的字段: desc user;
- 查看表创建的语句: show create table user\G # \G将查到的结果纵向显示
- 查看当前用户: select user(); # 结果是"用户名@主机名"
- 查看当前使用的数据库: select database();
- 创建库: create database test_db0;
- 创建表: create table test_tb0(`id` int(4));
- 删除表: drop table test_tb0;
- 查看数据库版本: select version();
- 查看数据库状态: show status;
- 查看参数: show variables; 可以模糊匹配 show variables like 'max_connect%'; #这些参数都可以在my.cnf中定义
- 修改参数 set global max_connect_errors=1000; # 更改只在内存中生效
- 查看队列 show processlist; show full processlist;