How do I make my databases in MySQL go on another drive on my computer?

Question

Say my website takes in lots of data from its users, and it goes to MySQL on my computer, but my computer runs out of space. If I connect a hard drive to my server computer, can I make it so I can put a new database on that hard drive, and all the data gets stored there? It would obviously be attached at all times.

Answer 1

You can export the database via PhpMyAdmin and the pass the saved database file onto another computer.

Exporting the database: https://serverpilot.io/community/articles/how-to-export-a-database-using-phpmyadmin.html

Importing the database: https://serverpilot.io/community/articles/how-to-import-a-database-using-phpmyadmin.html

Answer 2

This probably belongs on https://dba.stackexchange.com/ instead, and indeed there's quite a bit of in-depth discussion there about some of the techniques you could use.

One thing I've found helpful when working with large but temporary datasets is to enable innodb_file_per_table which — in my case — helps reclaim disk space when removing these temporary databases.

Moving the entire datadir

You can move the entire directory that MySQL uses to store files; this is called the datadir. Stop the MySQL daemon, move the folder, edit my.cnf to refer datadir = to the new folder location, and start the daemon.

File-per-table tablespace outside of the datadir

https://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html You can use the file-per-table tablespace configuration (innodb_file_per_table in the [mysqld] portion of the configuration file) in conjunction with CREATE TABLE to actually place a tablespace outside the datadir. When you have innodb_file_per_table enabled, you can use statements like CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/alternative/directory'; to put the datadir for that tablespace in a different directory.

Partitioning

Using partitioning, you can break up the databases, tables, and columns in to different storage partitions. This is probably not what you're looking for in this case because they still reside in the datadir. https://dev.mysql.com/doc/refman/5.7/en/partitioning.html