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,

RMAN Full and Incremental backup scripts for windows

You can use below scripts to schedule backup jobs using task scheduler.

===================================================================

 ECHO OFF

SET TMPSCRIPT=E:\rman\backup\fullbackup\rman_temp.txt

CD /D E:\rman\backup\fullbackup   

set temp=%DATE:/=%

set tem=%RANDOM%

set dirname=%temp:~4,2%%temp:~6,2%%temp:~8,4%%tem:~0,2%%tem:~3,2%

mkdir "%dirname%"

set oracle_sid=test

ECHO RUN > %TMPSCRIPT%

ECHO { >> %TMPSCRIPT%

ECHO ALLOCATE CHANNEL c1 DEVICE TYPE disk;  >> %TMPSCRIPT%

ECHO ALLOCATE CHANNEL c2 DEVICE TYPE disk;  >> %TMPSCRIPT%

ECHO ALLOCATE CHANNEL c3 DEVICE TYPE disk;  >> %TMPSCRIPT%

ECHO ALLOCATE CHANNEL c4 DEVICE TYPE disk;  >> %TMPSCRIPT%

ECHO BACKUP INCREMENTAL LEVEL 0 DATABASE TAG FULL_DB_BKP format 'E:\rman\backup\fullbackup\%dirname%\FULL-%%d_id-%%I_%%u'; >> \fullbackup\%dirname%\DB-%%d_id-%%I_%%u'; >> %TMPSCRIPT%

ECHO backup tag FULL_ARCH format 'E:\rman\backup\fullbackup\%dirname%\FULL_ARCH-%%d_id-%%I_%%u' archivelog all delete all input ; >> %TMPSCRIPT%

ECHO backup tag FULL_CTL current controlfile format 'E:\rman\backup\fullbackup\%dirname%\FULL_DB_CONTROL.bkp'; >> %TMPSCRIPT%

ECHO release channel c1;  >> %TMPSCRIPT%

ECHO release channel c2;  >> %TMPSCRIPT%

ECHO release channel c3;  >> %TMPSCRIPT%

ECHO release channel c4;  >> %TMPSCRIPT%

ECHO } >> %TMPSCRIPT%

rman target / @%TMPSCRIPT% log='E:\rman\backup\fullbackup\%dirname%\full_rman.log'

===================================================================

===================================================================

ECHO OFF

SET TMPSCRIPT=E:\rman\backup\incremental\rman_temp.txt

CD /D E:\rman\backup\incremental   

set temp=%DATE:/=%

set tem=%RANDOM%

set dirname=%temp:~4,2%%temp:~6,2%%temp:~8,4%%tem:~0,2%%tem:~3,2%

mkdir %dirname%

set oracle_sid=test

ECHO RUN > %TMPSCRIPT%

ECHO { >> %TMPSCRIPT%

ECHO ALLOCATE CHANNEL c1 DEVICE TYPE disk;  >> %TMPSCRIPT%

ECHO ALLOCATE CHANNEL c2 DEVICE TYPE disk;  >> %TMPSCRIPT%

ECHO ALLOCATE CHANNEL c3 DEVICE TYPE disk;  >> %TMPSCRIPT%

ECHO ALLOCATE CHANNEL c4 DEVICE TYPE disk;  >> %TMPSCRIPT%

ECHO BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG INCR_DB_BKP format 'E:\rman\backup\incremental\%dirname%\INCR-%%d_id-%%I_%%u'; >> \incremental\%dirname%\DB-%%d_id-%%I_%%u'; >> %TMPSCRIPT%

ECHO backup tag INCR_ARCH format 'E:\rman\backup\incremental\%dirname%\INCR_ARCH-%%d_id-%%I_%%u' archivelog all delete all input ; >> %TMPSCRIPT%

ECHO backup tag INCR_CTL current controlfile format 'E:\rman\backup\incremental\%dirname%\DB_CONTROL.bkp'; >> %TMPSCRIPT%

ECHO release channel c1;  >> %TMPSCRIPT%

ECHO release channel c2;  >> %TMPSCRIPT%

ECHO release channel c3;  >> %TMPSCRIPT%

ECHO release channel c4;  >> %TMPSCRIPT%

ECHO } >> %TMPSCRIPT%

rman target / @%TMPSCRIPT% log='E:\rman\backup\incremental\%dirname%\incr_rman.log'

===================================================================

Thanks,