13.1 更改MySQL数据库root密码
前面已经安装了mysql，作为一名linux运维工程师，我们需要掌握mysql一些基本的操作，以满足日常运维工作所需。
- 第一次进入数据库不需要密码：
# /usr/local/mysql/bin/mysql -uroot # -u 指定要登录的用户，后面有无空格都行；root为mysql自带的管理员账号，默认没有密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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> quit #退出时直接输入quit或exit即可
Bye
上面命令使用了绝对路径，不是很方便，为了更方便，可以把/usr/local/mysql/bin加入到PATH中。
# PATH=$PATH:/usr/local/mysql/bin
# mysql -uroot #现在就不需要使用绝对路径了
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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>
但这样重启后还是会失效，所以让它开机加载：
# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
# source /etc/profile
# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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 -uroot就能进入到mysql中。
- 给root用户设定密码：
# mysqladmin -uroot password '123456' #设定密码为123456
Warning: Using a password on the command line interface can be insecure. #这行为警告信息，意思是在命令行暴露了密码，不安全
# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) #再次登录，提示错误，因为没有密码
- 重新输入密码登录：
[root@localhost ~]# mysql -uroot -p # -p 后面不可以有空格，可以直接跟密码，也可以不跟，不跟密码就是以交互形式输入密码
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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用户密码：
mysql> SET PASSWORD FOR 'root'@localhost =PASSWORD('1234567'); #之前密码为123456，现在改为1234567
Query OK, 0 rows affected (0.00 sec)
# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) #输入123456，提示错误
# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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. #输入1234567，成功登陆
- 忘记root用户密码时修改密码：
# vim /etc/my.cnf
skip-grant #在[mysqld]下面增加这一行
# /etc/init.d/mysqld restart #重启服务
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
# mysql -uroot #进入mysql
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> update user set password = password ('1234567') where user='root'; #修改root密码
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
# vim /etc/my.cnf #去掉skip-grant这行
# /etc/init.d/mysqld restart #重启服务
# mysql -uroot -p #使用新密码登陆
13.2 连接数据库
上面我们使用mysql -uroot -p命令来连接数据库，但是连接的只是本地数据库的localhost。而很多时候，我们需要连接网络中某一主机上的mysql。
# mysql -uroot -p -h192.168.33.128 -P3306 # -h 指定远程主机的IP，-P（大写）用来指定远程主机mysql的绑定端口
Enter password:
13.3 MySQL基本常用命令
在日常工作中，难免会遇到一些与mysql相关的操作，如建库、建表、查询mysql状态等，我们需要掌握关于这些常用的命令。
查询当前库
- 查询当前库：
mysql> show databases; #注意mysql命令的最后面跟一个分号,这尤为重要
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows 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 #提示会把当前库里的所有表的字段全部读一段，可以在启动mysql时加上-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 db; #查看表的全部字段
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| 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 | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
- 另外，还可以使用这条命令：
mysql> show create table db\G; #这个命令显示信息更详细，且会把建表语句全部列出来； \G让列出来的结果竖排显示，这样看起来更清晰
*************************** 1. row ***************************
Table: db
Create Table: CREATE TABLE `db` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_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',
`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',
`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',
`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',
`Execute_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',
PRIMARY KEY (`Host`,`Db`,`User`),
KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'
1 row in set (0.00 sec)
查看当前是哪个用户
- 查看当前用户：
mysql> select user(); #查看当前用户
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
查看当前所使用的数据库
- 查看当前数据库：
mysql> select database(); #查看当前数据库
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
创建一个新库
- 新建一个库：
mysql> create database db1; #新建一个库db1
Query OK, 1 row affected (0.00 sec)
创建一个新表
- 新建一个表：
mysql> use db1 #切换到库db1
Database changed
mysql> create table t1 (`id` int(4),`name` char(40)); #新建表t1，并且写入数据，字段名id和name用反引号括起来
Query OK, 0 rows affected (0.01 sec)
查看当前数据库的版本
- 查看mysql版本：
mysql> select version(); #查看当前mysql版本
+-----------+
| version() |
+-----------+
| 5.6.36 |
+-----------+
1 row in set (0.00 sec)
查看MySQL的当前状态
- 查看当前mysql状态：
mysql> show status; #查看当前mysql状态
+-----------------------------------------------+-------------+
| 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 | 1124 |
| Bytes_sent | 25602 |
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 | #内容太长，没有列出所有信息
查看MySQL的参数
- 查看mysql各参数：
mysql> show variables; #查看mysql各参数
| innodb_stats_sample_pages | 8 |
| innodb_stats_transient_sample_pages | 8 |
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
| innodb_strict_mode | OFF |
| innodb_support_xa | ON |
| innodb_sync_array_size | 1 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_tmpdir | #这里内容太多，没有列出所有内容
修改MySQL的参数
上面列出的很多参数都是可以在/etc/my.cnf中定义的。
- 以参数 max_connect_errors为例，修改它：
mysql> show variables like 'max_connect%'; #mysql中，符号%类似于shell下的*，表示通配
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.00 sec)
mysql> set global max_connect_errors = 1000; #修改max_connect_errors的值为1000，set global可以临时修改一些参数，重启mysql服务失效，修改配置文件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 |
+--------------------+-------+ #max_connect_errors的值为1000
2 rows in set (0.00 sec)
查看当前MySQL服务器的队列
查看服务器队列在日常工作中用的最多，使用它可以查看mysql当前在干什么，也可以发现是否有锁表。
- 查看服务器队列：
mysql> show processlist; #查看服务器队列
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 11 | root | localhost | db1 | Sleep | 9373 | | NULL |
| 12 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)
创建一个普通用户并授权
- 授权：
mysql> grant all on *.* to user1 identified by '123456';
Query OK, 0 rows affected (0.00 sec)
上面，
all表示所有的权限（如读、写、查询、删除等操作）；
.有两个*，前面的*表示所有的数据库，后面的*表示所有的表；
identified by后面跟密码，用单引号括起来，这里user1指的是localhost上的user1。
- 给网络上其他机器上的某用户授权：
mysql> grant all on db1.* to 'user2'@'192.168.33.128' identified by '111222';
Query OK, 0 rows affected (0.00 sec)
上面，用户和主机的IP都用单引号括起来，两者之间有@符号，IP可以用%代替，表示所有主机。
13.4 常用的SQL语句
关系型数据库的SQL语句基本上是一样的。
查询语句select
- 第一种形式：
mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)
mysql.user表示mysql库的user表，count(*)表示表中共有多少行。
- 第二种形式：
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 |
| 192.168.33.128 | db1 | user2 | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
+----------------+---------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
3 rows in set (0.00 sec)
上面表示查询mysql库的db表中的所有数据。
- 查询单个字段或多个字段：
mysql> select db from mysql.db #查询单个字段
mysql> select db,user from mysql.db #查询多个字段
- 使用%查询：
mysql> select * from mysql.db where host like '192.168.%' #查询IP为192.168网段的mysql库的db表中的所有数据
插入一行insert
插入操作在mysql中也很常见。
- 插入：
mysql> insert into db1.t1 values (1,'abc'); #像db1库的t1表中插入1,abc
Query OK, 1 row affected (0.01 sec)
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)
更改表的某一行update
mysql表里存放的数据支持更改某个字段。
- 更改：
mysql> update db1.t1 set name='aaa' where id=1; #更改id=1的name为aaa
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
+------+------+
1 row in set (0.00 sec)
清空某个表的数据truncate
有时候我们不想删除表，只想清空数据。
- 清空：
mysql> truncate table db1.t1; #清空db1库的t1表
Query OK, 0 rows affected (0.01 sec)
mysql> select * from db1.t1;
Empty set (0.00 sec)
删除表drop table
如果某个表不需要了，那就直接删除。
- 删除表：
mysql> drop table db1.t1; #删除db1库的t1表
Query OK, 0 rows affected (0.01 sec)
删除数据库drop database
- 删除库：
mysql> drop database db1; #删除db1库
Query OK, 0 rows affected (0.00 sec)
MySQL数据库的备份与恢复
数据库的备份和恢复在日常工作中尤为重要。
MySQL备份mysqldump
- 备份：
# mysqldump -uroot -p'123456' mysql > /tmp/mysql.sql
Warning: Using a password on the command line interface can be insecure.
-u 和 -p 的作用和前面一样，后面的mysql指的是库名，然后重定向到一个文档里。
MySQL恢复
mysql的恢复与备份正好相反。
- 恢复：
# mysql -uroot -p'123456' mysql < /tmp/mysql.sql
Warning: Using a password on the command line interface can be insecure.
更多资料参考：
495
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
