准备配置

set global max_allowed_packet=300000000;
set global net_buffer_length=300000;
set global interactive_timeout=35900000;
set global wait_timeout=35900000;

查看数据库各表大小

SELECT table_name, data_length + index_length AS len, table_rows,
     CONCAT(ROUND((data_length + index_length)/1024/1024,2),'MB') AS size,table_comment
FROM information_schema.tables
WHERE table_schema = 'xhkjedu_question'
ORDER BY len DESC;

导出数据

导出数据库

1	mysqldump -v -u root -p testdb > /data/db/testdb.sql

查看导出的文件

ls -lh

导出一个表

1	mysqldump -u root -p testdb tablename> dbname_users.sql

服务器间复制

从本地复制到远程

复制单个文件

1	scp /data/db/testdb.sql root@111.111.111.111:/data/db/testdb.sql

复制目录

1	scp -r /data/db/* root@111.111.111.111:/data/db/

导入数据

方式一

可以显示导入过程

1	mysql -uroot -p

回车输入密码

创建数据库导入

create database testdb;
use testdb;
set names utf8;
source testdb.sql

方式二

无法显示导入过程

导入数据库

1	mysql -u root -p 123456 testdb < "/data/db/testdb.sql"

以CSV导入导出

导入过程中出现的问题

Invalid utf8 character string: ‘’

数据库表非utf8编码的修改为utf8编码
Can’t connect to local MySQL server through socket ‘/data/mysql/mysql.sock’ (111)

导入时不要进行数据库操作

导出与导入表结构

导出原数据库表结构

1	mysqldump -d -uroot -p12345678 xhkj_ques_0923 > xhkj_ques_0923_base.sql

新库导入表结构

mysql -u root -p
mysql> create database xhkj_ques_0924;
mysql> use xhkj_ques_0924;
mysql> set names utf8;
mysql> source xhkj_ques_0923_base.sql
mysql> quit

导出表数据

1) 删除之前导出的文件

1 2	rm -rf /var/lib/mysql-files/*.csv ls /var/lib/mysql-files/

2) 把数据库所有表表名保存到tables.txt

1	select table_name from information_schema.tables where table_schema='xhkj_ques_0923' and table_type='base table';

注意文件的最后一行要有一个空行 否则无法导入最后一个表

3) 导出数据

Linux环境

创建 mysql_export.sh文件

#!/bin/bash
while read line
do
echo "======> export $line"
mysql -uroot -p12345678 xhkj_ques_0923 -e "SELECT * INTO OUTFILE '/var/lib/mysql-files/$line.csv' FIELDS TERMINATED BY ',' FROM $line"
done < tables.txt

执行

1 2	chmod +x mysql_export.sh sh ./mysql_export.sh

Win环境

@echo off & setlocal enabledelayedexpansion

for /f %%i in (tables.txt) do ( set table=%%i
echo "======> !table!"
mysql -uroot -p12345678 xhkj_ques_0923 -e "SELECT * INTO OUTFILE 'F:/MySQL/Uploads/!table!.csv' FIELDS TERMINATED BY ',' FROM !table!"
)
pause

导入表数据

1) 暂时禁用外键检查

1	SET GLOBAL FOREIGN_KEY_CHECKS = 0;

或者删除外键

1
2
3

SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME,' ;') 
FROM information_schema.TABLE_CONSTRAINTS c 
WHERE c.TABLE_SCHEMA='库名' AND c.CONSTRAINT_TYPE='FOREIGN KEY';

然后运行生成的SQL

2) 导入数据

Linux环境

创建mysql_import.sh文件

#!/bin/bash
while read line
do
echo "======> import $line"
mysql -uroot -p12345678 xhkj_ques_0924 -e "LOAD DATA INFILE '/var/lib/mysql-files/$line.csv' INTO TABLE $line FIELDS TERMINATED BY ','"
done < tables.txt

执行

1 2	chmod +x mysql_import.sh sh ./mysql_import.sh

3) 导入后启用外键检查

1	SET GLOBAL FOREIGN_KEY_CHECKS = 1;

常见错误

The MySQL server is running with the –secure-file-priv option

查看可导入的文件夹

1	show variables LIKE '%secure_file_priv%';

NULL 表示限制mysql 不允许导入或者导出

修改mysql配置文件/etc/my.cnf 或 my.ini，在[mysqld]下添加

1 2	# secure_file_priv的值没有具体值时，mysqld的导入或导出不限制文件目录 secure_file_priv =

或者

1 2	# 限制mysqld 的导入或导出只能在指定目录下 secure_file_priv = 指定目录

重启mysql，再次查看

数据导入之前需在新机器上创建表结构，12G的数据导出用时3分钟左右，导入用时4分钟左右

导出导入时编码变更

只导出结构
1
mysqldump --opt -d 数据库名 -u root -p > aaa.sql
替换里面的
- utf8mb3_general_ci为utf8mb4_0900_ai_ci；
- utf8mb3为utf8mb4

只导出数据

1	mysqldump -t 数据库名 -uroot -p > bbb.sql

导入

可以显示导入过程

1	mysql -uroot -p

回车输入密码

创建数据库导入

create database testdb;
use testdb;
set names utf8;
source aaa.sql

查看数据行数

方式一 (精确)

生成查询各表条数的SQL

select concat(
    'select "',
    TABLE_name,
    '", count(*) from ',
    TABLE_SCHEMA,
    '.',
    TABLE_name,
    ' union '
) from information_schema.tables
where TABLE_SCHEMA='testdb';

把查询出来的结果整体复制后删除最后一个union 整体全选运行即可

方式二 (不精确)

对于InnoDB表，table_rows行计数仅是大概估计值,所以结果并不精确

查看各表数据行数(按表名排序)

use information_schema;
select table_name,table_rows from tables
where TABLE_SCHEMA = 'testdb'
order by table_name asc;

查看各表数据行数(按数据条数排序)

use information_schema;
select table_name,table_rows from tables
where TABLE_SCHEMA = 'testdb'
order by table_rows desc;

查看总条数

1 2	USE information_schema; SELECT SUM(TABLE_ROWS) FROM TABLES WHERE TABLE_SCHEMA = 'testdb';

修改数据库名称

思路：通过修改数据库表名时指定库名，来起到修改数据库名的目的。

修改表名SQL

1	RENAME TABLE db1.c_classroom TO db2.c_classroom;

假如我们要把db1名称改为db2

首先创建数据库db2

1	CREATE DATABASE db2;

生成执行的SQL

SELECT CONCAT(
    'RENAME TABLE ',
    TABLE_SCHEMA,
    '.',
    TABLE_name,
    ' TO db2.',
    TABLE_name,
    ';'
) FROM information_schema.tables
WHERE TABLE_SCHEMA='db1';

复制生成的SQL并执行

执行成功后db1的表就空了删除db1

1	DROP DATABASE db1;

常见错误

导入失败

Error 2020: Got packet bigger than ‘max_allowed_packet’ bytes when dumping table

打开配置文件

1	vi /etc/my.cnf

添加如下

1 2	[mysqldump] max_allowed_packet = 1G

我是码客，我是全栈工程师，我为自己代言。

Mysql服务器间迁移

准备配置

查看数据库各表大小

导出数据

服务器间复制

导入数据

方式一

方式二

以CSV导入导出

导出与导入表结构

导出表数据

导入表数据

常见错误

导出导入时编码变更

查看数据行数

方式一 (精确)

方式二 (不精确)

修改数据库名称

常见错误