MYSQL的常规操作

密码重置

[root@localhost ~]# netstat -ntpl
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1501/sshd           
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      2173/master         
tcp6       0      0 :::3306                 :::*                    LISTEN      23151/mysqld        
tcp6       0      0 :::22                   :::*                    LISTEN      1501/sshd           
tcp6       0      0 ::1:25                  :::*                    LISTEN      2173/master

[root@localhost ~]# mysql -uroot -p000000 （如果报错，就重置密码）
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)

[root@localhost ~]# vi /etc/my.cnf 修改配置文件
添加 skip-grant

[root@localhost ~]# service mysqld restart
Shutting down MySQL… SUCCESS!
Starting MySQL.sed: -e expression #1, char 6: unknown option to `s’
SUCCESS!
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.47 MySQL Community Server (GPL)

Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MySQL [(none)]> 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
MySQL [mysql]> update user set password=password(‘000000’) where user=‘root’;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
MySQL [mysql]> Bye
[root@localhost ~]# vi /etc/my.cnf （修改配置文件，把添加的去掉）
[root@localhost ~]# service mysqld restart 重启
Shutting down MySQL… SUCCESS!
Starting MySQL.sed: -e expression #1, char 6: unknown option to `s’
SUCCESS!
[root@localhost ~]# mysql -uroot -p000000 （就可以进入了）

修改密码
方法一：set password for 用户名@localhost = password('新密码’);
[root@localhost ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.47 MySQL Community Server (GPL)

Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MySQL [(none)]> set password for root@localhost = password(‘123456’);
Query OK, 0 rows affected (0.00 sec)

方法二：mysqladmin -u用户名 -p旧密码 password 新密码
[root@localhost ~]# mysqladmin -uroot -p123456 password 000000

更改root密码
/usr/local/mysql/bin/mysql -uroot

MySQL [(none)]> grant all privileges on . to root@’%’ identified by ‘000000’; 授权

连接MySQL

本地登录
mysql -uroot -p000000

远程登陆
mysql -uroot -p123456 -h127.0.0.1 -P3306

指定到/tmp/mysql.sock目录下
mysql -uroot -p123456 -S/tmp/mysql.sock

查看数据库中的表
mysql -uroot -p123456 -e “show databases”
[root@jing ~]# mysql -uroot -p000000 -h192.168.200.90 -P3306 -e ‘show databases;’ （p为大写，端口号）
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
±-------------------+
相当于进入数据库查看
① [root@jing ~]# mysql -uroot -p000000 -h192.168.200.90 -P3306
MySQL [(none)]> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
±-------------------+
4 rows in set (0.01 sec)
② [root@localhost ~]# mysql -uroot -p000000 -e 'show databases;'
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
±-------------------+

MySQL常用命令

[root@localhost ~]# mysql -uroot -p000000
MySQL [(none)]> select version(); 查看当前数据库版本
±----------+
| version() |
±----------+
| 5.6.47 |
±----------+
1 row in set (0.00 sec)

show status; 查看数据库状态

MySQL [(none)]> select sysdate(); 查看当前系统时间
±--------------------+
| sysdate() |
±--------------------+
| 2020-11-26 21:30:26 |
±--------------------+
1 row in set (0.01 sec)
MySQL [(none)]> select user(); 查看当前用户
±---------------+
| user() |
±---------------+
| root@localhost |
±---------------+
1 row in set (0.01 sec)

MySQL [(none)]> select current_date(); 查看当前日期
±---------------+
| current_date() |
±---------------+
| 2020-11-26 |
±---------------+
1 row in set (0.01 sec)

MySQL [(none)]> select current_time(); 查看当前时间
±---------------+
| current_time() |
±---------------+
| 21:35:21 |
±---------------+
1 row in set (0.00 sec)

MySQL [(none)]>  show processlist;             查看队列
MySQL [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host      | db   | Command | Time | State | Info                  |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 32 | root | localhost | NULL | Query   |    0 | init  | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

[root@localhost ~]# mysql -uroot -p000000
MySQL [(none)]> show databases; 查询库
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
±-------------------+
4 rows in set (0.00 sec)

MySQL [(none)]> 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 [mysql]> show tables;                查看库里的表
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
28 rows in set (0.00 sec)

查看表里的字段 desc tb_name;
MySQL [mysql]> desc user;
±-----------------------±----------------------------------±-----±----±----------------------±------+
| Field | Type | Null | Key | Default | Extra |
±-----------------------±----------------------------------±-----±----±----------------------±------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |

查看建表语句 show create table tb_name\G;
MySQL [mysql]> show create table user \G 查看建表语句
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE user (
Host char(60) COLLATE utf8_bin NOT NULL DEFAULT ‘’,
User char(16) COLLATE utf8_bin NOT NULL DEFAULT ‘’,
Password char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT ‘’,

MySQL [mysql]> select database(); 查看当前使用的数据库
±-----------+
| database() |
±-----------+
| mysql |
±-----------+
1 row in set (0.00 sec)

创建库 create database db1;
MySQL [mysql]> create database wordpress;
Query OK, 1 row affected (0.01 sec)

创建表 use db1; create table t1(id int(4), name char(40));
MySQL [wordpress]> create table yunyunwei(
-> name char(10)，
-> id int(2));

show variables;           查看各参数
show variables like 'max_connect%';

修改参数 set global max_connect_errors=1000;

MySQL创建用户以及授权

grant all privileges on . to ‘user1’@‘localhost’ identified by ‘passwd’;

grant SELECT,UPDATE,INSERT on db1.* to ‘user2’@‘192.168.133.1’ identified by ‘passwd’;

grant all privileges on db1.* to ‘user3’@’%’ identified by ‘passwd’;

show grants; 列出权限
show grants for user2@192.168.133.1; 查看用户权限

revoke select on wordpress.* from ‘user’@localhost identified by ‘123456’; //撤销权限

常用SQL语句

select count(*) from mysql.user; //查询表内容
select * from mysql.db;

select db from mysql.db;
MySQL [wordpress]> select * from yunyunwei;
±--------±-----+
| name | id |
±--------±-----+
| liufeng | 10 |
| shang | 1 |
±--------±-----+
2 rows in set (0.00 sec)

select db,user from mysql.db;

select * from mysql.db where host like ‘192.168.%’;
MySQL [wordpress]> select name from yunyunwei where name like ‘sha%’;
±------+
| name |
±------+
| shang |
±------+
1 row in set (0.01 sec)

insert into db1.t1 values (1, ‘abc’); //插入表内容
MySQL [wordpress]> insert into yunyunwei values(‘shang’,1);
Query OK, 1 row affected (0.01 sec)

update db1.t1 set name=‘aaa’ where id=1; 修改表内容

truncate table db1.t1; 清空表内容
MySQL [wordpress]> truncate table yunyunwei;
Query OK, 1 row affected (0.01 sec)

drop table db1.t1; //删除表
drop database db1; //删除数据库

MySQL数据库的备份与恢复

[root@localhost ~]# mysqldump -uroot -p000000 mysql > /tmp/mysql.sql             备份库
[root@localhost ~]# ll /tmp
total 688
-rwx------. 1 root root    827 Nov 26 16:22 ks-script-vIOesr
-rw-r--r--. 1 root root 698855 Nov 26 23:20 mysql.sql
-rw-------. 1 root root      0 Nov 26 16:16 yum.log
[root@localhost ~]# cd /tmp
[root@localhost tmp]# du -h mysql.sql
684K    mysql.sql

创建库 mysql -uroot -p000000 -e “create database mysqltest;”
MySQL [mysqltest]> show tables;

恢复库 mysql -uroot -p000000 mysqltest < /tmp/mysql.sql

[root@localhost ~]# mysqldump -uroot -p000000 mysqltest user > /tmp/user.sql 备份表
[root@localhost ~]# mysql -uroot -p000000 -e “create database mysqltestaaa;”
[root@localhost ~]# mysqldump -uroot -p000000 mysqltestaaa < /tmp/user.sql
–MySQL dump 10.14 Distrib 5.5.68-MariaDB, for Linux (x86_64)

– Host: localhost Database: mysqltestaaa

– Server version 5.6.47

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 /;
/!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;
/!40103 SET TIME_ZONE=’+00:00’ /;
/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO’ /;
/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 /;
/!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /;
/!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

– Dump completed on 2020-11-10 5:45:48

恢复表 mysql -uroot -p123456 mysqltest < /tmp/user.sql

[root@localhost tmp]# mysqldump -uroot -p000000 -d mysql > /tmp/xxx.sql               只备份表结构
[root@localhost tmp]# mysql -uroot -p000000 -e "create database xxx;"
[root@localhost tmp]# mysql -uroot -p000000 xxx < /tmp/xxx.sql
[root@localhost tmp]# mysql -uroot -p000000

备份所有库 mysqldump -uroot -p 123456 -A >/tmp/123.sql
-A :所有数据库

[root@localhost ~]# mysqldump -uroot -p000000 -A > /tmp/123.sql 
[root@localhost ~]# ll /tmp/
total 2072
-rw-r--r--. 1 root root 1402117 Nov  6 23:00 123.sql
-rwx------. 1 root root     827 Nov  6 16:14 ks-script-2S5TUq
-rw-r--r--. 1 root root  700404 Nov  6 22:30 mysql.sql
-rw-r--r--. 1 root root    7133 Nov  6 22:47 user.sql
-rw-------. 1 root root       0 Nov  6 16:06 yum.log
-rw-r--r--. 1 root root    1775 Nov  6 22:51 yu.sql