最近在迁移数据库,网上查询了一下觉得还ok,实践之后发现还是遇到了不少的坑。
导出很简单 mysqldump 语句就可以
mysqldump --column-statistics=0 --opt --user=root --password=mima --host=127.0.0.1 --port=3306 --protocol=tcp --default-character-set=utf8mb4 --events database1 (数据库名) table2(备份表名,不写备份除排除表的所有表) --ignore-table=table1 (排除的表名)
其中 --column-statistics=0 mysql8及以上需要加上,不然会报
Unknown table ‘COLUMN_STATISTICS’ in information_schema
就此,我得到了一个很大的sql文件,接下来就是把它导到目标库里,网上搜到了两种方式:
(1)mysql -h127.0.0.1 -uroot -pmima database_name < databases.sql
(2)mysql -h127.0.0.1 -uroot -p 输入密码后,use database_name 切换数据库
然后 source databases.sql
导入的时候遇到了一些问题:
报错1:
ERROR 1231 (42000): Variable ‘time_zone’ can’t be set to the value of ‘NULL’
ERROR 1231 (42000): Variable ‘sql_mode’ can’t be set to the value of ‘NULL’
ERROR 1231 (42000): Variable ‘foreign_key_checks’ can’t be set to the value of ‘NULL’
ERROR 1231 (42000): Variable ‘unique_checks’ can’t be set to the value of ‘NULL’
ERROR 1231 (42000): Variable ‘character_set_client’ can’t be set to the value of ‘NULL’
Query OK, 0 rows affected (0.00 sec)
这是导入的文件大小超过了最高限制,show VARIABLES like'%max_allowed_packet%'可以查看导入文件限制阈值。
show VARIABLES like'%max_allowed_packet%'
可以修改max_allowed_packet的值:
我先试了 语句设置 set global max_allowed_packet = 2*1024*1024*10,重启mysql后服务没生效,后来试了改my.ini文件,重启后才修改成功
global max_allowed_packet的 最大值是1G,sql文件比这个还大的话只能拆分了。
报错2:
这个是编码格式的问题,上边用的导入语句中,需要设置编码格式
(1)mysql -h127.0.0.1 -uroot -pmima --default-character-set=utf8mb4 database_name < databases.sql
(2)mysql --default-character-set=utf8mb4 -h127.0.0.1 -uroot -p 输入密码后,use database_name 切换数据库
这样才导进来。
另外我又试了一下直接复制mysql目录下的数据库文件,其中每个表对应2个文件,一个.frm文件(结构),一个.ibd文件(数据)
尝试把这2个文件直接复制过去,在navicat里不显示这个表,但是也不能新建这个表了;
然后自己新建表,只把ibd文件复制过去,仍然不行,
后来复制这个库,直接把Mysql服务干死了,
最后复制整个Data文件,才可以使用,感觉这样复制还算快,不过没有具体研究,不知道会不会有什么坑。