CentOS change MySQL Database directory location

CentOS change MySQL Database directory location

Because MySQL database is too large, the default installed "/var" disk can no longer accommodate the new added data, there is no way, only to find ways to transfer the data directory.

The following is a detailed procedure for transferring the MySQL database default installation directory from the "/var/lig/mysql" directory to the "/home/mysql_data/" directory:

Install the directory as the default path by installing the "MYSQL" Database with "YUM": "/var/lib/mysql"

Start the MYSQL database

[[Email protected] ~] #service mysqld Start

Set the "MYSQL" Database login password

[[Email protected] ~] #mysqladmin –uroot Password 123

Login Test "MYSQL" can log in normally

[[Email protected] ~] #mysql –uroot–p123

To create the required test database

mysql>create databases Ceshi;

Mysql>show databases;

Enter the test database to create the table

Mysql>use Ceshi;

Mysql>create table MyTable (name varchar), sex char (1), birth date,home varchar (30));

Mysql>show tables;

Adding data to the table

Mysql>insert into mytable values (' Zhangsan ', ' m ', ' 1977-05-16 ', ' China ');

Mysql>insert into mytable values (' Lisi ', ' m ', ' 1988-09-25 ', ' China ');

Mysql>select * FROM MyTable;

To this! Start the migration of the "MYSQL" Database directory location

1. Turn off "MYSQL"

[[Email protected] ~] #service mysqld Stop

2. Create the directory to be placed after the migrated database

[[Email protected] ~] #mkdir/home/mysql_data

3. Transfer data, the default installation directory "/var/lib/mysql" copy of the newly created data directory "/home/mysql_data" under

[[Email protected] ~] #cd/var/lib

[[email protected] lib] #cp-arp mysql/home/mysql_data

Note:-A This parameter keeps the permissions for the reason file, and the-R together with the directory copy "

[[Email protected] ~] #cd/home/mysql_data/mysql

4. Modify the configuration file, a total of three:

Modify the first file: Back up the original file before modifying

[[Email protected] ~] #cp/etc/my.cnf/etc/my.cnfbak

To modify the data storage directory:

Original storage path: "Datadir=/var/lib/mysql"

È

Now store the path: "Datadir=/home/mysql_data/mysql"

Original storage path: "Socket=/var/lib/mysql/mysql.sock"

È

Now store the path: "Socket=/home/mysql_data/mysql/mysql.sock"

Note: For security, you can remove the original comment and rejoin the line. 】

[[Email protected] ~] #vim/etc/my.cnf

5. Modify the second file: Back up the original file before modifying

[[Email protected] ~] #cp/etc/init.d/mysqld/etc/init.d/mysqldbak

To modify the Data directory:

Original storage Path "/var/lib/mysql"

È

Now store the path "/home/mysql_data/mysql"

"NOTE: The exact location is/etc/rc.d/init.d/mysqld, because here there is a mapping of/ETC/INIT.D to/ETC/RC.D/INIT.D,"

[[Email protected] ~] #vim/etc/init.d/mysqld

6. Modify the third file: Back up the original file before modifying

[[Email protected] ~] #cp/usr/bin/mysqld_safe/usr/bin/mysqld_safebak

To modify the Data directory:

Original storage Path "/var/lib/mysql"

È

Now store the path "/home/mysql_data/mysql"

[[Email protected] ~] #vim/usr/bin/mysqld_safe

7. Start the "MYSQL" service

[[Email protected] ~] #service mysqld Start

8. After starting the "MYSQL" service, you need to establish a mysql.sock link:

Ln-s/home/mysql_data/mysql/mysql.sock/var/lib/mysql/mysql.sock

9. Check if the process "MYSQL" is running as a modified path

[[Email protected] ~] #ps –ef | grep MySQL

Test whether the database is logged in normally

[[Email protected] ~] #mysql-uroot-p

10. Test inserting data into the table:

Data in the "mytable" table before inserting data

Mysql>select * FROM MyTable;

Insert a new piece of data into the "mytable" table

Mysql>insert into mytable values (' Wangwu ', ' m ', ' 1985-09-15 ', ' China ');

11. Finally modify the permissions of the database

[Email protected] ~]# chown-r mysql:mysql/home/data/mysql/

Note: Change the attribution of the database to MySQL

[Email protected] ~]# chmod 700/home/data/mysql/test/

Note: Change the database directory property to 700
[Email protected] ~]# chmod 660/home/data/mysql/test/*

Note: Changing the properties of the data in the database is 660

CentOS change MySQL Database directory location