Contents
How can I put MySQL databases in different disk partitions?
Create the new partition and filesystem. Mount it somewhere. Stop mysql and copy the entire contents of /var/lib/mysql to the new location. Be sure to preserve permissions and ownership. Delete /var/lib/mysql and create a symlink from it to the new directory. After the data is copied, mount the new filesystem directly on /var/lib/mysql.
Is there a way to separate MySQL databases?
Sadly, MySQL doesn’t have an option to configure separate storage paths for each database like more enterprise database servers do. The solution is to symbolicly link your databases from the new storage device to the MySQL data home directory, as below seen in figure 1.
How to create a new database in MySQL?
MySQL implements a database as a directory that contains all files which correspond to tables in the database. To create a new database in MySQL, you use the CREATE DATABASE statement with the following syntax: First, you specify the database_name following the CREATE DATABASE clause.
How to move MySQL files to a new location?
Set the SELINUX context type of the new directories to mysqld_db_t to allow MySQL access to them. Copy your database’s files to the new location, using cp and -preserve=all to maintain ownership and SELINUX contexts. Verify the SELINUX context is applied correctly to the directories and files.
How to move partition from one table to another?
Below is step-by-step instructions on how to move a partition from one table to another. In this example, one of our customers had two tables with the following structures: And their (likely obvious) goal is to move (not copy) the oldest partition from live_tbl to archive_tbl. To achieve this, we came up with the following procedure: 1.
How do I copy data from MySQL to another file?
After the data is copied, mount the new filesystem directly on /var/lib/mysql. Whichever path you take, when you start mysql back up again it will be using the new location. Not the answer you’re looking for?
Where are all MySQL tables stored on disk?
Historically, MySQL used to store all table data and indices in the system tablespace, represented by one or more ibdata files on disk. This means that data from multiple databases and tables was stored in the same file, making it impossible to move one of them to another location.
How to move individual MySQL tables on disk?
Until that bug is fixed, one can use the workaround with a temporary symlink as described in the bug report (comment of 20 Feb 2016 0:28) to move individual tables on vanilla MySQL. You may encounter the situation where you want to move one single MySQL database table to another (location on) disk, e.g. to free up disk space.
Why does MySQL take up so much disk space?
This time almost all disk space was taken by the MySQL database server however. Some tables had been growing steadily over time, eating up more and more disk space. Since this was data we had to keep, our only sustainable solution was to move these tables to another disk.