第13章 MySQL常用操作
MySQL版本 5.6.35
13.1 设置、更改root用户密码
首次直接使用mysql会提示‘该命令不存在’，原因是还没有将该命令加入环境变量，如果要使用该命令，需要使用其绝对路径：/usr/local/mysql/bin/mysql，为了方便，先将其加入系统环境变量：
1[root@cham002 ~]# ls /usr/local/mysql/bin/mysql 2/usr/local/mysql/bin/mysql 3[root@cham002 ~]# export PATH=$PATH:/usr/local/mysql/bin/ 4[root@cham002 ~]# mysql -uroot 5Welcome to the MySQL monitor. Commands end with ; or \g. 6Your MySQL connection id is 1 7Server version: 5.6.35 MySQL Community Server (GPL) 8 9Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 10 11Oracle is a registered trademark of Oracle Corporation and/or its 12affiliates. Other names may be trademarks of their respective 13owners. 14 15Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 16 17mysql> quit #quit退出 18Bye 19[root@cham002 ~]# vim /etc/profile 把变量放到 /etc/profile下面命令才能永久生效 20[root@cham002 ~]# source /etc/profile 21
首次登陆mysql，root用户没有密码，直接登录：
1[root@cham002 ~]# mysql -uroot -p 2Enter password: 3Welcome to the MySQL monitor. Commands end with ; or \g. 4Your MySQL connection id is 2 5Server version: 5.6.35 MySQL Community Server (GPL) 6 7Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 8 9Oracle is a registered trademark of Oracle Corporation and/or its 10affiliates. Other names may be trademarks of their respective 11owners. 12 13Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 14 15mysql> quit
说明： 登录mysql之后可以进行与mysql相关的一些操作，但是设置mysql用户的密码需要执行以下操作
设置密码
1[root@cham002 ~]# mysqladmin -uroot password champin 2Warning: Using a password on the command line interface can be insecure. 3 4 5[root@cham002 ~]# mysql -uroot 6ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 7[root@cham002 ~]# mysql -uroot -p 8Enter password: 9Welcome to the MySQL monitor. Commands end with ; or \g. 10Your MySQL connection id is 5 11Server version: 5.6.35 MySQL Community Server (GPL) 12 13Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 14 15Oracle is a registered trademark of Oracle Corporation and/or its 16affiliates. Other names may be trademarks of their respective 17owners. 18 19Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 20 21mysql> quit 22
注： -p=passwd，使用密码登录，在此可以将密码直接输入在命令行（跟在-p后面，不加空格：-p'123456'<此处单引号可以不加，但是当密码中有特殊符号时必须加，所以在命令行输入密码时养成习惯：加单引号>），也可以不在命令行输入，只跟-p选项，然后根据提示信息：“Enter password”，输入密码进行登录（此方法不会暴露用户密码，安全）。
更改密码
1更改密码 2[root@cham002 ~]# mysqladmin -uroot -p'champin' password 'champinz' 3Warning: Using a password on the command line interface can be insecure. 4更改成功！ 5[root@cham002 ~]# mysql -uroot -p 6Enter password: 7Welcome to the MySQL monitor. Commands end with ; or \g. 8Your MySQL connection id is 17 9Server version: 5.6.35 MySQL Community Server (GPL) 10 11Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 12 13Oracle is a registered trademark of Oracle Corporation and/or its 14affiliates. Other names may be trademarks of their respective 15owners. 16 17Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 18 19mysql>
密码重置
1[root@cham002 ~]# vim /etc/my.cnf 2 3[mysqld] 4skip-grant 5datadir=/data/mysql 6socket=/tmp/mysql.sock 7# Disabling symbolic-links is recommended to prevent assorted security risks 8symbolic-links=0 9# Settings user and group are ignored when systemd is used. 10# If you need to run mysqld under a different user or group, 11# customize your systemd unit file for mariadb according to the 12# instructions in http://fedoraproject.org/wiki/Systemd 13 14[mysqld_safe] 15#log-error=/var/log/mariadb/mariadb.log 16#pid-file=/var/run/mariadb/mariadb.pid 17 18# 19# include all files from the config directory 20# 21#!includedir /etc/my.cnf.d 22 23 24##说明： 完成该操作之后就可以任意登录mysql了（无需密码），所以此时mysql安全性很差，平时配置文件中一定不要添加该参数！！！ 25 26 27 28[root@cham002 ~]# /etc/init.d/mysqld restart 29Shutting down MySQL.. SUCCESS! 30Starting MySQL... SUCCESS! 31[root@cham002 ~]# mysql -uroot 32Welcome to the MySQL monitor. Commands end with ; or \g. 33Your MySQL connection id is 1 34Server version: 5.6.35 MySQL Community Server (GPL) 35 36Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 37 38Oracle is a registered trademark of Oracle Corporation and/or its 39affiliates. Other names may be trademarks of their respective 40owners. 41 42Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 43 44mysql> 45 46mysql> use mysql; 47Database changed 48mysql> select * from user; 49+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+ 50| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | 51+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+ 52| localhost | root | *8E87EF47792D95ABA2518006D165864F8993C533 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | | N | 53| cham002 | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | | N | 54| 127.0.0.1 | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | | N | 55| ::1 | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | | N | 56| localhost | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | NULL | N | 57| cham002 | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | NULL | N | 58+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+ 596 rows in set (0.00 sec) 60 61 62mysql> select password from user; 63+-------------------------------------------+ 64| password | 65+-------------------------------------------+ 66| *8E87EF47792D95ABA2518006D165864F8993C533 | 67| | 68| | 69| | 70| | 71| | 72+-------------------------------------------+ 736 rows in set (0.00 sec) 74 75mysql> update user set password=password('champin') where user='root'; 76Query OK, 4 rows affected (0.02 sec) 77Rows matched: 4 Changed: 4 Warnings: 0 78 79mysql> quit 80 81[root@cham002 ~]# vim /etc/my.cnf 82 83[mysqld] 84datadir=/data/mysql 85socket=/tmp/mysql.sock 86# Disabling symbolic-links is recommended to prevent assorted security risks 87symbolic-links=0 88# Settings user and group are ignored when systemd is used. 89# If you need to run mysqld under a different user or group, 90# customize your systemd unit file for mariadb according to the 91# instructions in http://fedoraproject.org/wiki/Systemd 92 93[mysqld_safe] 94#log-error=/var/log/mariadb/mariadb.log 95#pid-file=/var/run/mariadb/mariadb.pid 96 97# 98# include all files from the config directory 99# 100#!includedir /etc/my.cnf.d 101 102 103[root@cham002 ~]# /etc/init.d/mysqld restart 104Shutting down MySQL.. SUCCESS! 105Starting MySQL. SUCCESS! 106 107 108[root@cham002 ~]# mysql -uroot -pchampin 109Warning: Using a password on the command line interface can be insecure. 110Welcome to the MySQL monitor. Commands end with ; or \g. 111Your MySQL connection id is 2 112Server version: 5.6.35 MySQL Community Server (GPL) 113 114Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 115 116Oracle is a registered trademark of Oracle Corporation and/or its 117affiliates. Other names may be trademarks of their respective 118owners. 119 120Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 121 122mysql> 123 124
步骤： vim /etc/my.cnf-->添加skip-grant-->mysql restart-->登录-->use mysql-->update user set password=...-->vim /etc/my.cnf-->删除skip-grant-->mysql restart。
13.2 连接mysql（本地、远程）
远程连接：使用IP/port连接
1[root@cham002 ~]# mysql -uroot -p'champin' -h127.0.0.1 -P3306 2Warning: Using a password on the command line interface can be insecure. 3Welcome to the MySQL monitor. Commands end with ; or \g. 4Your MySQL connection id is 6 5Server version: 5.6.35 MySQL Community Server (GPL) 6 7Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 8 9Oracle is a registered trademark of Oracle Corporation and/or its 10affiliates. Other names may be trademarks of their respective 11owners. 12 13Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 14 15mysql>
注： -h：=host，指定IP；-P：=port，指定端口。
本地连接：使用socket连接
1[root@cham002 ~]# ps aux |grep mysql 2root 9695 0.0 0.1 113268 1596 pts/1 S 21:23 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/cham002.pid 3mysql 9831 0.1 45.4 1038852 458728 pts/1 Sl 21:23 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/cham002.err --pid-file=/data/mysql/cham002.pid --socket=/tmp/mysql.sock 4root 9871 0.0 0.0 112684 976 pts/1 S+ 21:31 0:00 grep --color=auto mysql 5[root@cham002 ~]# mysql -uroot -pchampin -S/tmp/mysql.sock 6Warning: Using a password on the command line interface can be insecure. 7Welcome to the MySQL monitor. Commands end with ; or \g. 8Your MySQL connection id is 7 9Server version: 5.6.35 MySQL Community Server (GPL) 10 11Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 12 13Oracle is a registered trademark of Oracle Corporation and/or its 14affiliates. Other names may be trademarks of their respective 15owners. 16 17Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 18 19mysql>
注： -S：=socket，指定socket。此方法只适用于本地连接，等同于“mysql -uroot -p123456”。
显示所有数据库
1[root@cham002 ~]# mysql -uroot -pchampin -e "show databases" 2Warning: Using a password on the command line interface can be insecure. 3+--------------------+ 4| Database | 5+--------------------+ 6| information_schema | 7| mysql | 8| performance_schema | 9| test | 10+--------------------+
注： 该方法使用于shell脚本中。
13.3 MySQL常用命令
查看库信息：
1mysql> show databases; 2+--------------------+ 3| Database | 4+--------------------+ 5| information_schema | 6| mysql | 7| performance_schema | 8| test | 9+--------------------+ 104 rows in set (0.00 sec) 11 12mysql>
切换库下面（切换到mysql库）
1mysql> use mysql; 2Reading table information for completion of table and column names 3You can turn off this feature to get a quicker startup with -A 4 5Database changed
查看库里面的表
1mysql> show tables; 2+---------------------------+ 3| Tables_in_mysql | 4+---------------------------+ 5| columns_priv | 6| db | 7| event | 8| func | 9| general_log | 10| help_category | 11| help_keyword | 12| help_relation | 13| help_topic | 14| innodb_index_stats | 15| innodb_table_stats | 16| ndb_binlog_index | 17| plugin | 18| proc | 19| procs_priv | 20| proxies_priv | 21| servers | 22| slave_master_info | 23| slave_relay_log_info | 24| slave_worker_info | 25| slow_log | 26| tables_priv | 27| time_zone | 28| time_zone_leap_second | 29| time_zone_name | 30| time_zone_transition | 31| time_zone_transition_type | 32| user | 33+---------------------------+ 3428 rows in set (0.00 sec)
查看表里的字段
1mysql> desc user; 2+------------------------+-----------------------------------+------+-----+-----------------------+-------+ 3| Field | Type | Null | Key | Default | Extra | 4+------------------------+-----------------------------------+------+-----+-----------------------+-------+ 5| Host | char(60) | NO | PRI | | | 6| User | char(16) | NO | PRI | | | 7| Password | char(41) | NO | | | | 8| Select_priv | enum('N','Y') | NO | | N | | 9| Insert_priv | enum('N','Y') | NO | | N | | 10| Update_priv | enum('N','Y') | NO | | N | | 11| Delete_priv | enum('N','Y') | NO | | N | | 12| Create_priv | enum('N','Y') | NO | | N | | 13| Drop_priv | enum('N','Y') | NO | | N | | 14| Reload_priv | enum('N','Y') | NO | | N | | 15| Shutdown_priv | enum('N','Y') | NO | | N | | 16| Process_priv | enum('N','Y') | NO | | N | | 17| File_priv | enum('N','Y') | NO | | N | | 18| Grant_priv | enum('N','Y') | NO | | N | | 19| References_priv | enum('N','Y') | NO | | N | | 20| Index_priv | enum('N','Y') | NO | | N | | 21| Alter_priv | enum('N','Y') | NO | | N | | 22| Show_db_priv | enum('N','Y') | NO | | N | | 23| Super_priv | enum('N','Y') | NO | | N | | 24| Create_tmp_table_priv | enum('N','Y') | NO | | N | | 25| Lock_tables_priv | enum('N','Y') | NO | | N | | 26| Execute_priv | enum('N','Y') | NO | | N | | 27| Repl_slave_priv | enum('N','Y') | NO | | N | | 28| Repl_client_priv | enum('N','Y') | NO | | N | | 29| Create_view_priv | enum('N','Y') | NO | | N | | 30| Show_view_priv | enum('N','Y') | NO | | N | | 31| Create_routine_priv | enum('N','Y') | NO | | N | | 32| Alter_routine_priv | enum('N','Y') | NO | | N | | 33| Create_user_priv | enum('N','Y') | NO | | N | | 34| Event_priv | enum('N','Y') | NO | | N | | 35| Trigger_priv | enum('N','Y') | NO | | N | | 36| Create_tablespace_priv | enum('N','Y') | NO | | N | | 37| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | 38| ssl_cipher | blob | NO | | NULL | | 39| x509_issuer | blob | NO | | NULL | | 40| x509_subject | blob | NO | | NULL | | 41| max_questions | int(11) unsigned | NO | | 0 | | 42| max_updates | int(11) unsigned | NO | | 0 | | 43| max_connections | int(11) unsigned | NO | | 0 | | 44| max_user_connections | int(11) unsigned | NO | | 0 | | 45| plugin | char(64) | YES | | mysql_native_password | | 46| authentication_string | text | YES | | NULL | | 47| password_expired | enum('N','Y') | NO | | N | | 48+------------------------+-----------------------------------+------+-----+-----------------------+-------+ 4943 rows in set (0.00 sec)
查看建表语句
1mysql> show create table user\G; 2*************************** 1. row *************************** 3 Table: user 4Create Table: CREATE TABLE `user` ( 5 `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', 6 `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '', 7 `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', 8 `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 9 `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 10 `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 11 `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 12 `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 13 `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 14 `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 15 `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 16 `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 17 `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 18 `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 19 `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 20 `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 21 `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 22 `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 23 `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 24 `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 25 `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 26 `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 27 `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 28 `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 29 `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 30 `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 31 `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 32 `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 33 `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 34 `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 35 `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 36 `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 37 `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '', 38 `ssl_cipher` blob NOT NULL, 39 `x509_issuer` blob NOT NULL, 40 `x509_subject` blob NOT NULL, 41 `max_questions` int(11) unsigned NOT NULL DEFAULT '0', 42 `max_updates` int(11) unsigned NOT NULL DEFAULT '0', 43 `max_connections` int(11) unsigned NOT NULL DEFAULT '0', 44 `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0', 45 `plugin` char(64) COLLATE utf8_bin DEFAULT 'mysql_native_password', 46 `authentication_string` text COLLATE utf8_bin, 47 `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 48 PRIMARY KEY (`Host`,`User`) 49) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' 501 row in set (0.01 sec) 51 52ERROR: 53No query specified
查看当前用户
1mysql> select user (); 2+----------------+ 3| user () | 4+----------------+ 5| root@localhost | 6+----------------+ 71 row in set (0.00 sec) 8 9mysql> 10 11 12[root@cham002 ~]# mysql -uroot -pchampin -h192.168.230.135 13Warning: Using a password on the command line interface can be insecure. 14Welcome to the MySQL monitor. Commands end with ; or \g. 15Your MySQL connection id is 11 16Server version: 5.6.35 MySQL Community Server (GPL) 17 18Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 19 20Oracle is a registered trademark of Oracle Corporation and/or its 21affiliates. Other names may be trademarks of their respective 22owners. 23 24Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 25 26 27 28mysql> select user (); 29+--------------+ 30| user () | 31+--------------+ 32| root@cham002 | 33+--------------+ 341 row in set (0.00 sec)
命令历史文件
1[root@cham002 ~]# ls -la 2总用量 37188 3dr-xr-x---. 11 root root 4096 1月 12 23:48 . 4dr-xr-xr-x. 20 root root 282 12月 14 21:06 .. 5drwxr-xr-x 3 root root 110 12月 5 21:23 111 6-rw-r--r-- 1 root root 184 12月 9 17:54 123.txt 7-rw-r--r-- 1 root root 490 11月 17 14:21 1.txt 8-rw-r--r-- 1 root root 31 11月 17 14:42 2.txt 9drwxr-xr-x 2 root root 58 12月 10 21:11 aaaaa 10-rw-r--r-- 1 root root 4461632 12月 9 18:00 aaa.txt 11-rw-r--r-- 1 root root 4464640 12月 9 18:04 aaa.txt.tar 12-rw-------. 1 root root 1422 10月 19 07:00 anaconda-ks.cfg 13drwxr-xr-x 2 root root 6 12月 16 00:09 apache 14-rwxrwxrwx 1 user1 test 231 12月 9 17:49 a.txt 15drwxr-xr-x 2 root root 35 11月 22 22:07 awk 16-rw-------. 1 root root 30230 1月 12 22:59 .bash_history 17-rw-r--r--. 1 root root 18 12月 29 2013 .bash_logout 18-rw-r--r--. 1 root root 176 12月 29 2013 .bash_profile 19-rw-r--r--. 1 root root 176 12月 29 2013 .bashrc 20drwxrwxrwx 2 root user1 19 12月 12 19:57 chamlinux 21-rw-r--r-- 1 root root 10240 12月 12 19:59 chamlinux.tar 22-rw-r--r--. 1 root root 100 12月 29 2013 .cshrc 23drwxr-xr-x 3 root root 65 11月 26 13:52 grep 24-rw-r--r-- 1 root root 8638793 10月 21 03:39 httpd-2.4.29.tar.gz 25-rw-r--r-- 1 root root 20395803 12月 15 15:04 mariadb-10.2.6-linux-glibc_214-x86_64.tar.gz 26-rw------- 1 root root 549 1月 12 23:48 .mysql_history 27drwxr----- 3 root root 19 11月 27 18:56 .pki 28-rw------- 1 root root 1024 1月 8 23:30 .rnd 29drwxr-xr-x 2 root root 70 11月 26 14:20 sed 30drwx------. 2 root root 80 10月 18 23:58 .ssh 31-rw-r--r--. 1 root root 129 12月 29 2013 .tcshrc 32-rw------- 1 root root 8453 1月 12 21:22 .viminfo 33[root@cham002 ~]# ls -l .mysql_history 34-rw------- 1 root root 549 1月 12 23:48 .mysql_history 35[root@cham002 ~]#
查看当前使用的数据库
1mysql> select database(); 2+------------+ 3| database() | 4+------------+ 5| NULL | 6+------------+ 71 row in set (0.00 sec) 8 9 10Database changed 11mysql> use mysql; 12Database changed 13mysql> select database(); 14+------------+ 15| database() | 16+------------+ 17| mysql | 18+------------+ 191 row in set (0.00 sec)
创建库以及
1mysql> create database db1; 2Query OK, 1 row affected (0.00 sec) 3 4mysql> show databases; 5+--------------------+ 6| Database | 7+--------------------+ 8| information_schema | 9| db1 | 10| mysql | 11| performance_schema | 12| test | 13+--------------------+ 145 rows in set (0.00 sec) 15#切换到创建好的库下面去 16mysql> use db1 17Database changed 18创建一个表 19mysql> create table t1(`id` int(4), `name` char(40)); 20Query OK, 0 rows affected (0.06 sec) 21 22 23mysql> show create table t1\G; 24*************************** 1. row *************************** 25 Table: t1 26Create Table: CREATE TABLE `t1` ( 27 `id` int(4) DEFAULT NULL, 28 `name` char(40) DEFAULT NULL 29) ENGINE=InnoDB DEFAULT CHARSET=latin1 301 row in set (0.00 sec) 31 32ERROR: 33No query specified 34 35 36mysql> drop table t1; 37Query OK, 0 rows affected (0.00 sec) 38 39 40mysql> create table t1(`id` int(4), `name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8; 41Query OK, 0 rows affected (0.02 sec) 42 43 44mysql> show create table t1\G; 45*************************** 1. row *************************** 46 Table: t1 47Create Table: CREATE TABLE `t1` ( 48 `id` int(4) DEFAULT NULL, 49 `name` char(40) DEFAULT NULL 50) ENGINE=InnoDB DEFAULT CHARSET=utf8 511 row in set (0.00 sec) 52 53ERROR: 54No query specified 55
查看数据库的版本
1mysql> select version(); 2+-----------+ 3| version() | 4+-----------+ 5| 5.6.35 | 6+-----------+ 71 row in set (0.00 sec)
查看数据库状态 show status; ##内容过多不截图
•查看各参数 show variables; show variables like 'max_connect%';
• 修改参数 set global max_connect_errors=1000;
查看队列 show processlist; show full processlist
1mysql> show processlist; 2+----+------+-----------------+------+---------+------+-------+------------------+ 3| Id | User | Host | db | Command | Time | State | Info | 4+----+------+-----------------+------+---------+------+-------+------------------+ 5| 16 | root | localhost:43522 | db1 | Query | 0 | init | show processlist | 6+----+------+-----------------+------+---------+------+-------+------------------+ 71 row in set (0.00 sec)
比processlist完整
1mysql> show full processlist; 2+----+------+-----------------+------+---------+------+-------+-----------------------+ 3| Id | User | Host | db | Command | Time | State | Info | 4+----+------+-----------------+------+---------+------+-------+-----------------------+ 5| 16 | root | localhost:43522 | db1 | Query | 0 | init | show full processlist | 6+----+------+-----------------+------+---------+------+-------+-----------------------+ 71 row in set (0.00 sec)