docker 命令图
帮助命令
docker官方文档
docker version
docker info
docker --help
镜像命令
doker images
-a 列举本地所有镜像
-q 只显示镜像ID
–digests 显示镜像摘要
–no-trunc 显示完整的镜像信息
docker search 某个镜像名
–no-trunc 显示完整的镜像信息
-s 列出收藏数不小于指定值的镜像
–automated 只列出 automated build类型的镜像
docker pull 某个镜像名
docker rmi
删除一个 -f 镜像 ID
删除多个 -f 镜像名1:tag 镜像名2:tag
删除全部 -f $(docker images -qa)
//查看启动的容器
[root@VM-0-7-centos ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
1800c8e4a184 mysql "docker-entrypoint..." 3 days ago Up 3 days 0.0.0.0:3306->3306/tcp, 33060/tcp mysql03
b6a0d66bc289 docker.io/redis "docker-entrypoint..." 3 days ago Up 3 days 0.0.0.0:6379->6379/tcp mmredis
//查看下载的镜像
[root@VM-0-7-centos ~]# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
docker.io/redis latest 1319b1eaa0b7 3 weeks ago 104 MB
docker.io/mysql latest 0d64f46acfd1 3 weeks ago 544 MB
docker.io/hello-world latest bf756fb1ae65 8 months ago 13.3 kB
//执行一个mysql
[root@VM-0-7-centos ~]# docker exec -it mysql03 /bin/bash
//连接mysql数据库
root@1800c8e4a184:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 352
Server version: 8.0.21 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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> show open tables
-> ;
+--------------------+---------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+---------------------------+--------+-------------+
| performance_schema | session_variables | 0 | 0 |
| mysql | column_statistics | 0 | 0 |
| db1 | Persons | 0 | 0 |
| mysql | tablespace_files | 0 | 0 |
| mysql | table_stats | 0 | 0 |
| mysql | tables_priv | 0 | 0 |
| mysql | view_table_usage | 0 | 0 |
| mysql | check_constraints | 0 | 0 |
| mysql | slave_relay_log_info | 0 | 0 |
| mysql | server_cost | 0 | 0 |
| mysql | table_partition_values | 0 | 0 |
| mysql | time_zone_transition | 0 | 0 |
| db1 | db2 | 0 | 0 |
| mysql | gtid_executed | 0 | 0 |
| mysql | engine_cost | 0 | 0 |
| mysql | columns_priv | 0 | 0 |
| mysql | triggers | 0 | 0 |
| mysql | schemata | 0 | 0 |
| mysql | index_column_usage | 0 | 0 |
| mysql | index_partitions | 0 | 0 |
| mysql | view_routine_usage | 0 | 0 |
| mysql | tables | 0 | 0 |
| mysql | slave_worker_info | 0 | 0 |
| mysql | indexes | 0 | 0 |
| mysql | foreign_keys | 0 | 0 |
| mysql | db | 0 | 0 |
| performance_schema | session_status | 0 | 0 |
| information_schema | SCHEMATA | 0 | 0 |
| mysql | foreign_key_column_usage | 0 | 0 |
| mysql | time_zone_name | 0 | 0 |
| mysql | component | 0 | 0 |
| mysql | columns | 0 | 0 |
| mysql | func | 0 | 0 |
| mysql | user | 0 | 0 |
| mysql | column_type_elements | 0 | 0 |
| mysql | procs_priv | 0 | 0 |
| mysql | proxies_priv | 0 | 0 |
| mysql | role_edges | 0 | 0 |
| mysql | default_roles | 0 | 0 |
| mysql | global_grants | 0 | 0 |
| mysql | password_history | 0 | 0 |
| mysql | character_sets | 0 | 0 |
| mysql | time_zone_leap_second | 0 | 0 |
| mysql | time_zone | 0 | 0 |
| mysql | slave_master_info | 0 | 0 |
| mysql | servers | 0 | 0 |
| mysql | events | 0 | 0 |
| mysql | catalogs | 0 | 0 |
| mysql | collations | 0 | 0 |
| mysql | table_partitions | 0 | 0 |
| information_schema | TABLES | 0 | 0 |
| mysql | time_zone_transition_type | 0 | 0 |
| mysql | tablespaces | 0 | 0 |
+--------------------+---------------------------+--------+-------------+
53 rows in set (0.00 sec)
//查看所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
//使用db1数据库
mysql> use db1;
Database changed
//给Persons加读锁
mysql> lock table Persons read;
Query OK, 0 rows affected (0.00 sec)
//再次查看表状态,Persons 被上读锁。(被锁定的状态)
mysql> show open tables;
+--------------------+---------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+---------------------------+--------+-------------+
| performance_schema | session_variables | 0 | 0 |
| mysql | column_statistics | 0 | 0 |
| db1 | Persons | 1 | 0 |
| mysql | tablespace_files | 0 | 0 |
| mysql | table_stats | 0 | 0 |
| mysql | tables_priv | 0 | 0 |
| mysql | view_table_usage | 0 | 0 |
| mysql | check_constraints | 0 | 0 |
| mysql | slave_relay_log_info | 0 | 0 |
| mysql | server_cost | 0 | 0 |
| mysql | table_partition_values | 0 | 0 |
| mysql | time_zone_transition | 0 | 0 |
| db1 | db2 | 0 | 0 |
| mysql | gtid_executed | 0 | 0 |
| mysql | engine_cost | 0 | 0 |
| mysql | columns_priv | 0 | 0 |
| mysql | triggers | 0 | 0 |
| mysql | schemata | 0 | 0 |
| mysql | index_column_usage | 0 | 0 |
| mysql | index_partitions | 0 | 0 |
| mysql | view_routine_usage | 0 | 0 |
| mysql | tables | 0 | 0 |
| mysql | slave_worker_info | 0 | 0 |
| mysql | indexes | 0 | 0 |
| mysql | foreign_keys | 0 | 0 |
| mysql | db | 0 | 0 |
| performance_schema | session_status | 0 | 0 |
| information_schema | SCHEMATA | 0 | 0 |
| mysql | foreign_key_column_usage | 0 | 0 |
| mysql | time_zone_name | 0 | 0 |
| mysql | component | 0 | 0 |
| mysql | columns | 0 | 0 |
| mysql | func | 0 | 0 |
| mysql | user | 0 | 0 |
| mysql | column_type_elements | 0 | 0 |
| mysql | procs_priv | 0 | 0 |
| mysql | proxies_priv | 0 | 0 |
| mysql | role_edges | 0 | 0 |
| mysql | default_roles | 0 | 0 |
| mysql | global_grants | 0 | 0 |
| mysql | password_history | 0 | 0 |
| mysql | character_sets | 0 | 0 |
| mysql | time_zone_leap_second | 0 | 0 |
| mysql | time_zone | 0 | 0 |
| mysql | slave_master_info | 0 | 0 |
| mysql | servers | 0 | 0 |
| mysql | events | 0 | 0 |
| mysql | catalogs | 0 | 0 |
| mysql | collations | 0 | 0 |
| mysql | table_partitions | 0 | 0 |
| information_schema | TABLES | 0 | 0 |
| mysql | time_zone_transition_type | 0 | 0 |
| mysql | tablespaces | 0 | 0 |
+--------------------+---------------------------+--------+-------------+
53 rows in set (0.00 sec)
//加写锁
mysql> lock table Persons write;
Query OK, 0 rows affected (0.03 sec)
//释放锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
//设置自动提交事务为否;(除读操作,都涉及事务,都会被锁定,为行锁,只有提交事务后,其他终端才会看见修改后的结果)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update Persons set PersonID='100';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from Persons;
+----------+----------+-----------+---------+------+
| PersonID | LastName | FirstName | Address | City |
+----------+----------+-----------+---------+------+
| 100 | NULL | NULL | NULL | NULL |
+----------+----------+-----------+---------+------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> update Persons set PersonID='1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from Persons;
+----------+----------+-----------+---------+------+
| PersonID | LastName | FirstName | Address | City |
+----------+----------+-----------+---------+------+
| 100 | NULL | NULL | NULL | NULL |
+----------+----------+-----------+---------+------+
1 row in set (0.00 sec)
mysql> update Persons set PersonID='1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from Persons;
+----------+----------+-----------+---------+------+
| PersonID | LastName | FirstName | Address | City |
+----------+----------+-----------+---------+------+
| 1 | NULL | NULL | NULL | NULL |
+----------+----------+-----------+---------+------+
1 row in set (0.01 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from Persons;
+----------+----------+-----------+---------+------+
| PersonID | LastName | FirstName | Address | City |
+----------+----------+-----------+---------+------+
| 100 | NULL | NULL | NULL | NULL |
+----------+----------+-----------+---------+------+
1 row in set (0.00 sec)
mysql>
//创建一个有主键的表
mysql> ceate table Person2( ID int primary key);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ceate table Person2( ID int primary key)' at line 1
mysql> create table Person2( ID int primary key);
Query OK, 0 rows affected (0.04 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from Person2;
Empty set (0.00 sec)
mysql> insert into Person2 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from Person2;
+----+
| ID |
+----+
| 1 |
+----+
1 row in set (0.01 sec)
//使用explain查看语句的执行情况
mysql> explain select * from Person2;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | Person2 | NULL | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
//打开profile
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> help profile;
//使用show profile 监控语句执行情况
mysql> show profile;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000073 |
| Executing hook on transaction | 0.000006 |
| starting | 0.000189 |
| query end | 0.000008 |
| closing tables | 0.000004 |
| freeing items | 0.000012 |
| cleaning up | 0.000012 |
+--------------------------------+----------+
7 rows in set, 1 warning (0.00 sec)
mysql> show profiles;
+----------+------------+-----------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------+
| 1 | 0.06766775 | help 'profile' |
| 2 | 0.00006225 | query 1 |
| 3 | 0.00006350 | query 1 |
| 4 | 0.00006600 | show query 1 |
| 5 | 0.00013375 | set profiling=1 |
| 6 | 0.00030250 | help 'profile' |
+----------+------------+-----------------+
6 rows in set, 1 warning (0.00 sec)
//指定上述的一条ID语句;
mysql> show profile for query 5;
+----------------+----------+
| Status | Duration |
+----------------+----------+
| starting | 0.000073 |
| Opening tables | 0.000025 |
| query end | 0.000007 |
| closing tables | 0.000005 |
| freeing items | 0.000011 |
| cleaning up | 0.000015 |
+----------------+----------+
6 rows in set, 1 warning (0.00 sec)
mysql>
//查看db1数据库中Person2的所含的索引;
mysql> show index from db1.Person2;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Person2 | 0 | PRIMARY | 1 | ID | A | 1 | NULL | NULL | | BTREE | | | YES | NULL |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)
mysql>
读锁只阻塞写,
写锁阻塞同时读写。
1.登录服务器(打开第一个终端)
2.查看docker启动的容器
3.docker关闭容器
4.docker启动容器
5.执行mysql容器
6.登录mysql
7.打开第二个终端(重复1-6)
1.查看数据库
2.查看数据库表的锁情况
3.终端A 锁一张表(读锁或写锁)
4.终端A 查看和修改被锁定的表
5.终端A 查看和修改其他表
6.终端B 重复4-5
7.终端A 解锁表
8.终端A,B 重复4-5
//要加索引(行锁)
MYSQL是用INNODB
set autocommit=0后是手动提交事务
当执行了更新操作后会自动锁定行(行锁)
当手动提交事务后才会释放行锁
//若索引使用不当可能升级为表锁(例如,自动类型转换)
间隙锁:(插入状态)
更新id 1-9 (但是2不存在)
若插入id=2的数据,则阻塞。
锁定一行:
begin;
select … for update;
…
…
commit;