本文详细介绍了MySQL数据库的管理与操作方法，包括设置与更改root密码、连接MySQL、常用命令、用户管理、SQL语句、数据库备份恢复等内容，是MySQL初学者与运维人员的实用手册。
13.1 设置更改root密码
1、更改用户root密码。
mysql中的root用户和Linux系统中的root用户类似，但是这两个root不是一个用户。
mysql默认的root密码是空的，直接就可以登录，为了安全起见，要对root用户号设置密码。
方法：
首先，把 /usr/local/mysql/bin/mysql 命令加入到变量PATH中，并加入到系统配置文件/etc/profile中，使其生效。
[root@liang-00 ~]# tail -n1 /etc/profile export PATH=$PATH:/usr/local/mysql/bin/ [root@liang-00 ~]# source /etc/profile [root@liang-00 ~]#
root用户没有设置密码直接登陆mysql。quit退出mysql。
[root@liang-00 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 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> quit Bye [root@liang-00 ~]#
给mysql的root用户设置密码（当密码为空的时候）。
[root@liang-00 ~]# mysqladmin -uroot password '123456' Warning: Using a password on the command line interface can be insecure. [root@liang-00 ~]#
此时再登陆mysql就需要密码了。
[root@liang-00 ~]# 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 5 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>
更改root用户的密码。
[root@liang-00 ~]# mysqladmin -uroot -p'123456' password '1234567' Warning: Using a password on the command line interface can be insecure. [root@liang-00 ~]#
输入的密码我们一般都加上单引号，以防系统错误识别成其它意思。
2、重置root用户密码。
当root用户密码忘记是，可以对root用户密码进行重置操作。
1）首先，在 /etc/my.cnf 文件的 [global] 中添加 skip-grant语句，表示跳过认证登陆。
[mysqld]
skip-grant
datadir=/data/mysql
socket=/tmp/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
重启mysql服务。
/etc/init.d/mysqld restart
2）其次，登录mysql，修改root密码。
直接登陆mysql。
[root@liang-00 ~]# mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 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>
之后进入mysql库。
use 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>
查看用户名和密码信息。
select * from user；
查看用户的密码，其中密码是以加密方式呈现的，它是由password函数生成的。
mysql> select password from user where user='root'; +-------------------------------------------+ | password | +-------------------------------------------+ | *6A7A490FB9DC8C33C2B025A91737077A7E9CC5E5 | | | | | | | +-------------------------------------------+ 4 rows in set (0.08 sec) mysql>
用password函数更改用户密码。
mysql> update user set password=password('liang123') where user='root';
Query OK, 4 rows affected (0.12 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql>
3）最后，改完密码要把配置文件/etc/my.cnf 中的skip-grant去掉，保证系统数据的安全。
4）重启mysql服务。
13.2 连接MySQL
1、直接用用户名密码连接，监听socket连接。
mysql -uroot -p123456
[root@liang-00 ~]# 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 1 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、连接远程主机mysql。
mysql -uroot -p123456 -h127.0.0.1 -P3306 #-h指定远程主机，-P指定端口。
[root@liang-00 ~]# 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 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>
3、用socket去连接。
mysql -uroot -p123456 -S/tmp/mysql.sock #这种方法只适合本机连接。
[root@liang-00 ~]# 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 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>
4、连接mysql后操作命令。
mysql -uroot -p123456 -e "show databases"
这种情况一般使用在shell脚本里，得到一些数据，例如获取mysql连接数。
[root@liang-00 ~]# 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 | +--------------------+ [root@liang-00 ~]#
13.3 MySQL常用命令
1、查询库。
show databases;
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.04 sec) mysql>
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 mysql> use mysql Database changed mysql>
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.01 sec) mysql>
4、查看表里的字段。
库是由表组成的，表是由字段组成的。
desc user； 查看user表里的字段结构。
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 | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_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 | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_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 | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
43 rows in set (0.08 sec)
mysql>
5、查看创表语句。
show create table user\G # \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',
......
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
6、查看当前用户。
select user();
mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.49 sec) mysql>
退出当前登录，切换到 -h192.168.37.200。
mysql> select user(); +---------------------+ | user() | +---------------------+ | root@192.168.37.200 | +---------------------+ 1 row in set (0.00 sec) mysql>
mysql中也可以使用命令历史，来快捷的翻看是用过的命令。位置在 ./.mysql_history 中。
ls -la ./.mysql_history
[root@liang-00 ~]# ll -a ./.mysql_history -rw------- 1 root root 2624 Dec 20 11:24 ./.mysql_history [root@liang-00 ~]# cat ./.mysql_history _HiStOrY_V2_ q uit quit qiut quit use\040mysql; select\040*\040from\040user ; show\040databases; use\040mysql; use\040mysql show\040tables;
7、查看当前使用的数据库。
select database();
mysql> select database(); +------------+ | database() | +------------+ | mysql | +------------+ 1 row in set (0.00 sec) mysql>
8、创建库。
create database db1;
mysql> create database db1; Query OK, 1 row affected (0.06 sec) mysql>
9、创建表。
create table t1(`id` int(4), `name` char(40)); #表名t1，id、name。
mysql> create table t1(`id` int(4), `name` char(40)); Query OK, 0 rows affected (0.94 sec) 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.07 sec) mysql>
更改 CHARSET为utf8。
首先drop table t1; 把之前的table去掉。
mysql> drop table t1; Query OK, 0 rows affected (0.17 sec) mysql> create table t1(`id` int(4), `name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.28 sec) 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=utf8 1 row in set (0.01 sec) mysql>
10、查看当前数据库版本。
select version();
mysql> select version(); +-----------+ | version() | +-----------+ | 5.6.40 | +-----------+ 1 row in set (0.06 sec) mysql>
11、查看数据库的状态。
show status; #把常用状态列出。
mysql> show status; +-----------------------------------------------+-------------+ | Variable_name | Value | +-----------------------------------------------+-------------+ | Aborted_clients | 0 | | Aborted_connects | 1 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Binlog_stmt_cache_disk_use | 0 | | Binlog_stmt_cache_use | 0 | | Bytes_received | 1203 | | Bytes_sent | 22085 | | 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 | | Com_alter_server | 0 |
12、查看各种参数。
show variables; #查看mysql的参数，在my.cnf中都可以定义。
show variables like 'max_connect%'; #查看指定参数。%为通配。
mysql> show variables like 'max_connect%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 100 | | max_connections | 151 | +--------------------+-------+ 2 rows in set (0.00 sec) mysql>
13、修改参数。
set global max_connect_errors=1000; #修改 max_connect_errors 的参数。
若要永久生效需要修改 /etc/my.cnf 文件，在[global]中修改max_connect_errors。
mysql> set global max_connect_errors=1000; Query OK, 0 rows affected (0.20 sec) mysql> show variables like 'max_connect%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 1000 | | max_connections | 151 | +--------------------+-------+ 2 rows in set (0.00 sec)
14、查看队列。
此命令可以查看mysql工作状况，那些用户在使用等，在linux运维工作中常用到。
show processlist;
show full processlist; #加full，表示全部完整的。
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 11 | 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 |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 11 | root | localhost | db1 | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
mysql>
13.4 MySQL用户管理
mysql创建用户及授权。
1、针对所有数据库和命令。
grant all on *.* to 'user1'@'127.0.0.1' identified by '123456a'; #创建用户user1，登录ip为127.0.0.1，在所有表中可以执行所有操作。
flush privileges; 刷新权限。
mysql> grant all on *.* to 'user1'@'127.0.0.1' identified by '123456a'; Query OK, 0 rows affected (0.00 sec) mysql> ^DBye [root@liang-00 ~]# mysql -uuser1 -p123456a -h127.0.0.1 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 13 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、针对socket创建用户。
grant all on *.* to 'user2'@'localhost' identified by '123456a'; #localhost就相当于socket。
flush privileges; 刷新权限。
mysql> grant all on *.* to 'user2'@'localhost' identified by '123456a'; Query OK, 0 rows affected (0.00 sec) mysql> ^DBye [root@liang-00 ~]# mysql -uuser2 -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 17 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>
3、针对指定数据库和操作命令创建用户。
grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd'; #指定db1数据库的所有表，可以使用的操作语句 SELECT,UPDATE,INSERT
flush privileges; 刷新权限。
mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd'; Query OK, 0 rows affected (0.11 sec) mysql> show
4、针对所有的IP进行授权。
grant all on *.* to 'user3'@'%' identified by 'passwd'; #用%来表示所有ip。
flush privileges; 刷新权限。
mysql> grant all on *.* to 'user3'@'%' identified by 'passwd'; Query OK, 0 rows affected (0.00 sec) mysql>
5、对同一个用户添加不同的登陆IP。
首先，查看user2的创建信息。
show grants for user2@192.168.133.1;
mysql> show grants for user2@192.168.133.1; +------------------------------------------------------------------------------------------------------------------+ | Grants for user2@192.168.133.1 | +------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user2'@'192.168.133.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' | | GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.1' | +------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
其次，为 user2 添加 192.168.133.2 登陆IP。
具体执行语句是 192.168.133.1 的 show grants 中的语句：
flush privileges; 刷新权限。
mysql> GRANT USAGE ON *.* TO 'user2'@'192.168.133.2' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.2'; Query OK, 0 rows affected (0.03 sec) mysql> show grants for user2@192.168.133.2; +------------------------------------------------------------------------------------------------------------------+ | Grants for user2@192.168.133.2 | +------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user2'@'192.168.133.2' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' | | GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.2' | +------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
6、删除用户
drop user user1@127.0.0.1; //删除user1
mysql> drop user user1@127.0.0.1; Query OK, 0 rows affected (0.00 sec) mysql>
13.5 常用sql语句
增、删、改、查是关系型数据库常用的操作。
1、查看表。
select count(*) from mysql.user; 查看mysql库user表的行数。
mysql> select count(*) from mysql.user; +----------+ | count(*) | +----------+ | 12 | +----------+ 1 row in set (0.00 sec) mysql>
select * from mysql.db; 查看mysql库db表的所有数据。
* 不建议用。
数据库常用的engine有：myisam、innodb。
对于myisam引擎会自动统计行数，但对于innodb就不会自动统计，执行起来就会非常耗费时间和资源。
select db from mysql.db 查看mysql库db表中的db字段。
mysql> select db from mysql.db; +---------+ | db | +---------+ | test | | test\_% | | db1 | | db1 | +---------+ 4 rows in set (0.00 sec) mysql>
select db,user from mysql.db
mysql> select db,user from mysql.db; +---------+-------+ | db | user | +---------+-------+ | test | | | test\_% | | | db1 | user2 | | db1 | user2 | +---------+-------+ 4 rows in set (0.00 sec) mysql>
select * from mysql.db where host like '192.168.%'\G 模糊查询。
mysql> select * from mysql.db where host like '192.168.%'\G; *************************** 1. row *************************** Host: 192.168.133.1 Db: db1 User: user2 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: N Create_priv: N Drop_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Execute_priv: N Event_priv: N Trigger_priv: N *************************** 2. row *************************** Host: 192.168.133.2 Db: db1 User: user2 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: N Create_priv: N Drop_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Execute_priv: N Event_priv: N Trigger_priv: N 2 rows in set (0.00 sec) ERROR: No query specified mysql>
2、插入数据。
进入db1数据库，查看db1.t1表。没有数据空表。
mysql> use db1; 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> desc db1.t1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | name | char(40) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql>
插入数据。
insert into db1.t1 values (1, 'abc'); id的值为1，name的值为abc（字符串用单引号）。
mysql> insert into db1.t1 values (1, 'abc'); Query OK, 1 row affected (0.17 sec) mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 1 | abc | +------+------+ 1 row in set (0.00 sec) mysql>
更改数据。
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.10 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 1 | aaa | +------+------+ 1 row in set (0.00 sec) mysql>
删除一条数据。
delete from db1.t1 where id = 1;
mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 1 | aaa | | 2 | abc | +------+------+ 2 rows in set (0.00 sec) mysql> delete from db1.t1 where id = 1; Query OK, 1 row affected (0.00 sec) mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 2 | abc | +------+------+ 1 row in set (0.00 sec) mysql>
清空表。
truncate table db1.t1; 清空表，字段结构保留。
mysql> truncate table db1.t1; Query OK, 0 rows affected (0.13 sec) mysql> select * from db1.t1; Empty set (0.00 sec) mysql> desc db1.t1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | name | char(40) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql>
删除表、库。
drop table db1.t1; 删除表，表结构也会删除掉。
drop database db1； 删除db1库。
mysql> drop table db1.t1; Query OK, 0 rows affected (0.07 sec) mysql> desc db1.t1; ERROR 1146 (42S02): Table 'db1.t1' doesn't exist mysql>
关于drop和truncate，一定要谨慎操作。
13.6 MySQL数据库备份恢复
1、备份库。
mysqldump -uroot -p123456 mysql >/tmp/mysqlbak.sql #对mysql库进行备份。
2、恢复库。
首先创建mysql2库。
mysql -uroot -p123456 -e 'create database mysql2'
再恢复库到mysql2。
mysql -uroot -p123456 mysql2 < /tmp/mysqlbak.sql
mysql -uroot -p123456 mysql2 直接登陆进入mysql2库。
[root@liang-00 ~]# mysql -uroot -p123456 mysql2 Warning: Using a password on the command line interface can be insecure. Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 25 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.
查看mysql2的表，数据和mysql一样。
mysql> show tables; +---------------------------+ | Tables_in_mysql2 | +---------------------------+ | 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>
3、备份和恢复表。
备份表。
mysqldump -uroot -p123456 mysql user > /tmp/user.sql 格式为 库 表 。
[root@liang-00 ~]# mysqldump -uroot -p123456 mysql user > /tmp/user.sql Warning: Using a password on the command line interface can be insecure. [root@liang-00 ~]# less /tmp/user.sql [root@liang-00 ~]#
恢复表。
mysql -uroot -p123456 mysql2 < /tmp/user.sql 直接写库名就可以。
4、备份所有库。
mysqldump -uroot -p123456 -A > /tmp/mysql_all.sql 用 -A选项来备份所有库。
5、只备份表结构。
mysqldump -uroot -p123456 -d mysql > /tmp/mysql_only_table.sql
mysqldump命令只适合备份数据量比较稍等数据库，对于数据量打的数据库另有方法，在扩展知识中有讲解。
扩展知识：
使用xtrabackup备份innodb引擎的数据库 innobackupex 备份 Xtrabackup 增量备份 http://zhangguangzhi.top/2017/08/23/innobackex%E5%B7%A5%E5%85%B7%E5%A4%87%E4%BB%BDmysql%E6%95%B0%E6%8D%AE/#%E4%B8%89%E3%80%81%E5%BC%80%E5%A7%8B%E6%81%A2%E5%A4%8Dmysql
相关视频：
链接：http://pan.baidu.com/s/1miFpS9M 密码：86dx
链接：http://pan.baidu.com/s/1o7GXBBW 密码：ue2f
mysql5.7 root密码更改 http://www.apelearn.com/bbs/thread-7289-1-1.html
myisam 和innodb引擎对比 http://www.pureweber.com/article/myisam-vs-innodb/
mysql 配置详解： http://blog.linuxeye.com/379.html
mysql调优： http://www.aminglinux.com/bbs/thread-5758-1-1.html
同学分享的亲身mysql调优经历： http://www.apelearn.com/bbs/thread-11281-1-1.html
SQL语句教程 http://www.runoob.com/sql/sql-tutorial.html
什么是事务？事务的特性有哪些？ http://blog.csdn.net/yenange/article/details/7556094
根据binlog恢复指定时间段的数据 https://blog.csdn.net/lilongsy/article/details/74726002
mysql字符集调整 http://xjsunjie.blog.51cto.com/999372/1355013
扫一扫
4463
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
