MySQL 8 教程

MySQLdump命令的基本语法

mysqldump -u username -p database_name > backup_file.sql

备份单个数据库

-- 备份单个数据库
docker exec -it mysql mysqldump -u root -p hr > hr_backup.sql

-- 备份数据库并包含存储过程、函数和事件
mysqldump -u root -p --routines --events --triggers database_name > backup_file.sql

-- 示例：备份hr数据库并包含所有对象
mysqldump -u root -p --routines --events --triggers hr > hr_backup_all.sql

备份多个数据库

-- 备份多个数据库
mysqldump -u root -p --databases database1 database2 > multi_database_backup.sql

-- 示例：备份hr和sales数据库
mysqldump -u root -p --databases hr sales > hr_sales_backup.sql

备份所有数据库

-- 备份所有数据库
mysqldump -u root -p --all-databases > all_databases_backup.sql

备份单个表

-- 备份单个表
mysqldump -u root -p database_name table_name > table_backup.sql

-- 示例：备份hr数据库中的employees表
mysqldump -u root -p hr employees > employees_backup.sql

备份多个表

-- 备份多个表
mysqldump -u root -p database_name table1 table2 > tables_backup.sql

-- 示例：备份hr数据库中的employees和departments表
mysqldump -u root -p hr employees departments > emp_dept_backup.sql

备份数据库结构但不包含数据

-- 备份数据库结构但不包含数据
mysqldump -u root -p --no-data database_name > structure_backup.sql

-- 示例：备份hr数据库的结构
mysqldump -u root -p --no-data hr > hr_structure.sql

备份数据库数据但不包含结构

-- 备份数据库数据但不包含结构
mysqldump -u root -p --no-create-info database_name > data_backup.sql

-- 示例：备份hr数据库的数据
mysqldump -u root -p --no-create-info hr > hr_data.sql

使用压缩备份

-- 使用gzip压缩备份
mysqldump -u root -p database_name | gzip > backup_file.sql.gz

-- 示例：压缩备份hr数据库
mysqldump -u root -p hr | gzip > hr_backup.sql.gz

-- 使用bzip2压缩备份
mysqldump -u root -p database_name | bzip2 > backup_file.sql.bz2

-- 示例：使用bzip2压缩备份hr数据库
mysqldump -u root -p hr | bzip2 > hr_backup.sql.bz2

直接复制数据库文件的步骤

1. 停止MySQL服务器：确保在复制数据库文件之前，MySQL服务器已经停止运行
2. 复制数据库文件：将MySQL数据目录中的数据库文件夹复制到备份位置
3. 重启MySQL服务器：完成复制后，重启MySQL服务器

直接复制数据库文件的示例

-- 在Linux系统上停止MySQL服务器
service mysql stop

-- 复制数据库文件到备份位置
cp -r /var/lib/mysql/database_name /backup/directory/

-- 重启MySQL服务器
service mysql start

-- 在Windows系统上，使用命令提示符或文件资源管理器复制数据库文件
-- 停止MySQL服务
net stop MySQL80

-- 复制数据库文件到备份位置
xcopy "C:\ProgramData\MySQL\MySQL Server 8.0\Data\database_name" "D:\backup\directory" /E /I

-- 重启MySQL服务
net start MySQL80

锁定表并复制数据库文件

如果不能停止MySQL服务器，可以使用FLUSH TABLES WITH READ LOCK语句锁定所有表，然后复制数据库文件。

-- 连接到MySQL服务器
mysql -u root -p

-- 锁定所有表
FLUSH TABLES WITH READ LOCK;

-- 在另一个终端窗口中复制数据库文件
tar -cvzf /backup/directory/database_name_backup.tar.gz /var/lib/mysql/database_name

-- 解锁表
UNLOCK TABLES;

MySQLhotcopy工具的基本语法

mysqlhotcopy -u username -p password database_name backup_directory

使用MySQLhotcopy工具备份数据库

-- 备份单个数据库
mysqlhotcopy -u root -p password hr /backup/mysql/

-- 备份多个数据库
mysqlhotcopy -u root -p password database1 database2 /backup/directory/

-- 使用正则表达式备份匹配的数据库
mysqlhotcopy -u root -p password "^db_" /backup/directory/

使用MySQL命令恢复数据库的基本语法

mysql -u username -p database_name < backup_file.sql

恢复单个数据库

-- 恢复单个数据库
mysql -u root -p database_name < backup_file.sql

-- 示例：恢复hr数据库
mysql -u root -p hr < hr_backup.sql

-- 如果数据库不存在，需要先创建数据库
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS database_name;"
mysql -u root -p database_name < backup_file.sql

恢复压缩的备份文件

-- 恢复gzip压缩的备份文件
gunzip < backup_file.sql.gz | mysql -u root -p database_name

-- 恢复bzip2压缩的备份文件
bunzip2 < backup_file.sql.bz2 | mysql -u root -p database_name

恢复所有数据库

-- 恢复所有数据库
mysql -u root -p < all_databases_backup.sql

直接复制数据库文件恢复的步骤

1. 停止MySQL服务器：确保在复制数据库文件之前，MySQL服务器已经停止运行
2. 复制备份文件：将备份的数据库文件夹复制到MySQL数据目录
3. 设置文件权限：确保复制的文件具有正确的所有权和权限
4. 重启MySQL服务器：完成复制和权限设置后，重启MySQL服务器

直接复制数据库文件恢复的示例

-- 在Linux系统上停止MySQL服务器
service mysql stop

-- 复制备份的数据库文件到MySQL数据目录
cp -r /backup/directory/database_name /var/lib/mysql/

-- 设置文件权限
chown -R mysql:mysql /var/lib/mysql/database_name

-- 重启MySQL服务器
service mysql start

-- 在Windows系统上，使用命令提示符或文件资源管理器复制数据库文件
-- 停止MySQL服务
net stop MySQL80

-- 复制备份的数据库文件到MySQL数据目录
xcopy "D:\backup\directory\database_name" "C:\ProgramData\MySQL\MySQL Server 8.0\Data" /E /I

-- 重启MySQL服务
net start MySQL80

MySQLhotcopy恢复的步骤

1. 停止MySQL服务器：确保在复制数据库文件之前，MySQL服务器已经停止运行
2. 复制备份文件：将使用mysqlhotcopy备份的数据库文件夹复制到MySQL数据目录
3. 设置文件权限：确保复制的文件具有正确的所有权和权限
4. 重启MySQL服务器：完成复制和权限设置后，重启MySQL服务器

-- 在Linux系统上的示例
-- 停止MySQL服务器
service mysql stop

-- 复制mysqlhotcopy备份的数据库文件
cp -r /backup/mysql/database_name /var/lib/mysql/

-- 设置文件权限
chown -R mysql:mysql /var/lib/mysql/database_name

-- 重启MySQL服务器
service mysql start

相同版本MySQL数据库迁移的步骤

1. 在源服务器上备份数据库：使用mysqldump命令备份需要迁移的数据库
2. 将备份文件传输到目标服务器：使用FTP、SCP等工具将备份文件传输到目标服务器
3. 在目标服务器上创建数据库：如果目标服务器上不存在同名数据库，需要先创建
4. 在目标服务器上恢复数据：使用mysql命令将备份数据恢复到目标数据库

-- 在源服务器上备份数据库
mysqldump -u root -p --opt database_name > database_backup.sql

-- 使用SCP将备份文件传输到目标服务器
scp database_backup.sql user@target_server:/path/to/backup/

-- 在目标服务器上创建数据库
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS database_name;"

-- 在目标服务器上恢复数据
mysql -u root -p database_name < /path/to/backup/database_backup.sql

不同版本MySQL数据库迁移的步骤

1. 在源服务器上备份数据库：使用mysqldump命令备份需要迁移的数据库
2. 将备份文件传输到目标服务器：使用FTP、SCP等工具将备份文件传输到目标服务器
3. 检查版本兼容性：查看MySQL官方文档，了解两个版本之间的兼容性问题
4. 在目标服务器上创建数据库：如果目标服务器上不存在同名数据库，需要先创建
5. 在目标服务器上恢复数据：使用新版本的mysql命令将备份数据恢复到目标数据库
6. 运行mysql_upgrade：如果从旧版本迁移到新版本，建议运行mysql_upgrade命令更新系统表

-- 在源服务器（MySQL 5.7）上备份数据库
mysqldump -u root -p --opt database_name > database_backup.sql

-- 使用SCP将备份文件传输到目标服务器
scp database_backup.sql user@target_server:/path/to/backup/

-- 在目标服务器（MySQL 8.0）上创建数据库
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS database_name;"

-- 在目标服务器上恢复数据
mysql -u root -p database_name < /path/to/backup/database_backup.sql

-- 运行mysql_upgrade命令更新系统表
mysql_upgrade -u root -p

不同数据库之间迁移的步骤

1. 分析源数据库结构：了解源数据库的表结构、索引、约束、存储过程、函数等
2. 设计目标MySQL数据库结构：根据源数据库的结构，设计MySQL的数据库结构，考虑数据类型映射问题
3. 创建目标MySQL数据库结构：在MySQL中创建数据库、表、索引、约束等
4. 导出源数据库数据：使用源数据库提供的工具导出数据，如CSV文件
5. 转换数据格式（如需）：将导出的数据转换为MySQL兼容的格式
6. 导入数据到MySQL：使用LOAD DATA INFILE或其他工具将数据导入到MySQL
7. 迁移存储过程和函数：手动转换和迁移存储过程、函数等
8. 测试和验证：测试迁移后的数据是否正确，应用程序是否能够正常工作

使用工具进行不同数据库之间的迁移

除了手动迁移外，还可以使用一些工具来简化不同数据库之间的迁移过程，如：

• MySQL Workbench：MySQL官方提供的图形化工具，支持从多种数据库迁移到MySQL
• mysqldump：对于支持导出为SQL格式的数据库，可以使用mysqldump导入
• 第三方工具：如Navicat、Toad等，提供了数据库迁移功能

-- 使用MySQL Workbench进行数据库迁移的基本步骤：
-- 1. 打开MySQL Workbench
-- 2. 点击"Database" -> "Migration Wizard"
-- 3. 选择源数据库类型和连接信息
-- 4. 选择目标MySQL数据库连接信息
-- 5. 选择要迁移的对象（表、视图、存储过程等）
-- 6. 执行迁移
-- 7. 验证迁移结果

SELECT...INTO OUTFILE语句的基本语法

SELECT column1, column2, ...
INTO OUTFILE 'file_path'
[CHARACTER SET charset_name]
[FIELDS
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
]
[LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
]
FROM table_name
[WHERE condition];

导出表数据为CSV文件

-- 导出表数据为CSV文件
SELECT *
INTO OUTFILE '/path/to/export/employees.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM employees;

-- 示例：导出hr数据库中employees表的数据为CSV文件
SELECT employee_id, first_name, last_name, email, hire_date, salary
INTO OUTFILE '/tmp/employees_data.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM hr.employees
WHERE department_id = 10;

导出表数据为制表符分隔的文本文件

-- 导出表数据为制表符分隔的文本文件
SELECT *
INTO OUTFILE '/path/to/export/employees.txt'
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
FROM employees;

使用MySQLdump命令导出文本文件的基本语法

mysqldump -u username -p --tab=directory_path database_name [table_name]

使用MySQLdump导出表结构和数据

-- 使用MySQLdump导出表结构和数据
mysqldump -u root -p --tab=/tmp/hr_backup hr employees

-- 导出的文件：
-- /tmp/hr_backup/employees.sql （表结构）
-- /tmp/hr_backup/employees.txt （表数据）

自定义导出的数据格式

-- 自定义导出的数据格式
mysqldump -u root -p --tab=/tmp/hr_backup --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=\n hr employees

使用MySQL命令导出文本文件的基本语法

mysql -u username -p -e "SELECT statement" database_name > file_path

使用MySQL命令导出表数据

-- 导出表数据为文本文件
mysql -u root -p -e "SELECT * FROM employees;" hr > /tmp/employees_data.txt

-- 使用-e选项执行多条SQL语句
mysql -u root -p -e "USE hr; SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10;" > /tmp/department_10_employees.txt

LOAD DATA INFILE语句的基本语法

LOAD DATA [LOCAL] INFILE 'file_path'
INTO TABLE table_name
[CHARACTER SET charset_name]
[FIELDS
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
]
[LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
]
[IGNORE number LINES]
[(column1, column2, ...)];

导入CSV文件数据

-- 导入CSV文件数据到表中
LOAD DATA INFILE '/path/to/import/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(employee_id, first_name, last_name, email, hire_date, salary);

-- 示例：导入CSV文件数据到hr数据库的employees表
LOAD DATA INFILE '/tmp/employees_data.csv'
INTO TABLE hr.employees
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES -- 忽略CSV文件的标题行
(employee_id, first_name, last_name, email, hire_date, salary);

使用LOCAL选项导入本地文件

-- 使用LOCAL选项导入本地文件
LOAD DATA LOCAL INFILE '/path/to/local/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

MySQLimport命令的基本语法

mysqlimport -u username -p [options] database_name file_name

使用MySQLimport导入CSV文件

-- 使用MySQLimport导入CSV文件
mysqlimport -u root -p --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=\n hr /path/to/import/employees.csv

-- 示例：导入CSV文件到hr数据库的employees表
mysqlimport -u root -p --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=\n --local hr /tmp/employees_data.csv

-- 使用--ignore-lines选项忽略文件的标题行
mysqlimport -u root -p --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=\n --ignore-lines=1 hr /tmp/employees_data.csv

使用MySQLimport导入制表符分隔的文件

-- 使用MySQLimport导入制表符分隔的文件
mysqlimport -u root -p --fields-terminated-by=\t --lines-terminated-by=\n hr /path/to/import/employees.txt