mysql管理维护常用操作

mysql管理维护常用的操作流程

目录

设置记录通用日志   
设置记录二进制日志  
查看二进制日志 
备份数据库  
数据备份恢复 
数据库迁移
导入导出
使用sql文件创建表
批量删除表  

设置记录通用日志

修改conf文件:                            
    general_log = 1 开启通用日志                            
    log_output=FILE,TABLE 设置保存类型                            
    general_log_file=/usr/local**/a.log                            
    #tabel的位置在myslq/mysql下(慢查询table也在这)

重启                            

设置记录二进制日志

修改conf文件:                            
   log_bin=1 开启                            
   log_bin_basename=/usr/local/mysql/data/binlog                            
   log_bin_index=/usr/local/mysql/data/binlog.index                            
   log_bin_trust_function_creators=0                            
   log_bin_use_v1_row_events=0                            
   sql_log_bin=1                            
                            
   max_binlog_cache_size                            
   max_binlog_size                            
   max_binlog_stmt_cache_size   

重启                        

查看二进制日志

另存为txt方便本地查看
./mysqlbinlog  /usr/local/mysql/data/binlog.000020 >a.txt

数据恢复

mysqlbinlog binlog.[0-9]* | mysql -u root -p                            
根据截止时间恢复(stop-datetime恢复的截止时间)
./mysqlbinlog --stop-datetime='2019-12-01 12:10:40' /.../binlog.000020 |mysql -u root -p  
根据时间段恢复
./mysqlbinlog  --start-datetime='...'--stop-datetime='2019-12-01 12:10:40' /.../binlog.000020 |mysql -u root -p                        
根据位置恢复(找到drop命令位置,跳过该命令)
./mysqlbinlog /usr/local/mysql/data/binlog.000020 --stop-position=1835 |mysql -u root -p                        

备份数据库

time ./mysql -h 192.168.0.201 -P 20006 -uroot -pxx  power< test.sql        
        
./mysqldump -h 192.168.0.201  -P 20006 -uroot -p123456 --no-tablespaces --column-statistics=0  t1 > test5.sql        
        
time ./mysqlpump -h 192.168.0.201  -P 20006 -uroot -p123456  --default-parallelism=6  -B t1 > test55.sql   

数据备份恢复

恢复方式
1使用mysql恢复 (如果a.sql包含库创建命令,不用指定dbname)
mysql -u root -p dbname < a.sql             (‘root’:a.sql的用户)                    
mysql -h 192.168.0.201 -P 20006 -uroot -pxx  power< test.sql   
2登录mysql使用source命令恢复
>use t1;                    
>source /.../a.sql;                    
3直接恢复到数据库目录 (仅支持MyISAM类型)
4mysqlhotcopy快速恢复 (必须指定文件的所有者)
chown -R mysqla.mysqla /…/dbname 
cp -R /…/daname /…/mysql/data                       
5使用 LOAD DATA 导入数据
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;                    
注意
恢复./mysqlpump -h xx  -P xx -uroot -pxx  --default-parallelism=6  -B t1 > test55.sql创建的时 ,必须创建同名数据库,或清空原有数据库,其内包含指定库名                    
                            
恢复./mysqldump -h xx  -P xx -uroot -pxx --no-tablespaces t1 > test5.sql创建的时 ,可指定任意库名,其内不包含指定库名                    

数据库迁移

同版本数据库    复制数据库文件目录 (仅支持MyISAM类型)                            
mysqldump将a.com主机数据库dbname迁到b.com
mysqldump -h a.com -u root -p dbname | mysql -h b.com -u root -p                            
mysqldump将a.com主机全部数据库迁到b.com
mysqldump -h a.com -u root -p --all-databases | mysql -h b.com -u root -p                            

导入导出

先查看配置:>show variables like '%secure%';                            
  secure_file_prive=null ––限制mysqld 不允许导入导出                            
  secure_file_priv=/path/ – --限制mysqld的导入导出只能发生在默认的/path/目录下                            
  secure_file_priv=’’ – --不对mysqld 的导入 导出做限制                            

secure_file_prive为只读变量,只能在my.cnf[mysqld]添加secure_file_prive=/xxxx路径。                            

导出

使用select语句导出 (导出默认位置:/.../mysql/data/库名( 具有写权限))
>use test02;
>select * from test02.testbak into OUTFILE ""aa.txt""; (默认无表头)"                            
使用mysqldump导出(不指定tables则导出所有表)
格式 mysqldump -T path dbname tables [options] -u root -p                            
mysqldump -T /test_sql test02 testbak -u root -p (默认无表头)                            
使用mysql导出(默认包含字段名称,--vertical多行显示 --html格式 --xml格式)
mysql -u root -p --execute="select * from table;" dbname > /test_sql/a.txt(有表头)                            
mysql -u root -p --vertical --execute="select * from testbak;" test02 > /test_sql/a.txt                            
mysql -u root -p --html --execute="select * from testbak;" test02 > /test_sql/a.html                            
mysql -u root -p --xml --execute="select * from testbak;" test02 > /test_sql/a.xml                            

导入

使用 load data infile 导入文本文件
LOAD DATA INFILE 'file.txt' INTO TABLE db.table [options] [ignore number lines];                            
>LOAD DATA INFILE '/test_sql/a.txt' INTO TABLE test03.loadfile;                            
#提前创建test.loadfile库表,并且表头符合a.txt,如果a.txt含表头,需删除表头                            
使用mysqlimport导入文本文件
格式 mysqlimport -u root -p dbname file.txt [options]                            
mysqlimport -u root -p test05 /test_sql/a.txt                            
# 必须提前创建test05.a(table名称与file一致,table表头符合file.txt)                            

使用sql文件创建表

use db;        
source /…/xx.sql;   #(source /var/log/mysql/a.sql;)        
成功返回 :Query OK, 0 rows affected,        
show tables;查看表情况        

批量删除表

先查询表名称,格式化输出        
    Select CONCAT( 'drop table ', table_name, ';' ) FROM information_schema.tables Where table_name LIKE '8%';    
        # 有重复项,会多删除。注意
再复制输出,运行
上一篇:JavaScript函数


下一篇:ThinkAdmin 服务器 Nginx 配置