Mysql服务器间迁移

2019-10-22 17:00:13 浏览数 (1)

准备配置

代码语言：javascript复制
set global max_allowed_packet=300000000;
set global net_buffer_length=300000;
set global interactive_timeout=35900000;
set global wait_timeout=35900000;

查看数据库各表大小

代码语言：javascript复制
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;

导出数据

导出数据库

代码语言：javascript复制
mysqldump -u root -p123456 testdb > /data/db/testdb.sql

导出一个表

代码语言：javascript复制
mysqldump -u root -p123456 testdb tablename> dbname_users.sql

服务器间复制

从本地复制到远程

复制单个文件

代码语言：javascript复制
scp /data/db/testdb.sql root@111.111.111.111:/data/db/testdb.sql

复制目录

代码语言：javascript复制
scp -r /data/db/* root@111.111.111.111:/data/db/

导入数据

方式一

可以显示导入过程

登录

代码语言：javascript复制
mysql -uroot -p

回车输入密码

创建数据库导入

代码语言：javascript复制
create database testdb;
use testdb;
set names utf8;
source testdb.sql

方式二

无法显示导入过程

导入数据库

代码语言：javascript复制
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. 导出与导入表结构

导出原数据库表结构

代码语言：javascript复制
mysqldump -d -uroot -p12345678 xhkj_ques_0923 > xhkj_ques_0923_base.sql

新库导入表结构

代码语言：javascript复制
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

2. 导出表数据

1) 删除之前导出的文件

代码语言：javascript复制
rm -rf /var/lib/mysql-files/*.csv
ls /var/lib/mysql-files/

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

代码语言：javascript复制
select table_name from information_schema.tables where table_schema='xhkj_ques_0923' and table_type='base table';

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

3) 导出数据

Linux环境

创建 mysql_export.sh文件

代码语言：javascript复制
#!/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

执行

代码语言：javascript复制
chmod  x mysql_export.sh
sh ./mysql_export.sh

Win环境

代码语言：javascript复制
@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

3. 导入表数据

1) 暂时禁用外键检查

代码语言：javascript复制
SET GLOBAL FOREIGN_KEY_CHECKS = 0;

或者删除外键

代码语言：javascript复制
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文件

代码语言：javascript复制
#!/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

执行

代码语言：javascript复制
chmod  x mysql_import.sh
sh ./mysql_import.sh

3) 导入后启用外键检查

代码语言：javascript复制
SET GLOBAL FOREIGN_KEY_CHECKS = 1;

4. 常见错误

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

查看可导入的文件夹

代码语言：javascript复制
show variables LIKE '%secure_file_priv%';

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

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

代码语言：javascript复制
# secure_file_priv的值没有具体值时，mysqld的导入或导出不限制文件目录
secure_file_priv =

或者

代码语言：javascript复制
# 限制mysqld 的导入或导出只能在指定目录下
secure_file_priv = 指定目录

重启mysql，再次查看

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

查看数据行数

方式一 (精确)

生成查询各表条数的SQL

代码语言：javascript复制
select concat(
    'select "',
    TABLE_name,
    '", count(*) from ',
    TABLE_SCHEMA,
    '.',
    TABLE_name,
    ' union '
) from information_schema.tables
where TABLE_SCHEMA='testdb';

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

方式二 (不精确)

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

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

代码语言：javascript复制
use information_schema;
select table_name,table_rows from tables
where TABLE_SCHEMA = 'testdb'
order by table_name asc;

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

代码语言：javascript复制
use information_schema;
select table_name,table_rows from tables
where TABLE_SCHEMA = 'testdb'
order by table_rows desc;

查看总条数

代码语言：javascript复制
USE information_schema;
SELECT SUM(TABLE_ROWS) FROM TABLES WHERE TABLE_SCHEMA = 'testdb';

常见错误

导入失败

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

打开配置文件

代码语言：javascript复制
vi /etc/my.cnf

添加如下

代码语言：javascript复制
[mysqldump] 
max_allowed_packet = 1G
数据库 云数据库SQLServer sql sqlserver linux

0 人点赞