mysql导入导出 等常用命令

记录post节点导出

导出
mysqldump -uroot -p -B dbname --master-data >dbnamepath.sql
导入
#mysql　dbname -f　<　dbnamepath.sql
-f为强制跳过错误，继续下一行导入
单个数据库导入dbname也可以不用写
>source　/bak/xxx.sql　

master-data导出

mysqldump -uroot -potoyix --single-transaction  --master-data=2 数据库名|gzip >/data/mysql/bak/数据库名`date +%Y-%m-%d`.sql.gz

mysqldump 参数说明

-f   为强制跳过错误，继续下一行导入
-t   不导出建表语句,即，在只导表结构的情况下使用
-B　数据库名　--table　表名
--skip-extended-insert  导出insert VALUES 值
--complete-insert     与上(--skip-extended-insert)同时使用,即表字段与值匹配导出
--single-transaction   确保备份在事务中进行，不锁表，即REPEATABLE READ 这样能保证在一个事务中所有相同的查询读取到同样的数据，也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表的数据并提交，对该dump线程的数据并无影响，在这期间不会锁表。
--skip-lock-tables     避免对表进行锁定。不锁表

表字段与值匹配导出
–skip-extended-insert --complete-insert

mysqldump -ucurefun -pcurefun --skip-extended-insert --complete-insert  dbname > dbnamedate.log

导入说明

mysqldump | gzip 压缩并导出数据库命令：

mysqldump -h主机IP -u数据库用户名 -p数据库密码 数据库名称 | gzip > /导出目录/test.sql.gz

系统崩溃，重建系统时，可以这样恢复数据：

#mysql -u root -p dbname< dbname_xxxx.sql

从压缩文件直接恢复：

#gzip < dbname_xxxxsql.gz | mysql -u root -p dbname

解压缩

保留源文件的方法
gunzip -c dbname_xxxx.sql.gz > dbname_xxxx.sql

不保留源文件，解压
gzip -dv dbname_xxxx.sql.gz
或
gunzip -c dbname_xxxx.sql.gz

其他常见导入导出

导出数据库中表的表数据及结构

mysqldump  dbname -u username -ppassword --tables table_name1 table_name2 table_name3>xxx.sql

只导出表结构 不导出数据

mysqldump --opt -d 数据库名 -u root -p > xxx.sql

只导出数据库中的某几个表的表结构

mysqldump -u root -p -d 数据库名 表名1  表名2 表名3 表名4> db_table.sql

导出数据不导出结构（常用）

	--complete-insert  字段与值匹配导出　（即insert与values值匹配）
	-t 不导出建表语句
mysqldump 数据库名 --complete-insert -t -u root -p >xxx.sql

–skip-extended-insert选项来使导出的数据，是多行插入形式的。

mysqldump为了加快导入导出，默认把数据都缩减在一行里面。查看和修改不方便，为此，我们可以使用--skip-extended-insert选项来使导出的数据，是多行插入形式的。

mysqldump -uroot -p --master-data  --default-character-set=utf8 --skip-extended-insert DBname TABLESname > TABLES.sql

导出数据和表结构

mysqldump　　-uroot　-pxxxxxx　数据库名 >xxx.sql　

导出指定表的结构和数据

/usr/local/mysql/bin/mysqldump  dbname  -uusername  -ppasword --tables tablename >test.sql

导出特定表的结构

mysqldump　-uroot　-p　-B　数据库名　--table　表名　>　xxx.sql

只导出数据 （一行一行的导出 insert语句）

mysqldump -t dbname --table tablename -uroot  -p  --skip-extended-insert --complete-insert  >/data/del20220415.sql

导出查条件的行

mysqldump -t dbname --table tablename -uroot  -p  --skip-extended-insert --complete-insert --where="create_time < '2019-12-07 12:04:50'" >/data/del20220415.sql

导入数据：

#mysql　数据库名　<　文件名
#source　/bak/xxx.sql　

删除表数据（保留表结构）

truncate table tablename

清空表

truncate table case_department

查看binlog日志

/usr/local/mysql/bin/mysqlbinlog -v --base64-output=decode-rows mysql-bin.000021 |sed -n '/#220403 14:32:13/,/#220404 12:00:04/p' >aaaaa.txt

另：
1、设置用户超级权限

CREATE DATABASE yearning CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
grant all on *.* to yearning@"192.168.%" identified by "aG7%oE2#aJ7{nF0@o222F2&";
UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='yearning';
flush privileges;

2、清除用户所有权限

revoke all privileges on *.* from root@"%";

3、清除用户单个数据库所有权限

REVOKE Alter Routine, Create, Create Routine, Create Temporary Tables, Create View, Delete, Drop, Event, Execute, Grant Option, Index, Insert, Lock Tables, References, Select, Show View, Trigger, Update ON `dbname`.* FROM `otoyix`@`%`;

美化SQL
REVOKE ALTER Routine,
CREATE,
CREATE Routine,
CREATE TEMPORARY TABLES,
CREATE VIEW,
DELETE,
DROP,
EVENT,
EXECUTE,
GRANT OPTION,
INDEX,
INSERT,
LOCK TABLES,
REFERENCES,
SELECT
	,
	SHOW VIEW,
	TRIGGER,
	UPDATE ON `dbname`.* 
FROM
	`otoyix` @`%`;

4、改表、字段 的 字符集和检验规则

改表
alter table 表名 charset=utf8mb4 collate=utf8mb4_general_ci;
改字段
alter table 表名 modify 字段名 varchar(2000) character set utf8mb4 collate utf8mb4_general_ci;

-----------end