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 -urootWelcome 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' #设定密码为123456Warning: Using a password on the command line interface can be insecure. #这行为警告信息，意思是在命令行暴露了密码，不安全# mysql -urootERROR 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，现在改为1234567Query OK, 0 rows affected (0.00 sec)# mysql -uroot -pEnter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) #输入123456，提示错误# mysql -uroot -pEnter 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.cnfskip-grant #在[mysqld]下面增加这一行# /etc/init.d/mysqld restart #重启服务Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! # mysql -uroot #进入mysqlmysql> 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; #新建一个库db1Query OK, 1 row affected (0.00 sec)
创建一个新表
- 新建一个表：
mysql> use db1 #切换到库db1Database 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的值为10002 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,abcQuery 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为aaaQuery 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.sqlWarning: Using a password on the command line interface can be insecure.
-u 和 -p 的作用和前面一样，后面的mysql指的是库名，然后重定向到一个文档里。
MySQL恢复
mysql的恢复与备份正好相反。
- 恢复：
# mysql -uroot -p'123456' mysql < /tmp/mysql.sqlWarning: Using a password on the command line interface can be insecure.
更多资料参考：
MySQL教程
mysqldump和binlog备份恢复某个库/表
