Change MySQL Data directory location in Windows

Step 1:

Stop the existing MySQL instance using services.msc, shutdown must be clean so that the instance flushes any pending changes to disk.

MySQL - Services.msc

MySQL – Services.msc

MySQL - Services.msc - stop

MySQL – Services.msc – stop

MySQL - Services.msc - stopped

MySQL – Services.msc – stopped

Step 2:

After successful instance shutdown, copy the data directory from existing location to the new location, in my case:

Existing location: C:\ProgramData\MySQL\MySQL Server 5.7

New Location: E:\MySQL

Step 3:

Change datadir parameter in current my.ini file. I have commented old entry and added new one:

MySQL - my.ini

MySQL – my.ini

# Path to the database root
#datadir=C:/ProgramData/MySQL/MySQL Server 5.7\Data
datadir=E:\MySQL\Data

Step 4:

Start MySQL instance using services.msc

MySQL - Services.msc - start

MySQL – Services.msc – start

MySQL - Services.msc - started

MySQL – Services.msc – started

Step 5:

After starting MySQL successfully, you can verify above changes by creating demo database and verify data dirctory for the same database. Test database: Myslott

MySQL - create database through workbench

MySQL – create database through workbench

MySQL - Data Directory

MySQL – Data Directory

Database Created successfully and myslott directory also created under new data directory.

Cheers!!

Stay Tune. 🙂

Backup & Restore MySQL database with compression option

If your MySQL database is very large, you may like to compress the output of mysqldump.
Use the MySQL backup command below & pipe the output to gzip, then you will get the output as gzip file.

Syntax:
$ mysqldump -u [user_name] -p [password] [database_name] | gzip -9 > [backupfile.sql.gz]

If you want to extract the .gz file, issue following command: Continue reading

How to backup your MySQL Database[s]

Backing up your database is as essential as you breathing. So please backing up your database on daily basis OR hourly basis depends on how your database contains critical/Important data.

 

In this article you will find easiest way to backup & restore your MySQL database.

Backing up your MySQL database using several methods like mentioned below: Continue reading