目录
1. 设置、更改 root 密码
2. 连接 MySQL
3. MySQL 常用命令
4. MySQL 用户管理
5. MySQL 常用语句
6. MySQL 的备份与恢复
1. 设置、更改 root 密码
1.1 将 mysql 的 bin 目录加入 PATH
[root@LNMP ~]# vim .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin:/usr/local/nginx/sbin:/usr/local/mysql/bin
export PATH
[root@LNMP ~]# source .bash_profile
1.2 测试是否需要密码
[root@LNMP ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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>
1.3 设置密码
[root@LNMP ~]# mysqladmin -uroot password '123123';
Warning: Using a password on the command line interface can be insecure.
[root@LNMP ~]# mysql -uroot -p123123
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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>
1.4 改密
[root@LNMP ~]# mysqladmin -uroot -p'123123' password '456456'
Warning: Using a password on the command line interface can be insecure.
[root@LNMP ~]# mysql -uroot -p456456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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>
1.5 当忘记root密码
1.5.1 修改 my.conf,加入 skip-grant
[root@LNMP ~]# vim /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
skip-grant # 加入该行
basedir = /usr/local/mysql
datadir = /data/mysql
port = 3306
socket = /tmp/mysql.sock
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
1.5.2 重启 mysqld
[root@LNMP ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
1.5.3 免密登录
[root@LNMP ~]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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> # 成功登录
1.5.4 用 password 函数,修改数据库中存储的 root 密码
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set password=password('123123') where user='root';
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4 Changed: 4 Warnings: 0
1.5.5 别忘了将 my.conf 中的 skip-grant 去除,并重启
2. 连接 MySQL
2.1 普通连接
mysql -uroot -p123123
2.2 远程连接
mysql -uroot -p123123 -h192.168.1.1 -P3306
2.3 本地 socket 连接
mysql -uroot -p123123 -S/tmp/mysql.sock
2.4 直接在 Linux 命令行中执行 SQL 语句
mysql -uroot -p123123 -e "show databases"
3. MySQL 常用命令
- 查库
show databases;
- 切库
use mysql; - 查表
show tables;
- 查字段
desc tb_name;
- 查建表语句
show create table tb_name\G; ## \G 代表竖排显示
- 查当前用户
select user();
- 查当前数据库
select databsase();
- 建库
create database db1;
- 建表
use db1; create table t1(
idint(4),
namechar(40));
use db1; create table t1(
idint(4),
namechar(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- 查当前 mysql 版本
select version();
- 查数据库状态
show status;
- 查各种参数
show variables; show variables like 'max_connect%';
- 改参数
set global max_connect_errors=1000; # 仅为临时修改,若要永久修改需配置 my.conf 文件
- 查队列
show processlist;
show full processlist;
4. MySQL 用户管理
grant all on *.* to 'user1' identified by 'passwd';
grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';
grant all on db1.* to 'user3'@'%' identified by 'passwd';
show grants;
show grants for user2@192.168.133.1;
5. MySQL 常用语句
select count(*) from mysql.user;
select * from mysql.db;
select db from mysql.db;
select db,user from mysql.db;
select * from mysql.db where host like ‘192.168.%’;
insert into db1.t1 values (1, ‘abc’);
update db1.t1 set name=’aaa’ where id=1;
truncate table db1.t1;
drop table db1.t1;
drop database db1;
6. MySQL 的备份与恢复
- 备份库
mysqldump -uroot -p123456 mysql > /tmp/mysql.sql
- 恢复库
mysql -uroot -p123456 mysql < /tmp/mysql.sql
- 备份表
mysqldump -uroot -p123456 mysql user > /tmp/user.sql
- 恢复表
mysql -uroot -p123456 mysql < /tmp/user.sql
- 备份所有库
mysqldump -uroot -p -A >/tmp/123.sql
- 只备份表结构
mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql