10. Prep for recovery: Configure MySQL to use data volume

 

# SSH into your EC2 instance and run following commands:

#Stop mysql:
sudo systemctl stop mysql

# Copy mysql db to /data, mount point of data vol, note that file permissions are also copied:
sudo rsync -av /var/lib/mysql /data

# Edit mysql config to change datadir, edit /etc/mysql/mysql.conf.d/mysqld.cnf:
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

datadir=/data/mysql # change datadir to this

# Edit AppArmor access rules, add this line below to /etc/apparmor.d/tunables/alias:
sudo vi /etc/apparmor.d/tunables/alias

alias /var/lib/mysql/ -> /data/mysql/,

# Restart Apparmor:
sudo systemctl restart apparmor

# Restart Mysql:
sudo mkdir /var/lib/mysql/mysql -p # this is to pass env checks
sudo systemctl start mysql

 

In this video.

Let's take a look at how to configure my sql to use the data volume.

And what we need to do is my sql has a configuration called data directory.

Right.

And this usually points to var lib.

mysql.

This is a folder in the root volume right.

And we want to change this and point this data directory to slash data which if you remember from the

previous video is the mount point of our data volume.

OK so we want to change this configuration from this to this.

ok?.

So this way we will have mysql reading and writing from our data volume which is what we want so that we

can recover from failures more easily.

Right so this is what we want to do.

OK the way we do this is first we will stop the mysql.

Server then we will do an rsync.

This is a command to synchronize the two folders so we will copy the data essentially from var lib Mysql

into slash data the right so we are moving all the sql files into slash data.

This is rsync.

Right.

And this by the way will also copy the files with the appropriate permissions.

After that we need to change the data directory.

This is the configuration change in my sql's configuration right.

And there are some security settings will have to change.

And this is apparmor which is ubuntu's access control.

Service.

And once all this is done we will restart the mysql.

Server ok and the verification we can do by using our localdb dot php page and if the mysql server is connecting

successfully right that can that can be a verification of this change this configuration change that

we are doing.

So lets see how to how to do this.

SSH into our server and run through these commands.

All right so let's SSH into our server here

and I'll use my notes to run through these steps.

So this is the command to stop the my sql server.

So we're going to copy that.

And over here in ubuntu right.

Let's stop my sql OK.

That's done.

Next we will rsync.

So we are essentially copying all the folders we synchronizing two folders and synchronizing the current

data directory of my sql which is var lib mysql and we are copying all these files into.

Slash data which is the mount point of our data volume.

OK so that's that's now done.

Next we will edit the mysql configuration file.

Ok this is the configuration file and what we want to do is change the data directory.

This is the line here.

So I'm going to comment that and instead I will create a new one.

Right.

And we will point this to slash data.

And also don't forget to add my sql after that.

So data directory now is slash data slash my sql.

This one right.

Instead of var lib mysql which was the which is the usual location of my sqls data directory.

And remember var lib mysql is in the root volume.

And this is the data volume.

All right.

So that's the main configuration change.

This is the main change.

And next we have to we have to edit apparmor.

Right.

This is more of a security setting.

dont worry so much about it.

But the but we have to do this this is required in ubuntu.

And what you have to do is add a line up and a line at the bottom like this and the line will be the

essentially creating an alias and the alias is pointing to be to be the data the mount point of our

of our EBS volume.

Yes.

OK so let me save this.

Let me save this file as well.

And after that it's just a matter of restarting.

So we restart apparmor first so that this change that we've just made takes effect.

Right.

And then we have to restart my sql.

But before that there's this command that is run just to it is kind of

a hack just to pass some environment checks dont worry about what this command does.

And this is just to enable us to restart my sql easily even though the data directory has changed

finally we restart my sql c and now let me exit the S-sh session

and let's test our connectivity to the localdb page right.

And you can see it still seems connected right it says it's connected which means even though my sql

is now using a new data directory.

But the connection is still successful.

OK.

So.

So basically what we've done is we've changed the data directory of my sql to a location which is which

is the new EBS volume a separate data volume and this way the data the mysql data

will all be in the data volume.

And this will be useful when we try and recover from those failure scenarios.

And specifically what we've done is we've changed the data directly from var lib Mysql to slash

data and to make the change we had to stop the server synchronize the folders so that all the mysql

folders were copied into Slash data right and then we edited the mysql configuration changed the data

or directory configuration item.

Right.

And the app armor some security settings we needed to change and then we restarted both apparmor.

And my sql server.

And then we verified using the localdb page.

All right so good luck with this.