I added 2 SSDs in RAID 1 configuration and mounted them as /ssd.
At first, it was very important that two tables from a single database are moved to faster drives so I used the pt-online-schema-change tool to change their datadir to /ssd/mysql
After some time (about 24 hrs) we were able to bring the server down for a couple of minutes so I:
- stopped the server
- copied /var/lib/mysql to /ssd/mysql, keeping file and directory ownership of the mysql user
- changed the datadir in my.cnf to /ssd/mysql
- started the server and got errors on tables that were previously moved with pt-online-schema-change
Here is the error log:
2017-08-30 12:55:22 2384 [ERROR] InnoDB: A tablespace for dbname/table_name has been found in multiple places;
2017-08-30 12:55:22 2384 [ERROR] InnoDB: Default location; ./dbname/table_name.ibd, LSN=0, Space ID=806060, Flags=1024
2017-08-30 12:55:22 2384 [ERROR] InnoDB: Dictionary location; /ssd/mysql/dbname/table_name.ibd, LSN=0, Space ID=806060, Flags=1024
2017-08-30 12:55:22 2384 [ERROR] InnoDB: Will not open the tablespace for ‘dbname/table_name’
2017-08-30 12:55:22 7f8967ff2700 InnoDB: cannot calculate statistics for table “dbname”.“table_name” because the .ibd file is missing. For help, please refer to [url]http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html[/url]
2017-08-30 12:55:23 7f8967ead700 InnoDB: cannot calculate statistics for table “dbname”.“table_name2” because the .ibd file is missing. For help, please refer to [url]http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html[/url]
and so on.
what should I do? Move those tables back to the original location with pt-online-schema-change and repeat the steps above?