How to change data directory path in MySQL

 

How to change data directory path in MySQL.

 

Below steps has been performed to modify the datadir path:

 

1. To see the current data directory location:

 

mysql> show global variables like 'datadir';

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

| Variable_name | Value           |

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

| datadir       | /var/lib/mysql/ |

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

1 row in set (0.00 sec)

 

2. Create the physical path

# mkdir mysqldata

# pwd

/mysqldata

 

3. Stop mysql server

systemctl stop mysqld

 

4. Edit the path in file /etc/my.cnf as mentioned below

#datadir=/var/lib/mysql

datadir=/mysqldata   >>> New path mentioned here

 

5. Copy all the files from current data directory to new data directory:

 

cd /var/lib/mysql

cp -RH * /mysqldata/

6. Change the permission to mysql 

chown -R mysql:mysql mysqldata

 

7. Start the mysql server, and check the path now

 

systemctl start mysqld

   

    mysql> show global variables like 'datadir';

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

| Variable_name | Value       |

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

| datadir       | /mysqldata/ |

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

1 row in set (0.01 sec)

 

    You are done.

User Management in MySQL

 In this article we will cover how to create Admin, Read and Write access user in MySQL.

1. To see all the user in mysql:

select user,host from mysql.user;

2. To create an Admin user

create user 'Adminuser'@'%' identified by 'Admin@123';

grant all on *.* to 'Adminuser'@'%' with grant option;

flush privileges;

3. To see all the permission of the user

select * from mysql.user \G;

4. Create write access user for a Particular Database

create user 'writeuser' identified by 'Write@123';

grant select,insert,update,delete on laptop.* to 'writeuser';

flush privileges;

In above example we are creating writeuser and giving the user permission to use all the tables on laptop database.

5. To create a Read user

create user 'readuser'@'%' identified by 'Read@123';

grant select,show view on *.* to 'readuser'@'%';

flush privileges;

Thanks,