数据库MySQL相关命令

数据库MySQL相关命令

* mysqldump命令参数

--no-create-db                 ---取消创建数据库sql(默认存在)
--no-create-info               ---取消创建表sql(默认存在)
--no-data                      ---不导出数据(默认导出)
--add-drop-database            ---增加删除数据库sql（默认不存在）
--skip-add-drop-table          ---取消每个数据表创建之前添加drop数据表语句（默认每个表之前存在drop语句）
--skip-add-locks               ---取消在每个表导出之前增加LOCK TABLES（默认存在锁）
--skip-comments                ---注释信息（默认存在）

* 用户管理

create user 'user'@'%' identified by '123';
grant all privileges on *.* to 'user'@'%' with grant option;
flush privileges;
drop user 'sys'@localhost';

* 清空表数据

truncate table 表名;

* 指定表只有insert语句热备

mysqldump -u root -p'123'  copy0 --tables 表名   表名  表名  --skip-add-drop-table --no-create-info --single-transaction > copy.sql

* 指定库全量热备

mysqldump -hhost -uroot -ppassword --single-transaction dbname > /opt/backup.sql

* 导出并压缩

mysqldump  -uroot -p'123' --single-transaction databasename | gzip > mdm_3.30_12w.sql.gz

* 压缩导入

gunzip < test.sql.gz | mysql -u root -p'123' databasename

* 查询两个库重复数据

select count(0) from `databasename`.tablename where enterprise_name in (select enterprise_name from(SELECT t.enterprise_name,COUNT(*) AS COUNT FROM( SELECT enterprise_name FROM copy0.tablename UNION ALL SELECT enterprise_name FROM `databasename`.tablename) t GROUP BY t.enterprise_name HAVING COUNT(*) > 1)t1 );

* 删除enterprise-mdm-backend中enterprise_base_info表重复数据

delete from `databasename`.tablename where enterprise_name in (select enterprise_name from(SELECT t.enterprise_name,COUNT(*) AS COUNT FROM( SELECT enterprise_name FROM copy0.tablename UNION ALL SELECT enterprise_name FROM `databasename`.tablename) t GROUP BY t.enterprise_name HAVING COUNT(*) > 1)t1 );

* 删除enterprise-mdm-backend中enterprise_reputation_info表中重复数据

delete from `databasename`. tablename where enterprise_name in (select enterprise_name from(SELECT t.enterprise_name,COUNT(*) AS COUNT FROM( SELECT enterprise_name FROM copy0.tablename UNION ALL SELECT enterprise_name FROM `databasename`.tablename) t GROUP BY t.enterprise_name HAVING COUNT(*) > 1)t1 );

* 查询enterprise_base_info表数量

select count(0) from copy0.tablename;

* 查询enterprise_reputation_info表数量

select count(0) from copy0.tablename;