本文详细介绍了MySQL数据库的管理与操作流程，包括root密码的设置与更改、不同方式的数据库连接、常用数据库命令、用户管理、SQL语句的使用及数据库备份恢复方法。
5.2 insert update drop(插入 更新 删除)
1.设置更改root密码
1.1在使用MySQL数据库时，由于某些原因长时间没有登陆MySQL，或者由于工作交接完成度不高，导致数据库root登陆密码忘记，这个有两种情况一种是知道原来的密码，另外一种是不知道root密码。
1.2 知道原来root的密码更改
//如果不能直接使用mysql命令，我们需要配置一下PATH变量就可以了
[root@knightlai ~]# mysql
-bash: mysql: command not found
[root@knightlai ~]# vi /etc/profile
export PATH=/usr/local/mysql/bin:$PATH
//使变量生效
[root@knightlai ~]#source /etc/profile
//mysql -u用来指定用户
[root@knightlai ~]# mysql -uroot -p
Enter password:
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.
1.3不知道原来root的密码的更改
//首先我们需要更改mysql的配置文件
[root@knightlai ~]# vi /etc/my.cnf
在[mysqld]下添加skip-grant-tables，然后保存并退出
[root@knightlai ~]#/etc/init.d/mysqld restart
mysql> 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
mysql> update user set password=password("knightlai") where user='root';
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0
2.连接mysql
2.1 本地连接
[root@knightlai ~]# mysql -uroot -p123456
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> 2.2mysql.sock连接
[root@knightlai ~]# mysql -uroot -p123456 -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 12
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>
2.3TCP/IP协议连接
[root@knightlai ~]# mysql -uroot -p123456 -h127.0.0.1 -P3306
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@knightlai ~]# /usr/local/mysql/bin/mysql -uroot -p123456 -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 10
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> 2.4 显示数据库
[root@knightlai ~]# mysql -uroot -p123456 -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 查询库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
3.2切换库，查看库里的表，查询表里的字段
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> 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 tables_priv\G;
*************************** 1. row ***************************
Field: Host
Type: char(60)
Null: NO
Key: PRI
Default:
Extra:
*************************** 2. row ***************************
Field: Db
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 3. row ***************************
Field: User
Type: char(16)
Null: NO
Key: PRI
Default:
Extra:
*************************** 4. row ***************************
3.3查看当前用户，当前使用的数据库
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)3.4 创建库db1,在库中创建表t1
mysql>create database db1;
mysql> create table t1(id int(4),name char(40));
Query OK, 0 rows affected (0.07 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
3.5 查看数据库版本，查看数据库状态
//查看数据库版本
mysql> select version;
ERROR 1054 (42S22): Unknown column 'version' in 'field list'
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 | 8 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Bytes_received | 1860 |
| Bytes_sent | 67236 |
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
.....................................................
//查看各参数
mysql> show variables\G;
*************************** 106. row ***************************
Variable_name: innodb_buffer_pool_dump_now
Value: OFF
*************************** 107. row ***************************
Variable_name: innodb_buffer_pool_filename
Value: ib_buffer_pool
*************************** 108. row ***************************
Variable_name: innodb_buffer_pool_instances
Value: 8
...........................................3.6查看参数，修改参数。查看对列
//查看参数
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=200;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 200 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.00 sec)
//查看对列
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 14 | root | localhost | db1 | Query | 0 | init | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 14 | root | localhost | db1 | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
4.mysql用户管理
4.1MySQL 默认有个root用户，但是这个用户权限太大，一般只在管理数据库时候才用。如果在项目中要连接 MySQL 数据库，则建议新建一个权限较小的用户来连接。MySQL授权系统主要通过五个表（user、db、host、tables_priv和columns_priv）来实现，其中用于访问数据库的各种用户信息都保存在mysql库的user表中。账户权限信息被存储在mysql数据库的user、db、host、tables_priv、columns_priv和procs_priv表中。
4.2 用GRANT语句来新建普通用户
用GRANT来创建新的用户时，能够在创建用户时为用户授权。但需要拥有GRANT权限。
//grant 权限 on 数据库.表 to '用户名'@'登录主机' [INDENTIFIED BY ‘用户密码’];
权限： select ,update,delete,insert(表数据)、create,alert,drop(表结构)、references(外键)、create temporary tables(创建临时表)、index(操作索引)、create view,show view(视图)、create routine,alert routine,execute(存储过程)、all,all privileges(所有权限)
数据库：数据库名或者*(所有数据库)
表：表名或者*(某数据库下所有表)，*.*表示所有数据库的所有表
主机:主机名或者%(任何其他主机)
mysql> grant all on *.* to 'user1' identified by '123456';
Query OK, 0 rows affected (0.07 sec)
//甚至还可以指定该用户只能执行 select 和 update 命令：
mysql> GRANT SELECT, UPDATE ON db1.* TO 'user2'@'127.0.0.1' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
//查询所有以授权的用户
mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
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 '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, UPDATE ON `db1`.* TO 'user2'@'127.0.0.1' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
4.3 删除MYSQL的用户
delete from mysql.user where user='用户名称' and host='主机名称';
删除后使用：FLUSH PRIVILEGES 来刷新权限
说明：
使用DELETET删除用户后，必须使用FLUSH PRIVILEGES 来刷新权限，否则将无法继续创建用户名与已删用户的用户名相同的用户，即使在user表中看不到已删除的用户，如果不刷新权限，也是无法再新建的。5.常用的sql语句
5.1 select 语句查询
//模糊查询
mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set (0.05 sec)
mysql> select * from mysql.db\G;
*************************** 1. row ***************************
Host: %
Db: test
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
................................5.2 insert update drop(插入 更新 删除)
//插入一条记录
mysql> insert into db1.t1 values(1,'abc');
Query OK, 1 row affected (0.07 sec)
//更新一条记录
mysql> update db1.t1 set name='aaa' where id=1;
Query OK, 1 row affected (0.15 sec)
Rows matched: 1 Changed: 1 Warnings: 0
//删除记录
mysql>drop table db1.t1;
6. mysql数据库备份恢复
6.1备份库，恢复库
[root@knightlai ~]#mysqldump -uroot -p123456 mysql > /tmp/mysql.sql;
Warning: Using a password on the command line interface can be insecure.
[root@knightlai tmp]# ls
mysql.sock mysql.sql
[root@knightlai tmp]#mysqldump -uroot -p123456 mysql < /tmp/mysql.sql;
Warning: Using a password on the command line interface can be insecure.
/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.6.39, for linux-glibc2.12 (x86_64) using EditLine wrapper
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.
....................................6.2备份表，恢复表
//备份数据库中的一个表
[root@knightlai tmp]#mysqldump -uroot -p123456 mysql user > /tmp/user.sql
Warning: Using a password on the command line interface can be insecure.
//恢复数据表
[root@knightlai tmp]#mysqldump -uroot -p123456 mysql user < /tmp/user.sql
Warning: Using a password on the command line interface can be insecure.
/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.6.39, for linux-glibc2.12 (x86_64) using EditLine wrapper
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.
Usage: /usr/local/mysql/bin/mysql [OPTIONS] [database]
-?, --help Display this help and exit.
-I, --help Synonym for -?
--auto-rehash Enable automatic rehashing. One doesn't need to use
'rehash' to get table and field completion, but startup
and reconnecting may take a longer time. Disable with
6.3备份所有库，只备份表结构
//用-A参数备份所有的库
[root@knightlai tmp]#mysqldump -uroot -p123456 -A < /tmp/user.sql
which global\ntransaction a given XA transaction is part of. The bqual part of the\nxid value must be different for each XA transaction within a global\ntransaction. (The requirement that bqual values be different is a\nlimitation of the current MySQL XA implementation. It is not part of\nthe XA specification.)\n\nThe XA RECOVER statement returns information for those XA transactions\non the MySQL server that are in the PREPARED state. (See\nhttp://dev.mysql.com/doc/refman/5.6/en/xa-states.html.) The output\nincludes a row for each such XA transaction on the server, regardless\nof which client started it.\n\nXA RECOVER output rows look like this (for an example xid value\nconsisting of the parts \'abc\', \'def\', and 7):\n\nmysql> XA RECOVER;\
..................................................
//只备份表结构
[root@knightlai tmp]# mysqldump -uroot -p123456 -d mysql < /tmp/user.sql
-- Table structure for table `ndb_binlog_index`
--
DROP TABLE IF EXISTS `ndb_binlog_index`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ndb_binlog_index` (
`Position` bigint(20) unsigned NOT NULL,
`File` varchar(255) NOT NULL,
`epoch` bigint(20) unsigned NOT NULL,
`inserts` int(10) unsigned NOT NULL,
`updates` int(10) unsigned NOT NULL,
`deletes` int(10) unsigned NOT NULL,
`schemaops` int(10) unsigned NOT NULL,
`orig_server_id` int(10) unsigned NOT NULL,
`orig_epoch` bigint(20) unsigned NOT NULL,
`gci` int(10) unsigned NOT NULL,
PRIMARY KEY (`epoch`,`orig_server_id`,`orig_epoch`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
扫一扫
1911
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
