Migrating MySQL Database files directory under Ubuntu

Using Ubuntu's APT package management tool to install the MySQL database, the database files are saved in the/var/lib/mysql directory by default, the database is getting bigger and larger, so it is ready to mount a new hard disk dedicated to the MySQL database.

1. Determine the MySQL database file storage directory

The general default is under the/var/lib/mysql directory. Log in to your MySQL database first, for example, I log in with the root account, and then use the following query statement query:

Show variables like '%dir% ';

Get database file configuration information:

You can see that the value of DataDir is/var/lib/mysql/, which is the directory where the current database file resides.

Another basedir parameter indicates where the MySQL database is installed, and the migration database file location does not need to change this parameter.

2. Migrate the database files to the new directory

Stop the MySQL database service using the following command first:

Sudo/etc/init.d/mysql stop

My new data disk is mounted in the/mnt/data directory, so I want to migrate the database to/mnt/data.

2.1 You can use the MV command to move the original database directory files to a new directory, the benefit is not simple, do not modify the original database file permissions, as well as user and user group attribution:

sudo mv/var/lib/mysql/mnt/data/

2.2 You can also use the CP Copy command to copy the original database directory files to the new directory, the advantage is: In the unlikely event of a migration failure, recovery is relatively straightforward, and it is not too late to confirm that the migration succeeded in deleting the original database directory file. In order to not affect the replication of the database directory file permissions and user group attribution problem, use the CP command to add the-a parameter:

sudo cp-a/var/lib/mysql/mnt/data/

Note: Since the/var/lib/mysql directory belongs to the MySQL user and MySQL user group created by MySQL database, root permission is required to migrate the files, and the command to use sudo

After the migration succeeds, you can see that the MySQL database file has been migrated from the/mnt/data/directory, and that the user group belonging to the directory file is still MySQL, with no change:

3. Modify the configuration file

A total of three configuration files need to be modified:

3.1 my.cnf File

MySQL database will be prioritized from/ETC/MY.CNF,/ETC/MYSQL/MY.CNF,/USR/ETC/MY.CNF, ~/.my.cnf four locations to find the MY.CNF configuration file, once found will not continue to look down. Ubuntu defaults to placing the MY.CNF configuration file in the/ETC/MYSQL/MY.CNF location, so this profile is not found in the/ETC/MY.CNF location.

Choose the text editor you use to edit the MY.CNF profile, I use vim, so sudo vim/etc/mysql/my.cnf. The same as sudo, with root permission to edit. Change the value of the DataDir property under the [Mysqld] label to the new database directory path/mnt/data/mysql,

Save and exit after modifying.

3.2 Usr.bin.mysqld File

Because Ubuntu uses the AppArmor security module, which is similar to the sandbox operation of a mechanism, it can limit the software at run time some of the behavior, such as to which directories and files can read and write lock and so on.

Because the database file path is modified, the AppArmor configuration file of the MySQL database is modified to add the read-write and lock permissions of the new database file directory and file, and can delete or comment out the permissions of the original/var/lib/mysql database file directory. The AppArmor configuration file path for the MySQL database is /etc/apparmor.d/usr.sbin.mysqld. Use the following command to edit this configuration file:

sudo vim/etc/apparmor.d/usr.sbin.mysqld

Find Them.

/VAR/LIB/MYSQL/R,
/var/lib/mysql/** Rwk,

Two lines of permission declarations can be preceded by a # good comment out. Then control the format and add the new path to the permission declaration:
/MNT/DATA/MYSQL/R,
/mnt/data/mysql/** Rwk,

Results

Save and close.

3.3 Abstractions/mysql File

Because the Abstractions/mysql file is referenced in the Usr.bin.mysqld file, the permission declarations in the Abstractions/mysql file are imported. Therefore, also modify the following file:

sudo vim/etc/apparmor.d/abstractions/mysql

The same is also the new database file path in the socket file permissions added, you can delete or comment out the full path of the request permissions, the effect

Exit after saving.

4. Restart the database

After the configuration file has been modified successfully, you can restart the database, before restarting the database, you need to reload the AppArmor configuration file and reload it using the following command:

Sudo/etc/init.d/apparmor restart

If the overload succeeds, you can start the database with the following command:

Sudo/etc/init.d/mysql start

5. Permissions issues

After the appeal step, you may be unable to start the database. Ignoring the Continue login database appears the following error about sock:

ERROR 2002 (HY000): Can ' t connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock ' (2)

View the database's startup error log,sudo vim/var/log/mysql/error.log, and see the Table ' plugin ' is read only error:

This behavior occurs because of permissions on the new database file directory.

The MySQL database needs to be executed as a MySQL user, so the MySQL user needs to be able to read and write the database file directory. While migrating the database files above, whether using the MV or the CP-A command does not change the MySQL directory Users and user groups, it also saw the/mnt/data/mysql belongs to the user and user group is still MySQL. It is therefore certain that the MySQL user has access to read and write/mnt/data/mysql, but this does not guarantee that the parent directory/mnt/data and the upper parent directory/mnt have access to the MySQL user. If the MySQL user does not have the upper directory/mnt/data and the upper parent directory/mnt Read permission, the MySQL user can not read and write their own/mnt/data/mysql directory, therefore will appear the above problem.

You can look at the structure of the original database file directory/var/lib/mysql:

You can see that the original database file directory/var/lib/mysql of the parent directory/var/lib belongs to the root user, but it is the same group of users and other groups of users have opened the ' R ' and ' x ' permissions, So even if the parent directory does not belong to the MySQL user, the MySQL user will also be able to enter and access their own database files normally.

The solution to this problem is as simple as ensuring that the MySQL user has the ' R ' and ' x ' permissions on all the parent directories of the final database file directory.

For example, use the following command to modify the permissions for/mnt/data in this article:

sudo chmod 755/mnt/data

The parent directory/mnt is the system directory, the root user, the root user's default directory permissions are 755, so do not modify.

Once the permissions have been modified, start the database again sudo/etc/init.d/mysqlstart, it should be able to start successfully.

Enter the database to view the current path configuration information:

The database has started normally, and the database file path has been replaced with the/mnt/data/mysql destination path, and the database file migration was successful.

Migrating MySQL Database files directory under Ubuntu