mysql常用操作
MySQL常用操作
1. 更改root密码
1.1更改环境变量
修改/etc/profile文件，文件尾添加mysql的绝对路径
1 vi /etc/profile
在最末端加入
export PATH=$PATH:/usr/local/mysql/bin/
1.2创建MySQL密码
使用命令mysqladmin -uroot password '123456'为root用户创建初始密码
mysqladmin -uroot password '123456'
注释：可以忽略warning内容，指的是明码输入屏幕不安全。
使用命令mysql -uroot -p123456，完成初始密码登录
[root@bogon ~]# mysql -uroot -paminglinux 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 26 Server version: 5.6.40 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>
1.3 密码重置
修改配置文件/etc/my.cnf，在mysqld配置段，增加字段skip-grant
[root@bogon ~]# vi /etc/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. [mysqld] skip-grant
修改完成后，重启mysql服务： /etc/init.d/mysqld restart
使用命令登入MySQL（修改的配置段，是完成忽略授权的操作，可以直接登入，无需输入用户名密码），切换到mysql库，对user表进行更新操作
[root@bogon ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [root@bogon ~]# mysql
修改完成后，确认新密码登录有效。把/etc/my.cnf改回原有状态，并重启mysql服务。
[root@bogon ~]# vi /etc/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. [mysqld]
[root@bogon ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!
2. 连接MySQL
2.1 mysql -uroot -p123456
[root@bogon ~]# mysql -uroot -paminglinux 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 2 Server version: 5.6.40 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>
2.2 mysql -uroot -p123456 -h127.0.0.1 -P3306.
[root@bogon ~]# mysql -uroot -paminglinux -h127.0.0.1 -P3306 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.40 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>
2.3 mysql -uroot -p123456 -S/tmp/mysql.sock
[root@bogon ~]# mysql -uroot -paminglinux -S/tmp/mysql.sock 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 4 Server version: 5.6.40 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>
2.4 mysql -uroot -p123456 -e “show databases”
[root@bogon ~]# mysql -uroot -paminglinux -e "show databases" Warning: Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+
3. MySQL常用命令
3.1 查询库 show databases;
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec)
3.2 切换库 use mysql;
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
3.3 查看库里的表 show tables;
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)
3.4 查看表里的字段 desc tb_name;
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 | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N
3.5查看建表语句 show create table tb_name\G;
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 '', `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)
3.6 查看当前用户 select user();
mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
3.7查看当前使用的数据库 select database();
mysql> select database(); +------------+ | database() | +------------+ | mysql | +------------+ 1 row in set (0.00 sec)
3.8 创建库 create database db1;
mysql> create database db1; Query OK, 1 row affected (0.00 sec)
3.9 创建表 use db1; create table t1(`id` int(4), `name` char(40));
mysql> use db1; create table t1(`id` int(4), `name` char(40)); Database changed Query OK, 0 rows affected (0.00 sec)
3.10 查看当前数据库版本 select version();
mysql> select version(); +-----------+ | version() | +-----------+ | 5.6.40 | +-----------+ 1 row in set (0.00 sec)
3.11 查看数据库状态 show status;
mysql> show status; +-----------------------------------------------+-------------+ | Variable_name | Value | +-----------------------------------------------+-------------+ | Aborted_clients | 0 | | Aborted_connects | 2 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Binlog_stmt_cache_disk_use | 0 | | Binlog_stmt_cache_use | 0 | | Bytes_received | 1136 | | Bytes_sent | 24915 | | Com_admin_commands | 0 | | Com_assign_to_keycache | 0 | | Com_alter_db | 0 | | Com_alter_db_upgrade | 0 | | Com_alter_event | 0 | | Com_alter_function | 0 | | Com_alter_procedure | 0 |
3.12 查看各参数 show variables; show variables like 'max_connect%';
+--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 100 | | max_connections | 151 | +--------------------+-------+ 2 rows in set (0.00 sec)
3.13修改参数 set global max_connect_errors=1000;
mysql> set global max_connect_errors=1000; Query OK, 0 rows affected (0.00 sec)
3.14 查看队列 show processlist; show full processlist;
mysql> show processlist; show full processlist; +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ | 8 | root | localhost | db1 | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+-------+------------------+ 1 row in set (0.00 sec) +----+------+-----------+------+---------+------+-------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+-----------------------+ | 8 | root | localhost | db1 | Query | 0 | init | show full processlist | +----+------+-----------+------+---------+------+-------+-----------------------+ 1 row in set (0.00 sec)
4. MySQL创建用户以及授权
4.1 进行授权
grant all on *.* to 'user1' identified by 'passwd';
grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';
grant all on db1.* to 'user3'@'%' identified by 'passwd';
mysql> grant all on *.* to 'user1' identified by 'passwd'; Query OK, 0 rows affected (0.00 sec) mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd'; Query OK, 0 rows affected (0.00 sec) mysql> grant all on db1.* to 'user3'@'%' identified by 'passwd'; Query OK, 0 rows affected (0.00 sec)
4.2查看授权表
show grants;
mysql> show grants; +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*1836D7557E753782F1509748BD403456701A0D2F' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
show grants for user2@192.168.133.1;
mysql> show grants for user2@127.0.0.1; +--------------------------------------------------------------------------------------------------------------+ | Grants for user2@127.0.0.1 | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user2'@'127.0.0.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' | | GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'127.0.0.1' | +--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
5.MYSQL常用SQL语句
5.1 查看表内行数select count(*) from mysql.user;
mysql> select count(*) from mysql.user; +----------+ | count(*) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec)
5.2 查看db表内的内容 select * from mysql.db;
mysql> select * from mysql.db; +---------------+---------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ | Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv | +---------------+---------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ | % | test | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | N | N | Y | Y | | % | test\_% | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | N | N | Y | Y | | 127.0.0.1 | db1 | user2 | Y | Y | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | % | db1 | user3 | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | 192.168.133.1 | db1 | user2 | Y | Y | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | +---------------+---------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ 5 rows in set (0.00 sec)
5.3 查看db表内含有db字段的内容 select db from mysql.db;
mysql> select db from mysql.db; +---------+ | db | +---------+ | db1 | | test | | test\_% | | db1 | | db1 | +---------+ 5 rows in set (0.00 sec)
5.4 搜索查看多个字段 select db,user from mysql.db;
mysql> select db,user from mysql.db; +---------+-------+ | db | user | +---------+-------+ | db1 | user3 | | test | | | test\_% | | | db1 | user2 | | db1 | user2 | +---------+-------+ 5 rows in set (0.00 sec)
注释：搜索多个字段时，字段中间要用“，”隔开
5.5 查询host为127.0的内容 select * from mysql.db where host like '192.168.%';
mysql> select * from mysql.db where host like '127.0.%'; +-----------+-----+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ | Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv | +-----------+-----+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ | 127.0.0.1 | db1 | user2 | Y | Y | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | +-----------+-----+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ 1 row in set (0.00 sec)
5.6 向db1.t1中插入内容 insert into db1.t1 values (1, ‘abc’);
mysql> insert into db1.t1 values (1, 'abc'); Query OK, 1 row affected (0.00 sec)
5.7 把id=1的字段内容更新成aaa update db1.t1 set name='aaa' where id=1;
mysql> update db1.t1 set name='aaa' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
5.8 清空db1.t1表内的内容 truncate table db1.t1;
mysql> truncate table db1.t1; Query OK, 0 rows affected (0.01 sec)
注释：清空后表的结构依然存在
5.9 删除db1.t1表内的内容 drop table db1.t1;
mysql> drop table db1.t1; Query OK, 0 rows affected (0.00 sec)
注释：清空后连同表的结构一同删除
5.10 清空db1.t1数据库 drop database db1;
mysql> drop database db1; Query OK, 0 rows affected (0.00 sec)
6.MySQL数据库的备份与恢复
6.1 备份库
mysqldump -uroot -p123456 mysql > /tmp/mysql.sql
[root@bogon ~]# mysql user -uroot -paminglinux mysql > /tmp/mysql.sql Warning: Using a password on the command line interface can be insecure
6.2 恢复库
mysql -uroot -plinux mysql < /tmp/mysql.sql
[root@bogon ~]# mysql -uroot -paminglinux mysql < /tmp/mysql.sql Warning: Using a password on the command line interface can be insecure.
6.3 备份表
mysqldump -uroot -plinux mysql user > /tmp/user.sql
[root@bogon ~]# mysql user -uroot -paminglinux mysql user > /tmp/user.sql Warning: Using a password on the command line interface can be insecure.
6.4 恢复表
mysql -uroot -plinux mysql < /tmp/user.sql
[root@bogon ~]# mysql -uroot -paminglinux mysql < /tmp/user.sql Warning: Using a password on the command line interface can be insecure.
6.5 备份所有库
mysql dump -uroot -p -A > /tmp/123.sql
[root@bogon ~]# mysql user -uroot -paminglinux -A /tmp/123.sql Warning: Using a password on the command line interface can be insecure. mysql Ver 14.14 Distrib 5.6.40, for linux-glibc2.12 (x86_64) using EditLine wrapper Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
6.6 只备份表结构
mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql
[root@bogon ~]# mysql user -uroot -paminglinux -d mysql > /tmp/mysql.sql Warning: Using a password on the command line interface can be insecure.
