Mysql学习总结_20190315

最新推荐文章于 2025-06-18 23:10:12 发布

原创最新推荐文章于 2025-06-18 23:10:12 发布 · 224 阅读

0 ·

CC 4.0 BY-SA版权

Mysql 专栏收录该内容

44 篇文章

订阅专栏

本文详细介绍MySQL数据库的用户管理、权限分配、数据操作、备份与恢复策略，包括使用mysqldump和mysqlhotcopy进行数据库备份，以及通过select...into outfile和load data infile进行数据的导出与导入。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--------创建用户、赋权于一体，并观察相应表的数据变化

mysql> grant select ,update(id,name) on test.emp1 to 'user0314'@'localhost' identified by '123' with max_connections_per_hour 30;
Query OK, 0 rows affected, 1 warning (0.13 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

-----user表数据

mysql> select host,user,select_priv,update_priv,max_connections from user where user='user0314';
+-----------+----------+-------------+-------------+-----------------+
| host      | user     | select_priv | update_priv | max_connections |
+-----------+----------+-------------+-------------+-----------------+
| localhost | user0314 | N           | N           |              30 |
+-----------+----------+-------------+-------------+-----------------+
1 row in set (0.00 sec)

-----tables_priv表数据

mysql> select host,db,user,table_name,table_priv,column_priv from tables_priv where user='user0314';
+-----------+------+----------+------------+------------+-------------+
| host      | db   | user     | table_name | table_priv | column_priv |
+-----------+------+----------+------------+------------+-------------+
| localhost | test | user0314 | emp1       | Select     | Update      |
+-----------+------+----------+------------+------------+-------------+
1 row in set (0.00 sec)

-----columns_priv表数据

mysql> select host,db,user,table_name,column_name,column_priv from columns_priv where user='user0314';
+-----------+------+----------+------------+-------------+-------------+
| host      | db   | user     | table_name | column_name | column_priv |
+-----------+------+----------+------------+-------------+-------------+
| localhost | test | user0314 | emp1       | id          | Update      |
| localhost | test | user0314 | emp1       | name        | Update      |
+-----------+------+----------+------------+-------------+-------------+
2 rows in set (0.00 sec)

-----show grants

mysql> show grants for 'user0314'@'localhost';
+----------------------------------------------------------------------------+
| Grants for user0314@localhost                                              |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user0314'@'localhost'                               |
| GRANT SELECT, UPDATE (name, id) ON `test`.`emp1` TO 'user0314'@'localhost' |
+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

-----Mysql循环插入数据

CREATE PROCEDURE test_insert ()
BEGIN

DECLARE i INT DEFAULT 0;

WHILE i < 1000 DO
	INSERT INTO test.emp1
VALUES
	(i, 'test', i + 1000, i / 2);

SET i = i + 1;

END
WHILE;

END;

call test_insert();

-----Mysql添加主键

mysql> alter table emp1 add constraint pk_emp1 primary key(id);
Query OK, 1000 rows affected (0.17 sec)
Records: 1000  Duplicates: 0  Warnings: 0

-----Mysql查看约束

select * from information_schema.TABLE_CONSTRAINTS;

-----Mysql查看触发器

select * from information_schema.`TRIGGERS`;

-------Mysql备份

----Mysqldump备份单个数据库的所有表

[root@rhel6 ~]# mysqldump -uroot -pCqmyg14dss test > /u01/backup/testdb_0314.sql

sql文件中，类似于/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */这样的为可执行的Mysql注释，可以被Mysql执行，但是在其他数据库管理系统中将被看作注释忽略;
/*!40101----数字代表Mysql的版本号，表名这些语句只有在4.01.01或者更高版本上才能被执行

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
上面一个语句在备份文件头，将当前系统变量CHARACTER_SET_CLIENT的值赋予用户自定义变量OLD_CHARACTER_SET_CLIENT,
下面一个语句在备份文件尾，将用户自定义变量OLD_CHARACTER_SET_CLIENT里保存的值赋予实际的系统变量CHARACTER_SET_CLIENT,
这样可以确保被恢复的数据库的系统变量和备份时候的系统变量相同。

----Mysqldump备份单个数据库的某个表

[root@rhel6 ~]# mysqldump -uroot -pCqmyg14dss test emp1> /u01/backup/testdb_emp1_0314.sql

----Mysqldump备份多个数据库(--all-databases参数可以备份所有数据库)

[root@rhel6 ~]# mysqldump -uroot -pCqmyg14dss --databases test mysql> /u01/backup/twodb_emp_0314.sql

----Mysqlhotcopy备份某个数据库(原理是LOCK TABLE、FLUSH TABLE、cp或scp)

[root@rhel6 ~]# mysqlhotcopy -u root -p test /u01/backup

注意：1.只是将表所在的目录复制到另一个位置
2.只能用于备份MyISAM和ARCHIVE表
3.Unix系统运行，且只能运行在数据库目录所在服务器上

-------Mysql恢复
1.使用mysql命令恢复

[root@rhel6 backup]# mysql -uroot -pCqmyg14dss testrecovery <testdb_0314.sql

mysql> select count(*) from testrecovery.emp1;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

注意：如果sql脚本里没有创建数据库的语句，且mysql指定恢复的数据库不存在，则会报错。

2.使用source命令恢复

[root@rhel6 backup]# mysql -uroot -pCqmyg14dss

mysql> use testrecovery;

Database changed

mysql> drop table emp1;
Query OK, 0 rows affected (0.00 sec)

mysql> source /u01/backup/testdb_0314.sql

--------Mysql导出文本文件

1. select ... into outfile 'filename'

mysql> select * from emp1 into outfile '/u01/backup/bak_emp1.sql';
Query OK, 1000 rows affected (0.02 sec)

注意参数：secure_file_priv(secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()传到哪个指定目录)，
   当参数为空时，可以导入到任意目录(前提是MySQL有写入权限);
   当参数为NULL时，不允许导入和导出;
   当参数为某个路径时，只能导入到该路径下;

secure_file_priv 参数是只读参数，不能使用set global命令修改,需要在参数文件中添加参数secure_file_priv=''，然后重启mysql服务生效

mysql> show variables like '%secure_file%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.01 sec)

select ... into outfile 'filename'的参数：
   fileds
       terminated by '':设置字段之间的分隔符，可以为单个或多个字符，默认为制表符'\t'
       [optionally] enclosed by '':设置字段的包围字符，只能为单个字符，如果使用optionally则只有char和varchar等字符数据字段被包括
       escaped by '':设置如何写入或读取特殊特殊字符，只能为单个字符，即设置转义字符，默认值为'\'
   lines
       starting by '':设置每行数据开头的字符，可以为单个或多个字符，默认情况下不使用任何字符
       terminated by '':设置每行数据结尾的字符，可以为单个或多个字符，默认值为'\n'

mysql> select * from test.emp1 into outfile '/u01/backup/emp1_0316.sql' fields terminated by ',' enclosed by '\"' escaped by '\'' lines terminated by '\r\n';
Query OK, 1000 rows affected (0.00 sec)

2. mysqldump命令导出文本文件
只有指定了-T参数才可以导出纯文本文件，可以指定数据库和表，如果不指定表则导出数据库下所有的表
(-T, Create tab-separated textfile for each table to given path. (Create .sql and .txt files.) NOTE: This only works if mysqldump is run on the same machine as the mysqld server.)

[root@rhel6 backup]# mysqldump -T /u01/backup test emp1 -uroot -pCqmyg14dss --fields-terminated-by=, --fields-optionally-enclosed-by=\" --fields-escaped-by=? --lines-terminated-by='\r\n'

       --fileds-terminated by=:设置字段之间的分隔符，可以为单个或多个字符，默认为制表符'\t'
       --fileds-enclosed-by=:设置字段的包围字符
       --fileds-optionally-enclosed-by=:设置字段的包围字符，只能为单个字符，只包括char和varchar等字符数据字段
       --fileds-escaped-by=:设置如何写入或读取特殊特殊字符，只能为单个字符，即设置转义字符，默认值为'\'
       --lines-terminated-by=:设置每行数据结尾的字符，可以为单个或多个字符，默认值为'\n'

3. mysql命令导出文本文件

[root@rhel6 backup]# mysql -uroot -pCqmyg14dss  --execute="select * from emp1 limit 10;" test > /u01/backup/mysql_emp_0316.sql

(--html --xml 参数可以将查询结果导入到对应格式的文件中)

--------Mysql导入文本文件

1.load data infile

mysql> delete from test.emp1;
Query OK, 1000 rows affected (0.02 sec)

mysql> select count(*) from test.emp1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> load data infile '/u01/backup/emp1.txt' into table test.emp1 fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
Query OK, 1000 rows affected (0.08 sec)
Records: 1000  Deleted: 0  Skipped: 0  Warnings: 0

2.Mysqlimport

[root@rhel6 backup]# mysqlimport -uroot -pCqmyg14dss testrecovery /u01/backup/emp1.txt --fields-terminated-by=',' --fields-optionally-enclosed-by='"'  --lines-terminated-by='\n'
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
testrecovery.emp1: Records: 1000  Deleted: 0  Skipped: 0  Warnings: 0

常用参数：
   -d，--delete：导入前清空表
   -f，--force：忽略错误