学习linux第五十四天-CSDN博客

mysql常用操作

设置更改root密码

[root@hanlin ~]# /etc/init.d/mysqld start
Starting MySQL SUCCESS!
[root@hanlin ~]# 181203 19:22:42 mysqld_safe A mysqld process already exists

[root@hanlin ~]# mysql uroot -p
bash: mysql: 未找到命令...
[root@hanlin ~]# ps -aux |grep mysql
root 1225 0.0 0.0 115388 1708 ? S 19:13 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data/mysql --pid-file=/usr/local/mysql/data/mysql/hanlin.pid
mysql 1524 0.3 22.5 1304760 458732 ? Sl 19:13 0:02 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=hanlin.err --pid-file=/usr/local/mysql/data/mysql/hanlin.pid --socket=/tmp/mysql.sock --port=3306
root 2132 0.0 0.0 112680 980 pts/0 S+ 19:23 0:00 grep --color=auto mysql

[root@hanlin ~]# export PATH=$PATH:/usr/local/mysql/bin/
[root@hanlin ~]# echo "export PATH=$PATH:/usr/local/mysql/bin/" >> /etc/profile （让配置永久生效）
[root@hanlin ~]# source /etc/profile （让环境变量立马生效）
[root@hanlin ~]# mysqladmin -uroot password 'abcd-1234' （给mysql设置密码）
Warning: Using a password on the command line interface can be insecure.
[root@hanlin ~]# mysql -uroot -p （mysql登入，进入mysql命令行）
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>

[root@hanlin ~]# mysqladmin -uroot -p'abcd-1234' password '123456a.' （给mysql修改密码，-p后面不能有空格）

[root@hanlin ~]# mysql -uroot -p'123456a.' （登入mysql命令行，用mysql命令，不是mysqladmin）
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 11
Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>

如果不知道mysql密码怎样改密码

[root@hanlin ~]# vim /etc/my.cnf

[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data/mysql
port = 3306
server_id = 128
socket = /tmp/mysql.sock
skip-grant
[root@hanlin ~]# /etc/init.d/mysqld restart （重启mysql服务）
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

[root@hanlin ~]# mysql -uroot （登入mysql，这时候可以免密登入）
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> use mysql （进入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> select password from user ;
+-------------------------------------------+
| password |
+-------------------------------------------+
| *E80A41360319BEA0CF1C238A481E087AF70C15BC |
| |
| |
| |
| |
| |
+-------------------------------------------+
6 rows in set (0.00 sec)

mysql> select password from user where user='root' ; （从user数据表里查询有关root账号的password信息）
+-------------------------------------------+
| password |
+-------------------------------------------+
| *E80A41360319BEA0CF1C238A481E087AF70C15BC |
| |
| |
| |
+-------------------------------------------+
4 rows in set (0.00 sec)

mysql> update user set password=password('123456a.') where user='root' ;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 4 Changed: 3 Warnings: 0
mysql> Ctrl-C -- exit!
Aborted
[root@hanlin ~]# vim /etc/my.cnf （再删除skip-grant这一行就可以了）
[root@hanlin ~]# /etc/init.d/mysqld restart

[root@hanlin ~]# mysql -uroot -p123456a. （用修改过后的密码登入）
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 3
Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>

连接mysql

四种方式

[root@hanlin ~]# mysql -uroot -p123456a. （正常本地链接）
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.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>

[root@hanlin ~]# mysql -uroot -p123456a. -h127.0.0.1 -P3306 （适用于远能连接数据库，-u表示用户，-

p表示密码，-h表示主机ip，-P表示连接端口号）
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 7
Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>

[root@hanlin ~]# mysql -uroot -p123456 -S/tmp/mysql.sock （本地用socket来登入）
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@hanlin ~]# mysql -uroot -p123456a. -S/tmp/mysql.sock （本地用socket来登入）
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 9
Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>

[root@hanlin ~]# mysql -uroot -p123456a. -e "show databases" （适用于shell脚本编辑，后面带动作）
Warning: Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+

mysql常用命令

[root@hanlin ~]# mysql -uroot -p123456a. （登入mysql）
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 14
Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> use mysql （切换到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> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)

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)

mysql> desc user; （查看表字段）
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N
mysql> show create table user\G; （查看建表语句，不加\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 '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
`plugin` char(64) COLLATE utf8_bin DEFAULT 'mysql_native_password',
`authentication_string` text COLLATE utf8_bin,
`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)

ERROR:
No query specified
mysql> select user(); （查看当前用户）
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)
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> select database();（查看当前使用的数据库）
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
[root@hanlin ~]# ls -a （颜色标注的是mysql有命令历史的根源）
. 1.txt .bash_profile .config for.sh .local .rnd .viminfo .xauthtSoKD8
.. .bash_history .bashrc .cshrc inotify.sh .mysql_history .ssh .viminfo.tmp
1.sh .bash_logout .cache .dbus .lesshst .pki .tcshrc while.sh
mysql> create database db1; （创建数据库）
Query OK, 1 row affected (0.00 sec)
mysql> show database;
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 'database' at line 1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)

mysql> create table t1(`id` int(4), `name` char(40)); （创建表后面跟的是表里的字段和参数）

mysql> show create table t1\G;
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(4) DEFAULT NULL,
`name` char(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> drop table t1; （删除表）
Query OK, 0 rows affected (0.01 sec)

mysql> use db1
Database changed
mysql> show create table t1;
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
mysql> create table t1(`id` int(4), `name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> select version(); （查看数据库版本）
+-----------+
| version() |
+-----------+
| 5.6.39 |
+-----------+
1 row in set (0.00 sec)

mysql> show status; （查看数据库状态）
+-----------------------------------------------+-------------+
| Variable_name | Value |
+-----------------------------------------------+-------------+
| Aborted_clients | 0 |
| Aborted_connects | 13 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Bytes_received | 920 |
| Bytes_sent | 2982 |
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |

mysql> show variables; （查看各种参数）
mysql> show variables like 'slow%';
+---------------------+---------------------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------------------+
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/mysql/hanlin-slow.log |
+---------------------+---------------------------------------------+
3 rows in set (0.01 sec)
mysql> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.00 sec)

mysql> set global max_connect_errors=1000; （在内存中修改mysql配置参数，重启失效，如果要永久生效，编辑/etc/my.cnf）
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 1000 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.00 sec)