本文详细介绍了MySQL的快速导出与导入数据方法，包括使用INTO OUTFILE和LOAD DATA INFILE命令，以及设置安全目录。同时，讨论了数据库迁移的原因和常见方案，如直接导出、使用第三方工具和数据文件拷贝。此外，还涵盖了统一字符编码、增删改查操作、配置文件的读取顺序以及MySQL命令行快捷命令。文章最后提及SQL语句的语义种类和egrep命令的使用。
一、 快速导出
1、表的快速导出
语法：
SELECT... INTO OUTFILE 导出文本文件
要想导出成功，需要设置安全目录才行
vim /etc/my.cnf
[mysqld]
secure-file-priv=/tmp
示例：
SELECT * FROM db1.t1
INTO OUTFILE '/tmp/db1_t1.txt'
FIELDS TERMINATED BY ',' -- 定义字段分隔符
OPTIONALLY ENCLOSED BY '"' -- 定义字符串使用什么符号括起来
LINES TERMINATED BY '\n'; -- 定义换行符
2、快速导入
语法
LOAD DATA INFILE 导入的文本文件路径
示例
mysql> DELETE FROM student1;
mysql> create table new_t1(表结构与文件中数据保持一致);
mysql> LOAD DATA INFILE '/tmp/db1_t1.txt'
INTO TABLE new_db.new_t1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
3、其他导出
mysql 命令导出文本文件
示例:
# mysql -u root -pEgon123 -e 'select * from db1.t1' > /tmp/db1_t1.txt
# mysql -u root -pEgon123 --xml -e 'select * from db1.t1' > /tmp/db1_t1.xml
# mysql -u root -pEgon123 --html -e 'select * from db1.t1' > /tmp/db1_t1.html
二、 示例
1、准备测试数据
create database egon_test;
use egon_test;
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male',
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, depart_id int );
#插入记录
#三个部门：教学，销售，运营
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values ('egon','male',18,'20170301','God',7300.33,401,1), ('alex','male',78,'20150302','teacher',1000000.31,401,1), ('wupeiqi','male',81,'20130305','teacher',8300,401,1), ('yuanhao','male',73,'20140701','teacher',3500,401,1), ('liwenzhou','male',28,'20121101','teacher',2100,401,1), ('jingliyang','female',18,'20110211','teacher',9000,401,1), ('jinxin','male',18,'19000301','teacher',30000,401,1);
2、设置安全目录为/tmp，并重启
vim /etc/my.cnf
[mysqld]
secure-file-priv=/tmp
3、快速导出
SELECT * FROM egon_test.employee
INTO OUTFILE '/tmp/test.txt'
FIELDS TERMINATED BY ',' -- 定义字段分隔符
OPTIONALLY ENCLOSED BY '"' -- 定义字符串使用什么符号括起来
LINES TERMINATED BY '\n'; -- 定义换行符 12345
4、导入
# 创建表，表结构与数据保持一致
create database if not exists test;
use test;
create table t1(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male',
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int,
depart_id int );
# 快速导入
LOAD DATA INFILE '/tmp/test.txt'
INTO TABLE test.t1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
三 、数据库迁移
1、为何要迁移数据库
数据库迁移是我们经常可遇到的问题，对于少量的数据，迁移基本上不会有什么问题。生产环境中，有
以下情况需要做迁移工作：
# 1、磁盘空间不够
比如一些老项目，选用的机型并不一定适用于数据库。随着时间的推移，硬盘很有可能出现短缺；
# 2、业务出现瓶颈
比如项目中采用单机承担所有的读写业务，业务压力增大，不堪重负。
如果 IO 压力在可接受的范围，会采用读写分离方案；
# 3、机器出现瓶颈
机器出现瓶颈主要在磁盘 IO 能力、内存、CPU，此时除了针对瓶颈做一些优化以外，选择迁移是
不错的方案；
# 4、项目改造
某些项目的数据库存在跨机房的情况，可能会在不同机房中增加节点，或者把机器从一个机房迁移
到另一个机房。再比如，不同业务共用同一台服务器，为了缓解服务器压力以及方便维护，也会做
迁移。
2、MySQL迁移数据库的方案
通常有三种方案
1、数据库直接导出，拷贝文件到新服务器，在新服务器上导入
#例如
（1）基于mysqldump
在源主机执行下述命令，需要目标主机开启远程账号权限
mysqldump -h 迁移源IP -uroot -p123 --databases bbs | mysql -h 目标IP - uroot -p456
（2）基于LOAD DATA INFILE
#优点：
会重建数据文件，减少数据文件的占用空间（释放undo段），兼容性最好，导出导入很少发生问 题，需求灵活
#缺点：
导入导出都需要很长的时间，并且导出后的文件还要经过网络传输，也要占用一定的时间。
2、使用第三方迁移工具
#例如
使用【MySQL GUI Tools】中的 MySQLMigrationTool。
#优点：
设置完成后传输无人值守，自动完成
#缺点：
1、不够灵活，设置繁琐
2、传输时间长，
3、传输中网络出现异常，不能及时的被发现，并且会一直停留在数据传输的状态不能被停止， 如不仔细观察不会被发现异常。
4、异常后很难从异常的位置继续传输。
3、数据文件和库表结构文件直接拷贝到新服务器，挂载到同样配置的MySQL服务下。
# 优点：
时间占用最短，文件可断点传输，操作步骤少。
# 缺点：
新旧服务器中MySQL版本及配置必须相同，可能引起未知问题
假如数据库迁移是因为业务瓶颈或项目改造等需要变动数据表结构的(比如分区分表)，我们便只能使用第一种方法了。
4、下面介绍一下第三种方案的迁移步骤：
1、保证Mysql版本一致，安装配置基本一致（注意：这里的数据文件和库表结构文件都指定在同一目录 data下）
2、停止两边的Mysql服务（A服务器--迁移-->B服务器)
3、删除B服务器Mysql的data目录下所有文件
4、拷贝A服务器Mysql的data目录下除了ib_logfile和.err之外的文件到B服务器data下
5、启动B服务器的Mysql服务，检测是否发生异常 12345
四、统一字符编码
#1. 修改配置文件
[mysqld]
default-character-set=utf8mb4
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
#mysql5.5以上：修改方式有所改动
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
[client] #添加这一行
default-character-set=utf8mb4 # 添加这一行
[mysql] # 添加这一行
default-character-set=utf8mb4 #添加这一行
删除或注释最后一行
# sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#2. 重启服务
#3. 查看修改结果：
\s
show variables like '%char%'
[root@db02 ~]# egrep '^[^#]' /etc/my.cnf
[mysqld]
character-set-server=utf8mb4 #增加这两行
collation-server=utf8mb4_general_ci #增加这两行
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
五、增删改查
增：insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3');
查：select * from t1;
select id,name from mm01.t1;
select id,name from mm01.t1 where id=3;
select id,name from mm01.t1 where id<3;
改：update t1 set name='sb' where id=2;
删：delete from t1 where id=1;
清空表：
truncate table t1;数据量大，删除速度比上一条快，且直接从零开始，# （推荐）
delete from db1.t1; -- # 不要这么做
delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。
auto_increment 表示：自增
primary key 表示：约束（不能重复且不能为空）；加速查找
六、配置文件
[root@db01 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#skip-grant-tables
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
plugin-load-add=validate_password.so
validate-password=FORCE_PLUS_PERMANENT
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
default-character-set=utf8mb4
[mysql]
user="root"
password="123456"
default-character-set=utf8mb4
七、mysql设置密码
方式一：[root@db04 ~]# mysqladmin -uroot password '123' #不指定端口的情况下默认是3306
方式二：mysql> update mysql.user set PASSWORD=password('123') where user='root'; #在数据库内直接编辑user表来指定用户设置密码
mysql>flush privileges; #授权要刷新授权
方式三：mysql> set password for root@localhost = password('123'); #在数据库内直接用set password命令指定用户设置密码
方式四：mysql>grant all on *.* to 'root'@'localhost' identified by '123'； #在数据库内用授权密令直接给用户加上密码
mysql>flush privileges; #授权要刷新授权
八、MySQL实例初始化配置
1.初始化配置文件的作用
1.预编译：cmake去指定，硬编码到程序当中去
2.在命令行设定启动初始化配置
--skip-grant-tables
--skip-networking
--datadir=/application/mysql/data
--basedir=/application/mysql
--defaults-file=/etc/my,cnf
--pid-file=/application/mysql/data/db01.pid
--socket=/application/mysql/data/mysql.sock
--user=mysql
--port=3306
--log-error=/application/mysql/data/db01.err
2.配置文件读取顺序
1）读取顺序
/etc/my.cnf
/etc/mysql/my.cnf
$basedir/my.cnf（前提是在环境变量中定义了MYSQL_HOME变量）
defaults-extra-file （类似include）
~/.my.cnf
2）生效顺序
~/.my.cnf
defaults-extra-file （类似include）
$basedir/my.cnf（前提是在环境变量中定义了MYSQL_HOME变量）
/etc/mysql/my.cnf
/etc/my.cnf
3）生效顺序验证
#配置/etc/my.cnf
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
#配置/etc/mysql/my.cnf
[root@db01 ~]# mkdir /etc/mysql
[root@db01 ~]# vim /etc/mysql/my.cnf
[mysqld]
server_id=2
#配置$basedir/my.cnf
[root@db01 ~]# vim /service/mysql/my.cnf
[mysqld]
server_id=3
#配置~/my.cnf
[root@db01 ~]# vim ~/.my.cnf
[mysqld]
server_id=4
#重启数据库（注意这里不能使用systemctl restart mysql重启）
[root@db01 ~]# systemctl stop mysql
[root@db01 ~]# /etc/init.d/mysqld start
#查看server_id
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 4 |
+---------------+-------+
1 row in set (0.00 sec)
3.思考参数执行优先级
#cmake：
socket=/service/mysql/tmp/mysql.sock
#命令行：
--socket=/tmp/mysql.sock
#配置文件：
vim /etc/my.cnf
[mysqld]
socket=/opt/mysql.sock
#default参数：
--defaults-file=/tmp/a.txt
vim /tmp/a.txt
[mysqld]
socket=/tmp/test.sock
#启动测试
mysql_safe --defaults-file=/tmp/a.txt --socket=/tmp/mysql.sock
优先级测试结论：
1、命令行
2、defaults-file
3、配置文件
4、预编译
命令行 >> defaults-file >> ~/.my.cnf >> defaults-extra-file >> $basedir/my.cnf >> /etc/mysql/my.cnf >> /etc/my.cnf >> cmake
九、mysql相关命令
1.mysql连接后命令行快捷命令
\c：中断当前输入语句
\r：重新连接到服务器
\d：设置语句sql结束符 mysql> \d ]
\e：编辑命令 输出输入的内容 echo
\p：打印当前命令并执行
\G：垂直显示结果
\q：退出mysql 等于 quit exit
\g：表示结束 等于 ; 的作用
\h：显示此帮助 等于 help #help还可以查看命令语法 help create database;
\t：不写入outfile
\T：将所有内容附加到给定的输出文件中（只支持本次会话） \T /tmp/a.log
#\n：禁用寻呼机，打印到标准输出。
#\P：设置寻呼机[到寻呼机]。通过寻呼机打印查询结果。
\R：更改mysql提示符 \R mysql>>>
\.：执行一个sql文件 等于 source
\s：从服务器获取状态信息 等于 status
\u：切换数据库 等于 use #查看当前所在数据库select database();
\C：切换到另一个字符集 一般不使用
\W：在每个语句后显示警告
\w：不在每个语句后显示警告
1）help命令
mysql> help
mysql> help contents help 用法
mysql> help select
mysql> help create
mysql> help create user
mysql> help status
mysql> help show
2.客户端mysqladmin命令
1.修改密码,设置密码:password
[root@db01 ~]# mysqladmin -uroot -p旧密码 password '新密码'
2.关闭MySQL服务:shutdown
[root@db01 ~]# mysqladmin -uroot -p密码 -S socket文件 shutdown
3.库外建库:create
[root@db01 ~]# mysqladmin -uroot -p密码 create mm
[root@db01 ~]# mysql -uroot -p123 -e 'create database mm'
4.库外删除数据库:drop
[root@db01 ~]# mysqladmin -uroot -p123 drop mm
Do you really want to drop the 'mm' database [y/N] y
Database "mm" dropped
5.查看配置文件所有的默认参数:variables
[root@db01 ~]# mysqladmin -uroot -p123 variables
[root@db01 ~]# mysqladmin -uroot -p123 variables | grep server_id
6.检测MySQL进程是否存活:ping
[root@db01 ~]# mysqladmin -uroot -p123 ping
7.查看数据库 慢查询,负载信息:status
[root@db01 ~]# mysqladmin -uroot -p123 status
Uptime MySQL服务器已经运行的秒数
Threads 活跃线程（客户）的数量
Questions 从mysqld启动起来自客户问题的数量 已经发送给服务器的查询的个数
Slow queries 已经超过long_query_time秒的查询数量
Opens mysqld已经打开了多少表
Flush tables flush ..., refresh和reload命令数量
Open tables 现在被打开的表数量
Queries per second avg: 0.046 负载
8.重载授权表,刷新缓存主机:reload，相当于flush privileges
[root@db01 ~]# mysqladmin -uroot -p123 reload
9.刷新binlog日志
[root@db01 ~]# mysqladmin -uroot -p123 flush-log
十一、SQL语句语义种类
DDL：Data Query Language 数据定义语言（CREATE）
DCL：Data control Language 数据控制语言（GRANT，ROLLBACK，COMMIT）
DML：Data Manipulation Language 数据操作语言（INSERT，UPDATE，DELETE）
DQL：Data Query Language 数据查询语言（SELECT）
#DPL：事务处理语言（BEGIN TRANSACTION、COMMIT和ROLLBACK）
实例===》库===》表===》字段、记录、索引
针对库的操作：
#1.使用help查看语句
mysql> help create database;
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
#2.创建mm数据库
mysql> create database mm;
mysql> create schema mm;
#3.创建已存在数据库时会报错
mysql> create database if not exists mm;
#4.查看建库语句
mysql> show create database mysql;
mysql> show create database mm;
#5.创建数据库并制定字符集和校验规则
mysql> create database if not exists mm collate utf8_general_ci charset utf8; #如果不存在就创建，存在就不创建
#6.删库:
mysql> drop database mm;
#7.修改库:
mysql> alter database mm charset utf8mb4;
十二、egrep的用法
过滤出不以#开头的行
grep -v '^#' /etc/inittab
过滤出不以#开头的非空行
grep -v '^#' /etc/crontab |grep -v '^$'
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
