rightphoenix.blogg.se

More background and motivation for why 'create new - copy - switch' is inefficient can be found in a blogpost I wrote on this topic. path/to/desired/locationĪnd import them: ALTER TABLE `table_name` IMPORT TABLESPACE The my.ini will be located in the MySQL program folder, which would be wherever it got installed. While keeping the connection open, move the tablespace files in a shell: $ mv /var/lib/mysql/database_name/table_name. Open up MySQLs configuration file into Notepad: my.ini. In this example, /mysql/ is used: mkdir -p /mysql Copy the database files from the old location to the new location: cp -R /var/lib/mysql/ /mysql/ Change the ownership of this location to allow access by the mysql user and group. So far the unspecific part of the answer. Create a new directory for the new location of the database(s). It stores the files in a specific directory that has the system variable 'datadir'. Basically mySQL stores data in files in your hard disk. mysql> use db1 mysql> show tables mysql> use db2 mysql> show tables mysql> use db3 mysql> show tables mysql> use tmpdb1 mysql> show tables mysql> use tmpdb2 mysql> show tables mysql> use tmpdb3 mysql> show tables Next, drop the original databases. As mentioned by Gergoes link, this is basically about modifying /etc/mysql/my.cnf and set a new value for datadir in the mysqld section. My answer is based on the linux file system.

to copy the structure of the table first and then you can use select from into outfile 'filename' to unload all the data from one server/disk and then can use load data local infile 'filename' into table to load the data in table or you can take mysqldump of the table only which. 29 Well, actually there is a potential Ubuntu specific answer to this question.

This is much more efficient on large and/or heavily indexed tables as MySQL does not have to redo work it has already done. I have dealt with this problem myself, and eventually found a more elegant solution than 'create new - copy - switch': detaching, moving and re-importing the underlying tablespace files.