Maximenu CK message : Your module is still working in V8 Legacy mode. Please change it in the Advanced options to remove this message.
  το site προορίζεται γα τεχνικούς της πληροφορικής, που συμμετέχουν στην ενημέρωσή του. Δεν υπάρχει δυνατότητα εγγραφής νέων μελών. Σκοπός του είναι η συλλογή θεμάτων που αφορούν την εγκατάσταση και ρύθμιση εφαρμογών  καθώς και επίλυση διαφόρων προβλημάτων. Δεν περιέχει γενικά άρθρα ή πληροφορία.  

Move MySQL Data Directory

...to a New Location on Ubuntu 16.04

Introduction

Databases grow over time, sometimes outgrowing the space on the file system. You can also run into I/O contention when they’re located on the same partition as the rest of the operating system. RAID, network block storage, and other devices can offer redundancy and other desirable features. Whether you’re adding more space, evaluating ways to optimize performance, or looking to take advantage of other storage features, this tutorial will guide you through relocating MySQL’s data directory.

Prerequisites

To complete this guide, you will need:

In this example, we’re moving the data to a block storage device mounted at /mnt/volume-nyc1-01. You can learn how to set one up in the How To Use Block Storage on DigitalOcean guide.

No matter what underlying storage you use, this guide can help you move the data directory to a new location.

 

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.

  • mysql -u root -p

When prompted, supply the MySQL root password. Then from the MySQL prompt, select the data directory:

  • select @@datadir;
Output
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

This output confirms that MySQL is configured to use the default data directory, /var/lib/mysql/, so that’s the directory we need to move. Once you've confirmed this, type exit to leave the monitor.

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

  • sudo systemctl stop mysql

systemctl doesn't display the outcome of all service management commands, so if you want to be sure you've succeeded, use the following command:

  • sudo systemctl status mysql

You can be sure it’s shut down if the final line of the output tells you the server is stopped:

Output
. . .
Jul 18 11:24:20 ubuntu-512mb-nyc1-01 systemd[1]: Stopped MySQL Community Server.

Now that the server is shut down, we’ll copy the existing database directory to the new location with rsync. Using the -a flag preserves the permissions and other directory properties, while-v provides verbose output so you can follow the progress.

Note: Be sure there is no trailing slash on the directory, which may be added if you use tab completion. When there’s a trailing slash, rsync will dump the contents of the directory into the mount point instead of transferring it into a containing mysql directory:

  • sudo rsync -av /var/lib/mysql /mnt/volume-nyc1-01

Once the rsync is complete, rename the current folder with a .bak extension and keep it until we’ve confirmed the move was successful. By re-naming it, we’ll avoid confusion that could arise from files in both the new and the old location:

  • sudo mv /var/lib/mysql /var/lib/mysql.bak

Now we’re ready to turn our attention to configuration.

Step 2 — Pointing to the New Data Location

MySQL has several ways to override configuration values. By default, the datadir is set to /var/lib/mysql in the /etc/mysql/mysql.conf.d/mysqld.cnf file. Edit this file to reflect the new data directory:

  • sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Find the line that begins with datadir= and change the path which follows to reflect the new location.

In our case, the updated file looks like the output below:

/etc/mysql/mysql.conf.d/mysqld.cnf
. . .
datadir=/mnt/volume-nyc1-01/mysql
. . .

This seems like the right time to bring up MySQL again, but there’s one more thing to configure before we can do that successfully.

 

Step 3 — Configuring AppArmor Access Control Rules

We’ll need to tell AppArmor to let MySQL write to the new directory by creating an alias between the default directory and the new location. To do this, edit the AppArmor alias file:

  • sudo nano /etc/apparmor.d/tunables/alias

At the bottom of the file, add the following alias rule:

/etc/apparmor.d/tunables/alias
. . .
alias /var/lib/mysql/ -> /mnt/volume-nyc1-01/mysql/,
. . .

For the changes to take effect, restart AppArmor:

  • sudo systemctl restart apparmor

Note: If you skipped the AppArmor configuration step, you would run into the following error message:

Output
Job for mysql.service failed because the control process 
exited with error code. See "systemctl status mysql.service" 
and "journalctl -xe" for details.

The output from both systemctl and journalctl concludes with:

Output
Jul 18 11:03:24 ubuntu-512mb-nyc1-01 systemd[1]: 
mysql.service: Main process exited, code=exited, status=1/FAILURE

Since the messages don’t make an explicit connection between AppArmor and the data directory, this error can take some time to figure out.

Step 4 — Restarting MySQL

The next step is to start MySQL, but if you do, you’ll run into another error. This time, instead of an AppArmor issue, the error happens because the script mysql-systemd-start checks for the existence of either a directory, -d, or a symbolic link, -L, that matches two default paths. It fails if they're not found:

/usr/share/mysql/mysql-systemd-start
. . .
if [ ! -d /var/lib/mysql ] && [ ! -L /var/lib/mysql ]; then
 echo "MySQL data dir not found at /var/lib/mysql. Please create one."
 exit 1
fi

if [ ! -d /var/lib/mysql/mysql ] && [ ! -L /var/lib/mysql/mysql ]; then
 echo "MySQL system database not found. Please run mysql_install_db tool."
 exit 1
fi

. . .

Since we need these to start the server, we will create the minimal directory structure to pass the script's environment check.

  • sudo mkdir /var/lib/mysql/mysql -p

Now we're ready to start MySQL.

  • sudo systemctl start mysql
  • sudo systemctl status mysql

To make sure that the new data directory is indeed in use, start the MySQL monitor.

  • mysql -u root -p

Look at the value for the data directory again:

Output
+----------------------------+
| @@datadir                  |
+----------------------------+
| /mnt/volume-nyc1-01/mysql/ |
+----------------------------+
1 row in set (0.01 sec)

Now that you’ve restarted MySQL and confirmed that it’s using the new location, take the opportunity to ensure that your database is fully functional. Once you’ve verified the integrity of any existing data, you can remove the backup data directory:

  • sudo rm -Rf /var/lib/mysql.bak

Restart MySQL one final time to be sure that it works as expected:

  • sudo systemctl restart mysql
  • sudo systemctl status mysql

Introduction to fstab

Introduction to fstab

IconsPage/hdd.png The configuration file /etc/fstab contains the necessary information to automate the process of mounting partitions. In a nutshell, mounting is the process where a raw (physical) partition is prepared for access and assigned a location on the file system tree (or mount point).

  • In general fstab is used for internal devices, CD/DVD devices, and network shares (samba/nfs/sshfs). Removable devices such as flash drives *can* be added to fstab, but are typically mounted by gnome-volume-manager and are beyond the scope of this document.
  • Options for mount and fstab are similar.
  • Partitions listed in fstab can be configured to automatically mount during the boot process.
  • If a device/partition is not listed in fstab ONLY ROOT may mount the device/partition.
  • Users may mount a device/partition if the device is in fstab with the proper options.

IconsPage/tip.png For usage with network shares, see SettingUpNFSHowTo , SettingUpSamba and SSHFS.

Fstab File Configuration

IconsPage/info.png The syntax of a fstab entry is :

[Device] [Mount Point] [File System Type] [Options] [Dump] [Pass]

fields

description

<device>

The device/partition (by /dev location or UUID) that contain a file system.

<mount point>

The directory on your root file system (aka mount point) from which it will be possible to access the content of the device/partition (note: swap has no mount point). Mount points should not have spaces in the names.

<file system type>

Type of file system (see LinuxFilesystemsExplained).

<options>

Mount options of access to the device/partition (see the man page for mount).

<dump>

Enable or disable backing up of the device/partition (the command dump). This field is usually set to 0, which disables it.

<pass num>

Controls the order in which fsck checks the device/partition for errors at boot time. The root device should be 1. Other partitions should be 2, or 0 to disable checking.

 

Device

By default, Ubuntu now uses UUID to identify partitions.

UUID=xxx.yyy.zzz

To list your devices by UUID use blkid

sudo blkid

Alternative ways to refer to partitions:

  • Label : LABEL=label
  • Network ID
  • Device : /dev/sdxy (not recommended)

Mount point

A mount point is a location on your directory tree to mount the partition. The default location is /media although you may use alternate locations such as /mnt or your home directory.

You may use any name you wish for the mount point, but you must create the mount point before you mount the partition.

For example : /media/windows

sudo mkdir /media/windows

File System Type

You may either use auto or specify a file system. Auto will attempt to automatically detect the file system of the target file system and in general works well. In general auto is used for removable devices and a specific file system or network protocol for network shares.

Examples:

  • auto
  • vfat - used for FAT partitions.
  • ntfs, ntfs-3g - used for ntfs partitions.
  • ext4, ext3, ext2, jfs, reiserfs, etc.
  • udf,iso9660 - for CD/DVD.
  • swap.

Options

Options are dependent on the file system.

You may use "defaults" here and some typical options may include :

  • Ubuntu 8.04 and later uses relatime as default for linux native file systems. You can find a discussion of relatime here : http://lwn.net/Articles/244829. This relates to when and how often the last access time of the current version of a file is updated, i.e. when it was last read. 

  • defaults = rw, suid, dev, exec, auto, nouser, and async.
  • ntfs/vfat = permissions are set at the time of mounting the partition with umask, dmask, and fmask and can not be changed with commands such as chown or chmod.

    • I advise dmask=027,fmask=137 (using umask=000 will cause all your files to be executable). More permissive options would be dmask=000,fmask=111.

  • For mounting samba shares you can specify a username and password, or better a credentials file. The credentials file contains should be owned by root.root with permissions = 0400 . 

Common options :

  • sync/async - All I/O to the file system should be done (a)synchronously.
  • auto - The filesystem can be mounted automatically (at bootup, or when mount is passed the -a option). This is really unnecessary as this is the default action of mount -a anyway.
  • noauto - The filesystem will NOT be automatically mounted at startup, or when mount passed -a. You must explicitly mount the filesystem.
  • dev/nodev - Interpret/Do not interpret character or block special devices on the file system.
  • exec / noexec - Permit/Prevent the execution of binaries from the filesystem.
  • suid/nosuid - Permit/Block the operation of suid, and sgid bits.
  • ro - Mount read-only.
  • rw - Mount read-write.
  • user - Permit any user to mount the filesystem. This automatically implies noexec, nosuid,nodev unless overridden.
  • nouser - Only permit root to mount the filesystem. This is also a default setting.
  • defaults - Use default settings. Equivalent to rw, suid, dev, exec, auto, nouser, async.
  • _netdev - this is a network device, mount it after bringing up the network. Only valid with fstype nfs.

For specific options with specific file systems see:

Dump

This field sets whether the backup utility dump will backup file system. If set to "0" file system ignored, "1" file system is backed up.

Dump is seldom used and if in doubt use 0.

Pass (fsck order)

Fsck order is to tell fsck what order to check the file systems, if set to "0" file system is ignored.

Often a source of confusion, there are only 3 options :

  • 0 == do not check.
  • 1 == check this partition first.
  • 2 == check this partition(s) next

In practice, use "1" for your root partition, / and 2 for the rest. All partitions marked with a "2" are checked in sequence and you do not need to specify an order.

Use "0" to disable checking the file system at boot or for network shares.

You may also "tune" or set the frequency of file checks (default is every 30 mounts) but in general these checks are designed to maintain the integrity of your file system and thus you should strongly consider keeping the default settings.

Examples

IconsPage/editor.png The contents of the file will look similar to following:

# /etc/fstab: static file system information.
#
# <file system> <mount point>   <type>  <options>       <dump>  <pass>

proc  /proc  proc  defaults  0  0
# /dev/sda5
UUID=be35a709-c787-4198-a903-d5fdc80ab2f8  /  ext3  relatime,errors=remount-ro  0  1
# /dev/sda6
UUID=cee15eca-5b2e-48ad-9735-eae5ac14bc90  none  swap  sw  0  0

/dev/scd0  /media/cdrom0  udf,iso9660  user,noauto,exec,utf8  0  0

NOTE: These network share examples (samba, nfs, and sshfs) assume you have already set up the appropriate server.

# FAT ~ Linux calls FAT file systems vfat)
# /dev/hda1
UUID=12102C02102CEB83  /media/windows  vfat auto,users,uid=1000,gid=100,dmask=027,fmask=137,utf8  0  0

# NTFS ~ Use ntfs-3g for write access (rw) 
# /dev/hda1
UUID=12102C02102CEB83  /media/windows  ntfs-3g  auto,users,uid=1000,gid=100,dmask=027,fmask=137,utf8  0  0

# Zip Drives ~ Linux recognizes ZIP drives as sdx'''4'''

# Separate Home
# /dev/sda7
UUID=413eee0c-61ff-4cb7-a299-89d12b075093  /home  ext3  nodev,nosuid,relatime  0  2

# Data partition
# /dev/sda8
UUID=3f8c5321-7181-40b3-a867-9c04a6cd5f2f  /media/data  ext3  relatime,noexec  0  2

# Samba
//server/share  /media/samba  cifs  user=user,uid=1000,gid=100  0  0
# "Server" = Samba server (by IP or name if you have an entry for the server in your hosts file
# "share" = name of the shared directory
# "user" = your samba user
# This set up will ask for a password when mounting the samba share. If you do not want to enter a password, use a credentials file.
# replace "user=user" with "credentials=/etc/samba/credentials" In the credentials file put two lines
# username=user
# password=password
# make the file owned by root and ro by root (sudo chown root.root /etc/samba/credentials && sudo chmod 400 /etc/samba/credentials)

# NFS
Server:/share  /media/nfs  nfs  rsize=8192 and wsize=8192,noexec,nosuid
# "Server" = Samba server (by IP or name if you have an entry for the server in your hosts file
# "share" = name of the shared directory

#SSHFS
sshfs#user@server:/share  fuse  user,allow_other  0  0
# "Server" = Samba server (by IP or name if you have an entry for the server in your hosts file
# "share" = name of the shared directory

File System Specific Examples

IconsPage/example.png Here are a couple of basic examples for different file system types. I will use /dev/sdb1 or /dev/hda2 for simplicity, but remember that any /dev location, UUID=<some_id>, or LABEL=<some_label> can work.

Extended file systems (ext)

Specifically, these are the ext2, ext3, and ext4 filesystems that are common as root filesystems in Linux. The main difference between ext2 and ext3 is that ext3 has journaling which helps protect it from errors when the system crashes. The more modern ext4 supports larger volumes along with other improvements, and is backward compatible with ext3.

A root filesystem:

UUID=30fcb748-ad1e-4228-af2f-951e8e7b56df / ext3 defaults,errors=remount-ro,noatime 0 1

A non-root file system, ext2:

/dev/sdb1 /media/disk2 ext2 defaults 0 2

File Allocation Table (FAT)

Specifically, fat16 and fat32, which are common for USB flash drives and flash cards for cameras and other devices.

/dev/hda2 /media/data1 vfat defaults,user,exec,uid=1000,gid=100,umask=000 0 0
/dev/sdb1 /media/data2 vfat defaults,user,dmask=027,fmask=137 0 0

New Technology File System (NTFS)

NTFS is typically used for a Windows partition.

/dev/hda2 /media/windows ntfs-3g defaults,locale=en_US.utf8 0 0

For a list of locales available on your system, run

  •  locale -a

Hierarchical File System (HFS)

HFS, or more commonly, HFS+, are filesystems generally used by Apple computers.

For Read/Write mounting:

/dev/sdb2 /media/Macintosh_HD hfsplus rw,exec,auto,users 0 0

Note: if you want to write data on this partition, you must disable the journalization of this partition with diskutil under Mac OS.

For Read only:

/dev/sda2 /media/Machintosh_HD hfsplus ro,defaults 0 2

Note: if you want to have access to your files on Ubuntu, you must change the permission of the folders and contained files you want to access by doing in the apple terminal:

sudo chmod -R 755 Folder

"Staff" group should have appeared in this folder's info. You can do this on Music and Movies to access these files from Ubuntu.

Editing fstab

IconsPage/editor.png Please, before you edit system files, make a backup. The -B flag with nano will make a backup automatically.

To edit the file in Ubuntu, run:

gksu gedit /etc/fstab

To edit the file in Kubuntu, run:

kdesu kate /etc/fstab

To edit the file directly in terminal, run:

sudo nano -Bw /etc/fstab
  • -B = Backup origional fstab to /etc/fstab~ .
  • -w = disable wrap of long lines.

Alternate:

sudo -e /etc/fstab

Useful Commands

IconsPage/terminal.png To view the contents of /etc/fstab, run the following terminal command:

cat /etc/fstab

To get a list of all the UUIDs, use one of the following two commands:

sudo blkid
ls -l /dev/disk/by-uuid

To list the drives and relevant partitions that are attached to your system, run:

sudo fdisk -l

To mount all file systems in /etc/fstab, run:

sudo mount -a

Remember that the mount point must already exist, otherwise the entry will not mount on the filesystem. To create a new mount point, use root privileges to create the mount point. Here is the generalization and an example:

sudo mkdir /path/to/mountpoint
sudo mkdir /media/disk2

After upgrade to 16.04 LTS rc.local is not executing a command

About rc.local :

Ubuntu is now using systemd, and rc.local is now considered a service which is turned "off" by default
You can turn rc.local "on" by entering the following command and rebooting:

sudo systemctl enable rc-local.service

ubuntu 1

example 4

Σελίδα 8 από 11