文章内容
2018/11/6 11:17:35,作 者: 黄兵
mysql 数据库迁移的一次记录
最近网站性能急剧下降，主要是由于有人使用爬虫抓取网站导致CPU 经常跑满，也采取了一些反制措施，但是还是有必要把数据库单独拿出来，放在宁外一台服务器上面。具体操作记录如下：
登陆服务器之后，登陆mysql数据库：
mysql -uroot -p # 查看数据库 SHOW DATABASES;
把所有数据库名保存下来，之后开始备份数据库，使用的是mysqldump命令。
mysqldump -uroot -p Material_Development --set-gtid-purged=OFF > /home/ubuntu/dumpout.sql
其中Material_Development是数据库名，保存在/home/ubuntu/目录下面。
设置 --set-gtid-purged=OFF 的解释如下：
如果打算仅使用转储服务器中的一些数据部署新的复制从属服务器，请使用 ON。如果打算通过在拓扑中复制表来修复表，请使用 OFF。如果打算在不相交的复制拓扑之间复制表，则使用 OFF 并将继续如此。
如果是具体某个表，使用如下命令：
mysqldump -uroot -p Material_Development --set-gtid-purged=OFF table_name, table_name_1, table_name_2 > /home/ubuntu/dumpout.sql
这里可以指定多个表名，例如上面的table_name，table_name_1等。
将文件保存到本地计算机，登陆另外一台服务器之后上传，之后执行如下命令：
mysql -uroot -p Material_Development < /home/ubuntu/dumpout.sql
如果出现如下错误：
ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i dumpout.sql
如果用这个方法无法解决问题，需要使用上面的设置：--set-gtid-purged=OFF
输入密码之后数据库已经恢复了，之后我们使用客户端连接一下看是否存在问题。
不允许远程连接，应该是原来的用户名没有转移过来，需要修改。
通过查询，用户名的确是没有转移过来：
SELECT User, Host, Password FROM mysql.user;
结果如下：
只能将原来的用户新建一个，密码相同，具体操作如下：
CREATE USER 'xxx'@'%' IDENTIFIED BY 'xxxxxx';
之后将用户赋予数据库：
GRANT SELECT, INSERT, CREATE, DROP, ALTER, DELETE, INDEX, UPDATE, CREATE VIEW, REFERENCES ON database_name.* TO 'user'@'%';
这些权限根据具体业务来定，不适用root来链接，主要是为了安全，同时不赋予所有权限也是为了安全，安全在各个环节都很重要。
好了，之后再次链接一下，已经链接成功，截图如下：
其他数据库依次这样操作，最终转移完成所有数据库。
参考资料：
3、MySQL/Amazon RDS error: "you do not have SUPER privileges..."
4、Access denied; you need (at least one of) the SUPER privilege(s) for this operation
黄兵个人原创。
转载请注明出处：黄兵个人博客 - mysql 数据库迁移的一次记录
评论列表