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

Create a MySQL Database - Part1

MySQL is a free and open source database management system. To create a database and set up tables for the same use the following sql commands.

1. CREATE DATABASE – create the database. To use this statement, you need the CREATE privilege for the database.
2. CREATE TABLE – create the table. You must have the CREATE privilege for the table.
3. INSERT – To add/insert data to table i.e. inserts new rows into an existing table.

Login as the mysql root user to create database:

mysql -u root -p

Create Database book

CREATE DATABASE books;

Select / Use Database for further operations

USE books;

Create a sample table in a database

CREATE TABLE authors (id INT, name VARCHAR(20), email VARCHAR(20));

Check the table definition

SHOW TABLES;

Insert record in author table

INSERT INTO authors (id,name,email) VALUES(1,"Jig","xuz@abc.com");

INSERT INTO authors (id,name,email) VALUES(2,"Mak","m@gmail.com");

INSERT INTO authors (id,name,email) VALUES(3,"Hari","Hari@yahoo.com");

Select records from that table.

SELECT * FROM authors;