迁移Mysql遇到的坑

最近在做些机器的迁移，其中就包括Mysql数据库的迁移，在迁移的过程中遇到一个坑，在这里和大家分享一下。我们使用的Mysql的版本是5.6，我将老的机器上的Mysql 5.6 的安装包直接拷贝到新的机器上安装好，并将相应配置文件也拷贝到了新机器上，使用了相同的命令启动Mysql服务结果报错了。报错内容如下：

Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

于是通过该关键字google了一下，得知Mysql初次启动的时候需要初始化data 目录，从而新建一些系统表。

As suggested above, i had similar issue with mysql-5.7.18, 
i did this in this way

1. Executed this command from "MYSQL_HOME\bin\mysqld.exe --initialize-insecure"
2. then started "MYSQL_HOME\bin\mysqld.exe"
3. Connect workbench to this localhost:3306 with username 'root'
4. then executed this query "SET PASSWORD FOR 'root'@'localhost' = 'root';"

password was also updated successfully.

于是我就按照上面的步骤进行data 目录的初始化，结果在第一步就报错了，unknown option --initialize-insecure， 经过查询得知我忽略了mysql 的版本号，5.7版本之后是可以通过 --initialize-insecure 进行data目录的初始化，但是5.6 版本并不支持这个参数，于是就去查了一下5.6的官方文档。其中提到了一个在Linux下初始化Data目录的方法，并没有windows系统下初始化data目录的方法。

1.Change location to the top-level directory of your MySQL installation, which is typically /usr/local/mysql (adjust the path name for your system as necessary):

    cd /usr/local/mysql

2. You will find several files and subdirectories inside the directory, including the bin and scripts subdirectories, which contain the server as well as client and utility programs.Initialize the data directory, including the mysql database containing the initial MySQL grant tables that determine how users are permitted to connect to the server. For example:

    scripts/mysql_install_db --user=mysql

我还特意到安装目录下找了一下这个scripts目录结果没找到，但是在安装目录下找到了一个data目录，该目录下有两个文件夹：

image.png

通过这两个目录在联想到之前报错信息，我确认这应该就是mysql默认的系统表，于是我将该data目录下的所有文件拷贝到我配置的data目录下，然后启动mysql服务，直接成功了。折腾这么久结果需要的东西正好在安装目录下，不过通过这次踩坑，对mysql在windows下的安装和启动有了更深的了解。