MySQL 数据导入导出

Question

load data local infile 'c:/country.csv'
into table country
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
ignore 1 rows

-- 全部
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT},
        [, col_name={expr | DEFAULT}] ...]

备份和恢复

mysqldump

mysqldump [OPTIONS] database [tables]

mysqldump 的基本用法: mysqldump [OPTIONS] database [tables] ,其中的一些可选项 OPTIONS 为:

• -d; --no-data : -d option means "without data", default false
• --comments : Add comments to dump file, default true
• --create-options : Include all MySQL-specific table options in CREATE TABLE statements, default true
• --where : Dump only rows selected by given WHERE condition
• --compact : Produce more compact output, default false. 让输出更加紧凑
• --add-drop-table : Add DROP TABLE statement before each CREATE TABLE statement, default true 但是使用了 --compact 参数之后就不会为 false
• --complete-insert : Use complete INSERT statements that include column names, default false
• --skip-add-drop-table : Do not add a DROP TABLE statement before each CREATE TABLE statement, default false
• --skip-add-locks : Do not add locks, default false
• --skip-comments : Do not add comments to dump file, default false
• --skip-extended-insert : Turn off extended-insert, default false. 把多值插入改成追条插入
• --lock-tables : Lock all tables before dumping them
• --lock-all-tables : Lock all tables across all databases
• --add-drop-database :

如果想要导出全库的同时带有一个 drop database if exists <dbname> 以及 create database <dbname> if no exists : mysqldump --add-drop-database -uroot -pmysql --databases dbname

备份 docker 中的 mysql 数据

#!/bin/bash
backupDir=/srv/mysql/backupDir
backupDate=$(date +%Y%m%d)
backupKeepday=30

if [ ! -d $backupDir ]; then
  mkdir $backupDir
fi

find $backupDir/*.gz -ctime +$backupKeepday -delete

docker exec mysql \
sh -c 'exec mysqldump test -uroot -p"$MYSQL_ROOT_PASSWORD"' \
| gzip -c > $backupDir/$backupDate.gz

避免使用 mysqldump 的时候锁数据库锁表

mysqldump 运行的时候默认会锁数据库，要想实现不锁表， 这里 :

• MyISAM： 使用 --lock-tables=false
• MyISAM： 使用 --single-transaction=true