MySQL导入导出数据

导出数据

# 导出整个数据库
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump  -u[username] -p[password] --opt name_db > /backup/databases/name_db.sql.`date +"%Y-%m-%d-%H-%M-%S"`

# 导出一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u dbuser -p dbname users> /backup/databases/dbname_users.sql.`date +"%Y-%m-%d-%H-%M-%S"`

# 导出一个数据库结构
mysqldump -u dbuser -p -d --add-drop-table dbname > d:/dbname_db.sql.`date +"%Y-%m-%d-%H-%M-%S"`
# -d 没有数据 
# --add-drop-table 在每个create语句之前增加一个drop table

导入数据

# 常用source 命令
# 登录mysql数据库

mysql -u root -p
# 选择数据库
mysql>use 数据库

# 然后使用source命令，后面参数为脚本文件(如这里用到的.sql)
mysql>source d:/dbname.sql

查找空表

未选择数据库

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_ROWS = 0 AND TABLE_SCHEMA = 'your_db_name' AND table_name LIKE '%Prefix_%'

已选择了数据库

SHOW TABLE STATUS WHERE Rows = 0;

Windows下使用powershell和mysqldump导出指定tables结构

鉴于powershell坑爹的utf8字符集太烦了，所以选择使用批处理来导出tables结构

@echo off

::需要备份的数据表文件
set tables_file=test.txt

::需要备份的数据库名称
set db_name=test

::设置导出的文件名称
set outfile=result.sql

for /f "delims=[" %%i in (%tables_file%) do mysqldump.exe -d %db_name% --tables %%i >> %outfile%

其中mysqldump没有输入账号密码，是因为How to perform a mysqldump without a password prompt?
在mysql配置文件中添加：

[mysqldump]
user=mysqluser
password=secret

这样脚本运行的时候就不需要再输入用户密码了，防止其他用户使用ps ax命令查看历史命令记录从而获知账号密码

创建唯一索引(unique)

新建表时创建唯一索引

CREATE TABLE table_name
(
    id INT NOT NULL,
    name CHAR(30) NOT NULL,
    UNIQUE INDEX UniqIdx(id) # UniqIdx 索引名称
);

查看唯一索引（查看表结构）

mysql> show create table table_name \G
*************************** 1. row ***************************
       Table: table_name
Create Table: CREATE TABLE `table_name` (
  `id` int(11) NOT NULL,
  `name` char(30) COLLATE utf8_unicode_ci NOT NULL,
  UNIQUE KEY `UniqIdx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

在已存在的表上创建唯一索引

添加索引之前，查看指定表中创建的索引

mysql> SHOW INDEX FROM table_name \G
*************************** 1. row ***************************
        Table: table_name
   Non_unique: 1
     Key_name: nameind
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

Table 表示创建索引的表
Non_unique 表示索引非唯一，1代表非唯一索引，0代表唯一索引
Key_name 表示索引的名称
Seq_in_index 表示该字段在索引中的位置，单列索引值为1，组合索引为每个字段在索引定义中的顺序
Column_name 表示定义索引的列字段
Sub_part 表示索引的长度
Null 表示该字段是否能为空值
Index_type 表示索引类型

添加唯一索引

##使用 ALTER 添加索引
# UniqidIdx 表示索引名称
# name 表示要当作唯一索引的字段名称
mysql> ALTER TABLE table_name ADD UNIQUE INDEX UniqidIdx (name);
##
## 查看索引
mysql> SHOW INDEX FROM table_name \G
*************************** 1. row ***************************
        Table: table_name
   Non_unique: 0
     Key_name: UniqidIdx
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

删除索引

ALTER TABLE table_name DROP INDEX index_name;
# 或者
DROP INDEX index_name ON table_name;

注意：添加AUTO_INCREMENT约束字段的唯一索引不能被删除

常规操作

登录：mysql -u root -p [password]

指定IP地址和端口号登录MySQL数据库
命令格式为：mysql -h ip -u root -p -P 3306例如：mysql -h 127.0.0.1 -u root -p -P 3306

显示数据库列表：show databases;
切换数据库：use [database_name];
显示数据表：show tables;
建立数据库：create database [database_name];
建立数据表：create table [table_name];
删除数据库：drop database [database_name];
删除数据表：drop table [table_name];

查看用户权限
show grants for 'user'@'localhost';

赋予用户权限
GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost';

ALL 代表所有权限

*.* 代表所有数据库，你可以指定数据库和数据表，例如 shop.goods，这个表示 shop数据库的 goods 表

更改用户密码

对于 10.2.0 之前的 MariaDB 版本(MySQL 5.7.6 之前)，不支持使用 ALTER USER 语法更改密码。取而代之的是 SET PASSWORD 命令。下面是使用该命令更改用户密码的方法：

SET PASSWORD FOR 'username'@'localhost' = PASSWORD('newpassword');

在 10.2.0 及更高版本中(MySQL 5.7.6 之后)

ALTER USER 'username'@'hostname' IDENTIFIED BY 'newpassword';
FLUSH PRIVILEGES;
EXIT;

列出所有用户

SELECT User, Host FROM mysql.user;

参考资料：
批处理命令读取txt中每行并打印出来
mysql导入导出sql文件

标签: none

评论已关闭