1.导入导出数据
mysqldump -uroot -p -d dbname –skip-tz-utc --set-gtid-purged=OFF> dbname .sql --只导出表结构
–skip-tz-utc 跳过时区设置,默认中时区导出的数据时间字段会相差8个小时
--、导入数据库
--方法一:
--(1)选择数据库
mysql>use dbname ;
--(2)设置数据库编码
mysql>set names utf8mb4;
--(3)导入数据(注意sql文件的路径)
mysql>source /home/xxxx/dbname .sql;
--方法二:
mysql -u用户名 -p密码 数据库名 < 数据库名.sql
2.创建存储过程
–存储过程
(distinct author_name)
drop procedure if exists updateBitch;
delimiter //
CREATE procedure updateBitch()
BEGIN
DECLARE i INT DEFAULT 0;
SET @var=(SELECT COUNT(*) FROM b_interlocution);
while i< @var
do
SET @var1=(SELECT id FROM b_interlocution LIMIT i,1);
update b_interlocution set answer_num=(SELECT COUNT(*) FROM b_interlocution_reply where question_id=@var1) where id=@var1;
--INSERT INTO b_article_source (admin_id,publish_name) VALUES(1,@var1 );
set i = i+1;
END while ;
commit;
END //
delimiter ;
call updateBitch();
3.mysql配置文件加载顺序
mysql --verbose --help | grep my.cnf
show grants for 'bera' --查看账户权限
revoke select/process on information.* from 'bera'; --撤销权限
grant select on information.* to 'bera'; --授予某个账户权限
GRANT all privileges ON bera_master.* TO 'liuxuyao'@'%' [IDENTIFIED BY 'lxy'];
REVOKE all privileges on winmax_activity_eth.* FROM 'winmax'@'%';
4.更改数据库编码
alter database bera_master character set utf8mb4;
5.查看数据库数据大小
SELECT table_name, data_length + index_length AS len, table_rows,
CONCAT(ROUND((data_length + index_length)/1024/1024,2),'MB') AS datas
FROM information_schema.tables WHERE table_schema = 'bera_master' ORDER BY len DESC;
6.更改数据库账户密码8.0
-- 修改密码为永不过期
mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.02 sec)
-- 修改密码并指定加密规则为mysql_native_password
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'lxy';
Query OK, 0 rows affected (0.01 sec)
-- 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
7,数据碎片整理(delete数据之后不释放内存)
optimize no_write_to_binlog table finance_detail;
ps:操作之前最好查看innodb_online_alter_log_max_size变量,尽量增大
{show variables like 'innodb_online_alter_log_max_size';set global innodb_online_alter_log_max_size=805306368;}
alter table finance_detail truncate partition p180701; 删除分区数据并释放占用内存
select * from test partition(p180701) 查询分区数据
8,清理数据库日志
purge master Logs to 'mysql-bin.003946'; 清理该日志之前数据
附:
mysql 官方 文档