一、mysql快速移动、复制表
移动:
移动到另一个库,只需要rename即可。
# 把test.t1移动到test_1
RENAME TABLE test.t1 TO test_1.t1;
前提是test、t1、test_1均存在。
复制:
# 1)复制 old_db.old_t 表(不包含表注释,但有列注释)
CREATE TABLE new_db.new_t [AS] SELECT * FROM old_db.old_t;
# 2)复制 old_db.old_t 表(包含所有注释、键、索引、触发器)
CREATE TABLE new_db.new_t LIKE old_db.old_t;
INSERT new_db.new_t [AS] SELECT * FROM old_db.old_t;
二、查看占用空间
#切换数据库
use `information_schema`;
#查看库占用大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='DB_Name' ;
#查看表占用大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='DB_Name' and table_name='Table_Name';
单位是MB,切换为GB就再除一个1024。
三、操作日志
1.查看日志配置
-- 查看是否开启操作日志记录和日志文件位置
mysql> show variables like 'gen%';
+------------------+-------------------------------------------+
| Variable_name | Value |
+------------------+-------------------------------------------+
| general_log | OFF |
| general_log_file | /usr/local/mysql/var/Clearwind_Aliyun.log |
+------------------+-------------------------------------------+
2 rows in set (0.00 sec)
2.开启
1)临时开启(重启服务后会关闭)
mysql> set global general_log=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'gen%';
+------------------+-------------------------------------------+
| Variable_name | Value |
+------------------+-------------------------------------------+
| general_log | ON |
| general_log_file | /usr/local/mysql/var/Clearwind_Aliyun.log |
+------------------+-------------------------------------------+
2 rows in set (0.00 sec)
2)永久开启
编辑配置文件,在mysqld下添加
general_log_file=/usr/local/mysql/var/Clearwind_Aliyun.log
general_log=ON
3.其他
1)查看日志
-- 全量查看
more /usr/local/mysql/var/Clearwind_Aliyun.log
2)把日志输出到数据库表
mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.00 sec)
-- 输出到表
set global log_output='TABLE';
-- 查看操作日志
select * from mysql.general_log;
-- 改回输出到文件(推荐)
set global log_output='FILE';
四、binlog
1.开启
#开启并设置日志路径
log-bin = /usr/local/mysql/logs/mysql-bin.log
#日志保存天数,0为永久
expire-logs-days = 7
#每个日志文件最大的大小
max-binlog-size = 500M
#masterid
server-id = 1
2.查看
#查看相关配置
show global variables like '%bin%';
#查看当前日志保存天数
show variables like '%expire_logs_days%';
````
3.清理日志
在slave关闭的情况下直接清除日志
reset master;
附:查看slave和master状态
show slave status
show master status
或者手动删除BINLOG (purge binary logs)
PURGE {MASTER | BINARY} LOGS TO 'log_name'
PURGE {MASTER | BINARY} LOGS BEFORE 'date'
例如:
PURGE MASTER LOGS TO 'mysql-bin.010';
PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00';
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);