进入mysql
1、只需要密码和用户登录
2、远程登录mysql
3、配置/etc/my.cnf 文件时内容有socket = /tmp/mysql.sock时，就要用指定-S 并且要指定端口，进入数据库，是socket = /var/lib/mysql/mysql.sock 时（如下图），就不用指定 -S。
4、不进入数据库，在数据库内使用命令
mysql的常用命令
一、关于查询
1、
例如：
MySQL [(none)]> select version();
+-----------+
| version() |
+-----------+
| 5.6.47 |
+-----------+
1 row in set (0.00 sec)
2、
显示有点多
3、
例如：
MySQL [(none)]> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2020-11-06 19:19:29 |
+---------------------+
1 row in set (0.00 sec)
4、
例如：
MySQL [(none)]> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
5、
例如：
MySQL [(none)]> select current_date();
+----------------+
| current_date() |
+----------------+
| 2020-11-06 |
+----------------+
1 row in set (0.00 sec)
6、
例如：
MySQL [(none)]> select current_time();
+----------------+
| current_time() |
+----------------+
| 19:26:13 |
+----------------+
1 row in set (0.00 sec)
7、
例如：
MySQL [(none)]> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 3 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
MySQL [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 3 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
二、关于库和表
1、
例如
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
2、
例如
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
3、
例如
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)
4、
例如：
MySQL [haha]> desc yu;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name | char(4) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
5、
tb_name是表名
MySQL [haha]> show create table yu\G
*************************** 1. row ***************************
Table: yu
Create Table: CREATE TABLE `yu` (
`name` char(4) DEFAULT NULL,
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
6、
MySQL [haha]> select database();
+------------+
| database() |
+------------+
| haha |
+------------+
1 row in set (0.00 sec)
7、
例如：
MySQL [(none)]> create database haha;
Query OK, 1 row affected (0.00 sec)
8、
MySQL [(none)]> use haha;
Database changed
MySQL [haha]> create table yuyu(name char(4),id int(4));
Query OK, 0 rows affected (0.05 sec)
MySQL [haha]>
9、
MySQL [(none)]> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.00 sec)
10、**********
mysql创建用户以及授权
1、创建一个user在localhost给所有数据库，所有表，并设置密码为
例如：
MySQL [haha]> grant all privileges on *.* to 'user'@'localhost' identified by '000000';
Query OK, 0 rows affected (0.00 sec)
2、
例如:
MySQL [haha]> grant select,update,insert on haha.* to 'user2'@'192.168.200.70'identified by '000000';
Query OK, 0 rows affected (0.00 sec)
3、
例如：
MySQL [haha]> grant all privileges on haha.* to 'user2'@'%' identified by '000000';
Query OK, 0 rows affected (0.01 sec)
4、查看所有权限
例如：
MySQL [haha]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
5、
例如：
MySQL [haha]> show grants for user2@192.168.200.70;
+-------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.200.70 |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.200.70' IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693' |
| GRANT SELECT, INSERT, UPDATE ON `haha`.* TO 'user2'@'192.168.200.70' |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
6、**********
常用mysql语句
1、
例如：查看yu这个表有多少行。
MySQL [haha]> select count(*) from yu;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
2、
例如：查看yu这个表的全部内容
MySQL [haha]> select * from yu;
+------+------+
| name | id |
+------+------+
| 1 | 1 |
| 2 | 2 |
+------+------+
2 rows in set (0.00 sec)
3、
例如：查看yu这个表的id的内容
MySQL [haha]> select id from yu;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
4、
查看yu这个表里name和id的内容
MySQL [haha]> select name,id from yu;
+------+------+
| name | id |
+------+------+
| 1 | 1 |
| 2 | 2 |
+------+------+
2 rows in set (0.00 sec)
5、
查找yu表内id和1像的内容
MySQL [haha]> select * from yu where id like 1;
+------+------+
| name | id |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)
6、
向yu表中插入内容
MySQL [haha]> insert into yu values(3,3);
Query OK, 1 row affected (0.00 sec)
7、********
8、
清空yu表的内容
MySQL [haha]> truncate table yu;
Query OK, 0 rows affected (0.06 sec)
MySQL [haha]> select * from yu;
Empty set (0.00 sec)
9、
例如：删除表yu
MySQL [haha]> drop table yu;
Query OK, 0 rows affected (0.01 sec)
MySQL [haha]> show tables;
Empty set (0.00 sec)
10、
例如：删除haha数据库
MySQL [mysql]> drop database haha;
Query OK, 0 rows affected (0.00 sec)
MySQL [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
创建数据库本地用户
1、
例如：
MySQL [(none)]> insert into mysql.user(host,user,Password,ssl_cipher,x509_issuer,x509_subject) values("localhost","examuser",Password("000000"),"","","");
Query OK, 1 row affected (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]> select host,user,password from user;
+-----------------------+----------+-------------------------------------------+
| host | user | password |
+-----------------------+----------+-------------------------------------------+
| localhost | root | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| localhost.localdomain | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| localhost.localdomain | | |
| localhost | user | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| 192.168.200.70 | user2 | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| % | user2 | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| localhost | examuser | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
+-----------------------+----------+-------------------------------------------+
10 rows in set (0.00 sec)
2、
例如：
MySQL [(none)]> create user exam@localhost identified by '000000';
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> select host,user,password from mysql.user;
+-----------------------+----------+-------------------------------------------+
| host | user | password |
+-----------------------+----------+-------------------------------------------+
| localhost | root | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| localhost.localdomain | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| localhost.localdomain | | |
| localhost | user | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| 192.168.200.70 | user2 | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| % | user2 | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| localhost | examuser | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| localhost | exam | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
+-----------------------+----------+-------------------------------------------+
11 rows in set (0.00 sec)
mysql数据库的备份与恢复
1、
例如：
把mysql数据库备份到/tmp/下，并取名mysql.sql(备份mysql的文件必须以.sql结尾)
[root@localhost ~]# mysqldump -uroot -p000000 mysql > /tmp/mysql.sql
[root@localhost ~]# ll /tmp/
total 688
-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-------. 1 root root 0 Nov 6 16:06 yum.log
2、
不进入数据库执行创建数据库的命令
[root@localhost ~]# mysql -uroot -p000000 -e "create database mysqltest;"
[root@localhost ~]# mysql -uroot -p000000 -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| haha |
| mysql |
| mysqltest |
| performance_schema |
| test |
+--------------------+
3、
例如：
把刚刚备份的数据库恢复（恢复到的数据库要是已经存在的）
[root@localhost ~]# mysql -uroot -p000000 mysqltest < /tmp/mysql.sql
课件两数据库（mysql和mysqltest）的表内容都一样
[root@localhost ~]# mysql -uroot -p000000 -e 'use 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 |
+---------------------------+
[root@localhost ~]# mysql -uroot -p000000 -e 'use mysqltest;show tables;'
+---------------------------+
| Tables_in_mysqltest |
+---------------------------+
| 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 |
+---------------------------+
[root@localhost ~]# mysql -uroot -p000000 -e 'use haha;show tables;'
+----------------+
| Tables_in_haha |
+----------------+
| yu |
+----------------+
4、
备份mysqltest里的yu表
[root@localhost ~]# mysqldump -uroot -p000000 mysqltest yu >/tmp/yu.sql
[root@localhost ~]# ll /tmp/
total 700
-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
5、
删除了表yu，然后在恢复，yu表又回来了！
[root@localhost ~]# mysql -uroot -p000000 -e 'use mysqltest;drop table yu;'
[root@localhost ~]# mysql -uroot -p000000 -e 'use mysqltest;show tables;'
+---------------------------+
| Tables_in_mysqltest |
+---------------------------+
| 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 |
+---------------------------+
[root@localhost ~]# mysql -uroot -p000000 mysqltest < /tmp/yu.sql
[root@localhost ~]# mysql -uroot -p000000 mysqltest -e 'use mysqltest;show tables;'
+---------------------------+
| Tables_in_mysqltest |
+---------------------------+
| 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 |
| yu |
+---------------------------+
6、
-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
7、
备份
[root@localhost ~]# mysqldump -uroot -p000000 -d mysql > /tmp/mysql.sql
[root@localhost ~]# ll /tmp/
total 1420
-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 30805 Nov 6 23:03 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
恢复到haha数据库，然后查看
[root@localhost ~]# mysql -uroot -p000000 haha < /tmp/mysql.sql
[root@localhost ~]# mysql -uroot -p000000 -e 'use haha;show tables;
> '
+---------------------------+
| Tables_in_haha |
+---------------------------+
| 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 |
| yu |
+---------------------------+
1066
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
