Moving the MySQL Data Directory - Part2

To prepare for moving MySQL’s data directory, let’s verify the current location by starting an interactive MySQL session using the administrative credentials.

mysql -u root -p

select @@datadir;

mysql> select @@datadir;

+-----------------+

| @@datadir       |

+-----------------+

| /var/lib/mysql/ |

+-----------------+

1 row in set (0.00 sec)

This output confirms that MySQL is configured to use the default data directory.

2. Shutdown database and check status

[root@hccmoenas ~]# service mysqld stop
Stopping mysqld:                                           [  OK  ]
[root@hccmoenas ~]# service mysqld status
mysqld is stopped

3. Copy datafiles to a different location

[root@hccmoenas lib]# mv mysql mysql.bkp

[root@hccmoenas lib]# cp -Rp mysql.bkp /u01/mysql

[root@hccmoenas lib]# du -sh /u01/mysql

21M     /u01/mysql

[root@hccmoenas lib]#

4. Pointing to the New Data Location

Edit file/etc/my.cnf  and update value for datadir location

[root@hccmoenas lib]# grep datadir /etc/my.cnf
#datadir=/var/lib/mysql
datadir=/u01/mysql
[root@hccmoenas lib]#

5. Start MySQL Database services and check datafile location and tables if it can access.

[root@hccmoenas lib]# service mysqld start

Starting mysqld:                                           [  OK  ]

[root@hccmoenas lib]# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@datadir;

+-------------+

| @@datadir   |

+-------------+

| /u01/mysql/ |

+-------------+

1 row in set (0.00 sec)

mysql> USE books;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SHOW TABLES;

+-----------------+

| Tables_in_books |

+-----------------+

| authors         |

+-----------------+

1 row in set (0.01 sec)

mysql> SELECT * FROM authors;

+------+-------+---------------+

| id   | name  | email         |

+------+-------+---------------+

|    1 | Vivek | xuz@abc.com   |

|    2 | Priya | p@gmail.com   |

|    3 | Tom   | tom@yahoo.com |

+------+-------+---------------+

3 rows in set (0.00 sec)

mysql> exit

Bye