How to Change a MySQL Data Directory to a New Location on CentOS 7

How to Change a MySQL Data Directory to a New Location on CentOS 7

Step 1 : Moving the MySQL Data Directory

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

[root@localhost ~]# mysql -u root -p

Enter password:

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

Your MySQL connection id is 4

Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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         |

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

| /var/lib/mysql/ |

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

1 row in set (0.00 sec)

step.2.

To ensure the integrity of the data, we’ll shut down MySQL before we actually make changes to the data directory:

[root@localhost ~]# systemctl stop mysqld

[root@localhost ~]# systemctl status mysqld

● mysqld.service - MySQL Server

   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

   Active: inactive (dead) since Tue 2020-09-22 23:49:45 IST; 8s ago

     Docs: man:mysqld(8)

           http://dev.mysql.com/doc/refman/en/using-systemd.html

  Process: 2812 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)

  Process: 2790 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

 Main PID: 2816 (code=exited, status=0/SUCCESS)

Sep 22 23:42:47 localhost.localdomain systemd[1]: Starting MySQL Server...

Sep 22 23:42:50 localhost.localdomain systemd[1]: Started MySQL Server.

Sep 22 23:49:43 localhost.localdomain systemd[1]: Stopping MySQL Server...

Sep 22 23:49:45 localhost.localdomain systemd[1]: Stopped MySQL Server.

step.3. 

To create a new mysql directory.

mkdir -p /home/data/mysql/

chmod 755 /home/data/mysql/

chown mysql:mysql /home/data/mysql/

step.4.

Then copy recursively the contents of /var/lib/mysql to /home/data/mysql/ preserving original permissions and timestamps:

[root@localhost ~]# cp -R -p /var/lib/mysql/* /home/data/mysql

step.5.

Configure a New MySQL Data Directory

[root@localhost ~]# vi /etc/my.cnf

BEFORE MYSQL DIRECTORY

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[client]

socket=/var/lib/mysql/mysql.sock

AFTER EDIT MySQL DIRECTORY

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

#datadir=/var/lib/mysql

#socket=/var/lib/mysql/mysql.sock

datadir=/home/data/mysql

socket=/home/data/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[client]

#socket=/var/lib/mysql/mysql.sock

socket=/home/data/mysql/mysql.sock

step.6.

Restarting MySQL

[root@localhost ~]# systemctl restart mysqld

[root@localhost ~]# systemctl status mysqld

● mysqld.service - MySQL Server

   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

   Active: active (running) since Wed 2020-09-23 00:57:20 IST; 19s ago

     Docs: man:mysqld(8)

           http://dev.mysql.com/doc/refman/en/using-systemd.html

  Process: 4368 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)

  Process: 4342 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

 Main PID: 4371 (mysqld)

   CGroup: /system.slice/mysqld.service

           └─4371 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Sep 23 00:57:16 localhost.localdomain systemd[1]: Starting MySQL Server...

Sep 23 00:57:20 localhost.localdomain systemd[1]: Started MySQL Server.

step.7. finally login mysql databases.

[root@localhost ~]# mysql -u root -p

Enter password:

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

Your MySQL connection id is 4

Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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         |

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

| /home/data/mysql/ |

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

1 row in set (0.00 sec)

step.8.

finallly directroy looking in image

Thank You.

HOW TO SETUP MYSQL MASTER MASTER REPLICATION WITH GTID.

 #######################

HOW TO SETUP MYSQL MASTER MASTER REPLICATION WITH GTID.

A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master). 

This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication setup. 

There is a 1-to-1 mapping between all transactions and all GTIDs. 

A GTID is represented as a pair of coordinates, separated by a colon character (:), as shown here:

GTID = source_id:transaction_idMySQL_gtid-master-master

Advantages :

● Setting up MySQL replication is simple

● Consistency is guaranteed between master and slaves.

● Simple to determine inconsistency

● Fail-over process become much easier.

step.1. Get Both server  serverA and serverB ip.

serverA ip=xx.xx.xx.xx

serverB ip=xx.xx.xx.xx

Step 2 : Configure the Master serverA

To enable GTID-based replication, each server must be started with GTID mode enabled by setting the gtid_mode variable to ON, 

and with the enforce_gtid_consistency variable enabled to ensure that only statements which are safe for GTID-based replication are logged.

 vi /etc/my.cnf

Add the following parameter under [mysqld] section of my.cnf file

server-id = 1

log-bin = mysql-bin

binlog_format = row

gtid-mode=ON

enforce-gtid-consistency

log-slave-updates

Now start the mysqld server process

sudo service mysqld start

Step 3 : Create a replication user for Master serverB

create user 'repl_user'@'%' identified by 'XXXXXXXXXX';

Grant replication slave on *.* to 'repl_user'@'%';

Step 4 : Take backup with mysqldump with Master serverA

Now we take a backup with mysqldump from the master:

mysqldump --all-databases -flush-privileges --single-transaction --flush-logs --triggers --routines --events --user=root  --password=XXXXXXXX > mysqlbackup_dump.sql

Step 5 : Configure the Master serverB 

 vi /etc/my.cnf

Add the following parameter under [mysqld] section of my.cnf file

server-id = 2

log-bin = mysql-bin

relay-log = relay-log-server

read-only = ON

gtid-mode=ON

enforce-gtid-consistency

log-slave-updates

Now start the mysqld server process

sudo service mysqld start

Step 6: Create a replication user for Master serverA

create user 'repl_user'@'%' identified by 'XXXXXXXXXX';

Grant replication slave on *.* to 'repl_user'@'%';

Step 7 : Load the mysqldump in Master server 2 

mysql>  source mysqlbackup_dump.sql

Step 8 : Execute the Change Master  on Master serverB 

Now we will tell the Master serverB to use Master serverA as a data source

CHANGE MASTER TO

MASTER_HOST = 'xx.xx.xx.xx',

MASTER_PORT = 3306,

MASTER_USER = 'repl_user',

MASTER_PASSWORD = 'XXXXXXXXX',

MASTER_AUTO_POSITION = 1;

Start the slave And then check status.

start slave;

show slave status\G

If every thing fine then move to master serverA to apply change master.

Step 9 : Execute the Change Master  on Master serverA

Now we will tell the Master serverA to use Master serverB as a data source

CHANGE MASTER TO

MASTER_HOST = 'xx.xx.xx.xx',

MASTER_PORT = 3306,

MASTER_USER = 'repl_user',

MASTER_PASSWORD = 'XXXXXXXXX',

MASTER_AUTO_POSITION = 1;

Start the slave And then check status.

start slave;

show slave status\G

Test the Replication

Create a table on both server and check whether the replication is working fine.

Thank you.